Sql Query Help..p.t

[tommy]

database lo changes cheyaniki privilages levvu.....so just tables chudanike anthe....

[andari bandhuvu]

use stored proc, instead of CTE's

 

Bhayya...What is CTE's... thittakandey.. nenu SQL ki kotta... yippudippude nerchukuntunna... bye1

[Vaampire]

chaala pedha query. table structure and requirement ardham kakunda em help cheyyaleru bhayya.

okka left join, case statement, union, group by. inni heavy functions same query lo use cheyyadam koncham risk ey

[SwamyRaRa]

chaala pedha query. table structure and requirement ardham kakunda em help cheyyaleru bhayya.

okka left join, case statement, union, group by. inni heavy functions same query lo use cheyyadam koncham risk ey

yeah..CTE, ha union lu group lu,count  vunnayi..

a part ki ha part divide chesi temp tables create chesi try cheye...

[SwamyRaRa]

Bhayya...What is CTE's... thittakandey.. nenu SQL ki kotta... yippudippude nerchukuntunna... bye1

http://blog.sqlauthority.com/2011/05/10/sql-server-common-table-expression-cte-and-few-observation/

[andari bandhuvu]

http://blog.sqlauthority.com/2011/05/10/sql-server-common-table-expression-cte-and-few-observation/

sHa_clap4 Thank you

[Uppi]

Idhi Oracle PL/SQL code or Microsoft SQL Server Code?

[tommy]

database lo tables chudachu anthe but cannot add or alter anything akada problem vasthundhi..

[tommy]

oracle bhayya...........

[loveindia]

Bhayya ee query run chesthe baga time padthundhi..so time koncham taginchali ante ela...

 

with cntMissEvents as (

    select pt_pgm_id, test_yr_num, analyte_id,

    max(case when evnt = '1' THEN evntcnt END) AS miss_event1,

    max(case when evnt = '2' THEN evntcnt END) AS miss_event2,

    max(case when evnt = '3' THEN evntcnt END) AS miss_event3

    from (

        select pt_pgm_id, test_yr_num, analyte_id, '1' as evnt, count(*) as evntcnt

        FROM clia_pt_enrlmnt ce

        where pt_scre_submsn_1_dt is not null and

        not exists (

            select clia_intrnl_id, pt_pgm_id, test_yr_num, analyte_id, pt_scre_submsn_dt

            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(*) as evntcnt

        FROM clia_pt_enrlmnt ce

        where pt_scre_submsn_2_dt is not null and

        not exists (

            select clia_intrnl_id, pt_pgm_id, test_yr_num, analyte_id, pt_scre_submsn_dt

            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(*) as evntcnt

        FROM clia_pt_enrlmnt ce

        where pt_scre_submsn_3_dt is not null and

        not exists (

            select clia_intrnl_id, pt_pgm_id, test_yr_num, analyte_id, pt_scre_submsn_dt

            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

       )

       group by pt_pgm_id, test_yr_num, analyte_id

),

cntTotEvents as (

    select pt_pgm_id, test_yr_num, analyte_id,

    max(case when evnt = '1' THEN evntcnt END) AS tot_event1,

    max(case when evnt = '2' THEN evntcnt END) AS tot_event2,

    max(case when evnt = '3' THEN evntcnt END) AS tot_event3

    from (

        select pt_pgm_id, test_yr_num, analyte_id, '1' as evnt, count(*) 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(*) 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(*) 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)

GROUP BY pt_pgm_id, test_yr_num, analyte_id)

select  cte.pt_pgm_id,cte.test_yr_num ,cte.analyte_id,

     cme.miss_event1, cme.miss_event2,cme.miss_event3, cte.total_event1, cte.total_event2,cte.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

 

...

 

 

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..
 

[loveindia]

Bhayya ee query run chesthe baga time padthundhi..so time koncham taginchali ante ela...

 

with cntMissEvents as (

    select pt_pgm_id, test_yr_num, analyte_id,

    max(case when evnt = '1' THEN evntcnt END) AS miss_event1,

    max(case when evnt = '2' THEN evntcnt END) AS miss_event2,

    max(case when evnt = '3' THEN evntcnt END) AS miss_event3

    from (

        select pt_pgm_id, test_yr_num, analyte_id, '1' as evnt, count(*) as evntcnt

        FROM clia_pt_enrlmnt ce

        where pt_scre_submsn_1_dt is not null and

        not exists (

            select clia_intrnl_id, pt_pgm_id, test_yr_num, analyte_id, pt_scre_submsn_dt

            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(*) as evntcnt

        FROM clia_pt_enrlmnt ce

        where pt_scre_submsn_2_dt is not null and

        not exists (

            select clia_intrnl_id, pt_pgm_id, test_yr_num, analyte_id, pt_scre_submsn_dt

            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(*) as evntcnt

        FROM clia_pt_enrlmnt ce

        where pt_scre_submsn_3_dt is not null and

        not exists (

            select clia_intrnl_id, pt_pgm_id, test_yr_num, analyte_id, pt_scre_submsn_dt

            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

       )

       group by pt_pgm_id, test_yr_num, analyte_id

),

cntTotEvents as (

    select pt_pgm_id, test_yr_num, analyte_id,

    max(case when evnt = '1' THEN evntcnt END) AS tot_event1,

    max(case when evnt = '2' THEN evntcnt END) AS tot_event2,

    max(case when evnt = '3' THEN evntcnt END) AS tot_event3

    from (

        select pt_pgm_id, test_yr_num, analyte_id, '1' as evnt, count(*) 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(*) 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(*) 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)

GROUP BY pt_pgm_id, test_yr_num, analyte_id)

select  cte.pt_pgm_id,cte.test_yr_num ,cte.analyte_id,

     cme.miss_event1, cme.miss_event2,cme.miss_event3, cte.total_event1, cte.total_event2,cte.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

 

...

 

 

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..
 

[loveindia]

yeah..CTE, ha union lu group lu,count  vunnayi..

a part ki ha part divide chesi temp tables create chesi try cheye...

 

but y man????

[loveindia]

when CTE is not fast look for table variables and temp tables.... 

 

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
 

[tommy]

Thanks bhayaa...yaa kaali unapudu okasari chudu...nenu office lo run chesi try chestha..

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..
 

 

[tommy]

byt he way temp tables create cheyaniki ledu naaku....insufficient privilages ani vasthundhi.....i dont have access..