Advanced Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Advanced Query Help

Author

Topic

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-20 : 10:07:04

Greetings All! I am in need of some advanced query help. I've been asked to create a procedure or view which when passed two dates (start/end) it shows that period and the same period a year earlier. I have that part down. What I now need is a count of how many rows are returned for each of the two periods. I will attach my current script (commented out the count statement since it doesn't provide the results I am after)

, If there is an easier way to go about it I'm open to suggestions. Thanks in advance for any insight.

ALTER PROCEDURE [dbo].[proc_Tag_Trend2]
@StartDate1 datetime,
@EndDate1 datetime
AS
BEGIN
DECLARE @StartDate2 datetime
DECLARE @EndDate2 datetime

SET @StartDate2 = @StartDate1 - 365;
SET @EndDate2 = @EndDate1 - 365;

SELECT LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag,
--COUNT (DISTINCT LMFW.Type) AS Ack_Count1
FROM LMFW
WHERE LMFW.Type = 'Ack' and MsgDate between @StartDate1 and @EndDate1
GROUP BY LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag
END;

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-20 : 10:18:03

Try this:


SELECT LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag,
ROW_NUMBER() OVER (ORDER BY LMFW.MsgDate DESC, LMFW.Type DESC, LMFW.UsrStr7 DESC, LMFW.Tag DESC ) + ROW_NUMBER() OVER (ORDER BY LMFW.MsgDate ASC, LMFW.Type ASC, LMFW.UsrStr7 ASC, LMFW.Tag ASC ) - 1 AS rownum 
FROM LMFW
WHERE LMFW.Type = 'Ack' and MsgDate between @StartDate1 and @EndDate1
GROUP BY LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-20 : 10:30:28

Thank you Rick. That does the job of counting the first date range, is it possible to also count the second date range?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-20 : 10:49:25

You didn't include a second date range in the query?!?

But you could simply do the same on a union and just replace the date variables..

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-20 : 11:08:56

I have the @StartDate2 and @EndDate2 variables in there. We're already above my capability, would you be able to point me in the right direction to perform the Union sir? Many thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-20 : 11:24:32

Something along these lines:


SELECT LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag,
ROW_NUMBER() OVER (ORDER BY LMFW.MsgDate DESC, LMFW.Type DESC, LMFW.UsrStr7 DESC, LMFW.Tag DESC ) + ROW_NUMBER() OVER (ORDER BY LMFW.MsgDate ASC, LMFW.Type ASC, LMFW.UsrStr7 ASC, LMFW.Tag ASC ) - 1 AS rownum 
FROM LMFW
WHERE LMFW.Type = 'Ack' and MsgDate between @StartDate1 and @EndDate1
GROUP BY LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag
UNION ALL
SELECT LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag,
ROW_NUMBER() OVER (ORDER BY LMFW.MsgDate DESC, LMFW.Type DESC, LMFW.UsrStr7 DESC, LMFW.Tag DESC ) + ROW_NUMBER() OVER (ORDER BY LMFW.MsgDate ASC, LMFW.Type ASC, LMFW.UsrStr7 ASC, LMFW.Tag ASC ) - 1 AS rownum 
FROM LMFW
WHERE LMFW.Type = 'Ack' and MsgDate between @StartDate2 and @EndDate2
GROUP BY LMFW.MsgDate, LMFW.Type, LMFW.UsrStr7, LMFW.Tag

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-20 : 13:37:25

That did the trick. Is it possible to take it a step further and display a count for 'ack' in each day? Or would that even be possible with the existing query?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-21 : 03:35:29

yeah, just use a different ROW_NUMBER(), can be used in the same query:

ROW_NUMBER() OVER (PARTITION BY ack, MsgDate ORDER BY ack DESC, MsgDate DESC) + ROW_NUMBER() OVER (PARTITION BY ack, MsgDate ORDER BY ack ASC, MsgDate ASC) - 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources