CODEWITHSUNDEEP
sqlsql-server
James Khan
James Khan

Reputation: 841

Simple sql Multiple Record Sets

I want to select all assignement records for an asset that have a end date less than 1 July 2013 and that have an end date not greater than 1 July 2013 .

So really simple example data

Asset | Start Date | End Date 
1       01/06/2013   01/07/2013
1       02/07/2013   01/08/2013
1       02/06/2013   01/07/2013
2       01/06/2013   8/06/2013
2       14/0/2013    17/06/2013
2       18/06/2013   24/06/2013

So for this record set I expect no records to be returned for Asset 1, and for Asset 2 I expect 3 records returned.

At the moment I am returning for all records before 1 July 2013 but can seem to change my query to cater for the record with an end date after 1st July 2013.

Select * from table 1
where [End Date]< 01/07/2013

How to do this ?

Upvotes: 0

Views: 59

Answers (2)

Serpiton
Serpiton

Reputation: 3684

I'm not sure why you stated the condition twice

have a end date less than 1 July 2013

and that

have an end date not greater than 1 July 2013

an option is to get the last [End Date] for every Asset and check that against 1 July 2013

WITH MED AS (
  SELECT Asset, MAX([End Date]) AED
  FROM   Table1
  GROUP BY Asset
)
SELECT a.Asset, [Start Date], [End Date]
FROM   Table1 a
       INNER JOIN MED ON a.Asset = MED.Asset
WHERE  MED.AED <= '2013-07-01'

SQLFiddle demo

Upvotes: 1

gvee
gvee

Reputation: 17161

Assets that ended before 1st July

SELECT asset
     , start_date
     , end_date
FROM   assets
WHERE  end_date < '2013-07-01'

Assets that ended on or after 1st July

SELECT asset
     , start_date
     , end_date
FROM   assets
WHERE  end_date >= '2013-07-01'

It sounds to me like you want to find those that exist in the first list that don't exist in the second.

Enter the [NOT] EXISTS clause!

SELECT asset
     , start_date
     , end_date
FROM   assets
WHERE  end_date < '2013-07-01'
AND    NOT EXISTS (
         SELECT x.asset
         FROM   assets As x
         WHERE  x.end_date >= '2013-07-01'
         AND    x.asset = assets.asset
       )

Note that the query in the exists clause is "joined" back to it's "parent".

Upvotes: 1

Related Questions