问:
Insert into temporary table query
I'm looking to optimise a query that takes an excessive amount of time to run (around 1.5 hours), and through looking at the execution plan one of the biggest drains seems to be inserting the data from the main query into a temporary table. When I insert the data into a temporary table there is the following process :

Process (cost %)

```<code>Compute Scalar (0%) <-- Stream Aggregate (0%) <-- Sort(0%) <-- Table Spool (eager spool) (10%)
</code>```

Which makes sense, but what doesn't to me is that this appears 9 times?

I can include the query / index information if this would help, but the query itself is quite long and I don't want to make this question into an essay!

Thanks,
Davin.

Edit to include query - sorry for the length!

```IF object_id('tempdb..#category') IS NOT NULL DROP TABLE #category
CREATE TABLE #category
(
contact_id NVARCHAR(255)
,donor_class NVARCHAR(255)
,payment_type NVARCHAR(255)
,donation_type NVARCHAR(255)
,approach_channel NVARCHAR(255)
,count1987 INT
,sum1987 DECIMAL(18,3)
,count1988 INT
,sum1988 DECIMAL(18,3)
,count1989 INT
,sum1989 DECIMAL(18,3)
,count1990 INT
,sum1990 DECIMAL(18,3)
,count1991 INT
,sum1991 DECIMAL(18,3)
,count1992 INT
,sum1992 DECIMAL(18,3)
,count1993 INT
,sum1993 DECIMAL(18,3)
,count1994 INT
,sum1994 DECIMAL(18,3)
,count1995 INT
,sum1995 DECIMAL(18,3)
,count1996 INT
,sum1996 DECIMAL(18,3)
,count1997 INT
,sum1997 DECIMAL(18,3)
,count1998 INT
,sum1998 DECIMAL(18,3)
,count1999 INT
,sum1999 DECIMAL(18,3)
,count2000 INT
,sum2000 DECIMAL(18,3)
,count2001 INT
,sum2001 DECIMAL(18,3)
,count2002 INT
,sum2002 DECIMAL(18,3)
,count2003 INT
,sum2003 DECIMAL(18,3)
,count2004 INT
,sum2004 DECIMAL(18,3)
,count2005 INT
,sum2005 DECIMAL(18,3)
,count2006 INT
,sum2006 DECIMAL(18,3)
,count2007 INT
,sum2007 DECIMAL(18,3)
,count2008 INT
,sum2008 DECIMAL(18,3)
,count2009 INT
,sum2009 DECIMAL(18,3)
,count2010 INT
,sum2010 DECIMAL(18,3)
,category05 NVARCHAR(255)
,category06 NVARCHAR(255)
,category07 NVARCHAR(255)
,category08 NVARCHAR(255)
,category09 NVARCHAR(255)
,category10 NVARCHAR(255)
,[1987] NVARCHAR(4)
,[1988] NVARCHAR(4)
,[1989] NVARCHAR(4)
,[1990] NVARCHAR(4)
,[1991] NVARCHAR(4)
,[1992] NVARCHAR(4)
,[1993] NVARCHAR(4)
,[1994] NVARCHAR(4)
,[1995] NVARCHAR(4)
,[1996] NVARCHAR(4)
,[1997] NVARCHAR(4)
,[1998] NVARCHAR(4)
,[1999] NVARCHAR(4)
,[2000] NVARCHAR(4)
,[2001] NVARCHAR(4)
,[2002] NVARCHAR(4)
,[2003] NVARCHAR(4)
,[2004] NVARCHAR(4)
,[2005] NVARCHAR(4)
,[2006] NVARCHAR(4)
,[2007] NVARCHAR(4)
,[2008] NVARCHAR(4)
,[2009] NVARCHAR(4)
,[2010] NVARCHAR(4)
)
CREATE INDEX #idx_category ON #category (contact_id)

;WITH complete_finances as
(
(
SELECT
don.supporter_id AS contact_id
,don.id AS gift_id
,YEAR(don.date_received) AS calendar_year
,YEAR(don.date_received) - CASE WHEN MONTH(don.date_received) < 4 THEN 1 ELSE 0 END AS financial_year
,don.date_received AS date_received
,don.event_id AS event_id
,SUM(CASE   WHEN don.gift_aid_status <> 4 THEN don.value_gross * ((dfa.percentage) / 100)
            WHEN don.gift_aid_status = 4 AND don.value_net > don.value_gross
            AND don.value_net <> 0       THEN don.value_net  * ((dfa.percentage) / 100)
            ELSE don.value_gross  * ((dfa.percentage) / 100)
            END
        )   AS donation_value
,(CASE  WHEN don.payment_method IN (1, 2, 10, 11, 12, 200000, 200001, 200002, 200003, 200004, 200007, 200008, 200009 --single payments
                                            ,7,13,14) -- single payment refunds
                    THEN 'single'
        WHEN don.payment_method IN (3,4,5,9,200006)
                     THEN 'regular'
        ELSE 'other' END) AS type1
,CASE WHEN dfa.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>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
FROM donation don WITH (nolock)
INNER JOIN donation_fund_allocation dfa WITH (nolock) ON dfa.donation_id = don.id
WHERE don.supporter_id IS NOT NULL
AND don.status = 4 -- posted donations only
AND don.value_gross <> 0 -- to include refunds as well
GROUP BY don.supporter_id
,don.id
,don.date_received
,don.event_id
,don.payment_method
,dfa.fund_id
)
UNION ALL
(
SELECT
caw.contact_id AS contact_id
,caw.id AS gift_id
,YEAR(caw.bank_date) AS calendar_year
,YEAR(caw.bank_date) - CASE WHEN MONTH(caw.bank_date) < 4 THEN 1 ELSE 0 END AS financial_year
,caw.bank_date AS date_received
,caw.event AS event_id
,(CASE      WHEN  caw.gift_aid_status <> 4 THEN caw.amount_exc_gift_aid
            WHEN  caw.gift_aid_status = 4   THEN amount_inc_gift_aid
            END
        )   AS donation_value
,'CAW gift' AS type1
,'Non-emergency' AS type2
FROM gifts caw WITH (nolock)
WHERE caw.contact_id IS NOT NULL
AND caw.amount_exc_gift_aid <> 0
)
)
,category AS
(
SELECT
cmf.contact_id AS contact_id
,lfu.description AS donor_class
,ISNULL(cmf.type1,'Total') AS payment_type
,ISNULL(lac.description, 'Total') AS approach_channel
--,ISNULL(CASE  WHEN type1 = 'CAW gift' THEN 'CAW Gift'
--      WHEN type1 =  'Single'  THEN 'Non CAW gift'
--      WHEN type1 =  'Regular' THEN 'Non CAW gift'
--      WHEN type1 =  'Other'   THEN 'Non CAW gift'
--      END,'Total') AS payment_type2
,ISNULL(cmf.type2,'Total') AS donation_type
,SUM(CASE WHEN cmf.financial_year = '1987' THEN 1 ELSE 0 END)                   AS count1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN 1 ELSE 0 END)               AS count1988
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1988
,SUM(CASE WHEN cmf.financial_year = '1989' THEN 1 ELSE 0 END)               AS count1989
,SUM(CASE WHEN cmf.financial_year = '1989' THEN cmf.donation_value ELSE 0 END)  AS sum1989
,SUM(CASE WHEN cmf.financial_year = '1990' THEN 1 ELSE 0 END)               AS count1990
,SUM(CASE WHEN cmf.financial_year = '1990' THEN cmf.donation_value ELSE 0 END)  AS sum1990
,SUM(CASE WHEN cmf.financial_year = '1991' THEN 1 ELSE 0 END)               AS count1991
,SUM(CASE WHEN cmf.financial_year = '1991' THEN cmf.donation_value ELSE 0 END)  AS sum1991
,SUM(CASE WHEN cmf.financial_year = '1992' THEN 1 ELSE 0 END)               AS count1992
,SUM(CASE WHEN cmf.financial_year = '1992' THEN cmf.donation_value ELSE 0 END)  AS sum1992
,SUM(CASE WHEN cmf.financial_year = '1993' THEN 1 ELSE 0 END)               AS count1993
,SUM(CASE WHEN cmf.financial_year = '1993' THEN cmf.donation_value ELSE 0 END)  AS sum1993
,SUM(CASE WHEN cmf.financial_year = '1994' THEN 1 ELSE 0 END)               AS count1994
,SUM(CASE WHEN cmf.financial_year = '1994' THEN cmf.donation_value ELSE 0 END)  AS sum1994
,SUM(CASE WHEN cmf.financial_year = '1995' THEN 1 ELSE 0 END)               AS count1995
,SUM(CASE WHEN cmf.financial_year = '1995' THEN cmf.donation_value ELSE 0 END)  AS sum1995
,SUM(CASE WHEN cmf.financial_year = '1996' THEN 1 ELSE 0 END)               AS count1996
,SUM(CASE WHEN cmf.financial_year = '1996' THEN cmf.donation_value ELSE 0 END)  AS sum1996
,SUM(CASE WHEN cmf.financial_year = '1997' THEN 1 ELSE 0 END)               AS count1997
,SUM(CASE WHEN cmf.financial_year = '1997' THEN cmf.donation_value ELSE 0 END)  AS sum1997
,SUM(CASE WHEN cmf.financial_year = '1998' THEN 1 ELSE 0 END)               AS count1998
,SUM(CASE WHEN cmf.financial_year = '1998' THEN cmf.donation_value ELSE 0 END)  AS sum1998
,SUM(CASE WHEN cmf.financial_year = '1999' THEN 1 ELSE 0 END)               AS count1999
,SUM(CASE WHEN cmf.financial_year = '1999' THEN cmf.donation_value ELSE 0 END)  AS sum1999
,SUM(CASE WHEN cmf.financial_year = '2000' THEN 1 ELSE 0 END)               AS count2000
,SUM(CASE WHEN cmf.financial_year = '2000' THEN cmf.donation_value ELSE 0 END)  AS sum2000
,SUM(CASE WHEN cmf.financial_year = '2001' THEN 1 ELSE 0 END)               AS count2001
,SUM(CASE WHEN cmf.financial_year = '2001' THEN cmf.donation_value ELSE 0 END)  AS sum2001
,SUM(CASE WHEN cmf.financial_year = '2002' THEN 1 ELSE 0 END)               AS count2002
,SUM(CASE WHEN cmf.financial_year = '2002' THEN cmf.donation_value ELSE 0 END)  AS sum2002
,SUM(CASE WHEN cmf.financial_year = '2003' THEN 1 ELSE 0 END)               AS count2003
,SUM(CASE WHEN cmf.financial_year = '2003' THEN cmf.donation_value ELSE 0 END)  AS sum2003
,SUM(CASE WHEN cmf.financial_year = '2004' THEN 1 ELSE 0 END)               AS count2004
,SUM(CASE WHEN cmf.financial_year = '2004' THEN cmf.donation_value ELSE 0 END)  AS sum2004
,SUM(CASE WHEN cmf.financial_year = '2005' THEN 1 ELSE 0 END)               AS count2005
,SUM(CASE WHEN cmf.financial_year = '2005' THEN cmf.donation_value ELSE 0 END)  AS sum2005
,SUM(CASE WHEN cmf.financial_year = '2006' THEN 1 ELSE 0 END)               AS count2006
,SUM(CASE WHEN cmf.financial_year = '2006' THEN cmf.donation_value ELSE 0 END)  AS sum2006
,SUM(CASE WHEN cmf.financial_year = '2007' THEN 1 ELSE 0 END)               AS count2007
,SUM(CASE WHEN cmf.financial_year = '2007' THEN cmf.donation_value ELSE 0 END)  AS sum2007
,SUM(CASE WHEN cmf.financial_year = '2008' THEN 1 ELSE 0 END)               AS count2008
,SUM(CASE WHEN cmf.financial_year = '2008' THEN cmf.donation_value ELSE 0 END)  AS sum2008
,SUM(CASE WHEN cmf.financial_year = '2009' THEN 1 ELSE 0 END)               AS count2009
,SUM(CASE WHEN cmf.financial_year = '2009' THEN cmf.donation_value ELSE 0 END)  AS sum2009
,SUM(CASE WHEN cmf.financial_year = '2010' THEN 1 ELSE 0 END)               AS count2010
,SUM(CASE WHEN cmf.financial_year = '2010' THEN cmf.donation_value ELSE 0 END)  AS sum2010
FROM complete_finances cmf
INNER JOIN contact con WITH (nolock) ON con.id = cmf.contact_id
INNER JOIN [l_function] lfu WITH (nolock) ON lfu.code = con.class
LEFT OUTER JOIN event eve with (nolock) on eve.id = cmf.event_id
INNER JOIN [l_approach channel] lac WITH (nolock) ON lac.code = eve.class
WHERE cmf.contact_id IS NOT NULL
GROUP BY
cmf.contact_id
,cmf.type1
,cmf.type2
,lfu.description
,lac.description
WITH CUBE
)
INSERT INTO #category
SELECT
contact_id
,donor_class
,payment_type
,donation_type
,approach_channel
,count1987
,sum1987
,count1988
,sum1988
,count1989
,sum1989
,count1990
,sum1990
,count1991
,sum1991
,count1992
,sum1992
,count1993
,sum1993
,count1994
,sum1994
,count1995
,sum1995
,count1996
,sum1996
,count1997
,sum1997
,count1998
,sum1998
,count1999
,sum1999
,count2000
,sum2000
,count2001
,sum2001
,count2002
,sum2002
,count2003
,sum2003
,count2004
,sum2004
,count2005
,sum2005
,count2006
,sum2006
,count2007
,sum2007
,count2008
,sum2008
,count2009
,sum2009
,count2010
,sum2010
,CASE   WHEN count2005 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 = 0
            THEN 'New donor'
        WHEN count2005 > 0 AND count2004 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003 > 0
            THEN 'Reactivated donor'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0 AND count2001 > 0
            THEN '5+yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0
            THEN '4yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            THEN '3yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0
            THEN '2yrs consecutive'
        WHEN count2005 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'Non donor'
END AS category05
,CASE   WHEN count2006 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'New donor'
        WHEN count2006 > 0 AND count2005 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Reactivated donor'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0 AND count2002 > 0
            THEN '5+yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0
            THEN '4yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            THEN '3yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0
            THEN '2yrs consecutive'
        WHEN count2006 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'Non donor'
END AS category06
,CASE   WHEN count2007 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'New donor'
        WHEN count2007 > 0 AND count2006 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Reactivated donor'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0 AND count2003 > 0
            THEN '5+yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0
            THEN '4yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            THEN '3yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0
            THEN '2yrs consecutive'
        WHEN count2007 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'Non donor'
END AS category07
,CASE   WHEN count2008 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'New donor'
        WHEN count2008 > 0 AND count2007 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Reactivated donor'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0 AND count2004 > 0
            THEN '5+yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0
            THEN '4yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            THEN '3yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0
            THEN '2yrs consecutive'
        WHEN count2008 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'Non donor'
END AS category08
,CASE   WHEN count2009 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'New donor'
        WHEN count2009 > 0 AND count2008 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Reactivated donor'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0 AND count2005 > 0
            THEN '5+yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0
            THEN '4yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            THEN '3yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0
            THEN '2yrs consecutive'
        WHEN count2009 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'Non donor'
END AS category09
,CASE   WHEN count2010 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'New donor'
        WHEN count2010 > 0 AND count2009 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Reactivated donor'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0 AND count2006 > 0
            THEN '5+yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0
            THEN '4yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            THEN '3yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0
            THEN '2yrs consecutive'
        WHEN count2010 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009+count2010 = 0
            THEN 'Non donor'
END AS category10
,CASE WHEN count1987 > 0 THEN 1987 END AS [1987]
,CASE WHEN count1988 > 0 THEN 1988 END AS [1988]
,CASE WHEN count1989 > 0 THEN 1989 END AS [1989]
,CASE WHEN count1990 > 0 THEN 1990 END AS [1990]
,CASE WHEN count1991 > 0 THEN 1991 END AS [1991]
,CASE WHEN count1992 > 0 THEN 1992 END AS [1992]
,CASE WHEN count1993 > 0 THEN 1993 END AS [1993]
,CASE WHEN count1994 > 0 THEN 1994 END AS [1994]
,CASE WHEN count1995 > 0 THEN 1995 END AS [1995]
,CASE WHEN count1996 > 0 THEN 1996 END AS [1996]
,CASE WHEN count1997 > 0 THEN 1997 END AS [1997]
,CASE WHEN count1998 > 0 THEN 1998 END AS [1998]
,CASE WHEN count1999 > 0 THEN 1999 END AS [1999]
,CASE WHEN count2000 > 0 THEN 2000 END AS [2000]
,CASE WHEN count2001 > 0 THEN 2001 END AS [2001]
,CASE WHEN count2002 > 0 THEN 2002 END AS [2002]
,CASE WHEN count2003 > 0 THEN 2003 END AS [2003]
,CASE WHEN count2004 > 0 THEN 2004 END AS [2004]
,CASE WHEN count2005 > 0 THEN 2005 END AS [2005]
,CASE WHEN count2006 > 0 THEN 2006 END AS [2006]
,CASE WHEN count2007 > 0 THEN 2007 END AS [2007]
,CASE WHEN count2008 > 0 THEN 2008 END AS [2008]
,CASE WHEN count2009 > 0 THEN 2009 END AS [2009]
,CASE WHEN count2010 > 0 THEN 2010 END AS [2010]
FROM category
WHERE
contact_id IS NOT NULL
AND donor_class IS NOT NULL
ORDER BY contact_id
```

Title: Insert into temporary table query

Body:
I'm looking to optimise a query that takes an excessive amount of time to run (around 1.5 hours), and through looking at the execution plan one of the biggest drains seems to be inserting the data from the main query into a temporary table. When I insert the data into a temporary table there is the following process :

Process (cost %)

Compute Scalar (0%) <-- Stream Aggregate (0%) <-- Sort(0%) <-- Table Spool (eager spool) (10%)

Which makes sense, but what doesn't to me is that this appears 9 times?

I can include the query / index information if this would help, but the query itself is quite long and I don't want to make this question into an essay!

Thanks,
Davin.

Edit to include query - sorry for the length!

IF object_id('tempdb..#category') IS NOT NULL DROP TABLE #category
CREATE TABLE #category
(
contact_id NVARCHAR(255)
,donor_class NVARCHAR(255)
,payment_type NVARCHAR(255)
,donation_type NVARCHAR(255)
,approach_channel NVARCHAR(255)
,count1987 INT
,sum1987 DECIMAL(18,3)
,count1988 INT
,sum1988 DECIMAL(18,3)
,count1989 INT
,sum1989 DECIMAL(18,3)
,count1990 INT
,sum1990 DECIMAL(18,3)
,count1991 INT
,sum1991 DECIMAL(18,3)
,count1992 INT
,sum1992 DECIMAL(18,3)
,count1993 INT
,sum1993 DECIMAL(18,3)
,count1994 INT
,sum1994 DECIMAL(18,3)
,count1995 INT
,sum1995 DECIMAL(18,3)
,count1996 INT
,sum1996 DECIMAL(18,3)
,count1997 INT
,sum1997 DECIMAL(18,3)
,count1998 INT
,sum1998 DECIMAL(18,3)
,count1999 INT
,sum1999 DECIMAL(18,3)
,count2000 INT
,sum2000 DECIMAL(18,3)
,count2001 INT
,sum2001 DECIMAL(18,3)
,count2002 INT
,sum2002 DECIMAL(18,3)
,count2003 INT
,sum2003 DECIMAL(18,3)
,count2004 INT
,sum2004 DECIMAL(18,3)
,count2005 INT
,sum2005 DECIMAL(18,3)
,count2006 INT
,sum2006 DECIMAL(18,3)
,count2007 INT
,sum2007 DECIMAL(18,3)
,count2008 INT
,sum2008 DECIMAL(18,3)
,count2009 INT
,sum2009 DECIMAL(18,3)
,count2010 INT
,sum2010 DECIMAL(18,3)
,category05 NVARCHAR(255)
,category06 NVARCHAR(255)
,category07 NVARCHAR(255)
,category08 NVARCHAR(255)
,category09 NVARCHAR(255)
,category10 NVARCHAR(255)
,[1987] NVARCHAR(4)
,[1988] NVARCHAR(4)
,[1989] NVARCHAR(4)
,[1990] NVARCHAR(4)
,[1991] NVARCHAR(4)
,[1992] NVARCHAR(4)
,[1993] NVARCHAR(4)
,[1994] NVARCHAR(4)
,[1995] NVARCHAR(4)
,[1996] NVARCHAR(4)
,[1997] NVARCHAR(4)
,[1998] NVARCHAR(4)
,[1999] NVARCHAR(4)
,[2000] NVARCHAR(4)
,[2001] NVARCHAR(4)
,[2002] NVARCHAR(4)
,[2003] NVARCHAR(4)
,[2004] NVARCHAR(4)
,[2005] NVARCHAR(4)
,[2006] NVARCHAR(4)
,[2007] NVARCHAR(4)
,[2008] NVARCHAR(4)
,[2009] NVARCHAR(4)
,[2010] NVARCHAR(4)
)
CREATE INDEX #idx_category ON #category (contact_id)

;WITH complete_finances as
(
(
SELECT
don.supporter_id AS contact_id
,don.id AS gift_id
,YEAR(don.date_received) AS calendar_year
,YEAR(don.date_received) - CASE WHEN MONTH(don.date_received) < 4 THEN 1 ELSE 0 END AS financial_year
,don.date_received AS date_received
,don.event_id AS event_id
,SUM(CASE   WHEN don.gift_aid_status <> 4 THEN don.value_gross * ((dfa.percentage) / 100)
            WHEN don.gift_aid_status = 4 AND don.value_net > don.value_gross
            AND don.value_net <> 0       THEN don.value_net  * ((dfa.percentage) / 100)
            ELSE don.value_gross  * ((dfa.percentage) / 100)
            END
        )   AS donation_value
,(CASE  WHEN don.payment_method IN (1, 2, 10, 11, 12, 200000, 200001, 200002, 200003, 200004, 200007, 200008, 200009 --single payments
                                            ,7,13,14) -- single payment refunds
                    THEN 'single'
        WHEN don.payment_method IN (3,4,5,9,200006)
                     THEN 'regular'
        ELSE 'other' END) AS type1
,CASE WHEN dfa.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>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
FROM donation don WITH (nolock)
INNER JOIN donation_fund_allocation dfa WITH (nolock) ON dfa.donation_id = don.id
WHERE don.supporter_id IS NOT NULL
AND don.status = 4 -- posted donations only
AND don.value_gross <> 0 -- to include refunds as well
GROUP BY don.supporter_id
,don.id
,don.date_received
,don.event_id
,don.payment_method
,dfa.fund_id
)
UNION ALL
(
SELECT
caw.contact_id AS contact_id
,caw.id AS gift_id
,YEAR(caw.bank_date) AS calendar_year
,YEAR(caw.bank_date) - CASE WHEN MONTH(caw.bank_date) < 4 THEN 1 ELSE 0 END AS financial_year
,caw.bank_date AS date_received
,caw.event AS event_id
,(CASE      WHEN  caw.gift_aid_status <> 4 THEN caw.amount_exc_gift_aid
            WHEN  caw.gift_aid_status = 4   THEN amount_inc_gift_aid
            END
        )   AS donation_value
,'CAW gift' AS type1
,'Non-emergency' AS type2
FROM gifts caw WITH (nolock)
WHERE caw.contact_id IS NOT NULL
AND caw.amount_exc_gift_aid <> 0
)
)
,category AS
(
SELECT
cmf.contact_id AS contact_id
,lfu.description AS donor_class
,ISNULL(cmf.type1,'Total') AS payment_type
,ISNULL(lac.description, 'Total') AS approach_channel
--,ISNULL(CASE  WHEN type1 = 'CAW gift' THEN 'CAW Gift'
--      WHEN type1 =  'Single'  THEN 'Non CAW gift'
--      WHEN type1 =  'Regular' THEN 'Non CAW gift'
--      WHEN type1 =  'Other'   THEN 'Non CAW gift'
--      END,'Total') AS payment_type2
,ISNULL(cmf.type2,'Total') AS donation_type
,SUM(CASE WHEN cmf.financial_year = '1987' THEN 1 ELSE 0 END)                   AS count1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN 1 ELSE 0 END)               AS count1988
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1988
,SUM(CASE WHEN cmf.financial_year = '1989' THEN 1 ELSE 0 END)               AS count1989
,SUM(CASE WHEN cmf.financial_year = '1989' THEN cmf.donation_value ELSE 0 END)  AS sum1989
,SUM(CASE WHEN cmf.financial_year = '1990' THEN 1 ELSE 0 END)               AS count1990
,SUM(CASE WHEN cmf.financial_year = '1990' THEN cmf.donation_value ELSE 0 END)  AS sum1990
,SUM(CASE WHEN cmf.financial_year = '1991' THEN 1 ELSE 0 END)               AS count1991
,SUM(CASE WHEN cmf.financial_year = '1991' THEN cmf.donation_value ELSE 0 END)  AS sum1991
,SUM(CASE WHEN cmf.financial_year = '1992' THEN 1 ELSE 0 END)               AS count1992
,SUM(CASE WHEN cmf.financial_year = '1992' THEN cmf.donation_value ELSE 0 END)  AS sum1992
,SUM(CASE WHEN cmf.financial_year = '1993' THEN 1 ELSE 0 END)               AS count1993
,SUM(CASE WHEN cmf.financial_year = '1993' THEN cmf.donation_value ELSE 0 END)  AS sum1993
,SUM(CASE WHEN cmf.financial_year = '1994' THEN 1 ELSE 0 END)               AS count1994
,SUM(CASE WHEN cmf.financial_year = '1994' THEN cmf.donation_value ELSE 0 END)  AS sum1994
,SUM(CASE WHEN cmf.financial_year = '1995' THEN 1 ELSE 0 END)               AS count1995
,SUM(CASE WHEN cmf.financial_year = '1995' THEN cmf.donation_value ELSE 0 END)  AS sum1995
,SUM(CASE WHEN cmf.financial_year = '1996' THEN 1 ELSE 0 END)               AS count1996
,SUM(CASE WHEN cmf.financial_year = '1996' THEN cmf.donation_value ELSE 0 END)  AS sum1996
,SUM(CASE WHEN cmf.financial_year = '1997' THEN 1 ELSE 0 END)               AS count1997
,SUM(CASE WHEN cmf.financial_year = '1997' THEN cmf.donation_value ELSE 0 END)  AS sum1997
,SUM(CASE WHEN cmf.financial_year = '1998' THEN 1 ELSE 0 END)               AS count1998
,SUM(CASE WHEN cmf.financial_year = '1998' THEN cmf.donation_value ELSE 0 END)  AS sum1998
,SUM(CASE WHEN cmf.financial_year = '1999' THEN 1 ELSE 0 END)               AS count1999
,SUM(CASE WHEN cmf.financial_year = '1999' THEN cmf.donation_value ELSE 0 END)  AS sum1999
,SUM(CASE WHEN cmf.financial_year = '2000' THEN 1 ELSE 0 END)               AS count2000
,SUM(CASE WHEN cmf.financial_year = '2000' THEN cmf.donation_value ELSE 0 END)  AS sum2000
,SUM(CASE WHEN cmf.financial_year = '2001' THEN 1 ELSE 0 END)               AS count2001
,SUM(CASE WHEN cmf.financial_year = '2001' THEN cmf.donation_value ELSE 0 END)  AS sum2001
,SUM(CASE WHEN cmf.financial_year = '2002' THEN 1 ELSE 0 END)               AS count2002
,SUM(CASE WHEN cmf.financial_year = '2002' THEN cmf.donation_value ELSE 0 END)  AS sum2002
,SUM(CASE WHEN cmf.financial_year = '2003' THEN 1 ELSE 0 END)               AS count2003
,SUM(CASE WHEN cmf.financial_year = '2003' THEN cmf.donation_value ELSE 0 END)  AS sum2003
,SUM(CASE WHEN cmf.financial_year = '2004' THEN 1 ELSE 0 END)               AS count2004
,SUM(CASE WHEN cmf.financial_year = '2004' THEN cmf.donation_value ELSE 0 END)  AS sum2004
,SUM(CASE WHEN cmf.financial_year = '2005' THEN 1 ELSE 0 END)               AS count2005
,SUM(CASE WHEN cmf.financial_year = '2005' THEN cmf.donation_value ELSE 0 END)  AS sum2005
,SUM(CASE WHEN cmf.financial_year = '2006' THEN 1 ELSE 0 END)               AS count2006
,SUM(CASE WHEN cmf.financial_year = '2006' THEN cmf.donation_value ELSE 0 END)  AS sum2006
,SUM(CASE WHEN cmf.financial_year = '2007' THEN 1 ELSE 0 END)               AS count2007
,SUM(CASE WHEN cmf.financial_year = '2007' THEN cmf.donation_value ELSE 0 END)  AS sum2007
,SUM(CASE WHEN cmf.financial_year = '2008' THEN 1 ELSE 0 END)               AS count2008
,SUM(CASE WHEN cmf.financial_year = '2008' THEN cmf.donation_value ELSE 0 END)  AS sum2008
,SUM(CASE WHEN cmf.financial_year = '2009' THEN 1 ELSE 0 END)               AS count2009
,SUM(CASE WHEN cmf.financial_year = '2009' THEN cmf.donation_value ELSE 0 END)  AS sum2009
,SUM(CASE WHEN cmf.financial_year = '2010' THEN 1 ELSE 0 END)               AS count2010
,SUM(CASE WHEN cmf.financial_year = '2010' THEN cmf.donation_value ELSE 0 END)  AS sum2010
FROM complete_finances cmf
INNER JOIN contact con WITH (nolock) ON con.id = cmf.contact_id
INNER JOIN [l_function] lfu WITH (nolock) ON lfu.code = con.class
LEFT OUTER JOIN event eve with (nolock) on eve.id = cmf.event_id
INNER JOIN [l_approach channel] lac WITH (nolock) ON lac.code = eve.class
WHERE cmf.contact_id IS NOT NULL
GROUP BY
cmf.contact_id
,cmf.type1
,cmf.type2
,lfu.description
,lac.description
WITH CUBE
)
INSERT INTO #category
SELECT
contact_id
,donor_class
,payment_type
,donation_type
,approach_channel
,count1987
,sum1987
,count1988
,sum1988
,count1989
,sum1989
,count1990
,sum1990
,count1991
,sum1991
,count1992
,sum1992
,count1993
,sum1993
,count1994
,sum1994
,count1995
,sum1995
,count1996
,sum1996
,count1997
,sum1997
,count1998
,sum1998
,count1999
,sum1999
,count2000
,sum2000
,count2001
,sum2001
,count2002
,sum2002
,count2003
,sum2003
,count2004
,sum2004
,count2005
,sum2005
,count2006
,sum2006
,count2007
,sum2007
,count2008
,sum2008
,count2009
,sum2009
,count2010
,sum2010
,CASE   WHEN count2005 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 = 0
            THEN 'New donor'
        WHEN count2005 > 0 AND count2004 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003 > 0
            THEN 'Reactivated donor'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0 AND count2001 > 0
            THEN '5+yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0
            THEN '4yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            THEN '3yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0
            THEN '2yrs consecutive'
        WHEN count2005 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'Non donor'
END AS category05
,CASE   WHEN count2006 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'New donor'
        WHEN count2006 > 0 AND count2005 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Reactivated donor'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0 AND count2002 > 0
            THEN '5+yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0
            THEN '4yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            THEN '3yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0
            THEN '2yrs consecutive'
        WHEN count2006 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'Non donor'
END AS category06
,CASE   WHEN count2007 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'New donor'
        WHEN count2007 > 0 AND count2006 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Reactivated donor'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0 AND count2003 > 0
            THEN '5+yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0
            THEN '4yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            THEN '3yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0
            THEN '2yrs consecutive'
        WHEN count2007 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'Non donor'
END AS category07
,CASE   WHEN count2008 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'New donor'
        WHEN count2008 > 0 AND count2007 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Reactivated donor'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0 AND count2004 > 0
            THEN '5+yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0
            THEN '4yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            THEN '3yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0
            THEN '2yrs consecutive'
        WHEN count2008 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'Non donor'
END AS category08
,CASE   WHEN count2009 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'New donor'
        WHEN count2009 > 0 AND count2008 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Reactivated donor'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0 AND count2005 > 0
            THEN '5+yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0
            THEN '4yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            THEN '3yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0
            THEN '2yrs consecutive'
        WHEN count2009 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'Non donor'
END AS category09
,CASE   WHEN count2010 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'New donor'
        WHEN count2010 > 0 AND count2009 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Reactivated donor'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0 AND count2006 > 0
            THEN '5+yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0
            THEN '4yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            THEN '3yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0
            THEN '2yrs consecutive'
        WHEN count2010 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009+count2010 = 0
            THEN 'Non donor'
END AS category10
,CASE WHEN count1987 > 0 THEN 1987 END AS [1987]
,CASE WHEN count1988 > 0 THEN 1988 END AS [1988]
,CASE WHEN count1989 > 0 THEN 1989 END AS [1989]
,CASE WHEN count1990 > 0 THEN 1990 END AS [1990]
,CASE WHEN count1991 > 0 THEN 1991 END AS [1991]
,CASE WHEN count1992 > 0 THEN 1992 END AS [1992]
,CASE WHEN count1993 > 0 THEN 1993 END AS [1993]
,CASE WHEN count1994 > 0 THEN 1994 END AS [1994]
,CASE WHEN count1995 > 0 THEN 1995 END AS [1995]
,CASE WHEN count1996 > 0 THEN 1996 END AS [1996]
,CASE WHEN count1997 > 0 THEN 1997 END AS [1997]
,CASE WHEN count1998 > 0 THEN 1998 END AS [1998]
,CASE WHEN count1999 > 0 THEN 1999 END AS [1999]
,CASE WHEN count2000 > 0 THEN 2000 END AS [2000]
,CASE WHEN count2001 > 0 THEN 2001 END AS [2001]
,CASE WHEN count2002 > 0 THEN 2002 END AS [2002]
,CASE WHEN count2003 > 0 THEN 2003 END AS [2003]
,CASE WHEN count2004 > 0 THEN 2004 END AS [2004]
,CASE WHEN count2005 > 0 THEN 2005 END AS [2005]
,CASE WHEN count2006 > 0 THEN 2006 END AS [2006]
,CASE WHEN count2007 > 0 THEN 2007 END AS [2007]
,CASE WHEN count2008 > 0 THEN 2008 END AS [2008]
,CASE WHEN count2009 > 0 THEN 2009 END AS [2009]
,CASE WHEN count2010 > 0 THEN 2010 END AS [2010]
FROM category
WHERE
contact_id IS NOT NULL
AND donor_class IS NOT NULL
ORDER BY contact_id

Body:
sample data as no room in question!

Main table information:

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)

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)

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)
)

CREATE TABLE #donation_fund
(donation_id NVARCHAR(255)
,fund_id NVARCHAR(255)
)

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.

To determine if the #donation is an emergency the following script snipped is used:

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>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

Expected output (looks like this, actual figures will be different as result set below includes additional values):

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


来源: stackexchange

问:
Why does this sort in Java not work?
I'm trying to implement a short sorting algorithm, but it simply doesn't work. Anyone with more experience in programming got any tips? The array is not sorted, several values are coming up multiple times in the end print. Why?</p>

```<code>public static void main(String[] args) {
    // Sort the data
    int[] array = {1,5,2,3,6,2,29,-2,23,3};

    for (int i = 0; i<array.length-1; i++) {

        if (array[i] > array[i+1]) {
            array[i+1] = array[i];
            array[i] = array[i+1];
        }
    }

    // Print the data.
    for (int i = 0; i<array.length; i++) {
        System.out.print(i+ ", ");
    }
}
</code>```

答:
Several points. See if below helps :

There should be few compilation problems - semi colon missing, trying length() on array than length.
2. In the end, you simply print i from 0 to array length. I suppose you want to print array[i].
 3. There are multiple sorting algorithm which you can find by googling.
4. If you choose bubble sort, try with two loops, one outer till length of array and one inner, till (array length-elements already sorted). And inside you compare using temp variable.

```<code>            temp = array[j];
            array[j] = array[j+1];
            array[j+1] = temp;
</code>```

ContentLicense: CC BY-SA 3.0

Title: Why does this sort in Java not work?

Body:
I'm trying to implement a short sorting algorithm, but it simply doesn't work. Anyone with more experience in programming got any tips? The array is not sorted, several values are coming up multiple times in the end print. Why?

public static void main(String[] args) {
    // Sort the data
    int[] array = {1,5,2,3,6,2,29,-2,23,3};

    for (int i = 0; i<array.length-1; i++) {

        if (array[i] > array[i+1]) {
            array[i+1] = array[i];
            array[i] = array[i+1];
        }
    }

    // Print the data.
    for (int i = 0; i<array.length; i++) {
        System.out.print(i+ ", ");
    }
}

Body:
Several points. See if below helps :

There should be few compilation problems - semi colon missing, trying length() on array than length.
2. In the end, you simply print i from 0 to array length. I suppose you want to print array[i].
 3. There are multiple sorting algorithm which you can find by googling.
4. If you choose bubble sort, try with two loops, one outer till length of array and one inner, till (array length-elements already sorted). And inside you compare using temp variable.

            temp = array[j];
            array[j] = array[j+1];
            array[j+1] = temp;


问:
PSR-4 autoloading trouble
I want to load HomeController class from lib directory:

```<code>root/
-lib/
--/HomeController.php
-vendor/
-composer.json
-index.php
</code>```

Composer.json

```<code>"autoload": {
    "psr-4": {
        "Lib\\": "lib/"
    }
}
</code>```

HomeController.php

```<code>namespace Lib;
class HomeController {}
</code>```

index.php

```<code>var_damp(new \Lib\HomeController.php);
</code>```

It doesn't find the class.

But if I put HomeController.php inside Controllers directory:

```<code>root/
-lib/
--/Controllers/HomeController.php
</code>```

And update the namespaces: index.php to var_damp(new \Lib\Controllers\HomeController.php); and HomeController.php to:

```<code>namespace Lib\Controllers;
class HomeController {}
</code>```

It works perfect.

It's weird, I can't find any docs talking about it. I don't need additional directories, in this case I want the HomeController class directly inside lib directory.

How can I make it works inside lib folder?

答:
I think the trailing slash in the path reference is your problem. Change the autoload section in composer.json to this:

```<code>"autoload": {
  "psr-4": {
    "Lib\\": "lib"
  }
}
</code>```

...then run composer dump-autoload.

Title: PSR-4 autoloading trouble

Body:
I want to load HomeController class from lib directory:

root/
-lib/
--/HomeController.php
-vendor/
-composer.json
-index.php

Composer.json

"autoload": {
    "psr-4": {
        "Lib\\": "lib/"
    }
}

HomeController.php

namespace Lib;
class HomeController {}

index.php

var_damp(new \Lib\HomeController.php);

It doesn't find the class.

But if I put HomeController.php inside Controllers directory:

root/
-lib/
--/Controllers/HomeController.php

And update the namespaces: index.php to var_damp(new \Lib\Controllers\HomeController.php); and HomeController.php to:

namespace Lib\Controllers;
class HomeController {}

It works perfect.

It's weird, I can't find any docs talking about it. I don't need additional directories, in this case I want the HomeController class directly inside lib directory.

How can I make it works inside lib folder?

Body:
I think the trailing slash in the path reference is your problem. Change the autoload section in composer.json to this:

"autoload": {
  "psr-4": {
    "Lib\\": "lib"
  }
}

...then run composer dump-autoload.


问:
How to achieve this workflow on git / github?
I have 2 computers. PC1 has some code that I uploaded to github following this tutorial http://kbroman.org/github_tutorial/pages/init.html

```<code>git init
git add
git commit
git remote add origin <url>
git push -u origin master
</code>```

Now I have another PC that I will use to work on the same project and I am stuck trying to link them to the same repository. fetching or pulling however seems to do nothing. My folder (local repository) is still empty. This is what I have done so far

```<code>git init
git remote add origin <same url>
git pull
</code>```

I was wondering if anyone can help me with a few simple lines for this. I am new to git and the things i google online seem highly complicated.

答:
You have to do:

```<code>git pull origin master
</code>```

As you did not setup master as your upstream branch.

Alternatively you can update the GIT repository using git-fetch and --track origin/master, as:

```<code> git init
 git remote add origin <same url>
 git fetch -all
 git checkout --track origin/master
</code>```

Following which you can simply run git pull to update local repo from remote.

Body:
I have 2 computers. PC1 has some code that I uploaded to github following this tutorial http://kbroman.org/github_tutorial/pages/init.html

git init
git add
git commit
git remote add origin <url>
git push -u origin master

Now I have another PC that I will use to work on the same project and I am stuck trying to link them to the same repository. fetching or pulling however seems to do nothing. My folder (local repository) is still empty. This is what I have done so far

git init
git remote add origin <same url>
git pull

I was wondering if anyone can help me with a few simple lines for this. I am new to git and the things i google online seem highly complicated.

Body:
You have to do:

git pull origin master

As you did not setup master as your upstream branch.

Alternatively you can update the GIT repository using git-fetch and --track origin/master, as:

 git init
 git remote add origin <same url>
 git fetch -all
 git checkout --track origin/master

Following which you can simply run git pull to update local repo from remote.


问:
iOS - Map a root JSON array with ObjectMapper in swift
I use the library ObjectMapper to map json with my objects but I have some issues to map a root json Array.

This is the received json :

```<code>[
   {
       CustomerId = "A000015",
       ...
   },
   {
       CustomerId = "A000016",
       ...
   },
   {
       CustomerId = "A000017",
       ...
   }
]
</code>```

This is my object

```<code>class Customer : Mappable
{
    var CustomerId : String? = nil

    class func newInstance(map: Map) -> Mappable? {
        return Customer()
    }

    func mapping(map: Map) {
        CustomerId   <- map["CustomerId"]
    }
}
</code>```

I map the json in my controller with

```<code>let json = NSJSONSerialization.JSONObjectWithData(data, options: NSJSONReadingOptions.MutableContainers, error: &error) as! NSArray

if (error != nil) {
    return completionHandler(nil, error)
} else {
    var customers = Mapper<Customer>().map(json)
}
</code>```

But it doesn't work, I tried Mapper<[Customer]>().map(json) but it doesn't work too.
Finally I tried to create a new swift object CustomerList containing a Customer array but it doesn't work.

Do you have an idea of how to map json of a root array ?

Thanks.

答:
The easiest solution is provided by AlamofireObjectMapper. Use the responseArray() convenience method:

```<code>Alamofire.request(endpoint).responseArray { (response: DataResponse<[MyMappableClass]>) in

            if let result = response.result.value {
                // Customer array is here
            } else if let error = response.result.error {
                // Handle error
            } else {
                // Handle some other not networking error
            }
        }
</code>```

Body:
I use the library ObjectMapper to map json with my objects but I have some issues to map a root json Array.

This is the received json :

[
   {
       CustomerId = "A000015",
       ...
   },
   {
       CustomerId = "A000016",
       ...
   },
   {
       CustomerId = "A000017",
       ...
   }
]

This is my object

class Customer : Mappable
{
    var CustomerId : String? = nil

    class func newInstance(map: Map) -> Mappable? {
        return Customer()
    }

    func mapping(map: Map) {
        CustomerId   <- map["CustomerId"]
    }
}

I map the json in my controller with

let json = NSJSONSerialization.JSONObjectWithData(data, options: NSJSONReadingOptions.MutableContainers, error: &error) as! NSArray

if (error != nil) {
    return completionHandler(nil, error)
} else {
    var customers = Mapper<Customer>().map(json)
}

But it doesn't work, I tried Mapper<[Customer]>().map(json) but it doesn't work too.
Finally I tried to create a new swift object CustomerList containing a Customer array but it doesn't work.

Do you have an idea of how to map json of a root array ?

Thanks.

Body:
The easiest solution is provided by AlamofireObjectMapper. Use the responseArray() convenience method:

Alamofire.request(endpoint).responseArray { (response: DataResponse<[MyMappableClass]>) in

            if let result = response.result.value {
                // Customer array is here
            } else if let error = response.result.error {
                // Handle error
            } else {
                // Handle some other not networking error
            }
        }


问:
android google maps app running on galaxy s, but not on galaxy tab - getting grey tiles
i have developed an android application using google maps. when i deploy the application to a samsung galaxy s handset (runnning on Android 2.1) and also on other handsets running on Android 2.1 everything works fine.
when i change my project to support Android 2.2 i can still deploy the application to the galaxy tablet  (running on Android 2.2). all functionality works there like on the galaxy s (Android 2.1) except the maps integration. when i open the maps on the android 2.2 device i only get grey tiles.

thanks in advance for your help.

br,
martin

答: This is usually due to wrong Maps API key. Are you exporting as debug to one and release to the other?</p>

ContentLicense: CC BY-SA 2.5


问:
Foundation email template wrapper background
I have a basic email template constructed using Foundation email template with Inky: http://foundation.zurb.com/emails/docs/wrapper.html

I have a gray background color, and it looks fine when it's in full viewport. However, when I resize it down to a certain viewport, the gray background disappears and it looks odd:

Now only the header has the gray background. I want the gray background to stay consistent regardless of what the viewport is. I've tried to wrap it using a <wrapper> as discussed in the Inky documentation, but it doesn't work.

```<code><wrapper bgcolor="#f3f3f3">
    <container>
        <row>
            //stuff here
        </row>
    </container>
</wrapper>
</code>```

I want it to essentially look like this template where the gray background is visible still.

答: Have you tried changing the $body-background SCSS variable in your settings.scss? You can see there reference here.

Body:
I have a basic email template constructed using Foundation email template with Inky: http://foundation.zurb.com/emails/docs/wrapper.html

I have a gray background color, and it looks fine when it's in full viewport. However, when I resize it down to a certain viewport, the gray background disappears and it looks odd:

Now only the header has the gray background. I want the gray background to stay consistent regardless of what the viewport is. I've tried to wrap it using a <wrapper> as discussed in the Inky documentation, but it doesn't work.

<wrapper bgcolor="#f3f3f3">
    <container>
        <row>
            //stuff here
        </row>
    </container>
</wrapper>

I want it to essentially look like this template where the gray background is visible still.


问:
how can I convert a string to ip address in python
how can i convert a string ip address to a decimal number. e.g I have a data bytes= b'363,3,1778952384,7076' , here 1778952384 is my ip address and 7076 is my port. How can I convert my IP address to decimal number. below is my code, please help me to solve these problem

/app.py

```<code>import socket
from tornado.tcpclient import TCPClient
from tornado import gen

@gen.coroutine
def f(self, message):

    global stream
    client = TCPClient()
    stream = yield client.connect('192.168.8.108', 2620, max_buffer_size=int(1e9))
    msg = '192.168.8.101, 8000'.encode('utf-8')
    yield stream.write(msg)
    data = yield stream.read_bytes(21)
    print("bytes=",data) #bytes= b'363,3,1778952384,7076
</code>```

答:
Python has an ipaddress module which can handle this sort of thing. It was added to the standard library in Python 3, but should be installable for earlier versions, too.

```<code>>>> import ipaddress
>>> print(ipaddress.ip_address(1778952384))
IPv4Address('106.8.168.192')
>>> print(str(ipaddress.ip_address(1778952384)))
'106.8.168.192'
</code>```

