sql GROUP BY DATE

[Raul2005]

I need to find at what date a product was changed from
status pending to any other status.



id pname date status
1 New Issue 2005-10-23 10:25:17.483 Pending
1 New Issue 2005-11-23 10:43:02.963 Accepted
1 New Issue 2005-11-23 10:53:03.237 Denied
1 New Issue 2005-11-23 10:54:24.113 Pending
1 New Issue 2005-11-23 10:55:04.910 Accepted

2 Secondary 2005-11-23 10:25:17.340 Pending
2 Secondary 2005-11-23 10:43:03.043 Accepted
2 Secondary 2005-11-23 10:53:03.300 Denied
2 Secondary 2005-11-23 10:54:24.177 Pending
2 Secondary 2005-11-23 10:55:04.973 Accepted

 

[NoCoolHandle]

Some thing like

Select id , max(convert(smalldatetime ,(convert(varchar(10),date,1)))
from Table 
Group by convert(smalldatetime ,(convert(varchar(10),date,1))

[code]

 

[NoCoolHandle]

I need to read more carefully ...
(and on second read I don't think I fully understand what you want)
What would you like to see as a result set?

Can you give an example...?

 

[vongrunt]

This will do as instructed:

declare @blah table (id int, pname varchar(20), [date] datetime, status varchar(8))
insert into @blah values (1, 'New Issue', '2005-10-23 10:25:17.483', 'Pending'  )
insert into @blah values (1, 'New Issue', '2005-11-23 10:43:02.963', 'Accepted' )
insert into @blah values (1, 'New Issue', '2005-11-23 10:53:03.237', 'Denied'   )
insert into @blah values (1, 'New Issue', '2005-11-23 10:54:24.113', 'Pending'  )
insert into @blah values (1, 'New Issue', '2005-11-23 10:55:04.910', 'Accepted' )
insert into @blah values (2, 'Secondary', '2005-11-23 10:25:17.340', 'Pending'  )
insert into @blah values (2, 'Secondary', '2005-11-23 10:43:03.043', 'Accepted' )
insert into @blah values (2, 'Secondary', '2005-11-23 10:53:03.300', 'Denied'   )
insert into @blah values (2, 'Secondary', '2005-11-23 10:54:24.177', 'Pending'  )
insert into @blah values (2, 'Secondary', '2005-11-23 10:55:04.973', 'Accepted' )

select A.id, A.pname, A.[date], min(B.[date]) as dateChanged
from
(	select id, pname, [date]
	from @blah
	where status = 'Pending'
) A
inner join 
(	select id, pname, [date]
	from @blah
	where status <> 'Pending'
) B on A.id=B.id and A.[date] < B.[date]
group by A.id, A.pname, A.[date]
-- order by A.id, A.pname, A.[date]

If you want NULLs for records that haven't changed yet from 'Pending' status, use left join instead of inner and turn off ANSI warnings temporarily (dirty, but gets the job done).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)