Actually i was wondering like how to create three sub columns for a main column i.e like Age is the mail column and it has 3 sub columns

Question

Post reply

Actually i was wondering like how to create three sub columns for a main column i.e like Age is the mail column and it has 3 sub columns

vinodvadla

Old Hand

Points: 310
More actions
July 7, 2010 at 11:46 am

#234883

Select
A.PlanID,
A.[Month-Year],
Case
When AgeDiff between 0 and 12 then '0-12'
When AgeDiff between 13 and 18 then '13-18'
When AgeDiff between 19 and 64 then '19-64'
When AgeDiff > 64 then '65+'
end as [Age group],
Count(members) as [Unique utilizers],
A.CUnits,
A.UnitsConverted,
A.[Month]
From
(
Select
TP.PlanID,
Case
When Month(CP.PmtDate) = 1 Then 'Jan' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 2 Then 'Feb' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 3 Then 'Mar' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 4 Then 'Apr' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 5 Then 'May' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 6 Then 'Jun' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 7 Then 'Jul' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 8 Then 'Aug' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 9 Then 'Sep' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 10 Then 'Oct' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 11 Then 'Nov' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 12 Then 'Dec' + '-' + Convert(varchar(4),Year(CP.PmtDate))
end as [Month-Year],
--Convert(varchar(10),Month(PmtDate) ) +'/'+ Convert(varchar(10),Year(PmtDate) ) as [Month/Year],
--DateDiff( year , TM.DOB, A.FromDate) ,
DateDiff( year , TM.DOB, A.FromDate) as AgeDiff,
Count(Distinct TM.MemberID) as Members,
Sum(A.CUnits) as CUnits,
--CT.TranTypeID,
Sum(CT.TranUnitsConverted) as UnitsConverted,
Month(CP.PmtDate) as [Month]
From
FcHistory.dbo.tdwClaims A
Inner Join FcCore.dbo.tCMMembers TM on A.MemberID = TM.MemberID
Inner Join FCHistory.dbo.tdwclaimTransactions CT on A.ClLineID = CT.ClLineID
Inner Join FcCore.dbo.tPlans TP on A.PlanID = TP.PlanID
Inner Join FCHistory.dbo.tdwclaimPayments CP on A.ProviderID = CP.ProviderID
Where
CP.PmtDate Between '04/01/2010' and '06/30/2010' and
TP.PlanID=15
Group By
Month(CP.PmtDate),Year(CP.PmtDate),TP.PlanID ,TM.DOB,A.FromDate,TM.MemberID, DateDiff( year , TM.DOB, A.FromDate),
Case
When Month(CP.PmtDate) = 1 Then 'Jan' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 2 Then 'Feb' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 3 Then 'Mar' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 4 Then 'Apr' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 5 Then 'May' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 6 Then 'Jun' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 7 Then 'Jul' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 8 Then 'Aug' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 9 Then 'Sep' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 10 Then 'Oct' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 11 Then 'Nov' + '-' + Convert(varchar(4),Year(CP.PmtDate))
When Month(CP.PmtDate) = 12 Then 'Dec' + '-' + Convert(varchar(4),Year(CP.PmtDate))
end ,Month(CP.PmtDate)
) A
group By
A.PlanID,A.[Month-Year],A.CUnits,A.UnitsConverted,A.[Month],
Case
When AgeDiff between 0 and 12 then '0-12'
When AgeDiff between 13 and 18 then '13-18'
When AgeDiff between 19 and 64 then '19-64'
When AgeDiff > 64 then '65+'
end
Order by A.[Month],A.[Month-Year],A.[Age Group]
FcHistory.dbo.tdwClaims A
FcCore.dbo.tCMMembers TM on A.MemberID = TM.MemberID
FCHistory.dbo.tdwclaimTransactions CT on A.ClLineID = CT.ClLineID
FcCore.dbo.tPlans TP on A.PlanID = TP.PlanID
FCHistory.dbo.tdwclaimPayments CP on A.ProviderID = CP.ProviderID
These are all the tables that i am using to create a Age Group Column and with three sub columns in it like UniqueUtilizers, CUnits(charged Units) and UnitsConverted( Paid Units) please help me out with the code and actually i am very new to SQL so please forgive me if the question seems to be confusing.
Thanks
Vinod

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

not really sure what your question is. Can you pare down your code and add a little more detail to your question?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 2

Actually,

there is already an ongoing discussion here.

@vinod

opening an identical thread just because you don't seem to like the replies you get is not really a professional way...

As you might notice, even in your new thread you get the same answer like on the other one. Just from a different person...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]