December 5, 2007 at 11:26 am
Hi All,
The current/ Base table would be like below,
ProductslevelDate
N1b11/5/2007
N2p11/6/2007
N3p11/7/2007
N4p11/14/2007
N5b11/15/2007
N6p11/23/2007
Expected Result.
<=11/7/2007<= 11/14/2007<=11/21/2007
b112
p234
Total346
As you can see, the above table has cumulative data.
1.It calculates the number of Products submitted till a particular date- weekly
2.The date columns should increase dynamically(if the dates in base table increases) each time the query is executed
For ex: the next date would be 11/28/2007
I tried something like, it gives me count of ‘b’ level and ‘p’ level products by week
declare @date1 as datetime
select @date1 = '6/30/2007'
while (@date1 != (select max(SDate) from dbo.TrendTable))
begin
set @date1 = @date1 + 7
select Level, count(Products)
from
dbo.TrendTable
where SDate < @date1
group by Level
end
what I want is a pivot that dynamically adds the columns for increase in date range.
Not sure how to do that. Pls help!!!
Alicia Rose
December 5, 2007 at 9:41 pm
This should give you what you need. You first have to create a function to get the begining of the week. By default Microsoft sets this value to a Sunday, so the function will return the value we need.
CREATE FUNCTION [dbo].[firstOfWeek]
(@DATE DATETIME
,@WEEK_START_DAY INT = 1)
RETURNS DATETIME
/* @date = date to calculate
@WEEK_START_DAY = the dayNumber of the first day of the week
Sun = 1, Mon = 2, Tue = 3, Wed = 4
Thu = 5, Fri = 6, Sat = 7
Default to Sunday
*/
AS
BEGIN
DECLARE @START_OF_WEEK_DATE DATETIME
-- Check for valid day of week
IF @WEEK_START_DAY between 1 and 7
BEGIN
SET
@START_OF_WEEK_DATE =
CASE WHEN @WEEK_START_DAY - datepart(dw,@DATE) > 0 THEN
dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE) - 7, @DATE)
ELSE
dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE), @DATE)
END
END
RETURN @START_OF_WEEK_DATE
END
Now execute the following code:
--===============================================
-- CREATE TABLE TO HOUSE TEMP PRODUCT INFO
--===============================================
DECLARE @Products TABLE
(
Products VARCHAR(10),
level CHAR(1),
Dt DATETIME
)
--===============================================
-- POPULATE DATA
--===============================================
INSERT INTO @Products
SELECT 'N1', 'b', '11/5/2007' UNION ALL
SELECT 'N2', 'p', '11/6/2007' UNION ALL
SELECT 'N3', 'p', '11/7/2007' UNION ALL
SELECT 'N4', 'p', '11/14/2007' UNION ALL
SELECT 'N5', 'b', '11/15/2007' UNION ALL
SELECT 'N6', 'p', '11/23/2007'
--===============================================
-- GET PRODUCT INFORMATION GROUPED BY WEEK AND
-- SAVE INTO TEMP TABLE
--===============================================
SELECT [WeekNbr] = CONVERT(VARCHAR,[dbo].[firstOfWeek](Dt,4),101),
Level, COUNT(Products) AS [Total]
INTO #tmp
FROM @Products
GROUP BY Level, CONVERT(VARCHAR,[dbo].[firstOfWeek](Dt,4),101)
--===============================================
-- PIVOT THE TEMP DATA AND HOUSE IN TEMP TABLE
--===============================================
SELECT * INTO #tmp2
FROM #tmp
PIVOT
(
SUM(Total)
FOR [Level]
IN (,[p])
) AS p
--===============================================
-- AGGREGATE THE FINAL DATA
--===============================================
SELECT WeekNbr AS BegOfWeek,
(SELECT SUM(b) FROM #tmp2 WHERE WeekNbr <= a.WeekNbr) AS ,
(SELECT SUM(p) FROM #tmp2 where WeekNbr <= a.WeekNbr) as [p],
(SELECT SUM(p) + SUM(b) FROM #tmp2 WHERE WeekNbr <= a.WeekNbr) AS [Total]
FROM #tmp2 a
--===============================================
-- CLEAN UP TABLES
--===============================================
DROP TABLE #tmp
DROP TABLE #tmp2
December 7, 2007 at 11:46 am
Hi Adam,
Thanks for the great script.
I did not think about breaking down the whole poblem and solving in different steps:)
The code works fine, except when i have multiple years in the date column,
the weeks are not sorted in ascending order.They get scattered, so i tried this in last step
convert(DATETIME, WeekNbr, 101)' and order by the same
But i want only the date part from that fo ex: instead of 2007-11-28 00:00:00.000, i need just 2007-11-28 .
any pointers/suggestions would be highly appreciated
Thanks,
Alicia Rose
December 7, 2007 at 10:11 pm
Put this in the ORDER BY clause. You dont have to add any additional columns as the third step does not use a group by.
ORDER BY CONVERT(VARCHAR,CONVERT(DATETIME, WeekNbr, 101),101)
December 7, 2007 at 11:09 pm
Are you trying to get the totals for a year at a time or would you like to see a aggregate total of everything prior to that year? For example, if a date of 'b', 11/4/2005 and a date p, 11/5/2006 existed would these be aggregated?
The below script aggregates everything that has a week less than the date and the previous years.
--===============================================
-- CREATE TABLE TO HOUSE TEMP PRODUCT INFO
--===============================================
DECLARE @Products TABLE
(
Products VARCHAR(10),
level CHAR(1),
Dt DATETIME
)
--===============================================
-- POPULATE DATA
--===============================================
INSERT INTO @Products
SELECT 'N1', 'b', '11/5/2006' UNION ALL
SELECT 'N1', 'p', '09/5/2005' UNION ALL
SELECT 'N1', 'b', '11/5/2007' UNION ALL
SELECT 'N2', 'p', '11/6/2007' UNION ALL
SELECT 'N3', 'p', '11/7/2007' UNION ALL
SELECT 'N4', 'p', '11/14/2007' UNION ALL
SELECT 'N5', 'b', '11/15/2007' UNION ALL
SELECT 'N6', 'p', '11/23/2007'
--===============================================
-- GET PRODUCT INFORMATION GROUPED BY WEEK AND
-- SAVE INTO TEMP TABLE
--===============================================
SELECT DATEPART(YYYY,Dt) as [YEAR],[WeekNbr] = CONVERT(VARCHAR,[dbo].[firstOfWeek](Dt,4),101),
Level, COUNT(Products) AS [Total]
INTO #tmp
FROM @Products
GROUP BY Level, CONVERT(VARCHAR,[dbo].[firstOfWeek](Dt,4),101),DATEPART(YYYY,Dt)
ORDER BY DATEPART(YYYY,Dt) ASC, CONVERT(VARCHAR,[dbo].[firstOfWeek](Dt,4),101) ASC
--===============================================
-- PIVOT THE TEMP DATA AND HOUSE IN TEMP TABLE
--===============================================
SELECT * INTO #tmp2
FROM #tmp
PIVOT
(
SUM(Total)
FOR [Level]
IN (,[p])
) AS p
ORDER BY Year ASC, WeekNbr ASC
--===============================================
-- AGGREGATE THE FINAL DATA
--===============================================
SELECT WeekNbr AS BegOfWeek,
(SELECT ISNULL(SUM(b),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year OR [Year] < a.Year) AS ,
(SELECT ISNULL(SUM(p),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year OR [Year] < a.Year) as [p],
(SELECT ISNULL(SUM(p),0) + ISNULL(SUM(b),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year OR [Year] < a.Year) AS [Total]
FROM #tmp2 a
ORDER BY [YEAR] ASC, WeekNbr ASC
--===============================================
-- CLEAN UP TABLES
--===============================================
DROP TABLE #tmp
DROP TABLE #tmp2
Note: that if you want info pertaining to one year a time then you should change this:
SELECT WeekNbr AS BegOfWeek,
(SELECT ISNULL(SUM(b),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year OR [Year] < a.Year) AS ,
(SELECT ISNULL(SUM(p),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year OR [Year] < a.Year) as [p],
(SELECT ISNULL(SUM(p),0) + ISNULL(SUM(b),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year OR [Year] < a.Year) AS [Total]
FROM #tmp2 a
ORDER BY [YEAR] ASC, WeekNbr ASC
To:
SELECT WeekNbr AS BegOfWeek,
(SELECT ISNULL(SUM(b),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year) AS ,
(SELECT ISNULL(SUM(p),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year) as [p],
(SELECT ISNULL(SUM(p),0) + ISNULL(SUM(b),0)
FROM #tmp2
WHERE WeekNbr <= a.WeekNbr AND [Year] = a.Year) AS [Total]
FROM #tmp2 a
ORDER BY [YEAR] ASC, WeekNbr ASC
December 19, 2007 at 9:02 am
Hi Adam,
Thanks a ton for the code.
The code works fantastic. I have used the same for monthly
reports as well.
Regards,
Alicia Rose
December 19, 2007 at 12:37 pm
Thanks for the feedback!! I am glad everything is working 😀
December 20, 2007 at 8:38 am
Wow! This is great!
I've been looking for a similar solution for quite a while. I was starting to think it wasn't possible.
Thanks to all! 😀
July 24, 2008 at 9:04 pm
Thanks for the code. I've been looking for this for a long time. I'd lost hope lol.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply