SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Counting Days over multiple rows / Date Add
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 07/25/2013 :  19:50:38  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/25/2013 :  21:33:42  Show Profile  Reply with Quote

-- 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




EDIT: 2008 Compatible query added

Edited by - MuMu88 on 07/25/2013 23:06:09
Go to Top of Page

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 07/30/2013 :  04:53:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000