spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

How to compare 2 records of same table?


If you're using SQL Server 2012 or later you can use theLAG()andLEAD()window functions to access previous and next rows and use a query like below to render your desired result:

-- 2012+ version 
SELECT 
    ScheduledID, StartTime, EndTime,
ModifiedTimeStamp,
    CASE 
       WHEN prevts IS NULL THEN 'Schedule created'

       WHEN prevStartTime <> StartTime AND
prevEndTime = EndTime AND nextts IS NOT NULL 
          THEN CONCAT('Start Time Modified FROM ',
prevStartTime, ' to ', StartTime)
       WHEN prevStartTime <> StartTime AND
prevEndTime <> EndTime AND nextts IS NOT
NULL 
          THEN CONCAT('Start Time Modified FROM ',
prevStartTime, ' to ', StartTime, 
          ', End Time Modified FROM ',
prevEndTime, ' to ', EndTime)
       WHEN prevStartTime = StartTime AND
prevEndTime <> EndTime AND nextts IS NOT
NULL 
          THEN CONCAT('End Time Modified FROM ',
prevEndTime, ' to ', EndTime)
       WHEN nextts IS NULL 
          THEN 'Current Schedule' 
    END as 'Action Type'
FROM (
SELECT 
    ScheduledID, 
    prevStartTime = LAG(StartTime)  OVER
(PARTITION by ScheduledID ORDER BY
ModifiedTimeStamp), 
    StartTime, 
    nextStartTime = LEAD(StartTime) OVER
(PARTITION by ScheduledID ORDER BY
ModifiedTimeStamp), 
    prevEndTime   = LAG(EndTime)     OVER
(PARTITION by ScheduledID ORDER BY
ModifiedTimeStamp), 
    EndTime, 
    nextEndTime = LEAD(EndTime)  OVER (PARTITION
by ScheduledID ORDER BY ModifiedTimeStamp), 
    prevts = LAG(ModifiedTimeStamp) OVER
(PARTITION by ScheduledID ORDER BY
ModifiedTimeStamp), 
    ModifiedTimeStamp, 
    nextts = LEAD(ModifiedTimeStamp) OVER
(PARTITION by ScheduledID ORDER BY
ModifiedTimeStamp)
FROM schedules
) a

If you're using a version older than 2012 you can use theROW_NUMBER()function and self-joins instead like below:

-- 2008 version
SELECT 
    curr.ScheduledID, curr.StartTime,
curr.EndTime, curr.ModifiedTimeStamp,
    CASE 
       WHEN prev.rn IS NULL THEN 'Schedule
created' 
       WHEN prev.StartTime <> curr.StartTime
AND prev.EndTime = curr.EndTime  
          THEN 'Start Time Modified FROM ' +
prev.StartTime + ' to ' + curr.StartTime 
       WHEN prev.StartTime <> curr.StartTime
AND prev.EndTime <> curr.EndTime AND next.rn
IS NOT NULL 
          THEN 'Start Time Modified FROM ' +
prev.StartTime + ' to ' + curr.StartTime 
          + ', End Time Modified FROM ' +
prev.EndTime + ' to ' + curr.EndTime 
       WHEN prev.EndTime <> curr.EndTime AND
next.rn IS NOT NULL 
          THEN 'End Time Modified FROM ' +
prev.EndTime + ' to ' + curr.EndTime 
       WHEN next.rn IS NULL THEN 'Current
schedule' 
    END as 'Action Type'
FROM (
    SELECT ScheduledID, StartTime, EndTime,
ModifiedTimeStamp, ROW_NUMBER() OVER (PARTITION by
ScheduledID ORDER BY ModifiedTimeStamp) rn
    FROM schedules) curr
LEFT JOIN (
    SELECT ScheduledID, StartTime, EndTime,
ModifiedTimeStamp, ROW_NUMBER() OVER (PARTITION by
ScheduledID ORDER BY ModifiedTimeStamp) rn
    FROM schedules
    ) prev on curr.rn = prev.rn + 1 AND
prev.ScheduledID = curr.ScheduledID
LEFT JOIN (
    SELECT ScheduledID, StartTime, EndTime,
ModifiedTimeStamp, ROW_NUMBER() OVER (PARTITION by
ScheduledID ORDER BY ModifiedTimeStamp) rn
    FROM schedules
    ) next on curr.rn = next.rn - 1 AND
curr.ScheduledID = next.ScheduledID
ORDER BY curr.rn

Sample SQL Fiddle for both versions.


Categories : SQL

Related to : How to compare 2 records of same table?
All records being deleted when deleting records from a temp table
Not sure what you get in @TheDocumentIDs, but I suppose it should work for you. First of all as @Chris mentioned, you should check put condition to where clause and as documentIds is a list of id's it should used with IN condition and not equality, that's why you need or use sp_executesql or fill id's to a temp table. EXECUTE sp_executesql N'delete from [master_group_documen

Categories : SQL
How to delete records in one table based on the values in another table
Not quite clear about the question. Sounds like: remove all wish_list entries for books that have quantity 0 in the book table? DELETE FROM wish_list WHERE isbn IN ( SELECT isbn FROM book WHERE quantity = 0 ); Or the update: UPDATE wish_list SET quantity = -1 WHERE isbn IN ( SELECT isbn FROM book WHERE quant

Categories : SQL
How to compare keys in a hash table with the values of an Array (stored in another Hash table)?
You can use map to quickly perform some of the operations you need, there are additional operators in List::MoreUtils that can help you do more with these types of data structures. #!/usr/bin/env perl use strict; use warnings; my @words_en = ('The', 'cat', 'eats', 'the', 'mouse'); my $conversion = { 'eats' => { 'manger' => '0.500000', 'mange' => '0.600000' }, 'cat' =

Categories : Perl
EF adds new many to many records to table
You need to find the EXISTING tags that already exist in your db rather than adding new ones. Replace this: foreach (var tag in blogpost.Tags.Where(t => t.IsChecked == true)) { tags.Add(new Tag { TagID = tag.TagID, Name = tag.Name }); } with: foreach (var tag in blogpost.Tags.Where(t => t.IsChecked == true)) { tags.Add(db.Ta

Categories : C#
Delete records from a given table
Unless I'm misunderstanding your question, your query is doing exactly what you want. You're deleting artists who have never recorded a jazz title. If you are trying to delete only artists who have recorded a jazz title, then you would change the WHERE clause to be WHERE t.artistid IS NOT NULL, or preferably, change your LEFT JOIN to an INNER JOIN

Categories : SQL
Recently Add
SQL command to insert two primary keys into third table
How to do a registerform by ASP.NET VB SQL
Shortest time and trip between two stops across many trip options (GTFS)
Using IN and EXISTS subquery with AND operation instead of OR?
How to select multiple alias columns from the same table
Delete records from a given table
date manipulation in postgresql
PSQL query with join query from a db newbie
Protect LocalDB from user access
Update field with combined contents from other fields
SQL select unique records under certain priority and conditions from multiple columns
Multiply quantities for all parent child relationships
I'm having an issue running a query from a batch file
Concatenate strings while filtering by another column in SQL Server view
Parsing Dynamic XML to SQL Server tables with Parent and child relation
Using MERGE for delete and insert in oracle
how to display the pre value and post value of a column after updating it?
sql loop to fill up date between two time stamps
With SQL/TSQL, how can I use STUFF within this WHILE clause?
Challenging db table and query
Query to fetch row corresponding to Max value in SQL
Oracle SQL - ORA-00936 on DATE()
how to split single row into multiple row in db2?
Combine fields into one column
How can I perform an SQl where query then list properly?
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.