Need Help - Insert Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help - Insert Statement

Author

Topic

DaNuGai
Starting Member

10 Posts

Posted - 2012-06-21 : 10:24:21

Suppose you have a table with the following columns

UserID|WeekID|ItemID|ItemQty|ItemYTDQty

I want to be able to write an insert statement so that each item is listed by user with appropriate quantity. See Images Below.

Original Data:

After Insert:

Also, if this user decides to get Item 5 starting week 6, then item 5 record should be added to each one of the weeks with appropriate quantity. So, for weeks 1-4, there should be a record for item 5 with ItemQty = 0 and ItemYTDQty = 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-21 : 10:55:40

[code]
; with
UserID as
(
select distinct UserID
from tbl
),
WeekID as
(
select distinct WeekID
from tbl
),
ItemID as
(
select distinct ItemID
from tbl
),
comb as
(
select UserID, WeekID, ItemID
from UserID
cross join WeekID
cross join ItemID
)
insert into tbl (UserID, WeekID, ItemID, ItemQty, ItemYTDQty)
select c.UserID, c.WeekID, c.ItemID, ItemQty = 0, isnull(y.ItemYTDQty, 0)
from comb c
cross apply
(
select ItemYTDQty = sum(ItemQty)
from tbl x
where x.UserID = c.UserID
and x.ItemID = c.ItemID
and x.WeekID <= c.WeekID
) y
where not exists
(
select *
from tbl x
where x.UserID = c.UserID
and x.WeekID = c.WeekID
and x.ItemID = c.ItemID
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

DaNuGai
Starting Member

10 Posts

Posted - 2012-06-25 : 12:07:38

Thank You khtan!

Your code didn't work, but it pointed me in the right direction. I was able to use your technique to solve the issue.

In your code, you did a cross join on WeekID and I was only looking for Cross Join on ItemID.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources