October 31, 2013 at 11:25 am
Actually, the missing months was an error on my part when providing a sampling. Both tables have equals months between them.
I constructed a cte, however, I'm still having some issues with the output. I'm getting the following errors below: Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
I tried casting the date, but still had issues. My date field format is: YYYYMM
Your insight is appreciated.
;WITH cte1 AS
(
SELECT
(ContractCode+'-'+ BenefitPlanCode)Product,
AdmitCCYYMM,
SUM(AmountPaid)Cost
From factAdmissions
GROUP BY
ContractCode,
BenefitPlanCode,
AdmitCCYYMM
),
cte2 AS
(
SELECT
(ContractCode+'-'+BenefitPlanCode)Product,
EffectiveCCYYMM,
Count(MemberId) AS numberofMembers
FROM
factmembership
GROUP BY
ContractCode,
BenefitPlanCode,
EffectiveCCYYMM
)
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)
dwain.c (10/2/2013)
Which code?Guessing here but I see that both your tables (from output results) have missing months. You'll need to fill in those missing months with a Calendar or Tally table.
Try putting both SELECTs (from the separate tables) into separate CTEs and then doing a LEFT JOIN (in each CTE) to the Calendar table to generate the dates. You can use these CTEs (using the INNER JOIN I did) to put rows into the #NewTable created with my Quirky Update method.
If that explanation isn't sufficient, feel free to ask more questions but I strongly recommend you Google "Calendar table" first so you can get that concept.
November 1, 2013 at 12:11 pm
Can someone please chime on my last post, I really need some insight.
November 1, 2013 at 1:22 pm
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
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2013 at 2:16 pm
Thanks for repyling. I tried your code below and got :Msg 174, Level 15, State 1, Line 32
The substring function requires 3 argument(s).
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
November 2, 2013 at 9:12 pm
Just an extra closing paren.
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)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2013 at 10:37 pm
I have added the extra closing paraenthesis, but still getting the same error.
LinksUp (11/2/2013)
Just an extra closing paren.
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)
November 2, 2013 at 11:09 pm
Briceston (11/2/2013)
I have added the extra closing paraenthesis, but still getting the same error.LinksUp (11/2/2013)
Just an extra closing paren.
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)
No, there was an extra closing paren. I removed it. What is posted above should work without error.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 3, 2013 at 5:31 pm
Not sure if this is still helpful but here's a pretty straightforward way to convert YYYYMM to a DATETIME:
DECLARE @YYYYMM VARCHAR(6) = '201203';
SELECT CAST(STUFF(@YYYYMM + '-01', 5, 1, '-') AS DATETIME);
You might want to take a gander at this article which compares various ways to calculate running totals, specifically to identify what is the fastest approach:
Calculating Values within a Rolling Window in Transact SQL[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 8:54 am
Links up and dwain c thank you both for your replies.
Links up - I removed the extra parenthesis and the query ran fine, but my results were off. I have to revisit my logic and see where I have gone wrong. I have way more rows than expected and duplication in my dates.
dwain.c- thanks for your insight below on how to convert as well as the link to the article.
Hopefully, I can gain some additional insight as I read through it.
dwain.c (11/3/2013)
Not sure if this is still helpful but here's a pretty straightforward way to convert YYYYMM to a DATETIME:
DECLARE @YYYYMM VARCHAR(6) = '201203';
SELECT CAST(STUFF(@YYYYMM + '-01', 5, 1, '-') AS DATETIME);
You might want to take a gander at this article which compares various ways to calculate running totals, specifically to identify what is the fastest approach:
Calculating Values within a Rolling Window in Transact SQL[/url]
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply