February 2, 2012 at 1:37 am
Hi guys, I am struggling with a query.
I have a date field, if the date falls under the currnent month, assign the value to this field, mif it has the previous months date, assign value to another field.
How do I do this with a CASE statement.
I have this
SELECT pvr.PVR,
dd.FinancialMonthLabel AS InformationDateID,
dsgd.Environment,
dpb.packagetype,
CASE WHEN fpsm.InformationDateID = @CurrentMonthEndID
THEN sum(fpsm.NoOfUnits) as NoofUnits
ELSE sum(fpsm.NoOfUnits) as PreviousNoofUnits
END,
dpb.ProductCode,
dpb.productgroup
FROM #PVR pvr
left join bi_ss.dbo.FactProductSubscriptionMonthly fpsm
on pvr.SubscriberAccountID = fpsm.SubscriberAccountID --and pvr.sourceid = fpsm.SourceID
and pvr.SmartcardDeviceID = fpsm.SmartcardDeviceID
left join bi_dw.dbo.DimSubscriptionGroupingDetail dsgd
on fpsm.DimSubGrpDetID = dsgd.DimSubGrpDetID --and fpsm.SourceID = dsgd.SourceID
left join BI_DW.dbo.DimDate dd
on fpsm.InformationDateID = dd.DateID
left join bi_dw.dbo.DimPackageBroadcast dpb
on dsgd.PackageBroadcastID = dpb.PackageBroadcastID --and dsgd.SourceID = dpb.SourceID
WHERE
dsgd.SubscriptionGrouping <> 'PVR'
AND fpsm.InformationDateID IN( @PreviousMonthEndID, @CurrentMonthEndID)
-- and exists ( select MonthEndID from #MonthEndDates where MonthEndID = fpsm.InformationDateID)
GROUP BY
dd.FinancialMonthLabel,
pvr.pvr,
dsgd.Environment,
dpb.packagetype,
dpb.ProductCode,
dpb.ProductType,
dpb.productgroup
any ideas
Ian Cockcroft
MCITP BI Specialist
February 2, 2012 at 2:26 am
You would need two columns, something like:
...
NoofUnits = SUM(
CASE
WHEN fpsm.InformationDateID = @CurrentMonthEndID
THEN fpsm.NoOfUnits
ELSE 0
END),
PreviousNoofUnits = SUM(
CASE
WHEN fpsm.InformationDateID = @PreviousMonthEndID
THEN fpsm.NoOfUnits
ELSE 0
END),
...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 2:34 am
thanks Paul. did something similar, but you solution is alot more elagent
CASE WHEN fpsm.InformationDateID = @CurrentMonthEndID
THEN sum(fpsm.NoOfUnits)
ELSE 0
END NoofUnits,
CASE WHEN fpsm.InformationDateID = @PreviousMonthEndID
THEN sum(fpsm.NoOfUnits)
ELSE 0 random
END PreviousNoofUnits
Ian Cockcroft
MCITP BI Specialist
February 6, 2012 at 7:19 am
Thank you very much ,I did more search ,But i can't arrive any useful solutions ,
You save my time
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply