问:
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
```

答: I would investigate rewriting your query using a crosstab query (PIVOT command in Sql Server). This is like a GROUP BY statement, except that it puts the aggregate values into columns rather than rows, which is what you're doing. It may take a little time to get your head round it but the effort will be worth it, you won't have all that copy-and-paste code, and it should run faster.</p>

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


来源: stackexchange

问:
Excel Populate Data
I have the following input data:

I need to populate each entry into 3 rows with different payout rates & periods, something like this:

Thecommission payout is based on the following rates:

i) 1st month - 50%

ii) 2nd month - 30%

iii) 3rd month - 20%

Example:-

The next criteria is the commission payout period. The current month collection will be paid in the following months.

How can I do it using a Excel & macro?

Thanks.

答:
Assuming your data is on the 1st sheet, create a second sheet and run this macro:

```<code>Option Explicit

Sub popData()

 Dim r As Integer, r2 As Integer, p As Integer: r2 = 3
 Dim sht2 As Worksheet: Set sht2 = Sheets(2)

 Dim rate(), headings()
 rate = Array(0.5, 0.3, 0.2)
 headings = Array("Date", "Sales", "Commission", "Collection Date", "Invoice No")

 For r = 0 To UBound(headings):
   sht2.Cells(2, r + 1).Value = headings(r)
 Next

 Dim dat As Date
 With Sheets(1)
   sht2.Cells(1, 1).Value = "Payout"
   For r = 3 To .Cells(.Rows.Count, "A").End(xlUp).row:
     For p = 0 To 2:
       sht2.Cells(r2, "A").NumberFormat = .Cells(r, "A").NumberFormat
       dat = DateAdd("m", p + 1, CDate(.Cells(r, "A")))
       sht2.Cells(r2, "A").Value = DateSerial(year(dat), month(dat) + 1, 0)
       sht2.Cells(r2, "B").Value = .Cells(r, "B").Value
       sht2.Cells(r2, "C").NumberFormat = "0.00"
       sht2.Cells(r2, "C").Value = .Cells(r, "C").Value * rate(p)
       sht2.Cells(r2, "D").NumberFormat = .Cells(r, "A").NumberFormat
       sht2.Cells(r2, "D").Value = .Cells(r, "A").Value
       sht2.Cells(r2, "E").Value = .Cells(r, "D").Value
       r2 = r2 + 1
     Next
   Next
 End With

 sht2.Columns("A:E").EntireColumn.AutoFit

End Sub
</code>```

The first loop adds the headers to the 2nd sheet.  The next double loop processes each salesperson with the inner loop creating the 3 rows for each commission payout.  It appears that you want the last day of the next month - which is provided for by adding a month to the present date and then using the DateSerial function to find the last day of that month.  The last line just ensures all the columns are expanded to their largest text entry so everything is visible.

ContentLicense: CC BY-SA 3.0

Title: Excel Populate Data

Body:
Assuming your data is on the 1st sheet, create a second sheet and run this macro:

Option Explicit

Sub popData()

 Dim r As Integer, r2 As Integer, p As Integer: r2 = 3
 Dim sht2 As Worksheet: Set sht2 = Sheets(2)

 Dim rate(), headings()
 rate = Array(0.5, 0.3, 0.2)
 headings = Array("Date", "Sales", "Commission", "Collection Date", "Invoice No")

 For r = 0 To UBound(headings):
   sht2.Cells(2, r + 1).Value = headings(r)
 Next

 Dim dat As Date
 With Sheets(1)
   sht2.Cells(1, 1).Value = "Payout"
   For r = 3 To .Cells(.Rows.Count, "A").End(xlUp).row:
     For p = 0 To 2:
       sht2.Cells(r2, "A").NumberFormat = .Cells(r, "A").NumberFormat
       dat = DateAdd("m", p + 1, CDate(.Cells(r, "A")))
       sht2.Cells(r2, "A").Value = DateSerial(year(dat), month(dat) + 1, 0)
       sht2.Cells(r2, "B").Value = .Cells(r, "B").Value
       sht2.Cells(r2, "C").NumberFormat = "0.00"
       sht2.Cells(r2, "C").Value = .Cells(r, "C").Value * rate(p)
       sht2.Cells(r2, "D").NumberFormat = .Cells(r, "A").NumberFormat
       sht2.Cells(r2, "D").Value = .Cells(r, "A").Value
       sht2.Cells(r2, "E").Value = .Cells(r, "D").Value
       r2 = r2 + 1
     Next
   Next
 End With

 sht2.Columns("A:E").EntireColumn.AutoFit

End Sub

The first loop adds the headers to the 2nd sheet.  The next double loop processes each salesperson with the inner loop creating the 3 rows for each commission payout.  It appears that you want the last day of the next month - which is provided for by adding a month to the present date and then using the DateSerial function to find the last day of that month.  The last line just ensures all the columns are expanded to their largest text entry so everything is visible.


问:
C# SQL Server: Application works with database from another pc
I have my application written in C#, it uses a SQL Server database. I have a connection to a database in my C# code, some queries to write/read from database.

Now consider following: if I want to run this app from another PC, this PC has no SQL Server, will this app work? (I assume not) If not then how can this another PC still work with database? Is there a way to programmatically create database on another PC using C# so it has a name I need, columns I need. I created my database using SQL Server Management Studio.

答:
You can do this by setting up SQL server on another machine (server).

Set that servers authentication to SQL Server authentication (requires username and password for that SQL server Management Studio)

Change the connection string in web/App.config from your C# app to use that servers Ip address and Credentails for SQL server


问:
Why is my 2D array of double not accepted as a valid parameter?
I have been trying to resolve an undefined reference error for hours caused by my 2D array, but I don't know what I am missing. I have a 2D array.

```<code>#include <iostream>
#include "ServoJoint.h"
#include "JointedLeg.h"
#include "TwoJointLeg.h"
#include "ThreeJointLeg.h"

int main() {

    //2D array containing the 7 calibrated positions for each of the 10 servo motors
    size_t servoCalibrations[10][7]={{143, 190, 245, 305, 365, 410, 467},
                                     {143, 205, 260, 320, 377, 423, 475},
                                     {115, 168, 230, 280, 340, 392, 440},
                                     {130, 182, 235, 290, 340, 397, 455},
                                     {135, 190, 235, 290, 345, 400, 455},
                                     {140, 190, 250, 305, 355, 415, 465},
                                     {130, 185, 240, 300, 355, 415, 465},
                                     {140, 200, 245, 300, 355, 410, 460},
                                     {140, 200, 245, 300, 355, 410, 460},
                                     {140, 200, 245, 300, 355, 410, 460}};

    double walkCycle[4][2]= {{45, 45},
                            {60, 150},
                            {60, 135},
                            {30, 60}};

    //Array of pointers to 10 size_t arrays of size 7
    //This is because C++ doesn't allow accessing an entire row/column of a 2D array
    //or I don't know how to
    size_t * calibrationArrays[10];
    for(size_t i=0; i<10; i++){
        size_t * tmp= new size_t[7];
        for(size_t j=0; j<7; j++){
            tmp[j]= servoCalibrations[i][j];
        }
        calibrationArrays[i]= tmp;
    }

    //Array of pointers to 10 ServoJoint objects
    ServoJoint * servoJoints[10];

    //Dynamically allocate 10 ServoJoint objects
    for(size_t i=0; i<10; i++){
        servoJoints[i]= new ServoJoint(i, 7, calibrationArrays[i]);
    }

    for(size_t i=0; i<10; i++){
        std::cout << "Servo motor " << i+1 << " has the following PWM values: ";
        for(size_t j=0; j<7; j++){
            std::cout << servoJoints[i]->getPWM(-90+(double)(j*30)) << " ";
        }
        std::cout<< std::endl;
    };

    //Array of pointers to 2 ThreeJointLeg objects
    ThreeJointLeg * threeJointLegs[2];

    //Dynamically allocate 2 ThreeJointLeg objects
    for(size_t i=0; i<6; i+=3){
        threeJointLegs[i]= new ThreeJointLeg(servoJoints[i], servoJoints[i+1], servoJoints[i+2], 1-(i%2)*2, walkCycle);
    }

    //Array of pointers to 2 TwoJointLeg objects
    TwoJointLeg * twoJointLegs[2];

    //Dynamically allocate 2 TwoJointLeg objects
    for(size_t i=6; i<10; i+=2){
        twoJointLegs[i]= new TwoJointLeg(servoJoints[i],servoJoints[i+1], 1, walkCycle);
    }

    return 0;
}
</code>```

JointedLeg.h:

```<code>#ifndef ROBOTLEG_JointedLeg_H
#define ROBOTLEG_JointedLeg_H

#include "ServoJoint.h"

class JointedLeg{

private:
//Pointers to the 2 ServoJoint objects for the upper and lower joint
ServoJoint * upperJoint;
ServoJoint * lowerJoint;

int isRight;

//2D array, where each of the four rows represents each phase in the walk cycle and each column of each row
//represents the upper and lower joint angles respectively

//walkCycle[0][]= home
//walkCycle[1][]= step forward
//walkCycle[2][]= step down
//walkCycle[3][]= step back
double walkCycle[4][2];

public:
JointedLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]);
</code>```

JointedLeg.cpp:

```<code>#include "JointedLeg.h"

JointedLeg::JointedLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]):upperJoint(joint1), lowerJoint(joint2), isRight(side){
    for(size_t i=0; i<4; i++){
        for(size_t j=0; j<2; j++){
            walkCycle[i][j]= givenWalk[i][j];
        }
    }
}
</code>```

TwoJointLeg.h:

```<code>#ifndef QUADRUPEDROBOT_TWOJOINTLEG_H
#define QUADRUPEDROBOT_TWOJOINTLEG_H

#include "JointedLeg.h"

class TwoJointLeg : public JointedLeg{

public:
    TwoJointLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]);

};

#endif //QUADRUPEDROBOT_TWOJOINTLEG_H
</code>```

TwoJointLeg.cpp:

```<code>#include "TwoJointLeg.h"

TwoJointLeg::TwoJointLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2])
        :JointedLeg(joint1, joint2, side, givenWalk){}
</code>```

and I wish to pass this as a parameter to the following constructor:

```<code>JointedLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]);
</code>```

I read on Passing a 2D array to a C++ function that when I declare a 2D array with 2 brackets, i.e. [][], I can do the same in the function header and simply pass the array variable name, which is what I believe I have done.

However, my compiler keeps telling me that I am passing a "double (*) [2]." I am aware that a 2D array is technically a pointer to sub-arrays and that the first array can be decayed to a pointer, but why is there a mismatch in the parameter type when I have the same syntax in the function call and the definition?

I've tried both of these in the function header:

```<code>double givenWalk[4][2]
double givenWalk[][2]
</code>```

答:
See the following minimal example, which shows that it works:</p>

```<code>void JointedLeg(double givenWalk[][2]) { }

int main(int argc, char* argv[]) {

    double walkCycle[4][2]= {{45, 45},
        {60, 150},
        {60, 135},
        {30, 60}};

    JointedLeg(walkCycle);

    return 0;
}
</code>```

Body:
I have been trying to resolve an undefined reference error for hours caused by my 2D array, but I don't know what I am missing. I have a 2D array.

#include <iostream>
#include "ServoJoint.h"
#include "JointedLeg.h"
#include "TwoJointLeg.h"
#include "ThreeJointLeg.h"

int main() {

    //2D array containing the 7 calibrated positions for each of the 10 servo motors
    size_t servoCalibrations[10][7]={{143, 190, 245, 305, 365, 410, 467},
                                     {143, 205, 260, 320, 377, 423, 475},
                                     {115, 168, 230, 280, 340, 392, 440},
                                     {130, 182, 235, 290, 340, 397, 455},
                                     {135, 190, 235, 290, 345, 400, 455},
                                     {140, 190, 250, 305, 355, 415, 465},
                                     {130, 185, 240, 300, 355, 415, 465},
                                     {140, 200, 245, 300, 355, 410, 460},
                                     {140, 200, 245, 300, 355, 410, 460},
                                     {140, 200, 245, 300, 355, 410, 460}};

    double walkCycle[4][2]= {{45, 45},
                            {60, 150},
                            {60, 135},
                            {30, 60}};

    //Array of pointers to 10 size_t arrays of size 7
    //This is because C++ doesn't allow accessing an entire row/column of a 2D array
    //or I don't know how to
    size_t * calibrationArrays[10];
    for(size_t i=0; i<10; i++){
        size_t * tmp= new size_t[7];
        for(size_t j=0; j<7; j++){
            tmp[j]= servoCalibrations[i][j];
        }
        calibrationArrays[i]= tmp;
    }

    //Array of pointers to 10 ServoJoint objects
    ServoJoint * servoJoints[10];

    //Dynamically allocate 10 ServoJoint objects
    for(size_t i=0; i<10; i++){
        servoJoints[i]= new ServoJoint(i, 7, calibrationArrays[i]);
    }

    for(size_t i=0; i<10; i++){
        std::cout << "Servo motor " << i+1 << " has the following PWM values: ";
        for(size_t j=0; j<7; j++){
            std::cout << servoJoints[i]->getPWM(-90+(double)(j*30)) << " ";
        }
        std::cout<< std::endl;
    };

    //Array of pointers to 2 ThreeJointLeg objects
    ThreeJointLeg * threeJointLegs[2];

    //Dynamically allocate 2 ThreeJointLeg objects
    for(size_t i=0; i<6; i+=3){
        threeJointLegs[i]= new ThreeJointLeg(servoJoints[i], servoJoints[i+1], servoJoints[i+2], 1-(i%2)*2, walkCycle);
    }

    //Array of pointers to 2 TwoJointLeg objects
    TwoJointLeg * twoJointLegs[2];

    //Dynamically allocate 2 TwoJointLeg objects
    for(size_t i=6; i<10; i+=2){
        twoJointLegs[i]= new TwoJointLeg(servoJoints[i],servoJoints[i+1], 1, walkCycle);
    }

    return 0;
}

JointedLeg.h:

#ifndef ROBOTLEG_JointedLeg_H
#define ROBOTLEG_JointedLeg_H

#include "ServoJoint.h"

class JointedLeg{

private:
//Pointers to the 2 ServoJoint objects for the upper and lower joint
ServoJoint * upperJoint;
ServoJoint * lowerJoint;

int isRight;

//2D array, where each of the four rows represents each phase in the walk cycle and each column of each row
//represents the upper and lower joint angles respectively

//walkCycle[0][]= home
//walkCycle[1][]= step forward
//walkCycle[2][]= step down
//walkCycle[3][]= step back
double walkCycle[4][2];

public:
JointedLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]);

JointedLeg.cpp:

#include "JointedLeg.h"

JointedLeg::JointedLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]):upperJoint(joint1), lowerJoint(joint2), isRight(side){
    for(size_t i=0; i<4; i++){
        for(size_t j=0; j<2; j++){
            walkCycle[i][j]= givenWalk[i][j];
        }
    }
}

TwoJointLeg.h:

#ifndef QUADRUPEDROBOT_TWOJOINTLEG_H
#define QUADRUPEDROBOT_TWOJOINTLEG_H

#include "JointedLeg.h"

class TwoJointLeg : public JointedLeg{

public:
    TwoJointLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]);

};

#endif //QUADRUPEDROBOT_TWOJOINTLEG_H

TwoJointLeg.cpp:

#include "TwoJointLeg.h"

TwoJointLeg::TwoJointLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2])
        :JointedLeg(joint1, joint2, side, givenWalk){}

and I wish to pass this as a parameter to the following constructor:

JointedLeg(ServoJoint * joint1, ServoJoint * joint2, int side, double givenWalk[][2]);

I read on Passing a 2D array to a C++ function that when I declare a 2D array with 2 brackets, i.e. [][], I can do the same in the function header and simply pass the array variable name, which is what I believe I have done.

However, my compiler keeps telling me that I am passing a "double (*) [2]." I am aware that a 2D array is technically a pointer to sub-arrays and that the first array can be decayed to a pointer, but why is there a mismatch in the parameter type when I have the same syntax in the function call and the definition?

I've tried both of these in the function header:

double givenWalk[4][2]
double givenWalk[][2]

Body:
See the following minimal example, which shows that it works:

void JointedLeg(double givenWalk[][2]) { }

int main(int argc, char* argv[]) {

    double walkCycle[4][2]= {{45, 45},
        {60, 150},
        {60, 135},
        {30, 60}};

    JointedLeg(walkCycle);

    return 0;
}


问:
Countdown timer that runs on server in php
How can I create a countdown timer in php that runs on a server. I have a countdown timer in javascript but it takes the current time from the user's system which i dont want. I want the timer should be same for all the user and time doesn't get affected if user changes the time on his system.</p>

答:
Use PHP's date_default_timezone_set function and set it to server's timezone or best will be UTC.

```<code>date_default_timezone_set('UTC');
$curDT = date('Y-m-d H:i:s');
</code>```

Pass this value in JS and use it for timer.

Body:
Use PHP's date_default_timezone_set function and set it to server's timezone or best will be UTC.

date_default_timezone_set('UTC');
$curDT = date('Y-m-d H:i:s');

Pass this value in JS and use it for timer.


问:
(Should be easy) Selenium chromedriver (java) no element found error
I tried this exact code on other websites and it seems to work fine. It's just on pizza hut that it can't even locate an element let alone click on it. Thread.sleep() doesn't make a difference. The problem is between the commented *, according to the compiler. Here's the code.</p>

```<code>package training;

import org.junit.Test;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;

public class PizzaHut {

WebDriver driver;
@Test
public void open() throws Exception {
        //SET UP WEBDRIVER AND OPEN WEBSITE
        System.setProperty("webdriver.chrome.driver","/Users/1mr4n/Downloads/chromedriver");
        WebDriver driver = new ChromeDriver();
        driver.get("https://www.pizzahut.com/#/home");
        Thread.sleep(1000);

        //CLICK PIZZA
        Thread.sleep(5000);
        test("before");
        //**************PROBLEM*CODE**********************
        driver.findElement(By.xpath("//a[@id='lg-nav-pizza']")).click();
        //************************************************
        test("clicked pizza");

        //CLOSE BROWSER
        Thread.sleep(15000);
        driver.close();
    }

    public static void test(String x) {
        System.out.println(x);
    }
}
</code>```

答:
Try this one ,definitely it help you.

package training;

```<code>    import org.junit.Test;
    import org.openqa.selenium.By;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.chrome.ChromeDriver;

    public class PizzaHut {
WebDriver driver;
@Test
public void open() throws Exception {
        //SET UP WEBDRIVER AND OPEN WEBSITE
      //  System.setProperty("webdriver.chrome.driver","/Users/1mr4n/Downloads/chromedriver");
        WebDriver driver = new ChromeDriver();
        driver.get("https://www.pizzahut.com/#/home");
        Thread.sleep(1000);

        //CLICK PIZZA
        Thread.sleep(5000);
        test("before");

        List<WebElement> webElements=driver.findElement(By.cssSelector("div.btn-group.btn-group-lg.btn-group-justified.ph-header-navigation"))
        .findElements(By.cssSelector("div.btn.btn-link.ph-ghost-padding.ng-scope"));

for (WebElement element : webElements) {
if (element.getAttribute("title").equals("PIZZA")) {
element.click();
}
}

Thread.sleep(5000);

        test("clicked pizza");

        //CLOSE BROWSER
        Thread.sleep(15000);
        driver.close();
    }

    public static void test(String x) {
        System.out.println(x);
    }
}
</code>```

Body:
I tried this exact code on other websites and it seems to work fine. It's just on pizza hut that it can't even locate an element let alone click on it. Thread.sleep() doesn't make a difference. The problem is between the commented *, according to the compiler. Here's the code.

package training;

import org.junit.Test;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;

public class PizzaHut {

WebDriver driver;
@Test
public void open() throws Exception {
        //SET UP WEBDRIVER AND OPEN WEBSITE
        System.setProperty("webdriver.chrome.driver","/Users/1mr4n/Downloads/chromedriver");
        WebDriver driver = new ChromeDriver();
        driver.get("https://www.pizzahut.com/#/home");
        Thread.sleep(1000);

        //CLICK PIZZA
        Thread.sleep(5000);
        test("before");
        //**************PROBLEM*CODE**********************
        driver.findElement(By.xpath("//a[@id='lg-nav-pizza']")).click();
        //************************************************
        test("clicked pizza");

        //CLOSE BROWSER
        Thread.sleep(15000);
        driver.close();
    }

    public static void test(String x) {
        System.out.println(x);
    }
}

Body:
Try this one ,definitely it help you.

package training;

    import org.junit.Test;
    import org.openqa.selenium.By;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.chrome.ChromeDriver;

    public class PizzaHut {
WebDriver driver;
@Test
public void open() throws Exception {
        //SET UP WEBDRIVER AND OPEN WEBSITE
      //  System.setProperty("webdriver.chrome.driver","/Users/1mr4n/Downloads/chromedriver");
        WebDriver driver = new ChromeDriver();
        driver.get("https://www.pizzahut.com/#/home");
        Thread.sleep(1000);

        //CLICK PIZZA
        Thread.sleep(5000);
        test("before");

        List<WebElement> webElements=driver.findElement(By.cssSelector("div.btn-group.btn-group-lg.btn-group-justified.ph-header-navigation"))
        .findElements(By.cssSelector("div.btn.btn-link.ph-ghost-padding.ng-scope"));

for (WebElement element : webElements) {
if (element.getAttribute("title").equals("PIZZA")) {
element.click();
}
}

Thread.sleep(5000);

        test("clicked pizza");

        //CLOSE BROWSER
        Thread.sleep(15000);
        driver.close();
    }

    public static void test(String x) {
        System.out.println(x);
    }
}


问:
Change one column value for a specific group of variables
I have the following dataset:

```<code>help <- data.frame(id = c(rep(5, times = 5), rep(12, times = 3), rep(33, times = 2), rep(54, times = 3), rep(66, times = 4)),
                   ob = c(rep(1, times = 5), rep(0, times = 3), rep(1, times = 2), rep(0, times =3), rep(1, times =4)))
</code>```

I am trying to change only the values for id = 5 and 66 from 1 to 0 so the df looks like this:

```<code>    id ob
1   5  0
2   5  0
3   5  0
4   5  0
5   5  0
6  12  0
7  12  0
8  12  0
9  33  1
10 33  1
11 54  0
12 54  0
13 54  0
14 66  0
15 66  0
16 66  0
17 66  0
</code>```

I have attempted:

```<code>change <- c(5, 66)
mutate(help, ob = ifelse(xor(id %in% change), ob == 0, 1))
</code>```

But I keep hitting errors and I don't want to change the values of any other id, only the ones specified. I could do this the long way by filtering out and replacing and joining back in, but guessing there is a quicker solution.  Any help would be great.

答:
We can use %in% to create a logical index and then based on that assign the values of 'ob' to 0

```<code>help$ob[help$id %in% c(5, 66)] <- 0
</code>```

If we are using ifelse, then

```<code>help$ob <- with(help, ifelse(id %in% c(5, 66), 0, ob))
</code>```

NOTE: Both of these are base R methods

Body:
I have the following dataset:

help <- data.frame(id = c(rep(5, times = 5), rep(12, times = 3), rep(33, times = 2), rep(54, times = 3), rep(66, times = 4)),
                   ob = c(rep(1, times = 5), rep(0, times = 3), rep(1, times = 2), rep(0, times =3), rep(1, times =4)))

I am trying to change only the values for id = 5 and 66 from 1 to 0 so the df looks like this:

    id ob
1   5  0
2   5  0
3   5  0
4   5  0
5   5  0
6  12  0
7  12  0
8  12  0
9  33  1
10 33  1
11 54  0
12 54  0
13 54  0
14 66  0
15 66  0
16 66  0
17 66  0

I have attempted:

change <- c(5, 66)
mutate(help, ob = ifelse(xor(id %in% change), ob == 0, 1))

But I keep hitting errors and I don't want to change the values of any other id, only the ones specified. I could do this the long way by filtering out and replacing and joining back in, but guessing there is a quicker solution.  Any help would be great.

Body:
We can use %in% to create a logical index and then based on that assign the values of 'ob' to 0

help$ob[help$id %in% c(5, 66)] <- 0

If we are using ifelse, then

help$ob <- with(help, ifelse(id %in% c(5, 66), 0, ob))

NOTE: Both of these are base R methods


问:
Show multiple table's data in one single cell in SQL Server
I want to show multiple data from tables into one single cell. Please find the script for my tables.

```<code>DECLARE @Tab TABLE(code VARCHAR(10), name varchar(20), val1 INT)
INSERT INTO @Tab VALUES ('A', 'Test', 34)
INSERT INTO @Tab VALUES ('B', 'Test', 6)

DECLARE @Tab1 TABLE(code VARCHAR(10), name varchar(20), val2 INT)
INSERT INTO @Tab1 VALUES ('A','Test', 178)

DECLARE @Tab2 TABLE(code VARCHAR(10), name varchar(20), Total INT)
INSERT INTO @Tab2 VALUES ('A','Test', 180)
INSERT INTO @Tab2 VALUES ('B', 'Test', 10)

DECLARE @Tab3 TABLE(code VARCHAR(10), name varchar(20), val1 INT)
INSERT INTO @Tab3 VALUES ('A', 'Test1', 56)

DECLARE @Tab4 TABLE(code VARCHAR(10), name varchar(20), val2 INT)
INSERT INTO @Tab4 VALUES ('A','Test1', 87)

DECLARE @Tab5 TABLE(code VARCHAR(10), name varchar(20), Total INT)
INSERT INTO @Tab5 VALUES ('A','Test1', 93)
</code>```

I want to show the data in a cell single cell in the format as below:-

Thanks

答:
Hope this helps you. </p>

```<code>With T (code, [name], val1, val2, total)
AS
(
Select code, [name], val1, 0, 0 from @Tab
Union
Select code, [name], 0, val2, 0 from @Tab1
Union
Select code, [name], 0, 0, Total from @Tab2
Union
Select code, [name], val1, 0, 0 from @Tab3
Union
Select code, [name], 0, val2, 0 from @Tab4
Union
Select code, [name], 0, 0, Total from @Tab5
),
T2 (code, [name], total, val1, val2)
AS
(
Select code, [name], total = sum(total), val1 = sum(val1), val2 = sum(val2)
from T group by code, [name]
)
Select code,
IsNull(Min( case [name] when 'Test' then CONCAT('Total ', total, '(val1:', val1, ',val2:', val2,')')  end ), 'Total 0(val1:0,val2:0)') Test,
IsNull(Min( case [name] when 'Test1' then CONCAT('Total ', total, '(val1:', val1, ',val2:', val2,')') end ), 'Total 0(val1:0,val2:0)') Test1
from T2
Group By code
</code>```

Body:
I want to show multiple data from tables into one single cell. Please find the script for my tables.

DECLARE @Tab TABLE(code VARCHAR(10), name varchar(20), val1 INT)
INSERT INTO @Tab VALUES ('A', 'Test', 34)
INSERT INTO @Tab VALUES ('B', 'Test', 6)

DECLARE @Tab1 TABLE(code VARCHAR(10), name varchar(20), val2 INT)
INSERT INTO @Tab1 VALUES ('A','Test', 178)

DECLARE @Tab2 TABLE(code VARCHAR(10), name varchar(20), Total INT)
INSERT INTO @Tab2 VALUES ('A','Test', 180)
INSERT INTO @Tab2 VALUES ('B', 'Test', 10)

DECLARE @Tab3 TABLE(code VARCHAR(10), name varchar(20), val1 INT)
INSERT INTO @Tab3 VALUES ('A', 'Test1', 56)

DECLARE @Tab4 TABLE(code VARCHAR(10), name varchar(20), val2 INT)
INSERT INTO @Tab4 VALUES ('A','Test1', 87)

DECLARE @Tab5 TABLE(code VARCHAR(10), name varchar(20), Total INT)
INSERT INTO @Tab5 VALUES ('A','Test1', 93)

I want to show the data in a cell single cell in the format as below:-

Thanks

Body:
Hope this helps you.

With T (code, [name], val1, val2, total)
AS
(
Select code, [name], val1, 0, 0 from @Tab
Union
Select code, [name], 0, val2, 0 from @Tab1
Union
Select code, [name], 0, 0, Total from @Tab2
Union
Select code, [name], val1, 0, 0 from @Tab3
Union
Select code, [name], 0, val2, 0 from @Tab4
Union
Select code, [name], 0, 0, Total from @Tab5
),
T2 (code, [name], total, val1, val2)
AS
(
Select code, [name], total = sum(total), val1 = sum(val1), val2 = sum(val2)
from T group by code, [name]
)
Select code,
IsNull(Min( case [name] when 'Test' then CONCAT('Total ', total, '(val1:', val1, ',val2:', val2,')')  end ), 'Total 0(val1:0,val2:0)') Test,
IsNull(Min( case [name] when 'Test1' then CONCAT('Total ', total, '(val1:', val1, ',val2:', val2,')') end ), 'Total 0(val1:0,val2:0)') Test1
from T2
Group By code


问:
controller not sending data back to ajax request codeigniter
I have developed a login system using ajax the problem is when i send the ajax request everything is working and validating fine i just need to pass data back to my ajax request I am using echo json_encode("true"); but somehow it is just echoing the value true in the controller and not going back in the view!

HTML

```<code>  <form onsubmit="return validate()" method="post" action="<?php echo base_url(); ?>admin/admin_login">
        <input class="md-input" placeholder="username" type="text" name = 'login_username'  id = 'login_username' />
        <input class="md-input" placeholder="password" type="password" name= 'login_password'  id= 'login_password' />
        <button type='submit' class="btn btn-primary btn-block btn-large">Login</button>
    </form>
</code>```

AJAX

```<code><script>

    function validate(){
        if(!$("#login_username").val()){
            alert("username is required");
            return false;
        }
        if(!$("#login_password").val()){
            alert("Password is required");
            return false;
        }
        return true;
        var data={
            "login_username" : $("#login_username").val(),
            "login_password" : $("#login_password").val()
        };
        $.ajax({
            type: 'post',
            url: '<?=base_url()?>Admin/admin_login',
            dataType: 'json',
            data:data,
            success: function (data) {
                if(data=="true"){
                    alert("ok");
                }
                else
                {
                    alert("not ok");
                }
            }
        });
    }
</script>
</code>```

admin_login controller

```<code> public function admin_login(){
        $data = $this->input->post();
        $status=$this->admin_validate->validate($data);
        if($status){
            $session=array(
                "admin"=>$this->input->post("login_username"),
            );

            $this->session->set_userdata($session);
            //redirect("Admin/contact");
            header('Content-Type: application/json');
            echo json_encode("true");
        }
        else
        {
            header('Content-Type: application/json');
            echo json_encode("false");
            //redirect("Admin");
        }
    }
</code>```

答:
Now iam going to change the code little

change the HTML form to

```<code><form>
        <input class="md-input" placeholder="username" type="text" name = 'login_username'  id = 'login_username' />
        <input class="md-input" placeholder="password" type="password" name= 'login_password'  id= 'login_password' />
        <button type='button' onclick="validate()" class="btn btn-primary btn-block btn-large">Login</button>
    </form>
</code>```

and Now change your ajax to

```<code><script>

    function validate(){
        if(!$("#login_username").val()){
            alert("username is required");
            return false;
        }
        if(!$("#login_password").val()){
            alert("Password is required");
            return false;
        }

        $.ajax({
            type: 'post',
            url: '<?php echo base_url()."Admin/admin_login"; ?>',
            data:{ "login_username" : $("#login_username").val(), "login_password" : $("#login_password").val() },
            success: function (data) {
                if(data=="true"){
                    alert("ok");
                }
                else
                {
                    alert("not ok");
                }
            }
        });
    }
</script>
</code>```

and your controller to

```<code>public function admin_login(){
        $data = $this->input->post();
        $status=$this->admin_validate->validate($data);
        if($status){
            $session=array(
                "admin"=>$this->input->post("login_username"),
            );

            $this->session->set_userdata($session);

            echo "true";
        }
        else
        {
            echo "false";
        }
    }
</code>```

Hope this helps you. :)

ContentLicense: CC BY-SA 4.0

Body:
I have developed a login system using ajax the problem is when i send the ajax request everything is working and validating fine i just need to pass data back to my ajax request I am using echo json_encode("true"); but somehow it is just echoing the value true in the controller and not going back in the view!

HTML

  <form onsubmit="return validate()" method="post" action="<?php echo base_url(); ?>admin/admin_login">
        <input class="md-input" placeholder="username" type="text" name = 'login_username'  id = 'login_username' />
        <input class="md-input" placeholder="password" type="password" name= 'login_password'  id= 'login_password' />
        <button type='submit' class="btn btn-primary btn-block btn-large">Login</button>
    </form>

AJAX

admin_login controller

 public function admin_login(){
        $data = $this->input->post();
        $status=$this->admin_validate->validate($data);
        if($status){
            $session=array(
                "admin"=>$this->input->post("login_username"),
            );

            $this->session->set_userdata($session);
            //redirect("Admin/contact");
            header('Content-Type: application/json');
            echo json_encode("true");
        }
        else
        {
            header('Content-Type: application/json');
            echo json_encode("false");
            //redirect("Admin");
        }
    }

Body:
Now iam going to change the code little

change the HTML form to

<form>
        <input class="md-input" placeholder="username" type="text" name = 'login_username'  id = 'login_username' />
        <input class="md-input" placeholder="password" type="password" name= 'login_password'  id= 'login_password' />
        <button type='button' onclick="validate()" class="btn btn-primary btn-block btn-large">Login</button>
    </form>

and Now change your ajax to

<script>

    function validate(){
        if(!$("#login_username").val()){
            alert("username is required");
            return false;
        }
        if(!$("#login_password").val()){
            alert("Password is required");
            return false;
        }

        $.ajax({
            type: 'post',
            url: '<?php echo base_url()."Admin/admin_login"; ?>',
            data:{ "login_username" : $("#login_username").val(), "login_password" : $("#login_password").val() },
            success: function (data) {
                if(data=="true"){
                    alert("ok");
                }
                else
                {
                    alert("not ok");
                }
            }
        });
    }
</script>

and your controller to

public function admin_login(){
        $data = $this->input->post();
        $status=$this->admin_validate->validate($data);
        if($status){
            $session=array(
                "admin"=>$this->input->post("login_username"),
            );

            $this->session->set_userdata($session);

            echo "true";
        }
        else
        {
            echo "false";
        }
    }

Hope this helps you. :)


问:
CastError mongoose
I am using mongodb in an express/nodejs project. I have a nested schema such that Themes have categories

Given this schema:

```<code>var ControlCategorySchema = new Schema({
    name: {
        type: String,
        Required: 'Please enter the category name'
    }
});
ControlCategorySchema.plugin(autoIncrement.plugin, 'ControlCategorySchemaCounter');

var ThemeSchema = new Schema({
    name : String,
    category : {type: Schema.Types.ObjectId, ref: 'ControlCategorySchema'}
});
ThemeSchema.plugin(autoIncrement.plugin, 'ThemeCounter');
</code>```

And the following controller code:

```<code>exports.create_a_theme = function(req, res) {
    console.log(req.body.category);
    if (req.body.category != undefined) {
        addTheme(req,res,req.body.category);
    }

};

function addTheme(req,res,category_id) {
    console.log("category id " + category_id);
    ControlCategory.findById(category_id, function (err, category_found) {
        if (err)
            res.send(err)

        req.body.category = category_found;
        console.log("body is " + req.body.category);
        var new_theme = new Theme();
        new_theme.name = req.body.name;
        new_theme.category = req.body.category;
        new_theme.save(function (err, theme) {
            if (err)
                res.send(err);
            console.log(theme);
            res.json(theme);
        });
    });
}
</code>```

Can anyone help solve why posting this json

```<code>{
    "name": "Application Security",
    "category": 0
}
</code>```

Results in the error below:

```<code>{
  "errors": {
    "category": {
      "message": "Cast to ObjectID failed for value \"{ _id: 0, name: 'Application Security', __v: 0 }\" at path \"category\"",
      "name": "CastError",
      "stringValue": "\"{ _id: 0, name: 'Application Security', __v: 0 }\"",
      "kind": "ObjectID",
      "value": {
        "_id": 0,
        "name": "Application Security",
        "__v": 0
      },
      "path": "category",
      "reason": {
        "message": "Cast to ObjectId failed for value \"{ _id: 0, name: 'Application Security', __v: 0 }\" at path \"category\"",
        "name": "CastError",
        "stringValue": "\"{ _id: 0, name: 'Application Security', __v: 0 }\"",
        "kind": "ObjectId",
        "value": {
          "_id": 0,
          "name": "Application Security",
          "__v": 0
        },
        "path": "category"
      }
    }
  },
  "message": "Theme validation failed",
  "name": "ValidationError"
}
</code>```

One thing that is very strange is that I do virtually the same thing on another schema and controller code and it works!

```<code>var AssessmentSchema = new Schema({
    name: {
        type: String,
        Required: 'Kindly enter the name of the assessment'
    },
    Created_date: {
        type: Date,
        default: Date.now
    },
    status: { type: Schema.ObjectId, ref: 'AssessmentStatus' },
});

var AssessmentStatusSchema = new Schema({
    name: {
        type: String,
        Required: 'Please enter the name of the assessment status'
    }

});

exports.create_an_assessment = function(req, res) {
    console.log(req.body.status);
    if (req.body.status == undefined) {
        AssessmentStatus.findOne({"name": "Due For Assessment"}, function (err, assessment_status) {
            if (err)
                res.send(err)
            addAssessment(req,res,assessment_status._id);
        });
    }
    else {

        addAssessment(req,res,req.body.status);
    }

};

function addAssessment(req,res,status_id) {
    console.log("status id " + status_id);
    AssessmentStatus.findById(status_id, function (err, assessment_status) {
        if (err)
            res.send(err)
        req.body.status = assessment_status;
        console.log("body is " + req.body.status);
        var new_assessment = new Assessment();
        new_assessment.name = req.body.name;
        new_assessment.status = assessment_status;
        new_assessment.save(function (err, assessment) {
            if (err)
                res.send(err);
            console.log(assessment);
            res.json(assessment);
        });
    });
}
</code>```

And there I post same kind of data

```<code>{
    "name": "Assessment 10",
    "status": 6
}
</code>```

and get the desired result

```<code>{
  "__v": 0,
  "_id": 28,
  "status": {
    "_id": 6,
    "name": "Due For Assessment",
    "__v": 0
  },
  "name": "Assessment 10",
  "Created_date": "2017-03-31T06:48:21.999Z"
}
</code>```

I cant tell what is different?

答:
This Error occurs when you provide an invalid ObjectId

The mongo _id is 12-byte BSON type ObjectId

here I have added if-else to verify whether the category_id is a Valid ObjectId or not, if it's then do the Operation, else throw an custom error message

When you are Using mongoose-autoincrement-package
it uses Number type instead of ObjectId and
the ref property type will be a Number instead of ObjectId, so when you declare the references  remember to change the reference property's type to Number instead of ObjectId if the referenced model is also using the plugin.

```<code>var ThemeSchema = new Schema({
    name : String,
    category : {type: Number, ref: 'ControlCategorySchema'}
});
</code>```

```<code>function addTheme(req, res, category_id) {
    console.log("category id " + category_id);
    var cond = mongoose.Types.ObjectId.isValid(category_id)

    if (cond) {
        ControlCategory.findById(category_id, function (err, category_found) {
            if (err)
                res.send(err)

            req.body.category = category_found;
            console.log("body is " + req.body.category);
            var new_theme = new Theme();
            new_theme.name = req.body.name;
            new_theme.category = req.body.category._id;
            new_theme.save(function (err, theme) {
                if (err)
                    res.send(err);
                console.log(theme);
                res.json(theme);
            });
        });
    } else {
        res.json({ message: 'not a valid object id' });
    }
}
</code>```

Title: CastError mongoose

Body:
I am using mongodb in an express/nodejs project. I have a nested schema such that Themes have categories

Given this schema:

var ControlCategorySchema = new Schema({
    name: {
        type: String,
        Required: 'Please enter the category name'
    }
});
ControlCategorySchema.plugin(autoIncrement.plugin, 'ControlCategorySchemaCounter');

var ThemeSchema = new Schema({
    name : String,
    category : {type: Schema.Types.ObjectId, ref: 'ControlCategorySchema'}
});
ThemeSchema.plugin(autoIncrement.plugin, 'ThemeCounter');

And the following controller code:

exports.create_a_theme = function(req, res) {
    console.log(req.body.category);
    if (req.body.category != undefined) {
        addTheme(req,res,req.body.category);
    }

};

function addTheme(req,res,category_id) {
    console.log("category id " + category_id);
    ControlCategory.findById(category_id, function (err, category_found) {
        if (err)
            res.send(err)

        req.body.category = category_found;
        console.log("body is " + req.body.category);
        var new_theme = new Theme();
        new_theme.name = req.body.name;
        new_theme.category = req.body.category;
        new_theme.save(function (err, theme) {
            if (err)
                res.send(err);
            console.log(theme);
            res.json(theme);
        });
    });
}

Can anyone help solve why posting this json

{
    "name": "Application Security",
    "category": 0
}

Results in the error below:

{
  "errors": {
    "category": {
      "message": "Cast to ObjectID failed for value \"{ _id: 0, name: 'Application Security', __v: 0 }\" at path \"category\"",
      "name": "CastError",
      "stringValue": "\"{ _id: 0, name: 'Application Security', __v: 0 }\"",
      "kind": "ObjectID",
      "value": {
        "_id": 0,
        "name": "Application Security",
        "__v": 0
      },
      "path": "category",
      "reason": {
        "message": "Cast to ObjectId failed for value \"{ _id: 0, name: 'Application Security', __v: 0 }\" at path \"category\"",
        "name": "CastError",
        "stringValue": "\"{ _id: 0, name: 'Application Security', __v: 0 }\"",
        "kind": "ObjectId",
        "value": {
          "_id": 0,
          "name": "Application Security",
          "__v": 0
        },
        "path": "category"
      }
    }
  },
  "message": "Theme validation failed",
  "name": "ValidationError"
}

One thing that is very strange is that I do virtually the same thing on another schema and controller code and it works!

var AssessmentSchema = new Schema({
    name: {
        type: String,
        Required: 'Kindly enter the name of the assessment'
    },
    Created_date: {
        type: Date,
        default: Date.now
    },
    status: { type: Schema.ObjectId, ref: 'AssessmentStatus' },
});

var AssessmentStatusSchema = new Schema({
    name: {
        type: String,
        Required: 'Please enter the name of the assessment status'
    }

});

exports.create_an_assessment = function(req, res) {
    console.log(req.body.status);
    if (req.body.status == undefined) {
        AssessmentStatus.findOne({"name": "Due For Assessment"}, function (err, assessment_status) {
            if (err)
                res.send(err)
            addAssessment(req,res,assessment_status._id);
        });
    }
    else {

        addAssessment(req,res,req.body.status);
    }

};

function addAssessment(req,res,status_id) {
    console.log("status id " + status_id);
    AssessmentStatus.findById(status_id, function (err, assessment_status) {
        if (err)
            res.send(err)
        req.body.status = assessment_status;
        console.log("body is " + req.body.status);
        var new_assessment = new Assessment();
        new_assessment.name = req.body.name;
        new_assessment.status = assessment_status;
        new_assessment.save(function (err, assessment) {
            if (err)
                res.send(err);
            console.log(assessment);
            res.json(assessment);
        });
    });
}

And there I post same kind of data

{
    "name": "Assessment 10",
    "status": 6
}

and get the desired result

{
  "__v": 0,
  "_id": 28,
  "status": {
    "_id": 6,
    "name": "Due For Assessment",
    "__v": 0
  },
  "name": "Assessment 10",
  "Created_date": "2017-03-31T06:48:21.999Z"
}

I cant tell what is different?

Body:
This Error occurs when you provide an invalid ObjectId

The mongo _id is 12-byte BSON type ObjectId

here I have added if-else to verify whether the category_id is a Valid ObjectId or not, if it's then do the Operation, else throw an custom error message

When you are Using mongoose-autoincrement-package
it uses Number type instead of ObjectId and
the ref property type will be a Number instead of ObjectId, so when you declare the references  remember to change the reference property's type to Number instead of ObjectId if the referenced model is also using the plugin.

var ThemeSchema = new Schema({
    name : String,
    category : {type: Number, ref: 'ControlCategorySchema'}
});

function addTheme(req, res, category_id) {
    console.log("category id " + category_id);
    var cond = mongoose.Types.ObjectId.isValid(category_id)

    if (cond) {
        ControlCategory.findById(category_id, function (err, category_found) {
            if (err)
                res.send(err)

            req.body.category = category_found;
            console.log("body is " + req.body.category);
            var new_theme = new Theme();
            new_theme.name = req.body.name;
            new_theme.category = req.body.category._id;
            new_theme.save(function (err, theme) {
                if (err)
                    res.send(err);
                console.log(theme);
                res.json(theme);
            });
        });
    } else {
        res.json({ message: 'not a valid object id' });
    }
}


问:
map/set iterator not incrementable error when I execute the function
The error occurs when I execute the following functions:
I have checked the iterator, it should be no problem, I think the problem should be the insert and erase but I don't how to fix it

```<code>     void addNewUser(Records *record) {

         if (mapUser.find(record->getUserName()) != mapUser.end())
             cout << "User Name has been used already!!! " << endl;
         else
            mapUser.insert(pair<string, Records*>(record->getUserName(), record));

     }

     void deleteUser(string key) {
         map<string, Records*>::iterator it = mapUser.find(key);
         map<string, Records*>::iterator end = mapUser.end();

         if (it != end) {
            delete it->second;
            it->second = NULL;
         }
         mapUser.erase(it);

     }
</code>```

The main:

```<code>int main() {

DataBaseOperation *a = new DataBaseOperation();

DataBaseOperation *a = new DataBaseOperation();

Records *r = new Records(10, 1, "Ben", "Ben Li");
Records *s = new Records(1, 1, "ken", "Ken Lee");

a->addNewUser(r);
a->deleteUser(s->getUserName());

a->displayAll();

delete a;

system("pause");
return 0;}
</code>```

if I call the function like this in my main function:

```<code>a->displayAll();
cout << endl;

a->addNewUser(r);
cout << endl;

a->displayAll();
cout << endl;

a->deleteUser(s->getUserName());
a->displayAll();
</code>```

it will work fine:

but if I want to call the functions(addNewUser and deleteUser) individually and when I execute "a->addNewUser(s);" in main, the error will occur.

Can anyone help me?

答:
You should call erase only if it points to an actual element (=not end).

The iterator pos must be valid and dereferenceable. Thus the end() iterator (which is valid, but is not dereferencable) cannot be used as a value for pos.

http://en.cppreference.com/w/cpp/container/map/erase

Just move the erase inside the if above.

Body:
The error occurs when I execute the following functions:
I have checked the iterator, it should be no problem, I think the problem should be the insert and erase but I don't how to fix it

     void addNewUser(Records *record) {

         if (mapUser.find(record->getUserName()) != mapUser.end())
             cout << "User Name has been used already!!! " << endl;
         else
            mapUser.insert(pair<string, Records*>(record->getUserName(), record));

     }

     void deleteUser(string key) {
         map<string, Records*>::iterator it = mapUser.find(key);
         map<string, Records*>::iterator end = mapUser.end();

         if (it != end) {
            delete it->second;
            it->second = NULL;
         }
         mapUser.erase(it);

     }

The main:

int main() {

DataBaseOperation *a = new DataBaseOperation();

DataBaseOperation *a = new DataBaseOperation();

Records *r = new Records(10, 1, "Ben", "Ben Li");
Records *s = new Records(1, 1, "ken", "Ken Lee");

a->addNewUser(r);
a->deleteUser(s->getUserName());

a->displayAll();

delete a;

system("pause");
return 0;}

if I call the function like this in my main function:

a->displayAll();
cout << endl;

a->addNewUser(r);
cout << endl;

a->displayAll();
cout << endl;

a->deleteUser(s->getUserName());
a->displayAll();

it will work fine:

but if I want to call the functions(addNewUser and deleteUser) individually and when I execute "a->addNewUser(s);" in main, the error will occur.

Can anyone help me?


问:
Apollo GraphQL - Multiple components sharing the same mutation
I am using react-apollo and creating an app that needs to sync the entire local data store of the application once every X seconds.

So essentially, imagine <input1 /> and <input2 /> both need to be synced to the server periodically.

```<code><App>
   <input1 />
   <input2 />
</App>
</code>```

Which component should GraphQL wrap in order to achieve the sync?

Is it possible to use a graphql mutation in a redux action so that the mutation can occur upon the dispatch of an action?

答:
I suggest you add a subscription to the whole app.
It could work like this:

When you open the page you query for the values of <input1 /><input2 />

Then you subscribe to the server via the subscribeToMore function of the query.

The server has a loop which publishes each x seconds the data for the subscription. Which would be the data for <input1 /><input2 />

Then you have some possibilities, you could update the store by hand or you add an id to the inputs and let the apollo client do the work via dataIdFromObject.

Body:
I am using react-apollo and creating an app that needs to sync the entire local data store of the application once every X seconds.

So essentially, imagine <input1 /> and <input2 /> both need to be synced to the server periodically.

<App>
   <input1 />
   <input2 />
</App>

Which component should GraphQL wrap in order to achieve the sync?

Is it possible to use a graphql mutation in a redux action so that the mutation can occur upon the dispatch of an action?


问:
How to play iptv channel using VLC player instead max player?
I have referred https://github.com/AlexanderSofronov/iptv.example for implementing iptv channels which is working fine, but it plays video using max player. But I want to play iptv channel using VLC player in application.

How can I integrate VLC player instead max player?

答:
i think you need to reset your default app settings. in case if you dont know how to reset it go for the link  below.

Reset android default apps


问:
How to make EditText trigger parent view scroll?
Multiline EditText in a ScrollView. Edittext can scroll if its content overflows. the direction is the same.
How to let EditText trigger ScollView scroll when it can not scroll(hit top bottom or not much content)? </p>

答:
```<code> private final void focusOnView(){
    your_scrollview.post(new Runnable() {
        @Override
        public void run() {
            your_scrollview.scrollTo(0, your_EditBox.getBottom());
        }
    });
}
</code>```


问:
cmake and make build reproductibility
I'm evaluating the use of cmake to generate makefile for embedded FW. The cmakelists.txt will be shared in the team.

Can you confirm the makefile cannot be shared between different computers ?

Is this still true if project path is identical on both computers ?

Using cmake makefile generation and same version of compiler, will the generated binary be the same on all computers ?

Is this the same behavior as a makefile shared in the project ?

答:
In theory, if your project path, toolchain path, toolchain version, and the path to every external library used by the project is the same on different computers, then you can move the generated Makefile between them without regenerating.  When you run make it might detect that things have changed and try to rerun CMake, though.  I'm not sure why you'd want to do this, however, it seems like kind of bad practice.

If the compilers, libraries, and code are the same, the same code will get generated (unless your compiler has some sort of bug).

Title: cmake and make build reproductibility