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)
 Working with Dates (Dateadd??)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 04/16/2013 :  09:17:15  Show Profile  Reply with Quote
Hi,

Im trying to do a date add based on a certain criteria to either add 182 days or 91 day (certificate due date) based on a what client group my learner is on. For ClientgroupID 1 its 182 and ClientgroupID 2 91.

Basically what im trying to do is work out what the certifciate due date will be for a learner.

However the 182 / 91 has to start counting down from the first history event startdate.

So in the example below customer (42457) has been placed 31 days in one placement and then moved to another placement so 31 has to be subtracted from the 182 in his next placement. This date will then stay the same as it will be for how many days he has been actively placed in a placement.

In the other example customer (22161) has been placed for 239 days, so we can work out the 182, but for his 2nd Placement i would need it to show 182 from their first start date.

I hope this makes sense.

i have played around with case statements but im struggling to get it to work, especially for the latest history for the learner. i will continue to look at this, but any help be appreciated greatly


CREATE TABLE [dbo].[WPR](
	[Learner_ID] [int] NOT NULL,
	[Firstname] [char](30) NULL,
	[Surname] [char](30) NULL,
	[NI] [char](9) NULL,
	[DOB] [smalldatetime] NULL,
	[Datereferral] [smalldatetime] NULL,
	[ClientGroupID] [int] NOT NULL
) 

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

INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral, ClientGroupID) VALUES (22161,'JEFFREY','SMITH','KK991155K','1979-12-28 00:00','2012-08-07 00:00',1)
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral, ClientGroupID) VALUES (42457,	'JO','BLOGGS','PP059991Z','1968-09-28 00:00','2012-06-01 00:00',2)

INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,DateEnd,Category_ID) VALUES (1,22161,	'2012-09-19 00:00','2011-08-31 00:00','2012-04-26 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,Category_ID) VALUES (2,22161,	'2012-08-16 00:00','2012-09-17 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,DateEnd,Category_ID) VALUES (3,42457,	'2012-09-19 00:00','2011-08-31 00:00','2011-10-01 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,Category_ID) VALUES (4,42457,	'2012-08-16 00:00','2012-09-17 00:00',6)

select w.Learner_ID,
       w.ClientGroupID,
       h.historyID,
		h.DateStart,
		h.DateEnd,
		datediff(d,h.DateStart, h.DateEnd)
from wpr w
      left join HISTORY H (nolock)
      on w.Learner_ID = h.Learner_ID
where H.Category_ID=6
Order by w.Learner_id





Ketan M Mistry

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 04/16/2013 :  10:20:59  Show Profile  Reply with Quote
not sure I'm completely following. Can you please post the desired result based on this sample data?

Be One with the Optimizer
TG
Go to Top of Page

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 04/16/2013 :  10:52:50  Show Profile  Reply with Quote
Hi TG,

Im hoping to get the following (apologies about the format):

Learner_ID,ClientGroupID,historyID,DateStart,DateEnd,Datediff,CertificateDate
22161,1,1,31/08/2011,26/04/2012,239,
22161,1,2,17/09/2012,,,29/02/2012
42457,2,3,31/08/2011,01/10/2011,31,
42457,2,4,17/09/2012,,,18/03/2012

The certificate date i've put against the latest placement against the learner but i don't mind if it was against both learner records. as long as its the same date.

Im trying to play with some CTEs now, still no joy yet



Ketan M Mistry
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/17/2013 :  02:38:19  Show Profile  Reply with Quote
;WITH Cte 
AS (select w.Learner_ID,
       w.[ClientGroupID],
       h.historyID,
		h.DateStart,
		h.DateEnd,
		datediff(d,h.DateStart, h.DateEnd) DaysDiff,
		ROW_NUMBER() OVER(PARTITION BY w.Learner_id ORDER BY h.DateStart) Rn
	from wpr w
		  left join HISTORY H (nolock)
		  on w.Learner_ID = h.Learner_ID
	where H.Category_ID=6
)
SELECT c1.*, c2.DateStart, c2.DateEnd,
CASE WHEN c1.DaysDiff >=182  THEN  DATEADD( DD, 182, MIN(c1.DateStart)OVER(PARTITION BY c1.Learner_id)) 
	WHEN  c1.DaysDiff <182  THEN  DATEADD(DD, 182, MAX(c1.DateStart) OVER(PARTITION BY c1.Learner_id)) END CertificateDate 
FROM Cte c1 LEFT join cte c2 on c1.Learner_ID = c2.Learner_ID AND c1.Rn = c2.Rn+1


--
Chandu
Go to Top of Page

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 04/23/2013 :  11:55:20  Show Profile  Reply with Quote
Hi Chandu,

Thanks for your reply i have just picked this up today as i have been unavailable. This has worked ok but i think i might have confused some way along the line. The Learner may possibly have more than 1 placement.

so need to workout the 182 days from their first start date, How many days they have been in each placement and add

in the example you sent it works fine for learner 22161, and did for 42457.

i have changed the data around again so for learner. I would expect something like 23/05/2013 for learner 42457.

I have reentered some more data.


CREATE TABLE [dbo].[WPR](
	[Learner_ID] [int] NOT NULL,
	[Firstname] [char](30) NULL,
	[Surname] [char](30) NULL,
	[NI] [char](9) NULL,
	[DOB] [smalldatetime] NULL,
	[Datereferral] [smalldatetime] NULL,
	[ClientGroupID] [int] NOT NULL
) 

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

INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral, ClientGroupID) VALUES (22161,'JEFFREY','SMITH','KK991155K','1979-12-28 00:00','2012-08-07 00:00',1)
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral, ClientGroupID) VALUES (42457,	'JO','BLOGGS','PP059991Z','1968-09-28 00:00','2012-06-01 00:00',2)

INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,DateEnd,Category_ID) VALUES (1,22161,	'2012-09-19 00:00','2011-08-31 00:00','2012-04-26 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,Category_ID) VALUES (2,22161,	'2012-08-16 00:00','2012-09-17 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,DateEnd,Category_ID) VALUES (3,42457,	'2012-09-19 00:00','2011-08-31 00:00','2011-10-01 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,DateEnd,Category_ID) VALUES (4,42457,	'2012-09-19 00:00','2012-09-17 00:00','2012-12-17 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,DateEnd,Category_ID) VALUES (5,42457,	'2012-09-19 00:00','2012-12-28 00:00','2013-01-01 00:00',6)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered, DateStart,Category_ID) VALUES (6,42457,	'2012-08-16 00:00','2013-03-29 00:00',6)

select w.Learner_ID,
       w.ClientGroupID,
       h.historyID,
		h.DateStart,
		h.DateEnd,
		datediff(d,h.DateStart, h.DateEnd)
from wpr w
      left join HISTORY H (nolock)
      on w.Learner_ID = h.Learner_ID
where H.Category_ID=6
Order by w.Learner_id



I hope this makes sense

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.08 seconds. Powered By: Snitz Forums 2000