Hi Everyone,
I have table1 that has multiple transactions per day. I need to group by the day then by the month and total the Amount field and the items field per day/month/year. so for example:
Existing Data:
date_time Extension points
2011-01-01 10:34:12.997 300.00 2
2011-01-01 20:20:20.657 157.89 3
2011-01-02 10:34:12.997 350.00 9
2011-01-02 20:20:20.657 198.89 3
2011-02-02 10:34:12.997 350.00 9
2011-02-02 20:20:20.657 145.89 3
2011-03-02 10:34:12.997 362.00 4
2011-03-02 20:20:20.657 200.00 3
2011-03-02 21:20:20.456 200.00 6
Becomes:
January
DateDay AmountValue DaValidation
2011-01-01 457.89 5
2011-01-02 548.89 12
*MONTHLY TOTALS* 1006.78 17
2011-02-02 495.89 12
*MONTHLY TOTALS* 495.89 12
2011-03-02 10:34:12.997 562.00 7
2011-03-03 21:20:20.456 200.00 6
*MONTHLY TOTALS* 762.00 13
*YEARLY TOTALS* 2264.67 42
This is what I currently have:
SELECT
DateDay,
SUM(amountvalue) as AmountValue,
COUNT(validation) as DaValidation
FROM
(SELECT
CONVERT(varchar,MYSERVER.dbo.transact.date_time,111) as DateDay, MYSERVER.dbo.transact.extension as amountvalue,
points1 as validation
FROM
MYSERVER.dbo.transact
INNER JOIN
MYSERVER.dbo.tr_info
ON
MYSERVER.dbo.transact.trans_no = MYSERVER.dbo.tr_info.trans_no
INNER JOIN
MYSERVER.dbo.gst_pass
ON
MYSERVER.dbo.tr_info.guest_no = MYSERVER.dbo.gst_pass.guest_no
WHERE
MYSERVER.dbo.transact.date_time
BETWEEN @transstartdate AND (@transenddate + ' 23:59:59.997')
AND MYSERVER.dbo.gst_pass.date_time
BETWEEN @passstartdate AND (@passenddate + ' 23:59:59.997')
AND MYSERVER.dbo.transact.salespoint LIKE @FlameOn
AND MYSERVER.dbo.transact.department = 'Something'
AND MYSERVER.dbo.transact.special IN ('250', '251')
AND MYSERVER.dbo.gst_pass.department = 'NA'
AND MYSERVER.dbo.gst_pass.category IN ('NA W ADV', 'ADVSR AC"')
AND MYSERVER.dbo.gst_pass.voided_by = '
AND MYSERVER.dbo.tr_info.info_type = 10
) a
GROUP BY DateDay
ORDER BY DateDay
--This does combine the records from the same day and adds the values, but I can't figure out how to subsequently group the days by month, and the months by year.
Any help would be greatly appreciated.
I have table1 that has multiple transactions per day. I need to group by the day then by the month and total the Amount field and the items field per day/month/year. so for example:
Existing Data:
date_time Extension points
2011-01-01 10:34:12.997 300.00 2
2011-01-01 20:20:20.657 157.89 3
2011-01-02 10:34:12.997 350.00 9
2011-01-02 20:20:20.657 198.89 3
2011-02-02 10:34:12.997 350.00 9
2011-02-02 20:20:20.657 145.89 3
2011-03-02 10:34:12.997 362.00 4
2011-03-02 20:20:20.657 200.00 3
2011-03-02 21:20:20.456 200.00 6
Becomes:
January
DateDay AmountValue DaValidation
2011-01-01 457.89 5
2011-01-02 548.89 12
*MONTHLY TOTALS* 1006.78 17
2011-02-02 495.89 12
*MONTHLY TOTALS* 495.89 12
2011-03-02 10:34:12.997 562.00 7
2011-03-03 21:20:20.456 200.00 6
*MONTHLY TOTALS* 762.00 13
*YEARLY TOTALS* 2264.67 42
This is what I currently have:
SELECT
DateDay,
SUM(amountvalue) as AmountValue,
COUNT(validation) as DaValidation
FROM
(SELECT
CONVERT(varchar,MYSERVER.dbo.transact.date_time,111) as DateDay, MYSERVER.dbo.transact.extension as amountvalue,
points1 as validation
FROM
MYSERVER.dbo.transact
INNER JOIN
MYSERVER.dbo.tr_info
ON
MYSERVER.dbo.transact.trans_no = MYSERVER.dbo.tr_info.trans_no
INNER JOIN
MYSERVER.dbo.gst_pass
ON
MYSERVER.dbo.tr_info.guest_no = MYSERVER.dbo.gst_pass.guest_no
WHERE
MYSERVER.dbo.transact.date_time
BETWEEN @transstartdate AND (@transenddate + ' 23:59:59.997')
AND MYSERVER.dbo.gst_pass.date_time
BETWEEN @passstartdate AND (@passenddate + ' 23:59:59.997')
AND MYSERVER.dbo.transact.salespoint LIKE @FlameOn
AND MYSERVER.dbo.transact.department = 'Something'
AND MYSERVER.dbo.transact.special IN ('250', '251')
AND MYSERVER.dbo.gst_pass.department = 'NA'
AND MYSERVER.dbo.gst_pass.category IN ('NA W ADV', 'ADVSR AC"')
AND MYSERVER.dbo.gst_pass.voided_by = '
AND MYSERVER.dbo.tr_info.info_type = 10
) a
GROUP BY DateDay
ORDER BY DateDay
--This does combine the records from the same day and adds the values, but I can't figure out how to subsequently group the days by month, and the months by year.
Any help would be greatly appreciated.