Final Count on query - 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

Final Count on query

Author

Topic

rcp
Starting Member

32 Posts

Posted - 2012-02-07 : 10:56:42

Hi,

I need to get a count on the customer_id column. This is the query I have so far:

WITH
T1Counts
AS
(
select
Convert(date,created_date) AS ordDate
,customer_id
from customer_order
where YEAR(created_date) = '2011'
and status like 'paid'
and PackageDescription not in ('Extended Archive Membership','Extended Archive Access')
)
SELECT COUNT(T1.customer_id) AS [Loyalty]
,T1.customer_id

FROM T1Counts T1

INNER Join (select distinct customer_id
from customer_order
where MONTH(created_date) = '1'
and YEAR(created_date) = '2012'
and status like 'paid'
and PackageDescription not in ('Extended Archive Membership','Extended Archive Access')) AS b
ON T1.customer_id=b.customer_id
GROUP BY T1.customer_id

HAVING COUNT(T1.customer_id) > 2

SAMPLE OF RESULTS:

Loyalty customer_id
4 14232EAD-D584-11D3-B772-0000E2220F51
5 8C3E6535-E2C0-11D3-B779-0000E2220F51
3 074606B8-E34C-11D3-B77A-0000E2220F51
5 0BF71A1A-F5C9-11D3-B77D-0000E2220F51

Any help would be greatly appreciated.

Regards,

Roland

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:29:55

whats the result you're looking at? whats the field on which you want to count on? explain with some sample data to help us understand grouping

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 12:35:30

over thinking this?

SELECT Customer_Id, COUNT(*) AS Loyalty FROM from customer_order ORDER BY 2 DESC

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:36:55

quote:
Originally posted by X002548

over thinking this?

SELECT Customer_Id, COUNT(*) AS Loyalty FROM ~~from~~ customer_order ORDER BY 2 DESC

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

I want to be completely sure what op wants before suggesting something

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcp
Starting Member

32 Posts

Posted - 2012-02-07 : 12:42:29

This query is now causing me pain, I am trying to find out, in a given month, the number of customers that are making their third or more purchase for the previous 12 months. Counting any multiple order in one day as one order.

The count needs to be on customer_id. The query I have uploaded shows the number of customers that have made a purchase in January that are making their 3rd or more orders

Loyalty customer_id
4 14232EAD-D584-11D3-B772-0000E2220F51
5 8C3E6535-E2C0-11D3-B779-0000E2220F51
3 074606B8-E34C-11D3-B77A-0000E2220F51
5 0BF71A1A-F5C9-11D3-B77D-0000E2220F51

All I want is to count how many customers meet this criteria, but it is proving much easier to state it then to come up with an answer.

Regards,

Roland

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 12:48:36

quote:
Originally posted by rcp

I am trying to find out, in a given month, the number of customers that are making their third or more purchase for the previous 12 months. Counting any multiple order in one day as one order.

Regards,

Roland

So, let me see if I understand.

For this Month, I need to add up the previous 12 months of purchases, and see if this Months total is equal to 2/3.00 times that total (or greater?). And all purchases on any give days should count as 1?

Is that correct?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 13:55:20

quote:
Originally posted by rcp

This query is now causing me pain, I am trying to find out, in a given month, the number of customers that are making their third or more purchase for the previous 12 months. Counting any multiple order in one day as one order.

The count needs to be on customer_id. The query I have uploaded shows the number of customers that have made a purchase in January that are making their 3rd or more orders

Loyalty customer_id
4 14232EAD-D584-11D3-B772-0000E2220F51
5 8C3E6535-E2C0-11D3-B779-0000E2220F51
3 074606B8-E34C-11D3-B77A-0000E2220F51
5 0BF71A1A-F5C9-11D3-B77D-0000E2220F51

All I want is to count how many customers meet this criteria, but it is proving much easier to state it then to come up with an answer.

Regards,

Roland

Sounds like this


SELECT COUNT(CustomerID)
FROM
(
SELECT DISTINCT CustomerID,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS MonthDate
FROM customer_order
WHERE created_date >=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND created_date < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
)m
CROSS APPLY (SELECT COUNT(1) AS Cnt
             FROM customer_order
             WHERE CustomerID=m.CustomerID
             AND created_date >=DATEADD(mm,-11,MonthDate)
AND created_date < DATEADD(mm,1,MonthDate)

)n
WHERE Cnt >=3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcp
Starting Member

32 Posts

Posted - 2012-02-08 : 09:21:02

Hi Brett,

What I am trying to do is get a count of the unique customers that have made a purchase in a given month eg. Jan 2012. Then I am matching those to the orders placed over the last twelve months before my given month, in this case, Jan 2011 to Dec 2011. I think I have done it, here is my code, let me know what you think.

WITH
T1Counts
AS
(
SELECT a.customer_id AS LoyalCustomers
from customer_order a
INNER JOIN(
SELECT COUNT(a.customer_id) AS [Loyal Customers]
,a.customer_id
FROM
(
select Convert(date,created_date) AS ordDate
,customer_id
from customer_order
where YEAR(created_date) = '2011'
and status like 'paid'
and PackageDescription not in ('Extended Archive Membership','Extended Archive Access')
group by customer_id,Convert(date,created_date)
) a
group by a.customer_id

having COUNT(a.customer_id) > 2
) b
ON a.customer_id=b.customer_id
where MONTH(a.created_date) = '1'
and YEAR(a.created_date) = '2012'
and a.status like 'paid'
and a.PackageDescription not in ('Extended Archive Membership','Extended Archive Access')
group by a.customer_id
)
SELECT COUNT(T1.LoyalCustomers) [3 Orders or More]
FROM T1Counts T1

RESULTS
3 Orders or More
1124

There is a little bit more to it. Any customer that places multiple orders on a single day is just counted as one order as this is determined to be an upgraded order.

Cheers,

Roland

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 13:59:53

what about my suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcp
Starting Member

32 Posts

Posted - 2012-02-09 : 05:57:36

Hi Visakh,

I tried your suggestion, but it does not take into account all of the other little bits that I had to consider. I did however take the bit about dates and have added it to my query. See below:

DECLARE
@MONTHSD AS datetime = DATEADD(mm,DATEDIFF(mm,0,'2011-01-01'),0);

WITH
T1Counts
AS
(
SELECT a.customer_id AS LoyalCustomers
from customer_order a
INNER JOIN(
SELECT COUNT(a.customer_id) AS [Loyal Customers]
,a.customer_id
FROM
(
select Convert(date,created_date) AS ordDate
,customer_id
from customer_order
where created_date between DATEADD(mm,-12,DATEADD(mm,DATEDIFF(mm,0,@MONTHSD),0)) and DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@MONTHSD),0))
and status like 'paid'
and PackageDescription not in ('Extended Archive Membership','Extended Archive Access','eam','eaa')
group by customer_id,Convert(date,created_date)
) a
group by a.customer_id

having COUNT(a.customer_id) >= 2
) b
ON a.customer_id=b.customer_id
where a.created_date between @MONTHSD and DATEADD(mm,DATEDIFF(mm,0,@MONTHSD)+1,0)
and a.status like 'paid'
and a.PackageDescription not in ('Extended Archive Membership','Extended Archive Access','eam','eaa')
group by a.customer_id
)
SELECT COUNT(T1.LoyalCustomers) [3 Orders or More]
FROM T1Counts T1

Thank you for all of your help. I really do appreciate it, I have learnt so much over the last month from sqlteam.com

Regards,

Roland

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources