Sql Query Help..p.t

[tommy]

last lo group by statement ledu...

Man, code raasina athanu, intelligent enough gaa raasadu for his things to happen first.. but I think right now its taking time than before cos of the MAX on the top. count(*) badulu count(pt_pgm_id) pettu... big relief that is for ur query...

 

Last lo left join daggara ON tarvata AND condition undi kada change that to where...

 

Pretty mature query idi... easy gaaney undi kuda.. I have to head out so can't work more... I will see it and let you know after I come back but I am not sure how soon..

 

Thanks..
 

 

[ghazni]

CTE's are fast man... table variable only less data aitey good... temp tables huge data aitey good... intermediate level data CTE's are good... Jai CTE Jai Jai CTE
 

BUT IT DEPENDS TOO.....

[areyoosamba]

Man, code raasina athanu, intelligent enough gaa raasadu for his things to happen first.. but I think right now its taking time than before cos of the MAX on the top. count(*) badulu count(pt_pgm_id) pettu... big relief that is for ur query...

 

Last lo left join daggara ON tarvata AND condition undi kada change that to where...

 

Pretty mature query idi... easy gaaney undi kuda.. I have to head out so can't work more... I will see it and let you know after I come back but I am not sure how soon..

 

Thanks..
 

 

 

oka chinna doubt bossu.. Left join condition thesuka poye where clause lo pettedthe ade Inner Join avthundi kada .. it will not act as left join any more..

[loveindia]

 
WITH cntMissEvents AS
(
select pt_pgm_id, test_yr_num, analyte_id, 1 as evnt, count(pt_pgm_id) as evntcnt
FROM clia_pt_enrlmnt ce
where pt_scre_submsn_1_dt is not null and
not exists ( select 1
             FROM clia_pt_scre cs
             where ce.prvdr_num = cs.prvdr_num
                AND ce.pt_pgm_id = cs.pt_pgm_id
                AND ce.test_yr_num =cs.test_yr_num
                AND ce.analyte_id = cs.analyte_id
                AND evnt_num = 1
                AND ce.pt_scre_submsn_1_dt = cs.pt_scre_submsn_dt)
group by pt_pgm_id, test_yr_num, analyte_id
UNION
select pt_pgm_id, test_yr_num, analyte_id, 2 as evnt, count(pt_pgm_id) as evntcnt
FROM clia_pt_enrlmnt ce
where pt_scre_submsn_2_dt is not null and
not exists ( select 1
             FROM clia_pt_scre cs
             where ce.prvdr_num = cs.prvdr_num
                AND ce.pt_pgm_id = cs.pt_pgm_id
                AND ce.test_yr_num =cs.test_yr_num
                AND ce.analyte_id = cs.analyte_id
                AND evnt_num = 2
                AND ce.pt_scre_submsn_2_dt = cs.pt_scre_submsn_dt)
group by pt_pgm_id, test_yr_num, analyte_id
UNION
select pt_pgm_id, test_yr_num, analyte_id, 3 as evnt, count(pt_pgm_id) as evntcnt
FROM clia_pt_enrlmnt ce
where pt_scre_submsn_3_dt is not null and
not exists ( select 1
             FROM clia_pt_scre cs
             where ce.prvdr_num = cs.prvdr_num
                AND ce.pt_pgm_id = cs.pt_pgm_id
                AND ce.test_yr_num =cs.test_yr_num
                AND ce.analyte_id = cs.analyte_id
                AND evnt_num = 3
                AND ce.pt_scre_submsn_3_dt = cs.pt_scre_submsn_dt)
group by pt_pgm_id, test_yr_num, analyte_id
)
,cntTotEvents as (
select pt_pgm_id, test_yr_num, analyte_id, 1 as evnt, count(pt_pgm_id) as evntcnt
from clia_pt_enrlmnt
where pt_scre_submsn_1_dt is not null
group by pt_pgm_id, test_yr_num, analyte_id
union
select pt_pgm_id, test_yr_num, analyte_id, 2 as evnt, count(pt_pgm_id) as evntcnt
from clia_pt_enrlmnt
where pt_scre_submsn_2_dt is not null
group by pt_pgm_id, test_yr_num, analyte_id
union
select pt_pgm_id, test_yr_num, analyte_id, 3 as evnt, count(pt_pgm_id) as evntcnt
from clia_pt_enrlmnt
where pt_scre_submsn_3_dt is not null
group by pt_pgm_id, test_yr_num, analyte_id
)
SELECT cme.pt_pgm_id
        , cme.test_yr_num
        , cme.analyte_id
        , CASE WHEN cme.evnt = 1 THEN MAX(cme.evntcnt) END AS miss_event1
        , CASE WHEN cme.evnt = 2 THEN MAX(cme.evntcnt) END AS miss_event2
        , CASE WHEN cme.evnt = 3 THEN MAX(cme.evntcnt) END AS miss_event3
        , CASE WHEN cte.evnt = 1 THEN MAX(cte.evntcnt) END AS total_event1
        , CASE WHEN cte.evnt = 2 THEN MAX(cte.evntcnt) END AS total_event2
        , CASE WHEN cte.evnt = 3 THEN MAX(cte.evntcnt) END AS total_event3
from cntTotEvents cte
left join cntMissEvents cme on cte.pt_pgm_id = cme.pt_pgm_id
AND cte.test_yr_num = cme.test_yr_num
    AND cte.analyte_id = cme.analyte_id
GROUP BY pt_pgm_id, test_yr_num, analyte_id