Actually i was wondering like how to write a sql code for combining 3 different columns into a single column

Question

Actually i was wondering like how to write a sql code for combining 3 different columns into a single column

vinodvadla

Old Hand

Points: 310
More actions
July 6, 2010 at 2:30 pm

#223613

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(A.FromDate) = 1 Then 'Jan' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 2 Then 'Feb' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 3 Then 'Mar' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 4 Then 'Apr' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 5 Then 'May' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 6 Then 'Jun' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 7 Then 'Jul' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 8 Then 'Aug' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 9 Then 'Sep' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 10 Then 'Oct' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 11 Then 'Nov' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 12 Then 'Dec' + '-' + Convert(varchar(4),Year(A.FromDate))
end as [Month-Year],
--Convert(varchar(10),Month(FromDate) ) +'/'+ Convert(varchar(10),Year(FromDate) ) 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(A.FromDate) 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
Where
A.FromDate Between '04/01/2010' and '06/30/2010' and
TP.PlanID=15
Group By
Month(A.FromDate),Year(A.FromDate),TP.PlanID ,TM.DOB,A.FromDate,TM.MemberID, DateDiff( year , TM.DOB, A.FromDate),
Case
When Month(A.FromDate) = 1 Then 'Jan' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 2 Then 'Feb' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 3 Then 'Mar' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 4 Then 'Apr' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 5 Then 'May' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 6 Then 'Jun' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 7 Then 'Jul' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 8 Then 'Aug' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 9 Then 'Sep' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 10 Then 'Oct' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 11 Then 'Nov' + '-' + Convert(varchar(4),Year(A.FromDate))
When Month(A.FromDate) = 12 Then 'Dec' + '-' + Convert(varchar(4),Year(A.FromDate))
end ,Month(A.FromDate)
) 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]
This is the code in which i get Age group as A SEPERATE TABLE BUT I WANT IT TO BE A COMMON TABLE AND UNDER THAT TABLE I WANT TO ADD UNIQUE UTILIZERS,CUNITS AND UNTISCONVERTED SO PLEASE HELP ME OUT.
THANKS

Viewing 9 posts - 1 through 8 (of 8 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide ready to use table def and sample data as described in the first link in my signature. It'll help us help you.

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]

vinodvadla Old Hand Points: 310 More actions · Answer 2

What exactly you want me to provide LUTZ.

Thanks & Rgrds,

Vinod

LutzM SSC Guru Points: 107049 More actions · Answer 3

vinodvadla (7/6/2010)
What exactly you want me to provide LUTZ.
Thanks & Rgrds,
Vinod

Table def in the format of CREATE TABLE FcHistory.dbo.tdwClaims () for all tables use in the query.

Fake but consistent sample data in the format INSERT INTO table SELECT values UNION ALL.

Expected result set based on your sample data.

You'll find a more detailed description when following the link I pointed you at in my previous post.

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]

vinodvadla Old Hand Points: 310 More actions · Answer 4

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

These are all the tables from which i am extracting the data required to me and in the tCMMembers table i have a cloumn DOB(date of birth)

and in the other table tDWClaims i have the column FromDate which i am using to calculate the Age of the provider, and under this Column i want to add three sub columns of Members, CharedUnits(CUnits),UnitsConverted(PaidUnits). So my question was that how casn i do it, since if i write the above code it shows Age as a seperate column.

LutzM SSC Guru Points: 107049 More actions · Answer 5

It seems like you didn't bother to read and follow the article I pointed you at or at least follow the recommendation I made in my previous post.

It leaves the impression of the issue being less important to you.

I'll drop it to the same level then...

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]

vinodvadla Old Hand Points: 310 More actions · Answer 6

Its not that way Lutz i am actually very new to SQL coding and i am not able to understand or get how exactly i can write the question in a simple way and it is a serious problem for me, So if u can please brief out like what exactly you want me provide.

Thanks

Vinod

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

vinodvadla (7/7/2010)
Its not that way Lutz i am actually very new to SQL coding and i am not able to understand or get how exactly i can write the question in a simple way and it is a serious problem for me, So if u can please brief out like what exactly you want me provide.
Thanks
Vinod

Ok, last change, go to that link, READ it and do what it tells you to.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Wesley Norton SSCrazy Points: 2827 More actions · Answer 8

Well, I must be pretty bored...

Table Creation Script:

if object_id('tempdb..#tCMMembers')is not null drop table #tCMMembers

if object_id('tempdb..#tdwclaimTransactions')is not null drop table #tdwclaimTransactions

if object_id('tempdb..#tPlans')is not null drop table #tPlans

if object_id('tempdb..#tdwClaims')is not null drop table #tdwClaims

CREATE TABLE #tCMMembers (

[DOB] smalldatetime not null,

[MemberID] int identity(1,1) not null

)

CREATE TABLE #tdwclaimTransactions(

[TranUnitsConverted] int not null,

[ClLineID] int identity(1,1) not null

)

CREATE TABLE #tPlans(

[PlanFoo] char(1) not null,

[PlanID] int identity(1,1) not null

)

CREATE TABLE #tdwClaims(

[ClLineID] int not null,

[MemberID] int not null,

[FromDate] smalldatetime not null,

[CUnits] int not null,

[PlanID] int identity(1,1) not null

)

Table Population Script:

INSERT #tCMMembers

SELECT '01/01/1972' UNION ALL

SELECT '04/21/1967' UNION ALL

SELECT '02/28/1935' UNION ALL

SELECT '09/01/1982' UNION ALL

SELECT '11/11/1947'

INSERT #tdwclaimTransactions

SELECT 10045 UNION ALL

SELECT 84522 UNION ALL

SELECT 99658 UNION ALL

SELECT 31102 UNION ALL

SELECT 78555

INSERT #tPlans

SELECT 'A' UNION ALL

SELECT 'B' UNION ALL

SELECT 'C'

INSERT #tdwClaims

SELECT 1,1,'04/02/2010',52 UNION ALL

SELECT 2,2,'04/15/2010',345 UNION ALL

SELECT 3,3,'04/24/2010',2 UNION ALL

SELECT 4,1,'05/03/2010',84 UNION ALL

SELECT 5,3,'05/09/2010',484 UNION ALL

SELECT 1,4,'05/27/2010',102 UNION ALL

SELECT 2,5,'06/02/2010',42 UNION ALL

SELECT 3,3,'06/12/2010',55 UNION ALL

SELECT 4,1,'06/15/2010',998 UNION ALL

SELECT 5,2,'06/29/2010',741

Your Script (edited to work with temp tables and fake planid's):

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(A.FromDate) = 1 Then 'Jan' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 2 Then 'Feb' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 3 Then 'Mar' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 4 Then 'Apr' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 5 Then 'May' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 6 Then 'Jun' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 7 Then 'Jul' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 8 Then 'Aug' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 9 Then 'Sep' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 10 Then 'Oct' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 11 Then 'Nov' + '-' + Convert(varchar(4),Year(A.FromDate))

When Month(A.FromDate) = 12 Then 'Dec' + '-' + Convert(varchar(4),Year(A.FromDate))

end as [Month-Year],

--Convert(varchar(10),Month(FromDate) ) +'/'+ Convert(varchar(10),Year(FromDate) ) 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(A.FromDate) as [Month]

From

#tdwClaims A

Inner Join #tCMMembers TM on A.MemberID = TM.MemberID

Inner Join #tdwclaimTransactions CT on A.ClLineID = CT.ClLineID

Inner Join #tPlans TP on A.PlanID = TP.PlanID

Where

A.FromDate Between '04/01/2010' and '06/30/2010'

--and TP.PlanID=15

Group By

Month(A.FromDate),Year(A.FromDate),TP.PlanID ,TM.DOB,A.FromDate,TM.MemberID, DateDiff( year , TM.DOB, A.FromDate),