Click here to Skip to main content
15,069,297 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi! I'm having trouble to obtain the correct price if a product is on offer.
Table structure (we only need 1 offer at any time per SKU):
Table Products:
SKU, RegularPrice, OfferStartDate, OfferEndDate, OfferPrice
1, 100, 2017-01-01 00:00, 2017-12-31 23:59, 90
2, 200, 2017-01-01 00:00, 2017-10-30 23:59, 190
3, 300

I tried the following SQL to extract it but I can't seem to have it right as if I change SKU 1 OfferEndDate to yesterday, it still shows the offer price (should show 0 as it'd be expired):

SELECT SKU , RegularPrice, (SELECT ISNULL((SELECT OfferPrice FROM Products aaa WHERE SKU = aaa.SKU AND OfferStartDate < getdate() and OfferEndDate > getdate()),0) as OfferPrice) FROM Products 


1. I'm returning 2 columns here and I'd need to find out if OfferPrice > 0 then it's on offer. I need to return both prices to show them.

2. For a different query, how could I come up with a SQL statement that returns just one price column with the right price (ie Regular price of Offer price)?

Thanks for your help on this SQL issue!
Sergio

What I have tried:

Tried the SQL statement described above.
Posted
Updated 22-Oct-17 11:02am

1 solution

Not sure i understand you correctly...
Seems you want to obtain OfferPrice, but when it's null, then you want to return RegularPrice. So, you have to use COALESCE() function[^].

SQL
SELECT COALESCE(OfferPrice, RegularPrice) AS ActualPrice
FROM Products


As to your needs, you have to use left join[^].

Check this:
SQL
DECLARE @tmp TABLE(SKU INT IDENTITY(1,1) NOT NULL, RegularPrice FLOAT, OfferStartDate DATETIME, OfferEndDate DATETIME, OfferPrice FLOAT)
DECLARE @d DATETIME = GETDATE()

INSERT INTO @tmp(RegularPrice, OfferStartDate, OfferEndDate, OfferPrice)
VALUES(100, '2017-01-01 00:00', '2017-12-31 23:59', 90), 
(200, '2017-02-01 00:00', '2017-10-30 23:59', 190),
(300, '2017-03-01 00:00', '2017-10-15 23:59', 270),
(420, '2017-04-01 00:00', '2017-09-30 23:59', 390),
(280, '2017-05-01 00:00', '2017-11-30 23:59', 250)

SELECT A.SKU, COALESCE(B.OfferPrice, A.RegularPrice) AS CurrentPrice
FROM @tmp AS A
LEFT JOIN (
	SELECT *
	FROM @tmp
	WHERE OfferStartDate<= @d AND OfferEndDate>=@d
) AS B ON A.SKU = B.SKU


Result:
txt
SKU	CurrentPrice
1	90
2	190
3	300 --regular price, an offer expired
4	420 --regular price, an offer expired
5	250


For further details, please see: Deciding between COALESCE and ISNULL in SQL Server[^]
Visual Representation of SQL Joins[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900