October 21, 2008 at 2:07 pm
Ok, I am having a complete brain fade today. I have a table which records a status for people. A record is written with a code of 1 when a new status begins. A record is written when a status is terminated/released. The data looks like this:
LAST_NAME STATUS_CODE_DATE ID_SET_STATUS_TYPE
------------------------------- ----------------------- ------------------
Graham 2002-10-07 00:00:00.000 1
Graham 2003-02-04 00:00:00.000 3
Graham 2003-05-23 00:00:00.000 1
Graham 2004-02-06 00:00:00.000 3
Graham 2004-02-10 00:00:00.000 1
Graham 2004-04-23 00:00:00.000 3
I need to write code to display the data above in three rows with each row having the start and end dates of the status. Example of what I need to see:
LAST_NAME STATUS_CODE START_DATE STATUS_EXIT_CODE END_DATE
--------- ----------- ----------------------- ---------------- -----------------------
Graham 1 2002-10-07 00:00:00.000 3 2003-02-04 00:00:00.000
Graham 1 2003-05-23 00:00:00.000 3 2004-02-06 00:00:00.000
Graham 1 2004-02-10 00:00:00.000 3 2004-04-23 00:00:00.000
Can someone please help me sort this out quickly? I have done this 1000 times if I have done it once BUT TODAY !?#@?$^@! I am at a loss. Of course, I am under the gun so the stress is cutting off the blood flow to my brain. 😉
Thanks in advance.
October 21, 2008 at 2:26 pm
This should get you close. You are going to have to add something in the column that has the query to identify a person like...
And A.CustomerID = B.CustomerID
Right Now I am just joing by Last Name.
Create Table #tmp
(LAST_NAME Varchar(50),
STATUS_CODE_DATE DateTime,
ID_SET_STATUS_TYPE Int)
Insert Into #tmp Values('Graham', '2002-10-07 00:00:00.000', 1)
Insert Into #tmp Values('Graham', '2003-02-04 00:00:00.000' , 3)
Insert Into #tmp Values('Graham' , '2003-05-23 00:00:00.000', 1)
Insert Into #tmp Values('Graham', '2004-02-06 00:00:00.000', 3)
Insert Into #tmp Values('Graham', '2004-02-10 00:00:00.000', 1)
Insert Into #tmp Values('Graham' , '2004-04-23 00:00:00.000', 3)
Insert Into #tmp Values('Simmons', '2002-10-07 00:00:00.000', 1)
Insert Into #tmp Values('Simmons' , '2004-04-23 00:00:00.000', 3)
Select LAST_NAME,
1,
STATUS_CODE_DATE ,
3,
(Select Min(STATUS_CODE_DATE) FROM #tmp A
Where A.ID_SET_STATUS_TYPE = 3 and
A.STATUS_CODE_DATE > B.STATUS_CODE_DATE AND
A.LAST_NAME = B.LAST_NAME)
from #tmp B
Where B.ID_SET_STATUS_TYPE = 1
Drop Table #tmp
October 21, 2008 at 2:32 pm
Thank you. That will get me moving forward.
October 21, 2008 at 2:34 pm
Something along this line :
SELECT
t.LAST_NAME,
t.ID_SET_STATUS_TYPE AS STATUS_CODE,
t.STATUS_CODE_DATE AS START_DATE,
(
SELECT Top 1 t1.ID_SET_STATUS_TYPE
FROM theHistoryTable t1
WHERE t1.LAST_NAME=t.LAST_NAME And t1.STATUS_CODE_DATE>t.STATUS_CODE_DATE
ORDER BY t1.STATUS_CODE_DATE ASC
) AS STATUS_EXIT_CODE,
(
SELECT Top 1 t1.STATUS_CODE_DATE
FROM theHistoryTable t1
WHERE t1.LAST_NAME=t.LAST_NAME And t1.STATUS_CODE_DATE>t.STATUS_CODE_DATE
ORDER BY t1.STATUS_CODE_DATE ASC
) AS END_DATE
FROM theHistoryTable t
WHERE t.ID_SET_STATUS_TYPE = 1
devloping robust and performant databaseapplications with Microsoft SQL-Server
October 21, 2008 at 2:40 pm
SELECT ...
FROM table1 t1
LEFT JOIN table1 t2 ON t2.key = t1.key AND t2.ID_SET_STATUS_TYPE = 3
WHERE t1.ID_SET_STATUS_TYPE = 1;
Need to identify the key column(s) for the table, but essentially - you need to join the table to itself including in the join only the final status and filter to the initial status.
Another option:
;WITH cteInitial (LastName, StatusDate, StatusCode)
AS (SELECT Last_Name
,STATUS_CODE_DATE
,ID_SET_STATUS_TYPE
FROM table1
WHERE ID_SET_STATUS_TYPE = 1)
,cteFinal (LastName, StatusDate, StatusCode)
,STATUS_CODE_DATE
,ID_SET_STATUS_TYPE
FROM table1
WHERE ID_SET_STATUS_TYPE = 3)
SELECT ...
FROM cteInitial i
JOIN cteFinal ON i.LastName = f.LastName;
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply