Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. Ach005ki

    SELECT only orders which have corresponding items

    ...a temp table to hold the relevant corresponding item codes but can't seem to construct a query which will return only those orders which contain * only * items from the corresponding items list. Does this make sense? it may be a little clearer on examination of the attached excel file which...
  2. Ach005ki

    SELECT only orders which have corresponding items

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

    Datename() in SQL column header? Dynamic SQL?

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

    Datename() in SQL column header? Dynamic SQL?

    ...header to return the month name directly preceding the current month. =IF(MONTH($A$2)-(COLUMN(A2)-1)<1,TEXT(ABS((12+(MONTH($A$2)-(COLUMN(A2)-1))))*29,"mmmmmmmm"),TEXT(ABS((MONTH($A$2)-(COLUMN(A2)-1)))*29,"mmmmmmmm")) The next column header would be as follows...
  5. Ach005ki

    Datename() in SQL column header? Dynamic SQL?

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

    Problem with Insert in sub query

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

    sql select sum(left?) query

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

    Change string to title case

    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!
  9. Ach005ki

    Change string to title case

    ** 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!
  10. Ach005ki

    Change string to title case

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

    sql select sum(left?) query

    ...create table #mhtemp2 (A char(6) ) insert into #mhtemp2 select '4h' union all select '12h' union all select '09h'union all select '100h' select * from #mhtemp2 SELECT SUBSTRING(A,1,LEN(RTRIM(A))-1) , CAST(SUBSTRING(A,1,LEN(RTRIM(A))-1) as SmallInt) ...
  12. Ach005ki

    Anyone help me with a SQL select statemen?

    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!
  13. Ach005ki

    How to make sense of it all?

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

    Left Outer Join help needed

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

    Left Outer Join help needed

    ...INSERT INTO tempMHJAssg VALUES (1, NULL) INSERT INTO tempMHJAssg VALUES (2, 'Mark H') INSERT INTO tempMHJAssg VALUES (5, 'Lee C') SELECT * FROM tempMHJOpen SELECT * FROM tempMHJStat SELECT * FROM tempMHJAssg SELECT O.jobno, O.jobname, J.jobstat FROM tempMHJOpen AS O LEFT OUTER JOIN...
  16. Ach005ki

    Left Outer Join help needed

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

    LOJ and MIN brain-freeze

    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!
  18. Ach005ki

    LOJ and MIN brain-freeze

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

    Learning SQL

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

    Naming Objects In Your Database

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

Part and Inventory Search

Back
Top