Skip to main content
O
Olivia_Crazy_Horse
Known Participant
July 9, 2009
Question

Dynamic date selections

  • July 9, 2009
  • 2 replies
  • 505 views

I use the following query to get the average days per order, for a month. It sums the datediff of all the dates and divides by the total number of open orders for the month,in this case Jun 09, which I hardcoded.

SELECT Avg_Aging, Site FROM
  (
   SELECT DISTINCT CAST(CAST(SUM(DateDiff(day,Date_Arrived, GETDATE()) + 1 ) AS DECIMAL)
   /CAST(COUNT(orderNumber)AS DECIMAL) AS DECIMAL (10,1)) AS Avg_Aging, Site
   FROM ordersTable INNER JOIN siteMasterr
   ON ordersTable.Site_ID = siteMasterr.Site_ID
   WHERE status NOT IN ('Voided','Closed')
   and date_arrived >= '06/01/2009' and date_arrived < '07/01/2009'
   GROUP BY Site
  ) AS I

where site = 'Florida'

What I need to know is, instead of hardcoding the date_arrived, for each month, how can I do it dynamically, so that each time I run this, it will get all the data for Jan 09, then Feb 09, etc., up to Jun 09. July 09 is not complete yet, so it should not do anything else after Jun.

    This topic has been closed for replies.

    2 replies

    J
    JoeBackward
    Participating Frequently
    July 10, 2009

    You can do this month-by-month rollup with a single query, but it's going to take a bunch of date arithmetic that is dependent on the make and model of table server you're running.

    In pidgin Oracle SQL it would be

    SELECT SUM(Sale) AS Sales

                 State,

                 TRUNC(Saledate, 'MM') AS month  -- the month in which the sale took place

    FROM Sales

      WHERE Saledate >= ADD_MONTHS (TRUNC(SYSDATE, 'MM'), -6)  -- six months before the first of this month

          AND  Saledate < TRUNC(SYSDATE,'MM')  -- the first of this month

    GROUP BY State, TRUNC(Saledate, 'MM')

    There's a wonderful book by Prof. Richard Snodgrass of Arizona State University on time-based databases here.  He has lots of examples in the languages of various makes and models of server.

    http://www.cs.arizona.edu/people/rts/tdbbook.pdf

    D
    Dan_Bracuk
    Inspiring
    July 9, 2009

    Date1 = CreateDate(something);

    Date2 = CreateDate(something);

    Then use these variables in your query, with cfquery param of course.

    Also, about your query:

    1.  Do your casting after you do your math, not before.

    2.  You don't need a subquery.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices