Dynamic past 12 month 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

Dynamic past 12 month query

Author

Topic

BITS
Starting Member

1 Post

Posted - 2010-05-10 : 06:07:11

Hello,

I'm pretty new to SQL and thought I'd register here to get some help and advice!

I currently have a query to give me the past 12 months results from a database. At the mo, it is a static 12 months which I specify.. I want it to be dynamic and give the past 12 months from the point of which the query is run..

My current code with static date range.

declare @startDate DATETIME
declare @endDate DATETIME
set @startDate = '2010-01-01 00:00:00'
set @endDate = '2010-02-01 00:00:00'

SELECT dh.deviceid,convert(char(10),dh.date,103) as Date,d.devicedescription,d.serialnumber,d.location,cast((cast(dh.ActualAvailability as float)/cast(dh.expectedAvailability as float)*100) as int) Percentage, emt.ErrorMessage, convert(char(8),dn.ReportTime,108) as ReportTime, MAX(m.DeviceTotal)AS[DeviceTotal], MAX(m.TotalCopy) AS TotalCopy, MAX(m.TotalPrint) AS TotalPrint, MAX(m.TotalFax) AS TotalFax
FROM DeviceAvailabilityHistory dh

left join Devices d
on(d.deviceid=dh.deviceid)

left join DeviceNotifications dn
on(dh.DeviceId=dn.DeviceId and dn.ErrorLevel = 1 and convert(char(10),dh.date,103) = convert(char(10),dn.ReportTime,103))

left join ErrorMessageText emt
on(dn.ErrorMessageid = emt.ErrorMessageId)

left join MeterReadings m
on (dh.Deviceid=m.deviceid)

where dh.date>=@startDate and dh.date<@endDate
AND (dh.ActualAvailability <> dh.expectedAvailability) and dn.ErrorLevel = 1 and d.Monitored='true'
group by dh.deviceid,dh.date,d.location,d.devicedescription,d.serialnumber,dn.ReportTime,dh.expectedAvailability,dh.ActualAvailability,emt.ErrorMessage
order by d.location asc,dh.date asc,ReportTime asc

Can anyone help with getting this code to show the last 12 months but dynamically?

Thankyou for any help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-10 : 06:58:21

you can get the 1st day of the current month & prev months with following query


select	[1st day of current month] = dateadd(month, datediff(month, 0, getdate()),       0),
	[1st day of previous month] = dateadd(month, datediff(month, 0, getdate()) -  1, 0),
	[1st day of 12 months back] = dateadd(month, datediff(month, 0, getdate()) - 12, 0)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:14:23

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dvrameshusa
Starting Member

1 Post

Posted - 2010-08-23 : 11:57:38

Please take a look at the sql, if you are planning to run a sql statement to get the dates for the last 12 months for example 08/01/2009 till 07/31/2009’.

SELECT DATEADD(year,-1,(dateadd(month, datediff(month, -1, getdate()) - 1, -1) + 1)) 'First day of last year 12 months',
dateadd(month, datediff(month, -1, getdate()) - 1, -1) 'Last day of the last month'

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 12:17:56

quote:
Originally posted by dvrameshusa

Please take a look at the sql, if you are planning to run a sql statement to get the dates for the last 12 months for example 08/01/2009 till 07/31/2009’.

SELECT DATEADD(year,-1,(dateadd(month, datediff(month, -1, getdate()) - 1, -1) + 1)) 'First day of last year 12 months',
dateadd(month, datediff(month, -1, getdate()) - 1, -1) 'Last day of the last month'

Thanks

i see so many -1 . whats purpose of that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources