Search results for query: *

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...

hi ndnalibi I may be wrong here but is it not one of your innner joins which are causing your problem... because you've got MaxDateTable.LastDate in your select list which is being polled via an {LOJ} and then an {IJ}? O {LOJ} J J {IJ} MaxTable J {IJ} MaxDateTable Mark, somewhere near...

Hi Boris, I'm back in work today so I'll try your suugestion ASAP and post up - many thanks for your assistance, I appreciate the code is untested but my whole energy yesterday was devoted to being present at work without lapsing into sickness! Mark, somewhere near Blackburn Lancs!

Hi guys I'm really struggling today - I've been laid up in bed all weekend with stomach cramps (I'll not elaborate) and I'm still not feeling 100%... ... I can't get my head around how to pull the earliest outstanding Purchase Order from a PO detail table so that I can show the quantity...

Guys, this seems such a popular thread that I wonder if you would consider creating the thread as a proper "Learning SQL" FAQ in the SQL Server Resources section. I think the sage advice from George (gmmastros), Rudy (r397), Denis (SQLDenis) and the useful links from others would be a very...

Hi guys - I don't post much here but I try to read up on many threads (even ones which don't directly affect me and those topics on which I haven't yet stumbled in the course of my work). I very much appreciate all the input in this thread, but especially from Esquared who has started the ball...