Help in this query

hi all, need solve problem:

  select a.machine_id, b.src_file_name as source, a.created_dttm as date     case       when b.src_file_name like '#%' then 'na'     else        (select src_file_name       from fct_machine_evnt_dtl ed       where ed.machine_event_detail_id =          (select top 1 ed1.src_file_name         from vw_fct_machine_evnt_dtl ed1,         vw_fct_machine_event me         where a.machine_event_id = ed1.machine_event_id         --and m.machine_id = ed1.machine_id         and a.event_type = 13         and convert(varchar, a.created_dttm, 101)         between '11/21/2010' and '11/27/2010'         and cast(ed1.created_dttm as float) =            (select min(cast(ed1.created_dttm as float))   	    from vw_fct_machine_evnt_dtl ed1,          vw_fct_machine_event me  	    where a.machine_event_id = ed1.machine_event_id  	    and m.machine_id = ed1.machine_id  	    and a.event_type = 13  	    and convert(varchar, a.created_dttm, 101)          between '11/21/2010' and '11/27/2010')))       end as device  from vw_fct_machine_event a, vw_fct_machine_evnt_dtl b, application_user c,     machine ma, application_user d, application_group e,     application_user_group f,    dim_machine m  where e.application_group_id = f.application_group_id  	and e.group_name = 'group1'     and f.application_user_id=a.machine_id     and f.application_user_id = d.application_user_id  	and f.group_user_active = 1   	and a.application_user_id=c.application_user_id  	and d.application_user_id = a.machine_id     and ma.machine_id = a.machine_id  	and ma.machine_id = d.application_user_id   	and a.machine_event_id = b.machine_event_id     and m.machine_id = a.machine_id    and event_type in (11, 12, 16, 7, 13)    and convert(varchar,a.created_dttm,101) between '11/21/2010' and '11/27/2010'  

i'm ommiting fields in select list, query not large, joins in clause needed. problem have in case sentence not getting results want , don't know why.

basically set of results follows:

machine_id    source        date          device  -------------   --------------   ----------        --------------   comp1       doc1.doc      11/22/2010 10:05:30       na  comp1       sheet1.xls     11/25/2010 00:10:15      na  comp1       #sony       11/23/2010 00:20:10       #sony (i'd result instead of null get)  comp1       #kingston     11/23/2010 15:20:54       #sony (i'd result instead of null get)  comp2       #doc3.ppt     11/21/2010 10:55:59       na  comp2       #kingston     11/26/2010 08:12:30       #kingston (i'd result instead of null get)  comp2       #sony       11/26/2010 09:55:55       #kingston (i'd result instead of null get)     

i'm gonna explain want: in field device when source doesn't start with '#' result na, when source starts '#'

then need obtain value of source field (which starts '#') , must 1 oldest date, that's why comp1 device #sony, because 1 oldest date (which 11/23/2010 00:20:10).

did explain well?

i hope can me.

 

thanks in advance

what sql server version? seems me can solve problem using outer apply in sql 2005+, e.g.

 

select a.machine_id, b.src_file_name source, a.created_dttm date  case   when b.src_file_name '#%' 'na'   else f.name    end device vw_fct_machine_event a, vw_fct_machine_evnt_dtl b, application_user c inner join  machine ma on c... = ma.... inner join  application_user d    application_group e,   application_user_group f,  dim_machine m  outer apply ( (select top 1 src_file_name  fct_machine_evnt_dtl ed    vw_fct_machine_event me 	 a.machine_event_id = ed1.machine_event_id 	 , m.machine_id = ed1.machine_id 	 , a.event_type = 13 	 , a.created_dttm >='20101121' ,  a.created_dttm <'20101128') order ...) f  event_type in (11, 12, 16, 7, 13)   , and a.created_dttm >='20101121' ,  a.created_dttm <'20101128' 

the query give idea - not real query. change style joins reals joins, fix date condition use iso date formats , avoid conversion.

 

---

premature optimization root of evil in programming. (c) donald knuth

naomi nosonovsky, sr. programmer-analyst

my blog

SQL Server  >  Transact-SQL