Post reply

Basic Sql Help - Sorry jsut starting

  • June 7, 2011 at 4:02 am

    #251305

    Got simple query:

    SELECT Department, Tax_Year, Tax_Period, Total_Bill, Total_Hours, Purchase_Order_Number

    FROM Timesheets

    WHERE (Department = '971') AND (Tax_Year = '2010') AND (Tax_Period > '41')

    I want to change this so it does a total_bill for each different Purchase_Order_Number Number for all timesheets between Tax_Week 41 and Tax_Year 2009

    and Tax_Week 40 and Tax_Year 2010 for Department 971

    When i do it i am only getting data > tax week 41 and less than week 40. It mjisses out tax year 1,2,3 ext for the higher year.

    Sorry this is so basic but teaching myself.

    Thanks for any help

  • June 7, 2011 at 4:18 am

    #1334869

    First off, if your numbers are stored as characters, the first thing you need to do is CAST them as an integer data type, otherwise you may get unexpected results such as '5' being greater than '41'.

    One way of doing what you require is with a series of ANDs and ORs - not particularly elegant, though. Something like:

    WHERE (Year = 2009 AND Week > 41)

    OR (Year > 2009 AND Year < 2010)

    OR (Year = 2010 AND Week < 40)

    Please post table DDL and sample data in the form of INSERT statements if you need any further help.

    John

  • June 7, 2011 at 4:30 am

    #1334875

    Thanks John Mitchell-245523

    That works perfectly thanks. Now i need to sum the charge by purchase order number! Wish me luck!!!

  • June 7, 2011 at 5:10 am

    #1334897

    Hint: Read up on the GROUP BY clause of the SELECT statement

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply