Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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 27th October, the report generated should be of 24th October, wherein we should understand how many challans till 24th October are executed in < 2 days, > 2 days & pending for updates/execution.

Former Member
Not what you were looking for? View more on this topic or Ask a question