none
select distinct date from datetime column, ordery by and other issues

    Question

  • I'm trying to get a list with a count of transactions by day, including the number of parts sold and the total cost of those parts. Lots of problems here. I used a date function to get the date (excluding time). This causes an error when I try to order by the date. Also, it doesn't look like the transaction count is accurate either. Any advice would be greatly appreciated.

    SELECT DISTINCT (DATEADD(d, 0, DATEDIFF(d, 0, th.RELEASE_DATE))) AS TRAN_DATE,
      COUNT (th.TRANSACTION_NO)                                      AS TRANS_NO,
      SUM (td.RELEASE_QNT)                                           AS RELEASE_QNT,
      SUM (td.TOTAL_COST)                                            AS TOT_COST
    FROM IC_TRANSACTION_HDR                                          AS th
    INNER JOIN IC_TRANSACTION_DTL                                    AS td
    ON th.TRANSACTION_NO=td.TRANSACTION_NO
    GROUP BY th.RELEASE_DATE,
      th.TRANSACTION_NO,
      td.RELEASE_QUANTITY,
      td.TOTAL_COST
    ORDER BY th.RELEASE_DATE DESC,
      th.TRANSACTION_NO,
      td.RELEASE_QUANTITY,
      td.TOTAL_COST

    Monday, January 02, 2012 4:37 AM

All replies

  • Could you please provide your table creation script along with some sample rows?
    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Monday, January 02, 2012 3:08 PM
  • This query would probably have been answered faster if it were in the Transact-SQL forum.

    Nubie, how about trying

    SELECT DISTINCT (DATEADD(d, 0, DATEDIFF(d, 0, th.RELEASE_DATE))) AS TRAN_DATE,
    COUNT (th.TRANSACTION_NO) AS TRANS_NO,
    SUM (td.RELEASE_QNT) AS RELEASE_QNT,
    SUM (td.TOTAL_COST) AS TOT_COST
    FROM IC_TRANSACTION_HDR AS th
    INNER JOIN IC_TRANSACTION_DTL AS td
    ON th.TRANSACTION_NO=td.TRANSACTION_NO
    GROUP BY  (DATEADD(d, 0, DATEDIFF(d, 0, th.RELEASE_DATE)))
    ORDER BY  (DATEADD(d, 0, DATEDIFF(d, 0, th.RELEASE_DATE)))

    Richard Lees

    http://RichardLees.com.au

    http://RichardLees.blogspot.com

     


    Richard
    • Edited by RichardLees Tuesday, January 03, 2012 3:53 AM syntax correction
    • Proposed as answer by Jerry NeeModerator Tuesday, January 03, 2012 9:27 AM
    Tuesday, January 03, 2012 3:51 AM
  • This is perfect Richard, thanks for the help. Next time I'll use the correct forum.
    Tuesday, January 03, 2012 3:07 PM