Date Range Conversion - 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

Date Range Conversion

Author

Topic

Jossy
Starting Member

2 Posts

Posted - 2009-07-22 : 03:32:13

I have a problem in ploting a graph of months against utilzation% our database have weeks and i do not know how to convert to month.My organisation considers a month to begin from the last Friday of a particular month to the last friday of the next month (Same rule applies to a Month of 4weeks and 5weeks). Example is as below:
For the Month of April 2009,it starts from 27th March - 24th April
For the Month of May 2009, it starts from 1st May - 29th May
For the Month of June 2009, it starts from 5th June - 26th June
Here's what i have so far
select period, h10/(htotal-h60 - h70)*100 as utilization
from
(
select format(wkending,'(mm)-mmm-YYYY') as period,sum(switch(extraid='10',hoursworked,extraid<>'10',0) ) as h10
, sum(switch(extraid='50',hoursworked,extraid<>'50',0)) as h50
,sum(switch(extraid='15',hoursworked,extraid<>'15',0)) as h15
,sum(switch(extraid='30',hoursworked,extraid<>'30',0)) as h30
,sum(switch(extraid='40',hoursworked,extraid<>'40',0)) as h40
,sum(switch(extraid='60',hoursworked,extraid<>'60',0)) as h60
,sum(switch(extraid='70',hoursworked,extraid<>'70',0)) as h70
,sum(hoursworked) as htotal
from misquery
where nationality IN ('W','E')
group by format(wkending,'(mm)-mmm-YYYY')
) as x

This makes the month start on a wrong date instead of starting from last Friday of a previous month to the next last friday of the current month.
Please can someone help with the SQL statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 03:41:10

See here to get the last friday of a month
http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 03:45:43

[code]SELECT theCurr AS theDate,
dbo.fnGetNthWeekdayOfMonth(thePrev, 5, -1) AS FromDate,
dbo.fnGetNthWeekdayOfMonth(theCurr, 5, -1) AS ToDate
FROM (
SELECT DATEADD(MONTH, Number, '20081201') AS thePrev,
DATEADD(MONTH, Number, '20090101') AS theCurr
FROM master..spt_values
WHERE Number < 12
AND Type = 'P'
) AS d

theDate FromDate ToDate
2009-01-01 2008-12-26 2009-01-30
2009-02-01 2009-01-30 2009-02-27
2009-03-01 2009-02-27 2009-03-27
2009-04-01 2009-03-27 2009-04-24
2009-05-01 2009-04-24 2009-05-29
2009-06-01 2009-05-29 2009-06-26
2009-07-01 2009-06-26 2009-07-31
2009-08-01 2009-07-31 2009-08-28
2009-09-01 2009-08-28 2009-09-25
2009-10-01 2009-09-25 2009-10-30
2009-11-01 2009-10-30 2009-11-27
2009-12-01 2009-11-27 2009-12-25[/code]

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

Jossy
Starting Member

2 Posts

Posted - 2009-07-22 : 04:20:53

Hi Peso,
Thank you for your response.I am still having some error message as i am new to SQL.Please can we chat on yahoo messengner if it's ok by you.
Rgds
Josephine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources