Convert the stored procedure to "Standard SQL Select"

Question

Convert the stored procedure to "Standard SQL Select"

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

Pesonally, I think that option 3 is the worst (it sounds like you're setting up to write embedded code, which is a huge mistake for maintenance, IMHO). Option 2 would be the best but it's your problem. Here's how you can do option 3 and still achieve what I was talking about.

WITH

cteFYStart AS (SELECT FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))

,cteFYAll AS (SELECT FYStart

,FYNextStart = DateAdd(yy,1,FYStart)

,FYPrevStart = DateAdd(yy,-1,FYStart)

FROM cteFYStart)

SELECTITSDivisionID

,A = Sum(Case When d.DocStatus = 'Retired' and d.RetiredDate >= fy.FYPrevStart and d.RetiredDate < fy.FYStart Then 1 Else 0 End)

,B = Sum(Case When d.DocStatus = 'Active' Then 1 Else 0 End)

,C = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= fy.FYStart and d.TargetClosureDate < fy.FYNextStart Then 1 Else 0 End)

,D = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= fy.FYNextStart Then 1 Else 0 End)

,Critical = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 4 Then 1 Else 0 End)

,High = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 3 Then 1 Else 0 End)

,Medium = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 2 Then 1 Else 0 End)

,Low = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 1 Then 1 Else 0 End)

,TBD = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 0 Then 1 Else 0 End)

FROM dbo.Document d

CROSS JOIN cteFYAll fy

WHERE DocType = 1

AND DocStatus IN ('Active','Retired')

GROUP BY ITSDivisionID

;

Again, the code isn't yet complete. I'm waiting for you to think outside the box a bit and make it so BCD and DRR are calculated without repeating code and to add the "Division" name column using just one more CTE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Jeff Moden (4/12/2014)
Pesonally, I think that option 3 is the worst (it sounds like you're setting up to write embedded code, which is a huge mistake for maintenance, IMHO).

I totally agree with you on this, however Option 3 sometimes is the best approach indeed: please just think about if it takes you two weeks or longer to implement any change in production. Option 3 doesn't ask you to go through the painful process, this is the beautiful advantage! Option 2 is best in terms of development while Option 1 is the simplest one.

Option 2 would be the best but it's your problem. Here's how you can do option 3 and still achieve what I was talking about.
WITH
cteFYStart AS (SELECT FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))
,cteFYAll AS (SELECT FYStart
,FYNextStart = DateAdd(yy,1,FYStart)
,FYPrevStart = DateAdd(yy,-1,FYStart)
FROM cteFYStart)
SELECTITSDivisionID
,A = Sum(Case When d.DocStatus = 'Retired' and d.RetiredDate >= fy.FYPrevStart and d.RetiredDate < fy.FYStart Then 1 Else 0 End)
,B = Sum(Case When d.DocStatus = 'Active' Then 1 Else 0 End)
,C = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= fy.FYStart and d.TargetClosureDate < fy.FYNextStart Then 1 Else 0 End)
,D = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= fy.FYNextStart Then 1 Else 0 End)
,Critical = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 4 Then 1 Else 0 End)
,High = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 3 Then 1 Else 0 End)
,Medium = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 2 Then 1 Else 0 End)
,Low = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 1 Then 1 Else 0 End)
,TBD = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 0 Then 1 Else 0 End)
FROM dbo.Document d
CROSS JOIN cteFYAll fy
WHERE DocType = 1
AND DocStatus IN ('Active','Retired')
GROUP BY ITSDivisionID
;

Again, the code isn't yet complete. I'm waiting for you to think outside the box a bit and make it so BCD and DRR are calculated without repeating code and to add the "Division" name column using just one more CTE.

Your code works perfectly in SQL, I have to think about how to make DRR, weekend is full to me. Your code is still not accepted by Dundas (because of the CTE), I will check with them to see if there could be any work around. Thank you once again for your kind help and patient. Have a break and enjoy your weekend with your family.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

halifaxdal (4/13/2014)
I totally agree with you on this, however Option 3 sometimes is the best approach indeed: please just think about if it takes you two weeks or longer to implement any change in production. Option 3 doesn't ask you to go through the painful process, this is the beautiful advantage! Option 2 is best in terms of development while Option 1 is the simplest one.

There's a reason why it takes 2 weeks to get something into production. I suspect it's because people need time to test your code. So now YOU stop and think about it... YOU had an error in your code that mixed up the Fiscal Year with the Calendar Year. If YOU had put that into production, you might be looking for a new place to work! 😉

Your code works perfectly in SQL, I have to think about how to make DRR, weekend is full to me. Your code is still not accepted by Dundas (because of the CTE), I will check with them to see if there could be any work around. Thank you once again for your kind help and patient. Have a break and enjoy your weekend with your family.

If Dundas doesn't accept CTEs, then you really ARE using the wrong option. :sick:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

As much as I hate saying this, since semicolons are statement terminators not statement begininators, try putting a semicolon in front of the WITH of the CTE declaration. I.E. ;with ... .

halifaxdal SSCoach Points: 19741 More actions · Answer 5

Jeff Moden (4/13/2014)

There's a reason why it takes 2 weeks to get something into production. I suspect it's because people need time to test your code. So now YOU stop and think about it... YOU had an error in your code that mixed up the Fiscal Year with the Calendar Year. If YOU had put that into production, you might be looking for a new place to work! 😉

In my company, any changes must go through UAT and then PROD (IST is not rigorously required but is suggested), they are on different domains that are not communicable and require different user id to test and UAT does not have PROD data and we are not allowed to use PROD data in UAT. There is weekly meeting to approve those change request. For database change it involves four departments + at least 5 forms to be created and approved by up to Director.

I hope you can imagine the lengthy process and understand why I think sometimes Option 3 is the one I like to go (and also my manager), reason is very simple, I don't need to go through the bureaucratic and debatable procedures.

halifaxdal SSCoach Points: 19741 More actions · Answer 6

Lynn Pettis (4/13/2014)
As much as I hate saying this, since semicolons are statement terminators not statement begininators, try putting a semicolon in front of the WITH of the CTE declaration. I.E. ;with ... .

Thank you, that's a good point.

halifaxdal SSCoach Points: 19741 More actions · Answer 7

As WITH is not allowed in this case, I did some replacement:

FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))

FYPrevStart = DateAdd(yy,-1,FYStart)

FYPrevStart = DateAdd(yy,-1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))

FYNextStart = DateAdd(yy,1,FYStart)

FYNextStart = DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))

The final working code is:

SELECTITSDivisionID

,A = Sum(Case When d.DocStatus = 'Retired' and d.RetiredDate >= DateAdd(yy,-1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) and d.RetiredDate < DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)) Then 1 Else 0 End)

,B = Sum(Case When d.DocStatus = 'Active' Then 1 Else 0 End)

,C = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)) and d.TargetClosureDate < DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) Then 1 Else 0 End)

,D = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) Then 1 Else 0 End)

,Critical = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 4 Then 1 Else 0 End)

,High = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 3 Then 1 Else 0 End)

,Medium = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 2 Then 1 Else 0 End)

,Low = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 1 Then 1 Else 0 End)

,TBD = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 0 Then 1 Else 0 End)

FROM dbo.Document d

WHERE DocType = 1

AND DocStatus IN ('Active','Retired')

GROUP BY ITSDivisionID

It makes the whole query [highlight=#ffff11]much more difficult to read and maintain[/highlight], but good thing is it will accepted by the rule to manually create a Virtual Table in Dundas Dashboard.

I have not figured out how to do the math.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

halifaxdal (4/14/2014)
As WITH is not allowed in this case, I did some replacement:
FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))
FYPrevStart = DateAdd(yy,-1,FYStart)
FYPrevStart = DateAdd(yy,-1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))
FYNextStart = DateAdd(yy,1,FYStart)
FYNextStart = DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))
The final working code is:
SELECTITSDivisionID
,A = Sum(Case When d.DocStatus = 'Retired' and d.RetiredDate >= DateAdd(yy,-1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) and d.RetiredDate < DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)) Then 1 Else 0 End)
,B = Sum(Case When d.DocStatus = 'Active' Then 1 Else 0 End)
,C = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)) and d.TargetClosureDate < DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) Then 1 Else 0 End)
,D = Sum(Case When d.DocStatus = 'Active' and d.TargetClosureDate >= DateAdd(yy,1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) Then 1 Else 0 End)
,Critical = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 4 Then 1 Else 0 End)
,High = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 3 Then 1 Else 0 End)
,Medium = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 2 Then 1 Else 0 End)
,Low = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 1 Then 1 Else 0 End)
,TBD = Sum(Case When d.DocStatus = 'Active' and d.RiskRating = 0 Then 1 Else 0 End)
FROM dbo.Document d
WHERE DocType = 1
AND DocStatus IN ('Active','Retired')
GROUP BY ITSDivisionID
It makes the whole query [highlight=#ffff11]much more difficult to read and maintain[/highlight], but good thing is it will accepted by the rule to manually create a Virtual Table in Dundas Dashboard.
I have not figured out how to do the math.

Here is the code rewritten without the CTE, curious to see if the derived tables will work in Dundas.

SELECT

ITSDivisionID