calculation for year month and quarter with date dimension table

  • date tablei want to know how i can use the date dimension table. I am trying to sum all my sales for month, quarter and Year with joining a date dimension table but not sure how to go about that can you assist or direct me to a good resource? I have a query but i do not think it is correct. As you can see from the image below the yearly values are not correct.

    table 1

    SELECT
    'Cash' strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN mnyCash ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN mnyCash ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN mnyCash ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN mnyCash ELSE 0 END),0) AS intYTD
    FROM
    tblUBMReport WITH (NOLOCK)
    INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
    WHERE
    intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
  • Without sample data and DDL, it's hard to help, but I would look here.  It is only bringing back where the dtDate = enddate

     

    INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Personally and generally, I think Date Dimension tables are usually overrated for such things.  Using things like GROUP BY with ROLLUP, CUBE, or GROUPING SETs is much more effective.  You can also use SUM(somecolumnname) OVER (PARTITON BY ___, ___ ORDER BY ____, ____), etc, etc.  That's probably not applicable to this case but had to say that out loud.

    I'll also state that the idea of using INTs for dates is a bit of a travesty because temporal functions are incredibly powerful and performant if you spend some time learning them.

    For this one, I'd be tempted to just pre-define the start of the year, quarter, and month and then do what you're doing, especially since you don't need to determine what's a holiday in this code.  Since they formulas would only be used once each, doing them "in-line" would work just fine.

    --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)
    Intro to Tally Tables and Functions

  • p.s.  The links in the signature lines of both Mike and I will get you better/actual coded answers more quickly.

    --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)
    Intro to Tally Tables and Functions

  • This is a bit of an odd question. Normally, if you have a date dimension table, you're working in a data warehouse, which means you're usually using either DAX or MDX to query it. In that case, you wouldn't create any crazy SQL queries at all, you'd use DAX functions.

     

  • jeannier175 wrote:

    date tablei want to know how i can use the date dimension table. I am trying to sum all my sales for month, quarter and Year with joining a date dimension table but not sure how to go about that can you assist or direct me to a good resource? I have a query but i do not think it is correct. As you can see from the image below the yearly values are not correct.

    table 1

    SELECT
    'Cash' strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN mnyCash ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN mnyCash ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN mnyCash ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN mnyCash ELSE 0 END),0) AS intYTD
    FROM
    tblUBMReport WITH (NOLOCK)
    INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
    WHERE
    intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate

    Sorry... I think we all got distracted by our own personal opinions but you've also not provided enough data for us to help you solve this problem.

    So, with that being said, we just need to verify a couple of things in your original post above...

    1. Can I assume that the "date" columns for the following attributes/variables are of the DATE datatype?  If not, then what is the datatype for each attribute/variable?  Let's hope they didn't screw us all by making the any or all of the below of the INT datatype.

      tblDate.dtDate

      tblDate.dtFirstDayOfMonth

      tblDate.dtFirstDayOfQuarter

      tblDate.dtFirstDayOfYear

      blUBMReport.dtReport

      @StartDate

      @EndDate

    2. What is the datatype of the @props variable and can you provide and example of what @props will contain?  This is extremely important because the WHERE IN you used isn't going to work the way you've coded it.

    THIS is why we also went on about some sample data.  If you had provided it in a readily consumable format, you would have like had a coded, tested, working example within an hour or two of when you asked the question.  Do yourself a favor and read'n'heed the article at the first link in my signature line below for what people that want to help could use to really help you quickly and without having to ask a bunch of other questions.

    --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)
    Intro to Tally Tables and Functions

  • I was able to resolve the issue i just deleted the date filter in the where clause.

  • jeannier175 wrote:

    I was able to resolve the issue i just deleted the date filter in the where clause.

    Have you taken a look at your execution plan since you've done that?

    --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)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 8 (of 8 total)

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