SQL CASE WHEN CROSS JOIN

Question

I am trying to do a calculation based on a lookup table doing a cross join and I am trying to select just 1 record per ID. This is on a SQL Server 2019 server.

I want 1 record from the TechFee table and the TechFee from the TechFeeTiers table where the projected spend is equal or greater than the target spend.

select 
  tf.[BrandSupplierTechFeeId],
    tf.[BuyerContactId],
    tf.[ReviewCompleted],
    tf.[BrandSupplierId],
    tf.[PriorYearSpend],
    tf.[CurrentYearSpend],
    tf.[ProjectedSpend],
    CASE WHEN tf.[ProjectedSpend] >=tft.TargetSpend  and tft.Tier = 'Tier 1' THEN tft.TechFee
    WHEN tf.[ProjectedSpend] >=tft.TargetSpend  and tft.Tier = 'Tier 2' THEN tft.TechFee
    WHEN tf.[ProjectedSpend] >=tft.TargetSpend  and tft.Tier = 'Tier 2' THEN tft.TechFee
    WHEN tf.[ProjectedSpend] >=tft.TargetSpend  and tft.Tier = 'Tier 3' THEN tft.TechFee
    WHEN tf.[ProjectedSpend] >=tft.TargetSpend  and tft.Tier = 'Tier 4' THEN tft.TechFee
    WHEN tf.[ProjectedSpend] >=tft.TargetSpend  and tft.Tier = 'Tier 5' THEN tft.TechFee
    WHEN tf.[ProjectedSpend] >=tft.TargetSpend  and tft.Tier = 'Tier 6' THEN tft.TechFee
    ELSE NULL END CurrTechFee,
    tf.[PriorYearTechFee]
FROM   TechFees tf, TechFeeTiers tft
order by tf.[BrandSupplierTechFeeId],CurrTechFee desc

Expected output is one row per ID with the the projected spend closest to the TargetSpend.

| ID | ProjectedSpend | CurrTechFee |
|----|----------------|-------------|
|  1 |        4362452 |        7500 |
|  2 |    1630481.875 |        3500 |
|  3 |       17795582 |       10000 |
|  4 |    1010422.625 |        3500 |
|  5 |       16245146 |       10000 |
|  6 |        4430861 |        7500 |
|  7 |    1650944.625 |        3500 |
|  8 |       80948944 |       30000 |
|  9 |      336441216 |       50000 |
| 10 |      4396726.5 |        7500 |

My tables and query attempt are below in the SQL Fiddle link. Any help or guidance is much appreciated.

http://sqlfiddle.com/#!18/48b2b7/9/0

Well your query is using 30-year old syntax which is a cartesian join - is that a surprise? Can you include your desired results. — Stu, Aug 17 '23 at 20:52
*"and I am getting a cartesian product."* What else would you expect when you use an ANSI-89 cross join? If you want an `INNER JOIN` it's time to adopt the ANSI-92 JOIN syntax; it did come out ***31 years*** ago. — Thom A, Aug 17 '23 at 20:54
where tf.[ProjectedSpend] >=tft.TargetSpend seems to be missing from your code — siggemannen, Aug 17 '23 at 20:59
Are you looking for **all** the Tiers where ` tf.[ProjectedSpend] >=tft.TargetSpend` or the single tier that is closest? i.e. Are you trying to find what specific tier a row in `TechFees` is in? — Martin Smith, Aug 17 '23 at 21:03
"Are you looking for all the Tiers where ` tf.[ProjectedSpend] >=tft.TargetSpend` or the single tier that is closest? " Single tier. — tjestesjr, Aug 17 '23 at 21:05
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Aug 17 '23 at 21:11
I have updated my SQL to be ANSI-92 JOIN and updated the where clause as suggested by @siggemannen and I am much closer http://sqlfiddle.com/#!18/48b2b7/9 — tjestesjr, Aug 17 '23 at 21:11
One way would be to use a `CROSS APPLY` and `TOP 1` http://sqlfiddle.com/#!18/48b2b7/10 - if the table is large or you don't have a useful index for this there will probably be better performing ways though — Martin Smith, Aug 17 '23 at 21:14
@MartinSmith Thank you very much that is what I am looking for! — tjestesjr, Aug 17 '23 at 21:18

SQL CASE WHEN CROSS JOIN

0 Answers0