SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Case expression value based on sequence
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 07/02/2013 :  00:27:43  Show Profile  Reply with Quote
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)


bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 07/02/2013 :  00:51:09  Show Profile  Reply with Quote
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)


--
Chandu
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 07/02/2013 :  01:02:37  Show Profile  Reply with Quote
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:


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)

Edited by - flamblaster on 07/02/2013 01:03:56
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 07/02/2013 :  01:07:19  Show Profile  Reply with Quote
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


--
Chandu
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 07/02/2013 :  01:16:01  Show Profile  Reply with Quote
Lol...got it! thanks...was just about to post something similar! I appreciate your help. That makes a lot of sense :)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 07/02/2013 :  01:26:53  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000