This is driving people crazy!

There is another sp to be converted and this sp contains loop and lots of calculation. The original sp is working fine and logic is very...

thank you so much for your quick reply, other than you forgot the End for the Case, it's working perfectly.

That was quick and simple, thank you.

dbalmf (3/25/2014)

---

Hi,

My Take on it.. 🙂

WITH DocCTE (DivIDCte, DivIDCount)

AS

(SELECT DivID,

COUNT(DivID)

FROM Doc GROUP BY DivID)

SELECTID,

Div,

'Count' =...

I am looking for an...

Jeff Moden (3/17/2014)

---

halifaxdal (3/17/2014)

---

Too bad! Only beautifully working on my local 2k8, when I run it on server 2k5, it fails with the term "VALUES"

CROSS APPLY( VALUES(1, SUBSTRING(d.Category, 1,...

Too bad! Only beautifully working on my local 2k8, when I run it on server 2k5, it fails with the term "VALUES"

CROSS APPLY( VALUES(1, SUBSTRING(d.Category, 1, 1)),

is VALUES new...

Beautifully working!!! Thank you so much.

Bingo!

Temp table doesn't have that restriction! All I need to do is to create temp table instead of a static table

Hello, I finally end up with the following code which is working as expected:

ALTER PROC ddGetDevStatsByUnitStateAndYear (@Unit varchar(20), @State varchar(10), @Year int)

AS

IF @State = 'All'

BEGIN

IF @Year = 0

BEGIN

SELECT...

Thanks for reply, maybe I didn't make it clearer, I decided to go this way:

ALTER PROC ddGetDevStatsByUnitStateAndYear (@Unit varchar(20), @State varchar(10), @Year int)

AS

IF @Year = 0

BEGIN

SELECT Unit = isnull(i.Division,...

Steve Jones - SSC Editor (2/28/2014)

---

Personally, I like the logic clear so if I glance at this in a year, or someone else does, it makes sense. I'd do this:

DECLARE...

Will this works?

((@Year = (Case @State When 'Open' Then CAST(YEAR(DateFirstIssued) AS int) When 'Closed' Then CAST(YEAR(RetiredDate) AS int) Else @Year End ) and @Year > 0) or

(@Year = 0 ))

Isnull won't work in my case as the where clause doesn't return any record.

I ended up using

if not exists (select ....)

insert into

select.....

insert into

select 0,0....

But I believe there...

Things getting complicated now:

The script help me get the sum for the specific reporting period, now I am asked to include the ""so far" sum:

for example, currently the script will...