noted thanks :

DECLARE @Table TABLE

(

code VARCHAR(10)

,Description VARCHAR(100)

);

INSERT INTO @Table

...

Hi guys

I managed to sort it out, I used the query Chris gave me.

Thanks a million!!!

what I want is the record with the minimum date per asset, in the case of 1004 and 1005 the date is the same and 1004 is the minimum record,...

thank you for the quick response,

my question is now, I technically I don't have ROWId in my table, I've it there In the example so I can distinguish easily which...

Thank you so much guys for your solutions, it worked perfectly!!

thanks

Thank you so much, it works

Hi John

here's the sample data, hope this makes sense:

CREATE TABLE CountWO

(WorkOrderCode varchar(10) null,RequiredByDate datetime null);

DECLARE @StartDate DATETIME,

DECLARE @EndDate DATETIME,

SET @StartDate = '20160308';

SET @EndDate = '20160313'

INSERT INTO CountWO (WorkOrderCode,RequiredByDate) VALUES ('S00172','2016-03-01...

Thanks John for the quick response.

The only issue with this query is I don't know what selection date is the user going to use when running the report since the...

you are a genuis

Hi John

I must say the script it's very complicated. I've managed to test the few and it works.

only if the dates is in the midnight i.e.

INSERT INTO #dates VALUES ('DA6557','2016-06-22...

there will always be a startdate.

each code i.e jls will not have more than 1 null end date

Hi J

according to your sample data. the results I'm expecting is

INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01',NULL)

this means the downtime is still open.

How do I go about doing that?

Hi

Can you help me again, I added some additional data. It seems whenever there's a null end date, I'm not getting those values.

CREATE TABLE #testtable(

ID ...

thank you so much J Livingston SQL,

it worked perfectly!!!