You may also be asking about splitting the string (on b',' since it's a bytestring), getting the right field, and converting it to an int:

```<code>data = b'363,3,1778952384,7076'
ipaddress.ip_address(int(data.split(b',')[2]))
</code>```

Body:
how can i convert a string ip address to a decimal number. e.g I have a data bytes= b'363,3,1778952384,7076' , here 1778952384 is my ip address and 7076 is my port. How can I convert my IP address to decimal number. below is my code, please help me to solve these problem

/app.py

import socket
from tornado.tcpclient import TCPClient
from tornado import gen

@gen.coroutine
def f(self, message):

    global stream
    client = TCPClient()
    stream = yield client.connect('192.168.8.108', 2620, max_buffer_size=int(1e9))
    msg = '192.168.8.101, 8000'.encode('utf-8')
    yield stream.write(msg)
    data = yield stream.read_bytes(21)
    print("bytes=",data) #bytes= b'363,3,1778952384,7076

Body:
Python has an ipaddress module which can handle this sort of thing. It was added to the standard library in Python 3, but should be installable for earlier versions, too.

>>> import ipaddress
>>> print(ipaddress.ip_address(1778952384))
IPv4Address('106.8.168.192')
>>> print(str(ipaddress.ip_address(1778952384)))
'106.8.168.192'

You may also be asking about splitting the string (on b',' since it's a bytestring), getting the right field, and converting it to an int:

data = b'363,3,1778952384,7076'
ipaddress.ip_address(int(data.split(b',')[2]))


问:
Boolean != false
In Java, you would usually say that

if(someBool != false)

is the same as

if(someBool)

But what if someBool is not of type boolean but  Boolean, and its value is null?

答:
If you want to handle Boolean instances as well as primitives and be null-safe, you can use this:

```<code>if(Boolean.TRUE.equals(someBool))
</code>```

Title: Boolean != false

Body:
If you want to handle Boolean instances as well as primitives and be null-safe, you can use this:

if(Boolean.TRUE.equals(someBool))


问:
Linux - unzip file in place
I need to download VM image provided in zip. Zip is 10 gb, unpacked VM image around 16gb and I have around 20gb space on disk. Is it possible to unzip downloaded file "in-place"?</p>

答:
See here.

The index of a ZIP file is at the end of the file. This means, you have to download everything, before you can start doing anything with the ZIP file. Even with other tools like bsdtar you have to store everything of the ZIP file somewhere.

All you can do is writing the download into a RAM disk. ;-)

Title: Linux - unzip file in place


问:
I can`t connect to database on my server it displays blank page
```<code><!Doctype html>
<html>
<head>
<title>Orders Database</title>
</head>
<body>
<?php
$con = mysql_connect('localhost','wrobell1_1','123');
$db = mysql_select_db('wrobell1_orders',$con);

if($con){
echo 'succefully connected'}
?>
</body>
</html>
</code>```

<p>What can be wrong? I put it on the server in public html folder and nothing displays </p>

答:
You have syntax error in code.</p>

```<code><!Doctype html>
<html>
<head>
<title>Orders Database</title>
</head>
<body>
<?php
$con = mysql_connect('localhost','wrobell1_1','123');
$db = mysql_select_db('wrobell1_orders',$con);

if($con){
  echo 'succefully connected';
}
?>
</body>
</html>
</code>```

Body:
<!Doctype html>

<title>Orders Database</title>

<?php
$con = mysql_connect('localhost','wrobell1_1','123');
$db = mysql_select_db('wrobell1_orders',$con);

if($con){
echo 'succefully connected'}
?>

What can be wrong? I put it on the server in public html folder and nothing displays

Body:
You have syntax error in code.

<!Doctype html>
<html>
<head>
<title>Orders Database</title>
</head>
<body>
<?php
$con = mysql_connect('localhost','wrobell1_1','123');
$db = mysql_select_db('wrobell1_orders',$con);

if($con){
  echo 'succefully connected';
}
?>
</body>
</html>


问:
Best Solution For the Train Composition
Recently I answered My Online Interview test Wherein I was asked Following question

A TrainComposition is built by attaching and detaching wagons from the left and the right sides.

For example, if we start by attaching wagon 7 from the left followed by attaching wagon 13, again from the left, we get a composition of two wagons (13 and 7 from left to right). Now the first wagon that can be detached from the right is 7 and the first that can be detached from the left is 13. Implement a TrainComposition that models this problem.

I used doubly linked list to solve the issue

```<code>class Node

{
    protected int data;
    protected Node next, prev;
    /* Constructor */
    public Node()
    {
        next = null;
        prev = null;
        data = 0;
    }

    /* Constructor */

    public Node(int d, Node n, Node p)
    {
        data = d;
        next = n;
        prev = p;
    }

    /* Function to set link to next node */
    public void setLinkNext(Node n) {
        next = n;
    }
    /* Function to set link to previous node */
    public void setLinkPrev(Node p) {
        prev = p;
    }

    /* Funtion to get link to next node */

    public Node getLinkNext() {
        return next;
    }

    /* Function to get link to previous node */

    public Node getLinkPrev() {
        return prev;
    }

    /* Function to set data to node */
    public void setData(int d) {
        data = d;
    }

    /* Function to get data from node */

    public int getData() {
        return data;
    }
}

public class SortedSearch {
    protected Node start;
    protected Node end;
    public int size;
    /* Constructor */
    public SortedSearch()
    {
        start = null;
        end = null;
        size = 0;
    }
    public boolean isEmpty()
    {
        return start == null;
    }

    public int getSize()
    {
        return size;
    }

    public void attachWagonFromLeft(int wagonId) {
        Node nptr = new Node(wagonId, null, null);
        if (start == null)
        {
            start = nptr;
            end = start;
        }
        else
        {
            start.setLinkPrev(nptr);
            nptr.setLinkNext(start);
            start = nptr;
        }
        size++;
    }
    public void attachWagonFromRight(int wagonId) {
        Node nptr = new Node(wagonId, null, null);
        if (start == null)
        {
            start = nptr;
            end = start;
        }
        else
        {
            nptr.setLinkPrev(end);
            end.setLinkNext(nptr);
            end = nptr;
        }
        size++;
    }
    public int detachWagonFromLeft() {
        int value=0;
        if (size == 1)
        {
            value = start.getData();
            start = null;
            end = null;
            size = 0;
            return value;

        }
        value = start.getData();
        start = start.getLinkNext();
        start.setLinkPrev(null);
        size--;
        return value;
    }

    public int detachWagonFromRight() {
        int value=0;
            value = end.getData();
            end = end.getLinkPrev();
            end.setLinkNext(null);
            size-- ;
            return value;
    }

    public static void main(String[] args) {
        SortedSearch tree = new SortedSearch();
        tree.attachWagonFromLeft(7);
        tree.attachWagonFromLeft(13);
        tree.attachWagonFromLeft(12);
        tree.attachWagonFromLeft(10);
        tree.attachWagonFromLeft(6);
        tree.attachWagonFromLeft(4);
        tree.attachWagonFromLeft(3);
        tree.attachWagonFromLeft(2);
        System.out.println(tree.detachWagonFromRight()); // 7
        System.out.println(tree.detachWagonFromRight()); // 13
        System.out.println(tree.detachWagonFromRight()); // 7
        System.out.println(tree.detachWagonFromRight()); // 13
        System.out.println(tree.detachWagonFromRight()); // 7
        System.out.println(tree.detachWagonFromRight()); // 13

    }
}
</code>```

And tested it accordingly. But when submitted it said failed internal test cases, and the answer was marked wrong.
Can you please tell which is the best solution for this problem.

答:
Looks a bit "long", your solution. I would define an object, called "train" which has a object.value=number_of_wagons (a list). Then define 2 methods:

train.attach(site,number): using the append command or the insert command for attaching the new number from the desired side

train.detach(site): deleting the element of the list and maybe printing you the number.

However, for my understanding (also the description of the problem is not very detailed, so i dont know what should be the expected answer) you can only attach wagons from one side as the engine is on the other) ;)

Title: Best Solution For the Train Composition

Body:
Recently I answered My Online Interview test Wherein I was asked Following question

A TrainComposition is built by attaching and detaching wagons from the left and the right sides.

For example, if we start by attaching wagon 7 from the left followed by attaching wagon 13, again from the left, we get a composition of two wagons (13 and 7 from left to right). Now the first wagon that can be detached from the right is 7 and the first that can be detached from the left is 13. Implement a TrainComposition that models this problem.

I used doubly linked list to solve the issue

class Node

{
    protected int data;
    protected Node next, prev;
    /* Constructor */
    public Node()
    {
        next = null;
        prev = null;
        data = 0;
    }

    /* Constructor */

    public Node(int d, Node n, Node p)
    {
        data = d;
        next = n;
        prev = p;
    }

    /* Function to set link to next node */
    public void setLinkNext(Node n) {
        next = n;
    }
    /* Function to set link to previous node */
    public void setLinkPrev(Node p) {
        prev = p;
    }

    /* Funtion to get link to next node */

    public Node getLinkNext() {
        return next;
    }

    /* Function to get link to previous node */

    public Node getLinkPrev() {
        return prev;
    }

    /* Function to set data to node */
    public void setData(int d) {
        data = d;
    }

    /* Function to get data from node */

    public int getData() {
        return data;
    }
}

public class SortedSearch {
    protected Node start;
    protected Node end;
    public int size;
    /* Constructor */
    public SortedSearch()
    {
        start = null;
        end = null;
        size = 0;
    }
    public boolean isEmpty()
    {
        return start == null;
    }

    public int getSize()
    {
        return size;
    }

    public void attachWagonFromLeft(int wagonId) {
        Node nptr = new Node(wagonId, null, null);
        if (start == null)
        {
            start = nptr;
            end = start;
        }
        else
        {
            start.setLinkPrev(nptr);
            nptr.setLinkNext(start);
            start = nptr;
        }
        size++;
    }
    public void attachWagonFromRight(int wagonId) {
        Node nptr = new Node(wagonId, null, null);
        if (start == null)
        {
            start = nptr;
            end = start;
        }
        else
        {
            nptr.setLinkPrev(end);
            end.setLinkNext(nptr);
            end = nptr;
        }
        size++;
    }
    public int detachWagonFromLeft() {
        int value=0;
        if (size == 1)
        {
            value = start.getData();
            start = null;
            end = null;
            size = 0;
            return value;

        }
        value = start.getData();
        start = start.getLinkNext();
        start.setLinkPrev(null);
        size--;
        return value;
    }

    public int detachWagonFromRight() {
        int value=0;
            value = end.getData();
            end = end.getLinkPrev();
            end.setLinkNext(null);
            size-- ;
            return value;
    }

    public static void main(String[] args) {
        SortedSearch tree = new SortedSearch();
        tree.attachWagonFromLeft(7);
        tree.attachWagonFromLeft(13);
        tree.attachWagonFromLeft(12);
        tree.attachWagonFromLeft(10);
        tree.attachWagonFromLeft(6);
        tree.attachWagonFromLeft(4);
        tree.attachWagonFromLeft(3);
        tree.attachWagonFromLeft(2);
        System.out.println(tree.detachWagonFromRight()); // 7
        System.out.println(tree.detachWagonFromRight()); // 13
        System.out.println(tree.detachWagonFromRight()); // 7
        System.out.println(tree.detachWagonFromRight()); // 13
        System.out.println(tree.detachWagonFromRight()); // 7
        System.out.println(tree.detachWagonFromRight()); // 13

    }
}

And tested it accordingly. But when submitted it said failed internal test cases, and the answer was marked wrong.
Can you please tell which is the best solution for this problem.


问:
Google Calendar API android create event
I`m trying create a event with file a (.pdf) in android App with Google Calendar API: Create Events

```<code>public static void addAttachment(Calendar calendarService, Drive driveService, String calendarId,
    String eventId, String fileId) throws IOException {
  File file = driveService, android .files().get(fileId).execute();
  Event event = calendarService.events().get(calendarId, eventId).execute();

  List<EventAttachment> attachments = event.getAttachments();
  if (attachments == null) {
    attachments = new ArrayList<EventAttachment>();
  }
  attachments.add(new EventAttachment()
      .setFileUrl(file.getAlternateLink())
      .setMimeType(file.getMimeType())
      .setTitle(file.getTitle()));

  Event changes = new Event()
      .setAttachments(attachments);
  calendarService.events().patch(calendarId, eventId, changes)
      .setSupportsAttachments(true)
      .execute();
}
</code>```

I copy totaly this but it doesnt work,Android Studio put in red getAlternateLink() and getTitle()doesnt reconize, specifically the lines:

```<code>attachments.add(new EventAttachment()
      .setFileUrl(file.getAlternateLink())
      .setMimeType(file.getMimeType())
      .setTitle(file.getTitle()));
</code>```

答:
In Drive:v3 doesnt exit getALternateLink() change de version to v2 in app

```<code>//    compile('com.google.apis:google-api-services-drive:v3-rev64-1.22.0') {
//        exclude group: 'org.apache.httpcomponents'
//    }
</code>```

put this

```<code>compile('com.google.apis:google-api-services-drive:v2-rev123-1.18.0-rc'){
    exclude group: 'org.apache.httpcomponents'
}
</code>```

Body:
I`m trying create a event with file a (.pdf) in android App with Google Calendar API: Create Events

public static void addAttachment(Calendar calendarService, Drive driveService, String calendarId,
    String eventId, String fileId) throws IOException {
  File file = driveService, android .files().get(fileId).execute();
  Event event = calendarService.events().get(calendarId, eventId).execute();

  List<EventAttachment> attachments = event.getAttachments();
  if (attachments == null) {
    attachments = new ArrayList<EventAttachment>();
  }
  attachments.add(new EventAttachment()
      .setFileUrl(file.getAlternateLink())
      .setMimeType(file.getMimeType())
      .setTitle(file.getTitle()));

  Event changes = new Event()
      .setAttachments(attachments);
  calendarService.events().patch(calendarId, eventId, changes)
      .setSupportsAttachments(true)
      .execute();
}

I copy totaly this but it doesnt work,Android Studio put in red getAlternateLink() and getTitle()doesnt reconize, specifically the lines:

attachments.add(new EventAttachment()
      .setFileUrl(file.getAlternateLink())
      .setMimeType(file.getMimeType())
      .setTitle(file.getTitle()));

Body:
In Drive:v3 doesnt exit getALternateLink() change de version to v2 in app

//    compile('com.google.apis:google-api-services-drive:v3-rev64-1.22.0') {
//        exclude group: 'org.apache.httpcomponents'
//    }

put this

compile('com.google.apis:google-api-services-drive:v2-rev123-1.18.0-rc'){
    exclude group: 'org.apache.httpcomponents'
}


问:
VBA Excel - Protect column in a sheet based on a cell value
1   1   2
ACT ACT FCST

I have three columns,  I would like to run a loop code to check values in 2nd row if it is ACT or FCST. If it is ACT, it will protect the whole column so it will not be updated.

答:
You don't protect columns, you protect sheet. Cells are set as locked or not locked. Then if the sheet protection is activated, locked cells cannot be edited. So to unlock cells to make them editable, you have to unprotect the sheet, set cells as unlocked then protect the sheet.

Use the macro recorder to help you create code. When you develop code that has issue, post it for analysis.


问:
AngularJS don't trigger outer click event (nested ng-click)
```<code><ul>
    <li ng-init="hide1={state1:true}" ng-repeat="(id,name) in tree_root_list" ng-click="click_e_s_0(id,hide1)">
        {$ name[1] $}
        <ul ng-hide=hide1.state1>
            <li ng-init="hide2={state2:true}" ng-repeat="id in e_s_1" ng-click="click_e_s_1(hide2)">
                 {$ id $}
            </li>
        </ul>
    </li>
</ul>
</code>```

When I trigger the click_e_s_1(hide2) event, Angular calls the click_e_s_0(id,hide1) event.

Actually I don't want to trigger click_e_s_0(id,hide1) event.

答:
You are actutally trying to trigger a ng-click (click_e_s_1) imbricated in another ng-click (click_e_s_0). If you want to trigger only the imbricated one, you need to stop the propagation of the parent:

Inline solution

```<code><li ... ng-click="click_e_s_1(hide2); $event.stopPropagation()">
</code>```

In function solution

```<code><li ... ng-click="click_e_s_1(hide2, $event)">
</code>```

And update click_e_s_1:

```<code>$scope.click_e_s_1 = function(hide2, $event) {
    $event.stopPropagation();
    // Your stuff
}
</code>```

Body:
        {$ name[1] $}

                 {$ id $}

When I trigger the click_e_s_1(hide2) event, Angular calls the click_e_s_0(id,hide1) event.

Actually I don't want to trigger click_e_s_0(id,hide1) event.

Body:
You are actutally trying to trigger a ng-click (click_e_s_1) imbricated in another ng-click (click_e_s_0). If you want to trigger only the imbricated one, you need to stop the propagation of the parent:

Inline solution

<li ... ng-click="click_e_s_1(hide2); $event.stopPropagation()">

In function solution

<li ... ng-click="click_e_s_1(hide2, $event)">

And update click_e_s_1:

$scope.click_e_s_1 = function(hide2, $event) {
    $event.stopPropagation();
    // Your stuff
}


问:
Run exe with custom arguments using powershell
I need to run(install) an exe which takes some custom parameter (arguments)

```<code>install.exe --no-prompt -u username -p password
</code>```

The above command is provided by the 3rd party team to allow silent installation of the agent

Since I am trying to install this via powershell, I want to know how to pass the custom parameters.

Note: This exe is a 3rd party component. I can run command to install an exe with credentials object. But the same is not working in this case.

答:
```<code>Start-Process -FilePath C:\run.exe -ArgumentList "--no-prompt -u username -p password"
</code>```

Body:
I need to run(install) an exe which takes some custom parameter (arguments)

install.exe --no-prompt -u username -p password

The above command is provided by the 3rd party team to allow silent installation of the agent

Since I am trying to install this via powershell, I want to know how to pass the custom parameters.

Note: This exe is a 3rd party component. I can run command to install an exe with credentials object. But the same is not working in this case.


问:
Can you cast the result of sizeof
I was wondering if it makes sense to cast the result of sizeof?

Example:

Change sizeof(example) to (size_t) sizeof(example)

The return type is std::size_tbut I get the error "Invalid arguements" in many functions (malloc, memset, ...) and after the cast it works. A quick check with printf showed me, that the results stayed the same.

-edited-

As requested a short example function:

```<code>   __cxa_dependent_exception* __cxxabiv1:: __cxa_allocate_dependent_exception()  throw()
    {
        __cxa_dependent_exception *ps_ret;

        ps_ret = static_cast<__cxa_dependent_exception *> (o_exception_pool.allocate (
                (size_t) sizeof(__cxa_dependent_exception )));

        if (!ps_ret)
        {
            std::terminate ();
        }

        memset (ps_ret, 0, (size_t) sizeof(__cxa_dependent_exception ));

        return ps_ret;
    }
</code>```

Without the casts this code throws the mentioned error. The full example can be found in the gcc 4.5.4 source code "libstdc++-v3/libsupc++/eh_alloc.cc"

I am using MinGW.

答:
Your question condenses to "can you cast std::size_t to another type".

Of course you can, subject to the normal type casting rules. For example, casting this to an int could get you in hot water for a very large type on a system with a 16 bit int. In other words, check that the new type has adequate capacity.

Extra brownie points if you can do that at compile time in C++.

ContentLicense: CC BY-SA 4.0

Title: Can you cast the result of sizeof

Body:
I was wondering if it makes sense to cast the result of sizeof?

Example:

Change sizeof(example) to (size_t) sizeof(example)

The return type is std::size_tbut I get the error "Invalid arguements" in many functions (malloc, memset, ...) and after the cast it works. A quick check with printf showed me, that the results stayed the same.

-edited-

As requested a short example function:

   __cxa_dependent_exception* __cxxabiv1:: __cxa_allocate_dependent_exception()  throw()
    {
        __cxa_dependent_exception *ps_ret;

        ps_ret = static_cast<__cxa_dependent_exception *> (o_exception_pool.allocate (
                (size_t) sizeof(__cxa_dependent_exception )));

        if (!ps_ret)
        {
            std::terminate ();
        }

        memset (ps_ret, 0, (size_t) sizeof(__cxa_dependent_exception ));

        return ps_ret;
    }

Without the casts this code throws the mentioned error. The full example can be found in the gcc 4.5.4 source code "libstdc++-v3/libsupc++/eh_alloc.cc"

I am using MinGW.


问:
Managing Hibernate concurrency with remote web service
In Spring/Hibernate application. user may send more than on request with the same data to banking account. here is the case:
1st request reaches first, bank serve it, return OK, set local account data status OK;
2nd request reaches second, bank serve it, return NOK, set local account data status NOK;

Now our local account data status is NOK, although its served well.
Any suggestions for this problem.

答:
A. Make sure the recieving web service queues the requests.

B. Perform not more than a maximum of two threads executions at a time at the web service side. This will provide you clues on why the status is set to NOK.