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

    Flatten A One To Many And Concatenate The Many Attribute

    I posted this problem over at [a href="http://www.sqlservercentral.com/Forums/Topic1002255-338-1.aspx"]SQL Server Central[/a] but had problems with my data and framing the problem so I try it here. I appreciate any help. I need to bring up the "many" attribute to a single row and into a csv...
  2. WoodyGuthrie

    Can anyone think of another name for the old-style Join?

    Just so everyone knows, I learned on SS2005 (the right way). This came about because I'm rewriting a slow running query which uses multiple cursors and calls multiple table-valued function multiple times (the functions use cursors as well). The query is used by multiple reports all of which run...
  3. WoodyGuthrie

    Can anyone think of another name for the old-style Join?

    Found it.....The term is: "COMMA JOINS". Posted By SQLSister: So I've put the real task off long enough. I have no more excuses. Now I must face the big, bad, and the ugly multiple cursor'd query which calls multiple procedural coded functions which call yet more procedure coded, cursor...
  4. WoodyGuthrie

    Can anyone think of another name for the old-style Join?

    No. That's not it. Maybe it was SQL-Sister that used the term.?? Not sure. I'm almost certain it was on this forum. Maybe the quote went something like "Stop using those _______ joins!" Woody
  5. WoodyGuthrie

    Can anyone think of another name for the old-style Join?

    I've seen it on this (maybe) and other forums but cannot find it in search. It's a slang term used for the old syntax: SELECT c.strReceiptNo, d.strName FROM dbo.tblControlLog c, dbo.tblDepartments d WHERE c.guidDepartmentID = d.guidDepartmentID Thanks Woody
  6. WoodyGuthrie

    Sum from the results of a Select Query

    It seems to me the Having clause is of no use. Why the datetime conversion? Why not... Where tame_cust.Status_code <>'PB' and Tame_cust.appt_date = '2009-07-07' GROUP BY Tame_cust.Rep_Name, Tame_cust.appt_date Woody
  7. WoodyGuthrie

    Query with One-To-Many Help

    I want to do this but, using set-based code. Has anyone done this using a tally table? Thanks. http://www.tek-tips.com/faqs.cfm?fid=2146 Woody
  8. WoodyGuthrie

    Query with One-To-Many Help

    I have the following sample data and query. The tblControl (cl) has a one to many relationship with tblControlAssociates (cla) and tblControlAssignments (cls). I need to be able to bring up all associates to the same row based on the Receipt. Declare @Temp Table ( facility VarChar(25)...
  9. WoodyGuthrie

    CTE not effective / Subquery returned multiple values

    ...,'2008-06-10') Insert Into @Temp Values(NULL ,'64A11AB65DB9','Unapproved','2008-06-11') Insert Into @Temp Values('837146E30E7B','648EE650E22E','Amnesty' ,'2008-09-11') Insert Into @Temp Values('64A11AB65DB9','C3F5D3C29599','Amnesty' ,'2008-06-11') SELECT * FROM @Temp Woody
  10. WoodyGuthrie

    CTE not effective / Subquery returned multiple values

    Thanks George. But this table (apc) has no date column. It only has the guid back to the original infractions in the tblAssociateAttendanceCodes (ac) which has the date I need. It is then that row (guid, attendance code, date, etc.) that is out of my query date range. Woody
  11. WoodyGuthrie

    CTE not effective / Subquery returned multiple values

    This seems like a good example of when to use a tally/numbers table but I'm just not sure how to execute it. Woody
  12. WoodyGuthrie

    CTE not effective / Subquery returned multiple values

    This works fine for attendance dates (Infractions) that are within my specified date range, but retains the Amnesty attendance date for Infraction Dates that fall outside that specified range. How can I incorporate all attendance dates so that no matter when the infraction occurred the amnesty...
  13. WoodyGuthrie

    CTE not effective / Subquery returned multiple values

    Someone tatto COALESCE on my forhead! Thanks a bunch George. Woody
  14. WoodyGuthrie

    CTE not effective / Subquery returned multiple values

    Hello Everyone! I'll try a slightly different approach here at Tek-Tips. I'm dealing with employee attendance data. An employee may receive amnesty for infractions that have occurred in the past (like unapproved absences etc.) I have an amnesty table that tracks the infraction(s) for which the...
  15. WoodyGuthrie

    help with SQL JOIN

    Not sure why you would want to choose one quantity on an item over another quantity on the same item, so I would try something like this: SELECT PM.CATEGORY , PM.RESOURCE , SUM(PM.TRANSQTY) , PO.vendditemno FROM PMTRAN AS PM LEFT JOIN PO ON PM.refdoc = po.ponumber WHERE...
  16. WoodyGuthrie

    Reporting Services NULL Check issues

    An "If" construct is not going to work within your Select statement. You may be able to use a case expression with subqueries or you could create a stored procedure and use your if/then logic to build your Where clause dynamically. But you may be better served looking into whether ISNULL or...
  17. WoodyGuthrie

    Exclude Multiple Date Ranges From Result

    I have a query that results in the following ID, StartDate and EndDate in a Table Variable(A). How do I now exclude data (in the next step) that was added to my production table(B) during any of these date ranges. The variable numbers of date ranges is based on the ID. Some may have no date...
  18. WoodyGuthrie

    How to determine each specific date range from one row to the next.

    Thanks for the reply. Sorry, I should've included the possibility that there could be two or more status changes between the RC status. Like below. RC 5/19/2008 Regular 6/12/2008 RC 10/17/2008 Regular 11/01/2008 Leave 12/09/2008 RC 01/05/2009 Woody
  19. WoodyGuthrie

    How to determine each specific date range from one row to the next.

    I need to determine the date range(s) for each given employee from the entry date of the status RC to the entry date of the next status change AND then exclude all attributes (paydates, pay, etc.) for those dates from the range of pay dates returned for that employee based on the input...
  20. WoodyGuthrie

    DateTime Conversion with parameter

    I have input parameters @SDate and @EDate for my date range. The date being compared is a date/time and so I do a conversion to (nvarchar(20),101) to get mm/dd/yyyy. The problem is sometimes the user enters the date parameters as mm/dd/yy. In this case it works fine for @SDate but the range is...

Part and Inventory Search

Back
Top