SQLTeam.com | Weblogs | Forums

Products Sold Per Month

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

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

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

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

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

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

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

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

so, take that query as input to you pivot

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      ...

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

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 ...
...

Brilliant , Many Thanks

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

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.

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