Ageing for stock code shipping dates

Transact-SQL
1

the following represents my table:

|StockCode|LineShipDate|Qty|

|stock1|31-Aug-20|100|
|Stock1|01-Sep-20|200|
|stock1|01-Sep-20|7|
|Stock1|01-Sep-20|20|
|Stock1|01-Oct-23|100|
|stock1|01-Nov-20|200|
|Stock2|01-Sep-20|34|
|Stock2|01-Nov-20|23|
|Stock3|31-Aug-20|50|
|Stock4|01-Nov-20|50|
|Stock4|01-Nov-20|50|

The table displays all items with ship date and qty.

We would like to know how much qty per each stock code we will need to fulfill in the near future. For this I have the ageing buckets.
Current is not to two weeks period , more than 2 weeks, over 4 weeks, and everything over 6 weeks.
In order to achieve that i would need to convert data to the following format:

image

Can I have some help in creating a view to display the above? thanks

2

I don't have data to test it with, but this should be very close at least:

SELECT
    StockCode,
    SUM(CASE WHEN DaysAge < 15 THEN Qty ELSE 0 END) AS [Current],
    SUM(CASE WHEN DaysAge >= 15 AND DaysAge < 29 THEN Qty ELSE 0 END) AS Over2Weeks,
    SUM(CASE WHEN DaysAge >= 29 AND DaysAge < 43 THEN Qty ELSE 0 END) AS Over4Weeks,
    SUM(CASE WHEN DaysAge >= 43 THEN Qty ELSE 0 END) AS Over6Weeks
FROM dbo.table_name
CROSS APPLY (
    SELECT DATEDIFF(DAY, GETDATE(), LineShipDate) AS DaysAge
) AS calc1
GROUP BY StockCode
ORDER BY StockCode
1 Like
3

this works great. however i just realized that adding warehouse would be beneficial since some items ship from different warehouse.

image
image1491×375 13.6 KB

4

Again, I have NO usable data to test with, so I'm not going to be much help beyond what I've already posted.

5

Thank you very much i was able to solve this issue. I will post data if i need more help.
For future .... excel list of data is oK or is there a better method to post the data?

6

Best is create table with INSERT statement(s):

CREATE TABLE dbo.table_name (
    col_name1 int NOT NULL,
    col_name2 varchar(30)  NULL,
    ...
)
INSERT INTO dbo.table_name VALUES
    (1, 'abc'),
    (2, 'def'),
    (3, 'ghi'),
    ...
7

I always used CTE ,
Now I will always used cross apply!!!