Offer price - SQL to obatin it

Question

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 22-Oct-17 9:23am

sterenas

Updated 22-Oct-17 11:02am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2017-10-22T11:02:00

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[^]