March 13, 2008 at 9:18 am
This report is based on the week
the code that i have so far will generate starting from Wk4 but overlaps ....
I would like to see based on the script & result is :
The Group 1 : IstDate Shiped is 4 ...
So it Start the week on 4 --- wk4....wk11, wk1, wk2, wk3..wk11
with the values basde on the DateRec and Cases for the Week of DateRec
Then Group 2: IstDate Shiped is 6 So it Start the week on 6 --- wk7....wk11, wk1, wk2, wk3..wk11 (no value should appear in the wk4 - Wk5 .. it should look like a rainfall pattern)
Then Group 3: IstDate Shiped is 10
So it Start the week on 10 --- wk10....wk11, wk1, wk2, wk3..wk11
==================================================
Creating a table
-----------------------
CREATE TABLE
[dbo].[OrdersShipped] (
[Group] [char] (10) NULL ,
[OrderId] [int] NULL ,
[OrderDesc] [char] (10) NULL ,
[DateRec] [datetime] NULL ,
[IstDate Shiped] [datetime] NULL ,
[Cases] [int] NULL
)
inserting values to the table
--------------------------------
INSERT INTO OrdersShipped
([Group], OrderId, OrderDesc, DateRec, [IstDate Shiped], Cases)
select '1','123','aaaa','2006-01-01',' 2008-01-25','150'
union all select '1','123','aaaa','2007-01-26','2008-01-25','200'
union all select '1','123','aaaa','2007-02-01','2008-01-25','235'
union all select '1','224','qqqq','2007-02-08','2008-01-25','200'
union all select '1','224','qqqq','2007-02-15','2008-01-25','195'
union all select '2','333','www','2007-02-01','2008-02-08','200'
union all select '2','333','www','2007-02-15','2008-02-08','898'
union all select '3','999','oooo','2007-02-01','2008-03-05','750'
union all select '3','888','mmm','2007-01-26','2008-03-05','5842'
union all select '3','999','oooo','2007-03-12','2008-03-05','685'
Function to create weeks
------------------------------------
CREATE FUNCTION ISOweek (@DATE datetime )
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
--set @DATE = (SELECT dt FROM vCurrentDateTime)
SET @ISOweek= DATEPART(wk,@DATE)+1-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
Calculating the weeks for DateRec & [IstDate Shiped]
----------------------------------------------------------
Select [Group],
[OrderId],
[OrderDesc],
[DateRec],
dbo.ISOweek([DateRec]) as WeekID,
[IstDate Shiped],
dbo.ISOweek([IstDate Shiped]) as WeekID,
[Cases]
from OrdersShipped
The script I have so far to produce the week Report
--------------------------------------------------------
DECLARE @sql varchar(8000), @Shipped_Week_ID int, @Min_Week_ID int, @Max_Week_ID int, @counter int, @wk int, @Min_DateRec datetime
SELECT @Shipped_Week_ID = Min(dbo.ISOweek([IstDate Shiped]))
FROM OrdersShipped1
SELECT @Min_Week_ID = MIN(dbo.ISOweek(DateRec))
FROM OrdersShipped1
SELECT @Max_Week_ID = MAX(dbo.ISOweek(DateRec))
FROM OrdersShipped1
SELECT @Min_DateRec = MIN(DateRec)
FROM OrdersShipped1
SET @counter = @Min_Week_ID
SELECT @sql =
'SELECT
[Group],
OrderId,
OrderDesc,
DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), [IstDate Shiped])) AS Shipped_Week_ID,
'
WHILE @counter <= @Max_Week_ID
BEGIN
SELECT @wk = CASE WHEN ((@counter + @shipped_week_id -1) % (@Max_Week_ID)) = 0 THEN @Max_Week_ID
ELSE ((@counter + @shipped_week_id -1) % (@Max_Week_ID)) END
SELECT @sql = @sql +
'Sum(CASE WHEN DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), DateRec)) = ' + CONVERT(varchar, @wk) + ' THEN Cases ELSE 0 END) AS [Wk' + CONVERT(varchar, @wk) + ' - ' + CONVERT(varchar, DATEADD(week, DATEDIFF(week, 5, @Min_DateRec) + (@wk - @Min_Week_ID), 5), 101) + ']
,'
SET @counter = @counter +1
END
SELECT @sql = STUFF(@sql, LEN(@sql), 1, '')
SELECT @sql = @sql + ' FROM OrdersShipped1
group by [Group],
OrderId,
OrderDesc, [IstDate Shiped]'
EXEC (@sql)
Open in New Window
March 13, 2008 at 12:32 pm
Unless I am missing something about the logical organization of the data, it seems to me that the problem stems from the inability to distinguish which eleven-week group each Wk# resides within.
March 13, 2008 at 2:46 pm
The Excel Sheet will give u an idea as how to place the out put...
The Shipped_Week_ID for Group 1 is 4
the Week of DateRec checks the Shipped_Week_ID for that group , if Week of DateRec >= Shipped_Week_ID then show the cases for that date - WK4
So the Data is from Wk4 - Wk11 is shown if there is a corresponding the cases for the DateRec and then the remaining weeks = Wk1 - Wk3 is displayed...
Same in the same of Group 2 which is 6
Here is the trick
There should be no data in WK4 when group 2 starts ,it should only start from Wk6 if there is cases for that week.
In Group 2 starting from Wk6 ... WK11 then wk1 - WK5
and so on...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply