Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieving Unique Customers in SQL Expand / Collapse
Author
Message
Posted Friday, July 24, 2009 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, July 24, 2009 7:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 11:33 AM
Points: 1,207, Visits: 1,269
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
Posted Monday, July 27, 2009 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, July 29, 2009 12:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, July 29, 2009 4:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse