August 8, 2016 at 7:21 am
Here is the solution I developed to my last post!
But where did my last post go?
PROCEDURE [dbo].[AllWorkedHoursByMonthCrosstab2]
@PPEndDate Date = '2015-12-28'
AS
--Declare necessary variables
DECLARE @PivotColumns AS NVARCHAR(MAX)
DECLARE @sqlquery AS NVARCHAR(MAX)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #TmpWorkedHours
(
HHArea Varchar(50),
MonthEnd DateTime,
TotWorkedHours Decimal
);
WITH WHList AS
(
SELECT TOP 1000 A.HHArea, WH.Weekending,
SUM(WH.BasicHours + WH.Training + WH.Other ) AS TotWHrs
FROM TblHHWorkedHours WH
Left Join TblStaff S
ON S.PersonnelNo = WH.PersonnelNo
Left Join TblHHArea A
ON WH.HHArea= A.ID
WHERE WH.Weekending Between @PPEndDate and DATEADD(Year,1,@PPEndDate)
GROUP BY A.HHArea, WH.Weekending
ORDER By A.HHArea, WH.Weekending
),
Tots AS(
SELECT TOP 1000
-- Add a row index
ROW_NUMBER() OVER (ORDER BY HHArea)AS RowNo,
HHArea, WeekEnding, TotWHrs
FROM WHList
ORDER By HHArea ,Weekending
),
AllData AS
(
SELECT TOP 1000 *
--creates unique int for each 4 rows!
,(FLOOR((Row_Number() OVER (ORDER BY RowNo)-1)/4)) AS xRank
FROM Tots
ORDER BY HHArea , WeekEnding
),
FinalSet AS(
SELECT HHArea, MAX(WeekEnding) AS MonthEnd,
SUM(TotWHrs) AS TotWorkedHours, SUM(Travel) AS [Travel (Km)]
FROM AllData
GROUP BY HHArea, xRank
)
INSERT INTO #TmpWorkedHours
SELECT HHArea,
DATEADD(dd, DATEDIFF(dd, 0, MonthEnd ), 0)
, TotWorkedHours
FROM FinalSet
-- Now do Pivot
--Get unique values of pivot column
SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(MonthEnd)
FROM (SELECT DISTINCT MonthEnd
FROM #TmpWorkedHours) AS PivotData
--Create the Dynamic Query with all the values for
--Pivot column at runtime
SET @sqlquery = N'SELECT HHArea, ' + @PivotColumns + '
FROM #TmpWorkedHours
PIVOT(SUM(TotWorkedHours)
FOR MONTHEND IN (' + @PivotColumns + ')) AS P'
SELECT @sqlquery
--Execute dynamic query
EXEC sp_executesql @sqlquery
If(OBJECT_ID('#TmpWorkedHours') Is Not Null)
Begin
Drop Table #TmpWorkedHours
End
END
August 8, 2016 at 9:22 am
can you please repost the original question....i was looking at this, and I cant find it either !
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 8, 2016 at 11:56 am
I can't either. Glad you finally got it to work.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 8, 2016 at 12:57 pm
Hi,
My original question was along the lines of I have a table that has three columns
Table Sales (HHArea, WeekEnding, SalesAmount)
There are 6 area's with 52 weeks of sales for each area thats a total of 312 rows off data max.
Starting at the start of the I order by Area and WeekEnding, I wan to sum each area first four week into a month so I will have 13 month per area, that i then wanted to turn into a pivot table.
Which the solution does.
kind regards
August 8, 2016 at 12:58 pm
Hi,
My original question was along the lines of I have a table that has three columns
Table Sales (HHArea, WeekEnding, SalesAmount)
There are 6 area's with 52 weeks of sales for each area thats a total of 312 rows off data max.
Starting at the start of the I order by Area and WeekEnding, I wan to sum each area first four week into a month so I will have 13 month per area, that i then wanted to turn into a pivot table.
Which the solution does.
kind regards
August 8, 2016 at 1:55 pm
I'm putting this example of an alternative in for reference by future readers. It uses a WeekNo instead of your week ending date, but the logic is the same. It requires no row number function. If you need to use Dynamic SQL to add specific dates for your column headers, just replace the 'as Period' strings to correspond to the 4 week period ending date.
declare @Areas tinyint = 6
declare @Weeks tinyint= 52
declare @MaxAmt int = 2500
-- create sample table (#data)
;with tally (N) as (select top(1000) row_number() over(order by (select null)) from sys.columns)
select ABS(CHECKSUM(NEWID())) % @Areas + 1 AS AreaNo
,ABS(CHECKSUM(NEWID())) % @Weeks + 1 as WeekNo
,ABS(CHECKSUM(NEWID())) % @MaxAmt + 1 as Amt
into #data
from tally
-- query to solve the problem follows
;with cte as (select AreaNo,(WeekNo-1)/4+1 as Week4, count(*) as TotalRows, sum(Amt) as TotalAmt
from #data
group by AreaNo,(WeekNo-1)/4+1
)
select AreaNo as Area
, max(Case when Week4 = 1 then TotalAmt else null end) as Period1
, max(case when week4 = 2 then TotalAmt else null end) as Period2
, max(case when week4 = 3 then TotalAmt else null end) as Period3
, max(case when week4 = 4 then TotalAmt else null end) as Period4
, max(case when week4 = 5 then TotalAmt else null end) as Period5
, max(case when week4 = 6 then TotalAmt else null end) as Period6
, max(case when week4 = 7 then TotalAmt else null end) as Period7
, max(case when week4 = 8 then TotalAmt else null end) as Period8
, max(case when week4 = 09 then TotalAmt else null end) as Period9
, max(case when week4 = 10 then TotalAmt else null end) as Period10
, max(case when week4 = 11 then TotalAmt else null end) as Period11
, max(case when week4 = 12 then TotalAmt else null end) as Period12
, max(case when week4 = 13 then TotalAmt else null end) as Period13
from cte
group by AreaNo
order by AreaNo
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 9, 2016 at 2:15 am
Hi Dixie,
Thanks for that, I am sure there are a number of alternative ways to solve the problem and I am always interested in looking and understanding other ways.
I was also extending my pivot example to add a Totals row at the bottom by adding a UNION ALL to the sql string and adding another select statement to it.
If anyone else has found an alternative to this please advise.
kind regards
August 9, 2016 at 11:25 am
-- query to solve the problem follows
;with cte as (select AreaNo,(WeekNo-1)/4+1 as Week4, count(*) as TotalRows, sum(Amt) as TotalAmt
from #data
group by AreaNo,(WeekNo-1)/4+1
)
-- the added ROLLUP is going to produce a totals row with a null AreaNo value
select isnull(convert(char(5),AreaNo),'Total') as Area
, max(Case when Week4 = 1 then TotalAmt else null end) as Period1
, max(case when week4 = 2 then TotalAmt else null end) as Period2
, max(case when week4 = 3 then TotalAmt else null end) as Period3
, max(case when week4 = 4 then TotalAmt else null end) as Period4
, max(case when week4 = 5 then TotalAmt else null end) as Period5
, max(case when week4 = 6 then TotalAmt else null end) as Period6
, max(case when week4 = 7 then TotalAmt else null end) as Period7
, max(case when week4 = 8 then TotalAmt else null end) as Period8
, max(case when week4 = 09 then TotalAmt else null end) as Period9
, max(case when week4 = 10 then TotalAmt else null end) as Period10
, max(case when week4 = 11 then TotalAmt else null end) as Period11
, max(case when week4 = 12 then TotalAmt else null end) as Period12
, max(case when week4 = 13 then TotalAmt else null end) as Period13
from cte
group by AreaNo WITH ROLLUP -- creates a grand totals row
order by case when areano is null then 1 else 0 end -- this forces the grand totals row to appear at the bottom
, AreaNo
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply