Last month logic in SQL query - 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

General SQL Server Forums

New to SQL Server Programming

Last month logic in SQL query

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-10-31 : 23:23:59

SELECT *
FROM(

SELECT AUDIT.PKGID,
AUDIT.PROCESSID,
EVENT.NAME,
AUDIT.AUDITSTAMP,
ROW_NUMBER() OVER(PARTITION BY AUDIT.PKGID ORDER BY AUDIT.AUDITSTAMP DESC) AS RowNum
FROM AUDIT INNER JOIN EVENT

ON AUDIT.PROCESSID = EVENT.PROCESSID
AND
AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)
AND (
(:timeperiod = 'LastWeek' and AUDITSTAMP >= dateadd(dd,0, datediff(dd,0,
dateadd(day,-1*datepart(weekday,getdate())+1,dateadd(week,-1,getdate()))))
and AUDITSTAMP < dateadd(dd,0, datediff(dd,0,
dateadd(day,7,dateadd(day,-1*datepart(weekday,getdate()),dateadd(week,-1,getdate())))
)))
or

:timeperiod = 'LastMonth' and

)

)AS T

WHERE RowNum = 1
and :timeperiod = 'LastWeek'
ORDER BY AUDITSTAMP DESC

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 02:25:13

If you want data from one month back then use the logic
DateColumn > DATEADD(MONTH, -1, GETDATE())

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

reacha
Starting Member

49 Posts

Posted - 2010-11-01 : 09:15:38

SELECT *
FROM(

SELECT AUDIT.PKGID,
AUDIT.PROCESSID,
EVENT.NAME,
AUDIT.AUDITSTAMP,
ROW_NUMBER() OVER(PARTITION BY AUDIT.PKGID ORDER BY AUDIT.AUDITSTAMP DESC) AS RowNum
FROM AUDIT INNER JOIN EVENT

ON AUDIT.PROCESSID = EVENT.PROCESSID
AND
AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)
AND (
(:timeperiod = 'LastWeek' and AUDITSTAMP >= dateadd(dd,0, datediff(dd,0,
dateadd(day,-1*datepart(weekday,getdate())+1,dateadd(week,-1,getdate()))))
and AUDITSTAMP < dateadd(dd,0, datediff(dd,0,
dateadd(day,7,dateadd(day,-1*datepart(weekday,getdate()),dateadd(week,-1,getdate())))
)))
or
(:timeperiod = 'LastMonth' and AUDITSTAMP >= DATEADD(MONTH, -1, GETDATE()))
)

)AS T

WHERE RowNum = 1
and :timeperiod = 'LastWeek'
ORDER BY AUDITSTAMP DESC

when i run the following query i was the getting the error as

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ':'.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-01 : 09:54:02

Put the Square bracket around column name :timeperiod.
moreover its not good practice to choose such names.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

reacha
Starting Member

49 Posts

Posted - 2010-11-01 : 10:14:34

Thanks for your help vaibhavktiwari

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 10:19:54

Do you mean "Last month" or "Previous month"?

N 56°04'39.26"
E 12°55'05.63"

reacha
Starting Member

49 Posts

Posted - 2010-11-01 : 10:30:04