Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Going on the theory that you want your periods calculated as 30 days from the first time a survey is submitted, here is a (gross) solution.</p> <pre><code>declare @Something table ( 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 'Cust1', '100', '7/13/13' union all select 'Cust2', '100', '4/20/13' union all select 'Cust2', '100', '5/22/13' union all select 'Cust2', '100', '7/20/13' union all select 'Cust2', '100', '7/24/13' union all select 'Cust2', '100', '9/28/13' --SELECT CustID,SurveyId,COUNT(*) FROM ( select a.CustID,a.SurveyId,b.ResponseStart,--CONVERT(int,a.ResponseDate-b.ResponseStart), CASE WHEN CONVERT(int,a.ResponseDate-b.ResponseStart) &gt; 30 THEN ((CONVERT(int,a.ResponseDate-b.ResponseStart))-(CONVERT(int,a.ResponseDate-b.ResponseStart) % 30))/30+1 ELSE 1 END CustomPeriod -- defines periods 30 days out from first entry of survey from @Something a inner join (select CustID,SurveyId,MIN(ResponseDate) ResponseStart from @Something group by CustID,SurveyId) b on a.SurveyId=b.SurveyId and a.CustID=b.CustID group by a.CustID,a.SurveyId,b.ResponseStart, CASE WHEN CONVERT(int,a.ResponseDate-b.ResponseStart) &gt; 30 THEN ((CONVERT(int,a.ResponseDate-b.ResponseStart))-(CONVERT(int,a.ResponseDate-b.ResponseStart) % 30))/30+1 ELSE 1 END --) x GROUP BY CustID,SurveyId </code></pre> <p>At the very least you'd probably want to make the CASE statement a function so it reads a bit cleaner. Better would be defining explicit windows in a separate table. This may not be feasible if you want to avoid situations like surveys returned at the end of period one followed by another in period two a couple days later.</p> <p>You should consider handling this on input if possible. For example, if you are identifying a customer in an online survey, reject attempts to fill out a survey. Or if someone is mailing these in, make the data entry person reject it if one has come within 30 days.</p> <p>Or, along the same lines as "wild and crazy", add a bit and an INSERT trigger. Only turn the bit on if no surveys of that type for that customer found within the time period.</p> <p>Overall, phrasing the issue a little more completely would be helpful. However I do appreciate the actual coded example.</p>
    singulars
    1. This table or related slice is empty.
    1. POCount Response once in 30 days SQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. UStetch
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCount Response once in 30 days SQL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI appreciate the other options you've mentioned, but they are not within my scope, as data comes from various big data stores across the org. So, can't touch the source database or input systems. Thanks, your code returns what I expected with some test data. I have to make a table of all responses received and will try this - the logic is actually too complicated (at least for me as I consider myself mid-level) as I have to count one response/per survey/ per customer/per group/per 30 days!
      singulars
      1. PO
      1. USJen Ro
    2. COThis is part of the code for a big report where the user is able to select date range and if they select YTD, then I have to see that this does not cause performance issues.
      singulars
      1. PO
      1. USJen Ro
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload