Joining tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining tables

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-02-24 : 08:48:20

Hello -

I've summarized data from several tables regarding claims dollars (by month) and subscribers be month. When I summarize the claims dollars further I get good accurate results. However, when I add in the subscriber information by month I do not get any results. Initially, I thought the dates were not formatted accurately so I tried converting them first but that did not work. Does anyone have any ideas:

Here is a sample of the code.
Step 1:
select convert(varchar(10), Month_of_Service , 101) as Period, sum(Medical) as Medical
into [NHPDOMAIN\johnb].COBTest_Medical_12012013_11302014_1_FinalReport
from [NHPDOMAIN\johnb].COBTest_Medical_12012013_11302014_1
group by Month_of_Service
order by Month_of_Service

select convert(varchar(10), Month_of_Service , 101) as Period, sum(Pharmacy) as Pharmacy
into [NHPDOMAIN\johnb].COBTest_Pharmacy_12012013_11302014_1_FinalReport
from [NHPDOMAIN\johnb].COBTest_Pharmacy_12012013_11302014_1
group by Month_of_Service
order by Month_of_Service

select convert(varchar(10), ReportMonth , 101) as Period, sum(CapAmount) as Capitation
into [NHPDOMAIN\johnb].COBTest_Cap_12012013_11302014_1_FinalReport
from [NHPDOMAIN\johnb].COBTest_CAP_12012013_11302014_1
group by ReportMonth
order by ReportMonth

--subs
select convert(varchar(10), ReportMonth, 101) as Period, case when ratecode = 'Individual' then 'Individual' when ratecode = 'Individual and 1 Dependent' then 'Individual & Child' when ratecode = 'Individual and Dependents' then 'Individual & Child' when ratecode = 'Individual and Family' then 'Family' when ratecode = 'Individual and Spouse' then 'Individual and Spouse' when ratecode = '** NO RATE CODE **' then 'Individual' end as RateCode, sum(All_Subs) as All_Subs
into [NHPDOMAIN\johnb].COBTest_Subs_12012013_11302014_1_FinalReport
from [NHPDOMAIN\johnb].COBTest_Subs_12012013_11302014_1
group by ReportMonth, case when ratecode = 'Individual' then 'Individual' when ratecode = 'Individual and 1 Dependent' then 'Individual & Child' when ratecode = 'Individual and Dependents' then 'Individual & Child' when ratecode = 'Individual and Family' then 'Family' when ratecode = 'Individual and Spouse' then 'Individual and Spouse' when ratecode = '** NO RATE CODE **' then 'Individual' end
order by ReportMonth

Step 2:
select m.Period, sum(m.Medical) as Medical, sum(p.Pharmacy) as Pharmacy, sum(c.Capitation) as Capitation, sum(m.Medical+p.Pharmacy+c.Capitation) as Total,
sum( case when RateCode = 'Individual' then all_subs end) as 'Individual'

from [NHPDOMAIN\johnb].COBTest_Medical_12012013_11302014_1_FinalReport m

inner join
[NHPDOMAIN\johnb].COBTest_Pharmacy_12012013_11302014_1_FinalReport p
on m.period = p.period

inner join
[NHPDOMAIN\johnb].COBTest_Cap_12012013_11302014_1_FinalReport c
on m.period = c.period

inner join
[NHPDOMAIN\johnb].COBTest_Subs_12012013_11302014_1_FinalReport s
on m.period = s.period

group by m.period

Thanks,

John

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-24 : 18:11:31

I would keep the period field as type "date" right up till "the last minute" (when you display the result in the last query).
Also keep the same "format" in the "group by" section. Ex:

select cast(Month_of_Service as date) as Period, sum(Medical) as Medical 
	into [NHPDOMAIN\johnb].COBTest_Medical_12012013_11302014_1_FinalReport 
	from [NHPDOMAIN\johnb].COBTest_Medical_12012013_11302014_1 
	group by cast(Month_of_Service as date)

No need for the "order by" section in the initial queries and the "group by" section in your last query (as the initial queries already did the sum/group by).
Lastly, check that you have the same dates in the subs table as you have in the medical table (same month and year). If not, you might be looking for "left outer join" instead of "inner join" on the subs table.

This can all be done in one query, without creating new tables.
Ofcause I don't know, if you actually need the tables from he initial queries and I don't know the layout of your tables, but I'm guessing the following query might do the job (perhaps with some tweaking):

select convert(varchar(8)
              ,coalesce(med.Period
                       ,pha.Period
                       ,cap.Period
                       ,sub.Period
                       )
              ,101
              ) as Period
      ,isnull(med.Medical,0) as Medical
      ,isnull(pha.Pharmacy,0) as Pharmacy
      ,isnull(cap.Capitation,0) as Capitation
      ,isnull(med.Medical,0)+isnull(pha.Pharmacy,0)+isnull(cap.Capitation,0) as Total
      ,isnull(sub.Individual,0) as Individual
      ,isnull(sub.[Individual & Child],0) as [Individual & Child]
      ,isnull(sub.Family,0) as Family
      ,isnull(sub.[Individual and Spouse],0) as [Individual and Spouse]
  from (select cast(Month_of_Service as date) as Period
              ,sum(Medical) as Medical
          from [NHPDOMAIN\johnb].COBTest_Medical_12012013_11302014_1
         group by cast(Month_of_Service as date)
       ) as med
       full outer join (select cast(Month_of_Service as date) as Period
                              ,sum(Pharmacy) as Pharmacy
                          from [NHPDOMAIN\johnb].COBTest_Pharmacy_12012013_11302014_1
                         group by cast(Month_of_Service as date)
                       ) as pha
                    on pha.Period=coalesce(med.Period,cap.Period,sub.Period)
       full outer join (select cast(ReportMonth as date) as Period
                              ,sum(CapAmount) as Capitation
                          from [NHPDOMAIN\johnb].COBTest_CAP_12012013_11302014_1
                         group by cast(ReportMonth as date)
                       ) as cap
                    on cap.Period=coalesce(med.Period,pha.Period,sub.Period)
       full outer join (select cast(ReportMonth as date) as Period
                              ,sum(case when ratecode='Individual'
                                          or ratecode='** NO RATE CODE **'         then All_Subs else 0 end) as Individual
                              ,sum(case when ratecode='Individual and 1 Dependent'
                                          or ratecode='Individual and Dependents'  then All_Subs else 0 end) as [Individual & Child]
                              ,sum(case when ratecode='Individual and Family'      then All_Subs else 0 end) as Family
                              ,sum(case when ratecode='Individual and Spouse'      then All_Subs else 0 end) as [Individual and Spouse]
                          from [NHPDOMAIN\johnb].COBTest_Subs_12012013_11302014_1
                         group by cast(ReportMonth as date)
                       ) as sub
                    on sub.Period=coalesce(med.Period,pha.Period,cap.Period)

Sorry for any syntax errors, but I was not able to test, as I didn't have the layout of the tables and sample data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources