July 24, 2009 at 7:32 am
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
July 24, 2009 at 7:40 am
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.
July 27, 2009 at 7:35 am
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 ?
July 29, 2009 at 12:09 am
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
July 29, 2009 at 4:43 am
That actually worked perfect!!!!!!!
Thank you so much!!!
How do I accept your reply as the answer?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply