December 3, 2014 at 4:35 am
I need to run a compare and return differences. I am finding it tough to express the SQL query in a syntax that works. The logic is there and I can explain but the syntax alludes me, can you help?
I have 1 table (locations) in a schema that hosts 2 primary keys 'locationID' and 'materialID'. Now i hold 3 locations in my table - PlayM PlayB and Ing. It is often the case that I have a materialID available on each location.
What I require to check is the integer value duration for each materialID on each locationID. If one the durations is not matching its peers for a result to return with all materialID on each location with the discrepancy under duration.
Example;
+------------+------------+----------+
| LOCATIONID | MATERIALID | DURATION |
+------------+------------+----------+
| PlayM | Clip1 | 626 |
| PlayB | Clip1 | 626 |
| Ing | Clip1 | 626 |
| PlayM | Clip2 | 600 |
| PlayB | Clip2 | 590 |
| Ing | Clip2 | 600 |
+------------+------------+----------+
Clip1 would not return a result as all location have the same duration. However clip 2 should report all 3 as one of the durations differ.
My example query would be as follows... note I am lost when referring to not equal 🙁
SELECT * FROM locations
WHERE materialID IN (SELECT materialID WHERE duration <>
Can you help?
Thanks....
SQL Learn
December 3, 2014 at 4:51 am
-- Sample data
IF OBJECT_ID ('tempdb..#Locations') IS NOT NULL DROP TABLE #Locations
CREATE TABLE #Locations (LOCATIONID VARCHAR(5), MATERIALID VARCHAR(5), DURATION SMALLINT)
INSERT INTO #Locations VALUES
('PlayM', 'Clip1', 626),
('PlayB', 'Clip1', 626),
('Ing', 'Clip1', 626),
('PlayM', 'Clip2', 600),
('PlayB', 'Clip2', 590),
('Ing', 'Clip2', 600);
WITH FlaggedData AS (
SELECT LOCATIONID, MATERIALID, DURATION,
MIN_DURATION = MIN(DURATION) OVER(PARTITION BY MATERIALID),
MAX_DURATION = MAX(DURATION) OVER(PARTITION BY MATERIALID)
FROM #Locations
)
SELECT LOCATIONID, MATERIALID, DURATION
FROM FlaggedData
WHERE MIN_DURATION <> MAX_DURATION
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 3, 2014 at 4:58 am
or
-- *** Test Data in Consumable Format ***
-- Please provide this in future
CREATE TABLE #t
(
LocationId varchar(20) NOT NULL
,MaterialId varchar(20) NOT NULL
,Duration int NOT NULL
);
INSERT INTO #t
VALUES ('PlayM', 'Clip1', 626)
,('PlayB', 'Clip1', 626)
,('Ing', 'Clip1', 626)
,('PlayM', 'Clip2', 600)
,('PlayB', 'Clip2', 590)
,('Ing', 'Clip2', 600);
-- *** End Test Data ***
WITH Vars
AS
(
SELECT MaterialID
,VAR(Duration) AS VarDuration
FROM #t
GROUP BY MaterialID
)
SELECT *
FROM #t T
WHERE NOT EXISTS
(
SELECT 1
FROM Vars V
WHERE V.MaterialId = T.MaterialId
AND V.VarDuration = 0
);
December 3, 2014 at 6:12 am
or
SELECT LocationId, MaterialId, Duration
FROM (
SELECT LocationId, MaterialId, Duration,
VAR(Duration) OVER (partition by MaterialID) AS VarDuration
FROM #t
) d
WHERE VarDuration > 0
😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 3, 2014 at 9:28 am
Thanks for all your responses.
I wanted to avoid creating a temp table for this process so I have opted to use the
SELECT LocationId, MaterialId, Duration
FROM (
SELECT LocationId, MaterialId, Duration,
VAR(Duration) OVER (partition by MaterialID) AS VarDuration
FROM #t
) d
WHERE VarDuration > 0
However this syntax at (partition is reported as an an error code 1064..... Odd.
Is Partition valid in MySQL?
I think I have posted in the wrong forum..... apologies... Not totally awake 🙁
December 3, 2014 at 9:34 am
James.Rivers (12/3/2014)
Thanks for all your responses.I wanted to avoid creating a temp table for this process so I have opted to use the
SELECT LocationId, MaterialId, Duration
FROM (
SELECT LocationId, MaterialId, Duration,
VAR(Duration) OVER (partition by MaterialID) AS VarDuration
FROM #t
) d
WHERE VarDuration > 0
However this syntax at (partition is reported as an an error code 1064..... Odd.
Is Partition valid in MySQL?
I think I have posted in the wrong forum..... apologies... Not totally awake 🙁
It works in current versions of SQL Server and DB2. No idea if it works with MySQL, sorry.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply