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



Comments

Post a Comment

Popular posts from this blog

Conditional formatting a graph vertical axis in SSRS 2012 charts

Image
hi, i have situation want color label on vertical axis of graph depending on condition. when values positive color black , when negative color red. i tried following condition but not working.   =iif(fields!myfield.value < 0 , "red", "black") i know there few threads mentioning feature not supported in ssrs 2008 wanted know if adddressed in ssrs 2012 or later? thanks , regards, oliver d'mello hi oliver, according description, want use expression specify different color axis labels based on values in chart. as tested in our environment, when apply condition in expression give dynamic color axis labels, color setting applies labels based on condition. issue happens in both sql server 2012 , 2014. please refer screenshots below: in scenario, recommend submit issue microsoft connect @ link https://connect.microsoft.com/sqlserver/feedback . connect site serve connecting point between , microsoft, , large community , microsoft intera
Read more

Register with Power BI failed

hi all, i've running local report server , want configure power bi integration. sign in azure active directory , registering power bi clientapp validated, fails following error: microsoft.reportingservices.wmiprovider.wmiproviderexception: unknown error has occurred in wmi provider. error code 80131500  ---> system.exception: exception of type 'system.exception' thrown.    --- end of inner exception stack trace ---    at microsoft.reportingservices.wmiprovider.rswmiadmin.throwonerror(managementbaseobject mo)    at microsoft.reportingservices.wmiprovider.rswmiadmin.savepowerbiinformation(string clientid, string clientsecret, string appobjectid, string tenantname, string tenantid, string resourceurl, string authurl, string tokenurl, string redirecturls)    at reportservicesconfigui.wmiprovider.rsreportserveradmin.savepowerbiinformation(powerbiappconfiguration appconfig) i tried sql server agent solution  ( https://social.msdn.microsoft.com/forums/en-us/58
Read more

SQL server replication error Cannot find the dbo or user defined function........

hi there, i ran problem while deploying sql 2008 transactional replication between 2 servers lets assume server , b. actually created  both distributor , publisher on server , after created subscriber pointed server b, launch replication monitor , found big red cross. when click on tab "distributor subscriber history" error says "cannot find either column "dbo" or user defined-function or aggregate "dbo.ufnleadingzeros", or name ambiguous". can body regarding issue. regards, ishuv hi, you need compare data , others reseved, index size, unused should not compared. differs. here server - data - 87896 kb server b - data - 78152 kb server data size includes publication tables, can't compare directly. need check article article know exact data size. SQL Server  > 
Read more