calculation for year month and quarter with date dimension table

Question

calculation for year month and quarter with date dimension table

SSC Veteran

Points: 213

August 18, 2022 at 4:20 pm

date table i 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

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

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/

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

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)

pietlinden SSC Guru Points: 63503 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

jeannier175 wrote:

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

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

jeannier175 SSC Veteran Points: 213 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

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)