SQL Query works in Microsoft Query, not in Microsoft Excel -

-

i have complex sql query seems having trouble translating correctly microsoft excel's data connection.

this code works when pasted in microsoft query, return excel, excel gives , won't fill sheet data. think stopped working when added left outer join pieces ensure had oldest skittypes in 2 groups of skittpes, thing complicated i'm not entirely sure.

left outer join table2 b2 (nolock) on (a.actingnumber = b2.actingnumber , b.actingnumber = b2.actingnumber , b2.skittype in ('180','184','185') ) left outer join table2 b3 (nolock) on (a.actingnumber = b3.actingnumber , b.actingnumber = b3.actingnumber , b3.skittype in ( '980','984','985') )

and full query: declare @clipid varchar(15) set @clipid = 'bus'

declare @mnth int declare @bgdate varchar(8) declare @enddate varchar(8) declare @prvmnth int  set @mnth = (select max(mnth) table1) set @mnth = (left(replace(convert(varchar(10),dateadd(mm, -5, convert(varchar(10),@mnth,120) + '01'),120),'-',''),6)) set @bgdate =  convert(varchar(8), @mnth, 120) + '01' set @enddate = replace(convert(varchar(10),dateadd(dd, -1, dateadd(mm, 6, convert(smalldatetime, @bgdate))),120),'-','') set @prvmnth = (left(replace(convert(varchar(10),dateadd(mm, -1, convert(varchar(10),@mnth,120) + '01'),120),'-',''),6))  declare @mnth2 int declare @delq_mnth char(8) declare @l_monthenddate datetime  set @mnth2 = left(replace(convert(varchar(10),dateadd(mm, 4, convert(varchar(10), @mnth,120) + '01'),120),'-',''),6) set @delq_mnth = (convert(varchar(8),dateadd(d,-1,dateadd(m,1,convert(smalldatetime,convert(varchar(6),@mnth2) + '01'))),112)) set @l_monthenddate = dateadd(d,-1,dateadd(m,1,convert(smalldatetime,convert(varchar(6),@mnth2) + '01')))   select a.actingnumber,  max(a.clipid) clipid,  max(a.outfitid) outfitid,  max(b.skittraindate) skittraindate,  max(b.sctrn$) skittrainhours, max(a.crowdcontrol) last_mnth_crowdcontrol table1 (nolock) join table2 b (nolock) on a.actingnumber = b.actingnumber left outer join table2 b2 (nolock)  on (a.actingnumber = b2.actingnumber , b.actingnumber = b2.actingnumber , b2.skittype in ('180','184','185') ) left outer join table2 b3 (nolock)  on (a.actingnumber = b3.actingnumber , b.actingnumber = b3.actingnumber , b3.skittype in ( '980','984','985') )  join ( select *  ( select distinct a.actingnumber, case when a.skitperfdate <> 0 b.prnbal else a.prnbal end prnbal , '' clipid ,'' outfitid , case when c.useprebump = 1           case when a.crowdage < 1 0         when a.crowdage between 1 , convert(varchar(4),datediff(dd,dateadd(month,-1,@delq_mnth),@delq_mnth)) 15         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-1,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-2,@delq_mnth),@delq_mnth)) 30         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-2,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-3,@delq_mnth),@delq_mnth)) 60         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-3,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-4,@delq_mnth),@delq_mnth)) 90         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-4,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-5,@delq_mnth),@delq_mnth)) 120         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-5,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-6,@delq_mnth),@delq_mnth)) 150         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-6,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-7,@delq_mnth),@delq_mnth)) 180         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-7,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-8,@delq_mnth),@delq_mnth)) 210         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-8,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-9,@delq_mnth),@delq_mnth)) 240         when a.crowdage between convert(varchar(4),datediff(dd,dateadd(month,-9,@delq_mnth),@delq_mnth)+1) , convert(varchar(4),datediff(dd,dateadd(month,-10,@delq_mnth),@delq_mnth)) 270         when a.crowdage > convert(varchar(4),datediff(dd,dateadd(month,-10,@delq_mnth),@delq_mnth)) 300         else 0         end  else          isnull(a.postbucket,0)  end crowdcontrol, a.skitperfdate, a.mnth, a.closcd table3 (nolock) left outer join table4 b (nolock) on b.actingnumber = a.actingnumber left join table5 c (nolock) on c.clipid = clipid , c.outfitid = outfitid --left outer join table3 a2 --on (a.actingnumber = a2.actingnumber , a.safldt > a2.safldt ) --left outer join table3 a3 --on (a.actingnumber = a3.actingnumber , a.crowdage > a3.crowdage )  a.safldt = 0 , a.crowdage = 0  --a3.actingnumber null  --and a2.actingnumber null --mnth >= @mnth  , a.calcstatus = 'completed'  ) ) h on a.actingnumber = h.actingnumber a.clipid = @clipid -- transaction codes: , b.skittype in ('180','184','185')--, '980','984','985') , b.skittraindate >= @bgdate  --and b2.skittype not in ( '980','984','985') , b2.skittype not null  , (b2.skittraindate  > b3.skittraindate or b3.skittraindate null )  group a.actingnumber --order last_mnth_crowdcontrol desc

turns out using existing query , modifying not work. had add query first, make modifications, , insert query spreadsheet (use existing data connection).


Comments

Post a Comment

Popular posts from this blog

c# - How Configure Devart dotConnect for SQLite Code First? -

-
can configure following sample code ? when run following code, error : no entity framework provider found ado.net provider invariant name 'devart.data.sqlite' setting in machine.config <system.data> <dbproviderfactories> <add name="dotconnect sqlite" invariant="devart.data.sqlite" description="devart dotconnect sqlite" type="devart.data.sqlite.sqliteproviderfactory, devart.data.sqlite, version=4.6.287.0, culture=neutral, publickeytoken=09af7300eec23701" /> </dbproviderfactories> </system.data> after add below block code in app.config <?xml version="1.0"?> <configuration>   <configsections> <!-- more information on entity framework configuration, visit http://go.microsoft.com/fwlink/?linkid=237468 --> <section name="entityframework" type="system.data.entity.internal.configfile.entityframeworksection, entit...
Read more

java - Copying object fields -

-
what best way of copying classes when need have 2 independent variables? have simple class: public class myclass { boolean = false; string b= "not empty"; } do need make method : assign(myclass data ) { a= data.a; b= data.b; } is there automatic method copy (duplicate) objects in java? do need make method : pretty close. instead of making method, should make constructor. such constructors called copy constructor , , create them this: myclass(myclass data) { = data.a; b = data.b; } and create copy of instance, use constructor this: myclass obj1 = new myclass(); myclass obj2 = new myclass(obj1); copy constructor can tedious : using copy constructor create deep-copy can tedious, when class has mutable fields. in case, assignment create copy of reference, , not object itself. have create copy of fields (if want deep copy). can go recursive. a better way create deep copy serialize , deserialize object. why not use clone()? ...
Read more

c++ - Clear the memory after returning a vector in a function -

-
i have function processes , stores lot of data in , returns results vector of class. amount of data stored in function tremendous , want clear storage memory of function after finished job. necessary (does function automatically clear memory) or should clear memory function? update: vector<customers> process(char* const *filename, vector<int> id) { vector<customers> list_of_customers; (perform actions) return list_of_customers; } variables defined locally within function automatically released @ end of function scope. destructors objects called, should free memory allocated objects (if objects coded correctly). example of such object std::vector . anything you've allocated new must have corresponding delete release storage. try avoid doing own allocation , use raii instead, i.e. containers or smart pointers.
Read more