3 Months to be added

Question

Hi

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"

Muhammad Imran Ansari · 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 Foster · Answer

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.