Sleepydog
asked on
I want to create a SQL query to report monthly sales
I want to loop through each months data and return a data set in a single query.
The SQL below only for a single month, and doesn't not include the month column.
Expected Result
Month Average Basket Total Transaction Count
7 19.8216 2482734
8 21.076 2904966
9 21.2056 2475887
The SQL below only for a single month, and doesn't not include the month column.
DECLARE
@VSTARTDATE DATETIME = '2014-09-01',
@VENDDATE DATETIME = '2014-09-23';
SELECT AVG(TOT_AMT) AS [Average Basket Total] , COUNT(TRAN_ID) AS [Transaction Count]
FROM TRANS_HEADER TH
WHERE
CONVERT( DATE, TH.TRAN_STRT_TS) BETWEEN @VSTARTDATE AND @VENDDATE
AND TH.TRAN_CD = 1 -- TRAN CODE = 1, SALES ONLY
AND TH.VOID_CD = 0 -- NO VOIDS
AND TH.TRNING_MDE_FG = 0 -- NOT TRAINING MODE
Expected Result
Month Average Basket Total Transaction Count
7 19.8216 2482734
8 21.076 2904966
9 21.2056 2475887
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER