Solved

Warning: Fatal error 3624 with massive union query

Posted on 2007-11-27
5
1,032 Views
Last Modified: 2012-08-14
We have 3 SQL Server 2005 setups for development, test and live.  The database in question resides on all three servers.  One of our report writers has a massive union query that runs against this database.  The query works in dev, works in test but fails in live with the following fatal error (something interesting to note, when the I break the union up and run the top half and the bottom half seperate, it runs fine... which leads me to believe that it maybe a configuration issue since it works on the dev and test boxes):

Location:       xxchng.cpp:436
Expression:       pxteExchange->FMarkTypeStart()
SPID:             52
Process ID:       2000
Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 3624 occurred at Nov 27 2007 10:20AM. Note the error and time, and contact your system administrator.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I'm going to include his SQL for reference.
SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', SUM(credit_amount) AS unit_amount, 0 AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') AND credit_type_code = 'H'

	AND an.unit_code != 'ALUM'

	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')+1 ELSE YEAR('10/31/2007') END 

	AND credit_date <= ('10/31/2007')

	GROUP BY MONTH(credit_date), YEAR(credit_date)
 

UNION

	SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, COUNT(DISTINCT td.transaction_id)AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') 

	AND an.unit_code != 'ALUM'	

	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')+1 ELSE YEAR('10/31/2007') END

	AND credit_date <= ('10/31/2007')

	GROUP BY MONTH(credit_date), YEAR(credit_date) 
 
 

UNION 

	SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', SUM(credit_amount) AS unit_amount, 0 AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') AND credit_type_code = 'H'

	AND an.unit_code != 'ALUM'

	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END

	AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

	GROUP BY MONTH(credit_date), YEAR(credit_date)
 

UNION 

	SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, COUNT(DISTINCT td.transaction_id)AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') 

	AND an.unit_code != 'ALUM'	

	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END

	AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

	GROUP BY MONTH(credit_date), YEAR(credit_date)
 
 

UNION
 

-- Unique Donor Counts Running Totals

	SELECT 

			'77' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('07/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'77' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('07/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 

UNION

	SELECT 

			'77' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('07/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 

UNION

	SELECT 

			'78' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('08/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'78' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('08/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 

UNION

	SELECT 

			'78' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('08/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 

UNION

	SELECT 

			'79' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('09/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'79' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('09/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 

UNION

	SELECT 

			'79' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('09/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 

UNION

	SELECT 

			'710' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('10/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'710' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('10/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

UNION

	SELECT 

			'710' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('10/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 

UNION

	SELECT 

			'711' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('11/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'711' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('11/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

UNION

	SELECT 

			'711' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('11/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 

UNION

	SELECT 

			'712' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('12/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'712' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('12/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

UNION

	SELECT 

			'712' AS 'Month'

			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('12/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 

UNION

	SELECT 

			'71' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('01/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'71' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('01/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 

UNION

	SELECT 

			'72' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date >= (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	

		AND credit_date <  (CONVERT(datetime,('03/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'72' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date >= (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <  (CONVERT(datetime,('03/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

UNION

	SELECT 

			'73' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('03/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'73' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2  ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('03/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

UNION

	SELECT 

			'74' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('04/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'74' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('04/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

UNION

	SELECT 

			'75' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('05/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'75' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

						 AND (CONVERT(datetime,('05/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

UNION

	SELECT 

			'76' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  

						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	

						 AND (CONVERT(datetime,('06/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

		AND credit_date <= ('10/31/2007')

UNION

	SELECT 

			'76' AS 'Month'

			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'

			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count

	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p

	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 

		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'

		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))

				AND (CONVERT(datetime,('06/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))

		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

Open in new window

0
Comment
Question by:IUFITS
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
0
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
Comment Utility
Not sure but here is a theory;

There is a limit of 256 tables in a single statement.

Multiplying 36 unions * 5 tables per select  + 4 worktables for GROUP BY's + any number of worktables for sorting may put you close to that limit.

Differences in dev/test/prod may depend on # of CPU's and how the query is run in parallel.

Looks like your select statements are exclusive of one another so you might as well use UNION ALL to eliminate an intermediate sort and work table.

You might also try OPTION (MAXDOP 1)  to see if that makes any difference.
0
 
LVL 3

Author Comment

by:IUFITS
Comment Utility
Adding the OPTION (MAXDOP 1) fixed the issue.  Am I correct that assuming that the parallization was causing the number of tables touched to rise and that setting this option decreased that number running at the same time?
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
Not as far as I can discover. Parallel operations require separate streams to be combined, but nothing I can find indicates these contribute to part of the 256 table limit.   I suspect this is a bug and MAXDOP 1 is a workaround.

You might set MAXDOP to 1/2 the # of CPU's rather than 1 to see some benefit of parallelism.

0
 
LVL 3

Author Comment

by:IUFITS
Comment Utility
That's a great suggestion.  I also tried this testing and it did work.  Thank you again for the prompt response, you saved me much time researching.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now