Skip to Content
avatar image
Former Member

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 28, 2015 at 11:07 AM

    Hi,

    Please have a look at the sql WEEKDAY() function for weekend days.

    Holidays are entirely location specific, and some holidays may or may not be regarded as  business days. For this purpose you should create a user defined table, and enter the dates that are relevant to your scenario.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • So basically ur question became a question about how to calculate workdays between two dates.

      Based on my knowledge, the most complicated solution actually is to generate a customized table having two two columns.

      1. Date

      2. Holiday flag. Y/N or 1/0

      This table need to be pre-calculated. The benefit for this approach is u can actually consider other holiday such as the coming thanksgiving.

      Once you pre-calculated the table, the next step is much easy.

      SELECT COUNT(*) FROM HOLIDAY WHERE D BETWEEN D1 AND D2 AND HOLIDAY_FLAG = 1

      This query will give u the answer.

      Frank