How to convert a Cursor into SELECT queries?

I am using SSRS 2008 and i have a stored proc which currently uses cursors to delete and add data, but even after I convert this to select queries, I am getting error:
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ')'.

Here was the original Cursor which worked:
OPEN PARTS
FETCH PARTS INTO	@PART_NUM,
@PART_DESC
SET @PARTS_FETCH = @@FETCH_STATUS
WHILE @PARTS_FETCH = 0 BEGIN
SET @THE_DATE = dateadd("yy", -1, dateadd("m", -1, getdate()))
SET @END_DATE = DATEADD(ms, -5, DATEADD(mm, DATEDIFF(m, 0, getdate()) + 1, 0))

-- Get PL for part number 
Delete from @tbl_PL

Insert @tbl_PL 
SELECT FRUD.tblXref.product_code FROM FRUD.tblfieldOrderablePart INNER JOIN
FRUD.MAX_APPROVED ON FRUD.tblfieldOrderablePart.fop_no = FRUD.MAX_APPROVED.fop_no AND 
FRUD.tblfieldOrderablePart.fop_revision = FRUD.MAX_APPROVED.MaxOffop_revision INNER JOIN
FRUD.tblXref ON FRUD.MAX_APPROVED.fop_no = FRUD.tblXref.fop_no AND 
FRUD.MAX_APPROVED.MaxOffop_revision = FRUD.tblXref.fop_revision
WHERE (dbo.FORMAT_PART(FRUD.tblfieldOrderablePart.fop_no) = dbo.FORMAT_PART(@PART_NUM))
-- End Get PL
	
WHILE @THE_DATE <= @END_DATE BEGIN
SET @THE_DATE = DATEADD(ms, -5, DATEADD(mm, DATEDIFF(m, 0, @THE_DATE) + 1, 0))
-- Get census using PL			
SELECT @ALL_TOTAL = SUM(TheSum) FROM (SELECT CAST(RELIABILITY.Census.Census AS DECIMAL(9,2)) AS TheSum, (CONVERT(datetime, LEFT(CONVERT(char, Period), 4) + '-' + RIGHT(Period, 2) + '-1', 102)) as ThePeriod
FROM RELIABILITY.Census
WHERE RELIABILITY.Census.PL In (Select distinct * FROM @tbl_PL)
AND (CONVERT(datetime, LEFT(CONVERT(char, Period), 4) + '-' + RIGHT(Period, 2) + '-1', 102) >=
DATEADD(mm, DATEDIFF(mm, 0, @THE_DATE) - 5, 0)) AND (CONVERT(datetime, LEFT(CONVERT(char, Period), 4)
+ '-' + RIGHT(Period, 2) + '-1', 102) <= @THE_DATE)							UNION ALL							SELECT     CAST(Census AS DECIMAL(9,2)) AS TheSum, Period							FROM         [MANUAL].SMARTSOLVE_CENSUS							WHERE     (Period >= DATEADD(mm, DATEDIFF(mm, 0, @THE_DATE) - 5, 0) AND Period <= @THE_DATE) AND (PL In (Select distinct * FROM @tbl_PL)))A

Open in new window


And here is my conversion to Select:
	DECLARE @THE_DATE datetime,
			@END_DATE datetime,
			@THE_GOAL decimal(18,2),
			@PART_NUM nvarchar(50),
			@UNCHANGED_PART_NUM nvarchar(50),
			@PART_DESC varchar(35),
			@PARTS_FETCH int,
			@NUM_FAILED int,
			@AVG_CENSUS decimal(18,2),
			@PL_VAR nvarchar(50),	
			@PL_FETCH int,
			@PL_TOTAL decimal(9,2),
			@ALL_TOTAL decimal(9,2)

	--WHILE @PARTS_FETCH = 0 BEGIN
		SET @THE_DATE = dateadd("yy", -1, dateadd("m", -1, getdate()))
		SET @END_DATE = DATEADD(ms, -5, DATEADD(mm, DATEDIFF(m, 0, getdate()) + 1, 0))	
		--WHILE @THE_DATE <= @END_DATE BEGIN
SET @THE_DATE = DATEADD(ms, -5, DATEADD(mm, DATEDIFF(m, 0, @THE_DATE) + 1, 0))
-- Get census using PL
							
SELECT @ALL_TOTAL = SUM(TheSum) FROM 
(SELECT CAST(RELIABILITY.Census.Census AS DECIMAL(9,2)) AS TheSum 
from RELIABILITY.Census
WHERE RELIABILITY.Census.PL In (Select distinct * FROM #tbl_PL) 
AND (CONVERT(datetime, LEFT(CONVERT(char, Period), 4) + '-' + RIGHT(Period, 2) + '-1', 102) >=	
DATEADD(mm, DATEDIFF(mm, 0, @THE_DATE) - 5, 0)) AND (CONVERT(datetime, LEFT(CONVERT(char, Period), 4)+ '-' + RIGHT(Period, 2) + '-1', 102) <= @THE_DATE)					
UNION ALL
SELECT     CAST(Census AS DECIMAL(9,2)) AS TheSum, Period			
FROM         [MANUAL].SMARTSOLVE_CENSUS					
WHERE     (Period >= DATEADD(mm, DATEDIFF(mm, 0, @THE_DATE) - 5, 0) AND Period <= @THE_DATE) AND (PL In (Select distinct * FROM #tbl_PL))
))A

Open in new window

ironryan77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
It appears you did indeed have an extra parenthesis.  I didn't check the validity of the code, just formatted to find if it was a simple bracket problem and appears so.  If you get an error beyond that, then we have to check logic/syntax.

SELECT @ALL_TOTAL = SUM(TheSum)
FROM (
   SELECT CAST(RELIABILITY.Census.Census AS DECIMAL(9,2)) AS TheSum
   FROM RELIABILITY.Census
   WHERE RELIABILITY.Census.PL In (Select distinct * FROM #tbl_PL)
   AND (CONVERT(datetime, LEFT(CONVERT(char, Period), 4) + '-' + RIGHT(Period, 2) + '-1', 102) >=      
            DATEADD(mm, DATEDIFF(mm, 0, @THE_DATE) - 5, 0))
   AND (CONVERT(datetime, LEFT(CONVERT(char, Period), 4)+ '-' + RIGHT(Period, 2) + '-1', 102) <= @THE_DATE)                              
   UNION ALL
   SELECT CAST(Census AS DECIMAL(9,2)) AS TheSum, Period                  
   FROM [MANUAL].SMARTSOLVE_CENSUS                              
   WHERE (Period >= DATEADD(mm, DATEDIFF(mm, 0, @THE_DATE) - 5, 0)
          AND Period <= @THE_DATE)
   AND (PL In (Select distinct * FROM #tbl_PL))
)A
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.