November 7, 2006 at 12:47 pm
I'm trying to find a script that will allow me to count how many times a customers has purchased. I need it to look something like this:
custno Orderdate Ordercount
11111 11/01/2006 1
11111 11/03/2006 2
22222 11/02/2006 1
22222 11/04/2006 2
22222 11/05/2006 3
What's happening now is I can't seem get the counter to restart at 1 everytime there is a new customer number on the list who has purchased. Any suggestions????
November 7, 2006 at 1:05 pm
What do you need exactly... update the table once, then forget about it. Have a select to return that number permanently. Then do you have access to a front end to do those calculations or you absolutely have to do it on the server??
November 7, 2006 at 1:53 pm
Post your query. Sounds like a GROUP/COUNT problem.
November 8, 2006 at 2:14 am
Orderdate is not unique so you cant group with that there unless you want each customer order with the same count. You want to do something like this (here I will show the last order date)
SELECT
custno,
max(orderdate),
count(custno) as Ordercount
FROM
Customer c
INNER JOiN Orders o ON c.custno = o.custno
GROUP BY
custno
ORDER BY
Ordercount
Complete guess at what you want but it gives you an idea of what you might be needing to do.
November 8, 2006 at 6:52 am
-- This would give you a total count with one record
SELECT
count(custno) as Ordercount
FROM
Customer c
INNER JOiN Orders o ON c.custno = o.custno
-- This would give you a count by Date, could be used to detect Trends
SELECT
OrderDate,
count(custno) as Ordercount
FROM
Customer c
INNER JOiN Orders o ON c.custno = o.custno
GROUP BY
OrderDate
ORDER BY
OrderDate
-- This should give you a count by Month, Year
SELECT
Year(OrderDate) as Year,
Month(OrderDate) as Month,
count(custno) as Ordercount
FROM
Customer c
INNER JOiN Orders o ON c.custno = o.custno
GROUP BY
Year(OrderDate),
Month(OrderDate)
ORDER BY
Year(OrderDate),
Month(OrderDate)
November 8, 2006 at 7:22 am
It looks to me as if you want a running total sort by customer and date.
Something like this might help:
--DROP TABLE Orders
GO
CREATE TABLE Orders
(
orderID int
, custno int
, orderDate datetime
)
GO
INSERT Orders (orderID, custno, orderDate)
SELECT 1, 11111, '11/01/2006'
UNION ALL
SELECT 2, 11111, '11/03/2006'
UNION ALL
SELECT 3, 22222, '11/02/2006'
UNION ALL
SELECT 4, 22222, '11/04/2006'
UNION ALL
SELECT 5, 22222, '11/05/2006'
SELECT a.custno
, a.Orderdate
, (SELECT Count(*)
FROM Orders b
WHERE b.orderDate <= a.orderDate
AND a.custno = b.custno
) AS OrderCount
FROM Orders a
ORDER BY a.custno, a.orderDate
November 8, 2006 at 8:05 am
mkeast - are you sure he should drop table orders????
November 8, 2006 at 8:08 am
On a dev box, it should be a big problem to repair.
You are develloping on a developpement box right???
November 8, 2006 at 10:33 am
Orders is my example table. If the whole script is run, it should be run in a test database on a development server, not in the production database.
That said, the query in bold is what I'm presenting, the rest is supporting code for the query. I'll use a temp table next time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply