SQLTeam.com | Weblogs | Forums

Grouping by Month

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.

Welcome,

We do not have access to you database and our telepathic skills are non existent, can you please provide us with sample data. Here is one way

create table #products(id int, name nvarchar(50))

insert into #products
select 1, 'Empenadas'

--we have no idea what columns are in 
--the below table and their data types
create table #inventories

drop table #products
drop table #inventories

This gives us an eye into your database, it is a sampling. Help us help you

something to get the :8ball: rolling

use sqlteam
go

create table #products(id int, name nvarchar(50))

insert into #products
select 1, 'Empenadas'


create table #inventories(productid int, STARTDATE date, ENDDATE date, END_INV int)

insert into #inventories
select distinct top 10000  1, DATEADD(month, DATEDIFF(month, 0, a.create_date), 0), 
EOMONTH(b.create_date), a.object_id
  from sys.all_objects a
  cross apply sys.all_objects b
 where a.object_id between 1 and 10000

 select DATENAME(mm,STARTDATE) from #inventories

 select *
FROM  
(
	SELECT productid, DATENAME(month, STARTDATE) as _monthName, sum(END_INV)  as inv  
	FROM #inventories
	group by productid, STARTDATE
) AS src  
PIVOT  
(  
	SUM(inv)  
	FOR _monthName IN ([January], [February],[March],[April],[May], [June], [July], [August], [2022], [2023], [2024], [2025])  
) AS pt;  


drop table #products
drop table #inventories

Hi!
My query is:

declare @begin DATE
declare @finish DATE
set @begin = '2021'
set @finish = DATEADD(YEAR, 1, @begin)


--START REPORT QUERY

SELECT
c_id as PRODUCT

-- HERE I HAVE TO SET A CALCULATION USING INITIAL AND FINAL INVENTORIES, SALES AND PURCHASES OF EACH MONTH/PRODUCT OF OUR DATABASE

FROM COMPONENT c -- BASE TABLE

--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 INVENTORY WHERE STARTDATE = @begin 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 INVENTORY WHERE STARTDATE = @finish GROUP BY C_ID, MONTH(STARTDATE)) END_INV
ON c.C_ID = END_INV.C_ID

--JOIN TO ADD SALES FOR EACH MONTH
LEFT JOIN
(SELECT C_ID, MONTH(STARTDATE) AS MES, SUM(REPORT_DELTA) AS SUM_REPORT_DELTA FROM SALES WHERE O_CAT_TYPE IN ('DESK', 'NOTEBOOK', 'BOOKS') AND STARTDATE >= @begin AND ENDDATE <= @finish GROUP BY C_ID, MONTH(STARTDATE)) SALES
ON c.C_ID = SALES.C_ID

LEFT JOIN TO ADD PURCHASES FOR EACH MONTH
(SELECT C_ID, MONTH(STARTDATE) AS MES, SUM(REPORT_DELTA) AS SUM_REPORT_DELTA FROM PURCHASES WHERE O_CAT_TYPE = 'BOOKS' AND STARTDATE >= @begin AND ENDDATE <= @finish GROUP BY C_ID, MONTH(STARTDATE)) PURCHASES
ON c.C_ID = PURCHASES.C_ID

I want to get something like this:

I hope this can provide an idea that I need to configurate.
Thanks

Finally, my table inventories have the following structure data (I took a few registers from this table):
inventories

That is a picture, we need you to provide it as the sample shown above. We are not going to retype all of that for you

Create table #inventories
Insert into # inventories

Help us help you

C_ID STARTDATE ENDDATE START_INV END_INV
NOTEBOOK 5/20/21 12:00 AM 5/21/21 12:00 AM 0 0
PENCIL 2/19/21 12:00 AM 2/20/21 12:00 AM 3862.263 3862.263
NOTEBOOK 4/9/21 12:00 AM 4/10/21 12:00 AM 757.248 841.64
CARBOARD 6/11/21 12:00 AM 6/12/21 12:00 AM 6.412 6.412
ENVELOPE 6/20/21 12:00 AM 6/21/21 12:00 AM 775.756 803.308
BINDER 6/11/21 12:00 AM 6/12/21 12:00 AM 94050.079 94050.079
CLIP 6/17/21 12:00 AM 6/18/21 12:00 AM 3099.701 3099.232
ENVELOPE 7/7/21 12:00 AM 7/8/21 12:00 AM 250.66 250.66
NOTEPAD 1/13/21 12:00 AM 1/14/21 12:00 AM 93334.004 93334.004
NOTEPAD 2/1/21 12:00 AM 2/2/21 12:00 AM 93337.386 93337.386
PENCIL 3/30/21 12:00 AM 3/31/21 12:00 AM 4635.745 3831.944
DESK ORGANIZER 2/22/21 12:00 AM 2/23/21 12:00 AM 0 0
CLIP 2/3/21 12:00 AM 2/4/21 12:00 AM 0 0
DESK ORGANIZER 3/24/21 12:00 AM 3/25/21 12:00 AM 0 -25740.527
FOLDER 4/29/21 12:00 AM 4/30/21 12:00 AM 0 0
BINDER 6/5/21 12:00 AM 6/6/21 12:00 AM 419.611 419.611
ERASER 1/13/21 12:00 AM 1/14/21 12:00 AM 11669.013 11669.013
PEN_MIX 3/14/21 12:00 AM 3/15/21 12:00 AM 0 0
BINDER 6/22/21 12:00 AM 6/23/21 12:00 AM 94023.61 94023.61
NOTEPAD 6/21/21 12:00 AM 6/22/21 12:00 AM 45416.856 45416.856
DESK ORGANIZER 4/16/21 12:00 AM 4/17/21 12:00 AM 44230.432 45728.932
NOTEPAD 2/7/21 12:00 AM 2/8/21 12:00 AM 93337.386 93337.386
NOTEBOOK 1/9/21 12:00 AM 1/10/21 12:00 AM 143.618 143.618
PEN 5/14/21 12:00 AM 5/15/21 12:00 AM 470.282 470.38
NOTEPAD 2/27/21 12:00 AM 2/28/21 12:00 AM 3766.49 3766.49
DESK ORGANIZER 5/24/21 12:00 AM 5/25/21 12:00 AM 97476.984 97476.984
ENVELOPE 7/6/21 12:00 AM 7/7/21 12:00 AM 0 0
NOTEBOOK 4/17/21 12:00 AM 4/18/21 12:00 AM 0 0
Scissors 5/4/21 12:00 AM 5/5/21 12:00 AM 21513.286 27676.796
NOTEPAD 2/19/21 12:00 AM 2/20/21 12:00 AM 45329.17 45329.17
PEN 1/17/21 12:00 AM 1/18/21 12:00 AM 0 0
PEN 2/10/21 12:00 AM 2/11/21 12:00 AM 0 0
DESK ORGANIZER 4/29/21 12:00 AM 4/30/21 12:00 AM 23560.726 23560.726
NOTEBOOK 3/1/21 12:00 AM 3/2/21 12:00 AM 1.95 1.95
NOTEPAD 1/3/21 12:00 AM 1/4/21 12:00 AM 45328.716 45328.716
NOTEPAD 2/27/21 12:00 AM 2/28/21 12:00 AM 58680.627 58680.627
DESK ORGANIZER 4/8/21 12:00 AM 4/9/21 12:00 AM 36834.379 36845.002
DESK ORGANIZER 5/16/21 12:00 AM 5/17/21 12:00 AM 44259.695 43490.944
NOTEBOOK 7/8/21 12:00 AM 7/9/21 12:00 AM 0.02 0.02
DESK ORGANIZER 5/23/21 12:00 AM 5/24/21 12:00 AM 44810.878 43020.617
Scissors 1/16/21 12:00 AM 1/17/21 12:00 AM 12051.564 12051.564
ENVELOPE 5/13/21 12:00 AM 5/14/21 12:00 AM 402.365 402.155
NOTEPAD 6/30/21 12:00 AM 7/1/21 12:00 AM 17524.437 17524.437
NOTEBOOK 7/15/21 12:00 AM NULL 1.55 NULL
NOTEPAD 1/7/21 12:00 AM 1/8/21 12:00 AM 45151.522 45151.522
ERASER 5/15/21 12:00 AM 5/16/21 12:00 AM 4126.176 4105.41
ENVELOPE 5/15/21 12:00 AM 5/16/21 12:00 AM 348.058 348.635
NOTEBOOK 3/11/21 12:00 AM 3/12/21 12:00 AM 1462.855 1462.855
Scissors 1/4/21 12:00 AM 1/5/21 12:00 AM 12240.094 12240.094
ERASER 2/14/21 12:00 AM 2/15/21 12:00 AM 8211.833 8211.833
NOTEPAD 1/15/21 12:00 AM 1/16/21 12:00 AM 90950.348 90950.348
Scissors 4/11/21 12:00 AM 4/12/21 12:00 AM 18213.104 15135.447
NOTEPAD 2/27/21 12:00 AM 2/28/21 12:00 AM 175909.279 175909.279
ERASER 6/9/21 12:00 AM 6/10/21 12:00 AM 1626.327 8240.306
DESK ORGANIZER 7/14/21 12:00 AM 7/15/21 12:00 AM 70.137 70.137
NOTEPAD 1/13/21 12:00 AM 1/14/21 12:00 AM 45329.623 45329.623
NOTEBOOK 1/13/21 12:00 AM 1/14/21 12:00 AM 845.397 845.397
CARBOARD 1/26/21 12:00 AM 1/27/21 12:00 AM 0 0
BINDER 6/10/21 12:00 AM 6/11/21 12:00 AM 0 0
ENVELOPE 4/29/21 12:00 AM 4/30/21 12:00 AM 635.449 635.449
ENVELOPE 5/5/21 12:00 AM 5/6/21 12:00 AM 434.401 444.018
NOTEPAD 2/22/21 12:00 AM 2/23/21 12:00 AM 175604.068 175604.068
NOTEBOOK 1/3/21 12:00 AM 1/4/21 12:00 AM 1268.433 1268.433
ERASER 6/18/21 12:00 AM 6/19/21 12:00 AM 5101.817 9805.895
NOTEPAD 2/9/21 12:00 AM 2/10/21 12:00 AM 45329.17 45329.17
NOTEPAD 6/3/21 12:00 AM 6/4/21 12:00 AM 57566.676 57566.676
CLIP 1/25/21 12:00 AM 1/26/21 12:00 AM 0 0
NOTEPAD 5/15/21 12:00 AM 5/16/21 12:00 AM 0 0
NOTEPAD 1/5/21 12:00 AM 1/6/21 12:00 AM 91875.921 91875.921
PEN 5/31/21 12:00 AM 6/1/21 12:00 AM 8.422 8.422
ENVELOPE 5/28/21 12:00 AM 5/29/21 12:00 AM 1128.45 1464.317
Scissors 6/7/21 12:00 AM 6/8/21 12:00 AM 56953.771 22822.475
BINDER 4/16/21 12:00 AM 4/17/21 12:00 AM 60482.338 60482.338
Scissors 3/21/21 12:00 AM 3/22/21 12:00 AM 17329.385 17329.385
ENVELOPE 2/1/21 12:00 AM 2/2/21 12:00 AM 0 0
NOTEPAD 1/8/21 12:00 AM 1/9/21 12:00 AM 175907.518 175884.627
PEN 6/30/21 12:00 AM 7/1/21 12:00 AM 20220.012 20219.392
PEN 5/29/21 12:00 AM 5/30/21 12:00 AM 45828.572 45848.677
ERASER 5/20/21 12:00 AM 5/21/21 12:00 AM 7636.346 3576.477
DESK ORGANIZER 5/10/21 12:00 AM 5/11/21 12:00 AM 28032.989 28047.276
FOLDER 7/11/21 12:00 AM 7/12/21 12:00 AM 0.91 0.91
BINDER 6/24/21 12:00 AM 6/25/21 12:00 AM 419.611 419.611

