How To Solve This Exercise

A. Identify which pairs of rows have identical Products, Customers and Measures,
and overlapping date ranges.

``````An example is rows 1 and 2 where the Product, Customer, Measure and Date
Ranges overlap between 20130301 and 20130401.

Store your output in table #PartA defined below.
``````

B. Create a process to "fix" the overlapping date ranges you have identified in
above, so that they no longer overlap.

``````How to do this is up to you, but note any assumptions as part of Part C.
``````

C. Document any assumptions or decisions you needed to make for your solution.

--Test Setup

BEGIN

USE tempdb

IF OBJECT_ID('tempdb..#TestInput') IS NOT NULL DROP TABLE #TestInput

CREATE TABLE #TestInput
(
RowID INT NOT NULL IDENTITY(1,1)
, Product VARCHAR(100) NOT NULL
, Customer VARCHAR(100) NOT NULL
, Measure VARCHAR(100) NOT NULL
, Value FLOAT NOT NULL
, Valid_From_Day INT NOT NULL
, Valid_To_Day INT NOT NULL
)

TRUNCATE TABLE #TestInput

INSERT INTO #TestInput
(
Product
, Customer
, Measure
, Value
, Valid_From_Day
, Valid_To_Day
)
VALUES
('Widgets', 'Tesco', 'Gross Sales Price', 1 , 20130101, 20130401)
, ('Widgets', 'Tesco', 'Gross Sales Price', 1.5, 20130301, 20131231)
, ('Widgets', 'Tesco', 'Gross Sales Price', 2 , 20130401, 20150101)
, ('Widgets', 'Tesco', 'Distribution Cost', 5 , 20130101, 20130401)
, ('Widgets', 'Tesco', 'Distribution Cost', 6 , 20130301, 20140401)
, ('Widgets', 'Tesco', 'Distribution Cost', 7 , 20131231, 20150101)
, ('Widgets', 'Asda' , 'Gross Sales Price', 100, 00000000, 99999999)
, ('Widgets', 'Asda' , 'Gross Sales Price', 200, 20131231, 20150101)
, ('Widgets', 'Asda' , 'Distribution Cost', 2 , 20130301, 20131231)
, ('Widgets', 'Asda' , 'Distribution Cost', 3 , 20140101, 20150101)

SELECT * FROM #TestInput

END

Self join

Tableabc a Tableabc b

Where a.column = b.column

A.Date between b.date ..for this part can Google search

Fixing .what do want to do
How fix

Hi

What does this mean ?
Date Ranges overlap between 20130301 and 20130401. ..

I mean in terms of ValidFromDay ValidToDay
Please give explanation .. so we can write SQL

Create Data Script
``````USE tempdb

DROP TABLE #TestInput

CREATE TABLE #TestInput
(
RowID INT NOT NULL IDENTITY(1,1)
, Product	VARCHAR(100) NOT NULL
, Customer	VARCHAR(100) NOT NULL
, Measure	VARCHAR(100) NOT NULL
, Value	FLOAT NOT NULL
, Valid_From_Day	INT NOT NULL
, Valid_To_Day INT NOT NULL
)

TRUNCATE TABLE #TestInput

INSERT INTO #TestInput
(
Product
, Customer
, Measure
, Value
, Valid_From_Day
, Valid_To_Day
)
VALUES
('Widgets', 'Tesco', 'Gross Sales Price', 1	, 20130101, 20130401)
, ('Widgets', 'Tesco', 'Gross Sales Price', 1.5, 20130301, 20131231)
, ('Widgets', 'Tesco', 'Gross Sales Price', 2	, 20130401, 20150101)
, ('Widgets', 'Tesco', 'Distribution Cost', 5	, 20130101, 20130401)
, ('Widgets', 'Tesco', 'Distribution Cost', 6	, 20130301, 20140401)
, ('Widgets', 'Tesco', 'Distribution Cost', 7	, 20131231, 20150101)
, ('Widgets', 'Asda' , 'Gross Sales Price', 100, 00000000, 99999999)
, ('Widgets', 'Asda' , 'Gross Sales Price', 200, 20131231, 20150101)
, ('Widgets', 'Asda' , 'Distribution Cost', 2	, 20130301, 20131231)
, ('Widgets', 'Asda' , 'Distribution Cost', 3	, 20140101, 20150101)
``````
SQL
``````SELECT *
FROM   #testinput a
JOIN #testinput b
ON a.product = b.product
AND a.customer = b.customer
AND a.measure = b.measure
``````
Result looks like this