October 5, 2011 at 6:14 pm
I am too tired and my brain is turned off, I can't figure it out 🙁
I basically want to know how many days total each FK_ID has spent in unavailable Status. From the first time they became unavailable (if they didn't start on unavailable ).
The below table holds the date, of when the status changes from status1 to status2. There can be other statuses other than available and unavailable.
Thank you for your help!!
CREATE TABLE dbo.ChangesLog
(
Id INT IDENTITY (1,1),
FK_ID INT,
Status1 VARCHAR(100),
Status2 VARCHAR(100),
CreateDate DATETIME
)
GO
INSERT INTO ChangesLog (FK_ID, Status1, Status2, CreateDate)
VALUES(1, 'Unavailable', 'Available', '20110221')
,(1, 'Available', 'Unavailable', '20110220')
,(4, 'Available', 'Unavailable', '20110110')
,(1, 'Unavailable', 'Available', '20110214')
,(1, 'Available', 'Unavailable', '20110213')
,(1, 'Unavailable', 'Available', '20110210')
,(3, 'Unavailable', 'Available', '20110123')
,(1, 'Available', 'Unavailable', '20110209')
,(1, 'Unavailable', 'Available', '20110203')
,(1, 'Available', 'Unavailable', '20110201')
,(3, 'Available', 'Unavailable', '20110115')
,(2, 'Unavailable', 'Available', '20110104')
,(1, 'Unavailable', 'Available', '20110129')
,(5, 'Unavailable', 'Available', '20110101')
,(1, 'Available', 'Unavailable', '20110125')
,(1, 'Unavailable', 'Available', '20110122')
,(5, 'Available', 'Unavailable', '20110101')
,(1, 'Available', 'Unavailable', '20110121')
,(6, 'Unavailable', 'Available', '20110130')
,(1, 'Unavailable', 'Available', '20110111')
,(1, 'Available', 'Unavailable', '20110105')
,(2, 'Available', 'Unavailable', '20110102')
,(6, 'Available', 'Unavailable', '20110101')
,(1, 'Unavailable', 'Available', '20110101')
,(4, 'Unavailable', 'Available', '20110101')
,(7, 'Unavailable', 'Available', '20110114')
,(7, 'Available', 'Unavailable', '20110101')
October 5, 2011 at 6:58 pm
EDIT:
Scratch that, doesn't quite work after verifying the data. But I will leave it up, maybe someone can use it as a basis to get it 100% correct. I will take another look at it tomorrow, must sleep now 🙂
WITH CTE AS (
SELECTFK_ID,Status1,CreateDate
,ROW_NUMBER() OVER(ORDER BY createdate) [rownum]
FROM ChangesLog
),
CTE2 AS (
SELECTA.FK_ID,DATEDIFF(DAY,A.CreateDate,B.CreateDate) [diff]
FROM CTE A, CTE B
WHERE b.rownum = a.rownum + 1
AND a.status1 = 'available'
)
SELECTFK_ID,SUM(diff) [Days Unavailable]
FROM CTE2
GROUP BY FK_ID
October 5, 2011 at 11:01 pm
2 Small tweaks made it work, thanks!
In case anyone cares the code is below. I added a partition by to the ranking function, and also added an additional join condition on the self join to make sure calculations are only done on the same FK_ID.
There was also a requirement I didn't mention which was that if there are 0 days spent as unavailable I still need to see the record but with a 0, I did this by changing the inner join to a left join and doing an isnull on the sum.
WITH CTE AS (
SELECT FK_ID,Status1,CreateDate
,ROW_NUMBER() OVER(PARTITION BY FK_ID ORDER BY createdate) [rownum]
FROM ChangesLog
),
CTE2 AS (
SELECT A.FK_ID,DATEDIFF(DAY,A.CreateDate,B.CreateDate) [diff]
FROM CTE A
LEFT JOIN CTE B
ON b.rownum = a.rownum + 1
AND a.status1 = 'available' AND A.FK_ID = B.FK_ID
)
SELECT FK_ID,ISNULL(SUM(diff), 0) [Days Unavailable]
FROM CTE2
GROUP BY FK_ID
October 6, 2011 at 2:43 am
Very nice!
Yeh Im not sure why I didnt have the partition by FK_ID, I think when I was initially testing it I put a WHERE clause in the first CTE to just show FK_ID = 1 so it gave me correct data, then I took it off to work on all the rows and didn't think about the different FK_IDs.
Hey, it was like 2am 🙂 But good to get it sorted.
October 6, 2011 at 10:27 am
Yeah, thanks! I couldn't have done it without you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply