SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help - Insert Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaNuGai
Starting Member

10 Posts

Posted - 06/21/2012 :  10:24:21  Show Profile  Reply with Quote
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)

Singapore
16746 Posts

Posted - 06/21/2012 :  10:55:40  Show Profile  Reply with Quote

; 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
	)



KH
Time is always against us


Edited by - khtan on 06/21/2012 10:56:12
Go to Top of Page

DaNuGai
Starting Member

10 Posts

Posted - 06/25/2012 :  12:07:38  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000