joining a pivot with another complex table

Question

my pivot query generates:

+-----------+----+----+---+---+---+---+---+
| client_id | 1  | 2  | 3 | 4 | 5 | 6 | 7 |
+-----------+----+----+---+---+---+---+---+
|    216436 |  9 |  0 | 0 | 0 | 0 | 0 | 0 |
|    110522 | 76 |  3 | 0 | 0 | 0 | 0 | 0 |
|    214981 |  0 |  1 | 0 | 0 | 0 | 0 | 0 |
|    216360 | 52 |  1 | 0 | 0 | 0 | 0 | 0 |
|    102574 |  1 |  0 | 0 | 0 | 0 | 0 | 0 |
|    211754 | 97 | 14 | 2 | 0 | 0 | 0 | 0 |
|    210734 |  8 |  4 | 0 | 0 | 0 | 0 | 0 |
|    100123 |  1 |  0 | 0 | 0 | 0 | 0 | 0 |
|    101840 |  2 |  0 | 0 | 0 | 0 | 0 | 0 |
+-----------+----+----+---+---+---+---+---+

here's the query:

   select client_id,
   [1],[2],[3],[4],[5],[6],[7] -- these are timestested (the amount of times tested)
   from
   (   SELECT DISTINCT CLIENT_ID
   , PATIENT_ID
   , count(*) over (partition by client_id, patient_id) AS patientcount

   from f_accession_daily) as SourceTable
   PIVOT
   (
   count(patient_id)
   for patientcount in ([1],[2],[3],[4],[5],[6],[7])
   ) as pivottable

I need to bring the max/min dates for every time tested, (for [1], [2], [3], etc) from this table:

+-----------+-------------+-------+------------+------------+
| client_id | TimesTested | count | maxRecDate | minRecDate |
+-----------+-------------+-------+------------+------------+
|    100034 |           2 |     1 | 6/25/2008  | 6/23/2008  |
|    100034 |           1 |    20 | 6/30/2008  | 6/19/2008  |
|    100038 |           3 |     1 | 7/25/2008  | 7/23/2008  |
|    100038 |           1 |     4 | 7/25/2008  | 7/1/2008   |
|    100050 |           1 |    15 | 8/11/2008  | 7/14/2008  |
|    100060 |           1 |     2 | 8/12/2008  | 7/29/2008  |
|    100070 |           1 |     3 | 8/15/2008  | 8/15/2008  |
|    100049 |           1 |     3 | 8/22/2008  | 7/11/2008  |
|    100029 |           3 |     2 | 8/25/2008  | 6/18/2008  |
+-----------+-------------+-------+------------+------------+

the above table is generated by:

SELECT a.client_id AS client_id
,a.patientcount TimesTested
   , count(a.patientcount)/a.patientcount AS count
   , max(f.received_date) AS maxRecDate
   , min(f.received_date) AS minRecDate
FROM
(
   SELECT DISTINCT CLIENT_ID
   , PATIENT_ID
   , count(*) over (partition by client_id, patient_id) AS patientcount

   from f_accession_daily

) AS a
JOIN F_ACCESSION_DAILY AS f ON a.CLIENT_ID = f.CLIENT_ID
   AND a.PATIENT_ID = f.PATIENT_ID

GROUP BY a.CLIENT_ID, a.patientcount

the resulting table that i need to get:

+-----------+----+----------+-----------+----+----------+-----------+---+----------+-----------+---+----------+-----------+-----+
| client_id | 1  | maxdate1 | mindate1  | 2  | maxdate2 | mindate2  | 3 | maxdate3 | mindate3  | 4 | maxdate4 | mindate4  |  5  |
+-----------+----+----------+-----------+----+----------+-----------+---+----------+-----------+---+----------+-----------+-----+
|    216436 |  9 | 1/1/2011 | 1/23/1985 |  0 | 1/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1985 | etc |
|    110522 | 76 | 1/1/2011 | 1/23/1984 |  3 | 1/1/2011 | 1/23/1984 | 0 | 1/1/2011 | 1/23/1984 | 0 | 2/1/2011 | 1/23/1984 |     |
|    214981 |  0 | 1/1/2013 | 1/23/1985 |  1 | 1/1/2013 | 1/23/1985 | 0 | 1/1/2013 | 1/23/1985 | 0 | 1/1/2013 | 1/23/1985 |     |
|    216360 | 52 | 1/1/2011 | 1/23/1985 |  1 | 1/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1985 |     |
|    102574 |  1 | 1/1/2011 | 1/23/1985 |  0 | 1/1/2014 | 1/23/1980 | 0 | 2/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1985 |     |
|    211754 | 97 | 1/1/2012 | 1/23/1985 | 14 | 1/1/2012 | 1/23/1985 | 2 | 1/1/2012 | 1/23/1985 | 0 | 1/1/2012 | 1/23/1985 |     |
|    210734 |  8 | 1/1/2011 | 1/23/1984 |  4 | 1/1/2011 | 1/23/1984 | 0 | 1/1/2011 | 1/23/1984 | 0 | 1/1/2011 | 1/23/1984 |     |
|    100123 |  1 | 1/1/2011 | 1/23/1985 |  0 | 1/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1987 |     |
|    101840 |  2 | 1/1/2011 | 1/23/1985 |  0 | 1/1/2011 | 1/23/1980 | 0 | 1/1/2011 | 1/23/1985 | 0 | 1/1/2011 | 1/23/1985 |     |
+-----------+----+----------+-----------+----+----------+-----------+---+----------+-----------+---+----------+-----------+-----+

how do i join the two tables? the speed does not matter! thank you very much for your kind help.

Answer 1

Not the prettiest, but I left your original queries intact and put it all in one [big] statement:

;WITH 
PivotQuery as (
    select client_id,
       [1],[2],[3],[4],[5],[6],[7]
       from
       (   SELECT DISTINCT CLIENT_ID
       , PATIENT_ID
       , count(*) over (partition by client_id, patient_id) AS patientcount

       from f_accession_daily) as SourceTable
       PIVOT
       (
       count(patient_id)
       for patientcount in ([1],[2],[3],[4],[5],[6],[7])
       ) as pivottable),

MinMaxTimes as (
    SELECT a.client_id AS client_id
    ,a.patientcount TimesTested
       , count(a.patientcount)/a.patientcount AS count
       , max(f.received_date) AS maxRecDate
       , min(f.received_date) AS minRecDate
    FROM
    (
       SELECT DISTINCT CLIENT_ID
       , PATIENT_ID
       , count(*) over (partition by client_id, patient_id) AS patientcount

       from f_accession_daily

    ) AS a
    JOIN F_ACCESSION_DAILY AS f ON a.CLIENT_ID = f.CLIENT_ID
       AND a.PATIENT_ID = f.PATIENT_ID

    GROUP BY a.CLIENT_ID, a.patientcount),

maxDates as (
SELECT client_id, [1] maxdate1, [2] maxdate2, [3] maxdate3, [4] maxdate4, [5] maxdate5, [6] maxdate6, [7] maxdate7
FROM MinMaxTimes t
PIVOT (max(maxRecDate)
for TimesTested IN ([1], [2], [3], [4], [5], [6], [7])
) as p),

minDates as (
SELECT client_id, [1] mindate1, [2] mindate2, [3] mindate3, [4] mindate4, [5] mindate5, [6] mindate6, [7] mindate7
FROM MinMaxTimes t
PIVOT (max(minRecDate)
for TimesTested IN ([1], [2], [3], [4], [5], [6], [7])
) as p)

SELECT p.client_id, 
    p.[1], max(maxdate1) maxdate1, max(mindate1) mindate1,
    p.[2], max(maxdate2) maxdate2, max(mindate2) mindate2,
    p.[3], max(maxdate3) maxdate3, max(mindate3) mindate3,
    p.[4], max(maxdate4) maxdate4, max(mindate4) mindate4,
    p.[5], max(maxdate5) maxdate5, max(mindate5) mindate5,
    p.[6], max(maxdate6) maxdate6, max(mindate6) mindate6,
    p.[7], max(maxdate7) maxdate7, max(mindate7) mindate7   
FROM PivotQuery p
LEFT OUTER JOIN maxDates a ON p.client_id = a.client_id
LEFT OUTER JOIN mindates i ON a.client_id = i.client_id
GROUP BY p.client_id, p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7]