Need Help on query to select every 2 rows based on a date
-
Dear all,
I can't figure this Problem out:
I have a table like the following (with much more data, but the concept is the same) with Dates and Actions for People and a column called Action with beginning Dates and end dates.
(I attached a picture because I could not figure out how to Format it)
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
I need the query to be like this. The idea is to have the query grab the next 'END' not all Ends, which my attempts have done i.e. I get not just the closest end to the begin date, but ALL Ends with the same Person.
I Need it to look like this:
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
There can be different People so the query Needs to return the beginning and end rows for the Person in sequential order.
Any help is greatly appreciated. I can't figure out how to select only the 'next' end. My query always gets 'end' values that have a 'begin'. Is this possible? Hope this makes sense.
Thanks for any help.
-
Quick solution
😎
USE tempdb;GO
SET NOCOUNT ON;
GO
DECLARE @SAMPLE_DATA TABLE
(
[action] VARCHAR(10) NOT NULL
,[date] DATE NOT NULL
,[Name] VARCHAR(25) NOT NULL
);
INSERT INTO @SAMPLE_DATA([action],[date] ,[Name])
VALUES ('end' ,'2014-10-31','phil')
,('begin' ,'2014-10-15','phil')
,('end' ,'2014-09-30','phil')
,('begin' ,'2014-09-18','phil')
,('end' ,'2014-08-23','John')
,('begin' ,'2014-08-21','John')
;
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.[Name]
ORDER BY SD.[date]
) - CASE
WHEN SD.[action] = 'end' THEN 1
ELSE 0
END AS RID
,SD.[action]
,SD.[date]
,SD.[Name]
FROM @SAMPLE_DATA SD
)
SELECT
MAX(CASE WHEN BD.[action] = 'begin' THEN BD.[action] END) AS [begin]
,MAX(CASE WHEN BD.[action] = 'begin' THEN BD.[date] END) AS [Date]
,MAX(CASE WHEN BD.[action] = 'end' THEN BD.[action] END) AS [end]
,MAX(CASE WHEN BD.[action] = 'end' THEN BD.[date] END) AS [Date]
,BD.[Name]
FROM BASE_DATA BD
GROUP BY BD.[Name]
,BD.RID
ORDER BY BD.[Name] DESC
,BD.RID DESC;
Results
begin Date end Date Name---------- ---------- ---------- ---------- -----
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
-
Many thanks!
This worked, but I made a temp table for the first part. TBH, I am fairly new to SQL and do not understand the beginning part:
;
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.[Name]
ORDER BY SD.[date] ........Etc....
I was getting some errors since maybe I do not Need to create a table. I made a temp table and then queried it. What exactly is the first select Statement I have never seen that before?
Either way this works fantastically. Thank you, seriously!
-
One more question...
If I wanted to get a DATEDIFF between the two Dates, how would I accomplish that? Many thanks.
-
Here is the code again with comments, let me know if you need further explanation.
😎
/* CTE (Common Table Expression for marking the sessions inchronological order, creating an identifier for each pair
of begin/end. It makes the assumption that there will
always be a beginning and an end for each party.
*/
;WITH BASE_DATA AS
(
SELECT
/* The ROW_NUMBER "counts" the instances of each party
and subtracs one from each "end". The result is a
group identifier of odd numbers, each value unique
for each session.
*/
ROW_NUMBER() OVER
(
PARTITION BY SD.[Name]
ORDER BY SD.[date]
) - CASE
WHEN SD.[action] = 'end' THEN 1
ELSE 0
END AS RID
,SD.[action]
,SD.[date]
,SD.[Name]
FROM @SAMPLE_DATA SD
)
/* Simple cross-tab to transpose/pivot the results to the
desired format. The group identifier is necessary in order
not to compress/pack the sessions, without it there would
only be one session per party
*/
SELECT
MAX(CASE WHEN BD.[action] = 'begin' THEN BD.[action] END) AS [begin]
,MAX(CASE WHEN BD.[action] = 'begin' THEN BD.[date] END) AS [Date]
,MAX(CASE WHEN BD.[action] = 'end' THEN BD.[action] END) AS [end]
,MAX(CASE WHEN BD.[action] = 'end' THEN BD.[date] END) AS [Date]
,BD.[Name]
FROM BASE_DATA BD
GROUP BY BD.[Name]
,BD.RID
ORDER BY BD.[Name] DESC
,BD.RID DESC;
-
g7u (9/6/2015)
One more question...If I wanted to get a DATEDIFF between the two Dates, how would I accomplish that? Many thanks.
Simply add it to the last part of the query
😎
;WITH BASE_DATA AS(
SELECT
/* The ROW_NUMBER "counts" the instances of each party
and subtracs one from each "end". The result is a
group identifier of odd numbers, each value unique
for each session.
*/
ROW_NUMBER() OVER
(
PARTITION BY SD.[Name]
ORDER BY SD.[date]
) - CASE
WHEN SD.[action] = 'end' THEN 1
ELSE 0
END AS RID
,SD.[action]
,SD.[date]
,SD.[Name]
FROM @SAMPLE_DATA SD
)
/* Simple cross-tab to transpose/pivot the results to the
desired format. The group identifier is necessary in order
not to compress/pack the sessions, without it there would
only be one session per party
*/
SELECT
MAX(CASE WHEN BD.[action] = 'begin' THEN BD.[action] END) AS [begin]
,MAX(CASE WHEN BD.[action] = 'begin' THEN BD.[date] END) AS [Date]
,MAX(CASE WHEN BD.[action] = 'end' THEN BD.[action] END) AS [end]
,MAX(CASE WHEN BD.[action] = 'end' THEN BD.[date] END) AS [Date]
,DATEDIFF(DAY,MAX(CASE WHEN BD.[action] = 'begin' THEN BD.[date] END)
,MAX(CASE WHEN BD.[action] = 'end' THEN BD.[date] END)) AS DURATION
,BD.[Name]
FROM BASE_DATA BD
GROUP BY BD.[Name]
,BD.RID
ORDER BY BD.[Name] DESC
,BD.RID DESC;
Results
begin Date end Date DURATION Name---------- ---------- ---------- ---------- ----------- ------
begin 2014-10-15 end 2014-10-31 16 phil
begin 2014-09-18 end 2014-09-30 12 phil
begin 2014-08-21 end 2014-08-23 2 John
-
I really can't thank you enough. Thank you for the superb help and explanation. This was stumping me for quite some time.
-
g7u (9/6/2015)
I really can't thank you enough. Thank you for the superb help and explanation. This was stumping me for quite some time.You are very welcome and thank you for the feedback;-)
😎
-
SELECT a.action as [begin],a.date,b.action as [end],b.date,a.Name
FROM(
SELECT * , ROW_NUMBER()OVER (order by action,date desc) as row_Num
FROM #SAMPLE_DATA
WHERE action='begin'
)a
INNER JOIN
(SELECT * , ROW_NUMBER()OVER (order by action,date desc) as row_Num
FROM #SAMPLE_DATA
WHERE action='end')b
ON a.row_Num=b.row_Num
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply