Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Counting Days over multiple rows / Date Add

Author  Topic 

ketanmmistry
Starting Member

8 Posts

Posted - 2013-07-25 : 19:50:38
I’m trying to work out 182 days (our certificate due date) from a selection of start and end dates for all my learners.

However what’s making things difficult is a customer will be enrolled onto one course at a time (however the start and end dates will always vary as some learners may take longer to complete a course)
The countdown of 182 days starts counting down from the first history event start date for a learner. Then if an end date is put in, the number of days taken to complete the course is worked out and then taken off the 182.

What I’m trying to do is work out what the certificate due date will be for a learner and this date will always be the same for that learner no matter how many courses the customer is enrolled on / completes (as its just based on 182 days, across all courses).

So in the example below learner (3449) has been placed 140 days in their first course and started a 2nd course meaning they only need to study for 42 days (182-140), so the certificate due date will be ‘2013-01-28’. Even when the learner completes the 2nd course and starts another one I want the due date to be ‘2013-01-28’ as their certificate due date can only be reached once and remains the same regardless of how many courses they go on to do.

I hope this makes sense.

I’ve gone back to the basics with the below query, as I have played around with case statements, self joining cte’s with a row over partition. I need this due date to be worked out no matter how many courses a learner goes on, it just needs to count down 182 across various rows of data per learner



CREATE TABLE [dbo].[WPR](
[Learner_ID] [int] NOT NULL,
[Firstname] [char](30) NULL,
[Surname] [char](30) NULL
)

CREATE TABLE [dbo].[HISTORY](
[HistoryID] [int] NOT NULL,
[Learner_ID] [int] NULL,
[DateStart] [smalldatetime] NULL,
[DateEnd] [smalldatetime] NULL
)

INSERT INTO WPR (Learner_ID, Firstname, Surname) VALUES (3449,'JEFFREY','SMITH')
INSERT INTO WPR (Learner_ID, Firstname, Surname) VALUES (772,'JO','BLOGGS')
INSERT INTO WPR (Learner_ID, Firstname, Surname) VALUES (999,'TOM','LOMAS')


INSERT INTO HISTORY (HistoryID,Learner_ID,DateStart,DateEnd,Category_ID) VALUES (1,3449,'2012-05-08 00:00','2012-09-26 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateStart,DateEnd,Category_ID) VALUES (2,3449,'2012-12-17 00:00','2013-02-08 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateStart,Category_ID) VALUES (3,3449,'2013-02-11 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateStart,DateEnd,Category_ID) VALUES (4,772,'2012-06-18 00:00','2013-04-02 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateStart,Category_ID) VALUES (5,772,'2013-05-28 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateStart,Category_ID) VALUES (6,999,'2013-01-28 00:00',6)


select w.Learner_ID,
h.historyID,
h.DateStart,
h.DateEnd,
datediff(d,h.DateStart, h.DateEnd)
from WPR w
INNER JOIN HISTORY H (nolock)
on w.Learner_ID = h.Learner_ID
where H.Category_ID=6


ketan

Ketan M Mistry

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-25 : 21:33:42
[CODE]
-- Works with SQL2012 & later

;
WITH CTE AS
(select w.Learner_ID,
h.historyID,
h.DateStart,
h.DateEnd,
ROW_NUMBER() OVER(PARTITION BY w.Learner_ID ORDER BY h.DateStart) as RN
from WPR w
INNER JOIN HISTORY H (nolock)
on w.Learner_ID = h.Learner_ID
),
CTE2 AS
(SELECT Learner_ID, DATEADD(dd, 180, DateStart) as FinishDate
FROM CTE WHERE RN = 1 )
SELECT T.Learner_ID, T.historyID, T.DateStart, T.DateEnd As OriginalEndDate,
IIF(DATEDIFF(dd, T.DateEnd, T2.FinishDate) > 0, T.DateEnd, T2.FinishDate) as EnforcedEndDate,
datediff(d, DateStart, IIF(DateEnd <= FinishDate, DateEnd, FinishDate)) as NumDays
FROM CTE T LEFT JOIN CTE2 T2 ON T.Learner_ID = T2.Learner_ID ORDER BY historyID



-- SQL2008 compatible Query
;WITH CTE AS
(select w.Learner_ID,
h.historyID,
h.DateStart,
h.DateEnd,
ROW_NUMBER() OVER(PARTITION BY w.Learner_ID ORDER BY h.DateStart) as RN
from WPR w
INNER JOIN HISTORY H (nolock)
on w.Learner_ID = h.Learner_ID
),
CTE2 AS
(SELECT Learner_ID, DATEADD(dd, 180, DateStart) as FinishDate
FROM CTE WHERE RN = 1 )
SELECT T.Learner_ID, T.historyID, T.DateStart, T.DateEnd As OriginalEndDate,
(CASE WHEN (DATEDIFF(dd, T.DateEnd, T2.FinishDate) > 0) THEN T.DateEnd ELSE T2.FinishDate END) as EnforcedEndDate,
datediff(d, DateStart, IIF(DateEnd <= FinishDate, DateEnd, FinishDate)) as NumDays
FROM CTE T LEFT JOIN CTE2 T2 ON T.Learner_ID = T2.Learner_ID ORDER BY historyID


[/CODE]

EDIT: 2008 Compatible query added
Go to Top of Page

ketanmmistry
Starting Member

8 Posts

Posted - 2013-07-30 : 04:53:50
Hi MuMu88,

Many thanks for your response - much appreciated. Ive changed the sql in Numdays to (so it works in 2008)

datediff(d, DateStart, (case when DateEnd <= FinishDate then DateEnd else FinishDate end)) as NumDays

However what i am trying to achieve is that the enforced end date (certificate due date) needs to be '2013-01-28’ for all entries (for learner 3449) or at least the latest entry. This is beacause they studied 141 days in their first course, but in the next course they only have to study 41 days to get to their certificate due date. Howeever the learner will carry on but still have '2013-01-28’ as their certificate due date.



The learner has 182 days so where less than 182 (proabably a sum of total datediffs) this needs to keep on getting subtracted from 182 for all other entries. until it is achieved. i hope this makes sense.

Ketan

Ketan M Mistry
Go to Top of Page
   

- Advertisement -