Query same time frame from last year.

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)

Query same time frame from last year.

Author

Topic

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-22 : 13:32:05

I have constructed this query which is working but I need to add to it without breaking it. In my view I'm cleaning up the datetime field MsgDate and counting the tags. What I need to add is a count for the same time period a year prior. I've been playing with the script but can't make any headway. Many thanks.

ALTER VIEW [dbo].[V_tag_trend]
AS
SELECT     LMFW.Type, LMFW.UsrStr1 AS Alarm,
		   dateadd(d,0,datediff(d,0,dbo.LMFW.MsgDate)) AS MsgDate,
		   count(*) AS TagCount,
		dbo.[GROUP-CONSOLE].CONSOLE	
FROM         dbo.LMFW JOIN
                      dbo.[GROUP-CONSOLE] ON dbo.LMFW.UsrStr7 = dbo.[GROUP-CONSOLE].[GROUP]
WHERE     (dbo.LMFW.Type = 'Ack') AND UsrStr1 IN ('COS','HILO','DEV','ROC','PLM','WARNTIME','RTUCOMM','STNBAL','PRESSMON')
GROUP BY dateadd(d,0,datediff(d,0,dbo.LMFW.MsgDate)), dateadd(d,0,datediff(d,0,dbo.LMFW.MsgDate - 365)), LMFW.Type, LMFW.UsrStr1, dbo.[GROUP-CONSOLE].CONSOLE

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 13:50:08

>> add is a count for the same time period a year prior

The dates shouldn't matter


   SELECT     L.[Type]
			, L.[UsrStr1]														AS [Alarm]
			, CONVERT(varchar(10),DATEADD(d,0,DATEDIFF(d,0,L.[MsgDate]),101))	AS [MsgDate]
			, COUNT(*)															AS [TagCount]
			, G.[CONSOLE]	
	  FROM    [dbo].[LMFW]			L
INNER JOIN	  [dbo].[GROUP-CONSOLE]	G 
		ON	  L.[UsrStr7] = G.[GROUP]
	 WHERE    L.[Type] = 'Ack' 
	   AND    L.[UsrStr1] IN ('COS','HILO','DEV','ROC','PLM','WARNTIME','RTUCOMM','STNBAL','PRESSMON')
  GROUP BY    L.[Type]
			, L.[UsrStr1]
			, CONVERT(varchar(10),DATEADD(d,0,DATEDIFF(d,0,L.[MsgDate]),101))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-22 : 15:09:24

Thanks for the reply Brett. I got an error when executing your code and I had to edit to run it (Posted below). I was hoping for at least one additional column that counts the occurances of the same date in the prior year. I may be confused or not being clear it's been a long day.

SELECT     L.[Type], L.[UsrStr1] AS [Alarm]
			, CONVERT (varchar(10),DATEADD(d,DATEDIFF(d,0,L.[MsgDate]),0),101)AS [MsgDate]
			, COUNT(*)AS [TagCount], G.[CONSOLE]	
	  FROM    [dbo].[LMFW]L
INNER JOIN	  [dbo].[GROUP-CONSOLE]G 
		ON	  L.[UsrStr7] = G.[GROUP]
	 WHERE    L.[Type] = 'Ack' AND L.[UsrStr1] IN ('COS','HILO','DEV','ROC','PLM','WARNTIME','RTUCOMM','STNBAL','PRESSMON')
  GROUP BY    L.[Type], L.[UsrStr1],  G.[CONSOLE], CONVERT (varchar(10),DATEADD(d,DATEDIFF(d,0,L.[MsgDate]),0),101)

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 15:17:03

well post the error..without DDL I can't Parse it to see what's wrong

So what you are saying is that you want the count of the current year AND the count of the previous year for the same day and other grouping?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-22 : 15:36:41

Sorry about that. As I mentioned in my last post I edited your original reply and got it to work, the code in my last post is the working script. The error message was :Msg 174, Level 15, State 1, Line 3
The dateadd function requires 3 argument(s).

Because of the limitations of the front end software this query is designed for I need two dates and a count of each.

MsgDate, Count of MsgDate
MsgDate2 (which is exactly a year prior to the value of MsgDate), Count of how many times MsgDate2 occurs in MsgDate (the first one)

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 15:54:36

>> Count of how many times MsgDate2 occurs in MsgDate (the first one)

Care to post some sample data and what the expect result should be?

you're kinda losing me...which isn't that hard

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-22 : 16:13:20

I think I'm losing myself too lol.
Ok here goes:

[Type], [Alarm] [MsgDate] [TagCount] [Console] [MsgDate2**]

Ack, COS, 09/06/2011, 133, REDRIVER, 09/06/2010
Ack, RTUCOMM,09/27/2011, 52, PRODNE , 09/27/2010
Ack, ROC, 12/14/2011, 11, PRODNE , 12/14/2010
Ack, COS, 01/13/2011, 141, REDRIVER, 01/13/2010
Ack, COS, 04/12/2011, 352, SEAWAY , 04/12/2010
Ack, HILO, 03/30/2011, 253, DIXIE , 03/30/2010
Ack, PLM, 07/01/2011, 5, EAST , 07/01/2010
Ack, RTUCOMM,05/29/2011, 7, SEMINOLE, 05/29/2010
Ack, WARNTIME,10/21/2011, 7, REDRIVER, 10/21/2010
Ack, HILO, 11/29/2011, 146, SEMINOLE, 11/29/2010

[CountofMsgDate2InMsgDate] (as an additional column which counts how many times [MsgDate]-1 year occurs in [MsgDate])

This is used to identify a time period in the current year, and compare it to the same period last year. I may be going about this all wrong as well. Many thanks for your efforts.

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-27 : 13:02:48

Any luck Brett?
Hope your holiday was pleasant.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 15:16:12

Would something like this work for you? My changes to your original query are in red.

ALTER VIEW [dbo].[V_tag_trend]
AS
WITH cte AS
(
   SELECT
      LMFW.Type,
      LMFW.UsrStr1 AS Alarm,
      DATEADD(d, 0, DATEDIFF(d, 0, dbo.LMFW.MsgDate)) AS MsgDate,
      COUNT(*) AS TagCount,
      dbo.[GROUP-CONSOLE].CONSOLE
   FROM
      dbo.LMFW
      JOIN dbo.[GROUP-CONSOLE]
         ON  dbo.LMFW.UsrStr7 = dbo.[GROUP-CONSOLE].[GROUP]
   WHERE
      (dbo.LMFW.Type = 'Ack')
      AND UsrStr1 IN ('COS', 'HILO', 'DEV', 'ROC', 'PLM', 'WARNTIME', 'RTUCOMM', 
                  'STNBAL', 'PRESSMON')
   GROUP BY
      DATEADD(d, 0, DATEDIFF(d, 0, dbo.LMFW.MsgDate)),
      DATEADD(d, 0, DATEDIFF(d, 0, dbo.LMFW.MsgDate - 365)),
      LMFW.Type,
      LMFW.UsrStr1,
      dbo.[GROUP-CONSOLE].CONSOLE
)
SELECT
   c1.*,
   c2.TagCount AS TagCountOneYearAgo
FROM
   cte c1
   LEFT JOIN cte c2 ON
      c1.[Type] = c2.[Type]
      AND c1.Alarm = c2.Alarm
      AND c1.MsgDate = DATEADD(YEAR,1,c2.MsgDate)
      AND c1.CONSOLE = c2.CONSOLE

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-27 : 16:26:44

Your additional code executes fine but when I go to open the view I get:
"
SQL Execution Error.
Executed SQL statement: SELECT, Type, Alarm, MsgDate, TagCount, CONSOLE, TagCountOneYearAgo FROM V_tag_trend
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeouct period elapsed prior to completion of the operation or the server is not responding.
"

Which to me is odd because it's not written to select from V_tag_trend.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-27 : 16:30:30

V_tag_trend is the view you are opening, hence the error. As for timeout issue, you'll need to investigate missing indexes by viewing the execution plan. Oh and make sure you add a where clause or restrict the returned result.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-28 : 13:46:30

The execution plan is almost non-existant, meaning there are only two items in it, it doesn't look like a plan at all actually. Any idea as to why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-28 : 13:53:30

Show it to us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-28 : 14:12:54

Sorry it took me a minute.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-28 : 14:17:44

It doesn't look like you are running the view, but rather just looking at the execution plan of the view's code.

Select the "include actual execution plan" from the Query menu and then run your select query. Oh and be sure to show us the select query, not what's inside the view, but rather your query of the view.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

exwarrior187
Starting Member

13 Posts

Posted - 2011-12-28 : 15:14:05

Ok I pointed the procedure at my view and the data comes back exactly as it should. One last thing in the view though. I need to trim the hh:mm:ss out of MsgDate in the view.
The below MsgDate is from the View and MsgDate2 is from the procedure.

MsgDate, MsgDate2
2011-12-10 00:00:00.000, 12/10/2011

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-28 : 16:26:41

You should do this kind of formatting in your application and not in T-SQL. T-SQL can do it via CONVERT function, however don't bother wasting SQL's resources on a job that it is not responsible for. The application is responsible for formatting and is the presentation layer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources