cancel
Showing results for 
Search instead for 
Did you mean: 

Dispatch Pendency Report query

0 Kudos

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

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

0 Kudos

Thanks for reply.

How do I use weekday() in above query.

It least I can consider all Sundays.

Johan_H
Active Contributor
0 Kudos

Hi,

I noticed that the WEEKDAY() function is specific to certain software. It will not work in MS SQL. However, you can also use the DATEPART() or DATENAME() functions in a similar way.

I am not sure what your query is supposed to tell us, nor how Sundays and holidays are related to that information.

Could you please explain in a little more detail, what information your query shows ?

Regards,

Johan

0 Kudos

Hi,

The objective here is number of pending deliveries within 2 days and over two days from posting date.

If the transportation details not updated in UDF (T0.U_Transporter) it means material not get delivered from warehouse.

The delivery TAT is 2 days so I want to separate within 2 days number of deliveries and more than 2 days deliveries  from posting date of delivery which I have done in this query.

Now I don’t want to count Sundays because of its non-working day.

Johan_H
Active Contributor
0 Kudos

Hi,

Ok, I think I understand what you mean. It will be pretty complicated though. We will need to adapt this part of the query: Datediff(day, T0.DocDate,GETDATE())

This gives us a certain amount of days. Could this value be negative ?

We probably have to build a loop that checks if each day between GETDATE() and T0.DocDate is a sunday or not. It maybe easiest to create an sql function for this purpose that returns an integer.

Please use this example of a loop to build a solution and let us know if we can help:

DECLARE @I AS INT = 3

WHILE @I > 0

BEGIN

PRINT DATEPART(dw, DATEADD(DAY, @I, GETDATE()))

            + ' ' +

            DATENAME(dw, DATEADD(DAY, @I, GETDATE()))

SET @I -= 1

END

It will return the day of the week as an integer, and a description so you can determine which days is Sunday.

Regards,

Johan

frank_wang6
Active Contributor
0 Kudos

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