How To Solve This Exercise

Transact-SQL
1

Tasks:

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

2

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

3

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

image
image.png1642×1304 120 KB