I have 2 tables in Sql db and need to match two columns accordingly as given in the below description

Pethealth
Pethealth used Ask the Experts™
on
Table 1: Intake
Table 2: Outcome

Intake Structure
Animal#      Date
A1            2009-01-04
A2            2009-01-01
A3            2009-01-06
A1            2009-01-12
A3            2009-01-09
A4            2009-01-22


Outcome Sturcture
Animal#    Date
A1            2009-01-06
A3            2009-01-08
A2            2009-01-12
A3            2009-01-16
A1            2009-02-05

I need to draw a  Visit table i.e. I need to match for each Animal exactly the number of times INTAKEN and the corresponding OUTCOME

Visit table structure will be as follows:
Animal #     Visit#        DateIn            DateOut
A1                1            2009-01-04      2009-01-06
A1                2            2009-01-12      2009-02-05
A2                1            2009-01-01      2009-01-12
A3                1            2009-01-06      2009-01-08
A3                2            2009-01-09      2009-01-16
A4                1            2009-01-22      

Can some body please help me how to write T-sql script for the above structure.
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
SELECT i.AnimalID, i.[Date] AS DateIn,
      (SELECT COUNT(i2.[Date]) FROM Intake i2 WHERE i2.AnimalID = i.AnimalID AND i2.[Date] <= i.[Date]) AS VisitNum,
      (SELECT MIN(o.[Date]) FROM Outcome o WHERE o.AnimalID = i.AnimalID AND o.[Date] >= i.[Date]) AS DateOut
FROM Intake i
ORDER BY i.AnimalID, i.[Date]

Author

Commented:
Hi matthewspatrick

Thanks very much for the reply !!!

I actually want to show the Visit number (1, 2, 3...) and the corresponding  DateIn and DateOut for each visit.

Currently the DateIn and Dateout are shown identical.

Thanks so much for your help.

pHelth.

Author

Commented:
Currently the visit column showing the total count alternatively it should show the 1st, 2nd ... and so on visit numbers and their DateIn and corresponding DateOut.

Thanks
PHealth.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2010
Commented:
Pethealth,

I ran this SQL as a test, and I came up with exactly what you have in your example.

CREATE TABLE Intake (AnimalID varchar(20), [Date] datetime)

CREATE TABLE Outcome (AnimalID varchar(20), [Date] datetime)

INSERT INTO Intake (AnimalID, [Date])
SELECT 'A1',            '2009-01-04' UNION ALL
SELECT 'A2',            '2009-01-01' UNION ALL
SELECT 'A3',            '2009-01-06' UNION ALL
SELECT 'A1',            '2009-01-12' UNION ALL
SELECT 'A3',            '2009-01-09' UNION ALL
SELECT 'A4',            '2009-01-22'

INSERT INTO Outcome (AnimalID, [Date])
SELECT 'A1',            '2009-01-06' UNION ALL
SELECT 'A3',            '2009-01-08' UNION ALL
SELECT 'A2',            '2009-01-12' UNION ALL
SELECT 'A3',            '2009-01-16' UNION ALL
SELECT 'A1',            '2009-02-05'


SELECT i.AnimalID,
      (SELECT COUNT(i2.[Date]) FROM Intake i2 WHERE i2.AnimalID = i.AnimalID AND i2.[Date] <= i.[Date]) AS VisitNum,
      i.[Date] AS DateIn,
      (SELECT MIN(o.[Date]) FROM Outcome o WHERE o.AnimalID = i.AnimalID AND o.[Date] >= i.[Date]) AS DateOut
FROM Intake i
ORDER BY i.AnimalID, i.[Date]

Regards,

Patrick
Results: 
A1	1	2009-01-04 00:00:00.000	2009-01-06 00:00:00.000
A1	2	2009-01-12 00:00:00.000	2009-02-05 00:00:00.000
A2	1	2009-01-01 00:00:00.000	2009-01-12 00:00:00.000
A3	1	2009-01-06 00:00:00.000	2009-01-08 00:00:00.000
A3	2	2009-01-09 00:00:00.000	2009-01-16 00:00:00.000
A4	1	2009-01-22 00:00:00.000	NULL

Open in new window

Top Expert 2010

Commented:
Please note that the query I used above is identical to that from my first post; the only difference is that
I moved the order of columns in the SELECT clause.

Author

Commented:
Thank you Patrick !!!

It works now, perhaps i made mistake in the first instance but it works fine now.

Thanks so much once again for your help.

Regards
Pet Health
Chris LuttrellSenior Database Architect
Commented:
Try this code using the ROW_NUMBER() function

SELECT I.Animal#, I.Visit, DateIn, DateOut
FROM 
(SELECT Animal#, Date DateIn, ROW_NUMBER() OVER (PARTITION BY Animal# ORDER BY Date ) Visit
FROM Intake) I
LEFT OUTER JOIN (SELECT Animal#, Date DateOut, ROW_NUMBER() OVER (PARTITION BY Animal# ORDER BY Date ) Visit
FROM Outcome) O ON I.Animal# = O.Animal# AND I.Visit = O.Visit

Open in new window

Author

Commented:
Hi CGLuttrell

It's amazing the Row_Number function is so simple and the results were accurate what i was thinking.

Thanks
Pet Health

Chris LuttrellSenior Database Architect

Commented:
Glad to help.
Yes, Row_Number has been available since SQL 2005 and comes in quite handy in cases like this.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
And, just for something different...

SELECT Animalid, ROW_NUMBER() OVER (PARTITION BY Animalid ORDER BY Date ) Visit, Date DateIn,
       (select top 1 Date from outcome where outcome.AnimalID = intake.AnimalID AND outcome.Date >= intake.Date) dateout
FROM Intake
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
and probably should add an order by to make sure of the result set sequencing...

SELECT Animalid, ROW_NUMBER() OVER (PARTITION BY Animalid ORDER BY Date ) Visit, Date DateIn,
       (select top 1 Date from outcome where outcome.AnimalID = intake.AnimalID AND outcome.Date >= intake.Date) dateout
FROM Intake
ORDER BY 1,2
Chris LuttrellSenior Database Architect

Commented:
If you are going to do it by matching on outcome.Date >= intake.Date instead of the second ROW_NUMBER I used, you have to add an ORDER BY Date inside the subselect or selecting top 1 could return a different row than expected.  Row order is not equivalent to insert order and no database will guarantee the order or same order every time unless some sort of ORDER clause is used.  I know Mark knows this but I wanted to state it for those that are learning from our examples.
SELECT Animalid, ROW_NUMBER() OVER (PARTITION BY Animalid ORDER BY Date ) Visit, Date DateIn,
       (select top 1 Date from outcome where outcome.AnimalID = intake.AnimalID AND outcome.Date >= intake.Date ORDER BY Date) dateout
FROM Intake
ORDER BY 1,2

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Yes of course, and was quite remiss of me to exclude it... Thanks CGLutrell for picking up on it :)
Chris LuttrellSenior Database Architect

Commented:
Hi Pethealth,
Are you still having questions on this?  Just trying to followup on my questions from this week to make sure I am not leaving someone hanging.
thanks,
chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial