Determine most current date and substract

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

Transact-SQL (2005)

Determine most current date and substract

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-21 : 14:26:17

Hi experts,

This is a tough one.

I have the following fieldnames:

Crt_PrgmArraignmentDate
FirstNewStartDate
secondNewStartDate
Crt_ReadmitDate
DOB

They are all of datetime data types.

My requirement is to determine which of the following 4 fieldnames (Crt_PrgmArraignmentDate,FirstNewStartDate,secondNewStartDate,Crt_ReadmitDate) has the most recent date and
substract DOB from it.

Any ideas how to handle this?

Thanks a lot in advance

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 15:18:48

Hello,

Does the table have a primary key or unique constraint?

If so, Then perhaps something like;


SELECT
	t.PK,  --primary key
	t.DOB,
	d.dt,
	DATEDIFF(dd,t.DOB,d.dt) AS diffInDays	
FROM <yourtable> t
JOIN
(	
	SELECT PK, MAX(dt) AS dt
	FROM
	(
		SELECT
			Crt_PrgmArraignmentDate as dt, 
			PK
		FROM <yourtable>

		UNION

		SELECT
			FirstNewStartDate as dt, 
			PK
		FROM <yourtable>

		UNION

		SELECT
			secondNewStartDate as dt, 
			PK
		FROM <yourtable>

		UNION

		SELECT
			Crt_ReadmitDate as dt, 
			PK
		FROM <yourtable>
	) d
	GROUP BY PK
) d on d.PK = t.PK

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-21 : 16:44:28

Fantastic!!!
Works great. thanks a lot.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-21 : 19:46:23

Sorry to lie to you ehorn but the code doesn't work.

It is supposed to be substracting dob from anyone of the 4 values with the most recent date.

Problem is it works great by substracting from current date.

Any more help would be greatly appreciated.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 19:55:28

Hello simflex,

Can you post your query so we could have a look?

TIA.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-21 : 20:07:33

Thank you ehorn.

Here is the most current code


SELECT
	t.id,  --primary key
	e.DOBirth,
	d.dt,
	DATEDIFF(yy,e.DOBirth,d.dt) AS diffInDays	
FROM tblMaster e,tblEpisode  t
JOIN
(	
	SELECT id, MAX(dt) AS dt
	FROM
	(
		SELECT
			Crt_PrgmArraignmentDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
        WHERE m.id = d.id

		UNION

		SELECT
			FirstNewStartDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
        WHERE m.id = d.id

		UNION

		SELECT
			secondNewStartDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
	    WHERE m.id = d.id

		UNION

		SELECT
			Crt_ReadmitDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
        WHERE m.id = d.id
	) d
	GROUP BY id
) d on d.id = t.id

I made a few changes.

First, I changed dd to yy because we wanted to get the age.

For instance, if someone was born 10/1/1981 and the most recent date of these:
Crt_PrgmArraignmentDate
FirstNewStartDate
secondNewStartDate
Crt_ReadmitDate

is Crt_ReadmitDate, then it will be crt_ReadmitDate - DOB or something like that. That will give the age of that someone.

The other change I made is that there are 2 tables, master and episode.

dob is on the master table the other 4 fieldnames are on the episode table.

the primary key linking them is id.

I don't know which of the changes messed things up.

Thanks againg.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 20:13:10

How are you joining tblMaster e,tblEpisode t?

I do not see a join condition for these two table in the where clause. I do see a join in the derived table in the form of;

WHERE m.id = d.id

Something similar should be present in the outer query. Something like;

WHERE e.id = t.id

So the statement might look like this;

SELECT
	d.id,  --primary key
	e.DOBirth,
	d.dt,
	DATEDIFF(yy,e.DOBirth,d.dt) AS diffInDays	
FROM 
(	
	SELECT id, MAX(dt) AS dt
	FROM
	(
		SELECT
			Crt_PrgmArraignmentDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
        WHERE m.id = d.id

		UNION

		SELECT
			FirstNewStartDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
        WHERE m.id = d.id

		UNION

		SELECT
			secondNewStartDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
	    WHERE m.id = d.id

		UNION

		SELECT
			Crt_ReadmitDate as dt, 
			d.id
		FROM tblMaster m,tblEpisode d
        WHERE m.id = d.id
	) d
	GROUP BY id
) d
JOIN tblMaster e ON e.id = d.id
JOIN tblEpisode  t ON t.id = d.id

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-21 : 20:46:22

sorry, I didn't use this:

JOIN tblMaster e ON e.id = d.id
JOIN tblEpisode t ON t.id = d.id

but I was adding to each like this:

WHERE e.id = t.id and t.id = d.id not just on the outer query but anywhere where I see WHERE e.id = t.id

Many thanks ehorn

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 21:25:40

I see. Thanks for clarifying.

d.dt is the most recent date found among the 4 columns (the union of the inner query). Then we simply use the DATEDIFF function (in whatever datepart you need) to calculate the difference between the most recent date (d.dt) and the DOB (e.DOBirth).

If you are not seeing the results you expect, break it down a bit and examine the data to see if you are pulling the intended values. Then rebuild it step by step. This is how I would approach it. Hope that helps.

Have a nice evening and best wishes.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-21 : 21:48:29

It is working perfectly now.

This time, it is correct.

Many, many thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 22:09:36

yvw,

Glad you got it working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources