Classic ASP Discussion - SQL Date Query for Sales Report

Print Page | Close Window

SQL Date Query for Sales Report

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=27372
Printed Date: 28 March 2026 at 9:09pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Date Query for Sales Report

Posted By: Brolin99
Subject: SQL Date Query for Sales Report
Date Posted: 15 May 2009 at 11:43pm

Hey guys,

My boss wants me to produce a report that tracks daily sales from our website. At the moment, each sales has a timestamp. What he wants to be able to do is show all sales between a date range, and then have it broken down into daily totals.

i.e.

Monday $500
Tuesday $2000
Wednesday $800
TOTAL $3200

He wants to be able to select dates at random. Does anyone have any idea on how to do this with SQL and VBScript? I can produce one for a specific day fine, or a grand total fine (by looping), but I'm stuck on how to seperate them into days.

Any help would be greatly appeciated!

Replies:

Posted By: cmv
Date Posted: 22 May 2009 at 9:40pm

if you say *timestamp* this is hopefully a datetime in your database ....

as a prerequisite you need 2 calenders (or at least 2 sets of dropdown boxes for day, month year), after making sure the end_date is later than the begin_date you add that as WHERE clause to your query ... eg. "SELECT SUM(Amount) AS S FROM tblSales WHERE SalesDate >= " & begin_date & " AND SalesDate < " & end_date & " GROUP BY Year(SalesDate), Month(SalesDate), Day(SalesDate)" ... of course you need to add ORDER BY and a SubSelect for appropriate formatting.

advanced users could also add a WITH CUBE clause for subtotals and you need to write everything into an array which maybe also displays weekday, number of sales, ect ....

depending on your database type you will need to add certain entities to your begin and end date like ' # ... consider also using the datetime function of vb-script

hth, christian

-------------
and remember: a CRAY is the only computer that runs an endless loop in just four hours ...