CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
IvanN
IvanN

Reputation: 327

Selecting rows partially matching rows in another table

I have a table for the actions. Table

The table has several slots for the same time on the same day. Same action can't be booked for the same time twice. I'm trying to come up with the way to list all the IDs for an action 'A', such as every available time is listed only once, even if there are both slots available, but if 'A' is book for some time already and another slot for this time is empty, that slot wouldn't be showing. And it comes to me that I don't know T-SQL that good. I overcame this by selecting all the rows where 'A' is booked, selecting all distinct (date, time start and time end) which are not booked and doing check whether 'A' is already booked for this time. But all this checking is done on the software level, and those multiple requests to the server and looping in the program to perform the same job as one LIKELY SIMPLE sql request don't look very efficient to me. If there a way to do something like:

    SELECT ID FROM mytable
    WHERE Action IS NULL AND (date, time_start, time_end **'ALL TOGETHER IN ONE ROW'**)
    NOT IN (SELECT date, time_start, time_end FROM mytable 
            WHERE Action = 'A')
HAVING 'THOSE THREE BEING DISTINCT'

By other words can I select rows which partially match other rows? It would be simple if I had only one column to compare, but there are three.

Upvotes: 0

Views: 250

Answers (2)

HABO
HABO

Reputation: 15841

You question is a bit unclear, but I think this will point you in a productive direction. SQL is designed to perform operation on sets of rows, not to loop through processing one row at a time. The following code will correlate your data into one row for each pair of slots at each date/time. You can use a CASE expression, as shown, to add a column that indicates the status of the row, and you can then add a WHERE clause, not shown, to perform any additional filtering.

-- Sample data.
declare @Samples as Table ( SampleId Int, Slot Int, EventDate Date, StartTime Time(0), EndTime Time(0), Action VarChar(10) );
insert into @Samples ( SampleId, Slot, EventDate, StartTime, EndTime, Action ) values
  ( 200, 1, '20150501', '00:00:00', '00:30:00', NULL ),
  ( 201, 2, '20150501', '00:00:00', '00:30:00', NULL ),
  ( 202, 1, '20150501', '00:30:00', '01:00:00', 'A' ),
  ( 203, 2, '20150501', '00:30:00', '01:00:00', NULL ),
  ( 204, 1, '20150501', '01:00:00', '01:30:00', NULL ),
  ( 205, 2, '20150501', '01:00:00', '01:30:00', 'A' ),
  ( 206, 1, '20150501', '01:30:00', '02:00:00', 'B' ),
  ( 207, 2, '20150501', '01:30:00', '02:00:00', 'B' );
select * from @Samples;

-- Data correleated for each date/time.
select Slot1.EventDate, Slot1.StartTime, Slot1.EndTime,
  Slot1.Action as Action1, Slot2.Action as Action2,
  Coalesce( Slot1.Action, Slot2.Action ) as SummaryAction,
  case when Slot1.Action = Slot2.Action then 'ERROR!' else 'Okay.' end as Status
  from @Samples as Slot1 inner join
    @Samples as Slot2 on Slot2.EventDate = Slot1.EventDate and Slot2.StartTime = Slot1.StartTime and
    Slot1.Slot = 1 and Slot2.Slot = 2;

Upvotes: 0

Nick Pfitzner
Nick Pfitzner

Reputation: 216

In SQL Server we generally use WHILE instead of FOR. I believe what you're trying to do could be fulfilled as follows if you want to loop through the table (ideally your ID field would be the PRIMARY KEY as well). This is just inserting it into a temp table for now, but potentially it should give you the results you want:

-- DECLARE and set counters
DECLARE @curr INT, @prev INT, @max INT
SELECT @curr = 0, @prev = 0, @max = MAX(ID) FROM myTable

-- Make a simple temp table
CREATE TABLE #temp (ID INT)

-- Start looping
WHILE (@curr < @max)
BEGIN
    -- Set our counter for the next row
    SELECT @curr = MIN(ID) FROM myTable WHERE ID > @prev

    -- Populate temp table with a self-join to compare slots
    -- Slot must match on date + time but NOT have equal SLOT value 
    -- Will only INSERT if we meet our criteria i.e. neither slot booked
    INSERT  INTO #temp
    SELECT  DISTINCT A.ID
    FROM    myTable A
    JOIN    myTable B ON B.[Date] = A.[date] AND B.time_start = A.time_start AND B.time_end = A.time_end
    WHERE   A.[Action] IS NULL  -- Indicates NO booking
    AND     B.[Action] IS NULL  -- Indicates NO booking
    AND     A.SLOT <> B.SLOT
    AND     A.ID = @curr

    -- Update our counter         
    SET @prev = @curr
END

-- Get all our records
SELECT * FROM #temp

-- Remove the sleeping dog ;)
DROP TABLE #temp

There is a little bit of redundancy here because it checks ALL rows, even if a condition has been found in the first row of that time slot, but you can tweak it from here if you need to.

You should really avoid using field names like "Date" and "Action" because these are reserved words in SQL.

Upvotes: 1

Related Questions