/*detail- primary clincian/provider  	priemp_num joined to facemp Y
        primary service (act,cm,opt),           pripgm_num primary program,  
                                          select proc_cde from v_autsvc join svc 
                                           on proc_cde where pgm_num=pripgm_num
                                                H0039 then T1017 t1016 then H0036
        location of service,                     pgm_num of 90862 auth
        date of auth                             v_autsvc.aut_dte
        cons name/number:                        CLTCLT
        auth date range                          v_autsvc.autbeg_dte  v_autsvc.autend_dte
        number of claims in the last fiscal year  v_clmsvc, count(*) 
        primary psycchiatrist.                     cltdmo.pspemp_num join phy on phy_num


select clt_num, count(*) as num_claims from v_clmsvc
 where beg_dte between '10/1/2005' and '9/30/2006 23:59'
 group by clt_num

tables: v_autsvc, pgm, cltdmo.population='mi adult'
proc_cde in ('90862','90801')
autend_Dte>'10/1/06'

 I need a report showing all Adult MI consuemrs receiving psych services by agency- like the 
aggregate and detail of who gets them at where-bridgeways, sr srv... including our clinic.

i would need in the detail- primary clincian/provider, primary service (act,cm,opt), location of service, 
date of auth, cons name/number, auth date range/# of units and number of claims in the last fiscal year and 
primary psycchiatrist.  if xxx asks for something similar, let me know as we might be duplicating.  

i know that xxx is going to be asking for a report that is for just our clinic that has more to do with 
insurance type.  if it would be easier, you could combine the 2 reports as long as we have the capacity 
to sort and print by provider.  thanks*/

/**********************************************************************************
Primary Service
***********************************************************************************/
-- select primary proc_cde for primary service Candidates
drop table #ConsumerProcCodes
select distinct a.clt_num, 
	   a.proc_cde, 
       a.aut_dte, 
	   a.autbeg_dte, 
	   a.autend_dte
  into #ConsumerProcCodes
  from cltdmo d join v_autsvc a on a.clt_num = d.clt_num 
                               and d.pripgm_num = a.pgm_num
                               and a.autsts = 1 
                               and getdate() between a.autbeg_dte and a.autend_dte


-- create table of ProcCde Precedence
drop table  #ProcCde_Precedence
create table #ProcCde_Precedence
 (proc_cde varchar(10),
  Precedence int)

insert #ProcCde_Precedence
(proc_cde,Precedence)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100 union all
select 'H2015', 110 union all
select 'T1005', 120 

-- assign precedence to proc_cde
drop table #ConsumerProcCodesWPrec
select distinct c.clt_num, 
       c.aut_dte, 
       c.autbeg_dte, 
       c.autend_dte,
	   case when p.Precedence is null then 1000
            else p.Precedence
       end as Precedence,
 	   left(c.proc_cde,5) as proc_cde
  into #ConsumerProcCodesWPrec
  from #ConsumerProcCodes c left join #ProcCde_Precedence p on p.proc_cde = left(c.proc_cde,5)

-- select primary proc_cde for each consumer and get associated PrimaryService
drop table #PrimaryService
select distinct p1.clt_num, 
	   p1.proc_cde, 
	   p1.aut_dte, 
	   p1.autbeg_dte, 
	   p1.autend_dte,
       s.acttyp_des as PrimaryService
  into #PrimaryService
  from #ConsumerProcCodesWPrec p1 join svc s on p1.proc_cde = s.proc_cde
 where Precedence = (select min(Precedence)
                       from #ConsumerProcCodesWPrec p2
                      where p1.clt_num = p2.clt_num)

-- Find Consumers with more than one primary Proc_cde
/*
select clt_num, proc_cde, aut_dte, autbeg_dte, autend_dte
  from #PrimaryService
 where clt_num in (select clt_num 
                     from #PrimaryService
                    group by clt_num
                   having sum(1) > 1)
*/
/**********************************************************************************
Primary Location
***********************************************************************************/
-- select pgm_num for Primary location Candidates
drop table #ConsumerLOS
select distinct a.clt_num, 
       a.pgm_num, 
       p.pgm_nme
  into #ConsumerLOS
  from v_autsvc a join pgm p on a.pgm_num = p.pgm_num
 where a.autsts = 1 
   and getdate() between a.autbeg_dte and a.autend_dte
   and a.proc_cde = '90862'

-- create table of pgm_num for Location of Services Precedence
drop table  #LOS_Precedence
create table #LOS_Precedence
 (pgm_num int,
  Precedence int)

insert #LOS_Precedence
(pgm_num,Precedence)
select 5600, 10 union all
select 6200, 20 union all
select 6000, 30 union all
select 1611, 40 union all
select 1612, 50 union all
select 1601, 60 

-- assign precedence to pgm_num
drop table #ConsumerLOSwPrecedence
select distinct c.clt_num, 
	   case when p.Precedence is null then 1000
            else p.Precedence
       end as Precedence,
 	   c.pgm_num
  into #ConsumerLOSwPrecedence
  from #ConsumerLOS c left join #LOS_Precedence p on p.pgm_num = c.pgm_num


-- select pgm_num for primary location of Services for each consumer
drop table #PrimaryLOS
select distinct p1.clt_num, 
       case n.NumRows
            when 1 then cast(p1.pgm_num as varchar(10))
            else cast(p1.pgm_num as varchar(10)) + '*'
       end as LocationOfService
  into #PrimaryLOS
  from #ConsumerLOSwPrecedence p1 join (select clt_num,count(*) as NumRows
                                          from #ConsumerLOSwPrecedence 
                                         group by clt_num) n on p1.clt_num = n.clt_num
 where Precedence = (select min(Precedence)
                       from  #ConsumerLOSwPrecedence p2
                      where p1.clt_num = p2.clt_num)

  

-- Find Consumers with more than one primary pgm_cde for Location of Services.
/*select distinct clt_num from #PrimaryLOS
select * from  #PrimaryLOS
where clt_num in (select clt_num 
                     from #PrimaryLOS
                    group by clt_num
                   having sum(1) > 1)*/

/**********************************************************************************
Put it all together
***********************************************************************************/
drop table #FinalResults
select distinct c.clt_num,
	   c.fst_nme + ' ' + c.lst_nme as ConsumerName,
	   f.fst_nme + ' ' + f.lst_nme as PrimaryClinician,
	   p.fst_nme + ' ' + p.lst_nme as PrimaryPsychiatrist,
	   ps.PrimaryService,
	   COALESCE(n.num_claims,0) as num_claims,
       los.LocationOfService,
	   ps.aut_dte,
       ps.autbeg_dte,
       ps.autend_dte
  into #FinalResults
  from cltctl c join cltdmo d   on c.clt_num = d.clt_num and d.population = 'mi adult'
                join v_autsvc a on c.clt_num = a.clt_num  
                               and a.autsts = 1 
                               and getdate() between a.autbeg_dte and a.autend_dte
                               and a.proc_cde in ('90862','90801')
				join facemp f on d.priemp_num = f.facemp_num
                left join phy    p on d.pspemp_num = p.phy_num
                left join #PrimaryService ps on ps.clt_num = c.clt_num
				left join (select clt_num, count(*) as num_claims 
                             from v_clmsvc
 					        where beg_dte between '10/1/2005' and '9/30/2006 23:59'
                            group by clt_num) n on c.clt_num = n.clt_num
                left join #PrimaryLOS los on c.clt_num = los.clt_num             
 order by c.clt_num

select * from #FinalResults
/*
select * from #FinalResults
 where PrimaryService is null

select * from #FinalResults
 where LocationOfService is null

select * from #FinalResults
 where PrimaryPsychiatrist is null

select * from #FinalResults
 where PrimaryClinician is null

select * from #FinalResults
 where num_claims = 0

*/