July 7, 2008 at 10:02 pm
Hi folks
wondering if someone could help me with the following issue in creating the sql
i have a table called 'TimesheetDetails' with columns 'TimesheetID' 'Shiftdate' 'Totalhours'
workers daily timesheet info is entered daily, i'm trying to create a View on this table that will provide me with a cross tab or pivot like grid showing:
July to June months as columns (as thats our fiscal year period) and Current year, Last year as rows
dates are entered as dd/mm/yyyy, if anyone can help its much appreciated.
regards
jerry
July 8, 2008 at 7:45 am
[font="Verdana"]SQL 2005 has PIVOT and UNPIVOT operators. For more information refer below file.
Mahesh[/font]
MH-09-AM-8694
July 8, 2008 at 3:44 pm
jerrym (7/7/2008)
Hi folkswondering if someone could help me with the following issue in creating the sql
i have a table called 'TimesheetDetails' with columns 'TimesheetID' 'Shiftdate' 'Totalhours'
workers daily timesheet info is entered daily, i'm trying to create a View on this table that will provide me with a cross tab or pivot like grid showing:
July to June months as columns (as thats our fiscal year period) and Current year, Last year as rows
dates are entered as dd/mm/yyyy, if anyone can help its much appreciated.
regards
jerry
you could use pivot as suggested earlier, but you may find a grouped query easier. this should get you started:
declare @TimesheetDetails table ( ShiftDate smalldatetime, TotalHours int )
insert into @TimesheetDetails
select '12/1/2007', 10
union
select '1/1/2007', 10
union
select '7/5/2008', 10
union
select '6/1/2008', 10
union
select '6/15/2008', 10
union
select '8/1/2007', 10
union
select '1/1/2008', 10
select
year(dateadd(month,-1,ShiftDate)) as fiscal_year,
sum(case when month(ShiftDate) = 7 then TotalHours end) as JulHours,
sum(case when month(ShiftDate) = 8 then TotalHours end) as AugHours,
--...
sum(case when month(ShiftDate) = 12 then TotalHours end) as DecHours,
--...
sum(case when month(ShiftDate) = 1 then TotalHours end) as JanHours,
--...
sum(case when month(ShiftDate) = 6 then TotalHours end) as JunHours
from @TimesheetDetails
group by year(dateadd(month,-1,ShiftDate))
July 9, 2008 at 12:06 am
Hi, thank you for your replies.
i tried antonio's grouped query suggestion but i rec'd an error 'invalid column name TotalHours'
this is what i did, i created a new View in design mode and pasted this code below:
===
SELECT YEAR(DATEADD(month, - 1, ShiftDate)) AS fiscal_year, SUM(CASE WHEN month(ShiftDate) = 7 THEN TotalHours END) AS JulHours,
SUM(CASE WHEN month(ShiftDate) = 8 THEN TotalHours END) AS AugHours, SUM(CASE WHEN month(ShiftDate) = 9 THEN TotalHours END)
AS SepHours, SUM(CASE WHEN month(ShiftDate) = 10 THEN TotalHours END) AS OctHours, SUM(CASE WHEN month(ShiftDate)
= 11 THEN TotalHours END) AS NovHours, SUM(CASE WHEN month(ShiftDate) = 12 THEN TotalHours END) AS DecHours,
SUM(CASE WHEN month(ShiftDate) = 1 THEN TotalHours END) AS JanHours, SUM(CASE WHEN month(ShiftDate) = 2 THEN TotalHours END)
AS FebHours, SUM(CASE WHEN month(ShiftDate) = 3 THEN TotalHours END) AS MarHours, SUM(CASE WHEN month(ShiftDate)
= 4 THEN TotalHours END) AS AprHours, SUM(CASE WHEN month(ShiftDate) = 5 THEN TotalHours END) AS MayHours,
SUM(CASE WHEN month(ShiftDate) = 6 THEN TotalHours END) AS JunHours
FROM dbo.TimesheetDetails
GROUP BY YEAR(DATEADD(month, - 1, ShiftDate))
===
could anyone point out where i went wrong here. thanks again.
July 9, 2008 at 7:12 am
Your syntax appears fine. Double check the database that your running it on (or add DATABASENAME.dbo.TABLENAME), the name of the fields, and the name of the table.
July 9, 2008 at 9:48 am
A column named TotalHours is not present in your dbo.TimesheetDetails table. Change TotalHours to the appropriate column.
July 9, 2008 at 4:28 pm
Hi folks
thank you for your patience, i've managed to run the code now without any errors. the result set returned by the grouped query is:
In columns > fiscal_year then Julhours to JunHours (this is great!)
BUT in rows > i've got 2006, 2007, 2008
because our fiscal year is between jul to jun, each fiscal year has 2 different calendar years. is there a way where i can filter the data then group it by say 'current_year' & 'last_year' ??
thanking you guys in advance.
Jerry
July 10, 2008 at 2:56 pm
the code i gave you should produce the correct fiscal year number. all you need to do is qualify the query with the start of last year's fiscal year:
Shiftdate >= '06/01/2007' for fiscal years 2007 and 2008
Shiftdate between '06/01/2006' and '05/31/2007' for fiscal years 2006 and 2007
but i wouldn't replace the fiscal year with a 'current year', 'prior year' label in a view since it would become impossible to get two years of useful data at the beginning of july (fy 2008 would be empty and only fy 2007 would have data).
July 10, 2008 at 11:37 pm
Hi Antonio
this is an amended code from what you suggested.
===
SELECT YEAR(DATEADD(month, - 1, ShiftDate)) AS fiscal_year, SUM(CASE WHEN month(ShiftDate) = 7 THEN TotalHrs END) AS Jul,
SUM(CASE WHEN month(ShiftDate) = 8 THEN TotalHrs END) AS Aug, SUM(CASE WHEN month(ShiftDate) = 9 THEN TotalHrs END) AS Sep,
SUM(CASE WHEN month(ShiftDate) = 10 THEN TotalHrs END) AS Oct, SUM(CASE WHEN month(ShiftDate) = 11 THEN TotalHrs END) AS Nov,
SUM(CASE WHEN month(ShiftDate) = 12 THEN TotalHrs END) AS Dec, SUM(CASE WHEN month(ShiftDate) = 1 THEN TotalHrs END) AS Jan,
SUM(CASE WHEN month(ShiftDate) = 2 THEN TotalHrs END) AS Feb, SUM(CASE WHEN month(ShiftDate) = 3 THEN TotalHrs END) AS Mar,
SUM(CASE WHEN month(ShiftDate) = 4 THEN TotalHrs END) AS Apr, SUM(CASE WHEN month(ShiftDate) = 5 THEN TotalHrs END) AS May,
SUM(CASE WHEN month(ShiftDate) = 6 THEN TotalHrs END) AS Jun
FROM dbo.TimesheetDetails
WHERE (ShiftDate >= (CASE WHEN Month(CURRENT_TIMESTAMP) >= 7 THEN CAST(YEAR(CURRENT_TIMESTAMP) - 1 AS CHAR(4))
+ '0701' WHEN Month(CURRENT_TIMESTAMP) < 7 THEN CAST(YEAR(CURRENT_TIMESTAMP) - 2 AS CHAR(4)) + '0701' END))
GROUP BY YEAR(DATEADD(month, - 1, ShiftDate))
===
the result set i'm getting is;
in row 1 2007
in row 2 2008
and i have columns from jul to jun
BUT, Jan to Jun 2008 figures are located in row 2 INSTEAD of row 1
i'm attempting to setup this View so that No hard coded parameters need to be inputted. ie if the View is run in July2008 then i would like the View to return;
all data for fiscal year 2007-08 AND 2008-09 in columns Jul to Jun
hope you can help as i'm not a trained programmer.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply