Calculate SUM for each combination based on 3 tables and a date

34 Views Asked by At

Let's say 5 kids help each other selling fruit during 4 days. They sell 5 kinds of fruit.

I need a query that shows the sum of each kids' sale per fruit type per day. But the catch is: i need a "0" (zero) to be displayed if one of the kids didn't sell anything of a specific fruit, or on a specific day at all.

In exampledata below, please notice that (on "today-3", no fruit was sold at all) and (on "today-2", no banana was sold at all)

In the end, i would like my query to return this: final result

How would you write this query - i tried cross apply and other principles, but found my self af the end of my knowledge.

Example data:

create table temp_Entry (
    entryId int identity(1,1)
,   consumptionDate date
,   personId varchar(100)
,   fruitId varchar(10) 
,   quantity integer
)
GO

create table temp_Person (
    personId int identity(1,1)
,   personName varchar(100)
)
GO

create table temp_Fruit (
    fruitId int identity(1,1)
,   fruitName varchar(100)
)
GO


insert temp_Person
select 'Alan' union
select 'Bobby' union
select 'Chris' union
select 'Xavier' union
select 'Yoshi'

insert temp_Fruit
select 'Banana' union
select 'Apple' union
select 'Pear' union
select 'Melon' union
select 'Grapefruit'


insert temp_Entry (consumptionDate, personId, fruitId, Quantity)
select dateadd(dd, -1, getdate()), 'Alan', 'Banana', 2 union
select dateadd(dd, -1, getdate()), 'Alan', 'Apple', 1 union
select dateadd(dd, -1, getdate()), 'Alan', 'Grapefruit', 5 union
select dateadd(dd, -1, getdate()), 'Alan', 'Melon', 3 union
select dateadd(dd, -1, getdate()), 'Alan', 'Pear', 12 union
select dateadd(dd, -2, getdate()), 'Alan', 'Apple', 10 union
select dateadd(dd, -2, getdate()), 'Alan', 'Grapefruit', 2 union
select dateadd(dd, -2, getdate()), 'Alan', 'Melon', 3 union
select dateadd(dd, -2, getdate()), 'Alan', 'Pear', 5 union
select dateadd(dd, -4, getdate()), 'Alan', 'Banana', 1 union
select dateadd(dd, -4, getdate()), 'Alan', 'Apple', 10 union
select dateadd(dd, -4, getdate()), 'Alan', 'Grapefruit', 2 union
select dateadd(dd, -4, getdate()), 'Alan', 'Melon', 2 union
select dateadd(dd, -4, getdate()), 'Alan', 'Pear', 1  union

select dateadd(dd, -1, getdate()), 'Bobby', 'Banana', 2 union
select dateadd(dd, -1, getdate()), 'Bobby', 'Apple', 1 union
select dateadd(dd, -1, getdate()), 'Bobby', 'Grapefruit', 2 union
select dateadd(dd, -1, getdate()), 'Bobby', 'Melon', 2 union
select dateadd(dd, -1, getdate()), 'Bobby', 'Pear', 3 union
select dateadd(dd, -2, getdate()), 'Bobby', 'Apple', 4 union
select dateadd(dd, -2, getdate()), 'Bobby', 'Grapefruit', 5 union
select dateadd(dd, -2, getdate()), 'Bobby', 'Melon', 3 union
select dateadd(dd, -2, getdate()), 'Bobby', 'Pear', 2 union
select dateadd(dd, -4, getdate()), 'Bobby', 'Banana', 2 union
select dateadd(dd, -4, getdate()), 'Bobby', 'Apple', 1 union
select dateadd(dd, -4, getdate()), 'Bobby', 'Grapefruit', 5 union
select dateadd(dd, -4, getdate()), 'Bobby', 'Melon', 1 union
select dateadd(dd, -4, getdate()), 'Bobby', 'Pear', 1  union

select dateadd(dd, -1, getdate()), 'Chris', 'Banana', 2 union
select dateadd(dd, -1, getdate()), 'Chris', 'Apple', 1 union
select dateadd(dd, -1, getdate()), 'Chris', 'Grapefruit', 2 union
select dateadd(dd, -1, getdate()), 'Chris', 'Melon', 4 union
select dateadd(dd, -1, getdate()), 'Chris', 'Pear', 5 union
select dateadd(dd, -2, getdate()), 'Chris', 'Apple', 1 union
select dateadd(dd, -2, getdate()), 'Chris', 'Grapefruit', 2 union
select dateadd(dd, -2, getdate()), 'Chris', 'Melon', 7 union
select dateadd(dd, -2, getdate()), 'Chris', 'Pear', 5 union
select dateadd(dd, -4, getdate()), 'Chris', 'Banana', 2 union
select dateadd(dd, -4, getdate()), 'Chris', 'Apple', 1 union
select dateadd(dd, -4, getdate()), 'Chris', 'Grapefruit', 5 union
select dateadd(dd, -4, getdate()), 'Chris', 'Melon', 3 union
select dateadd(dd, -4, getdate()), 'Chris', 'Pear', 4  union

select dateadd(dd, -1, getdate()), 'Xavier', 'Banana', 2 union
select dateadd(dd, -1, getdate()), 'Xavier', 'Apple', 1 union
select dateadd(dd, -1, getdate()), 'Xavier', 'Grapefruit', 5 union
select dateadd(dd, -1, getdate()), 'Xavier', 'Melon', 5 union
select dateadd(dd, -1, getdate()), 'Xavier', 'Pear', 2 union
select dateadd(dd, -2, getdate()), 'Xavier', 'Apple', 1 union
select dateadd(dd, -2, getdate()), 'Xavier', 'Grapefruit', 5 union
select dateadd(dd, -2, getdate()), 'Xavier', 'Melon', 5 union
select dateadd(dd, -2, getdate()), 'Xavier', 'Pear', 2 union
select dateadd(dd, -4, getdate()), 'Xavier', 'Banana', 2 union
select dateadd(dd, -4, getdate()), 'Xavier', 'Apple', 1 union
select dateadd(dd, -4, getdate()), 'Xavier', 'Grapefruit', 5 union
select dateadd(dd, -4, getdate()), 'Xavier', 'Melon', 4 union
select dateadd(dd, -4, getdate()), 'Xavier', 'Pear', 5  union

select dateadd(dd, -1, getdate()), 'Yoshi', 'Banana', 2 union
select dateadd(dd, -1, getdate()), 'Yoshi', 'Apple', 1 union
select dateadd(dd, -1, getdate()), 'Yoshi', 'Grapefruit', 5 union
select dateadd(dd, -1, getdate()), 'Yoshi', 'Melon', 5 union
select dateadd(dd, -1, getdate()), 'Yoshi', 'Pear', 1 union
select dateadd(dd, -2, getdate()), 'Yoshi', 'Apple', 1 union
select dateadd(dd, -2, getdate()), 'Yoshi', 'Grapefruit', 5 union
select dateadd(dd, -2, getdate()), 'Yoshi', 'Melon', 1 union
select dateadd(dd, -2, getdate()), 'Yoshi', 'Pear', 1 union
select dateadd(dd, -4, getdate()), 'Yoshi', 'Banana', 2 union
select dateadd(dd, -4, getdate()), 'Yoshi', 'Apple', 1 union
select dateadd(dd, -4, getdate()), 'Yoshi', 'Grapefruit', 5 union
select dateadd(dd, -4, getdate()), 'Yoshi', 'Melon', 3 union
select dateadd(dd, -4, getdate()), 'Yoshi', 'Pear', 5 

select * from temp_Fruit
select * from temp_Person
select * from temp_entry
0

There are 0 best solutions below