calculation for year month and quarter with date dimension table

Jeannie Jones1975 1 Reputation point

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.

'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  
tblUBMReport WITH (NOLOCK)  
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate  
intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate][1]![232494-table-1.png][2]  
{count} votes

1 answer

Sort by: Most helpful
  1. Haris Rashid 1 Reputation point

    Group by date table Group by year (date) , month(date), day(date) and sum the joined table ubmReport sum (Cash)

    You need the sum statement only once.