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

Answer 1

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.

Answer 2

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

Answer 3

This is perfect Richard, thanks for the help. Next time I'll use the correct forum.