Hi. I'm a SQL beginner so, It´s very confusing to me create a query with the next specs:
- The user submits a year. This must be a variable declared in my code. What var type can be more suitable for this purpose? I was thinking of INT but I have problems converting to DATE.
- Given the year, I need to group initial and final inventories for each month, sales, and purchases of each month too.
- In each column, I have to show a result from a formula that uses initial and final inventories for each month, sales, and purchases of each month.
The query has to be similar to the next image:
I started to declare my variables in this way:
declare @begin DATE
declare @end DATE
set @begin = '2021'
set @end = DATEADD(YEAR, 1, @begin)
Later, I have to join inventories, sales, and purchases to my base table to do my calculations, but I have problems grouping the data.
The initial and final inventories for each month are not correct. I'm doing in this way:
--JOIN TO ADD INVENTORIES FOR EACH MONTH
LEFT JOIN --JOIN TO ADD INITIAL INVENTORIES
(SELECT C_ID, MONTH(MIN(STARTDATE)) AS STARTDATE, SUM(START_INV) AS START_INV, MAX(ENDDATE) AS ENDDATE FROM #MAB_INVENTORY_TEMP WHERE STARTDATE = @EMPEZAR GROUP BY C_ID, MONTH(STARTDATE)) INI_INV
ON c.C_ID = INI_INV.C_ID
LEFT JOIN -- JOIN TO ADD END INVENTORIES
(SELECT C_ID, MONTH(MIN(STARTDATE)) AS STARTDATE, SUM(END_INV) AS END_INV, MAX(ENDDATE) AS ENDDATE FROM #MAB_INVENTORY_TEMP WHERE STARTDATE = @TERMINAR GROUP BY C_ID, MONTH(STARTDATE)) END_INV
ON c.C_ID = END_INV.C_ID
The initial inventories just bring me data from January and the rest of the months not. Also, the final inventories are not correct, because bring me just NULL. I am really frustrated dealing to get the correct inventories, I appreciate it if any of you take a look to help me.
I don´t have any issues grouping sales and purchases. My nightmare is the inventories.
Any idea how can I code these joins?
Finally, I would like an idea of how I can present my data, maybe PIVOT function applies. Currently, all the months are showing in a column for each category (initial, end inventories, sales, and purchases)
Thanks in advance for your help.
rolling
