Projection-Forecast used in a query
-
Hi,
i need help on this.i have a query which is as follows
QUERY
SELECT count(Distinct PolicyNumber)as Count,CONVERT(varchar(7) ,EffectiveDate, 120)as Period,Month_of_file
from GE_Transaction
WHERE Status='A' AND DATEADD(year,10,EffectiveDate) > (DATEADD(mm, 1,MonthReported) - 1) and Month_of_file='20060901'
GROUP BY CONVERT(varchar(7), EffectiveDate, 120),Month_of_file
order by Period asc
When i run the above query i get the following result set as below
Count Period Month_of_file
619 1996-11 2006-09-01 00:00:00.000
550 1996-10 2006-09-01 00:00:00.000
1878 1996-12 2006-09-01 00:00:00.000
3047 1996 200609
which means that the Total Count for the period 1996 in the Month_of_file of sept-06 is 3047(550+619+1878 =3047)
What i want is to write a query and take the dates FROM the column Period Add 10 years to it and then deduct the relevent count for that period.My
Result set should look like this as below .
In other words
Now take earliest date from the column Period which is "1996-10" and add 10 years to it and also the count "550" for that period.
now after adding 10 years to 1996-10 + 10 years = it becomes 200610 and our result set should look like this when we execute the query
200609 3047
200610 3047-550(deduct count for that period) which comes out to be 2497 and so on
200611 2497-619 = 1878
-
At first guess and off the top of my head (untested)
CREATE TABLE #temp (Period char(6),PolicyNumber int)
INSERT INTO #temp (Period,PolicyNumber)
SELECT DISTINCT CONVERT(char(6),DATEADD(year,10,a.Period),112),PolicyNumber
FROM GE_Transaction
WHERE Status='A' AND DATEADD(year,10,EffectiveDate) > (DATEADD(mm, 1,MonthReported) - 1) and Month_of_file='20060901'
SELECT a.Period,COUNT(*) AS [Count]
FROM (SELECT DISTINCT Period FROM #temp) a
INNER JOIN #temp b ON b.Period <= a.Period
GROUP BY a.Period
ORDER BY a.Period ASC
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
i get the following error message when i execute the above suggested query
Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'a' does not match with a table name or alias name used in the query.
-
Ooops! sorry
Remove the a. in the first select from
SELECT DISTINCT CONVERT(char(6),DATEADD(year,10,a.Period),112),PolicyNumber
to
SELECT DISTINCT CONVERT(char(6),DATEADD(year,10,Period),112),PolicyNumber
Far away is close at hand in the images of elsewhere.
Anon. -
still gives an error message
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Period'.
-
Damn
I did say untestedReplace
SELECT DISTINCT CONVERT(char(6),DATEADD(year,10,Period),112),PolicyNumber
with
SELECT DISTINCT CONVERT(char(6),DATEADD(year,10,EffectiveDate),112),PolicyNumber
Far away is close at hand in the images of elsewhere.
Anon. -
still not clear getting following message
Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'FFG1212267' to a column of data type int.
-
Change the datatype of PolicyNumber in the CREATE TABLE
CREATE TABLE #temp (Period char(6),PolicyNumber int)
to the datatype of your data
Far away is close at hand in the images of elsewhere.
Anon. -
CREATE TABLE #temp (Period char(6),PolicyNumber varchar)
INSERT INTO #temp (Period,PolicyNumber)
SELECT DISTINCT CONVERT(char(6),DATEADD(year,10,EffectiveDate),112),PolicyNumber
FROM GE_Transaction
WHERE Status='A' AND DATEADD(year,10,EffectiveDate) > (DATEADD(mm, 1,MonthReported) - 1) and Month_of_file='20060901'
SELECT a.Period,COUNT(*) AS [Count]
FROM (SELECT DISTINCT Period FROM #temp) a
INNER JOIN #temp b ON b.Period <= a.Period
GROUP BY a.Period
ORDER BY a.Period ASC
DROP TABLE #temp
still get this error
Server: Msg 8152, Level 16, State 9, Line 2
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
-
You must size the varchar for PolicyNumber
varchar is the same as varchar(1) one character/byte
Far away is close at hand in the images of elsewhere.
Anon. -
Hahaha! I really enjoy reading the thread when it's posted by Best!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply