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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query taking to long too load

Author  Topic 

giteshshah
Starting Member

5 Posts
Posted - 2013-04-11 : 20:09:28
Hi Guys,

This is my first post on SQLTeam.com. So please ignore if my post is not in the correct section of the website.

I have got a scenario where my query is taking too long too load.

I have got three tables.

1. RR_PublicationSubCategories(Screenshot of tables below)


2. CMS_User(Screenshot of tables below)


3. RR_Professions(Screenshot of tables below)


Output of my report should be as below:
http://demo.kudosweb.com/Temp-SQL-Forum/report.csv

Below is my query that I have used to achieve the above report:

SELECT 
	prof.[ProfessionName] AS Professions
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11129%') AND (sub.PublicationSubCategoriesID LIKE '%11129%')) THEN 1 END) AS 'Allergy'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11130%') AND (sub.PublicationSubCategoriesID LIKE '%11130%')) THEN 1 END) AS 'Bone Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11131%') AND (sub.PublicationSubCategoriesID LIKE '%11131%')) THEN 1 END) AS 'Dermatology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11133%') AND (sub.PublicationSubCategoriesID LIKE '%11133%')) THEN 1 END) AS 'General Practice'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11134%') AND (sub.PublicationSubCategoriesID LIKE '%11134%')) THEN 1 END) AS 'Pain Management'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11135%') AND (sub.PublicationSubCategoriesID LIKE '%11135%')) THEN 1 END) AS 'Smoking Cessation'	
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11137%') AND (sub.PublicationSubCategoriesID LIKE '%11137%')) THEN 1 END) AS 'Mens Sexual Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11138%') AND (sub.PublicationSubCategoriesID LIKE '%11138%')) THEN 1 END) AS 'Womens Sexual Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11139%') AND (sub.PublicationSubCategoriesID LIKE '%11139%')) THEN 1 END) AS 'Asian Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11140%') AND (sub.PublicationSubCategoriesID LIKE '%11140%')) THEN 1 END) AS 'Eye Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11141%') AND (sub.PublicationSubCategoriesID LIKE '%11141%')) THEN 1 END) AS 'Foot & Ankle'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11142%') AND (sub.PublicationSubCategoriesID LIKE '%11142%')) THEN 1 END) AS 'Health Manager'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11143%') AND (sub.PublicationSubCategoriesID LIKE '%11143%')) THEN 1 END) AS 'Hearing'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11144%') AND (sub.PublicationSubCategoriesID LIKE '%11144%')) THEN 1 END) AS 'Maori Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11145%') AND (sub.PublicationSubCategoriesID LIKE '%11145%')) THEN 1 END) AS 'Natural Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11146%') AND (sub.PublicationSubCategoriesID LIKE '%11146%')) THEN 1 END) AS 'Pacific Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11147%') AND (sub.PublicationSubCategoriesID LIKE '%11147%')) THEN 1 END) AS 'Pharmacy'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11148%') AND (sub.PublicationSubCategoriesID LIKE '%11148%')) THEN 1 END) AS 'Patient Psychology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11149%') AND (sub.PublicationSubCategoriesID LIKE '%11149%')) THEN 1 END) AS 'Rehabilitation'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11150%') AND (sub.PublicationSubCategoriesID LIKE '%11150%')) THEN 1 END) AS 'Internal Medicine'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11151%') AND (sub.PublicationSubCategoriesID LIKE '%11151%')) THEN 1 END) AS 'Cardiology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11152%') AND (sub.PublicationSubCategoriesID LIKE '%11152%')) THEN 1 END) AS 'Diabetes & Obesity'	
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11156%') AND (sub.PublicationSubCategoriesID LIKE '%11156%')) THEN 1 END) AS 'Inflammatory Bowel Disease'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11157%') AND (sub.PublicationSubCategoriesID LIKE '%11157%')) THEN 1 END) AS 'Gastroenterology'			
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11161%') AND (sub.PublicationSubCategoriesID LIKE '%11161%')) THEN 1 END) AS 'Nephrology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11162%') AND (sub.PublicationSubCategoriesID LIKE '%11162%')) THEN 1 END) AS 'Respiratory'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11163%') AND (sub.PublicationSubCategoriesID LIKE '%11163%')) THEN 1 END) AS 'Rheumatology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11164%') AND (sub.PublicationSubCategoriesID LIKE '%11164%')) THEN 1 END) AS 'Geriatrics'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11165%') AND (sub.PublicationSubCategoriesID LIKE '%11165%')) THEN 1 END) AS 'Sports Medicine'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11166%') AND (sub.PublicationSubCategoriesID LIKE '%11166%')) THEN 1 END) AS 'Haematology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11167%') AND (sub.PublicationSubCategoriesID LIKE '%11167%')) THEN 1 END) AS 'Lymphoma/Leukaemia'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11168%') AND (sub.PublicationSubCategoriesID LIKE '%11168%')) THEN 1 END) AS 'Multiple Myeloma'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11169%') AND (sub.PublicationSubCategoriesID LIKE '%11169%')) THEN 1 END) AS 'Hepatitis'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11170%') AND (sub.PublicationSubCategoriesID LIKE '%11170%')) THEN 1 END) AS 'HIV'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11171%') AND (sub.PublicationSubCategoriesID LIKE '%11171%')) THEN 1 END) AS 'Infectious Diseases'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11172%') AND (sub.PublicationSubCategoriesID LIKE '%11172%')) THEN 1 END) AS 'Travel Medicine'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11173%') AND (sub.PublicationSubCategoriesID LIKE '%11173%')) THEN 1 END) AS 'Breast Cancer'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11174%') AND (sub.PublicationSubCategoriesID LIKE '%11174%')) THEN 1 END) AS 'Colorectal Oncology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11175%') AND (sub.PublicationSubCategoriesID LIKE '%11175%')) THEN 1 END) AS 'Neurology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11176%') AND (sub.PublicationSubCategoriesID LIKE '%11176%')) THEN 1 END) AS 'Child Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11177%') AND (sub.PublicationSubCategoriesID LIKE '%11177%')) THEN 1 END) AS 'Paediatric Vaccines'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11178%') AND (sub.PublicationSubCategoriesID LIKE '%11178%')) THEN 1 END) AS 'Behavioural Disorders'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11179%') AND (sub.PublicationSubCategoriesID LIKE '%11179%')) THEN 1 END) AS 'Psychiatry'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11180%') AND (sub.PublicationSubCategoriesID LIKE '%11180%')) THEN 1 END) AS 'Anaesthesia'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11181%') AND (sub.PublicationSubCategoriesID LIKE '%11181%')) THEN 1 END) AS 'General Surgery'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11182%') AND (sub.PublicationSubCategoriesID LIKE '%11182%')) THEN 1 END) AS 'GI Surgery/ Endoscopy'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11183%') AND (sub.PublicationSubCategoriesID LIKE '%11183%')) THEN 1 END) AS 'Hip & Knee'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11184%') AND (sub.PublicationSubCategoriesID LIKE '%11184%')) THEN 1 END) AS 'Plastics'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11185%') AND (sub.PublicationSubCategoriesID LIKE '%11185%')) THEN 1 END) AS 'Trauma'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11186%') AND (sub.PublicationSubCategoriesID LIKE '%11186%')) THEN 1 END) AS 'Vascular'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11187%') AND (sub.PublicationSubCategoriesID LIKE '%11187%')) THEN 1 END) AS 'Urology'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11188%') AND (sub.PublicationSubCategoriesID LIKE '%11188%')) THEN 1 END) AS 'Dental'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11189%') AND (sub.PublicationSubCategoriesID LIKE '%11189%')) THEN 1 END) AS 'Oral Health'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11191%') AND (sub.PublicationSubCategoriesID LIKE '%11191%')) THEN 1 END) AS 'Fertility'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11253%') AND (sub.PublicationSubCategoriesID LIKE '%11253%')) THEN 1 END) AS 'Infection Control and Prevention'
	,COUNT(CASE WHEN ((usr.PublicationsNZ LIKE '%11254%') AND (sub.PublicationSubCategoriesID LIKE '%11254%')) THEN 1 END) AS 'Wound Care'
FROM 
	[ResearchReview-Staging].dbo.RR_PublicationSubCategories sub, [ResearchReview-Staging].dbo.CMS_User usr
JOIN
	[ResearchReview-Staging].dbo.RR_Professions prof
ON
	CAST(usr.Profession AS VARCHAR) LIKE '%' + CAST(prof.ProfessionsID AS VARCHAR) + '%'
AND
	prof.SubSite LIKE 'RR-NZ-UC'
AND 
	usr.CountryOfPractice LIKE 'RR-NZ-UC'
AND 
	usr.UserEnabled LIKE '1'
AND 
	usr.PublicationsNZ IS NOT NULL 
AND 
	usr.PublicationsNZ NOT LIKE ''
WHERE 
	sub.CanUsersSubScribe LIKE 'Yes'
AND
	sub.SubSite LIKE 'RR-NZ-UC'	
Group By
	prof.[ProfessionName]
ORDER BY 
	prof.ProfessionName


My query is taking 10-12 minutes to load. Is there any better way of achieving this report?

Note: There are total 18000 rows in the CMS_Users table.

Thanks
Gitesh Shah


Gitesh Shah

UnemployedInOz
Yak Posting Veteran

54 Posts
Posted - 2013-04-11 : 21:33:44
Can you post what indexes you have on each table?
Also, if you dont's have to use the "Like" dont. Use "="
e.g. prof.SubSite LIKE 'RR-NZ-UC'
should be
prof.SubSite = 'RR-NZ-UC'
Go to Top of Page

giteshshah
Starting Member

5 Posts
Posted - 2013-04-14 : 19:51:51
quote:
Originally posted by UnemployedInOz

Can you post what indexes you have on each table?
Also, if you dont's have to use the "Like" dont. Use "="
e.g. prof.SubSite LIKE 'RR-NZ-UC'
should be
prof.SubSite = 'RR-NZ-UC'



Hi,

Do you mean to say the rows inside each table?

Sorry not sure what you mean by indexes.

Thanks for your quick reply. Appreciate your help.

I have no specific reason for using 'LIKE' instead of '='. Will using '=' speed up the process?

Thanks
Gitesh Shah



Gitesh Shah
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

54 Posts
Posted - 2013-04-14 : 20:58:24
http://www.tutorialspoint.com/sql/sql-indexes.htm

1) Read up on indexing and how it speeds up your queries.
2) Replace "Like" with "=" if you are looking for an exact match.
3) [ResearchReview-Staging].dbo.RR_PublicationSubCategories does not seem to relate to the other tables e.g. SUB.id = user.id

4) Add some indexing e.g.
CREATE NONCLUSTERED INDEX [_dta_index_RR_PublicationSubCategories] ON [ResearchReview-Staging].dbo.RR_PublicationSubCategories
(
SubSite ,
CanUsersSubScribe
)
INCLUDE ( PublicationSubCategoriesID ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO



CREATE NONCLUSTERED INDEX [_dta_index_CMS_User] ON [ResearchReview-Staging].dbo.CMS_User
(
CountryOfPractice ,
UserEnabled ,
PublicationsNZ ,
Profession
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [_dta_index_RR_Professions] ON [ResearchReview-Staging].dbo.RR_Professions
(
SubSite ,
ProfessionName
)
INCLUDE ( ProfessionsID ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2013-04-15 : 06:47:46
[code]SELECT prof.ProfessionName AS Professions,
SUM(CASE WHEN f.Result = 1 THEN 1 ELSE 0 END) AS [Allergy],
SUM(CASE WHEN f.Result = 2 THEN 1 ELSE 0 END) AS [Bone Health],
SUM(CASE WHEN f.Result = 3 THEN 1 ELSE 0 END) AS [Dermatology],
SUM(CASE WHEN f.Result = 4 THEN 1 ELSE 0 END) AS [General Practice],
SUM(CASE WHEN f.Result = 5 THEN 1 ELSE 0 END) AS [Pain Management],
SUM(CASE WHEN f.Result = 6 THEN 1 ELSE 0 END) AS [Smoking Cessation],
SUM(CASE WHEN f.Result = 7 THEN 1 ELSE 0 END) AS [Mens Sexual Health],
SUM(CASE WHEN f.Result = 8 THEN 1 ELSE 0 END) AS [Womens Sexual Health],
SUM(CASE WHEN f.Result = 9 THEN 1 ELSE 0 END) AS [Asian Health],
SUM(CASE WHEN f.Result = 10 THEN 1 ELSE 0 END) AS [Eye Health],
SUM(CASE WHEN f.Result = 11 THEN 1 ELSE 0 END) AS [Foot & Ankle],
SUM(CASE WHEN f.Result = 12 THEN 1 ELSE 0 END) AS [Health Manager],
SUM(CASE WHEN f.Result = 13 THEN 1 ELSE 0 END) AS [Hearing],
SUM(CASE WHEN f.Result = 14 THEN 1 ELSE 0 END) AS [Maori Health],
SUM(CASE WHEN f.Result = 15 THEN 1 ELSE 0 END) AS [Natural Health],
SUM(CASE WHEN f.Result = 16 THEN 1 ELSE 0 END) AS [Pacific Health],
SUM(CASE WHEN f.Result = 17 THEN 1 ELSE 0 END) AS [Pharmacy],
SUM(CASE WHEN f.Result = 18 THEN 1 ELSE 0 END) AS [Patient Psychology],
SUM(CASE WHEN f.Result = 19 THEN 1 ELSE 0 END) AS [Rehabilitation],
SUM(CASE WHEN f.Result = 20 THEN 1 ELSE 0 END) AS [Internal Medicine],
SUM(CASE WHEN f.Result = 21 THEN 1 ELSE 0 END) AS [Cardiology],
SUM(CASE WHEN f.Result = 22 THEN 1 ELSE 0 END) AS [Diabetes & Obesity],
SUM(CASE WHEN f.Result = 23 THEN 1 ELSE 0 END) AS [Inflammatory Bowel Disease],
SUM(CASE WHEN f.Result = 24 THEN 1 ELSE 0 END) AS [Gastroenterology],
SUM(CASE WHEN f.Result = 25 THEN 1 ELSE 0 END) AS [Nephrology],
SUM(CASE WHEN f.Result = 26 THEN 1 ELSE 0 END) AS [Respiratory],
SUM(CASE WHEN f.Result = 27 THEN 1 ELSE 0 END) AS [Rheumatology],
SUM(CASE WHEN f.Result = 28 THEN 1 ELSE 0 END) AS [Geriatrics],
SUM(CASE WHEN f.Result = 29 THEN 1 ELSE 0 END) AS [Sports Medicine],
SUM(CASE WHEN f.Result = 30 THEN 1 ELSE 0 END) AS [Haematology],
SUM(CASE WHEN f.Result = 31 THEN 1 ELSE 0 END) AS [Lymphoma/Leukaemia],
SUM(CASE WHEN f.Result = 32 THEN 1 ELSE 0 END) AS [Multiple Myeloma],
SUM(CASE WHEN f.Result = 33 THEN 1 ELSE 0 END) AS [Hepatitis],
SUM(CASE WHEN f.Result = 34 THEN 1 ELSE 0 END) AS [HIV],
SUM(CASE WHEN f.Result = 35 THEN 1 ELSE 0 END) AS [Infectious Diseases],
SUM(CASE WHEN f.Result = 36 THEN 1 ELSE 0 END) AS [Travel Medicine],
SUM(CASE WHEN f.Result = 37 THEN 1 ELSE 0 END) AS [Breast Cancer],
SUM(CASE WHEN f.Result = 38 THEN 1 ELSE 0 END) AS [Colorectal Oncology],
SUM(CASE WHEN f.Result = 39 THEN 1 ELSE 0 END) AS [Neurology],
SUM(CASE WHEN f.Result = 40 THEN 1 ELSE 0 END) AS [Child Health],
SUM(CASE WHEN f.Result = 41 THEN 1 ELSE 0 END) AS [Paediatric Vaccines],
SUM(CASE WHEN f.Result = 42 THEN 1 ELSE 0 END) AS [Behavioural Disorders],
SUM(CASE WHEN f.Result = 43 THEN 1 ELSE 0 END) AS [Psychiatry],
SUM(CASE WHEN f.Result = 44 THEN 1 ELSE 0 END) AS [Anaesthesia],
SUM(CASE WHEN f.Result = 45 THEN 1 ELSE 0 END) AS [General Surgery],
SUM(CASE WHEN f.Result = 46 THEN 1 ELSE 0 END) AS [GI Surgery/ Endoscopy],
SUM(CASE WHEN f.Result = 47 THEN 1 ELSE 0 END) AS [Hip & Knee],
SUM(CASE WHEN f.Result = 48 THEN 1 ELSE 0 END) AS [Plastics],
SUM(CASE WHEN f.Result = 49 THEN 1 ELSE 0 END) AS [Trauma],
SUM(CASE WHEN f.Result = 50 THEN 1 ELSE 0 END) AS [Vascular],
SUM(CASE WHEN f.Result = 51 THEN 1 ELSE 0 END) AS [Urology],
SUM(CASE WHEN f.Result = 52 THEN 1 ELSE 0 END) AS [Dental],
SUM(CASE WHEN f.Result = 53 THEN 1 ELSE 0 END) AS [Oral Health],
SUM(CASE WHEN f.Result = 54 THEN 1 ELSE 0 END) AS [Fertility],
SUM(CASE WHEN f.Result = 55 THEN 1 ELSE 0 END) AS [Infection Control and Prevention],
SUM(CASE WHEN f.Result = 56 THEN 1 ELSE 0 END) AS [Wound Care]
FROM [ResearchReview-Staging].dbo.RR_PublicationSubCategories AS sub
INNER JOIN [ResearchReview-Staging].dbo.CMS_User AS usr ON usr.CountryOfPractice = 'RR-NZ-UC'
AND usr.UserEnabled = '1'
INNER JOIN [ResearchReview-Staging].dbo.RR_Professions AS prof ON prof.SubSite = 'RR-NZ-UC'
AND CAST(usr.Profession AS VARCHAR(30)) LIKE '%' + CAST(prof.ProfessionsID AS VARCHAR(30)) + '%'
CROSS APPLY (
VALUES (
CASE
WHEN usr.PublicationsNZ LIKE '%11129%' AND sub.PublicationSubCategoriesID LIKE '%11129%' THEN 1
WHEN usr.PublicationsNZ LIKE '%11130%' AND sub.PublicationSubCategoriesID LIKE '%11130%' THEN 2
WHEN usr.PublicationsNZ LIKE '%11131%' AND sub.PublicationSubCategoriesID LIKE '%11131%' THEN 3
WHEN usr.PublicationsNZ LIKE '%11133%' AND sub.PublicationSubCategoriesID LIKE '%11133%' THEN 4
WHEN usr.PublicationsNZ LIKE '%11134%' AND sub.PublicationSubCategoriesID LIKE '%11134%' THEN 5
WHEN usr.PublicationsNZ LIKE '%11135%' AND sub.PublicationSubCategoriesID LIKE '%11135%' THEN 6
WHEN usr.PublicationsNZ LIKE '%11137%' AND sub.PublicationSubCategoriesID LIKE '%11137%' THEN 7
WHEN usr.PublicationsNZ LIKE '%11138%' AND sub.PublicationSubCategoriesID LIKE '%11138%' THEN 8
WHEN usr.PublicationsNZ LIKE '%11139%' AND sub.PublicationSubCategoriesID LIKE '%11139%' THEN 9
WHEN usr.PublicationsNZ LIKE '%11140%' AND sub.PublicationSubCategoriesID LIKE '%11140%' THEN 10
WHEN usr.PublicationsNZ LIKE '%11141%' AND sub.PublicationSubCategoriesID LIKE '%11141%' THEN 11
WHEN usr.PublicationsNZ LIKE '%11142%' AND sub.PublicationSubCategoriesID LIKE '%11142%' THEN 12
WHEN usr.PublicationsNZ LIKE '%11143%' AND sub.PublicationSubCategoriesID LIKE '%11143%' THEN 13
WHEN usr.PublicationsNZ LIKE '%11144%' AND sub.PublicationSubCategoriesID LIKE '%11144%' THEN 14
WHEN usr.PublicationsNZ LIKE '%11145%' AND sub.PublicationSubCategoriesID LIKE '%11145%' THEN 15
WHEN usr.PublicationsNZ LIKE '%11146%' AND sub.PublicationSubCategoriesID LIKE '%11146%' THEN 16
WHEN usr.PublicationsNZ LIKE '%11147%' AND sub.PublicationSubCategoriesID LIKE '%11147%' THEN 17
WHEN usr.PublicationsNZ LIKE '%11148%' AND sub.PublicationSubCategoriesID LIKE '%11148%' THEN 18
WHEN usr.PublicationsNZ LIKE '%11149%' AND sub.PublicationSubCategoriesID LIKE '%11149%' THEN 19
WHEN usr.PublicationsNZ LIKE '%11150%' AND sub.PublicationSubCategoriesID LIKE '%11150%' THEN 20
WHEN usr.PublicationsNZ LIKE '%11151%' AND sub.PublicationSubCategoriesID LIKE '%11151%' THEN 21
WHEN usr.PublicationsNZ LIKE '%11152%' AND sub.PublicationSubCategoriesID LIKE '%11152%' THEN 22
WHEN usr.PublicationsNZ LIKE '%11156%' AND sub.PublicationSubCategoriesID LIKE '%11156%' THEN 23
WHEN usr.PublicationsNZ LIKE '%11157%' AND sub.PublicationSubCategoriesID LIKE '%11157%' THEN 24
WHEN usr.PublicationsNZ LIKE '%11161%' AND sub.PublicationSubCategoriesID LIKE '%11161%' THEN 25
WHEN usr.PublicationsNZ LIKE '%11162%' AND sub.PublicationSubCategoriesID LIKE '%11162%' THEN 26
WHEN usr.PublicationsNZ LIKE '%11163%' AND sub.PublicationSubCategoriesID LIKE '%11163%' THEN 27
WHEN usr.PublicationsNZ LIKE '%11164%' AND sub.PublicationSubCategoriesID LIKE '%11164%' THEN 28
WHEN usr.PublicationsNZ LIKE '%11165%' AND sub.PublicationSubCategoriesID LIKE '%11165%' THEN 29
WHEN usr.PublicationsNZ LIKE '%11166%' AND sub.PublicationSubCategoriesID LIKE '%11166%' THEN 30
WHEN usr.PublicationsNZ LIKE '%11167%' AND sub.PublicationSubCategoriesID LIKE '%11167%' THEN 31
WHEN usr.PublicationsNZ LIKE '%11168%' AND sub.PublicationSubCategoriesID LIKE '%11168%' THEN 32
WHEN usr.PublicationsNZ LIKE '%11169%' AND sub.PublicationSubCategoriesID LIKE '%11169%' THEN 33
WHEN usr.PublicationsNZ LIKE '%11170%' AND sub.PublicationSubCategoriesID LIKE '%11170%' THEN 34
WHEN usr.PublicationsNZ LIKE '%11171%' AND sub.PublicationSubCategoriesID LIKE '%11171%' THEN 35
WHEN usr.PublicationsNZ LIKE '%11172%' AND sub.PublicationSubCategoriesID LIKE '%11172%' THEN 36
WHEN usr.PublicationsNZ LIKE '%11173%' AND sub.PublicationSubCategoriesID LIKE '%11173%' THEN 37
WHEN usr.PublicationsNZ LIKE '%11174%' AND sub.PublicationSubCategoriesID LIKE '%11174%' THEN 38
WHEN usr.PublicationsNZ LIKE '%11175%' AND sub.PublicationSubCategoriesID LIKE '%11175%' THEN 39
WHEN usr.PublicationsNZ LIKE '%11176%' AND sub.PublicationSubCategoriesID LIKE '%11176%' THEN 40
WHEN usr.PublicationsNZ LIKE '%11177%' AND sub.PublicationSubCategoriesID LIKE '%11177%' THEN 41
WHEN usr.PublicationsNZ LIKE '%11178%' AND sub.PublicationSubCategoriesID LIKE '%11178%' THEN 42
WHEN usr.PublicationsNZ LIKE '%11179%' AND sub.PublicationSubCategoriesID LIKE '%11179%' THEN 43
WHEN usr.PublicationsNZ LIKE '%11180%' AND sub.PublicationSubCategoriesID LIKE '%11180%' THEN 44
WHEN usr.PublicationsNZ LIKE '%11181%' AND sub.PublicationSubCategoriesID LIKE '%11181%' THEN 45
WHEN usr.PublicationsNZ LIKE '%11182%' AND sub.PublicationSubCategoriesID LIKE '%11182%' THEN 46
WHEN usr.PublicationsNZ LIKE '%11183%' AND sub.PublicationSubCategoriesID LIKE '%11183%' THEN 47
WHEN usr.PublicationsNZ LIKE '%11184%' AND sub.PublicationSubCategoriesID LIKE '%11184%' THEN 48
WHEN usr.PublicationsNZ LIKE '%11185%' AND sub.PublicationSubCategoriesID LIKE '%11185%' THEN 49
WHEN usr.PublicationsNZ LIKE '%11186%' AND sub.PublicationSubCategoriesID LIKE '%11186%' THEN 50
WHEN usr.PublicationsNZ LIKE '%11187%' AND sub.PublicationSubCategoriesID LIKE '%11187%' THEN 51
WHEN usr.PublicationsNZ LIKE '%11188%' AND sub.PublicationSubCategoriesID LIKE '%11188%' THEN 52
WHEN usr.PublicationsNZ LIKE '%11189%' AND sub.PublicationSubCategoriesID LIKE '%11189%' THEN 53
WHEN usr.PublicationsNZ LIKE '%11191%' AND sub.PublicationSubCategoriesID LIKE '%11191%' THEN 54
WHEN usr.PublicationsNZ LIKE '%11253%' AND sub.PublicationSubCategoriesID LIKE '%11253%' THEN 55
WHEN usr.PublicationsNZ LIKE '%11254%' AND sub.PublicationSubCategoriesID LIKE '%11254%' THEN 56
ELSE 0
END
)
) AS f(Result)
WHERE sub.CanUsersSubScribe = 'Yes'
AND sub.SubSite = 'RR-NZ-UC'
AND f.Result > 0
GROUP BY prof.ProfessionName
ORDER BY prof.ProfessionName;[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site