SQL Query Help

Kevin Lyons 21 Reputation points

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

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points

    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  


    week	restaurant	supermarket  
    1	1	1  
    2	1	0  

    Best regards,

    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.

0 additional answers

Sort by: Most helpful