Is this suitable for you?

close but no cigar because we would have to do a lot of manual edit. look at the sample table provided earlier.

create table #inventories(C_ID varchar(10),	
STARTDATE date,	
ENDDATE	date, 
START_INV decimal(8,3),	
END_INV decimal(8,3))

insert into #inventories
--here you provide the sample data you pasted above.
select 'NOTEBOOK', '5/20/21 12:00 AM', '5/21/21 12:00 AM', 0, 0 union
select ---etc

Reminds me of many nightmares like this I have FACED many many times

  • Despite Being a Senior Guy in SQL

One Idea

  • Break it down into very very small bits
  • Do the connections
  • Put the parts back together

I am always
P... I.... T... As..... !#$%%$%#^%$ ( Always facing different things = same headaches )

can i remote desktop and do it FOR YOU ?

or at least guide you

Will Make Things a LOT LOT Easier than 100 back and forths

the solution is already provided for the OP. it is a very simple PIVOT solution, not complicated at all. no headaches, no PHD :wink:

thank GOD for that :grimacing: :stuck_out_tongue_winking_eye: :rofl:

select *
FROM  
(
	SELECT C_ID, DATENAME(month, STARTDATE) as _monthName, sum(END_INV)  as inv  
	FROM INVENTORY 
	where STARTDATE >= @begin and ENDDATE < @finish
	group by C_ID, STARTDATE
) AS src  
PIVOT  
(  
	SUM(inv)  
	FOR _monthName IN ([January], [February],[March],[April],[May], [June], [July], [August], [September], [October], [November], [December])  
) AS pt;