ISNULL within a subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ISNULL within a subquery

Author

Topic

SJHudson
Starting Member

5 Posts

Posted - 2009-06-18 : 06:58:42

Hi There,
I don't know if this is the correct section of the forum to be posting this (please don't flame me if I'm wrong!) I've only been working with SQL for 6 months so I guess that makes me 'New'

I'm having some difficulty using the ISNULL function within a sub- query. I'm using the below code to find the most recent payment received date that is less that a 100 days before the payment due date.

(SELECT TOP 1 pay.received_date
FROM vw_payment pay
WHERE regin.internalid = pay.fk_registered_in_id
AND pay.received_date >
(SELECT TOP 1 lev.due_date
FROM vw_levied_fee lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100
ORDER BY pay.received_date DESC) AS PaymentReceivedDate

This works fine, however some records don't have a received date and use a DD claim date instead. This would be found using:

(SELECT TOP 1 pay.DD_claim_date
FROM vw_payment pay
WHERE regin.internalid = pay.fk_registered_in_id
AND pay.received_date >
(SELECT TOP 1 lev.due_date
FROM vw_levied_fee lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100
ORDER BY pay.dd_claim_date DESC) AS PaymentReceivedDate

However, I'm not sure how if I can use the ISNULL function to look for the received date first, then the DD claim date if the received date value is null.

Any ideas?

Thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 08:23:04

I'm guessing from your subqueries that the received_date and the claim_date are not in the same row in your data so I don't think you can use a simple ISNULL().

It would probably be a better idea to rewrite this sub-query into a derived table and then JOIN it. Better performance and better readability.

Why not post the whole query and some sample data with required output. Folks here will optimise the hell out of it for you.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-18 : 08:24:39

maybe (you left a received_date in your second query)

SELECT TOP 1 received_date = coalesce(pay.received_date, pay.dd_claim_date)
FROM vw_payment pay
WHERE regin.internalid = pay.fk_registered_in_id
AND coalesce(pay.received_date, pay.dd_claim_date) >
(SELECT TOP 1 lev.due_date
FROM vw_levied_fee lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100
ORDER BY coalesce(pay.received_date, pay.dd_claim_date) DESC

or

(SELECT TOP 1 pay.received_date
FROM vw_payment pay
WHERE regin.internalid = pay.fk_registered_in_id
AND pay.received_date >
(SELECT TOP 1 lev.due_date
FROM vw_levied_fee lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100
ORDER BY pay.received_date DESC
union all
SELECT TOP 1 pay.DD_claim_date
FROM vw_payment pay
WHERE regin.internalid = pay.fk_registered_in_id
AND pay.dd_claim_date >
(SELECT TOP 1 lev.due_date
FROM vw_levied_fee lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100
ORDER BY pay.dd_claim_date DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SJHudson
Starting Member

5 Posts

Posted - 2009-06-18 : 10:42:17

quote:
Originally posted by nr

SELECT TOP 1 received_date = coalesce(pay.received_date, pay.dd_claim_date)
FROM vw_payment pay
WHERE regin.internalid = pay.fk_registered_in_id
AND coalesce(pay.received_date, pay.dd_claim_date) >
(SELECT TOP 1 lev.due_date
FROM vw_levied_fee lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100
ORDER BY coalesce(pay.received_date, pay.dd_claim_date) DESC

Thanks for that, that's excellent!

Charlie - I can't post any sample data as some of it is sensitive. I can however post the whole query if anyone is interested in giving me some pointers re optimization?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 11:01:06

Can you make up some fake sample data and expected output?

Or sanitise your original data somehow?

changing those sub-queries into derived tables will speed up your code a lot.

Feel free to post the SQL code.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SJHudson
Starting Member

5 Posts

Posted - 2009-06-18 : 11:20:20

ok Charlie, here goes - the full query in all it's inefficient glory! Please be kind in your assessments and bear in mind that I've only been working with SQL for 6 months and am completely unqualified and pretty much self taught.

Please note that the '#' is used instead of information that is deemed to be senstive.

SELECT DISTINCT ind.scr_number,
CASE (SELECT TOP 1 pay1.fk_method
FROM vw_payment_# pay1
WHERE pay1.fk_registered_in_id = regin.internalid
ORDER BY pay1.received_date DESC)
WHEN 2
THEN 'Yes'
ELSE 'No'
END AS 'LastPaymentDirectDebit?',

(SELECT TOP 1 pers.progress_set_date
FROM dbo.vw_ar_personal_details_# pers
WHERE pers.fk_app_reg_id = app.internalid
AND pers.fk_progress = 16
ORDER BY app.date_received DESC) AS referredtocommitteedate,

(SELECT TOP 1 c.sent_or_received_date
FROM vw_contact_# c
INNER JOIN vw_material_# m
ON c.internalid = m.fk_contact_id
WHERE ind.internalid = c.fk_ext_addr_individual
AND (m.name LIKE '%32%'
OR m.name LIKE '%finance%/%')
ORDER BY c.sent_or_received_date DESC) AS date32dayremindersent,

(SELECT TOP 1 c1.sent_or_received_date
FROM vw_contact_# c1
INNER JOIN vw_material_# m1
ON c1.internalid = m1.fk_contact_id
WHERE ind.internalid = c1.fk_ext_addr_individual
AND m1.name LIKE '%14%reminder%'
AND c1.sent_or_received_date > (SELECT TOP 1 c.sent_or_received_date
FROM vw_contact_# c
INNER JOIN vw_material_# m
ON c.internalid = m.fk_contact_id
WHERE ind.internalid = c.fk_ext_addr_individual
AND (m.name LIKE '%32%'
OR m.name LIKE '%finance%/%')
ORDER BY c.sent_or_received_date DESC)
ORDER BY c1.sent_or_received_date) AS date14dayremindersent,

(SELECT TOP 1 c2.sent_or_received_date
FROM vw_contact_# c2
INNER JOIN vw_material_# m2
ON c2.internalid = m2.fk_contact_id
WHERE ind.internalid = c2.fk_ext_addr_individual
AND m2.name LIKE '%fee%overdue%'
AND c2.sent_or_received_date > (SELECT TOP 1 lev.due_date
FROM vw_levied_fee_# lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC)
ORDER BY c2.sent_or_received_date DESC) AS date7dayoverduesent,

(SELECT TOP 1 lev.due_date
FROM vw_levied_fee_# lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) AS duedate,
(SELECT TOP 1 pers1.progress_set_date
FROM dbo.vw_ar_personal_details_# pers1
WHERE pers1.fk_app_reg_id = app.internalid
AND pers1.fk_progress = 26
ORDER BY app.date_received DESC) AS requestvoluntaryremoval,

(SELECT TOP 1 ris.date_from
FROM vw_registered_in_status_# ris
INNER JOIN vw_registered_in_# regin
ON regin.internalid = ris.fk_registered_in
WHERE regin.fk_individual_id = ind.internalid
AND ris.fk_status = 9
ORDER BY ris.date_from DESC) AS Expr1,

(SELECT TOP 1 Isnull(pay.dd_claim_date,pay.received_date)
FROM vw_payment_# pay
WHERE regin.internalid = pay.fk_registered_in_id
AND (pay.received_date > (SELECT TOP 1 lev.due_date
FROM vw_levied_fee_# lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100 OR pay.dd_claim_date > (SELECT TOP 1 lev.due_date
FROM vw_levied_fee_# lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC) - 100)
ORDER BY pay.received_date DESC) AS paymentreceiveddate

FROM dbo.vw_individual_access_# ind
INNER JOIN dbo.vw_registered_in_# regin
ON ind.internalid = regin.fk_individual_id
INNER JOIN dbo.vw_payment_# pay
ON regin.internalid = pay.fk_registered_in_id
INNER JOIN dbo.vw_levied_fee_# lev1
ON regin.internalid = lev1.fk_registered_in_id
LEFT OUTER JOIN dbo.vw_app_reg_form_# app
ON ind.internalid = app.fk_individualid
AND app.internalid IN (SELECT TOP 1 app1.internalid FROM vw_app_reg_form_# app1
WHERE app1.fk_individualid = ind.internalid
RDER BY app1.date_received)

WHERE (@startdate IS NULL
OR @startdate <= (SELECT TOP 1 lev.due_date
FROM vw_levied_fee_# lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC))
AND (@enddate IS NULL
OR @enddate >= (SELECT TOP 1 lev.due_date
FROM vw_levied_fee_# lev
WHERE lev.fk_registered_in_id = regin.internalid
ORDER BY lev.due_date DESC))
AND ((SELECT TOP 1 app1.fk_council
FROM vw_app_reg_form_# app1
WHERE app1.fk_individualid = ind.internalid
ORDER BY app1.date_received) = 4)

Finding dummy data is proving to be a bit of a challenge - will it make it that much easier to help then?

Thank you in advance guys

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 11:36:16

Formatted a little: No changes yet:


SELECT DISTINCT
	ind.scr_number

	, CASE (
		SELECT TOP 1 pay1.fk_method
		FROM vw_payment_# pay1
		WHERE pay1.fk_registered_in_id = regin.internalid
		ORDER BY
			pay1.received_date DESC
		)
		WHEN 2 THEN 'Yes'
		ELSE 'No'
	END AS 'LastPaymentDirectDebit?'

	, ( SELECT TOP 1 pers.progress_set_date
		FROM dbo.vw_ar_personal_details_# pers
		WHERE
			pers.fk_app_reg_id = app.internalid 
			AND pers.fk_progress = 16
		ORDER BY
			app.date_received DESC
		) AS referredtocommitteedate

	, ( SELECT TOP 1 c.sent_or_received_date 
		FROM
			vw_contact_# c 
			INNER JOIN vw_material_# m ON c.internalid = m.fk_contact_id
		WHERE
			ind.internalid = c.fk_ext_addr_individual 
			AND (m.name LIKE '%32%' OR m.name LIKE '%finance%/%')
		ORDER BY
			c.sent_or_received_date DESC
		) AS date32dayremindersent

	, ( SELECT TOP 1 c1.sent_or_received_date 
		FROM
			vw_contact_# c1 
			INNER JOIN vw_material_# m1 ON c1.internalid = m1.fk_contact_id 
		WHERE
			ind.internalid = c1.fk_ext_addr_individual 
			AND m1.name LIKE '%14%reminder%' 
			AND c1.sent_or_received_date > (
				SELECT TOP 1 c.sent_or_received_date 
				FROM
					vw_contact_# c 
					INNER JOIN vw_material_# m ON c.internalid = m.fk_contact_id 
				WHERE
					ind.internalid = c.fk_ext_addr_individual 
					AND (m.name LIKE '%32%' OR m.name LIKE '%finance%/%') 
				ORDER BY
					c.sent_or_received_date DESC
				) 
		ORDER BY
			c1.sent_or_received_date
		) AS date14dayremindersent

	, ( SELECT TOP 1 c2.sent_or_received_date 
		FROM
			vw_contact_# c2 
			INNER JOIN vw_material_# m2 ON c2.internalid = m2.fk_contact_id 
		WHERE
			ind.internalid = c2.fk_ext_addr_individual 
			AND m2.name LIKE '%fee%overdue%' 
			AND c2.sent_or_received_date > (
				SELECT TOP 1 lev.due_date 
				FROM
					vw_levied_fee_# lev 
				WHERE
					lev.fk_registered_in_id = regin.internalid 
				ORDER BY
					lev.due_date DESC
				) 
		ORDER BY
			c2.sent_or_received_date DESC
		) AS date7dayoverduesent

	, ( SELECT TOP 1 lev.due_date 
		FROM
			vw_levied_fee_# lev 
		WHERE
			lev.fk_registered_in_id = regin.internalid 
		ORDER BY
			lev.due_date DESC
		) AS duedate

	, ( SELECT TOP 1 pers1.progress_set_date 
		FROM
			dbo.vw_ar_personal_details_# pers1 
		WHERE
			pers1.fk_app_reg_id = app.internalid 
			AND pers1.fk_progress = 26 
		ORDER BY
			app.date_received DESC
		) AS requestvoluntaryremoval

	, ( SELECT TOP 1 ris.date_from 
		FROM
			vw_registered_in_status_# ris 
			INNER JOIN vw_registered_in_# regin ON regin.internalid = ris.fk_registered_in 
		WHERE
			regin.fk_individual_id = ind.internalid 
			AND ris.fk_status = 9 
		ORDER BY
			ris.date_from DESC
		) AS Expr1

	, ( SELECT TOP 1 Isnull(pay.dd_claim_date,pay.received_date) 
		FROM
			vw_payment_# pay 
		WHERE
			regin.internalid = pay.fk_registered_in_id 
			AND (
				pay.received_date > (
					SELECT TOP 1 lev.due_date 
					FROM
						vw_levied_fee_# lev 
					WHERE
						lev.fk_registered_in_id = regin.internalid 
					ORDER BY
						lev.due_date DESC
					) - 100
				OR pay.dd_claim_date > (
					SELECT TOP 1 lev.due_date 
					FROM
						vw_levied_fee_# lev 
					WHERE
						lev.fk_registered_in_id = regin.internalid 
					ORDER BY
						lev.due_date DESC
					) - 100
				) 
		ORDER BY
			pay.received_date DESC
		) AS paymentreceiveddate 

FROM
	dbo.vw_individual_access_# ind 
	INNER JOIN dbo.vw_registered_in_# regin ON ind.internalid = regin.fk_individual_id 
	INNER JOIN dbo.vw_payment_# pay ON regin.internalid = pay.fk_registered_in_id 
	INNER JOIN dbo.vw_levied_fee_# lev1 ON regin.internalid = lev1.fk_registered_in_id 
	LEFT OUTER JOIN dbo.vw_app_reg_form_# app ON
		ind.internalid = app.fk_individualid 
		AND app.internalid IN (
			SELECT TOP 1
				app1.internalid
			FROM
				vw_app_reg_form_# app1 
			WHERE
				app1.fk_individualid = ind.internalid 
			ORDER BY app1.date_received
			) 
WHERE
	(
		@startdate IS NULL 
		OR @startdate <= (
			SELECT TOP 1 lev.due_date 
			FROM
				vw_levied_fee_# lev 
			WHERE
				lev.fk_registered_in_id = regin.internalid 
			ORDER BY
				lev.due_date DESC
			)
	) 
	AND (
		@enddate IS NULL 
		OR @enddate >= (
			SELECT TOP 1 lev.due_date 
			FROM
				vw_levied_fee_# lev 
			WHERE
				lev.fk_registered_in_id = regin.internalid 
			ORDER BY
				lev.due_date DESC
			)
	) 
	AND (
		(
			SELECT TOP 1 app1.fk_council 
			FROM
				vw_app_reg_form_# app1 
			WHERE
				app1.fk_individualid = ind.internalid 
			ORDER BY
				app1.date_received
		) = 4
	)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 11:51:12

This section is repeated a lot!


SELECT TOP 1 lev.due_date 
FROM
	vw_levied_fee_# lev 
WHERE
	lev.fk_registered_in_id = regin.internalid 
ORDER BY
	lev.due_date DESC

This is to get the latest lev.[due_date] per lev.[fk_registered_in_id]?

Does this sql do the same thing (using a derived table)? I can't test obviously


SELECT DISTINCT
	ind.scr_number

	, CASE (
		SELECT TOP 1 pay1.fk_method
		FROM vw_payment_# pay1
		WHERE pay1.fk_registered_in_id = regin.internalid
		ORDER BY
			pay1.received_date DESC
		)
		WHEN 2 THEN 'Yes'
		ELSE 'No'
	END AS 'LastPaymentDirectDebit?'

	, ( SELECT TOP 1 pers.progress_set_date
		FROM dbo.vw_ar_personal_details_# pers
		WHERE
			pers.fk_app_reg_id = app.internalid 
			AND pers.fk_progress = 16
		ORDER BY
			app.date_received DESC
		) AS referredtocommitteedate

	, ( SELECT TOP 1 c.sent_or_received_date 
		FROM
			vw_contact_# c 
			INNER JOIN vw_material_# m ON c.internalid = m.fk_contact_id
		WHERE
			ind.internalid = c.fk_ext_addr_individual 
			AND (m.name LIKE '%32%' OR m.name LIKE '%finance%/%')
		ORDER BY
			c.sent_or_received_date DESC
		) AS date32dayremindersent

	, ( SELECT TOP 1 c1.sent_or_received_date 
		FROM
			vw_contact_# c1 
			INNER JOIN vw_material_# m1 ON c1.internalid = m1.fk_contact_id 
		WHERE
			ind.internalid = c1.fk_ext_addr_individual 
			AND m1.name LIKE '%14%reminder%' 
			AND c1.sent_or_received_date > (
				SELECT TOP 1 c.sent_or_received_date 
				FROM
					vw_contact_# c 
					INNER JOIN vw_material_# m ON c.internalid = m.fk_contact_id 
				WHERE
					ind.internalid = c.fk_ext_addr_individual 
					AND (m.name LIKE '%32%' OR m.name LIKE '%finance%/%') 
				ORDER BY
					c.sent_or_received_date DESC
				) 
		ORDER BY
			c1.sent_or_received_date
		) AS date14dayremindersent

	, ( SELECT TOP 1 c2.sent_or_received_date 
		FROM
			vw_contact_# c2 
			INNER JOIN vw_material_# m2 ON c2.internalid = m2.fk_contact_id 
		WHERE
			ind.internalid = c2.fk_ext_addr_individual 
			AND m2.name LIKE '%fee%overdue%' 
			AND c2.sent_or_received_date > levD.[latest_date] 
		ORDER BY
			c2.sent_or_received_date DESC
		) AS date7dayoverduesent

	, levD.[latest_date] AS duedate

	, ( SELECT TOP 1 pers1.progress_set_date 
		FROM
			dbo.vw_ar_personal_details_# pers1 
		WHERE
			pers1.fk_app_reg_id = app.internalid 
			AND pers1.fk_progress = 26 
		ORDER BY
			app.date_received DESC
		) AS requestvoluntaryremoval

	, ( SELECT TOP 1 ris.date_from 
		FROM
			vw_registered_in_status_# ris 
			INNER JOIN vw_registered_in_# regin ON regin.internalid = ris.fk_registered_in 
		WHERE
			regin.fk_individual_id = ind.internalid 
			AND ris.fk_status = 9 
		ORDER BY
			ris.date_from DESC
		) AS Expr1

	, ( SELECT TOP 1 Isnull(pay.dd_claim_date,pay.received_date) 
		FROM
			vw_payment_# pay 
		WHERE
			regin.internalid = pay.fk_registered_in_id 
			AND (
				pay.received_date > levD.[latest_date] - 100
				OR pay.dd_claim_date > levD.[latest_date] - 100
				) 
		ORDER BY
			pay.received_date DESC
		) AS paymentreceiveddate 

FROM
	dbo.vw_individual_access_# ind 
	INNER JOIN dbo.vw_registered_in_# regin ON ind.internalid = regin.fk_individual_id 
	INNER JOIN dbo.vw_payment_# pay ON regin.internalid = pay.fk_registered_in_id 
	INNER JOIN dbo.vw_levied_fee_# lev1 ON regin.internalid = lev1.fk_registered_in_id 
	LEFT OUTER JOIN dbo.vw_app_reg_form_# app ON
		ind.internalid = app.fk_individualid 
		AND app.internalid IN (
			SELECT TOP 1
				app1.internalid
			FROM
				vw_app_reg_form_# app1 
			WHERE
				app1.fk_individualid = ind.internalid 
			ORDER BY app1.date_received
			)

	LEFT JOIN (
		SELECT
			[vw_levied_fee_#] AS [internalId]
			, MAX([due_date]) AS [latest_Date]
		FROM
			vw_levied_fee_# lev
		GROUP BY
			[vw_levied_fee_#]
		)
		levD ON levD.[internalId] = regin.[internalID]
WHERE
	(
		@startdate IS NULL 
		OR @startdate <= levD.[latest_date]
	) 
	AND (
		@enddate IS NULL 
		OR @enddate >= levD.[latest_date]
	) 
	AND (
		(
			SELECT TOP 1 app1.fk_council 
			FROM
				vw_app_reg_form_# app1 
			WHERE
				app1.fk_individualid = ind.internalid 
			ORDER BY
				app1.date_received
		) = 4
	)

/* Derived Tables

SELECT
	[vw_levied_fee_#] AS [internalId]
	, MAX([due_date]) AS [latest_Date]
FROM
	vw_levied_fee_# lev
GROUP BY
	[vw_levied_fee_#]

*/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SJHudson
Starting Member

5 Posts

Posted - 2009-06-19 : 05:09:29

Hi Charlie,

Sorry, I'm not really sure what a derived table is! I'm going to google for a tutorial and see if I can make some sense of it.

Thank's a lot for your help so far - once I've worked it out I'll use your code and let you know if it's any quicker, as the report is sluggish at the moment

Regards

Sam

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-19 : 08:42:26

well the derived table should certainly be a lot faster!

What's happening in your code at the moment is:

For every ROW in the result set from:



dbo.vw_individual_access_# ind 
	INNER JOIN dbo.vw_registered_in_# regin ON ind.internalid = regin.fk_individual_id 
	INNER JOIN dbo.vw_payment_# pay ON regin.internalid = pay.fk_registered_in_id 
	INNER JOIN dbo.vw_levied_fee_# lev1 ON regin.internalid = lev1.fk_registered_in_id 
	LEFT OUTER JOIN dbo.vw_app_reg_form_# app ON
		ind.internalid = app.fk_individualid 
		AND app.internalid IN (
			SELECT TOP 1
				app1.internalid
			FROM
				vw_app_reg_form_# app1 
			WHERE
				app1.fk_individualid = ind.internalid 
			ORDER BY app1.date_received
			)

You are then doing a bunch of sub-queries. If there are a 1000 rows then you will be performing 1000x the number of sub-queries. Even with cached plans that's a lot of work. Performance is terrible.

The derived table is different. What it does is to do 1 query for all your employee at the same time rather than 1 by 1


SELECT
			[vw_levied_fee_#] AS [internalId]
			, MAX([due_date]) AS [latest_Date]
		FROM
			vw_levied_fee_# lev
		GROUP BY
			[vw_levied_fee_#]

Which gets the latest [due_date] per [vw_levied_fee_#] from vw_levied_fee_#. Think of it as just another table which you can JOIN on as you normally would. That way for 1000's of employees you only need to make 1 set based calculation.

I think you can probably re-write all your sub-queries this way.

Good luck -- learning how to use derived tables is absolutely worth the mental effort .

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SJHudson
Starting Member

5 Posts

Posted - 2009-06-19 : 09:56:56

Charlie, You're a legend mate!

Previous time for report to run for all individuals on the database (circa 8500) was 1 minute 35 seconds .............. New time 19 seconds!

Loving the derived tables!

Thanks
Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources