Case expression value based on sequence

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

General SQL Server Forums

New to SQL Server Programming

Case expression value based on sequence

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-07-02 : 00:27:43

Hi there, I currently have a query that calculates time and distances against rows in a table that have things like "pick up" and "Drop off" information, however, I need to use a bunch of derived tables (or CTE's) that take the min or max value for the day in order to process it. I would like to assign a distance traveled or time transpired based on the order of events. We use a status of "Pullout", "Pullin", "Pickup", "Dropoff" or "Noshow" to determine things like deadhead or non-revenue time/distance.

I've put together a table variable with some sample data and marked up the rows to show how I would want to add a column with a case expression in it to add an additional status value of (last pick up/first pick up etc).

If someone has any idea how to achieve the values I was looking for in the commented rows, I'd appreciate it! Thanks in advance!

[CODE]
declare @Table table (id int primary key identity not null, Sdate date, EstTime time, StatusType varchar(10))

insert into @Table (Sdate, EstTime, StatusType)
values
('20130101', '07:00', 'PullOut'),
('20130101', '07:15', 'PickUp'),
('20130101', '07:18', 'DropOff'),
('20130101', '07:20', 'PickUP'),
('20130101', '07:25', 'DropOff'),
('20130101', '07:30', 'PickUp'),
('20130101', '07:40', 'DropOff'),
('20130101', '07:45', 'PullIn'),

('20130102', '07:00', 'PullOut'),
('20130102', '07:15', 'NoShow'),
('20130102', '07:20', 'PickUP'),
('20130102', '07:25', 'DropOff'),
('20130102', '07:30', 'PickUp'),
('20130102', '07:40', 'DropOff'),
('20130102', '07:45', 'PullIn'),

('20130103', '07:00', 'PullOut'),
('20130103', '07:15', 'PickUp'),
('20130103', '07:18', 'DropOff'),
('20130103', '07:20', 'PickUP'),
('20130103', '07:25', 'DropOff'),
('20130103', '07:30', 'NoShow'),
('20130103', '07:45', 'PullIn')

select id, Sdate, EstTime, StatusType, ROW_NUMBER() Over (Partition by Sdate Order by EstTime) as EventOrder
from @Table
order by Sdate, EstTime

Results with comments:



(22 row(s) affected)
id          Sdate      EstTime          StatusType EventOrder
----------- ---------- ---------------- ---------- --------------------
1           2013-01-01 07:00:00.0000000 PullOut    1
2           2013-01-01 07:15:00.0000000 PickUp     2            --Need a column that calls this row "1st pickup" because it's the first pickup after pullout
3           2013-01-01 07:18:00.0000000 DropOff    3
4           2013-01-01 07:20:00.0000000 PickUP     4
5           2013-01-01 07:25:00.0000000 DropOff    5
6           2013-01-01 07:30:00.0000000 PickUp     6
7           2013-01-01 07:40:00.0000000 DropOff    7            --Need a column that calls this row "last dropoff" because it's the last dropoff before pullin
8           2013-01-01 07:45:00.0000000 PullIn     8
9           2013-01-02 07:00:00.0000000 PullOut    1
10          2013-01-02 07:15:00.0000000 NoShow     2            
11          2013-01-02 07:20:00.0000000 PickUP     3            --Need a column that calls this row "1st pickup" because it's the first pickup after pullout, noshow is ignored
12          2013-01-02 07:25:00.0000000 DropOff    4
13          2013-01-02 07:30:00.0000000 PickUp     5
14          2013-01-02 07:40:00.0000000 DropOff    6            --Need a column that calls this row "last dropoff" because it's the last dropoff before pullin
15          2013-01-02 07:45:00.0000000 PullIn     7
16          2013-01-03 07:00:00.0000000 PullOut    1
17          2013-01-03 07:15:00.0000000 PickUp     2             --Need a column that calls this row "1st pickup" because it's the first pickup after pullout
18          2013-01-03 07:18:00.0000000 DropOff    3
19          2013-01-03 07:20:00.0000000 PickUP     4
20          2013-01-03 07:25:00.0000000 DropOff    5            --Need a column that calls this row "last dropoff" because it's the last dropoff before pullin, noshow is ignored
21          2013-01-03 07:30:00.0000000 NoShow     6
22          2013-01-03 07:45:00.0000000 PullIn     7

(22 row(s) affected)


[/code]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-02 : 00:51:09

[code]SELECT id, Sdate, EstTime, StatusType
FROM (select id, Sdate, EstTime, StatusType, CASE statusType WHEN 'PickUp' THEN 1 WHEN 'DropOff' THEN 2 END flag
,ROW_NUMBER() Over (Partition by Sdate, CASE statusType WHEN 'PickUp' THEN 1 ELSE 0 END ORDER BY EstTime ) as EventPickUp
,ROW_NUMBER() Over (Partition by Sdate, CASE statusType WHEN 'DropOff' THEN 1 ELSE 0 END ORDER BY EstTime DESC) as EventDropOff
from @Table
) t
WHERE (flag = 1 AND EventPickUp = 1) OR (flag = 2 AND EventDropOff = 1)[/code]

--
Chandu

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-07-02 : 01:02:37

Hi Chandu,

Thanks for the quick reply. Sorry, I guess I wasn't clear, I still want to display all 22 records, I just want to mark an additional "status" column that would look like this:

[CODE]
id Sdate EstTime StatusType EventOrder AdditionalStatus
----------- ---------- ---------------- ---------- -------------------- ----------------
1 2013-01-01 07:00:00.0000000 PullOut 1 Pullout
2 2013-01-01 07:15:00.0000000 PickUp 2 1st Pickup
3 2013-01-01 07:18:00.0000000 DropOff 3 Dropoff
4 2013-01-01 07:20:00.0000000 PickUP 4 Pickup
5 2013-01-01 07:25:00.0000000 DropOff 5 Dropoff
6 2013-01-01 07:30:00.0000000 PickUp 6 Pickup
7 2013-01-01 07:40:00.0000000 DropOff 7 Last Dropoff
8 2013-01-01 07:45:00.0000000 PullIn 8 Pullin
9 2013-01-02 07:00:00.0000000 PullOut 1 Pullout
10 2013-01-02 07:15:00.0000000 NoShow 2 Skip
11 2013-01-02 07:20:00.0000000 PickUP 3 1st Pickup
12 2013-01-02 07:25:00.0000000 DropOff 4 Dropoff
13 2013-01-02 07:30:00.0000000 PickUp 5 Pickup
14 2013-01-02 07:40:00.0000000 DropOff 6 Last Dropoff
15 2013-01-02 07:45:00.0000000 PullIn 7 Pullin
16 2013-01-03 07:00:00.0000000 PullOut 1 Pullout
17 2013-01-03 07:15:00.0000000 PickUp 2 1st Pickup
18 2013-01-03 07:18:00.0000000 DropOff 3 Dropoff
19 2013-01-03 07:20:00.0000000 PickUP 4 Pickup
20 2013-01-03 07:25:00.0000000 DropOff 5 Last Dropoff
21 2013-01-03 07:30:00.0000000 NoShow 6 Skip
22 2013-01-03 07:45:00.0000000 PullIn 7 Pullin

(22 row(s) affected)
[/CODE]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-02 : 01:07:19

[code]SELECT id, Sdate, EstTime, StatusType,
CASE WHEN (flag = 1 AND EventPickUp = 1) THEN '1st PickUp'
WHEN (flag = 2 AND EventDropOff = 1) THEN 'Last DropOff'
ELSE StatusType
END as AdditionalStatus
FROM (select id, Sdate, EstTime, StatusType, CASE statusType WHEN 'PickUp' THEN 1 WHEN 'DropOff' THEN 2 END flag
,ROW_NUMBER() Over (Partition by Sdate, CASE statusType WHEN 'PickUp' THEN 1 ELSE 0 END ORDER BY EstTime ) as EventPickUp
,ROW_NUMBER() Over (Partition by Sdate, CASE statusType WHEN 'DropOff' THEN 1 ELSE 0 END ORDER BY EstTime DESC) as EventDropOff
from @Table
) t
ORDER BY Sdate, EstTime[/code]

--
Chandu

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-07-02 : 01:16:01

Lol...got it! thanks...was just about to post something similar! I appreciate your help. That makes a lot of sense :)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-02 : 01:26:53

quote:
Originally posted by flamblaster

Lol...got it! thanks...was just about to post something similar! I appreciate your help. That makes a lot of sense :)

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources