SQL Query Help

Question

Given this criteria will somebody help in the query to accomplish this?

Table A contains the weekly price of lemonade at the local restaurant.
Table B contains the weekly price of lemonade as the supermarket.
Table C contains every student at the local school and their lemonade allowance which never changes.
A student will always buy the most expensive lemonade within their budget.
Write a query that will provide the total number of lemonades sold by the restaurant and the supermarket each week.

Here is the sample data per table:

Table A

Week ---- Price
1 ------- $1.00
2 ------- $2.00

Table B

Week ---- Price
1 ------- $2.00
2 ------- $3.00

Table C

Student ---- Allowance
Bill ------- $1.50
Sally ------ $2.75

Answer 1

Hi @Kevin Lyons ,

Welcome to Microsoft Q&A!

What is your expected output?

Please refer below and check whether it is helpful to you.

;with cte as (  
select a.week,a.Price restaurant,b.Price supermarket,c.Allowance  
from TableA a   
inner join tableB b   
on a.Week=b.Week  
cross join TableC c)  
,cte1 as (  
SELECT week,iif(restaurant<Allowance,restaurant,null) restaurant,  
iif(supermarket<Allowance,supermarket,null) supermarket  
FROM cte)  
select week,count(iif(restaurant<supermarket,null,restaurant)) restaurant,  
count(iif(restaurant<supermarket,supermarket,null)) supermarket  
from cte1   
group by week  

Output:

week	restaurant	supermarket  
1	1	1  
2	1	0  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.