Show Year to Date sales

Question

I am having difficulty adding a column for YTD sales (from Jan 1, 2017 to last passed Sunday). I have made two columns which provide the Employees with their weekly sales (week ends every Sunday and that is the reason I am using the date_trunc function so it is not necessitated to change the date every week in and out). The reason I am using coalesce is due to not employees making sales every week so I need to show a Zero next to their names (I get the list of all employees from the Employees table). The problem with my current syntax is that the YTD just emulates the results from the This Week column. How can I add the YTD column in my syntax? I would appreciate any assistance.

select coalesce(Employees,'Total') as "Employees", "This Week", "YTD"

from 
(select t2.Employees,coalesce(sum(t1.Sales),0) "This Week", coalesce(sum (t1.Sales),0) "YTD"
  from Employees t2 
  left join Sales t1 ON Employees = Employees
  and "Week" = date_trunc('week', now())::date - 1
  group by rollup(t2.Employees)) Z

My result from the above snippet:

Employees                        This Week                    YTD
Derek Jeter                        0                           0
Barry Bonds                        3                           3
Luis Gonzalez                      6                           6    
Mike Piazza                        10                          10
Jason Witten                       0                           0
Lebron James                       7                           7 
Daryl Wade                         5                           5
Eli Manning                        11                          11
Total                              42                          42

Mark Adelsberger Mark Adelsberger · Accepted Answer · 2017-01-17T18:17:58

UPDATE - from comments you only have the "Week" value and not an exact sales date, so my original code for looking at only "current year" will not work. Updated to suggest options for dealing with this

As it stands you're including only the current week's data in your underlying result set; that won't work. You need the result set to include all data used in any of the final outputs, and then filter each output accordingly.

My understanding is that your data only attributes sales to a week - not to a specific day - so you need to make a choice about which week should be the first included. For 2017, because the year started on a Sunday, this may not be an issue; but in general you need to decide whether to count sales from a week that starts in the previous year but ends in the current year.

To exclude them you could just do

select coalesce(t2.Employees, 'Total') as "Employees"
     , coalesce(sum(case when "Week" = date_trunc('week', now())::date - 1
                         then t1.Sales
                    end),0) "This Week"
     , coalesce(sum(t1.Sales),0) "YTD"
  from           Employees t2 
       left join Sales t1 
              ON Employees = Employees
             and "Week" <= date_trunc('week', now())::date - 1
             and "Week" >= date_trunc('year', 'now())::date
 group by rollup(t2.Employees)

To include them I guess you could do this

select coalesce(t2.Employees, 'Total') as "Employees"
     , coalesce(sum(case when "Week" = date_trunc('week', now())::date - 1
                         then t1.Sales
                    end),0) "This Week"
     , coalesce(sum(t1.Sales),0) "YTD"
  from           Employees t2 
       left join Sales t1 
              ON Employees = Employees
             and "Week" <= date_trunc('week', now())::date - 1
             and "Week" >= date_trunc('week',
                                      date_trunc('year', 'now())::date)
 group by rollup(t2.Employees)

Show Year to Date sales

1 Answers