Post reply

Query to show units in use on each day for past 12 months

  • June 19, 2017 at 2:11 am

    #322182

    Hi,
    I need to be able to create a query which shows the number of our products that were in use on any specific given day within the past 12 months. I think I need to do this in two parts but it is the second part I am struggling with. For the first part I have a query below which list all products that have been or still are in use for the past 12 months, this is the query...

    SELECT ProductType  StartedAt, EndedAt
    FROM ContractTable
    WHERE ContractTable.EndedAt >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-12, 0)
    OR ContractTable.EndedAt IS NULL

    This will produce something like this...
    ProductType     StartedAt     EndedAt
    ProductA           01/06/17      16/06/17
    ProductA           16/06/17       null
    ProductB           01/01/16      31/01/17
    ProductB           01/02/17      null

    Now this is the bit I am struggling with, I need to be able to see how many of these products were in use on each day for the past 12 months. The results of which would look something like this...
    Date           ProductType      Quantity
    15/06/17    ProductA             1
    15/06/17    ProductB             1
    16/06/17    ProductA             2
    16/06/17    ProductB             1

    Does anyone have any ideas how I could take the results from the first query to show me a units in use by as per above? Any help or advice will be much appreciated as I am really struggling with this one.

    Many thanks 

  • June 19, 2017 at 3:49 am

    #1947220

    Start with a calendar table and then do something like this:

    SELECT
        c.MyDate
    ,   t.ProductType
    ,   COUNT(t.ProductType)
    FROM Calendar c
    LEFT JOIN ContractTable t
    ON c.MyDate >= t.StartedAt AND c.MyDate <= COALESCE(t.EndedAt,CAST(GETDATE() AS date))
    WHERE c.MyDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-12, 0)
    OR ContractTable.EndedAt IS NULL
    GROUP BY
        c.MyDate
    ,   t.ProductType

    John

  • June 19, 2017 at 8:48 am

    #1947260

    Excellent, thank you that has sorted it. Not quite fully understanding what the COALESCE part does but it does what I need it to.

  • June 19, 2017 at 8:55 am

    #1947264

    Chances are you're going to be supporting it, so you need to document and understand it.  The COALESCE function takes the value of EndedAt, and if it's NULL, changes it into today's date.  You can use an arbitrary placeholder such as '20991231' instead of GETDATE(), if you prefer.  If you don't use the COALESCE, no rows where EndedAt is NULL will be included in the results.

    John

  • June 19, 2017 at 2:09 pm

    #1947316

    dcaulton73 - Monday, June 19, 2017 8:48 AM

    Excellent, thank you that has sorted it. Not quite fully understanding what the COALESCE part does but it does what I need it to.

    Heh... so do a Yabingooglehoo on it.  You're going to need to support this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply