SQLTeam.com | Weblogs | Forums

Products Sold Per Month


#1

SO I'm Learning all Be it slow but as always trying to run before I can walk , Went on a Basic course that has answered a lot of questions but has driven my interest further .
Im stuck trying to get products sold by there invoice date grouped into monthy sales , I've googled some stuff but cant seem to get it to work which is probably just me

 SELECT        YEAR(SalesInvoices.EffectiveDate) AS YEAR,
 MONTH(SalesInvoices.EffectiveDate) AS MONTH,
  DATENAME(MONTH, SalesInvoices.EffectiveDate) AS [MONTH NAME],
                          COUNT(1) AS [Sales COUNT], 
						  Products.ProductId, Products.ProductDescription, 
						  SalesInvoiceItems.Quantity
FROM            SalesInvoiceItems INNER JOIN
                         SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice INNER JOIN
                         Products ON SalesInvoiceItems.Product = Products.Product INNER JOIN
                         SalesOrderItems ON Products.Product = SalesOrderItems.Product

I would like it to look like this

Thanks In advance Jon


#2

Could you post the results you get with your query? e.g. I don't see the column [MONTH NAME] in your desired results.


#3

Are you trying to count the number of sales (invoices) OR the quantity of products sold?


#4

Hi , I'm try to count the products , by invoice date per month


#5

Read up on PIVOT. You'll need that to get the desired result


#6

HI

Ive played with Pivot and and have the below working but this is only looking at the SalesInvoice tables how to i get it to look at the Products sold on these invoices

Products.ProductID
SalesInvoiceItems.Quantity

Thanks Jon

> SELECT *
> From (SELECT year(SalesInvoices.EffectiveDate) 
> AS [YEAR],LEFT (datename(Month,SalesInvoices.EffectiveDate),3) 
> AS [Month],SalesInvoices.InvoiceValue AS Amount  From SalesInvoices) 
> As  s PIVOT
> ( Sum ( Amount)
> FOR [MONTH] IN ( Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
> )AS PVT

#7

Your subquery is only refering to the sales invoices table. Can you try to add a join to the product table?


#8

Sorry I think i'm confusing myself and everyone else

I'm trying to List the below Query as a pivot in Months

SELECT        Products.ProductId, Products.ProductDescription, SalesInvoices.EffectiveDate, SalesInvoiceItems.Quantity

FROM            SalesInvoices INNER JOIN
                         SalesInvoiceItems ON SalesInvoices.SalesInvoice = SalesInvoiceItems.SalesInvoice INNER JOIN
                         Products ON SalesInvoiceItems.Product = Products.Product

#9

so, take that query as input to you pivot


#10
DECLARE @year date
SET @year = '20170101' --GETDATE()

--guarantee @year is Jan 01 00:00:00.
SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)

SELECT        Products.ProductId, Products.ProductDescription, 
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 01, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Jan,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 02, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Feb,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 03, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Mar ,
              --...
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 11, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Nov,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Dec

FROM            SalesInvoices INNER JOIN
                         SalesInvoiceItems ON SalesInvoices.SalesInvoice = SalesInvoiceItems.SalesInvoice INNER JOIN
                         Products ON SalesInvoiceItems.Product = Products.Product

GROUP BY      Products.ProductId, Products.ProductDescription
--ORDER BY      ...

#11

That is exactly waht I was looking for and I've added the additional Months and all works except that when i change the YEAR

SET @year = '20170101' --GETDATE()

To

SET @year = '20160101' --GETDATE()

They both return 2018 data , I'm clearly missing something from my tiny brain


#12

D'OH, good point, typo!

Change this line:
SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 0)

Also, I should have added a WHERE clause to the main query as well, for efficiency, to "tell" SQL from the start that we only need the rows for a single year:

SELECT ...
FROM ...
WHERE SalesInvoices.EffectiveDate >= @year AND
    SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @year)
GROUP BY ...
...

#13

Brilliant , Many Thanks


#14

I think I'm beign stupid here , the Where's not working and I cant see why ,

DECLARE @year date
SET @year = '20140101' --GETDATE()

--guarantee @year is Jan 01 00:00:00.
SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 0)

SELECT        Products.ProductId, Products.ProductDescription, 
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 01, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Jan,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 02, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Feb,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 03, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Mar ,
                 SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 03, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 04, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Apr,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 04, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 05, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS May,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 05, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 06, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS June ,
   SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 06, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 07, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS July,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 07, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 08, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Aug,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 08, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 09, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Sep ,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 09, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 10, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Oct ,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 11, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Nov,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Dec

FROM            SalesInvoices INNER JOIN
                             SalesInvoiceItems ON SalesInvoices.SalesInvoice = SalesInvoiceItems.SalesInvoice INNER JOIN
 
                            Products ON SalesInvoiceItems.Product = Products.Product

WHERE SalesInvoices.EffectiveDate >= @Year_ AND
  SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @Year_)

    GROUP BY      Products.ProductId, Products.ProductDescription
Order BY Products.ProductId DESC

#15

Hmm, the only thing I see is that:
@year and @Year_ are two different variables.

Be sure the WHERE clause is using the exact same value as the CASE clauses.


#16

Yep , double checked it and it still only returns the SET @year