I have data in two tables with dates PAL date and HUB date. I’m looking to show the number of day difference in a table with 6 columns:
| Client | 1day | 2days | 3days | 4days | 5days |
|---|
Client will display the clients grouped, 1 Day will display the count of how many datediff of 1. 2 Days will display the count of how many datediff of 2, and then 3 and then 4 and then 5.
PAL Table:
| Consignment_Number | eventdatetime |
|---|---|
| T00A1A0001000001 | 2021-09-27 12:17:00.000 |
| T00A1A0001000002 | 2021-09-27 12:27:00.000 |
| T00A1A0001000003 | 2021-09-28 13:37:00.000 |
| T00A1A0001000004 | 2021-09-28 13:47:00.000 |
| T00A1A0001000005 | 2021-09-29 13:57:00.000 |
| T00A1A0001000006 | 2021-09-39 14:07:00.000 |
| T00A1A0001000007 | 2021-09-30 14:17:00.000 |
| T00A1A0001000008 | 2021-09-30 14:27:00.000 |
| T00A1A0001000009 | 2021-09-30 14:37:00.000 |
HUB Table:
| Consignment_Number | eventdatetime |
|---|---|
| T00A1A0001000001 | 2021-09-30 18:39:00.000 |
| T00A1A0001000002 | 2021-10-01 18:40:00.000 |
| T00A1A0001000003 | 2021-10-01 18:46:00.000 |
| T00A1A0001000004 | 2021-10-02 18:46:00.000 |
| T00A1A0001000005 | 2021-10-02 18:46:00.000 |
| T00A1A0001000006 | 2021-10-03 18:46:00.000 |
| T00A1A0001000007 | 2021-10-03 18:46:00.000 |
| T00A1A0001000008 | 2021-10-04 18:46:00.000 |
| T00A1A0001000009 | 2021-10-05 18:46:00.000 |
Ultimately it will look something like this:
| Clients | 1day | 2days | 3days | 4days | 5days |
|---|---|---|---|---|---|
| Client1 | 5 | 2 | 3 | 1 | 0 |
| Client2 | 2 | 2 | 2 | 1 | 0 |
| Client3 | 4 | 2 | 5 | 1 | 0 |
Advertisement
Answer
select client , sum(case when abs(datediff(day, Consignment_Number,Consignment_Number)) = 1 then 1 else 0 end) 1day , sum(case when abs(datediff(day, Consignment_Number,Consignment_Number)) = 2 then 1 else 0 end) 2days , sum(case when abs(datediff(day, Consignment_Number,Consignment_Number)) = 3 then 1 else 0 end) 3days , ... , sum(case when abs(datediff(day, Consignment_Number,Consignment_Number)) = 1 then 1 else 0 end) * 100.0 / count(*) 1dayperc , ... from PAL p join HUB h on h.Consignment_Number = p.Consignment_Number group by client