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 2008 Forums
 Transact-SQL (2008)
 Gets the job done but it not efficient

Author  Topic 

MarkJeff880
Starting Member

2 Posts
Posted - 2014-05-28 : 14:01:57
HI everyone.

I'm dusting of my SQL Skills and I've made an extremely bloated script but it works for the info I'm trying to present.

can you guys help me clean it up or tell me a better way.

this is the script
DECLARE @LoopINT INT
SET @LoopINT = 1
While (@LoopINT <> 310 )
BEGIN

SELECT
C.company_Name as [Company name],
count (CSS.Question) AS [Kaseya Agent],
(
SELECT
count (CSS.Question) AS [LogMeIn Agent]
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
(
Con.CS_Survey_RecID >= 68
OR
Con.CS_Survey_RecID <= 69
)
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
(CSS.CS_Survey_Detail_RecID = 718
OR
CSS.CS_Survey_Detail_RecID = 723)
Group By C.Company_Name
)
AS [Logmein],
(
SELECT
count (CSS.Question) AS [Backup Test Restore]
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
AND
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 720
Group By C.Company_Name
)
AS [Backup Test Restore],
(
SELECT
count (CSS.Question) AS [Server Backup]
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
AND
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 716

)
AS [Server Backup],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
AND
C.Company_RecID = Con.Company_RecID
and
(
Con.CS_Survey_RecID >= 68
OR
Con.CS_Survey_RecID <= 69
)
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
(CSS.CS_Survey_Detail_RecID = 713
OR
CSS.CS_Survey_Detail_RecID = 715)
Group By C.Company_Name
)
AS [ESET Managment],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 68
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 714
Group By C.Company_Name
)
AS [Workstation Backup],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 719
Group By C.Company_Name
)
AS [Server Managment],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
AND
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 721
Group By C.Company_Name
)
AS [Backup Test Restore]
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
(
Con.CS_Survey_RecID >= 68
OR
Con.CS_Survey_RecID <= 69
)
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
(CSS.CS_Survey_Detail_RecID = 717
OR
CSS.CS_Survey_Detail_RecID = 722)
Group By C.Company_Name
SET @LoopINT = @LoopINT + 1
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2014-05-28 : 14:12:07
This is confusing to me. What is the join to CS_Survey_Detail supposed to be? I think it'll do a full outer join, not not sure as the syntax is not well formed. 
FROM 
	Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
	CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID


If you want to post sample data and expected output we can probably help better, but my suggestions would be to:
1. Get rid of the loop.
2. Do one query and use CASE expressions to get the COUNTs.
Go to Top of Page

MarkJeff880
Starting Member

2 Posts
Posted - 2014-05-28 : 19:15:25
Hey Lamprey

Output looks like this the NULL values are a count of the service titled in the headers

ID Company name Kaseya Agent Logmein Backup Test Restore Server Backup Workstation Backup Server Managment Backup Test Restore ESET Managment
3 ConnectWise NULL NULL NULL NULL NULL NULL NULL NULL

here is the latest iteration of the script

DECLARE @LoopINT INT
SET @LoopINT = 1
While (@LoopINT <> 700)
BEGIN

SELECT
C.Company_recID as [ID],
C.company_Name as [Company name],
(
SELECT count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
(
Con.CS_Survey_RecID >= 68
OR
Con.CS_Survey_RecID <= 69
)
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
(CSS.CS_Survey_Detail_RecID = 717
OR
CSS.CS_Survey_Detail_RecID = 722)
Group By C.Company_Name
)
AS [Kaseya Agent],
(
SELECT
count (CSS.Question) AS [LogMeIn Agent]
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
(
Con.CS_Survey_RecID >= 68
OR
Con.CS_Survey_RecID <= 69
)
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
(CSS.CS_Survey_Detail_RecID = 718
OR
CSS.CS_Survey_Detail_RecID = 723)
Group By C.Company_Name
)
AS [Logmein],
(
SELECT
count (CSS.Question) AS [Backup Test Restore]
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 720
Group By C.Company_Name
)
AS [Backup Test Restore],
(
SELECT
count (CSS.Question) AS [Server Backup]
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
AND
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 716
Group By C.Company_Name
)
AS [Server Backup],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
AND
C.Company_RecID = Con.Company_RecID
and
(
Con.CS_Survey_RecID >= 68
OR
Con.CS_Survey_RecID <= 69
)
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
(CSS.CS_Survey_Detail_RecID = 713
OR
CSS.CS_Survey_Detail_RecID = 715)
Group By C.Company_Name
)
AS [ESET Managment],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 68
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 714
Group By C.Company_Name
)
AS [Workstation Backup],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
and
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 719
Group By C.Company_Name
)
AS [Server Managment],
(
SELECT
count (CSS.Question)
FROM
Company AS C INNER JOIN Config as Con ON C.Company_RecID = Con.Company_RecID,
CS_Survey_Detail as CSS INNER JOIN CS_Result_Detail as CSR ON CSS.CS_Survey_Detail_RecID = CSR.CS_Survey_Detail_RecID
WHERE
C.Company_RecID = @LoopINT
AND
C.Company_RecID = Con.Company_RecID
and
Con.CS_Survey_RecID >= 69
AND
CSR.CS_Result_RecID = Con.Config_recid
AND
CSR.answer = 'True'
AND
CSS.CS_Survey_Detail_RecID = 721
Group By C.Company_Name
)
AS [Backup Test Restore]
FROM
Company AS C
WHERE
C.Company_RecID = @LoopINT

SET @LoopINT = @LoopINT + 1
END
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