3 Months to be added

Ramco Ramco

I have below query . I want if For e.g Current month is say Feb 2025 then i want to add 3 colums Non 24,Dec 24 , Jan 25. I want to get Stock of items in these 3 months.

If current month is Jan 25 then Oct 24 , Nov 24 , Dec 24.

SELECT T0."WhsCode", T0."ItemCode",T0."OnHand"
FROM OITW T0 inner join OITM T1 on T0."ItemCode" = T1."ItemCode"

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari1y ago

Accepted answer

Hello Ramco,

To achieve your requirement, you need to dynamically calculate the three months based on the current month and then retrieve the stock of items for those months. This can be done using SQL by calculating the months dynamically and joining the relevant tables.

Here’s how you can write the query:

DECLARE @CurrentDate DATE = GETDATE(); -- Get the current date
DECLARE @Month1 DATE = DATEADD(MONTH, -2, @CurrentDate); -- 2 months before current month
DECLARE @Month2 DATE = DATEADD(MONTH, -1, @CurrentDate); -- 1 month before current month
DECLARE @Month3 DATE = @CurrentDate; -- Current month

SELECT 
    T0."WhsCode", 
    T0."ItemCode", 
    SUM(CASE WHEN MONTH(T2."DocDate") = MONTH(@Month1) AND YEAR(T2."DocDate") = YEAR(@Month1) THEN T2."Quantity" ELSE 0 END) AS "Non 24",
    SUM(CASE WHEN MONTH(T2."DocDate") = MONTH(@Month2) AND YEAR(T2."DocDate") = YEAR(@Month2) THEN T2."Quantity" ELSE 0 END) AS "Dec 24",
    SUM(CASE WHEN MONTH(T2."DocDate") = MONTH(@Month3) AND YEAR(T2."DocDate") = YEAR(@Month3) THEN T2."Quantity" ELSE 0 END) AS "Jan 25"
FROM 
    OITW T0 
INNER JOIN 
    OITM T1 ON T0."ItemCode" = T1."ItemCode"
LEFT JOIN 
    IGN1 T2 ON T0."ItemCode" = T2."ItemCode" AND T0."WhsCode" = T2."Warehouse"
WHERE 
    T2."DocDate" BETWEEN DATEADD(MONTH, -2, @CurrentDate) AND @CurrentDate
GROUP BY 
    T0."WhsCode", T0."ItemCode";

Good Luck!

Emily Foster1y ago
Based on the information provided, the query is aimed at retrieving the stock of items for specific months as defined based on the current month. To cater to the requirement of fetching stock for the current month and the two months preceding it, adjustments need to be made to the query dynamically based on the current month.

Here's an approach you can consider for dynamically adding the three columns representing the months for which you need stock:

1. Determine the current month. For instance, if the current month is February 2025, the columns would be Non 24, Dec 24, and Jan 25.

2. Modify the SQL query to include the additional columns for the required months based on the current month.

Here's a conceptual example of how the SQL query might look based on the current month scenario you provided:
```
SELECT 
    T0."WhsCode", 
    T0."ItemCode",
    T0."OnHand",
    -- Calculate the month names dynamically based on current month
    CASE 
        WHEN MONTH(GETDATE()) = 2 THEN T0.Non_24_Column
        WHEN MONTH(GETDATE()) = 1 THEN T0.Dec_24_Column
        ELSE T0.Jan_25_Column
    END AS CurrentMonthStock
FROM OITW T0 
INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
```
By incorporating this dynamic approach, you can adjust the query to fetch the stock information for the current month and the two months preceding it, ensuring flexibility and accuracy in retrieving the required data.
0