September 24, 2010 at 6:07 am
Hello Experts,
I am trying to get weekly inventory values from my db table named LookupInv.
This table consists the exact inventory values for styles as DAILY basis . But I want get these items' inventory values as weekly period.
select DATEPART(WEEK,CAST(MAX(DateID) as CHAR(8))),DATEPART(YEAR,CAST(MAX(DateID) as CHAR(8))),StyleCode,WarehouseID,Quantity from LookupInv
group by StyleCode,WarehouseID,Quantity
order by 1,2,3
This query does not suffer my needs because my desired resultset should be like this:
Week Year StyleCode WarehouseID Quantity
33 2010 S10045460007143 1001 6
33 2010 S10045460007143 1002 6
33 2010 S10045460007143 1003 27
I have attached some sample data and LookupInv table script.
CREATE TABLE LookupInv
(
DateID int,
StyleCode varchar(23),
WarehouseID varchar(max),
Quantity int
)
INSERT INTO LookupInv VALUES ('20100809','S10045460007143','1002',7)
GO
INSERT INTO LookupInv VALUES ('20100812','S10045460007143','1002',6)
GO
INSERT INTO LookupInv VALUES ('20100809','S10045460007143','1003',28)
GO
INSERT INTO LookupInv VALUES ('20100814','S10045460007143','1001',6)
GO
INSERT INTO LookupInv VALUES ('20100810','S10045460007143','1003',27)
GO
Regards
September 24, 2010 at 6:35 am
Don't you want to sum the quantity over each partition?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 24, 2010 at 6:43 am
Thanks for your reply.
Actually, I want to retrieve the MAX(InvDate), Quantity combination of each item. But as you considered, this combination should be in weekly basis.
If you select LookupInv table than you would see the following result:
DateID StyleCode WarehouseID Quantity
----------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
20100809 S10045460007143 1002 7
20100812 S10045460007143 1002 6
20100809 S10045460007143 1003 28
20100814 S10045460007143 1001 6
20100810 S10045460007143 1003 27
(5 row(s) affected)
For example there are 2 quantity values for warehouseID '1002'. My desired Quantity column value should be 6. Because its dateID is greater than the other one.
September 24, 2010 at 6:44 am
I guess the quantity are the latest value, so you wouldn't want to sum it.
Try this:Select b.Week, b.year , a.StyleCode, a.WarehouseID, a.Quantity
From #LookupInv a
Join (
Select datepart(WEEK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) Week, datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) year, StyleCode, WarehouseID, MAX(DateID) DateID
From #LookupInv
Group by datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)), datepart(WEEK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)), StyleCode, WarehouseID
) b on b.DateID = a.DateID and b.StyleCode = a.StyleCode and b.WarehouseID = a.WarehouseID
Order by b.Week, b.year , a.StyleCode, a.WarehouseID
September 24, 2010 at 7:00 am
;with cte as (
select DATEPART(WEEK,CAST(DateID AS varchar)) as [week], -- why is your dateID stored as an int?
DATEPART(YEAR,CAST(DateID AS varchar)) as [year], -- best practice is to name columns
StyleCode,WarehouseID,Quantity,
rowNo = ROW_NUMBER () over (partition by Stylecode,Warehouseid order by DATEID desc)
from #LookupInv
)
select * from cte
where rowNo = 1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 24, 2010 at 7:09 am
DROP TABLE #LookupInv
CREATE TABLE #LookupInv
(
DateID int,
StyleCode varchar(23),
WarehouseID varchar(max),
Quantity int
)
INSERT INTO #LookupInv
VALUES
(20100802,'S10045460007143','1002',2),
(20100809,'S10045460007143','1002',7),
(20100812,'S10045460007143','1002',6),
(20100809,'S10045460007143','1003',28),
(20100814,'S10045460007143','1001',6),
(20100810,'S10045460007143','1003',27),
(20100819,'S10045460007143','1003',19)
;WITH SensibleDates AS(
SELECT
StyleCode,
WarehouseID,
Quantity,
InventoryDate = CONVERT(DATE,CAST(DateID AS CHAR(8)),112)
FROM #LookupInv
),
DataWithSensiblePartitions AS (
SELECT
MyRow = ROW_NUMBER() OVER(PARTITION BY YEAR(InventoryDate), DATEPART(wk,InventoryDate), WarehouseID, StyleCode ORDER BY InventoryDate DESC),
InventoryYear = YEAR(InventoryDate),
InventoryWeek = DATEPART(wk,InventoryDate),
InventoryDate,
WarehouseID,
StyleCode,
Quantity
FROM SensibleDates
)
SELECT
InventoryYear,
InventoryWeek,
WarehouseID,
StyleCode,
Quantity
FROM DataWithSensiblePartitions
WHERE MyRow = 1
ORDER BY InventoryYear, InventoryWeek, WarehouseID, StyleCode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 24, 2010 at 7:12 am
Thank you very much for your great comments. Problem solved with your helps!
I have used DateID column with int datatype. Because my purpose is to use this column as a Dimension keyID within my DW database table.
I think it is really a painful issue to adjust the granularity of these tables..
September 24, 2010 at 7:36 am
You're welcome. For what it's worth, you should test both of the solutions at production volumes. The ROWNUMBER() solution I gave you has to do a sort. The GROUP BY/JOIN solution has to make two passes through the source. There could be a significant performance difference as your volumes scale up.
__________________________________________________
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