return duplicate records

Hello experts,

I'm trying the run the following query with specific intentions.

I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.

I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?

Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);

alexAlex,

There are a few solutions. Two are (might have typos, but you should be
able to get the idea):

select Employee_last_name, Employee_first_name
from tbl_employee
join (
select '009' as id union all
select '008' as id union all
select '007' as id union all
select '007' as id union all
select '006' as id
) as IDs
on IDs.id = tbl_employee.employee_id

or to make the specification of ids simpler:

declare @.ids varchar(1000)
set @.ids = '009008007007006'
declare @.idlength int
set @.idlength = 3

select Employee_last_name, Employee_first_name
from tbl_employee
join a_permanent_table_of_integers_from_0_to_whatever as Nums
on employee_id = substring(@.ids,@.idlength*n+1,@.idlength)
and n < len(@.ids)/@.idlength
-- [n] is the column name for the permanent table and should
-- be that tables primary key

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
alex wrote:

Quote:

Originally Posted by

Hello experts,
>
I'm trying the run the following query with specific intentions.
>
I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.
>
I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?
>
Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);
>
alex
>

|||>I would like the query to return 5 results; i.e., 4 distinct and one duplicate. <<

The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:

SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>I would like the query to return 5 results; i.e., 4 distinct and one

duplicate. <<

Quote:

Originally Posted by

>
The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:
>
SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'

Joe, I thought you knew SQL? This query will not return the results
that Alex was asking for.

Why is left as an exercise to the reader.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

return columns for different sql bases

I want in one query return columns from tables where exist in different sql bases.

In MS Access can link tables from many defferents sql bases or other MSAccess bases and finally can make a simple query where return columns from tables where exist in different bases.

How can i have the same functionality in Visual Basic .Net 2005 (or Visual Basic 6.0)

If you are using SQL Server, you can use Linked servers (see http://msdn2.microsoft.com/en-us/library/ms188279.aspx) or for a one time query to another database, you can use the OPENROWSET T-SQL directly in your SQL query (see http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

Regards, Uwa.

sql

Return All Rows

Hello Again,

Here is my situation..

I have a datareader that reads through all the records that satisfy my query condition... Once i have my records in the datareader i want to run through all of the values from it and form HTML rows, how do i do it? Following is the code that i have written

CustID = Session("CustCode")
viewSqlStr = "Select [Lead Title], [Lead Date] from [Lead Info] Where [Customer ID] = '" & CustID & "'"
objCommand = New SqlCommand(viewSqlStr, objConn)
objConn.Open()
objDR = objCommand.ExecuteReader()

Then in the HTML i do:

<% While objDR.Read() %>
<tr>
<td class="f10" bgcolor="#ffffff" valign="top">Edit</td>
<td class="f10" bgcolor="#ffffff" valign="top"><%= objDR("Lead Title") %></td>
<td class="f10" bgcolor="#ffffff" valign="top"><%= objDR("Lead Date") %></td>
</tr>
<% End While %
When i do this... inspite of having more than one records in the database.. only the last record is displayed on the screen. how do i make it display all the records here? i know in asp ado there was a movenext statement... what goes here? can someone pls help me with this so that all the records can be displayed here?

Thanks in advance,
~ssBinding this to a datagrid would probably be much faster...|||What this will do is to read through all of them - - forward only - - then, the last one in the list gets left on the screen for you to see.

What you want can be done with a Repeater or a DataList....Create the Item Template, then, instead of objDR.Read - - just bind the dataReader to the DataList or Repeater.

Check out:
http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=dListdReader

It's not exactly your scenario, with the Table, etc, but it will show you how to use a DataList with a DataReader. You can add a HeaderTemplate for the Column HEaders and then one row and a cell for each item you want to show, in the ItemTemplate.

However, it's MUCH easier to use a DataGrid - - - skipping all the Table/Row/Cell stuff - - it's done automatically with a DataGrid

return all rows

Hello,
Here is my query for my report. Can you make this query fetch all rows if
@.myID parameter is null?
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
Thanks,
Jim.write a stored procedure and use an if condition
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||is it possible without stored procedure?
"NI" wrote:
> write a stored procedure and use an if condition
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >|||Hello Jim
Try to set your "is null" clause in the report settings itself
Ruud Boots
Holland
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||Here is a technique I like for the WHERE clause so that null means "all":
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID or @.myID is null)
AND myDate BETWEEN @.StartDate AND @.EndDate
I am currently trying to find how to pass a null parameter to a report. If
you know how to pass a null in RS, I would appreciate the feedback.
Thanks.
Randy Howie
--
"Ruud" wrote:
> Hello Jim
> Try to set your "is null" clause in the report settings itself
> Ruud Boots
> Holland
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >sql

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:

> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it
to
> be function and return all fields from all tables, BUT this one do not wan
t
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
> Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...[vbcol=seagreen]
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
>|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 = Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:
> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it to
> be function and return all fields from all tables, BUT this one do not want
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 => Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
>> Following sample function. This one works in query analyzer, as well as
>> works as stored procedure. Because of the nature of the program I need it
>> to
>> be function and return all fields from all tables, BUT this one do not
>> want
>> to compile because it has fileds with the same names in different tables.
>> How to fix it?
>> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
>> RETURNS TABLE
>> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
>> column names on different tables to be displayed
>> FROM Table1 INNER JOIN (((Table5 AS uu
>> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
>> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
>> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =>> Table4.Filed5
>> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>>
>> --
>> Tamir Khason
>> "The computer is no better
>> than its program." [Elting Elmore Morison]
>> http://www.dotnet.us/
>>|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
>> Thank you for response, BUT there are 10 tables with about 100
>> columns each one. It's seemed me rather supid to specified 1000
>> columns for such function.
>>
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.comsql

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:

> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it to
> be function and return all fields from all tables, BUT this one do not want
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
> Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>
|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...[vbcol=seagreen]
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com
|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Return all databases names using query?

Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.

Hi,

SQL Server has built-in proceduresp_databases to do that. You just need to have permissions to execute it.

Return a value after insert the query

Hi!

create table testReturn
(
id int identity(100,1),
name varchar(10)
)

How can I return the value of identity column after inserting the value.

Dim objConn As SqlConnection
Dim SQLCmd As SqlClient.SqlCommand
Dim ds As New DataSet
Dim strsql As String

Try

objConn = New SqlConnection
objConn.ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=localhost;" & _
"Initial Catalog=mydb;" & _
"User ID=userid;" & _
"Password=pass"

objConn.Open()

strsql = "insert into testReturn values ('a')"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
Dim rowsAffected As Integer = 0
rowsAffected = SQLCmd.ExecuteNonQuery

Dim rv As String
rv = SQLCmd.Parameters(0).Value.ToString()
Response.Write(rv)

Catch ex As Exception
Response.Write(ex.ToString)
End Try

SeeHow do I get the IDENTITY / AUTONUMBER value for the row I inserted?

|||

strsql = "insert into testReturn values ('a'); select @.@.Identity"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)

dim Identiy as Object

Identity = SQLCmd.ExecuteScalar

|||

You need to put "Select @.@.identity" statement immediately after your insert query so it would return the ID value of the record inserted, using the preceding Insert statement.

Cheers
Ritesh

|||

Using @.@.IDENTITY is an vulnerable approach, since it returns the last inserted id of any table. If you are using SQL 2005 then you can use the OUTPUT clause.

SeeHow to get an Identity value with SQL Server 2005

|||

You can either use @.@.identity or scope_identity().

@.@.identity gives you the last generated identity value.

scope_identity() gives you the last generated identity value for current scope.

Just have a quick look at BOL for further understanding.

Hope this will help.

Return a todays Date query?

Hello , i want to writ a query that returns ruslts for today's date only,How to do it? i tried to filter the results using Now() function but it did`t work, any help please?

hi leonardo ,

try this

select columns from tablename where convert(varchar, column_with_date_datatype, 103) = convert(varchar,getdate(),103)

hope it helps

regards,

satish

|||

Hi Leo,

U can also use the split function to receive the date.

I think the now.tostring gives date<space>time

so,

dim dat() as string

dat=split(now.tostring)

Msgbox(dat(0).tostring)

dat(1)-->time

-PSK

|||

Thanks for the help

but what i want is to build up a query in the SQL Server that returns today's date in SQL Statment

Select JoinDate

From Employee

Where **********;

how to have a result for a today's date , a week before and a month before ...

|||

you could use

dateadd() function in that case, for more help refer to sql books online.

thanks,

satish.

|||

You could get the start of the day like so DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)..

This means you would do something like...

SELECT * FROM table WHERE Created >= DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)

Enjoy, Steve

Return a horizontal table for result

Has any one had luck writing a query that returns a result as follows:
CheckID PayAmtDeductAmt Tax Amt
1Admin200.00401k70.00FUTA13.00
1SAL1500.00FICA25.00
1MED30.00
1SOC107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
Please post the exact DDL (CREATE TABLE statements) for all of the tables
involved. It sounds like what you want is a pivot table, which can be
easily done.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
Has any one had luck writing a query that returns a result as follows:
CheckID Pay Amt Deduct Amt Tax Amt
1 Admin 200.00 401k 70.00 FUTA 13.00
1 SAL 1500.00 FICA 25.00
1 MED 30.00
1 SOC 107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check
table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
|||Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKeyintNOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKeyintNOT NULL,
paytDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKeyintNOT NULL ,
pctPayCheckintNOT NULL,
pctTaxTypeintNOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKeyintNOT NULL,
dedtDescriptionvarchar(80)NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKeyintNOT NULL ,
taxtDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtTypetinyintNOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKeyintNOT NULL,
pcdPayCheckintNOT NULL,
pcdDeductionTypeintNOT NULL,
pcdAmount decimal(19,2)NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType(paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType(paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType(dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType(dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck(pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:

> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||Looking at the original post, you have an assortment of results, with an
inconsistent number or columns in each row. What exactly are the business
requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
Do you want all deductions spread horizontally?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKey int NOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKey int NOT NULL,
paytDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKey int NOT NULL ,
pctPayCheck int NOT NULL,
pctTaxType int NOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKey int NOT NULL,
dedtDescription varchar(80) NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKey int NOT NULL ,
taxtDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtType tinyint NOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKey int NOT NULL,
pcdPayCheck int NOT NULL,
pcdDeductionType int NOT NULL,
pcdAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType (paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:

> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:

> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||So, IOW, there is no direct correlation between a given deduction and a
given pay. Rather the correlation is between the deduction and the check
only. Is that right?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:

> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime,
> etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee
> Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||Yes that is correct.
"Tom Moreau" wrote:

> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays, and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Well, that is an odd requirement. The following may suffice:
select
pc.pcKey
, x.Type
, p.pAmount Pay
, pcd.pcdAmount Deduction
, pct.pctAmount Tax
from
(
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) as x (Type)
cross
join
PayCheck pc
left
join
Pays p on p.pPayType = x.Type
and p.pPayCheck = pc.pcKey
left
join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
and pcd.pcdDeductionType = x.Type
left
join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
and pct.pctTaxType = x.Type
where not
(
p.pAmount is null
and pcd.pcdAmount is null
and pct.pctAmount is null
)
Note that you will get gaps, i.e. there may be nulls interspersed within a
column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
Yes that is correct.
"Tom Moreau" wrote:

> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays,
> and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:

> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
|||My pleasure. Speaking of UNION ALL, have you considered FOR XML EXPLICIT?
select
1 as Tag
, NULL as Parent
, pcKey as [Check!1!CheckID]
, NULL as [Pay!2!Pay]
, NULL as [Deduction!3!Deduction]
, NULL as [Tax!4!Tax]
from
PayCheck
union all
select
2
, 1
, pPayCheck
, pAmount
, NULL
, NULL
from
Pays
union all
select
3
, 1
, pcdPayCheck
, NULL
, pcdAmount
, NULL
from
PayCheckDeduction
union all
select
4
, 1
, pctPayCheck
, NULL
, NULL
, pctAmount Tax
from
PayCheckTax
order by
[Check!1!CheckID]
for xml explicit
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:B68BD197-6E5A-4A8A-8A98-A81CBF848F46@.microsoft.com...
Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:

> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
sql

Return 2 entries per group based on the two lowest values in group

Below is a query that I need to modify so that it returns just the 2
MastNUMs, Stores, and Distances where the Distances are the two lowest for a
mastnum. So the results would be more like this
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000068 76 4.447685
000000068 70 5.516853
000000069 70 3.836682
000000069 76 6.331691
000000070 76 3.729323
SELECT TOP 15 MastNUM, Store, Distance
FROM DistTest
GROUP BY MastNUM, Store, Distance
ORDER BY MastNUM, Distance
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000067 69 8.112116
000000067 112 19.924702
000000068 76 4.447685
000000068 70 5.516853
000000068 69 6.874022
000000068 112 18.622366
000000068 71 19.396528
000000069 70 3.836682
000000069 76 6.331691
000000069 69 8.924779
000000069 112 16.709897
000000069 71 17.462224
000000070 76 3.729323
--
KoryI can get the lowest for each with the following but how can I get the
lowest 2?
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||I think I have found a way to get the lowest and 2nd lowest, but I hope
their is a better way than the following..
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
(
SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest t2
WHERE Distance > (
SELECT MIN(Distance)
FROM DistTest d2
WHERE t2.MastNum = d2.MastNum
GROUP BY MastNum
)
GROUP BY MastNum
) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
order by T1.mASTnUM
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:51 AM CDT ] [Execution: 16/ms]
MastNUM Store Distance
-- -- --
000000067 70 7.082444
000000068 70 5.516853
000000069 76 6.331691
000000070 70 6.957127
000000071 70 5.944537
000000072 76 6.277529
000000073 70 5.487792
000000074 70 7.193161
000000076 70 5.201532
000000077 70 5.412091
000000078 76 5.295454
000000079 76 5.395715
000000080 76 5.950446
000000081 70 5.920339
000000082 70 5.529576
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:52 AM CDT ] [Execution: 93/ms]
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:uVgDQB9WDHA.2268@.TK2MSFTNGP11.phx.gbl...
> I can get the lowest for each with the following but how can I get the
> lowest 2?
> SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
> FROM DistTest t1,
> ( SELECT MastNUM, MIN(Distance) AS MinDist
> FROM DistTest
> GROUP BY MastNum ) as Dmin
> WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
> go
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000068 76 4.447685
> 000000069 70 3.836682
> 000000070 76 3.729323
> 000000071 76 4.046238
> 000000072 70 3.928709
> 000000073 76 4.663551
> 000000074 76 3.206388
> 000000076 76 4.745636
> 000000077 76 5.338428
> 000000078 70 5.121837
> 000000079 70 4.580213
> 000000080 70 4.338181
> 000000081 76 4.069455
> 000000082 76 4.465975
>
> "Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
> news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> > Below is a query that I need to modify so that it returns just the 2
> > MastNUMs, Stores, and Distances where the Distances are the two lowest
for
> a
> > mastnum. So the results would be more like this
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000070 76 3.729323
> >
> > SELECT TOP 15 MastNUM, Store, Distance
> > FROM DistTest
> > GROUP BY MastNUM, Store, Distance
> > ORDER BY MastNUM, Distance
> >
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000067 69 8.112116
> > 000000067 112 19.924702
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000068 69 6.874022
> > 000000068 112 18.622366
> > 000000068 71 19.396528
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000069 69 8.924779
> > 000000069 112 16.709897
> > 000000069 71 17.462224
> > 000000070 76 3.729323
> >
> > --
> >
> > Kory
> >
> >
>|||The initial step creating this list is as follows:
SELECT g.MASTNUM, s.STORE, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG ) AS Distance
INTO DistTest
FROM geomailing g, geoStore s
WHERE master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) <= 20
GROUP BY g.Mastnum, s.Store, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG )
ORDER BY g.Mastnum, master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG )
Can anyone suggest a way that it only inserts into this table the 2 lowest
Distances ( master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) ) ? So
as to avoid having to pull out the two lowest distances later?
Thanks.
Kory wrote in message news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||Does anyone have any suggestions on how to get just the 2 lowest values
grouped?|||I would imagine a lot of people have a lot of ideas but with what you have
given us it would be stabbing in the dark.
1. Can you post a simple table structure
2. Post sample data to enter in
3. Tell us what you expect to see as the end result.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:OPqoHUDXDHA.208@.tk2msftngp13.phx.gbl...
> Does anyone have any suggestions on how to get just the 2 lowest values
> grouped?
>

Return 0 rather than Null

i have query which does the following select x from y where t = "House"

x is an integer. If no record is found, how do i get it to return 0 rather than null?

Use a stored procedure and return the affected rows. This will result in 0 if the Select statement you exampled gets no results.|||This is a query within a stored procedure so is there any other way?|||Why can't you add it to the stored procedure?|||Because i would prefer to do it an alternative way not using a stored procedure.|||The only other way is to assign a value of 0 to an int if the calling app detects that no rows were returned to it by the select statement.|||

Are you using this to populate a datatable or a dataset? This would be the most logical way to handle both cases (returning rows OR looking for a record count).

In your code, do:

' assuming table is a System.Data.DataTable that has been' populated with the results of a SQL query.If table.Rows.Count > 0Then' the table has rows.Else' the table has no rows.End If

|||Thanks for your help. It is returned to a variable within a stored procedure. Could I use an IF statement or anything within the SP?|||

Sorry, I didn't see that your select statement was inside an sp.

You can use an if statement if you want. Just like c++ or c#, if it's only one line after the if, you don't need anything else. If there are multiple lines, use BEGIN and END tags.

Also, I'm not sure how to get the number of rows within the last select statement, but you could perform a count before doing the select.

DECLARE @.Countint-- count the number of rows to be selectedSELECT @.Count =Count(*)from MyTableWhere FName ='Jane'IF @.Count > 0BEGIN-- Perform the SQL statement to get rows.SELECT *from MyTableWhere FName ='Jane'ENDELSEBEGIN-- return 0, no rows were returned.Return 0-- or Return @.CountEND

|||

this would work faster:

IF EXISTS(SELECT *from MyTableWhere FName ='Jane')

BEGIN
-- Perform the SQL statement to get rows.
SELECT *from MyTableWhere FName ='Jane'
END
ELSE
BEGIN
-- return 0 in first cell in one row
SELECT count(*)from MyTableWhere FName ='Jane' -- or just SELECT 0
-- and return if you need
RETURN 0

END

Return 0 if null

Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:

> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:

> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:

>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Return # of results that would have been returned without TOP

I want to return the first 100 results of a query that would otherwise retur
n
tens of thousands of results. To do this, I use SELECT TOP 100 ...
I'd really like to be able to show the user how many results would have been
returned had I not limited the query to 100 results. Is there a way to do
this in the same query?
The key is in the same query - obviously I could SELECT COUNT (*) and use
the exact same from and where clauses from the original query. That has two
big divantages. First, any time I need to change the query I need to
change two queries, and second, it's two trips to the database, and it
doesn't seem like there should be a need for that.
Thank you.Greg,
Try one of these approaches:
select top 10
OrderID,
(select count(*) from Northwind..Orders)
from Northwind..Orders
order by CustomerID
go
select top 10
OrderID, CustomerID, C.ct
from Northwind..Orders
cross join (
select count(*) as ct
from Northwind..Orders
) as C
order by CustomerID
Steve Kass
Drew University
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:E94CC761-3C77-43F0-BDA2-F646D609A837@.microsoft.com...
>I want to return the first 100 results of a query that would otherwise
>return
> tens of thousands of results. To do this, I use SELECT TOP 100 ...
> I'd really like to be able to show the user how many results would have
> been
> returned had I not limited the query to 100 results. Is there a way to do
> this in the same query?
> The key is in the same query - obviously I could SELECT COUNT (*) and use
> the exact same from and where clauses from the original query. That has
> two
> big divantages. First, any time I need to change the query I need to
> change two queries, and second, it's two trips to the database, and it
> doesn't seem like there should be a need for that.
> Thank you.|||Greg Smalter (GregSmalter@.discussions.microsoft.com) writes:
> I want to return the first 100 results of a query that would otherwise
> return tens of thousands of results. To do this, I use SELECT TOP 100
> ...
> I'd really like to be able to show the user how many results would have
> been returned had I not limited the query to 100 results. Is there a
> way to do this in the same query?
> The key is in the same query - obviously I could SELECT COUNT (*) and
> use the exact same from and where clauses from the original query. That
> has two big divantages. First, any time I need to change the query I
> need to change two queries, and second, it's two trips to the database,
> and it doesn't seem like there should be a need for that.
If you are on SQL 2005, you can do this:
WITH CTE (OrderID, CustomerID) AS
(SELECT OrderID, CustomerID
FROM Orders
WHERE OrderDate BETWEEN '19971201' AND '19971231')
SELECT TOP 10 OrderID, CustomerID,
Cnt = (SELECT COUNT(*) FROM CTE)
FROM CTE
This evades the maintenance problem entirely. Whether it is actually the
best from a performance point of view, requires benchmarking.
The alternative is to run the base SELECT into a temp table, and the
run a SELECT TOP and a SELECT COUNT from that one.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||on 2005, you can write this:
select top 10
OrderID,
row_number() over(order by CustomerId desc)
total_in_the_first_row
from Northwind..Orders
order by CustomerID
go
It's possible the performance will suck however|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> on 2005, you can write this:
> select top 10
> OrderID,
> row_number() over(order by CustomerId desc)
> total_in_the_first_row
> from Northwind..Orders
> order by CustomerID
> go
> It's possible the performance will suck however
And the output is somewhat incomprehensible:
OrderID total_in_the_first_row
11011 825
10952 826
10835 827
10702 828
10692 829
10643 830
10926 821
10759 822
10625 823
10308 824
The correct answer of 830 is in the middle...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thank you for the correction. Yet I guess something like this should
work if OrderID is a PK:
select top 10
OrderID,
row_number() over(order by CustomerId desc, OrderID desc)
total_in_the_first_row
from Northwind..Orders
order by CustomerID, OrderID
cannot test it until tomorrow however...

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving data from SQL text field

I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?Use a parameter.|||SQL QA does not allow declaring local variables with text type. I am trying to pull the SQL text filed's all the text. Declaring or converting to varchar will limit it to 8000 characters only.

An example will be very handy..

Thxsql

Retrieving XML Data using FOR XML AUTO into sql variable

hello guru's

I am using SQL server 2000, Version 8.0(SP4)
I need to put XML string returned from SELECT FOR XML AUTO query to a variable, But i can not able to do it in Transact SQL . can you please help me out ?

Declare @.Message varchar(200)

SELECT col1,col2 from table1 FOR XML AUTO

I need to put result of above query to @.Message variable.

I have tried with following but failed ......

SET @.Message = SELECT col1,col2 from table1 FOR XML AUTO

SET @.Message = sp_executesql N'SELECT col1,col2 from table1 FOR XML AUTO'

can anyone suggest me the solution to this .....

Thanks

Which version of SQL Server are you using -- 2000 or 2005?

|||

Parentheses should help e.g.

Code Snippet

SET @.Message = (SELECT col1, col2 FROM table1 FOR XML AUTO);

|||Thanks Kent for pointing me out,

I am using SQL server 2000 Version 8.0(SP4)

I have updated the post.

Thanks

|||Hello Martin,

Parenthesis does not helped ...It is giving syntax error.

Thanks for reply
|||

Sorry, works with SQL Server 2005 I tested with but then obviously not with SQL Server 2000 which you are using.

|||

Hi,

The code you are trying to execute will not work as FOR XML is not supported to work with assignment in SQL Server 2000. This works only with 2005