question

KevinLyons-8299 avatar image
0 Votes"
KevinLyons-8299 asked Viorel-1 edited

SQL Query Help

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That's to less on information.

Please post table design as DDL, some sample data as DML statement and the expected result.

Write a query that will provide the total number of lemonades sold by the restaurant and the supermarket each week.

So a homework question? You learn more by solving it on your own.




1 Vote 1 ·

It is not homework for me but my nephew and it was stumping me!

Melissa's solution looks spot on visually; I will test shortly.

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered Viorel-1 edited

Hi @KevinLyons-8299,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Melissa,

Thank you so much; it worked exactly as needed!

My nephew (and I) will learn from your approach.

Much appreciated.

Kevin

0 Votes 0 ·

Maybe the answer must be different?

week restaurant supermarket
  1   1    1
  2   1    1

It depends on what Allowance means.

0 Votes 0 ·