• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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.

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

Open in new window


Expected Result
Month     Average Basket Total        Transaction Count
7                      19.8216                               2482734
8                      21.076                              2904966
9                      21.2056                              2475887
0
Sleepydog
Asked:
Sleepydog
1 Solution
 
Haris DulicCommented:
Try this
DECLARE       
@VSTARTDATE   DATETIME      =      '2014-09-01',
@VENDDATE     DATETIME      =      '2014-09-23';

SELECT month(TH.TRAN_STRT_TS) as month, 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
group by month(TH.TRAN_STRT_TS) 
order by 1

Open in new window

0
 
SleepydogAuthor Commented:
Works perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now