Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: trying to graph Sales over 12 months

    Hi guys,

    I am working on a simple (or so I though!) expression to return all the orders in my database over the last 12 months from todays date i.e. all orders from 08 Feb 08 - 08 Feb 09.

    To keep things simple assume my table has the following:
    Code:
    CREATE TABLE dbo.Orders(
    		  OrderID INT IDENTITY PRIMARY KEY,
    		  OrderDate DATETIME,
    		  OrderAmount float
    	)
    I currently have my SQL statement like the following:

    Code:
    SELECT Month(O.OrderDate) as MyMonth, COUNT(Month(O.OrderDate)) AS NumOrders, Year(O.OrderDate) as MyYear
    FROM Orders O
    WHERE O.OrderDate >= DATEADD(year,-1,GETDATE()) AND O.OrderDate <=  GETDATE()
    GROUP BY Month(O.OrderDate), Year(O.OrderDate)
    This gives me the right data as expected but, obivously, if I have no orders for a month then I don't have any figure returned for that month and this is messing up my front end chart.

    I am wondering if it is possible to return a month with a NumOrders of 0 for any months that are missing in my Orders table. I know I need to group by Months to get it working but I can't for the life of me figure out what to do!

    I could code this in my .net code but would rather do this filter on the database results if it was possible.

    Anyone have any ideas? Thanks in advance for taking the time to read this!

    Cheers,
    Rich

  2. #2
    Join Date
    Feb 2009
    Posts
    5
    There may well be an eaiser way but...

    --set up test table
    DECLARE @orders table (orderID int not null identity(1,1), OrderDate datetime, OrderAmount money)
    insert @orders(OrderDate,OrderAmount) values('01 jan 2009', 12.50)
    insert @orders(OrderDate,OrderAmount) values('01 feb 2009', 12.50)

    --set up a table of months
    DECLARE @reportMonths int --made number of months to go back a variable
    SET @reportMonths = 12
    DECLARE @counter int
    DECLARE @monthList table(yearVal int, monthval INT)
    SET @counter = 0
    WHILE @counter <= @reportMonths
    BEGIN
    INSERT @monthList(yearval,MonthVal) VALUES(year(dateadd(m,-@counter,getdate())),month(dateadd(m,-@counter,getdate())))
    SET @counter = @counter +1
    END


    SELECT ISNULL(year(o.OrderDate),r.yearVal),ISNULL(month(o .OrderDate),r.monthVal), count(o.OrderID) AS NumOrders
    FROM @monthList r
    LEFT JOIN @Orders o
    ON year(o.OrderDate) = r.yearVal
    AND month(o.OrderDate) = r.monthVal
    GROUP BY ISNULL(year(o.OrderDate),r.yearVal),ISNULL(month(o .OrderDate),r.monthVal)
    ORDER BY 1 DESC,2 DESC

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you don't already have one, start by creating an integers table
    Code:
    CREATE TABLE integers 
      (i INTEGER NOT NULL PRIMARY KEY) 
    INSERT INTO INTEGERS (i) VALUES (0) 
    INSERT INTO INTEGERS (i) VALUES (1) 
    INSERT INTO INTEGERS (i) VALUES (2) 
    INSERT INTO INTEGERS (i) VALUES (3) 
    INSERT INTO INTEGERS (i) VALUES (4) 
    INSERT INTO INTEGERS (i) VALUES (5) 
    INSERT INTO INTEGERS (i) VALUES (6) 
    INSERT INTO INTEGERS (i) VALUES (7) 
    INSERT INTO INTEGERS (i) VALUES (8) 
    INSERT INTO INTEGERS (i) VALUES (9)
    INSERT INTO INTEGERS (i) VALUES (10) 
    INSERT INTO INTEGERS (i) VALUES (11) 
    INSERT INTO INTEGERS (i) VALUES (12)
    we'll need integers 0 through 12

    now we can generate the monthly boundary dates as follows:
    Code:
    SELECT DATEADD(mm,i*-1,
           DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ) AS monthday1
      FROM integers
     WHERE i BETWEEN 0 AND 12
    /*
    monthday1           
    ------------------- 
    2008-02-01 00:00:00
    2008-03-01 00:00:00
    2008-04-01 00:00:00
    2008-05-01 00:00:00
    2008-06-01 00:00:00
    2008-07-01 00:00:00
    2008-08-01 00:00:00
    2008-09-01 00:00:00
    2008-10-01 00:00:00
    2008-11-01 00:00:00
    2008-12-01 00:00:00
    2009-01-01 00:00:00
    2009-02-01 00:00:00
    
    13 row(s)
    */
    you won't need the WHERE clause if you have exactly those integers in the table, but it's always a good idea to be explicit, in case you use the integers table for other things

    now we can join to these rows using LEFT OUTER JOIN

    note that you don't want full monthly totals, so we'll put the "no more than exactly 12 months ago from today" into a subquery
    Code:
    SELECT YEAR(monthday1) AS yyyy
         , MONTH(monthday1) AS mm
         , COUNT(O.OrderDate) AS order_count
         , COALESCE(SUM(OrderAmount),0) AS total_amount
      FROM ( SELECT DATEADD(mm,i*-1,
                    DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ) AS monthday1
               FROM integers
              WHERE i BETWEEN 0 AND 12 ) AS d
    LEFT OUTER 
      JOIN ( SELECT * 
               FROM Orders 
              WHERE OrderDate >= DATEADD(year,-1,GETDATE()) 
                AND OrderDate <= GETDATE() 
           ) AS O
        ON O.OrderDate >= monthday1
       AND O.OrderDate  < DATEADD(mm,1,monthday1)
    GROUP 
        BY YEAR(monthday1) 
         , MONTH(monthday1) 
    ORDER
        BY YEAR(monthday1) 
         , MONTH(monthday1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2009
    Posts
    2
    Thanks very much for both of your examples. They both worked for me. I would never have thought to use a reference table for comparing the dates so thanks for the tip.

    I'm off to make that work for order in the last 30 days now

    Thanks again!

    Rich

  5. #5
    Join Date
    Feb 2009
    Posts
    5
    Glad they worked. One of the reasons I added the the parameter for 12 months was so you could use itas an example fo longer ranges or change it for days and so on.

    One thing to look out for in these types of queries is that the timestamp from run date is time specific so if run at 3pm will ignore anything brfore 3pm at the earliest date.

    I always standardise my input criteria to 00:00 hours then add day to my TO crideria to make it inclusive

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •