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)
 Working with Dates (Dateadd??)

Author  Topic 

ketanmmistry
Starting Member

8 Posts
Posted - 2013-04-16 : 09:17:15
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
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2013-04-16 : 10:20:59
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

8 Posts
Posted - 2013-04-16 : 10:52:50
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
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-17 : 02:38:19
[code];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[/code]

--
Chandu
Go to Top of Page

ketanmmistry
Starting Member

8 Posts
Posted - 2013-04-23 : 11:55:20
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site