July 6, 2010 at 2:30 pm
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
July 6, 2010 at 2:37 pm
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.
July 6, 2010 at 3:00 pm
What exactly you want me to provide LUTZ.
Thanks & Rgrds,
Vinod
July 6, 2010 at 3:25 pm
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.
July 7, 2010 at 7:27 am
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.
July 7, 2010 at 11:24 am
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...
July 7, 2010 at 11:29 am
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
July 7, 2010 at 11:33 am
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://qa.sqlservercentral.com/articles/Best+Practices/61537/
July 8, 2010 at 11:47 am
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),
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]
Now, the one thing I can't do for you is tell you what you want your results to look like. So what are you asking?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply