Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p><strong>sample data as no room in question!</strong></p> <p>Main table information:</p> <pre><code>Create table #donation ( contact_id NVARCHAR(255) ,id UNIQUEIDENTIFIER ,date_received DATETIME ,event_id NVARCHAR(255) ,value_gross DECIMAL(18,3) ,value_net DECIMAL(18,3) ,giftaid_status NVARCHAR(50) ,payment_method NVARCHAR(50) -- 3 = regular, 1 = single ) INSERT INTO #donation VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590', 'CEDB5C00-DB78-4D70-B222-892DCAD361E6', '2008-02-12 00:00:00.000', 'AA9237FC-E9E0-48D4-BF2C-62DDCDA7E874', 15.00, 15.00, 1, 3) ('18EC3CD2-3065-4FF4-BE40-000004228590', '45FB0841-BD82-47F2-B66F-473CE196B9E0', '2010-09-08 00:00:00.000', 'AA9237FC-E9E0-48D4-BF2C-62DDCDA7E874', 15.00, 15.00, 1, 3) ('18EC3CD2-3065-4FF4-BE40-000004228590', 'A27451B9-7542-4F61-A38F-EB202AD24CC5', '2010-03-09 00:00:00.000', 'AA9237FC-E9E0-48D4-BF2C-62DDCDA7E874', 15.00, 15.00, 1, 3) ('18EC3CD2-3065-4FF4-BE40-000004228590', '8F9E143C-4E35-4C9B-A385-A86F20D8EDA4', '2008-06-06 00:00:00.000', 'AA9237FC-E9E0-48D4-BF2C-62DDCDA7E874', 15.00, 15.00, 1, 3) ('18EC3CD2-3065-4FF4-BE40-000004228590', '47E91479-DAC6-4485-9C66-5970EA236CB0', '2009-07-10 00:00:00.000', 'AA9237FC-E9E0-48D4-BF2C-62DDCDA7E874', 15.00, 15.00, 1, 3) ('18EC3CD2-3065-4FF4-BE40-000004228590', '5A8DED80-C7A7-477D-9E3C-5C4E147B89D3', '2009-04-08 00:00:00.000', 'AA9237FC-E9E0-48D4-BF2C-62DDCDA7E874', 15.00, 15.00, 1, 3) ('18EC3CD2-3065-4FF4-BE40-000004228590', 'CD45A6B2-1204-495C-8C4F-27DFECF689CB', '2009-05-08 00:00:00.000', 'AA9237FC-E9E0-48D4-BF2C-62DDCDA7E874', 15.00, 15.00, 1, 3) ('18EC3CD2-3065-4FF4-BE40-000004228590', 'E9C8C820-ECB7-4649-B27A-B84CD6029111', '2005-06-08 00:00:00.000', '34AE7B04-279F-476E-B040-78BA727E4370', 10.00, 12.82, 4, 3) CREATE TABLE #gifts ( contact_id NVARCHAR(255) ,bank_date DATETIME ,id UNIQUEIDENTIFIER ,event_id NVARCHAR(255) ,giftaid_status INT ,amount_exc_giftaid DECIMAL(18,3) ,amount_inc_giftaid DECIMAL(18,3) ) INSERT INTO #gifts VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-05-31 00:00:00.000','A000BBCA-2C1D-4C53-8724-0E65498A7B21','97DAEE33-260A-46ED-8A14-201924BD057D',1,10.00,12.82) ('18EC3CD2-3065-4FF4-BE40-000004228590','2002-05-31 00:00:00.000','F81A2690-C096-4BE9-8DD5-1670896323FF','F9F06373-38BC-4567-9F8A-EEB4434524BA',1,10.00,12.82) </code></pre> <p>Additional tables: --data no really important here, but these are linked to the #donation table to determine if a donation is an emergency one or not (everything in the #gift table is assumed NOT to be an emergency)</p> <pre><code>CREATE TABLE #FUND ( donation_id NVARCHAR(255) ,fund_id NVARCHAR(255) ,finance_code NVARCHAR(10) ,finance_code2 NVARCHAR(10) ) CREATE TABLE #audit ( entity_name NVARCHAR(255) ,changes NVARCHAR(5000) ) </code></pre> <p>CREATE TABLE #donation_fund (donation_id NVARCHAR(255) ,fund_id NVARCHAR(255) )</p> <p>The #fund table is used to determine if the donation in the #donation table is an emergency or not - whether or not the fund id is an emergency one is determined by data in the #audit table.</p> <p>To determine if the #donation is an emergency the following script snipped is used:</p> <pre><code>CASE WHEN #donation_fund.fund_id IN --all emergency funds ( select f.id FROM _audit a INNER JOIN _fund f ON a.article_id = f.id WHERE a.entity_name = 'Fund' AND a.Changes LIKE '%finance_code2%OldValue&gt;3%' UNION select id FROM _fund WHERE (finance_code2 LIKE '3%' OR finance_code2 LIKE '9%') AND finance_code2 IS NOT NULL ) THEN 'Emergency' else 'Non-emergency' end as type2 </code></pre> <p>Expected output (looks like this, actual figures will be different as result set below includes additional values):</p> <pre><code>contact_id donor_class payment_type donation_type count2002 sum2002 count2003 sum2003 count2004 sum2004 count2005 sum2005 count2006 sum2006 count2007 sum2007 count2008 sum2008 count2009 sum2009 count2010 sum2010 category05 category06 category07 category08 category09 category10 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted gift Non-emergency 1 10 0 0 0 0 0 0 1 10 0 0 0 0 0 0 0 0 Lapsed Donor Reactivated donor Lapsed Donor Lapsed Donor Lapsed Donor Lapsed Donor NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2002 NULL NULL NULL 2006 NULL NULL NULL NULL 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted regular Non-emergency 0 0 1 12.82 2 25.64 12 151.022 12 224.359 12 180 12 180 12 180 8 120 3yrs consecutive 4yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2003 2004 2005 2006 2007 2008 2009 2010 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted single Non-emergency 0 0 0 0 0 0 1 19.231 0 0 0 0 0 0 0 0 0 0 New donor Lapsed Donor Lapsed Donor Lapsed Donor Lapsed Donor Lapsed Donor NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2005 NULL NULL NULL NULL NULL 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted Total Non-emergency 1 10 1 12.82 2 25.64 13 170.253 13 234.359 12 180 12 180 12 180 8 120 4yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2002 2003 2004 2005 2006 2007 2008 2009 2010 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted gift Total 1 10 0 0 0 0 0 0 1 10 0 0 0 0 0 0 0 0 Lapsed Donor Reactivated donor Lapsed Donor Lapsed Donor Lapsed Donor Lapsed Donor NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2002 NULL NULL NULL 2006 NULL NULL NULL NULL 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted regular Total 0 0 1 12.82 2 25.64 12 151.022 12 224.359 12 180 12 180 12 180 8 120 3yrs consecutive 4yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2003 2004 2005 2006 2007 2008 2009 2010 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted single Total 0 0 0 0 0 0 1 19.231 0 0 0 0 0 0 0 0 0 0 New donor Lapsed Donor Lapsed Donor Lapsed Donor Lapsed Donor Lapsed Donor NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2005 NULL NULL NULL NULL NULL 18EC3CD2-3065-4FF4-BE40-000004228590 Unrestricted Total Total 1 10 1 12.82 2 25.64 13 170.253 13 234.359 12 180 12 180 12 180 8 120 4yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive 5+yrs consecutive NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2002 2003 2004 2005 2006 2007 2008 2009 2010 </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