Select from a table and update same table

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 2005 Forums

Transact-SQL (2005)

Select from a table and update same table

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-28 : 10:37:02

I have a query that selects top 3 products and quantity for previous month and updates a temp table, code below:

declare @DateFrom datetime
declare @DateTo datetime 

If Object_ID('tempdb..#Table1') Is Not Null Drop Table #Table1

create table	#Table1
		              (
                       product_code nvarchar(10),	
                       prev_month_qty dec(15,2) default 0,
                       prev_prev_month_qty dec(15,2) default 0,
                                                       )


set @DateFrom = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) 
set @DateTo = DATEADD(MONTH, 1, @DateFrom)

insert into #Table1
             (
              product_code,
              prev_month_qty
                             ) 
select        top 3 with ties
              branded as product,
              cast(sum(gross) as decimal(15, 2)) as quantity
from          tp6000.dbo.loadcomp
where         (@DateFrom IS Null or date >= @DateFrom) and
              (@DateTo IS Null or date <= dateadd(day, 1, @DateTo))
group by      branded
order by      sum(gross)desc

Now I need to expand my query as follows:

I need to compare the result for the previous month to the month prior to that for the top 3 selected products in the temp table and update the same temp table (prev_prev_month_qty). Not sure how to do that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 10:49:34

try


select 	@DateFrom	= dateadd(month, datediff(month, 0, getdate()) - 2, 0),
	@DateTo 	= dateadd(month, datediff(month, 0, getdate()), -1)

select	product, quantity1, quantity2
from
(
	select	branded as product,
	        sum(case when datepart(month, date) = datepart(month, @DateFrom) then gross else 0 end) as quantity1,
		sum(case when datepart(month, date) = datepart(month, @DateTo)   then gross else 0 end) as quantity2,
	        rank	= rank() over (partition by branded 
	        			order by sum(case when datepart(month, date) = datepart(month, @DateTo)  
	        					  then gross else 0 end) desc)
	from	tp6000.dbo.loadcomp
	where	(@DateFrom	is null	or	date	>= @DateFrom)
	and	(@DateTo	is null	or	date	<  dateadd(day, 1, @DateTo))
	group by      branded
) r
where	r.rank	<= 3

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 10:54:41

i am using case when . . . you can also use PIVOT to do it

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-28 : 11:23:43

Hi KH,

1. I changed the @DateTo to be the first day in next month since we are dealing with time so this is what I have:

select 	@DateFrom	= dateadd(month, datediff(month, 0, getdate()) - 2, 0),
	@DateTo 	= dateadd(month, datediff(month, 0, getdate()), -1)

2. When I run the query looks like all products are selected and not the top 3 (I get 12 records). First I need the top 3 for previous month only and then use only these 3 to get the quantities for the same for the prior month.

select	product, quantity1, quantity2
from
(
	select	branded as product,
	        sum(case when datepart(month, date) = datepart(month, @DateFrom) then gross else 0 end) as quantity1,
		sum(case when datepart(month, date) = datepart(month, @DateTo)   then gross else 0 end) as quantity2,
	        rank	= rank() over (partition by branded 
	        			order by sum(case when datepart(month, date) = datepart(month, @DateTo)  
	        					  then gross else 0 end) desc)
	from	tp6000.dbo.loadcomp
	where	(@DateFrom	is null	or	date	>= @DateFrom)
	and	(@DateTo	is null	or	date	<  dateadd(day, 1, @DateTo))
	group by      branded
) r
where	r.rank	<= 3

This is the result I get when I run the code originally posted:

Product     Prev Month     Prev Prev Month
PG64 A	    4357913.00	   0.00
PG76 A	    4230432.00	   0.00
672T A	    3617627.00	   0.00

So now I need to take the 3 product codes and get the quantities prior to the previous month

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 20:28:40

[code]
select product, quantity1, quantity2
from
(
select branded as product,
sum(case when datepart(month, date) = datepart(month, @DateFrom) then gross else 0 end) as quantity1,
sum(case when datepart(month, date) = datepart(month, @DateTo) then gross else 0 end) as quantity2,
rank = ~~rank()~~ row_number() over (~~partition by branded~~
order by sum(case when datepart(month, date) = datepart(month, @DateTo)
then gross else 0 end) desc)
from tp6000.dbo.loadcomp
where (@DateFrom is null or date >= @DateFrom)
and (@DateTo is null or date < dateadd(day, 1, @DateTo))
group by branded
) r
where r.rank <= 3
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 20:33:03

quote:
1. I changed the @DateTo to be the first day in next month since we are dealing with time so this is what I have:

select @DateFrom = dateadd(month, datediff(month, 0, getdate()) - 2, 0),
@DateTo = dateadd(month, datediff(month, 0, getdate()), -1)

if your @DateTo is the 1st of next month then you should not add 1 in your query


where	(@DateFrom	is null	or	date	>= @DateFrom)
and	(@DateTo	is null	or	date	<  @DateTo)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources