divide hours in weeks for fiscal calendar.

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

divide hours in weeks for fiscal calendar.

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-18 : 08:57:39

Hi,
I want to divide the Hours in weeks of the month depending on the dates.

I have the code below, which works for Calendar Year, I want it for Fiscal Year.

eg:- For fiscal Year,
1st week of Aug'2012 doesnt start from 1 Aug , but 28th July '2012.

so 28th July'2012 to 03 Aug 2012 will be Week 1
04th Augu'2012 to 10 Aug 2012 will be Week 2
11th Augu'2012 to 17 Aug 2012 will be Week 3
18th Augu'2012 to 24 Aug 2012 will be Week 4
25th Augu'2012 to 31 Aug 2012 will be Week 5

Working query is

DECLARE @Table TABLE
(
name varchar(100),
date datetime,
hours int
)

INSERT @Table
SELECT 'a','07/31/2012',2 union ALL
SELECT 'b','08/11/2012',3 union ALL
SELECT 'c','08/18/2012',4 union ALL
SELECT 'd','08/30/2012',5 union ALL
SELECT 'e','09/25/2012',6

Select Name, date,
IsNull([1],0) as 'Wk1',
IsNull([2],0) as 'Wk2',
IsNull([3],0) as 'Wk3',
IsNull([4],0) as 'Wk4',
IsNull([5], 0) as 'Wk5'
From
(
Select Name, date,
DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 1), Date)+1 as [Weeks],
hours as 'hours'
From @Table
Where DatePart(Month, Date)= DatePart(Month, Date)
)p
Pivot (Sum(hours) for Weeks in ([1],[2],[3],[4],[5])) as pv

In result for name=a it should appear in Wk1 instead of Wk5 in Fiscal Year Query.

Any help would be highly appreciated.
Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-18 : 09:23:57

What determines the start of the fiscal calendar? Is there a rule that you can use to determine that it should start on July 28th in 2012, or is that just something that "they" decided? Assuming the latter, can you just declare that and use it as start of the year - for example, like this:


DECLARE @fiscalStart date = '20120728';

SELECT NAME,
       date,
       ISNULL([1], 0) AS 'Wk1',
       ISNULL([2], 0) AS 'Wk2',
       ISNULL([3], 0) AS 'Wk3',
       ISNULL([4], 0) AS 'Wk4',
       ISNULL([5], 0) AS 'Wk5'
FROM   (
           SELECT NAME,
                  date,
                  DATEDIFF(dd,@FiscalStart,Date)/7+1  [Weeks],
                  --DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 1), Date) + 1 AS [Weeks],
                  hours AS 'hours'
           FROM   @Table
           WHERE  DATEPART(MONTH, Date) = DATEPART(MONTH, Date)
       )p 
       PIVOT(SUM(hours) FOR Weeks IN ([1], [2], [3], [4], [5])) AS pv

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-18 : 09:41:51

quote:
Originally posted by sunitabeck

What determines the start of the fiscal calendar? Is there a rule that you can use to determine that it should start on July 28th in 2012, or is that just something that "they" decided? Assuming the latter, can you just declare that and use it as start of the year - for example, like this:[code[/code]

Hi Sunita,

In Fiscal Year ,The Lastdate of every Month is the last last Friday of that month.
so the July has Last Friday on 27th July 2012 , Hence 28th is first day of August 2012.

Similary 31 Aug 2012 is last friday, so 1st Sep 2012 is the start date of September 2012 and 28th Sep 2012 is last day.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-18 : 14:13:29

The query below should let you calculate the start date - this way you wouldn't need to hard code it. The @date is any date in the month that you are interested in.

DECLARE @date date = '20120901';
-- start of fiscal month in which @date is
SELECT DATEADD(
           dd,
           1-DATEDIFF(dd, 4, DATEADD(mm,DATEDIFF(mm,0,@date),-1))%7,
           DATEADD(mm,DATEDIFF(mm,0,@date),-1)
)

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-18 : 20:52:55

I have calendar table in database

Month Startdate Enddate
1 1/1/2012 1/27/2012
2 1/28/2012 2/24/2012
3 2/25/2012 3/30/2012
4 3/31/2012 4/27/2012
5 4/28/2012 5/25/2012
6 5/26/2012 6/29/2012
7 6/30/2012 7/27/2012
8 7/28/2012 8/31/2012
9 9/1/2012 9/28/2012
10 9/29/2012 10/26/2012
11 10/27/2012 11/30/2012
12 12/1/2012 12/31/2012

now instead of below o/p

Name date Wk1 Wk2 Wk3 Wk4 Wk5
a 2012-07-31 0 0 0 0 2
b 2012-08-11 0 3 0 0 0
c 2012-08-18 0 0 4 0 0
d 2012-08-30 0 0 0 0 5
e 2012-09-25 0 0 0 6 0

should be

Name date Wk1 Wk2 Wk3 Wk4 Wk5
a 2012-07-31 2 0 0 0 0
b 2012-08-11 0 3 0 0 0
c 2012-08-18 0 0 4 0 0
d 2012-08-30 0 0 0 0 5
e 2012-09-25 0 0 0 6 0

as the 2012-07-31 is in first week of the Month Aug-2012.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 12:02:50

do you've fiscal week column in your calendar table?

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

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-20 : 01:44:24

No, I dont have fiscal week columns.
I believe Thats what making it complicate .

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-20 : 01:54:15

Here is query I have to find the week startdate and weekenddate.

DECLARE @dd datetime
SET @dd='08-01-2012'

SELECT datepart(weekday,@dd),
Case when datepart(weekday,@dd) = 7
then dateadd(day,-1*(datepart(weekday,@dd)-7),@dd)
else dateadd(day,-1*(datepart(weekday,@dd)),@dd)
End as WeekStartDate,
Case when datepart(weekday,@dd) = 7
then dateadd(day,-1*(datepart(weekday,@dd)-13),@dd)
else dateadd(day,-1*(datepart(weekday,@dd)-6),@dd)
End as WeekEndDate

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-09-20 : 05:20:47

Hi

I am able to modify for ,but 4th row in output is not proper

Current working query is,

DECLARE @Table TABLE
(
name varchar(100),
date datetime,
hours int
)

INSERT @Table
SELECT 'a','07/31/2012',2 union ALL
SELECT 'b','08/11/2012',3 union ALL
SELECT 'c','08/18/2012',4 union ALL
SELECT 'd','08/30/2012',5 union ALL
SELECT 'e','09/28/2012',6

Select Name, date,
IsNull([1],0) as 'Wk1',
IsNull([2],0) as 'Wk2',
IsNull([3],0) as 'Wk3',
IsNull([4],0) as 'Wk4',
IsNull([5], 0) as 'Wk5'
From
(
SELECT SL.Name,SL.date,
CASE WHEN SL.weeknum=12 THEN 5 ELSE Weeknum%4+1 END AS Weeks ,
hours as 'hours'
FROM
(select Name,date,datediff(day,dateadd(year,datediff(year,0,Date),0),Date) / 7 %13 as weeknum
,hours as 'hours'
FROM @table
)SL
)p
Pivot (Sum(hours) for Weeks in ([1],[2],[3],[4],[5])) as pv

Name date Wk1 Wk2 Wk3 Wk4 Wk5
d 2012-08-30 5 0 0 0 0

should be
Name date Wk1 Wk2 Wk3 Wk4 Wk5
d 2012-08-30 0 0 0 0 5

Financial Month EndDate is the Last Friday of the Month.
Here 2012-08-31 is last Friday, so 2012-08-30 should appear in WK5.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources