Help in CASE function

Thanks Rudy for the answer of my previous thread.
Here's another scenario of my problem that i hardly get the correct query.

I have a tables with fields and sample records in my database;

Table: tblPo
FIELDS RECORD1
po_number: PO10001
entered_by: chris
approved_by: albert

Table: tblUser
FIELDS RECORD1 RECORD2
user_id: chris albert
first_name: Christopher Alberto
mi: S W
last_name: Lambert Del Puerto

So this is how my database is structured. entered_by and approved_by fields in tblPo are both existed in the tblUser table as user_id so they would share the same table tblUser for First, Middle,Last Names since they both also must be a valid user to create and approve a PO.

Now, I need to get a result like this:

PO # Prepared by Approved by
PO10001 Christopher S. Lambert Alberto W. Del Puerto

My problem now is how to make a query to generate this report. I already joined the two tables with tblPo.user_id to tblUser.user_id and tblPo.approved_by to tblUser.user_id. Unfortunately, both Preparedby and Approvedby resulted to NULL values.

Any help? thanks in advance

BernieHere you go :

use pubs
go
create table tblPO (po_number varchar(100),entered_by varchar(100),approved_by varchar(100))
go
create table tblUser (user_id varchar(100),first_name varchar(100),mi varchar(100),last_name varchar(100))
go
insert into tblPO values('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
go
select
a.po_number,
isnull(b.first_name,'') +' '+ isnull(b.mi,'') +'. '+ isnull(b.last_name,'') Prepared_by,
isnull(c.first_name,'') +' '+ isnull(c.mi,'') +'. ' + isnull(c.last_name,'') Approved_by
from
tblPO a,
tbluser b,
tbluser c
where
a.entered_by = b.user_id
and a.approved_by = c.user_id

go
drop table tbluser
go
drop table tblpo
go|||create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)|||Originally posted by cvandemaele
create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)

Thats better .. that would work even if userid does not exist in tblUser ... but remember to take care of null values .. coz null added to anything yields null|||SALAMAT! Thank you guyz...

I can proceed now with my report...

Berniesql

Help for setting up SQL 2000!

I got sbs 2003 server with sql 2000, and using backup data function to move
the database to another 2k server w/sql2k, after I restore on the 2k server,
I from workstation try to login by using that data, but there is no account
appear on the screen.
What is wrong with it or I did something wrong in SQL setting?
Thanks!
Danny
The new server may not have the login accounts.
See these articles for more information:
Moving Server/Database/Logins
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/colu...rdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Danny" <dannyf@.deny.com> wrote in message
news:%23%23TSFV49GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I got sbs 2003 server with sql 2000, and using backup data function to move
>the database to another 2k server w/sql2k, after I restore on the 2k
>server, I from workstation try to login by using that data, but there is no
>account appear on the screen.
> What is wrong with it or I did something wrong in SQL setting?
> Thanks!
> Danny
>

Help for setting up SQL 2000!

I got sbs 2003 server with sql 2000, and using backup data function to move
the database to another 2k server w/sql2k, after I restore on the 2k server,
I from workstation try to login by using that data, but there is no account
appear on the screen.
What is wrong with it or I did something wrong in SQL setting'
Thanks!
DannyThe new server may not have the login accounts.
See these articles for more information:
Moving Server/Database/Logins
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Danny" <dannyf@.deny.com> wrote in message
news:%23%23TSFV49GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I got sbs 2003 server with sql 2000, and using backup data function to move
>the database to another 2k server w/sql2k, after I restore on the 2k
>server, I from workstation try to login by using that data, but there is no
>account appear on the screen.
> What is wrong with it or I did something wrong in SQL setting'
> Thanks!
> Danny
>

Help for INTRO function

Hello.
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

sql

Help convert MS Access function to MS SQL User Defined Function

I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.
For example:
TrimZero("000000001023") > "1023"
TrimZero("E1025") > "E1025"
TrimZero("000000021021") > "21021"
TrimZero("R5545") > "R5545"
Here is the function that works in access:
Public Function TrimZero(strField As Variant) As String
Dim strReturn As String
If IsNull(strField) = True Then
strReturn = ""
Else
strReturn = strField
Do While Left(strReturn, 1) = "0"
strReturn = Mid(strReturn, 2)
Loop
End If
TrimZero = strReturn
End Function
Is this not possible? I seem not to be able to figure it out onmy own. If I am missing any information in my question, whichinformation should I include?
|||Okay.. with the help from experts-exchange.com I was able to get the answer.
CREATE FUNCTION [dbo].[TrimZero] (@.MyString varchar(50))
RETURNS varchar(50)
AS
BEGIN
If ISNUMERIC(@.MyString) = 1
Begin
While Left (@.MyString,1) = '0'
Begin
Set @.MyString = Right(@.Mystring,Len(@.Mystring) -1)
End
End
Return @.MyString
END
|||A even better function to do the same thing:
create function dbo.trimzero (@.mystring varchar(50))
returns varchar(50)
AS
begin
declare @.myStrInt int
if isnumeric(@.mystring)=1
set @.myString = cast(@.mystring as int)
return @.mystring
end
GO

HELP - need a function like MID in access

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Quote:

Originally Posted by tingirl76

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Nevermind I got it...SUBSTRING(expression, startposition, length)....thank you anyway!!!

Help - Formulas with Functions across Databases

I'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?

-- JakeAre you running at least sp2 ?

-PatP|||Yes, I'm running SP2.

-- Jake|||I know that certain situations do force you to use cross-database DML. But if you're creating a calculated field (is that what you're trying to do?) with a function in another database this means that both databases, AND THEIR NAMES (!!!) must go together, right? Why don't you move the function to the same database as the table? And if the function does reference objects in another database, then it would be transparent for the table. If you want to apply "reusability" and you happen to have a table(-s) in another database that has a calculated field that utilizes the same function, then you can just reference that function within the function wrapper.|||The Database I'm working on is a template that will be replicated for numerous individual projects, so I'm trying to reduce the number of locations the generalized Function code will be stored in. Otherwise, I was curious why I can put ([OtherDatabase].[dbo].[FunctionName](parameters)) in a View (for example) and get the desired result, yet have it not recognize the object when said code is placed in the Formula field (even after it supposedly validates the code). If I'm missing something syntax-wise, please let me know.

help

Does anyone see anything wrong with this statement

CREATE FUNCTION "All Bally Games" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT "Master List"."Tribal No", "Master List"."Stand No",
"Master List"."Serial No", "Master List".Manufacturers, "Master List".Description, "Master
List".Denomination, "Master List".Class, "Bally Main Eprom"."ID Number A", "Bally Main
Eprom"."ID Number B"
FROM "Master List" INNER JOIN "Bally Main Eprom" ON ("Master List"."Tribal No" = "Bally
Main Eprom"."Tribal No")
WHERE ((("Master List".Manufacturers) Like '5'))Unless you include a percent sign (%), the LIKE clause could just as well be an equal sign. I would much prefer the use of brackets [] over the use of quotes "" if that is acceptable.

That's all that jumps right out at me.

-PatP