StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is dynamic version</p> <pre><code>Declare @currentYear int Declare @OlderThan5yrs datetime Set @currentYear = Year(GetDate()) - Case When month(GetDate())<4 then 1 else 0 end Set @OlderThan5yrs = cast(cast( @currentYear-5 as varchar(4))+'/04/01' as datetime) Select p.pName, p.purchaser_id, isNull(a.[5+YrAgo],0) as [5+YrAgo], isNull(a.[4YrAgo], 0) as [4YrAgo], isNull(a.[3YrAgo], 0) as [3YrAgo], isNull(a.[2YrAgo], 0) as [2YrAgo], isNull(a.[1YrAgo], 0) as [1YrAgo], isNull(a.[CurYr], 0) as [CurYr], isNull(a.Category, 'Non-purchaser (ever)') as Category From purchasers p Left Join ( Select purchases_purchaser_id, [5] as [5+YrAgo], [4] as [4YrAgo], [3] as [3YrAgo], [2] as [2YrAgo], [1] as [1YrAgo], [0] as [CurYr], Case When [4]+[3]+[2]+[1]+[0] = 5 Then '5+ year consecutive' When [2]+[1]+[0] = 3 Then '3-4 yr consecutive' When [1]+[0] = 2 Then '2 yr Consecutive' When [1]=1 and [0]=0 Then 'Lapsed' When [2]=1 and [1]=0 and [0]=1 Then 'Reactivated' When [4]+[3]+[2]+[1]=0 and [0]=1 Then 'New' When [4]+[3]+[2]+[1]+[0] = 0 Then 'Non-purchaser (last 5 yrs)' Else 'non categorized' End as Category From ( Select purchases_purchaser_id, Case When date_purchased < @OlderThan5yrs Then 5 Else @currentYear - Year(date_purchased)+ Case When month(date_purchased)<4 Then 1 else 0 end end as fiscalYear, count(*) as nPurchases From purchases Group by purchases_purchaser_id, Case When date_purchased < @OlderThan5yrs Then 5 Else @currentYear - Year(date_purchased)+ Case When month(date_purchased)<4 Then 1 else 0 end end ) as AggData PIVOT ( count(nPurchases) for fiscalYear in ([5],[4],[3],[2],[1],[0]) ) pvt ) as a on p.purchaser_id=a.purchases_purchaser_id </code></pre> <p><strong>UPDATED:</strong></p> <p>Here is result with data I inserted in previous query (You will have to add # to table names in the query). </p> <pre><code>pName purchaser_id 5+YrAgo 4YrAgo 3YrAgo 2YrAgo 1YrAgo CurYr Category -------------------- ------------ ------- ------ ------ ------ ------ ----- -------------------------- Non-purchaser 0 0 0 0 0 0 0 Non-purchaser (ever) New purchaser 1 0 0 0 0 0 1 New Reactivated 2 0 0 1 1 0 1 Reactivated Lapsed 3 0 0 0 1 1 0 Lapsed 2 yr Consecutive 4 0 0 0 0 1 1 2 yr Consecutive 3 yr consecutive 5 0 0 0 1 1 1 3-4 yr consecutive 4 yr consecutive 6 0 0 1 1 1 1 3-4 yr consecutive 5+ year consecutive 7 1 1 1 1 1 1 5+ year consecutive Uncategorized 8 0 0 1 0 0 0 non categorized old one 9 1 0 0 0 0 0 Non-purchaser (last 5 yrs) </code></pre> <p>You also don't need columns [5+YrAgo], [4YrAgo], [3YrAgo], [2YrAgo], [1YrAgo] and [CurYr]. I added them to be easier to check query logic.</p> <p><strong>UPDATE 2</strong></p> <p>Below is query you asked in comment. <strong>Note</strong> table structures I've used in query are:</p> <pre><code>Table purchasers ( purchaser_id int, pName varchar(20)) Table purchases (purchases_purchaser_id int, date_purchased datetime) </code></pre> <p>and there is Foreign key on purchases (purchases_purchaser_id) referencing purchases (purchaser_id).</p> <pre><code>;With AggData as ( Select purchases_purchaser_id, Case When [4]+[3]+[2]+[1]+[0] = 5 Then 1 end as [Consec5], Case When [4]=0 and [2]+[1]+[0] = 3 Then 1 end as [Consec34], Case When [2]=0 and [1]+[0] = 2 Then 1 end as [Consec2], Case When [1]=1 and [0]=0 Then 1 end as [Lapsed], Case When [2]=1 and [1]=0 and [0]=1 Then 1 end as [Reactivated], Case When [4]+[3]+[2]+[1]=0 and [0]=1 Then 1 end as [New], Case When [4]+[3]+[2]>0 and [1]+[0]=0 Then 1 end as [Uncateg] From ( Select purchases_purchaser_id, @currentYear - Year(date_purchased) + Case When month(date_purchased)<4 Then 1 else 0 end as fiscalYear, count(*) as nPurchases From purchases Where date_purchased >= @OlderThan5yrs Group by purchases_purchaser_id, @currentYear - Year(date_purchased) + Case When month(date_purchased)<4 Then 1 else 0 end ) as AggData PIVOT ( count(nPurchases) for fiscalYear in ([4],[3],[2],[1],[0]) ) pvt ) Select count([Consec5]) as [Consec5], count([Consec34]) as [Consec34], count([Consec2]) as [Consec2], count([Lapsed]) as [Lapsed], count([Reactivated]) as [Reactivated], count([New]) as [New], count(*)-count(a.purchases_purchaser_id) as [Non], count([Uncateg]) as [Uncateg] From purchasers p Left Join AggData as a on p.purchaser_id=a.purchases_purchaser_id </code></pre> <p>Result (With test data from previous post)</p> <pre><code>Consec5 Consec34 Consec2 Lapsed Reactivated New Non Uncateg ------- -------- ------- ------ ----------- --- --- ------- 1 2 1 1 1 1 2 1 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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