sql server - Query inside CASE statement with SUM -

-

i have nestled query need use in case statement. need use max(agedate) correctly retrieved in select statement in case statement not see max(agedate) in select statement in case statement. please help...

select         inv.invoicenumber,        fact_fininvoice.invoiceid,        dbo.dim_date.fulldate invoicedate,        pra.practicename,        pra.practicelogonname,        pat.accno,        pat.fileno,        pmai.medicalaidnumber,        per.title + ' ' + per.initials + ' ' + per.surname patientname,        sch.schemes5,        sch.schemeoption, (select max(b.agedate) fact_fininvoice b fact_fininvoice.invoiceid = b.invoiceid , fact_fininvoice.practiceidkey = b.practiceidkey)  maxagedate,         sum(fact_fininvoice.amount) amount,        sum(fact_fininvoice.amountfunder) amountfunder,        sum(fact_fininvoice.amountpatient) amountpatient,          sum(case when datediff(dd, maxagedate, getdate()) between 0 , 29 , amountfunder != 0 amountfunder else 0 end) fundercurrent,        sum(case when datediff(dd, maxagedate, getdate()) between 30 , 59 , amountfunder != 0 amountfunder else 0 end) funder30days,         sum(case when datediff(dd, maxagedate, getdate()) between 60 , 89 , amountfunder != 0 amountfunder else 0 end) funder60days,        sum(case when datediff(dd, maxagedate, getdate()) between 90 , 119 , amountfunder != 0 amountfunder else 0 end) funder90days,        sum(case when datediff(dd, maxagedate, getdate()) >= 120 , amountfunder != 0 amountfunder else 0 end) funder120days,          sum(case when datediff(dd, maxagedate, getdate()) between 0 , 29 , amountpatient != 0 amountpatient else 0 end) patientcurrent,        sum(case when datediff(dd, maxagedate, getdate()) between 30 , 59 , amountpatient != 0 amountpatient else 0 end) patient30days,         sum(case when datediff(dd, maxagedate, getdate()) between 60 , 89 , amountpatient != 0 amountpatient else 0 end) patient60days,        sum(case when datediff(dd, maxagedate, getdate()) between 90 , 119 , amountpatient != 0 amountpatient else 0 end) patient90days,        sum(case when datediff(dd, maxagedate, getdate()) >= 120 , amountpatient != 0 amountpatient else 0 end) patient120days,          mcs.name bureaumanager,        mcso.name bureauofficer            dbo.fact_fininvoice left outer join                 dbo.dim_practice pra on dbo.fact_fininvoice.practiceidkey = pra.practiceidcode left outer join                 dbo.fact_invoice inv on dbo.fact_fininvoice.invoiceid = inv.id left outer join                 dbo.dim_personmainfo pmai  inner join                 dbo.dim_scheme sch  on sch.codeno = pmai.codeno on inv.personmainfocode = pmai.personmainfoid  left outer join                 dbo.dim_bureaustaffprovider bsp  inner join                 dbo.dim_medichargestaff mcs on mcs.id = bsp.bureaustaffid on bsp.providerid = inv.providerid inner join                 dbo.dim_medichargestaff mcso on mcs.manager = mcso.id left outer join                 dbo.dim_date on inv.invoicedatekey = dbo.dim_date.datekey left outer join                 dbo.dim_patient pat inner join                 dbo.dim_persondata per on per.persondataid = pat.personid on pat.patientid = inv.patientkey    group    inv.invoicenumber,             fact_fininvoice.invoiceid,             dbo.dim_date.fulldate,             pra.practicename,             pra.practicelogonname,             pmai.medicalaidnumber,             sch.schemes5,             sch.schemeoption,             mcs.name,             mcso.name,             pat.accno,             pat.fileno,             per.title + ' ' + per.initials + ' ' + per.surname,             [fact_fininvoice].practiceidkey

use cte on top , join cte joins:

;with mycte ( select max(agedate) maxagedate,invoiceid  fact_fininvoice  group invoiceid )  select         inv.invoicenumber,        fact_fininvoice.invoiceid,        dbo.dim_date.fulldate invoicedate,        pra.practicename,        pra.practicelogonname,        pat.accno,        pat.fileno,        pmai.medicalaidnumber,        per.title + ' ' + per.initials + ' ' + per.surname patientname,        sch.schemes5,        sch.schemeoption,         maxagedate,        sum(fact_fininvoice.amount) amount,        sum(fact_fininvoice.amountfunder) amountfunder,        sum(fact_fininvoice.amountpatient) amountpatient,          sum(case when datediff(dd, maxagedate, getdate()) between 0 , 29 , amountfunder != 0 amountfunder else 0 end) fundercurrent,        sum(case when datediff(dd, maxagedate, getdate()) between 30 , 59 , amountfunder != 0 amountfunder else 0 end) funder30days,         sum(case when datediff(dd, maxagedate, getdate()) between 60 , 89 , amountfunder != 0 amountfunder else 0 end) funder60days,        sum(case when datediff(dd, maxagedate, getdate()) between 90 , 119 , amountfunder != 0 amountfunder else 0 end) funder90days,        sum(case when datediff(dd, maxagedate, getdate()) >= 120 , amountfunder != 0 amountfunder else 0 end) funder120days,          sum(case when datediff(dd, maxagedate, getdate()) between 0 , 29 , amountpatient != 0 amountpatient else 0 end) patientcurrent,        sum(case when datediff(dd, maxagedate, getdate()) between 30 , 59 , amountpatient != 0 amountpatient else 0 end) patient30days,         sum(case when datediff(dd, maxagedate, getdate()) between 60 , 89 , amountpatient != 0 amountpatient else 0 end) patient60days,        sum(case when datediff(dd, maxagedate, getdate()) between 90 , 119 , amountpatient != 0 amountpatient else 0 end) patient90days,        sum(case when datediff(dd, maxagedate, getdate()) >= 120 , amountpatient != 0 amountpatient else 0 end) patient120days,          mcs.name bureaumanager,        mcso.name bureauofficer            dbo.fact_fininvoice left outer join                 dbo.dim_practice pra on dbo.fact_fininvoice.practiceidkey = pra.practiceidcode left outer join                 dbo.fact_invoice inv on dbo.fact_fininvoice.invoiceid = inv.id left outer join                 dbo.dim_personmainfo pmai  inner join                 dbo.dim_scheme sch  on sch.codeno = pmai.codeno on inv.personmainfocode = pmai.personmainfoid  left outer join                 dbo.dim_bureaustaffprovider bsp  inner join                 dbo.dim_medichargestaff mcs on mcs.id = bsp.bureaustaffid on bsp.providerid = inv.providerid inner join                 dbo.dim_medichargestaff mcso on mcs.manager = mcso.id left outer join                 dbo.dim_date on inv.invoicedatekey = dbo.dim_date.datekey left outer join                 dbo.dim_patient pat inner join                 dbo.dim_persondata per on per.persondataid = pat.personid on pat.patientid = inv.patientkey                 join mycte ct                 on fact_fininvoice.invoiceid = ct.invoiceid  group    inv.invoicenumber,             fact_fininvoice.invoiceid,             dbo.dim_date.fulldate,             pra.practicename,             pra.practicelogonname,             pmai.medicalaidnumber,             sch.schemes5,             sch.schemeoption,             mcs.name,             mcso.name,             pat.accno,             pat.fileno,             per.title + ' ' + per.initials + ' ' + per.surname,             [fact_fininvoice].practiceidkey

Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more