Help with date ranges
-
Hi all,
I'd like to know if anyone can help me with a good suggestion on how to achieve this.
I have these 2 tables:
And I want to create a query to achieve this third table:
Basically, in the table Discounts I have all the discounts associated with a certain Product_Code and I want to include the ListPrice to be able to calculate the FinalPrice.
The formula to achieve the FinalPrice is: ListPrice * (1-0,1) * (1-0,15)
So, for example, for the first line it is: 600 * (1-0,1) * (1-0,15) = 459.The issue that I'm having is that I need to cover the original date ranges for the discounts, even if they don't have a ListPrice, hence why there is a N/A in the last record.
The table scripts I created are the following:
CREATE TABLE Discounts (
ProductCode VARCHAR (255),
ValidFrom DATE,
ValidTo DATE,
Discount1 VARCHAR (255),
Discount2 VARCHAR (255),
)
;INSERT INTO Discounts
(ProductCode, ValidFrom, ValidTo, Discount1, Discount2)
VALUES
('P000020','01-01-2005','01-01-2009','0,1', '0,15'),
('T000090','01-01-2005','01-01-2007','0,2','0,3');
CREATE TABLE ListPrice (
ProductCode VARCHAR (255),
ListPrice VARCHAR (255),
ValidFrom DATE,
ValidTo DATE
)
;INSERT INTO ListPrice
(ProductCode, ListPrice, ValidFrom, ValidTo)
VALUES
('P000020', '600', '01-01-2004','01-01-2006'),
('P000020', '610', '01-02-2006','01-01-2008'),
('P000020', '620', '01-02-2008','01-01-2012'),
('T000090', '400', '01-01-2004','01-01-2006');
CREATE TABLE FinalPrice (
ProductCode VARCHAR (255),
ValidFrom DATE,
ValidTo DATE,
ListPrice VARCHAR (255),
Discount1 VARCHAR (255),
Discount2 VARCHAR (255),
FinalPrice VARCHAR (255)
)
;INSERT INTO FinalPrice
(ProductCode, ValidFrom, ValidTo, ListPrice, Discount1, Discount2, FinalPrice)
VALUES
('P000020', '01/01/2005', '01/01/2006','600', '0,1', '0,15', '459'),
('P000020', '01/02/2006', '01/01/2008','610', '0,1', '0,15', '466,65'),
('P000020', '01/02/2008', '01/01/2009','620', '0,1', '0,15', '474,3'),
('T000090', '01/01/2005', '01/01/2006','400', '0,2', '0,3', '224'),
('T000090', '01/02/2006', '01/01/2007','N/A', '0,2', '0,3', 'N/A')Does anyone know how to achieve this and keep the original discount dates in the table FinalPrice even if they don't have a ListPrice?
Any help is much appreciated! - Vegeta7 - Saturday, October 21, 2017 11:34 AMHi all,
I'd like to know if anyone can help me with a good suggestion on how to achieve this.
I have these 2 tables:
And I want to create a query to achieve this third table:
Basically, in the table Discounts I have all the discounts associated with a certain Product_Code and I want to include the ListPrice to be able to calculate the FinalPrice.
The formula to achieve the FinalPrice is: ListPrice * (1-0,1) * (1-0,15)
So, for example, for the first line it is: 600 * (1-0,1) * (1-0,15) = 459.The issue that I'm having is that I need to cover the original date ranges for the discounts, even if they don't have a ListPrice, hence why there is a N/A in the last record.
The table scripts I created are the following:
CREATE TABLE Discounts (
ProductCode VARCHAR (255),
ValidFrom DATE,
ValidTo DATE,
Discount1 VARCHAR (255),
Discount2 VARCHAR (255),
)
;INSERT INTO Discounts
(ProductCode, ValidFrom, ValidTo, Discount1, Discount2)
VALUES
('P000020','01-01-2005','01-01-2009','0,1', '0,15'),
('T000090','01-01-2005','01-01-2007','0,2','0,3');
CREATE TABLE ListPrice (
ProductCode VARCHAR (255),
ListPrice VARCHAR (255),
ValidFrom DATE,
ValidTo DATE
)
;INSERT INTO ListPrice
(ProductCode, ListPrice, ValidFrom, ValidTo)
VALUES
('P000020', '600', '01-01-2004','01-01-2006'),
('P000020', '610', '01-02-2006','01-01-2008'),
('P000020', '620', '01-02-2008','01-01-2012'),
('T000090', '400', '01-01-2004','01-01-2006');
CREATE TABLE FinalPrice (
ProductCode VARCHAR (255),
ValidFrom DATE,
ValidTo DATE,
ListPrice VARCHAR (255),
Discount1 VARCHAR (255),
Discount2 VARCHAR (255),
FinalPrice VARCHAR (255)
)
;INSERT INTO FinalPrice
(ProductCode, ValidFrom, ValidTo, ListPrice, Discount1, Discount2, FinalPrice)
VALUES
('P000020', '01/01/2005', '01/01/2006','600', '0,1', '0,15', '459'),
('P000020', '01/02/2006', '01/01/2008','610', '0,1', '0,15', '466,65'),
('P000020', '01/02/2008', '01/01/2009','620', '0,1', '0,15', '474,3'),
('T000090', '01/01/2005', '01/01/2006','400', '0,2', '0,3', '224'),
('T000090', '01/02/2006', '01/01/2007','N/A', '0,2', '0,3', 'N/A')Does anyone know how to achieve this and keep the original discount dates in the table FinalPrice even if they don't have a ListPrice?
Any help is much appreciated!What is the 0,1 indicates? Is it for discount percentage or amount? Please clarify yo hrlp i best
- kannan_egd - Sunday, October 22, 2017 1:36 AM
I assumed a percentage as well. For example, 600 - 10% - 15% = 459, which is the value in their Final table.
The OP, however, is storing those values as varchars and with commas (numbers don't have commas in them), meaning that they can do no "maths" on them without conversion and character replacement. With a big table, this query is going to perform awfully unless the data types are fixed.
Edit: Typo (as always).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Sunday, October 22, 2017 4:05 AM
To add to that, FinalPrice is expected to store both a number and 'N/A'. Would be better to store a NULL, and do the 'N/A' in the presentation layer
-
Ok, this gets you MOST of the way there. Couple of things though:
- Numbers don't have commas. It's 0.1 not 0,1 (which is a comma separated list of numbers 0 and 1). In my data I use proper numbers.
- Numbers should be stored as numbers, not strings. In my solution, I store the numbers as either integers or decimals.
- Your date ranges aren't inclusive. For example, you're ValidTo is 2017-0-01 and your next ValidFrom is 2017-02-01. What happens between 2017-01-01 and 2017-02-01? This query returns NULL for this period,as it appears there are not valid products for that date range. If this shouldn't be the case, your ValidTo should be 2017-01-31. Months have more than 1 day in them.
- N/A is not displayed, like Des mentioned. NULL is returned instead. Put the N/A in your presenation layer. N/A is not a nu,ber, so you can't mix the data types.
Anyway, here's my answer:
WITH Dates AS (
SELECT ValidFrom, ProductCode
FROM ListPrice
UNION
SELECT ValidTo, ProductCode
FROM ListPrice
UNION
SELECT ValidFrom, ProductCode
FROM Discounts
UNION
SELECT ValidTo, ProductCode
FROM Discounts),
DateRanges AS (
SELECT ValidFrom, LEAD(ValidFrom) OVER (PARTITION BY ProductCode ORDER BY ValidFrom) AS ValidTo,
ProductCode
FROM Dates)
SELECT DR.ProductCode, DR.ValidFrom, DR.ValidTo,
LP.ListPrice,
D.Discount1, D.Discount2,
LP.ListPrice * (1 - D.Discount1) * (1 - D.Discount2) AS FinalPrice
FROM DateRanges DR
JOIN Discounts D ON DR.ProductCode = D.ProductCode
AND DR.ValidFrom BETWEEN D.ValidFrom AND D.ValidTo
AND DR.ValidTo BETWEEN D.ValidFrom AND D.ValidTo
LEFT JOIN ListPrice LP ON D.ProductCode = LP.ProductCode
AND DR.ValidFrom BETWEEN LP.ValidFrom AND LP.ValidTo
AND DR.ValidTo BETWEEN LP.ValidFrom AND LP.ValidTo
WHERE DR.ValidTo IS NOT NULL
ORDER BY DR.ProductCode, DR.ValidFrom;
If you don't understand any of it, please reply and ask.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you very much for your answer Thom A !
To answer your points:
1) I live in Europe and I guess it's the inverse from the US 🙂 here we use commas to mark the decimal points. This happens in excel, for example, although I think it in SQL Server it uses dots, so I will work on that conversion.
2) Because of the above I didn't use int or decimals, but I will surely change that now 🙂
3) The dates in Europe are always dd-mm-yyyy. And for some reason, I had to write mm-dd-yyyy in the script to get to dd-mm-yyyy in SSMS. Confusing I know, and apologies for that.
4) I agree with both of you, thank you for pointing this out to me Thom A and DesNorton 🙂 I really appreciate it!Thanks again Thom A, I ran your query and this is the answer I was looking for 🙂 I have 2 extra columns and I understand why they are there and I will try to exclude them, but nonetheless, this is exactly what I was looking for 😀
- Vegeta7 - Sunday, October 22, 2017 6:08 AM
I'm European. Numbers use periods, not commas. 😉
In regards to your dates Im guessing your language is set to English, rather than British, French, German (other European language). Hence dates in dd-MM-yyyy are read as MM-dd-yyyy. SQL Server reads date strings in the format yyyy-MM-dd as yyyy-dd-MM, regardless of your language setting. I believe this is a "feature". Hence I generally use the format yyyyMMdd (without the hyphens) when passing dates. This means there is no confusion, especially across laguages.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Assuming that 01-02-2017 is 2nd January 2017, then full script with correct data types and date values:
USE Sandbox;
GOCREATE TABLE Discounts (
ProductCode VARCHAR (255),
ValidFrom DATE,
ValidTo DATE,
Discount1 decimal(4,3),
Discount2 decimal(4,3));INSERT INTO Discounts
(ProductCode, ValidFrom, ValidTo, Discount1, Discount2)
VALUES
('P000020','20050101','20090101',0.1,0.15),
('T000090','20050101','20070101',0.2,0.3);CREATE TABLE ListPrice (
ProductCode VARCHAR(255),
ListPrice int,
ValidFrom DATE,
ValidTo DATE);INSERT INTO ListPrice
(ProductCode, ListPrice, ValidFrom, ValidTo)
VALUES
('P000020', 600, '20040101','20060101'),
('P000020', 610, '20060102','20080101'),
('P000020', 620, '20080102','20120101'),
('T000090', 400, '20040101','20060101');
GO
/*
SELECT *
FROM ListPrice;
SELECT *
FROM Discounts;
*/
GO
WITH Dates AS (
SELECT ValidFrom, ProductCode
FROM ListPrice
UNION
SELECT ValidTo, ProductCode
FROM ListPrice
UNION
SELECT ValidFrom, ProductCode
FROM Discounts
UNION
SELECT ValidTo, ProductCode
FROM Discounts),
DateRanges AS (
SELECT ValidFrom, LEAD(ValidFrom) OVER (PARTITION BY ProductCode ORDER BY ValidFrom) AS ValidTo,
ProductCode
FROM Dates)
SELECT DR.ProductCode, DR.ValidFrom, DR.ValidTo,
LP.ListPrice,
D.Discount1, D.Discount2,
LP.ListPrice * (1 - D.Discount1) * (1 - D.Discount2) AS FinalPrice
FROM DateRanges DR
JOIN Discounts D ON DR.ProductCode = D.ProductCode
AND DR.ValidFrom BETWEEN D.ValidFrom AND D.ValidTo
AND DR.ValidTo BETWEEN D.ValidFrom AND D.ValidTo
LEFT JOIN ListPrice LP ON D.ProductCode = LP.ProductCode
AND DR.ValidFrom BETWEEN LP.ValidFrom AND LP.ValidTo
AND DR.ValidTo BETWEEN LP.ValidFrom AND LP.ValidTo
WHERE DATEDIFF(DAY, DR.ValidFrom, DR.ValidTo) > 1 AND DR.ValidTo IS NOT NULL
ORDER BY DR.ProductCode, DR.ValidFrom;
GO
DROP TABLE ListPrice;
DROP TABLE Discounts;
GO
There is a slight correction, as if that is the case, then the 1 day ranges need to be removed. This is done by the addition of an extra clause the WHERE.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Sunday, October 22, 2017 8:53 AM
You Sir are incredible! That's perfect! Many thanks 😀
- Vegeta7 - Sunday, October 22, 2017 9:33 AM
Do you understand everything it's doing?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Sunday, October 22, 2017 9:39 AM
Yes, so far so good. I will be applying this to my real data and workout any other possibilities. I did notice that for the product_code T000090, the second record generated has a ValidFrom equal to 2006-01-01 rather than 2006-01-02. That's what I'm trying to solve right now 🙂
-
When I read the suggested solution, I knew there had to be a better approach, because it's scanning each of the tables three times: once for the beginning dates, once for the end dates, and once for the values. I came up with a solution that only requires one scan.
;
WITH Combined AS
(
SELECT ProductCode, v.ValidDate, v.ListPrice, 0 AS Discount1, 0 AS Discount2
FROM #ListPrice
CROSS APPLY
(
SELECT ValidFrom, ListPriceUNION ALL
SELECT ValidTo, -ListPrice
) v(ValidDate, ListPrice)
UNION ALL
SELECT ProductCode, d.ValidDate, 0, d.Discount1, d.Discount2
FROM #Discounts
CROSS APPLY
(
SELECT ValidFrom, Discount1, Discount2UNION ALL
SELECT DATEADD(DAY, 1, ValidTo), -Discount1, -Discount2
) d(ValidDate, Discount1, Discount2)
)
, RunningValues(ProductCode, ValidFrom, ValidTo, ListPrice, Discount1, Discount2) AS
(
SELECT ProductCode
, ValidDate
, DATEADD(DAY, -1, LEAD(ValidDate, 1, '9999-12-30') OVER(PARTITION BY ProductCode ORDER BY ValidDate))
, SUM(ListPrice) OVER(PARTITION BY ProductCode ORDER BY ValidDate ROWS UNBOUNDED PRECEDING)
, SUM(Discount1) OVER(PARTITION BY ProductCode ORDER BY ValidDate ROWS UNBOUNDED PRECEDING)
, SUM(Discount2) OVER(PARTITION BY ProductCode ORDER BY ValidDate ROWS UNBOUNDED PRECEDING)
FROM Combined
)
SELECT *
FROM RunningValues
CROSS APPLY( VALUES(NULLIF(ListPrice, 0) * (1 - Discount1) * (1 - Discount2) ) ) fp(FinalPrice)
WHERE ValidFrom < ValidTo
AND (Discount1 > 0 OR Discount2 > 0)
;Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, October 25, 2017 8:59 AM
Thanks a ton Drew! I will analyze and study this over the weekend 🙂 All these answers are helping me a lot to understand and learn more about SQL 😀
- Thom A - Sunday, October 22, 2017 8:53 AM
Hi Thom A,
I was working om your query before applying it to my real case. I added a small detail so the last line will be exactly as the FinalPrice Table I mentioned 🙂 Just a little small detail. Also, I want to thank you again for helping me and giving me such a good opportunity to learn more about how to create these queries.Without any further ado, here is the small detail:
WITH Dates AS (
SELECT ValidFrom, ProductCode
FROM ListPrice
UNION
SELECT ValidTo, ProductCode
FROM ListPrice
UNION
SELECT ValidFrom, ProductCode
FROM Discounts
UNION
SELECT ValidTo, ProductCode
FROM Discounts),DateRanges AS (
SELECT
ValidFrom,
LEAD(ValidFrom) OVER (PARTITION BY ProductCode ORDER BY ValidFrom) AS ValidTo,
ProductCode
FROM Dates)SELECT
DR.ProductCode,
CASE WHEN ListPrice IS NULL THEN DATEADD(day,1,DR.ValidFrom) ELSE DR.ValidFrom END AS ValidFrom,
DR.ValidTo,
LP.ListPrice,
D.Discount1, D.Discount2,
LP.ListPrice * (1 - D.Discount1) * (1 - D.Discount2) AS FinalPrice
FROM DateRanges DR
JOIN Discounts D ON DR.ProductCode = D.ProductCode
AND DR.ValidFrom BETWEEN D.ValidFrom AND D.ValidTo
AND DR.ValidTo BETWEEN D.ValidFrom AND D.ValidTo
LEFT JOIN ListPrice LP ON D.ProductCode = LP.ProductCode
AND DR.ValidFrom BETWEEN LP.ValidFrom AND LP.ValidTo
AND DR.ValidTo BETWEEN LP.ValidFrom AND LP.ValidTo
WHERE DATEDIFF(DAY, DR.ValidFrom, DR.ValidTo) > 1 AND DR.ValidTo IS NOT NULL
ORDER BY DR.ProductCode, DR.ValidFromAs for your query, drew.allen,I'm still trying to understand it as I'm new to the Cross Apply 🙂 But I'l get there and also many thanks again for taking the time to help me 😀
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply