CODEWITHSUNDEEP
sqlsql-server-2005
steve.g
steve.g

Reputation: 115

counting date and time for historical reporting

I am currently working on a query that will be used in junction with share-point to run reports. I have a query that I know will work with Oracle, but the company I am working for is running SQL Server 2005.

What the report will do is give the person the ability to select any date and time, and give the count for that specific operation. The problem is that there are large gaps in the time stamps (because it takes a little while for the product to get to the next operation). The date type is varchar, so i used substrings to parse out the year, month, day, and time. I have sample data available.

The people looking at the reports want the ability to say at this time and day how many units went through this operation.

I know this is is confusing, let me know if you need any clarification.

Here is the oracle syntax 

SELECT T3.PAYMENT_DATE, T3."Hr", T3."Min", 
       (SELECT COUNT(*) 
        FROM INVOICE_ARCHIVE T4 
        WHERE TO_NUMBER(TO_CHAR(T4.PAYMENT_DATE, 'MM')) <= T3."Hr" 
        AND TO_NUMBER(TO_CHAR(T4.PAYMENT_DATE, 'DD')) <= T3."Min") AS "NUM"
FROM(SELECT T1.PAYMENT_DATE, T2."Hr", T2."Min"
     FROM (SELECT (FLOOR((LEVEL + 359)/60)) AS "Hr", 
                  MOD((LEVEL + 359), 60) AS "Min"
           FROM dual CONNECT BY LEVEL <= 961) T2, INVOICE_ARCHIVE T1
     ORDER BY T1.PAYMENT_DATE, T2."Hr", T2."Min") T3

Upvotes: 0

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The answer to your question is the datepart() function in SQL Server. This will allow you to extract minutes and hours from dates.

The harder part is the "connect by level" portion. How is this being used? You might need to use recursive CTEs to handle this.

With the little hint from spencer, the following may suffice for your query:

SELECT T3.PAYMENT_DATE, T3."Hr", T3."Min",
       (SELECT COUNT(*)
        FROM INVOICE_ARCHIVE T4
        WHERE datepart(month, T4.PAYMENT_DATE) <= T3."Hr" AND
              datepart(day, T4.PAYMENT_DATE, 'DD') <= T3."Min"
       ) AS "NUM"
FROM (SELECT T1.PAYMENT_DATE, T2."Hr", T2."Min"
      FROM (SELECT top 961 (FLOOR((LEVEL + 359)/60)) AS "Hr",
                    MOD((LEVEL + 359), 60) AS "Min"
            FROM (select top 961 row_number() over (order by (select NULL)) as level
                  from invoice_archive
                 ) t
           ) T2 cross join
           INVOICE_ARCHIVE T1
     ) T3
 ORDER BY T3.PAYMENT_DATE, T3."Hr", T3."Min"

I made the following changes:

  1. Changed the date arithmetic to use datepart() instead of to_char() .
  2. Replaced the method for getting a list of numbers, by using row_number() instead of connect by level
  3. Made the cross join explicit
  4. Moved the order by to the outer query, since neither SQL Server nor Oracle guarantee the results of an order by in a subquery (and SQL Server does not allow it unless you have a "TOP" query)

Upvotes: 2

Related Questions