'Change date format to Month and Date
How can I change this code to sum at the month level? For instance, DOL 2012-05-07 = May 2019. I think the fix needs to be Select Date of Loss line
select p.policyTerm as Term
, p.isRenewal as IsRenewal
, ci.dateOfLoss as DOL
, SUM(il.Amount) as NetIncurredLoss
from (
select ce.claimID as ClaimID
, 'Recoveries' as Type
, CAST(ce.paymentDate as date) as TransactionDate
, ce.amount as Amount
from Aggressive.dbo.ClaimExpense as ce with (nolock)
where (ce.isExpense = 0 and ce.ExpenseType = 0 and ce.RecoveryType = 1)
or (ce.isExpense = 0 and ce.ExpenseType = 0 and ce.RecoveryType = 2)
or (ce.isExpense = 2 and ce.ExpenseType = 0 and ce.RecoveryType = 3)
union all
select crl2.claimID as ClaimID
, 'Gross Incurred Loss' as Type
, CAST(crl2.addDate as date) as TransactionDate
, crl2.lossReserveChange as Amount
from Aggressive.dbo.ClaimReserveLog as crl2 with (nolock)
where crl2.lossReserveChange <> 0
and crl2.claimID <> 1
) as il
left join Aggressive.dbo.Claim as c with (nolock)
on c.claimID = il.ClaimID
left join Aggressive.dbo.ClaimIncident as ci with (nolock)
on ci.claimIncidentID = c.claimIncidentID
left join Aggressive.dbo.Policy as p with (nolock)
on p.policyID = ci.policyID
group by p.policyTerm
, p.isRenewal
, ci.dateOfLoss
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
