Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Although it COULD be done a bit easier with another table of date ranges showing the 5 fiscal years, I have hard-coded the from/to date references for your query and appears to be working... </p> <p>The INNER Select will pre-gather a "flag" based on any 1 or more purchase within the given date range... ex: Apr 1, 2010 = "20100401" for date conversion to Mar 31, 2011 = "20110331", and cycle through last 5 years... Additionally, a flag to count for ANY with a date purchase within the actual purchases table to confirm a "never purchased" vs someone purchasing 6, 7 or older years history...</p> <p>That queries' basis will basically create a cross-tab of possible individual years where activity has occurred. I can then query with the most detailed criteria for some caption of their classification down to the least...</p> <p>I converted from another SQL language as best as possible to comply with SQL-Server syntax (mostly about the date conversion), but otherwise, the principle and queries do work... The final classification column is character, but can be whatever you want to supercede.</p> <pre><code>SELECT id, CASE WHEN year1 + year2 + year3 + year4 + year5 = 5 THEN "5+yrs " WHEN year1 + year2 + year3 + year4 &gt;= 3 THEN "3-4yrs" WHEN year1 + year2 = 2, "2yrs " WHEN year1 = 1 AND year2 = 0 AND year3 = 1 THEN "Reacti" WHEN year1 = 1 THEN "New " WHEN year1 = 0 AND year2 = 1 THEN "Lapsed" WHEN AnyPurchase = 1, "over5" ELSE "never" BuyerClassification END FROM ( SELECT id, MAX( CASE WHEN date_purchased &gt;= CONVERT( Date, "20100401", 112 ) AND date_purchased &lt;= CONVERT( Date, "20110331", 112 ) THEN 1 ELSE 0 END ) Year1, MAX( CASE WHEN date_purchased &gt;= CONVERT( Date, "20090401", 112 ) AND date_purchased &lt;= CONVERT( Date, "20100331", 112 ) THEN 1 ELSE 0 END ) Year2, MAX( CASE WEHEN date_purchased &gt;= CONVERT( Date, "20080401", 112 ) AND date_purchased &lt;= CONVERT( Date, "20090331", 112 ) THEN 1 ELSE 0 END ) Year3, MAX( CASE WHEN date_purchased &gt;= CONVERT( Date, "20070401", 112 ) AND date_purchased &lt;= CONVERT( Date, "20080331", 112 ) THEN 1 ELSE 0 END ) Year4, MAX( CASE WHEN date_purchased &gt;= CONVERT( Date, "20060401", 112 ) AND date_purchased &lt;= CONVERT( Date, "20070331", 112 ) THEN 1 ELSE 0 END ) Year5, MAX( CASE WHEN date_purchased &lt;= CONVERT( Date, "20100401", 112 ) THEN 1 ELSE 0 END ) AnyPurchase FROM purchaser LEFT OUTER JOIN purchases ON purchaser.id = purchases.purchases_purchaser_id GROUP BY 1 ) PreGroup1 </code></pre> <p>EDIT -- fixed parens via syntax conversion and missed it...</p> <p>The "Group By 1" refers to doing a group by the first column in the query which is the purchaser's ID from the purchaser. By doing a left-outer join will guarantee all possible people in the purchasers table regardless of having any actual purchases. The "PreGroup1" is the "alias" of the select statement just in case you wanted to do other joins subsequent in the outer most select where detecting the year values for classification.</p> <p>Although it will work, but may not be as efficient as others have chimed-in on by doing analysis of the query, it may open your mind to some querying and aggregating techniques. This process is basically creating a sort-of cross-tab by utilization of case/when construct on the inner SQL-Select, and final classification in the OUTER most SQL-Select.</p>
    singulars
    1. This table or related slice is empty.
    1. POGrouping by financial year and applying these groups as filters
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDRapp
    1. USDRapp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks, that looks awesome! sorry for the stupidity of this ask, but what does the GROUP BY 1) pregroup refer to? should this group by purchaser.id instead? Also would it be possible to get the date to be a rolling 5 years instead of hardcoded (for example something like: SET @year = CONVERT(NVARCHAR(7), YEAR(GETDATE())) SET @pyear = CONVERT(NVARCHAR(7), YEAR(GETDATE())) - 1 SET @yr_start = CAST('01/04/'+ @pyear AS DATETIME) SET @yr_end = CAST('31/03/'+ @year AS DATETIME) As I'm new to SQL not sure if this is nonsense though! Massive thanks again, Davin :)
      singulars
      1. PO
      1. USDibstar
    2. CO- your For BuyerClassification condition has to be modified (does not reflect initial conditions) - Your method performs 10 table scans of purchases table, which is not efficient if you have huge number of rows - syntax is not correct: some THEN clause missing, Alias BuyerClassification should go after END clause, not before, Max should have closing parenthesis (between END and column alias).
      singulars
      1. PO
      1. USNiikola
    3. COTIME/IO Comparison (1000000 rows in purchases): DRApp Table 'purchases'. Scan count 10, logical reads 5774, ... Table 'purchasers'. Scan count 1, logical reads 2, ... CPU time = 1669 ms, elapsed time = 1678 ms. Niikola: Table 'Worktable'. Scan count 0, logical reads 0, ... Table 'purchases'. Scan count 1, logical reads 2598, ... Table 'purchasers'. Scan count 1, logical reads 2, ... CPU time = 827 ms, elapsed time = 798 ms.
      singulars
      1. PO
      1. USNiikola
 

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