How can I add a queried variable to my current query?

Question

I hope anyone could help me work this out.

Below is my table and code (which is a little wrong because of one thing..), let me put it part by part to further explain my situation..

Let's say I have this table for Pandimandata2002.dbo.tblCrew:

CaseNo    DatePIConsult    cgoInceptionDate
------    -------------    ----------------
TR12-A    12/03/2012       10/11/2012
TR13-S    11/15/2012       09/15/2012
PO09-B                     08/20/2012
SX58-Q    11/22/2012       11/01/2012
BR88-W    10/05/2012       11/05/2012

and I have this data for PostMeds.dbo.tblpms:

CaseNo    med_stat    med_stateff
------    --------    -----------
BR88-W    FIT         12/01/2012
TR12-A    UNDERTX     03/13/2013
TR13-S    UNDERTX     03/10/2013 
PO09-B    UNDERTX     01/25/2013
TR13-S    FIT         04/01/2013
BR88-W    UNDERTX     11/17/2012

From there, I would like to have this:

CaseNo    DIFF2
------    -----    
BR88-W    57         
TR12-A    122      
PO09-B    227     
TR13-S    137     
SX58-Q    133

• DIFF2 is the difference of two dates (say, DATE1-DATE2).

  DATE1 is equal to DATE NOW (4/4/2013) if CaseNo is not existing in PostMeds.dbo.tblpms

  DATE1 is equal to (max of med_stateff where med_stat is like '%FIT%') of a specific CaseNo

  OR ELSEIF max(med_stateff) is null or line is not existing, DATE1 will be equal also to DATE NOW (4/4/2013)

  DATE2 is equal to cgoInceptionDate if DatePIConsult is NULL

  ELSE DATE2 is equal to DatePIConsult

below is my current query:

SELECT
     t1.CaseNo,
     (SELECT
          CASE WHEN MAX(cast(o.med_stateff as datetime)) IS NULL THEN DATEDIFF(DAY,
               CASE WHEN t1.DatePIConsult IS NULL THEN t1.cgoInceptionDate
               ELSE t1.DatePIConsult END, GETDATE()) 
          WHEN ----(select o.med_stat --------)
       DATEDIFF(DAY,
            CASE WHEN t1.DatePIConsult IS NULL THEN t1.cgoInceptionDate
            ELSE t1.DatePIConsult END,
       MAX(cast(o.med_stateff as datetime))) END AS DIFF2

       FROM PostMeds.dbo.tblpms o

       WHERE t1.CaseNo COLLATE DATABASE_DEFAULT = o.CaseNo COLLATE DATABASE_DEFAULT) AS DIFF2

FROM Pandimandata2002.dbo.tblCrew t1

The wrong part of my code is in the condition for DIFF2..

In there, I would like to add the condition to get the value of med_stat where the value of med_stateff (w/c is aligned with med_stat) is the max value WHERE (or considering) CaseNo is equal to my particular SUBPRIMARYKEY. after I get that value, I have to check if that value is equal to some word ('FIT'), after that I can make the necessary adjustments for my code.

I really hope someone could help me sort this out.. thanks in advance to you guys..

Answer 1

I think I've gathered your word problem into a query that at least gets the results you seem to be after. Well, it's still April 3rd for me, to get the results you wanted I used SYSUTCDATETIME(). You may want to change that to SYSDATETIME() depending on how far away you are from London.

SELECT CaseNo, DIFF2 = DATEDIFF(DAY, DATE2, DATE1)
FROM
(
  SELECT 
    c.CaseNo,
    DATE1 = COALESCE(p.med_stateff, SYSUTCDATETIME()),
    DATE2 = COALESCE(c.DatePIConsult, c.cgoInceptionDate)
  FROM dbo.tblCrew AS c
  LEFT OUTER JOIN 
  (
    SELECT CaseNo, med_stateff = MAX(med_stateff)
      FROM dbo.tblpms 
      WHERE med_stat LIKE '%FIT%'
      GROUP BY CaseNo
  ) AS p
  ON c.CaseNo COLLATE DATABASE_DEFAULT = p.CaseNo COLLATE DATABASE_DEFAULT
) AS x;

Results:

CaseNo   DIFF2
------   -----
TR12-A   122
TR13-S   137
PO09-B   227
SX58-Q   133
BR88-W   57

You'll have to add the ORDER BY because I don't know how you got the order you show in the desired results.