asked on

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

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

Patrick Matthews

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]

Pethealth

ASKER

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.

Pethealth

ASKER

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.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

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.

Pethealth

ASKER

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

SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pethealth

ASKER

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 Luttrell

Glad to help.
Yes, Row_Number has been available since SQL 2005 and comes in quite handy in cases like this.

Mark Wills

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 Wills

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 Luttrell

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 Wills

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

Chris Luttrell

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