on 10-28-2015 10:12 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
User | Count |
---|---|
89 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.