CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Update multiple variables in a single query

I have a table with Sum of Amounts for Each Weekday (Sunday to Saturday) . Table structure is as below.

enter image description here

I need to assign these table values into parameters. For E.g : I need to assign sum of rdate '2015-11-15' i.e 324 to a variable @sundayval , 374 to variable @mondayval etc...

How to do this In a single update query. I have tried out with Case statement, But it only assigns value to variable @saturdayval .

Thanks for the Help.

Upvotes: 0

Views: 270

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

This does the job. It doesn't depend on any particulat DATEFIRST settings - it instead uses an arbitrarily chosen sunday (I picked 17th May this year) (what I usually refer to as a "known good" date because it has the property we're looking for, in this case the right day of the week):

declare @t table ([sum] int not null,rdate datetime2 not null)
insert into @t([sum],rdate) values
(324,'20151115'),
(374,'20151116'),
(424,'20151117'),
(474,'20151118'),
(524,'20151119'),
(574,'20151120'),
(624,'20151121')

declare @sundayval int
declare @mondayval int
declare @tuesdayval int
declare @wednesdayval int
declare @thursdayval int
declare @fridayval int
declare @saturdayval int

select
    @sundayval    = SUM(CASE WHEN DATEPART(weekday,rdate) = DATEPART(weekday,'20150517') THEN [sum] END),
    @mondayval    = SUM(CASE WHEN DATEPART(weekday,rdate) = DATEPART(weekday,'20150518') THEN [sum] END),
    @tuesdayval   = SUM(CASE WHEN DATEPART(weekday,rdate) = DATEPART(weekday,'20150519') THEN [sum] END),
    @wednesdayval = SUM(CASE WHEN DATEPART(weekday,rdate) = DATEPART(weekday,'20150520') THEN [sum] END),
    @thursdayval  = SUM(CASE WHEN DATEPART(weekday,rdate) = DATEPART(weekday,'20150521') THEN [sum] END),
    @fridayval    = SUM(CASE WHEN DATEPART(weekday,rdate) = DATEPART(weekday,'20150522') THEN [sum] END),
    @saturdayval  = SUM(CASE WHEN DATEPART(weekday,rdate) = DATEPART(weekday,'20150523') THEN [sum] END)
from @t

select @sundayval,@mondayval,@tuesdayval,@wednesdayval,@thursdayval,@fridayval,@saturdayval

Result:

----------- ----------- ----------- ----------- ----------- ----------- -----------
324         374         424         474         524         574         624

Upvotes: 2

bmsqldev
bmsqldev

Reputation: 2735

I have No idea Whether a Single UPDATE Statement will do this. I have assigned value for Each Variable like as Below,

SELECT @SunTotal =  [sum]
                    FROM [table] 
                    where rdate = @Startdate

SELECT @MonTotal =  [sum]
                    FROM [table] 
                    where rdate = DATEADD(DAY,1,@Startdate)

SO ON...

Upvotes: 0

Jeff Meatball Yang
Jeff Meatball Yang

Reputation: 39057

Ok - let's do it this way: the else case is set to return itself, so each variable essentially aggregates as a coalesce. Note: I don't have any way to test this right now. :)

SELECT
    @sundayval = case when DATEPART(weekday, rdate) = 1 then sum else @sundayval end
    , @mondayval = case when DATEPART(weekday, rdate) = 2 then sum else @mondayval end
    , @tuesdayval = case when DATEPART(weekday, rdate) = 3 then sum else @tuesdayval end
    , @wednesdayval = case when DATEPART(weekday, rdate) = 4 then sum else @wednesdayval end
    , @thursdayval = case when DATEPART(weekday, rdate) = 5 then sum else @thursdayval end
    , @fridayval = case when DATEPART(weekday, rdate) = 6 then sum else @fridayval end
    , @saturdayval = case when DATEPART(weekday, rdate) = 7 then sum else @saturdayval end
FROM TABLE

Upvotes: 1

Related Questions