Recent content by Ach005ki

Hi George, Thank you for your swift assistance. I appreciate the effort taken - unfortunately my explanation of the problem and simplified example may have been a little misleading. The actual number of corresponding items is 144 items (the number of other possible items is in their 1000s)...

Hi all I'm really struggling with this query: To only select sales orders where the ordered items are in a particular list of items. In the simplified example below, I would want to select only sales orders containing apples or oranges but not those orders containing any other fruit...

Hi RyanEK & RiverGuy I'll review your comments but part of my problem (esp with regard to the dynamic Sql) is that I'm almost certain I can't build or execute stored procs in MSQuery, and so I don't know where that leaves me with regard to variable declarations. Ryan, the dates are not...

Hi bb (and any / all other thread viewers) Why is a good question; the users of this report are relying on the report to provide the data pre-formatted. Ordinarily and usually I would agree but unfortunately I have to work within the parameters I'm set! The report is generated via an Excel...

Hi all I'm sorry if this question has been posted previously - I couldn't find evidence of such in six pages of searched posts; the nearest I came to was Results from Query as Headers Background I have monthly sales information in a table which is stored as SalesQty1 through to SalesQty12...

People, please don't f[b]lame me if I'm wrong but isn't it this part which is missing the Inner Join info? ... FROM dm_HRB_Legacy_Box_Conversion_hits INNER JOIN dm_HRB_Legacy_Box_Conversion ON dm_HRB_Legacy_Box_Conversion_hits.HRB_recid = dm_HRB_Legacy_Box_Conversion.recid...

I don't know why I opted for a SUBSTRING rather than a LEFT, but the LEFT would be something like: SELECT SUM(CAST(LEFT(A,LEN(RTRIM(A))-1) as SmallInt)) FROM #mhtemp2 BTW, I like yours for simplicity, djj - I hadn't realised that a CAST would trim trailing blank / space characters before...

lol, I presumed (rather than asked for clarification) that your "contact details" was a field containing multiple parts (such as title, forename, surname etc). Silly mistake on my part, but an example would have helped. Mark, somewhere near Blackburn Lancs!

** Update ** I should have mentioned this in my post, but the reason I linked to the SQLTeam thread is that there are very relevant comments in relation to exception data where proper case capitalisation may have undesired effects. Mark, somewhere near Blackburn Lancs!

Hi Katehol I seemed to recall a script which created a function in order to do this - perhaps you can adapt it: http://vyaskn.tripod.com/code/propercase.txt (found at SQLTeam: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59289) PS I hope it is okay to post up links Mark, somewhere...

Have you tried something along the lines of this to start with? The last select is the summing one, the one before this simply checks that the CAST operation has worked. create table #mhtemp2 (A char(6) ) insert into #mhtemp2 select '4h' union all select '12h' union all select '09h'union all...

George, I agree with djj as derived tables are an especially useful tool. George, I think you deserve a star for your efforts! Mark, somewhere near Blackburn Lancs!

Xomp, I was brought into my company purely to help tighten stock control and through my previous limited experience of SQL & Crystal Reports from several years ago (and a reasonably good grasp of Excel) I found I was striving to strengthen and broaden Excel reports which were serviced by data...

I've simplified your table joins as per below and to me these fields are the ones which would cause the rows not to be returned where the job has no transactions: MaxDateTable.LastDate, JL.UpdateDate, LaborCostCntr.LCCDescription, JL.LLRecNum, JL.StartTime From OpenJob AS O Left Outer Join...

I think this example explains it better: CREATE TABLE tempMHJOpen (jobno int PRIMARY KEY, jobname varchar(20) NULL) CREATE TABLE tempMHJStat (jobno int PRIMARY KEY, jobstat varchar(20) NULL) CREATE TABLE tempMHJAssg (jobno int PRIMARY KEY, jobassg varchar(20) NULL) INSERT INTO tempMHJOpen...