Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 83 - Compare rows in the same table and group the data

The challenge is to compare the data of the rows and group the input data. The data needs to be grouped based on the Product ID, Date, TotalLines, LinesOutOfService. You need to check each row in the table to see if that particular product has the different range of values for TotalLines and Linesoutofservice when compared to the rows below. If yes, then insert that date where a different range is encountered into the "End Date" column. If no change in the values are detected then insert a future date "01/12/2050". In the TotalCustomerCalls column you place the sum of CustomerCalls for the given Start Date / End Date group.

"12 tools for simpler, faster database development
From taking the pain out of comparing and deploying database schemas to helping you code SQL effortlessly, the tools in the SQL Developer Bundle cut the time spent in dull tasks. Sound good? Learn more."

Sample Data

ProductID Date	     TotalLines LinesOutOfService CustomerCalls
--------  ---------- ---------- ----------------- -------------
522       2010-04-05 345        5                 100
522       2010-04-06 345        5                 80
522       2010-04-07 120        4                 50
522       2010-04-08 345        5                 60
522       2010-04-09 345        5                 40
522       2010-04-10 345        5                 70
522       2010-04-11 117        20                300
522       2010-04-12 345        5                 55
522       2010-04-14 345        5                 75
522       2010-04-15 260        10                150
522       2010-04-16 345        5                 30
522       2010-04-17 345        5                 95
522       2010-04-19 345        5                 60

Expected Results

ProductID Start Date End Date   TotalLines LinesOutOfService TotalCustomerCalls
--------- ---------- ---------- ---------- ----------------- ------------------
522       2010-04-05 2010-04-06 345        5                 180
522       2010-04-07 2010-04-07 120        4                 50
522       2010-04-08 2010-04-10 345        5                 170
522       2010-04-11 2010-04-11 117        20                300
522       2010-04-12 2010-04-14 345        5                 130
522       2010-04-15 2010-04-15 260        10                150
522       2010-04-16 2050-12-01 345        5                 185

Rules

  1. The output should be ordered by ProductID, Start Date, End Date.
  2. There will be no duplicate dates within a Product ID.
  3. The data will contain more than one ProductID.

Sample Script

Use the TSQL Script given below to generate the source table and fill them with sample data.

    
IF OBJECT_ID('TC83','U') IS NOT NULL BEGIN
  DROP TABLE TC83
END 
GO

CREATE TABLE TC83(
	[ProductID] [INT] NOT NULL,
	[Date] DATETIME NOT NULL,
	[TotalLines] [INT],	
	[LinesOutOfService] [INT],
	[CustomerCalls] [INT]
) ON [PRIMARY]

GO

INSERT INTO TC83(ProductID,Date,TotalLines,LinesOutOfService,CustomerCalls) 
SELECT 522,'2010-04-05',345,5,100 UNION ALL
SELECT 522,'2010-04-06',345,5,80 UNION ALL
SELECT 522,'2010-04-07',120,4,50 UNION ALL
SELECT 522,'2010-04-08',345,5,60 UNION ALL
SELECT 522,'2010-04-09',345,5,40 UNION ALL
SELECT 522,'2010-04-10',345,5,70 UNION ALL
SELECT 522,'2010-04-11',117,20,300 UNION ALL
SELECT 522,'2010-04-12',345,5,55 UNION ALL
SELECT 522,'2010-04-14',345,5,75 UNION ALL
SELECT 522,'2010-04-15',260,10,150 UNION ALL
SELECT 522,'2010-04-16',345,5,30 UNION ALL
SELECT 522,'2010-04-17',345,5,95 UNION ALL
SELECT 522,'2010-04-19',345,5,60 

SELECT * FROM TC83
GO

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
  3. Your solution can use features from all versions up to and including SQL Server 2012.
"12 tools for simpler, faster database development
From taking the pain out of comparing and deploying database schemas to helping you code SQL effortlessly, the tools in the SQL Developer Bundle cut the time spent in dull tasks. Sound good? Learn more."

puzzles
13 · 13% · 4057
Submit a Solution
Previous Challenge
3
Liked
12
Comments
69
Solutions



Submit

TSQL Challenge 83 - Compare rows in the same table and group the data

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.