Retrieving Unique Customers in SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Retrieving Unique Customers in SQL

Retrieving Unique Customers in SQL

Rate Topic

Display Mode

Topic Options

Author

Message

CheyneL

Posted Friday, July 24, 2009 7:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 22, 2009 7:21 AM
Points: 4, Visits: 24

Hi,

I am trying to work out the number of UNIQUE customers within a date range. So between the 1st and 8th I want to display the NUMBER of Unique customers per day(1st - 8th), meaning the customers on the 1st must not show on the 2nd - 8th, and those on the 2nd must not show on the 3rd - 8th etc.

My query up to now is displaying ALL the customers, from the 1st to the 8th:

CREATE TABLE #UNIQUE_CUST_UNSUCC1
(DateTimeStamp Varchar(20),
CustomerName Varchar(100),
CustomerID INT)
INSERT INTO #UNIQUE_CUST_UNSUCC1
(DateTimeStamp,
CustomerName,
CustomerID)

SELECT (CAST (CAST (RIGHT ('0' + CAST(YEAR([DateTimeStamp]) AS VARCHAR(4)),4) + '/' +
RIGHT('0' + CAST((MONTH([DateTimeStamp])) AS VARCHAR(2)),2) + '/' +
RIGHT('0' + CAST(DAY([DateTimeStamp]) AS VARCHAR(2)),2) AS CHAR(10))AS VARCHAR(10))),
CUS.CustomerName,CUS.CustomerID
FROM EasyRecharge.dbo.CustomerUsage CUS
INNER JOIN EasyRecharge.dbo.Customers_Dormant CNU on CUS.FromMSISDN = CNU.MSISDN
WHERE ([DateTimeStamp]
between '2009/07/01 00:00:00' AND '2009/07/08 23:59:59')
GROUP BY CUS.CustomerName,CUS.CustomerID, (CAST (CAST (RIGHT ('0' + CAST(YEAR([DateTimeStamp]) AS VARCHAR(4)),4) + '/' +
RIGHT('0' + CAST((MONTH([DateTimeStamp])) AS VARCHAR(2)),2) + '/' +
RIGHT('0' + CAST(DAY([DateTimeStamp]) AS VARCHAR(2)),2) AS CHAR(10))AS VARCHAR(10)))
ORDER BY (CAST (CAST (RIGHT ('0' + CAST(YEAR([DateTimeStamp]) AS VARCHAR(4)),4) + '/' +
RIGHT('0' + CAST((MONTH([DateTimeStamp])) AS VARCHAR(2)),2) + '/' +
RIGHT('0' + CAST(DAY([DateTimeStamp]) AS VARCHAR(2)),2) AS CHAR(10))AS VARCHAR(10)))

I hope someone can help me.'

Thank you

Regards,
Cheyne

Post #759061

matt6288

Posted Friday, July 24, 2009 7:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:11 PM
Points: 1,130, Visits: 1,182

SELECT MIN(dateTimeStamp), CUS.customerName, CUS.CustomerID
FROM EasyRecharge.dbo.CustomerUsage CUS 
INNER JOIN EasyRecharge.dbo.Customers_Dormant CNU on CUS.FromMSISDN = CNU.MSISDN
WHERE ([DateTimeStamp] 
between '2009/07/01 00:00:00' AND '2009/07/08 23:59:59')
GROUP BY CUS.customerName, CUS.customerID

This is assuming that the dateTimeStamp column is of dateTime datatype. You might have to do all of that formatting to this column but I think this is close to what you were asking.

Let me know if this works.

Post #759068

CheyneL

Posted Monday, July 27, 2009 7:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 22, 2009 7:21 AM
Points: 4, Visits: 24

It doesnt seem to work. It is still displaying all the customers.

Basically what I want is,eg day 1 -7. day 7 must show only the unique customers, which means that the customers between day 1 - 6 must not be displayed in day 7. The same goes for day 6 , must not have day 1 - 5 customers in it.

But I want to display the number of customers, and not the actualy names. Eg

Day 1 : 5 Unique customers
Day 2 : 2 Unique customers
Day 3 : 0 Unique customers(which means that the same customers in Day 1 and 2 might have done some transactions
Day 4 : 15 Unique Customers(this is excluding day 1 - 3' customers)

ect.

any ideas how ?

Post #760038

manish.singh-1128425

Posted Wednesday, July 29, 2009 12:09 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, October 28, 2010 3:03 AM
Points: 18, Visits: 106

Hi,

In that case you can try the bellow query

SELECT a.dt, COUNT(*) As Customer_Count
FROM
(
SELECT MIN(Convert(varchar,dateTimeStamp,101)) AS dt, CUS.customerName, CUS.CustomerID
FROM EasyRecharge.dbo.CustomerUsage CUS 
INNER JOIN EasyRecharge.dbo.Customers_Dormant CNU on CUS.FromMSISDN = CNU.MSISDN
WHERE ([DateTimeStamp]
between '2009/07/01 00:00:00' AND '2009/07/08 23:59:59')
GROUP BY CUS.customerName, CUS.customerID
) A
GROUP BY a.dt

Post #761285

CheyneL

Posted Wednesday, July 29, 2009 4:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 22, 2009 7:21 AM
Points: 4, Visits: 24

That actually worked perfect!!!!!!!

Thank you so much!!!

How do I accept your reply as the answer?

Post #761374

« Prev Topic | Next Topic »

Permissions