LinksUp (11/1/2013)

---

Briceston (10/31/2013)

---

I tried casting the date, but still had issues. My date field format is: YYYYMM

SELECT

(COALESCE(a1.Cost,0) + COALESCE(a2.cost,0) + COALESCE(a3.cost,0))/(3.0*b.numberofMembers)

FROM

cte1 a1

INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM

LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = DATEADD(Month,1,a2.AdmitCCYYMM)

LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = DATEADD(Month,2,a3.AdmitCCYYMM)

From an earlier post

. . .

Contract Varchar(4),

Admissiondate Varchar(6),

I am guessing that AdmitCCYYMM is the same as Admissiondate. You are trying to do date arithmetic on a VarChar. Casting it does not help because of the format you have chosen. (YYYYMM) When you Cast('201301' as Date) the Cast sees year = 20, (2020), month=13 and day = 01. And of course the month is out of bounds.

You could convert AdmissionDate to a proper datetime in the table and this problem will go away. Or do something like DATEADD(Month,1,a2.AdmitCCYYMM + '01'). But this gives a couple more issues to worry about.

1. A time component is added and has to be accounted for when comparing for equality

2. This has to be done on both sides of the = and that brings Sargeability issues into play.

Or you could do something like:

SELECT SubString(CONVERT(varchar, DATEADD(MM, 1, '201301'+'01'), 112), 1, 6)

Returns 201302

INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM

LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,1,a2.AdmitCCYYMM) + '01'), 112), 1, 6)

LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,2,a3.AdmitCCYYMM) + '01'), 112), 1, 6)

These are just some thoughts off the top of my head.

HTH