I want to show records from in 1 row

Table 1 (history data)

SiteName    OutcomeType  SpeciesType     Count  DateType
-------------------------------------------------------------
S1          Adopted         Dog        开发者_如何学运维     3       0
S2          Adopted         Cat             12      0
S1          Transferred     Puppy           2       0
S1          Transferred     Dog             5       0

Table 2 (Current data)

SiteName    OutcomeType     SpeciesType     Count   DateType
--------------------------------------------------------------
S1          Adopted         Dog             9       1
S2          Adopted         Dog             10      1
S1          Transferred     Dog             12      1

** DateType: 0-indicates history date, 1-Current date

I merged the above 2 tables data in 1 table and called it as Master table(which is temp table in the stored procedure). Which will recemble as below.

SiteName    OutcomeType     SpeciesType     Count    DateType
-------------------------------------------------------------
S1          Adopted         Dog             3        0
S2          Adopted         Cat             12       0
S1          Adopted         Dog             9        1
S2          Adopted         Dog             10       1
S1          Transferred     Puppy           2        0
S1          Transferred     Dog             5        0
S1          Transferred     Dog             12       1

Here basically I am trying to compare the data against history and current

the user will enter the 2 dates (eg: Oct2009, Oct2010)

All the data is now fetched in 1 table as shown above.

As noticed from the master table (temp table in the Stored procedure)

there is one Species Type: Puppy for site S1 and it is from history date.

how can I match that record from the current table records (where in Species type Puppy does not exist).

I want to show the above records in 1 row as shown below.

SiteName1 OutecomeType1 AgeSpecies1 Count1  History SiteName2 OutcomeType2 AgeSpecies2  Count2  Current Difference
S1  Adopted Dog 3   yes S1  Adopted Dog 9   yes 6
S1  Transferred Puppy   2   yes -   -   -   -   -   -
S2  Adopted Cat 12  yes -   -   -   -   -   -
S1  Transferred Dog 5   yes S1  Transferred Dog 12  yes 7
-   -   -   -   -   S2  Adopted Dog 10  yes

10

---

I would write a query for each table to get the basic counts from that table to use as derived tables. Then I would use a full outer join to join the two tables together on the sitename. Then just pull the fields you want from each derived table. Just make sure your derived table queries only pull one row each for each siteName.