query to retrieve all status if a given status falls in a specific date range

Question

query to retrieve all status if a given status falls in a specific date range

ssisnovice

SSC-Addicted

Points: 481
More actions
October 3, 2011 at 1:20 pm

#244765

Hi, I have a table with item_id,status,datetime columns
item_id status date
534 Evaluated 2011-09-01 00:00:00.000
635 Evaluated 2010-09-30 00:00:00.000
635 Data Entry 2011-09-05 17:55:49.000
534 Initiated 2011-09-25 17:55:49.000
534 Completed 2011-09-25 17:55:49.000
my requirement is to fetch all the statuses for given item_id when status ='Evaluated' with in given time period.
Suppose if the status ='Evaluated' where the status_date between 2011-09-01 and 2011-09-30 then i need to have all the rows for that particular item_id irrespective of any status.
Ex:- in the above table for item_id 534 with status 'evaluated' and status_date between 2011-09-01 and 2011-09-30 should fetch me all this rows
534 Evaluated 2011-09-01 00:00:00.000
534 Initiated 2011-09-25 17:55:49.000
534 Completed 2011-09-25 17:55:49.000
Thank you

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your sample data didn't show the "or" part of your query...all the data would be returned regardless, because the4y are all between your start and end dates.

i changed the sample data slightly,and i think this is what you want:

if an Evaluated record exists in the date range, return all data

else

return just hte records matching the date range.

CREATE TABLE #mySampleData (item_id int ,status varchar(30),date datetime )

INSERT INTO #mySampleData

SELECT 534,'Evaluated',CONVERT(datetime,'2011-09-01 00:00:00.000') UNION ALL

SELECT 635,'Evaluated','2010-09-30 00:00:00.000' UNION ALL

SELECT 635,'Data Entry','2011-09-05 17:55:49.000' UNION ALL

SELECT 534,'Initiated','2011-10-25 17:55:49.000' UNION ALL

SELECT 534,'Completed','2011-11-25 17:55:49.000'

SELECT #mySampleData.* FROM #mySampleData

WHERE item_id = 534

AND EXISTS(SELECT 1 FROM #mySampleData WHERE item_id = 534 AND status = 'Evaluated' AND date between '2011-09-01' and '2011-09-30' )

UNION

SELECT #mySampleData.* FROM #mySampleData

WHERE item_id = 534 AND date between '2011-09-01' and '2011-09-30'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!