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