# Dispatch Pendency Report query

Hi expert

I need help on below query

select cardName as CustomerName, sum(count1) as '< 2 days', sum(count2) as '> 2 days', (sum(count1)+sum(count2)) as 'Total'

from(

SELECT Distinct T0.[CardName], T0.docnum,

CASE WHEN Datediff(day, T0.DocDate,GETDATE())<2 THEN 1 else 0 END 'count1',

CASE WHEN Datediff(day, T0.DocDate,GETDATE())>=2 THEN 1 else 0 END 'count2'

FROM [dbo].[ODLN] T0

Where

T0.DocDate >='20150401' AND (T0.U_Transporter IS NULL )

Group By

T0.CardName, T0.docdate, t0.docnum) a

Group by cardname

union all

select 'GrandTotal' as CustomerName, sum(count1), sum(count2), (sum(count1)+sum(count2)) as 'Total'

from(

SELECT Distinct T0.[CardName], T0.docnum,

CASE WHEN Datediff(day, T0.DocDate,GETDATE())<2 THEN 1 else 0 END 'count1',

CASE WHEN Datediff(day, T0.DocDate,GETDATE())>=2 THEN 1 else 0 END 'count2'

FROM [dbo].[ODLN] T0

Where

T0.DocDate >='20150401' AND (T0.U_Transporter IS NULL )

Group By

T0.CardName, T0.docdate, t0.docnum) a

Order By sum(count2) DESC, sum(count1) DESC

My TAT time is 2 days

a) > 2 days should be calculated by system considering Weekly off’s (Sundays) & declared holidays.

b) The system report should give precise information with the cut-off i.e. If today is 27^{th} October, the report generated should be of 24^{th} October, wherein we should understand how many challans till 24^{th} October are executed in < 2 days, > 2 days & pending for updates/execution.