Report on Sales by DAY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Report on Sales by DAY

Author

Topic

justynb
Starting Member

7 Posts

Posted - 2009-09-25 : 16:42:18

Hi guys,

struggling to get my head around how to do this.

I would like a report of sales per day eg

25/09/2009 - 5 sales
24/09/2009 - 7 sales
23/09/2009 - 4 sales

I have a new customer record for each sale so the table structure is very, very easy:

Table: Customers
CustomerID - Unique ID of customer (Int)
Created - Date order was placed (DateTime)

Could anyone tell me how I could write something that returned the number of customers per day?

I could get all customers in date order then do some loops in ASP to output the data but I'm sure there must be a cleaner way to do it using SQL?

Many thanks in advance.

Justyn.

nnogueira
Starting Member

18 Posts

Posted - 2009-09-25 : 18:27:09

SELECT CustomerID,Created,COUNT(Created) AS Expr1
FROM Customers
GROUP BY Created, CustomerID

This will return number of sales per user/day

It help you ?

justynb
Starting Member

7 Posts

Posted - 2009-09-25 : 18:32:45

I can try that but I don't see how that would return the number of sales per day? COUNT(Created) makes no reference to date whatsoever?

nnogueira
Starting Member

18 Posts

Posted - 2009-09-25 : 18:45:10

No... sorry.. i write wrong query.. its a datetime value.. and need to be group in same day.. try this..

SELECT CustomerID,LEFT(Created, 11) AS date2, COUNT(*) AS Expr1
FROM Customers
GROUP BY CustomerID,LEFT(Created, 11)

It will return CustomerID,Dates(Created collumm) and count of sales (Count(*)) of that day for that customers

justynb
Starting Member

7 Posts

Posted - 2009-09-26 : 02:51:35

That doesn't work.

I had 4 sales yesterday and this is what I get in my array:

10417 Sep 25 2009 1
10419 Sep 25 2009 1
10420 Sep 25 2009 1
10421 Sep 25 2009 1

(The first column is the customer ID)

All I wanted returned was:

DATE: Sep 25 2009
TotalSales: 4

nnogueira
Starting Member

18 Posts

Posted - 2009-09-27 : 15:21:31

Well, its simple, remove the CustomerID from query, now u can get that values, dates and number of sales per day.

example:

SELECT LEFT(Created, 11) AS date2, COUNT(*) AS Expr1
FROM Customers
GROUP BY LEFT(Created, 11)

justynb
Starting Member

7 Posts

Posted - 2009-09-27 : 15:28:47

Thanks.

I've managed to get it working BUT I get this:

20/09/2009 1 Sale
18/09/2009 1 Sale
17/09/2009 1 Sale

You will notice 19/09/2009 is missing as there were no sales that day. How can I return a value for EVERY day in the selected period regardless of whether there were sales or not?

Thanks in advance.

lack
Starting Member

5 Posts

Posted - 2009-09-28 : 03:40:20

u're gonna need an auxiliary date table for that.
suppose ur date table name is tblDate and its date column is date1:

SELECT A.date1, IsNull(B.Expr1,0) FROM
tblDate A LEFT OUTER JOIN
(
SELECT LEFT(Created, 11) AS date2, COUNT(*) AS Expr1
FROM Customers
GROUP BY LEFT(Created, 11)
) B ON A.date1=B.date2
WHERE date1 IS BETWEEN begin_date_here AND ending_date_here

------------- Vi Veri Universum Vivus Vici -------------
* I, while living, have conquered the universe by truth *

lack
Starting Member

5 Posts

Posted - 2009-09-28 : 03:42:05

if u don't want to make an auxiliary table just for that, u gotta learn tsql..

------------- Vi Veri Universum Vivus Vici -------------
* I, while living, have conquered the universe by truth *

justynb
Starting Member

7 Posts

Posted - 2009-09-30 : 11:49:10

OK I decided to do it in ASP. For future reference this is what I did:

My Stored proc gets all sales between the start and end dates requested ordered by date descending.

Then in ASP:

		TotalSales = 0
		ThisDate = CStr(ThisDayTo & "/" & ThisMonthTo & "/" & ThisYearTo)
		For j = 0 to (0-ThisDateDiff) Step - 1
			For i = 0 to ubound(sArray,2)
				If Left(CStr(sArray(1,i)),10) = ThisDate Then
					' at least 1 sale on this day
					TotalSales = TotalSales + 1
				End If
			Next
			If TotalSales = 0 Then
				Response.Write("<tr><td>" & ThisDate & " <b>0 Sales</b></td></tr>" & VbCrLf)
			ElseIf TotalSales = 1 Then
				Response.Write("<tr><td>" & ThisDate & " <b>1 Sale</b></td></tr>" & VbCrLf)
			Else
				Response.Write("<tr><td>" & ThisDate & " <b>" & TotalSales & " Sales</b></td></tr>" & VbCrLf)
			End If
			TotalSales = 0
			ThisDate = CStr(DateAdd("d", -1, CDate(ThisDate)))
		Next

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources