July 10, 2013 at 1:05 pm
Hello,
If I have a customer respond to the same survey in 30 days more than once, I only want to count it once. Can someone show me code to do that please?
CustomerID SurveyId ResponseDate
cust1 100 5/6/13
Cust1 100 5/13/13
Cust2 100 4/20/13
Cust2 100 5/22/13
Then output should be like this:
CustomerID SurveyId CountSurvey
Cust1 100 1
Cust2 100 2
July 10, 2013 at 1:11 pm
COUNT(DISTINCT CustomerID)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 1:18 pm
Hi Sean,
Thanks, but it's not just distinct customer, I need to count based on whether his response to survey was in the last 30 days. If he responded to more than once in the last 30 days to the same survey then count him only once. If he responded more than once but after 30 days from the last response, then count it as twice and so on.
July 10, 2013 at 1:35 pm
It isn't too bad but can you turn your data into something readily consumable? A good example is what I posted in your other thread. It makes it a LOT easier for us to get started.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 1:52 pm
Sure, is this what you mean?
create table #Something
(
CustID Char(10),
SurveyId char(5),
ResponseDate datetime
)
insert #Something
select 'Cust1', '100', '5/6/13' union all
select 'Cust1', '100', '5/13/13' union all
select 'Cust2', '100', '4/20/13' union all
select 'Cust2', '100', '5/22/13'
select distinct custid, SurveyId, Count(custid) as CountResponse from #Something
group by CustID, SurveyId
The above code only gives me the total count of Response, not sure how to code to count only once per 30 day period.
July 10, 2013 at 2:17 pm
puja63 (7/10/2013)
Sure, is this what you mean?
create table #Something
(
CustID Char(10),
SurveyId char(5),
ResponseDate datetime
)
insert #Something
select 'Cust1', '100', '5/6/13' union all
select 'Cust1', '100', '5/13/13' union all
select 'Cust2', '100', '4/20/13' union all
select 'Cust2', '100', '5/22/13'
select distinct custid, SurveyId, Count(custid) as CountResponse from #Something
group by CustID, SurveyId
The above code only gives me the total count of Response, not sure how to code to count only once per 30 day period.
This can get rather nasty. How do you define your 30 day window? What happens if say the first person has a third response from June 5th? That is 31 days after the first response but less than 30 from the second response.
insert #Something
select 'Cust1', '100', '5/6/13' union all
select 'Cust1', '100', '5/13/13' union all
select 'Cust1', '100', '6/5/13' union all
select 'Cust2', '100', '4/20/13' union all
select 'Cust2', '100', '5/22/13'
What would be the expected output of that?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 3:40 pm
I got a head-ache from this really, b'cos I thought about that as well and this kind of logic is new for me. I talked to the Client and they said they do want the 30 day rolling period.
insert #Something
select 'Cust1', '100', '5/6/13' union all
select 'Cust1', '100', '5/13/13' union all
select 'Cust1', '100', '6/6/13' union all
select 'Cust1', '100', '7/1/13' union all
select 'Cust2', '100', '4/20/13' union all
select 'Cust2', '100', '5/22/13'
In your example, the third row should actually be 6/6/13 for it to be the 31st day from 5/6/13 I think. If so, count for Customer1 would be 2, because 6/6 is after 30 days from 5/6. And from this date onwards, they want to count only 1 response for the next 30 days from 6/6 and so on..
Can this be coded?
If not, I have to talk to them to see if I can count all responses after the first 30 days have elapsed. So, in our example the count would be 1 (for 5/6 ) and don't count 5/13 b'cos it's within 30 days of the 5/6, but count 6/6, 7/1 for Cust1. Therefore, the total count will be 3. Even this I'm having trouble to code as it seems like a loop for each customer, survey!
July 11, 2013 at 1:23 am
I'm not sayin' this is the best or fastest way or nuthin' but it (or somethin' like it) might work for you:
CREATE TABLE #Responses
(CustID VARCHAR(10), SurveyID VARCHAR(10), ResponseDate DATE);
CREATE TABLE #Responses2
(CustID VARCHAR(10), SurveyID VARCHAR(10), ResponseDate DATE);
INSERT #Responses (CustID, SurveyID, ResponseDate)
SELECT 'Cust1', '100', '5/6/13' union all
SELECT 'Cust1', '100', '5/13/13' union all
SELECT 'Cust1', '100', '6/5/13' union all
SELECT 'Cust1', '100', '7/1/13' union all
SELECT 'Cust2', '100', '4/20/13' union all
SELECT 'Cust2', '100', '5/22/13';
DECLARE @Rows INT = 1;
INSERT INTO #Responses2
SELECT CustID, SurveyID, MIN(ResponseDate)
FROM #Responses
GROUP BY CustID, SurveyID;
WHILE @Rows > 0
BEGIN
WITH Responses AS (
SELECT CustID, SurveyID, ResponseDate
,rn=ROW_NUMBER() OVER (PARTITION BY CustID, SurveyID ORDER BY ResponseDate)
FROM #Responses a
WHERE ResponseDate > DATEADD(day, 30, (
SELECT MAX(ResponseDate)
FROM #Responses2 b
WHERE a.CustID = b.CustID AND a.SurveyID = b.SurveyID
GROUP BY CustID, SurveyID))
)
INSERT INTO #Responses2
SELECT CustID, SurveyID, ResponseDate
FROM Responses
WHERE rn=1;
SELECT @Rows = @@ROWCOUNT;
END
SELECT CustID, SurveyID, Responses=COUNT(*)
FROM #Responses2
GROUP BY CustID, SurveyID;
GO
DROP TABLE #Responses;
DROP TABLE #Responses2;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 11, 2013 at 2:46 am
puja63 (7/10/2013)
Hi Sean,Thanks, but it's not just distinct customer, I need to count based on whether his response to survey was in the last 30 days. If he responded to more than once in the last 30 days to the same survey then count him only once. If he responded more than once but after 30 days from the last response, then count it as twice and so on.
puja63 (7/10/2013)
I got a head-ache from this really, b'cos I thought about that as well and this kind of logic is new for me. I talked to the Client and they said they do want the 30 day rolling period.
insert #Something
select 'Cust1', '100', '5/6/13' union all
select 'Cust1', '100', '5/13/13' union all
select 'Cust1', '100', '6/6/13' union all
select 'Cust1', '100', '7/1/13' union all
select 'Cust2', '100', '4/20/13' union all
select 'Cust2', '100', '5/22/13'
In your example, the third row should actually be 6/6/13 for it to be the 31st day from 5/6/13 I think. If so, count for Customer1 would be 2, because 6/6 is after 30 days from 5/6. And from this date onwards, they want to count only 1 response for the next 30 days from 6/6 and so on..
Can this be coded?
If not, I have to talk to them to see if I can count all responses after the first 30 days have elapsed. So, in our example the count would be 1 (for 5/6 ) and don't count 5/13 b'cos it's within 30 days of the 5/6, but count 6/6, 7/1 for Cust1. Therefore, the total count will be 3. Even this I'm having trouble to code as it seems like a loop for each customer, survey!
Don't you need the survey date as a fixed start date for this? If you use today's date, your result set could be different for every day this week.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply