February 24, 2009 at 11:28 am
Hello every one,
I am new to SQL server.
I have question. I want to find days between two days for the action type closed.
for issueid =2683338 days will be diff between dates of actiontype open and close simple.
datediff(d,2008-06-24 11:35:56.943 2008-05-02 07:08:21.253),
But complexity is for issueid 2708592
I have seq 1 to 8 but i want to get individual days between each opened and closed in order so at the end i by adding all the days i will get total days. i can not user opened date for eq no1 an closed date for seq 8 as in some cases there will be gap between last closed and next open so that will not be counted in total days.
issueid seq no actiontype actiondateactionreason
26833381OPENED2008-05-02 07:08:21.253NULL
26833382CLOSED2008-06-24 11:35:56.943NULL
27085921OPENED2008-05-19 17:06:24.837NULL
27085922CLOSED2008-05-19 17:12:46.667NULL
27085923OPENED2008-05-20 11:36:11.7671
27085924CLOSED2008-05-20 21:39:44.983NULL
27085925OPENED2008-05-21 07:00:00.8301
27085926CLOSED2008-05-21 09:52:18.420NULL
27085927OPENED2008-05-21 15:23:51.7931
27085928CLOSED2008-05-22 09:34:46.840NULL
27412351OPENED2008-06-16 10:17:01.260NULL
THank you in advance.
February 24, 2009 at 11:45 am
If you select from two copies of the table joined to each other on issue number and sequence, you can get the sequential open and close dates and the days in between.
For example:
select t1.issueid, datediff(day, t1.actiondate, t2.actiondate) as days
from dbo.MyTable t1
inner join dbo.MyTable t2
on t1.issueid = t2.issueid
and t1.[seq no] = t2.[seq no] - 1
where t1.actiontype = 'open'
and t2.actiontype = 'close';
Try that, see if it gives you what you need in terms of days. Then you can sum up the days column per issueid and get the total.
Keep in mind that datediff will give you the number of numerical days between two dates, which can be wrong in many business cases. For example, if the open action is at the end of the day on Friday, and close is at the beginning of the day on Monday, does that count as 3 days (which is what datediff will give you)?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 7:31 am
Thank you so much for your help,
I wanted to avoid self join , was looking for lead, lag function like in oracle.
😀
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply