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 ««12

Change Excel formula to Case Statement Expand / Collapse
Author
Message
Posted Thursday, October 03, 2013 3:29 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, Visits: 382
Koen Verbeeck (10/3/2013)
You'll probably need a cursor to update your values, because to calculate the OneStopOrNot column for one row, you need the calculated result of OneStopOrNot column of the previous row.

With a set-based solution all the rows would be updated at once, which means you cannot use the result of the previous row.

To get you started on cursors:
SQL Server Cursor Example

Just to be perfectly clear: cursors are bad and slow for performance. However, in your specific business case you might need them. (unless someone else proves me wrong of course and uses set-based TSQL to solve this issue)


Cool, thanks for this, I'll have a look and give it a go
Post #1501078
Posted Thursday, October 03, 2013 5:37 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
Hi,

This can be achieved using CTE


declare @person table (PersonID int, ApptDate datetime, ApptStatus varchar(10), NoOfAppts int, OneStopOrNot VARCHAR(10))

insert into @person (PersonID, ApptDate, ApptStatus, NoOfAppts) values
(49820,'07-09-2013','Seen',1),
(49827,'12-sep-2013','Seen',1),
(49831,'07-sep-2013','Seen',1),
(49834,'07-sep-2013','Seen',1),
(50084,'07-sep-2013','Seen',1),
(50097,'05-sep-2013','Seen',1),
(50172,'05-sep-2013','Seen',1),
(50172,'27-sep-2013','Seen',2),
(50175,'05-sep-2013','Seen',1)


;WITH persons AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY PersonID, ApptStatus),
*
FROM @person
)
UPDATE cur SET OneStopOrNot =
( case WHEN cur.PersonID != prev.PersonID THEN 'OneStop'
WHEN prev.PersonID IS NULL THEN 'OneStop'
WHEN cur.PersonID = prev.PersonID AND prev.OneStopOrNot = 'MoreThan1' THEN 'MoreThan1'
WHEN cur.PersonID = prev.PersonID AND cur.ApptDate = prev.ApptDate THEN 'OneStop'
ELSE 'MoreThan1' END)
FROM persons cur
LEFT JOIN persons prev on prev.rownum = cur.rownum - 1

select * from @person

Post #1501126
Posted Thursday, October 03, 2013 5:41 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
This can be achieved using CTE


declare @person table (PersonID int, ApptDate datetime, ApptStatus varchar(10), NoOfAppts int, OneStopOrNot VARCHAR(10))

insert into @person (PersonID, ApptDate, ApptStatus, NoOfAppts) values
(49820,'07-09-2013','Seen',1),
(49827,'12-sep-2013','Seen',1),
(49831,'07-sep-2013','Seen',1),
(49834,'07-sep-2013','Seen',1),
(50084,'07-sep-2013','Seen',1),
(50097,'05-sep-2013','Seen',1),
(50172,'05-sep-2013','Seen',1),
(50172,'27-sep-2013','Seen',2),
(50175,'05-sep-2013','Seen',1)


;WITH persons AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY PersonID, ApptStatus),
*
FROM @person
)
UPDATE cur SET OneStopOrNot =
( case WHEN cur.PersonID != prev.PersonID THEN 'OneStop'
WHEN prev.PersonID IS NULL THEN 'OneStop'
WHEN cur.PersonID = prev.PersonID AND prev.OneStopOrNot = 'MoreThan1' THEN 'MoreThan1'
WHEN cur.PersonID = prev.PersonID AND cur.ApptDate = prev.ApptDate THEN 'OneStop'
ELSE 'MoreThan1' END)
FROM persons cur
LEFT JOIN persons prev on prev.rownum = cur.rownum - 1

select * from @person

Post #1501128
Posted Thursday, October 03, 2013 6:34 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
By mistaken two times posted the same.
Post #1501158
Posted Thursday, October 03, 2013 9:20 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, Visits: 382
parulprabu (10/3/2013)
By mistaken two times posted the same.


Thanks for the CTE, I'll give that a go :)
Post #1501244
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse