Calculate days difference between two dates from two queries conslidated by union

Question

I have two queries with a union all between them. I am trying to calculate the differences between two dates, one of them in the 1st query, and the other is in the 2nd query.

The 1st date in the 1st query is t0.duedate, the 2nd date in the 2nd query is t0.docdate. I'll use this query in crystal report where i'll put a condition to order the resualt based on the field t0.[u_a_id] which available in both queries the resualt right now is like this

 DocENTRY   slpname DocNum  SeriesName  Series  DocType DocDate DocdueDate  CardCode    CardName    DocTotal    u_a_id  type
1               -   1   Primary 12  C   2006-01-31 00:00:00.000 2006-01-31 00:00:00.000 C20000  Norm Thompson   14023.800000    NULL    A/R INCOMING PAYMENT

323 Sales Manager   323 Primary 1   I   2012-06-09 00:00:00.000 2012-07-09 00:00:00.000 C20000  Maxi-Teq    4253.250000 NULL    A/R INVOICE

i'll need to have one more column need Days Difference which will calculate the diffrences between days of the two dates "2012-07-09 - 2006-01-31" like this:

Days Difference 
 1921 Day

Here is the query :

 SELECT DISTINCT
        t0.[DocENTRY] ,
        oslp.slpname ,
        t0.[DocNum] ,
        nnm1.SeriesName ,
        t0.[Series] ,
        t0.[DocType] ,
        t0.[DocDate] ,
        t0.[DocdueDate] ,
        t0.[CardCode] ,
        t0.[CardName] ,
        t0.[DocTotal] ,
        t0.[u_a_id] ,
        'A/R INVOICE' AS type
FROM    OINV t0
        INNER JOIN NNM1 ON nnm1.series = t0.Series
        INNER JOIN OSLP ON oslp.slpcode = t0.slpcode
WHERE   t0.DOCSTATUS = 'C'
UNION ALL
SELECT DISTINCT
        t0.[DocENTRY] ,
        '-' AS slpname ,
        t0.[DocNum] ,
        nnm1.SeriesName ,
        t0.[Series] ,
        t0.[DocType] ,
        t0.[DocDate] ,
        t0.[DocdueDate] ,
        t0.[CardCode] ,
        t0.[CardName] ,
        t0.[DocTotal] ,
        t0.[u_a_id] ,
        'A/R INCOMING PAYMENT' AS type
FROM    orct t0
        INNER JOIN NNM1 ON nnm1.series = t0.Series
ORDER BY t0.[CardCode]

Answer 1

By using a nested query to bring the date from the 2nd query to the 1st query , then in CR made a formula to calculate the different between the dates , query now after update it :

select distinct T0.[DocENTRY],

T0.[DocNum],nnm1.SeriesName, T0.[Series],oslp.SlpName,

 T0.[DocDate] as 'Invoice Date',T0.[DocDueDate], T0.[CardCode], 

 T0.[CardName],  T0.[DocTotal], T0.[Comments], 

  'A/R invoice' as type, t0.U_A_ID,
  (select docdate from ORCT where orct.U_A_ID = t0.U_A_ID)
   as PaymentDt


from oinv t0 

inner join  NNM1 on nnm1.series =t0.Series 

inner join OSLP on oslp.SlpCode =t0.SlpCode

where T0.DocStatus ='c'

union all 

select distinct T0.[DocENTRY],

T0.[DocNum],nnm1.SeriesName, T0.[Series],

'_' AS SlpName,  T0.[DocDate], 

T0.[DocDueDate] AS 'Payment Due Date',

 T0.[CardCode], T0.[CardName],  T0.[DocTotal],

  T0.[Comments], 

 'A/R Incoming Payment' as type, t0.U_A_ID , getdate() as paymentdt

from ORCT t0 


inner join  NNM1 on nnm1.series =t0.Series 


order by t0.CardCode,t0.u_A_id

and the formula used in CR is :

 DATEDIFF("d",{Command.PaymentDt},{Command.DocDueDate})