Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: using between (Select Beg Date) and (Select End Date)

    /*
    Sorry this is from access but i think the logic will apply if im still just using sql.

    I have 30 queries i have to run at qtr end. The common issue is the dates so i would like to create a
    table with the begining date range and the ending date range and have the report
    pull the between date range from thier.

    My sql is a little rusty so i am thinking
    between (Select begDate From tblRange) and (Select endDate from tblRange)
    I tried this with only the 1st select clause and the 2nd being the hard coded date and it worked
    but when i put the second select in to replace the 2nd hard coded date
    it didnt work.
    */
    SELECT Deal.Group,
    Detail.Deal,
    Master.ExpenseType,
    Sum(Detail.InvoiceAmount) AS SumOfInvoiceAmount,
    Deal.Group
    FROM (Vendor
    INNER JOIN Master
    ON Vendor.Vendor = Master.VendorName)
    INNER JOIN ((Deal INNER JOIN Detail
    ON Deal.Deal = Detail.Deal)
    INNER JOIN Employee
    ON Detail.Person = Employee.EmployeeName)
    ON Master.CheckNum = Detail.CheckNum
    WHERE (((Master.VendorName)
    Not Like "Ray*"
    And (Master.VendorName)
    Not Like "Flight Options*")
    AND ((Detail.Deal) Not Like " U")
    AND ((Deal.DropDown)="M")
    AND ((Master.CheckDate) Between #1/1/2003# And #6/30/2003#))
    GROUP BY Deal.Group, Detail.Deal, Master.ExpenseType, Deal.Group
    HAVING (((Deal.Group) Like "AF*"));

  2. #2
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15
    would this work?

    create a new table called tbl_date_range with two fields: begin_date and end_date.

    add "tbl_date_range, " to the FROM clause (followed by the rest of your FROM clause)

    then rewrite the date portion of your WHERE clause like this:

    AND ((Master.CheckDate) Between (tbl_date_range.begin_date) And (tbl_date_range.begin_date.end_date))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •