declare @fdate int =201605, @tdate int =201704
drop table #temp3
drop table #temp1
drop table #temp2
select YEAR_MO, count(*) MBRMTH into #temp3 from [VW_MEMBMTHS] group by YEAR_MO order by 1
SELECT a.LOB, a.PROD_NAME, a.HCG_MR_line HCG_Detail, a.YEAR_MO, PROV_LNAME BillingProviderName,MBRMTH MemberMonth,sum(AMT_PAID) ClaimsAmtPaid ,
sum(MR_procs_raw)Utilization into #temp1
FROM [XXXXXXX].[dbo].[Table1] a with(nolock)
left outer join [XXXXXXX].[dbo].[DERS] b on PROV_ID=PROV_BILL
left outer join #temp3 c on a.YEAR_MO=c.YEAR_MO
where a.YEAR_MO between @fdate and @tdate and HCG_MR_line in('P84','P85')
group by a.HCG_MR_line, a.LOB, a.PROD_NAME, a.YEAR_MO,PROV_LNAME ,MBRMTH
order by a.YEAR_MO,PROV_LNAME
--select * from #temp1 order by cntprovd desc
select * ,ROW_NUMBER() over ( PARTITION BY year_MO order by year_MO,ClaimsAmtPaid desc ) AS RowNo into #temp2
from #temp1 order by year_MO,ClaimsAmtPaid desc
select * from #temp2 where RowNo<21
This is my technical area for troubleshooting and learning new programming skills and much more. Here, you will find answers on a wide range of technologies such as AI, Machine Learning, OpenAI, Databricks, ASP.NET, C#, Python, Microsoft Access, MySQL, Amazon Web Services, SQL Server, PL/SQL, JD Edwards, SAS, Salesforce, APIs, MVC, and many others. Please feel free to visit and join the discussion!
Tuesday, June 20, 2017
Thursday, March 30, 2017
Convert Julian date to Calendar date PL SQL Oracle
select YADOB DOB,
CASE
when YADOB = 0 Then TO_CHAR(TO_DATE(SUBSTR (YADOB,2,5),'YYDDD'),'YYYY-MM-DD')
when YADOB < 100000 Then TO_CHAR(TO_DATE(YADOB+1900000, 'YYDDD'), 'MM/DD/YYYY')
when YADOB > 100000 Then TO_CHAR(TO_DATE(YADOB+1900000, 'YYDDD'), 'MM/DD/YYYY')
ELSE '0' end JulianDT
from proddta.F060116
By Shyamini Nair
Tuesday, February 14, 2017
Oracle VBA Working ConnectionString OraOLEDB
Dim cnt As New adodb.Connection
Set cnt = New adodb.Connection
connStr = "Provider=OraOLEDB.Oracle;Data Source=HIBUDP1;User Id=Budget;Password=xxxx#xx;"
cnt.Open connStr
Set rst = cnt.Execute(SQL_Str)
How to enable textbox on dropdown
function myFunction() {
document.getElementById("myText").disabled = false;
}
Name:
type="text" id="myText" disabled="true">
Click the button to disable the text field.
http://stackoverflow.com/questions/31647425/how-to-enable-text-box-on-drop-down-value-change-in-javascript
Friday, February 10, 2017
F1602 Data recalss
--select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt into #temp_11 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
--select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=2016 order by 1,2
--select * from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=2016 order by 1,2
declare @fy int=2016
--PN 10
drop table #temp_10
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban10/100 Amt into #temp_10 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp_10_2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp_10_2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp_10
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '10' ForPN, * from #temp_10_2
--PN 11
drop table #temp_11
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt into #temp_11 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp_11_2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp_11_2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp_11
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '11' ForPN, * from #temp_11_2
--for 12
drop table #temp111
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban12/100 Amt into #temp111 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp111
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '12' ForPN, * from #temp2
--select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=2016 order by 1,2
--select * from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=2016 order by 1,2
declare @fy int=2016
--PN 10
drop table #temp_10
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban10/100 Amt into #temp_10 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp_10_2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp_10_2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp_10
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '10' ForPN, * from #temp_10_2
--PN 11
drop table #temp_11
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt into #temp_11 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp_11_2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp_11_2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp_11
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '11' ForPN, * from #temp_11_2
--for 12
drop table #temp111
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban12/100 Amt into #temp111 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp111
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '12' ForPN, * from #temp2
Wednesday, February 8, 2017
F4111 VS F0911 Reconciliation..
drop table #temp11
drop table #temp12
drop table #temp13
--select * from proddta.f4111 where ILDGL>117000
--select * from proddta.f0911 where GLDGJ>117000
select ildoc,ildct,ILJELN,ILLITM, ilmcu,iltrdj,ilicu,ildgl, sum(iltrqt)iltrqt,sum(ilpaid)/100ilpaid into #temp11
from proddta.f4111 where ILDGL>117000
group by ildoc,ildct,ILJELN,ILLITM, ilmcu,iltrdj,ilicu,ildgl
select gldoc,gldct,GLJELN,glmcu,gLexr, glsub,glani,sum(glu)/100 glu, sum(glaa)/100 glaa into #temp12
from proddta.f0911 where gLDGj>117000 and gldct in(
select distinct ildct from proddta.f4111 where ILDGL>117000)
group by GLDOC,gldct,glexr,glsub,GLJELN,glani,glmcu
select *,( glaa-ilpaid) amt into #temp13
from #temp11
left outer join #temp12 on ildoc=gldoc and ildct=gldct and GLJELN=iljeln
--where len(glsub) >3 and glaa-ilpaid<>0
select
ildoc DOC#,ildct DcTy,illitm ItemNo,ilmcu BP,iltrqt Inv_Qty,ilpaid Inv_Amt,glani Acno,ilicu BatchNo,
DATEADD(dy, cast(iltrdj as varchar(10)) % 1000, DATEADD(yy, cast(iltrdj as varchar(10)) / 1000,-1)) TransDate,
DATEADD(dy, cast(ildgl as varchar(10)) % 1000, DATEADD(yy, cast(ildgl as varchar(10)) / 1000,-1)) GL_Date,
glu GL_Qty,glaa GL_Amt, (ilpaid-glaa) AmtDiff,
case when (ilpaid-glaa)=0 then 'Recoincile' when glaa is null then 'Not in GL' else 'Not Recoinciled' end RecoincileYN
from #temp13 order by 4,3,2
-- IK
drop table #temp12
drop table #temp13
--select * from proddta.f4111 where ILDGL>117000
--select * from proddta.f0911 where GLDGJ>117000
select ildoc,ildct,ILJELN,ILLITM, ilmcu,iltrdj,ilicu,ildgl, sum(iltrqt)iltrqt,sum(ilpaid)/100ilpaid into #temp11
from proddta.f4111 where ILDGL>117000
group by ildoc,ildct,ILJELN,ILLITM, ilmcu,iltrdj,ilicu,ildgl
select gldoc,gldct,GLJELN,glmcu,gLexr, glsub,glani,sum(glu)/100 glu, sum(glaa)/100 glaa into #temp12
from proddta.f0911 where gLDGj>117000 and gldct in(
select distinct ildct from proddta.f4111 where ILDGL>117000)
group by GLDOC,gldct,glexr,glsub,GLJELN,glani,glmcu
select *,( glaa-ilpaid) amt into #temp13
from #temp11
left outer join #temp12 on ildoc=gldoc and ildct=gldct and GLJELN=iljeln
--where len(glsub) >3 and glaa-ilpaid<>0
select
ildoc DOC#,ildct DcTy,illitm ItemNo,ilmcu BP,iltrqt Inv_Qty,ilpaid Inv_Amt,glani Acno,ilicu BatchNo,
DATEADD(dy, cast(iltrdj as varchar(10)) % 1000, DATEADD(yy, cast(iltrdj as varchar(10)) / 1000,-1)) TransDate,
DATEADD(dy, cast(ildgl as varchar(10)) % 1000, DATEADD(yy, cast(ildgl as varchar(10)) / 1000,-1)) GL_Date,
glu GL_Qty,glaa GL_Amt, (ilpaid-glaa) AmtDiff,
case when (ilpaid-glaa)=0 then 'Recoincile' when glaa is null then 'Not in GL' else 'Not Recoinciled' end RecoincileYN
from #temp13 order by 4,3,2
-- IK
lpad substring in PL SQL
select ltrim(rtrim(CCNAME)) aaa, length(ltrim(rtrim(CCNAME))) fkj,
'000000'|| substr(ltrim(rtrim(CCNAME)),-length(ltrim(rtrim(CCNAME))),30),
LPAD(substr(ltrim(rtrim(CCNAME)),1,6),50-length(ltrim(rtrim(CCNAME))),0),
RPAD('000000'|| substr(ltrim(rtrim(CCNAME)),-length(ltrim(rtrim(CCNAME))),23), 10)
from PRODDTA.F0010
Sunday, February 5, 2017
Month End Inventory-to-G/L Reconciliation - F4111 Vs F0911 Compare
drop table #temp11
drop table #temp12
drop table #temp13
--select * from proddta.f4111 where ILDGL>117000
--select * from proddta.f0911 where GLDGJ>117000
select ildoc,ildct,ILJELN,ILLITM, ilmcu, sum(iltrqt)iltrqt,sum(ilpaid)/100ilpaid into #temp11
from proddta.f4111 where ILDGL>117000
group by ildoc,ildct,ILLITM,ilmcu,ILLITM,ILJELN
select gldoc,gldct,GLJELN,glmcu,gLexr, glsub,glani,sum(glu)/100 glu, sum(glaa)/100 glaa into #temp12
from proddta.f0911 where gLDGj>117000 and gldct in(
select distinct ildct from proddta.f4111 where ILDGL>117000)
group by GLDOC,gldct,glexr,glsub,GLJELN,glani,glmcu
select *,( glaa-ilpaid) amt into #temp13
from #temp11
left outer join #temp12 on ildoc=gldoc and ildct=gldct and GLJELN=iljeln
--where len(glsub) >3 and glaa-ilpaid<>0
select
ildoc,ildct,illitm,ilmcu,iltrqt,ilpaid,glani,glu,glaa, (ilpaid-glaa) AmtDiff,
case when (ilpaid-glaa)=0 then 'Recoincile' when glaa is null then 'Not in GL' else 'Not Recoinciled' end RecoincileYN
from #temp13
--where glaa is null
--select illitm,ilmcu,sum(ilpaid)ilpaid,glsub, sum(glaa) glaa from #temp13
--where ilpaid-glaa<>0 group by illitm,ilmcu,glsub
----select * from proddta.f4111 where illitm='1645618' and ltrim(ilmcu)='GRAYTIF-RT' and ildgl>117000 and
----select * from proddta.f0911 where gldgj>117000 and GLEXR='1649608' and ltrim(glsub) in('NIAGRA','010')
----select * from proddta.f0911 where gldoc=127572
--select * from proddta.f4111 where ildoc=127375 and illitm='1654571' and ilmcu=' SCHAILI-SC2'
--select * from proddta.f0911 where GLDOC=127375 and gldct='IK' --illitm='1654571' and ilmcu=' SCHAILI-SC2'
-- IK
drop table #temp12
drop table #temp13
--select * from proddta.f4111 where ILDGL>117000
--select * from proddta.f0911 where GLDGJ>117000
select ildoc,ildct,ILJELN,ILLITM, ilmcu, sum(iltrqt)iltrqt,sum(ilpaid)/100ilpaid into #temp11
from proddta.f4111 where ILDGL>117000
group by ildoc,ildct,ILLITM,ilmcu,ILLITM,ILJELN
select gldoc,gldct,GLJELN,glmcu,gLexr, glsub,glani,sum(glu)/100 glu, sum(glaa)/100 glaa into #temp12
from proddta.f0911 where gLDGj>117000 and gldct in(
select distinct ildct from proddta.f4111 where ILDGL>117000)
group by GLDOC,gldct,glexr,glsub,GLJELN,glani,glmcu
select *,( glaa-ilpaid) amt into #temp13
from #temp11
left outer join #temp12 on ildoc=gldoc and ildct=gldct and GLJELN=iljeln
--where len(glsub) >3 and glaa-ilpaid<>0
select
ildoc,ildct,illitm,ilmcu,iltrqt,ilpaid,glani,glu,glaa, (ilpaid-glaa) AmtDiff,
case when (ilpaid-glaa)=0 then 'Recoincile' when glaa is null then 'Not in GL' else 'Not Recoinciled' end RecoincileYN
from #temp13
--where glaa is null
--select illitm,ilmcu,sum(ilpaid)ilpaid,glsub, sum(glaa) glaa from #temp13
--where ilpaid-glaa<>0 group by illitm,ilmcu,glsub
----select * from proddta.f4111 where illitm='1645618' and ltrim(ilmcu)='GRAYTIF-RT' and ildgl>117000 and
----select * from proddta.f0911 where gldgj>117000 and GLEXR='1649608' and ltrim(glsub) in('NIAGRA','010')
----select * from proddta.f0911 where gldoc=127572
--select * from proddta.f4111 where ildoc=127375 and illitm='1654571' and ilmcu=' SCHAILI-SC2'
--select * from proddta.f0911 where GLDOC=127375 and gldct='IK' --illitm='1654571' and ilmcu=' SCHAILI-SC2'
-- IK
Thursday, January 5, 2017
Convert JDE Julian date to Calendar date PL/SQL
convert Julian date to Calendar date Oracle
select to_char(to_date(GLDGJ+1900000,'YYYYDDD'), 'MM/DD/YYYY') as adate , gldgj from PRODDTA.F0911 where gldgj>116360
Monday, January 2, 2017
Update GLPT in F4101/F4102/F41021 - JD Edwards SQL
declare @glpt varchar(4)
declare @mcu varchar(12)
set @glpt='XX12'
set @mcu='ZZZZWFFF'
--select ibglpt,* from proddta.f4102 where ltrim(ibmcu)=@mcu
--select liglpt,* from proddta.f41021 where ltrim(LIMCU)=@mcu
--select * from proddta.f4101 where imitm in(select distinct IBLITM from proddta.f4102 where ltrim(ibmcu)=@mcu)
update proddta.f4102 set ibglpt=@glpt where ltrim(ibmcu)=@mcu
update proddta.f41021 set LIGLPT=@glpt where ltrim(LIMCU)=@mcu
update proddta.f4101 set imglpt=@glpt where imitm in(select distinct IBITM from proddta.f4102 where ltrim(ibmcu)=@mcu)
Tuesday, November 1, 2016
Oracle Output Result Copy and paste in Excel
Run the query after that :-
1. Ctrl + A
2. Ctrl + Shift + C
3. Paste to Excel
1. Ctrl + A
2. Ctrl + Shift + C
3. Paste to Excel
Monday, October 17, 2016
maintain scroll position javascript
http://stackoverflow.com/questions/17642872/refresh-page-and-keep-scroll-position
http://www.redips.net/javascript/maintain-scroll-position/?param1=a¶m2=b¶m3=c&scroll=606
Sunday, September 25, 2016
Gridview update code behind c#
protected void TaskGridView_RowUpdating(object sender, GridViewUpdateEventArgs e) { //Retrieve the table from the session object. DataTable dt = (DataTable)Session["TaskTable"]; //Update the values. GridViewRow row = TaskGridView.Rows[e.RowIndex]; dt.Rows[row.DataItemIndex]["Id"] = ((TextBox)(row.Cells[1].Controls[0])).Text; dt.Rows[row.DataItemIndex]["Description"] = ((TextBox)(row.Cells[2].Controls[0])).Text; dt.Rows[row.DataItemIndex]["IsComplete"] = ((CheckBox)(row.Cells[3].Controls[0])).Checked; //Reset the edit index. TaskGridView.EditIndex = -1; //Bind data to the GridView control. BindData(); }
https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowupdating(v=vs.110).aspx
Thursday, August 11, 2016
Subscribe to:
Posts (Atom)