Need help with query or possible Stored Procedure

sah

I need some help with the following query:

DECLARE @SRV VARCHAR(20), @date smalldatetime
SET @SRV = (select @@servername)
SET @date = '20040901'
select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name =
'Info_Table', Date_of_Records = @date,
count(*) AS 'Actual Total' ,
max (SER_NO)- min (SER_NO)+1 AS 'Desired total ',
count(*) - (max (SER_NO)- min (SER_NO)+1) AS 'Missing Records',
min (SER_NO) AS 'MIN SER_NO',
max (SER_NO) AS 'MAX SER_NO'
from Info_Table
where DateTime >= @date and DateTime < dateadd(DAY, 1, @date)

I would like to get records of next 30 days from the @date. I can copy
paste this query 30 times with different date and get the desired
results but would prefer to use one query only.

If possible would like a add a variable to get the table name by using
the following query into the above query:

SELECT DISTINCT so.name
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON so.id=sc.id
WHERE so.name like 'm_%' AND sc.name = 'DateTime' OR sc.name ='SER_NO'

So basic idea is to run one simple (or complexed) query to get 30 days
data of many tables select by the above query.
Can someone help please?

Jul 20 '05 #1

Adam Machanic

"sah" <us****@gmail.com> wrote in message
news:36**************************@posting.google.c om...

I would like to get records of next 30 days from the @date. I can copy
paste this query 30 times with different date and get the desired
results but would prefer to use one query only.

You need to group by the on the days to make this work. One way to do that
is using CONVERT and getting rid of the time portion that way. Another is
with DATEADD and DATEDIFF.

I think this should do it for you (using the DATEADD/DATEDIFF method):
DECLARE @SRV VARCHAR(20), @date smalldatetime
SET @SRV = (select @@servername)
SET @date = '20040901'
select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name =
'Info_Table', Date_of_Records = DATEADD(dd, DATEDIFF(dd, 0, [DateTime]), 0),
count(*) AS 'Actual Total' ,
max (SER_NO)- min (SER_NO)+1 AS 'Desired total ',
count(*) - (max (SER_NO)- min (SER_NO)+1) AS 'Missing Records',
min (SER_NO) AS 'MIN SER_NO',
max (SER_NO) AS 'MAX SER_NO'
from Info_Table
where DateTime >= @date and DateTime < dateadd(DAY, 30, @date)
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, [DateTime]), 0)

Jul 20 '05 #2

sah

us****@gmail.com (sah) wrote in message news:<36**************************@posting.google. com>...

I need some help with the following query:

DECLARE @SRV VARCHAR(20), @date smalldatetime
SET @SRV = (select @@servername)
SET @date = '20040901'
select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name =
'Info_Table', Date_of_Records = @date,
count(*) AS 'Actual Total' ,
max (SER_NO)- min (SER_NO)+1 AS 'Desired total ',
count(*) - (max (SER_NO)- min (SER_NO)+1) AS 'Missing Records',
min (SER_NO) AS 'MIN SER_NO',
max (SER_NO) AS 'MAX SER_NO'
from Info_Table
where DateTime >= @date and DateTime < dateadd(DAY, 1, @date)

I would like to get records of next 30 days from the @date. I can copy
paste this query 30 times with different date and get the desired
results but would prefer to use one query only.

If possible would like a add a variable to get the table name by using
the following query into the above query:

SELECT DISTINCT so.name
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON so.id=sc.id
WHERE so.name like 'm_%' AND sc.name = 'DateTime' OR sc.name ='SER_NO'

So basic idea is to run one simple (or complexed) query to get 30 days
data of many tables select by the above query.
Can someone help please?

That worked, thanks.

Now to the second part of it. Please review the following query:

SELECT DISTINCT so.name
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON so.id=sc.id
WHERE so.name like 'm_%' AND sc.name = 'DateTime' OR sc.name ='SER_NO'
I wrote this query to get table names using certain filters. How can I
use this in the previous query instad of the table name.

Also a minor thing. The query you helped me with gives records for one
months only, can you please guide me in getting records from greater
then the given date.

Thanks.

Jul 20 '05 #3

Adam Machanic

"sah" <us****@gmail.com> wrote in message
news:36**************************@posting.google.c om...

Now to the second part of it. Please review the following query:

SELECT DISTINCT so.name
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON so.id=sc.id
WHERE so.name like 'm_%' AND sc.name = 'DateTime' OR sc.name ='SER_NO'

I wrote this query to get table names using certain filters. How can I
use this in the previous query instad of the table name.
Probably not a good idea; why don't you know the name of the table you
want to query? That sounds like a design flaw to me. If you must, you
could use dynamic SQL, but that query could return multiple rows... what
would you do in that case?
Also a minor thing. The query you helped me with gives records for one
months only, can you please guide me in getting records from greater
then the given date.

Sure, just add more than 30 days:

where DateTime >= @date and DateTime < dateadd(DAY, 30, @date)
--30 days
where DateTime >= @date and DateTime < dateadd(DAY, 60, @date)
--60 days
where DateTime >= @date and DateTime < dateadd(DAY, 900, @date)
--900 days...

Jul 20 '05 #4

sah

us****@gmail.com (sah) wrote in message news:<36**************************@posting.google. com>...

I need some help with the following query:

DECLARE @SRV VARCHAR(20), @date smalldatetime
SET @SRV = (select @@servername)
SET @date = '20040901'
select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name =
'Info_Table', Date_of_Records = @date,
count(*) AS 'Actual Total' ,
max (SER_NO)- min (SER_NO)+1 AS 'Desired total ',
count(*) - (max (SER_NO)- min (SER_NO)+1) AS 'Missing Records',
min (SER_NO) AS 'MIN SER_NO',
max (SER_NO) AS 'MAX SER_NO'
from Info_Table
where DateTime >= @date and DateTime < dateadd(DAY, 1, @date)

I would like to get records of next 30 days from the @date. I can copy
paste this query 30 times with different date and get the desired
results but would prefer to use one query only.

If possible would like a add a variable to get the table name by using
the following query into the above query:

SELECT DISTINCT so.name
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON so.id=sc.id
WHERE so.name like 'm_%' AND sc.name = 'DateTime' OR sc.name ='SER_NO'

So basic idea is to run one simple (or complexed) query to get 30 days
data of many tables select by the above query.
Can someone help please?

Thank you so much for all your help Adam. :-)

Jul 20 '05 #5