April 18, 2008 at 6:40 am
I am attempting to add the previous period "ytd gross written premium" held in the dw.Finance_Pivot_MG table (say January data) with the current gross premium written in the stg.UKRIS_Policy table (say February) and then add a new record in the dw.Finance_Pivot_MG table which hold the updated ytd premium value. The quesries are correct in themselves but SQL will not allow the aggregation of a sub query
INSERT INTO dw.Finance_Pivot_MG
([year],
[month],
[pol_no],
[written_premium],
[ytd_written_premium])
SELECT
[year],
[month],
pol_no,
gross_written_premium,
sum(gross_written_premium +
(SELECT [month],ytd_written_premium,pol_no
FROM dw.Finance_Pivot_MG
where [month]=1 and [year]=2008
group by [month],ytd_written_premium,pol_no))
FROM
stg.UKRIS_Policy
group by [year],[month],pol_no, gross_written_premium
Any suggestions?
April 18, 2008 at 6:46 am
Move the subquery into the from clause and treat it as a derived table.
Something like
SELECT
[year],
[month],
pol_no,
gross_written_premium,
sum(gross_written_premium + ytd_written_premium)
FROM
stg.UKRIS_Policy Pol inner join
(SELECT ytd_written_premium,pol_no
FROM dw.Finance_Pivot_MG
where [month]=1 and [year]=2008
) sub on sub.pol_no = Pol.pol_id
group by [year],[month],pol_no, gross_written_premium
You'll probably have to tweak the code. I didn't test it, cause there's no schema or sample data. It should give you the idea though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2008 at 6:47 am
Your query is rather interesting, particularly the
sum(gross_written_premium + ( SELECT [month],
ytd_written_premium,
pol_no
...
What is this supposed to do? The subselect returns three columns,
Did you have something like
sum(gross_written_premium + ( SELECT SUM(ytd_written_premium)
FROM dw.Finance_Pivot_MG
where [month] = 1
and [year] = 2008
and pol_no = ...
))
...
in mind? In which case you could have moved the subselect outside with a join statement.
Regards,
Andras
April 18, 2008 at 8:48 am
Moving the subquery and Tweaking the code has provided the right answer.
Thanks
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply