Finding most recent date from current date

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

General SQL Server Forums

New to SQL Server Programming

Finding most recent date from current date

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-21 : 08:03:50

Hi

This is a follow up from an earlier post where the work I was doing has changed, the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:

So for instance looking at a copy of one person's date below I have:


PersonID        Last Dept                               Arrival Date To Last Dept     Leaving Date From Last Dept  Next Arrival Date to Unscheduled Dept
========        =================                       =========================     =========================== =====================================
0106573268	Dept 5	                                2013-03-01 	                  2013-03-03	                       2013-03-05 
0106573268	Dept 6                    	        2013-02-27 	                  2013-02-27 	                       2013-03-05
0106573268	dept 2	                                2013-02-26 	                  2013-02-26 	                       2013-03-05

In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.

My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:


SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept" 
,b.leaving_Date AS "leaving Date From Last dept", 
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
                                       FROM scheduled_Leaves
				       where [leaving date] is not null) b 
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] >= b.[leaving date]   
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
order by a.Person_ID

Hopefully someone can point out what I need to do, I reckon it's something to do with MAX date or something like that.

Thanks for any suggestions

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 08:48:09

[code]
SELECT personID,
[Last leaving dept],
[arrival Date To Last dept],
[Next arrival Date to AREA_GH]
FROM
(
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
,ROW_NUMBER() OVER (PARTITION BY b.PersonID ORDER BY b.leaving_Date DESC) AS Seq
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] >= b.[leaving date]
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
)t
where Seq=1
order by Person_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-21 : 09:20:36

I now get this:


PersonID        Last Dept                               Arrival Date To Last Dept     Leaving Date From Last Dept  Next Arrival Date to Unscheduled Dept
========        =================                       =========================     =========================== =====================================
0106573268	Dept 5	                                2013-03-05 	                  2013-03-05	                       2013-03-05

But am trying to get:


PersonID        Last Dept                               Arrival Date To Last Dept     Leaving Date From Last Dept  Next Arrival Date to Unscheduled Dept
========        =================                       =========================     =========================== =====================================
0106573268	Dept 5	                                2013-03-01 	                  2013-03-03	                       2013-03-05

Maybe a slight change to the query but can't see what

Thanks

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 09:36:45

do you mean this?


SELECT personID,
[Last leaving dept],
[arrival Date To Last dept],
[Next arrival Date to AREA_GH]
FROM
(
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept" 
,b.leaving_Date AS "leaving Date From Last dept", 
a.[arrival Date] as "Next arrival Date to AREA_GH"
,ROW_NUMBER() OVER (PARTITION BY b.PersonID ORDER BY b.leaving_Date DESC) AS Seq
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
                                       FROM scheduled_Leaves
				       where [leaving date] is not null) b 
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] > b.[leaving date]   
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
)t
where Seq=1
order by Person_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-21 : 09:38:23

If I select the data from inline view and filtering by the person ID I get:


0106573268	dept2		 2013-06-21 	2013-06-21 	 2013-03-05 	1
0106573268	dept2		 2013-06-20 	2013-06-20 	 2013-03-05 	2
0106573268	dept2		 2013-06-19 	2013-06-19 	 2013-03-05 	3
0106573268	dept2		 2013-06-18 	2013-06-18 	 2013-03-05 	4
0106573268	dept2		 2013-06-17 	2013-06-17 	 2013-03-05 	5
0106573268	dept2		 2013-06-11 	2013-06-11 	 2013-03-05 	6
0106573268	dept2		 2013-05-24 	2013-05-24 	 2013-03-05 	7
0106573268	dept2		 2013-05-23 	2013-05-23 	 2013-03-05 	8
0106573268	dept2		 2013-05-22 	2013-05-22 	 2013-03-05 	9
0106573268	dept2		 2013-05-21 	2013-05-21 	 2013-03-05 	10
0106573268	dept2		 2013-05-20 	2013-05-20 	 2013-03-05 	11
0106573268	dept2		 2013-05-16 	2013-05-16 	 2013-03-05 	12
0106573268	dept2		 2013-05-14 	2013-05-14 	 2013-03-05 	13
0106573268	dept2		 2013-04-25 	2013-04-25 	 2013-03-05 	14
0106573268	dept2		 2013-04-23 	2013-04-23 	 2013-03-05 	15
0106573268	dept2		 2013-04-22 	2013-04-22 	 2013-03-05 	16
0106573268	dept2		 2013-04-16 	2013-04-16 	 2013-03-05 	17
0106573268	dept2		 2013-03-29 	2013-03-29 	 2013-03-05 	18
0106573268	dept2		 2013-03-28 	2013-03-28 	 2013-03-05 	19
0106573268	dept2		 2013-03-27 	2013-03-27 	 2013-03-05 	20
0106573268	dept2		 2013-03-26 	2013-03-26 	 2013-03-05 	21
0106573268	dept2		 2013-03-25 	2013-03-25 	 2013-03-05 	22
0106573268	dept2	         2013-03-19 	2013-03-19 	 2013-03-05 	23
0106573268	Unscheduled dept 2013-03-05 	2013-03-05 	 2013-03-05 	24
0106573268	dept2		 2013-03-01 	2013-03-03 	 2013-03-05 	25
0106573268	dept4		 2013-02-27 	2013-02-27 	 2013-03-05 	26
0106573268	dept4		 2013-02-26 	2013-02-26 	 2013-03-05 	27
0106573268	dept4		 2013-02-25 	2013-02-25 	 2013-03-05 	28
0106573268	dept4		 2013-02-19 	2013-02-19 	 2013-03-05 	29
0106573268	dept2 	         2013-02-01 	2013-02-02 	 2013-03-05 	30

I am trying to return the line in bold but currently am returning the line above for the unscheduled dept visit.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-21 : 09:40:17

quote:

Originally posted by visakh16

do you mean this?


SELECT personID,
[Last leaving dept],
[arrival Date To Last dept],
[Next arrival Date to AREA_GH]
FROM
(
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept" 
,b.leaving_Date AS "leaving Date From Last dept", 
a.[arrival Date] as "Next arrival Date to AREA_GH"
,ROW_NUMBER() OVER (PARTITION BY b.PersonID ORDER BY b.leaving_Date DESC) AS Seq
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
                                       FROM scheduled_Leaves
				       where [leaving date] is not null) b 
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] > b.[leaving date]   
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
)t
where Seq=1
order by Person_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

If I remove that condition i still get the same line of data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 09:42:42

quote:

Originally posted by Grifter

quote:

Originally posted by visakh16

do you mean this?


SELECT personID,
[Last leaving dept],
[arrival Date To Last dept],
[Next arrival Date to AREA_GH]
FROM
(
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept" 
,b.leaving_Date AS "leaving Date From Last dept", 
a.[arrival Date] as "Next arrival Date to AREA_GH"
,ROW_NUMBER() OVER (PARTITION BY b.PersonID ORDER BY b.leaving_Date DESC) AS Seq
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
                                       FROM scheduled_Leaves
				       where [leaving date] is not null) b 
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] > b.[leaving date]   
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
)t
where Seq=1
order by Person_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

If I remove that condition i still get the same line of data

i didnt ask you to remove. I just suggested to change >= to > as the operator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-21 : 09:43:53

quote:
Originally posted by visakh16

quote:
Originally posted by Grifter

quote:
Originally posted by visakh16

do you mean this?

SELECT personID,
[Last leaving dept],
[arrival Date To Last dept],
[Next arrival Date to AREA_GH]
FROM
(
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
,ROW_NUMBER() OVER (PARTITION BY b.PersonID ORDER BY b.leaving_Date DESC) AS Seq
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] > b.[leaving date]
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
)t
where Seq=1
order by Person_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

If I remove that condition i still get the same line of data

i didnt ask you to remove. I just suggested to change >= to > as the operator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Right that seems to work so far - appreciate the help very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 09:48:04

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-21 : 09:52:44

Hope you don't mind me asking but why is it that when I keep the ">=" instead of ">" in the date filter that it only shows the one row - the unscheduled arrival. It'd of thought it would show the unscheduled arrival and all the scheduled ones before it i.e. larger than or equal to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 14:55:25

thats because the logic we put just retrieves you one with latest b.leaving_Date. So if you've > = it will always be the = valued record ie unscheduled arrival which will have latest leaving_date value and will get retrived.
If you attempt was to get unscheduled arrival and just preceding scheduled arrival then use logic like


SELECT personID,
[Last leaving dept],
[arrival Date To Last dept],
[Next arrival Date to AREA_GH]
FROM
(
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept" 
,b.leaving_Date AS "leaving Date From Last dept", 
a.[arrival Date] as "Next arrival Date to AREA_GH"
,ROW_NUMBER() OVER (PARTITION BY b.PersonID ORDER BY b.leaving_Date DESC) AS Seq
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
                                       FROM scheduled_Leaves
				       where [leaving date] is not null) b 
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] >= b.[leaving date]   
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
)t
where Seq<=2
order by Person_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-21 : 15:09:44

Which version of SQL Server are you using?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources