Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update table using Group By Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 9:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:46 AM
Points: 146, Visits: 791
Hi,

I have a column (Total) which needs to be updated based on a calculation of 2 other columns(Assigned + Unassigned) and is Grouped By a 4th column (Delivery Date).

Before

DeliveryDate	Assigned	Unaasigned
2013-04-29 1 0
2013-04-29 1 1
2013-04-29 1 1
2013-04-29 1 1
2013-04-30 0 0
2013-04-30 0 1
2013-05-01 0 0



After

DeliveryDate	Assigned	Unaasigned	Total
2013-04-29 1 0 7
2013-04-29 1 1 7
2013-04-29 1 1 7
2013-04-29 1 1 7
2013-04-30 0 0 1
2013-04-30 0 1 1
2013-05-01 0 0 0


The combined Assigned and Unassigned values for 2013-04-29 is 7 and is to be displayed in each of the 4 rows.

Can someone show me how to do this using T-Sql please?

Thanks in advance,
Post #1445539
Posted Tuesday, April 23, 2013 9:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Something like this??

DECLARE @t TABLE(DeliveryDate DATE,Assigned INT,Unassigned INT, Total INT)
INSERT INTO @t(DeliveryDate,Assigned,Unassigned)
VALUES
('2013-04-29',1,0),
('2013-04-29',1,1),
('2013-04-29',1,1),
('2013-04-29',1,1),
('2013-04-30',0,0),
('2013-04-30',0,1),
('2013-05-01',0,0);

SELECT DeliveryDate,Assigned,Unassigned,
SUM(Assigned + Unassigned) OVER(PARTITION BY DeliveryDate) Total
FROM @t
ORDER BY DeliveryDate



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1445548
Posted Monday, April 29, 2013 12:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:02 PM
Points: 150, Visits: 445
It seems from the wording of your original post that you may want this to be a computed column in a permanent table. Unfortunately, window functions cannot be part of a computed column definition. So you will have to use a view solution (such as the one Mark suggested above) or use a trigger to update the value any time the Assigned, Unassigned, or DeliveryDate columns change.
Post #1447683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse