Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: BETWEEN predicate with passed parameters

    I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause.

    I tried a standard BETWEEN predicate in my WHERE clause like:
    IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0

    But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then.....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal.

    Thanks in advance,
    Michelle

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SELECT * 
      FROM Orders
    WHERE OrderDate   >= '1996-07-08 00:00:00.000'
      AND ShippedDate <= '1996-07-15 23:59:59.000'
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Yeah, I can't just use the standard WHERE clause with >= and <=. I need to return a separate value for each month of the calendar year (these are setup in separate fields on my report). So, if the user enters parameter values into begper = '200401' and endper = '200403', then the report needs to print all 3 fields for months 01, 02, and 03, because they all fall within the range of 200401 and 200403. I would have to code all possible combos of a begper >= 'xx' and endper <= 'yy', ya know? While that may be entertaining, I wonder if there's a more efficient route? <g>

    More details:
    Table:
    Custid, Janbudget, Febbudget, Marbudget, Aprbudget, etc etc.
    User enters runtime parameters for a monthly period range, such as between '200401' (January) and '200403' (March). In this case, my report should only print the values in fields Janbudget, Febbudget and Marbudget. All other fields will either not print or print 0.

    Any other ideas?

    Thanks!
    Michelle

    Originally posted by Brett Kaiser
    Code:
    USE Northwind
    GO
    
    SELECT * 
      FROM Orders
    WHERE OrderDate   >= '1996-07-08 00:00:00.000'
      AND ShippedDate <= '1996-07-15 23:59:59.000'
    GO

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to use Group BY and use scalar functions....hold on...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Like this?

    Code:
    SELECT OrderId
    	, DATEPART(yy,OrderDate) AS OrderDate_yy
    	, DATEPART(mm,OrderDate) AS OrderDate_mm
    	, DATEPART(yy,ShippedDate) AS ShippedDate_yy
    	, DATEPART(mm,ShippedDate) AS ShippedDate_mm
    	, COUNT(*) AS Orders 
      FROM Orders
    WHERE OrderDate   >= '1996-07-08 00:00:00.000'
      AND ShippedDate <= '1996-07-15 23:59:59.000'
    GROUP BY OrderId	
    	, DATEPART(yy,OrderDate)
    	, DATEPART(mm,OrderDate)
    	, DATEPART(yy,ShippedDate)
    	, DATEPART(mm,ShippedDate)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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