SELECT TO_CHAR(START_TIME,'HH:MI AM')START_TIME,TO_CHAR(END_TIME,'HH:MI AM')END_TIME
FROM hr_empshiftdetail
where EMP_NO='E011302000087'
and sh_dt=trunc(sysdate)
order by 1
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail where emp_no='E011302000087' and sh_dt=trunc(sysdate) order by 1
hpms_doctor
slot multi query no dependent others
SELECT TO_CHAR(START_TIME,'HH:MI AM')START_TIME,TO_CHAR(END_TIME,'HH:MI AM')END_TIME
FROM OP_APPOINTMST
WHERE EMP_NO='E011302000083'
AND APPOINT_DATE='19-sep-2013'
hr_empshiftdetail
-----------------------------------------------------------------------------------
public List<AppointmentPatientInfo> getslotTimeSubSectionList(String empNo, String slotDate) {
query = "select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail " +
"where emp_no='"+empNo+"' and sh_dt=to_date('08/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS') order by 1";
---------------------------------------------------------------------------------
or above particular date and under sysdate
Different sh_dt=trunc(sysdate)
EQUAL
sh_dt=to_date('08/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
--------------------------------------------------------------------------------
public List<AppointmentPatientInfo> getslotTimeSubSectionList(String empNo, String slotDate) {
query = "select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail " +
"where emp_no='"+empNo+"' and sh_dt=trunc(sysdate) order by 1";
--------------------------------------------------------------------------------
*************************************************************************************************************************
This is problem portion
-----------------------------------------------------------------------------------
public List<AppointmentPatientInfo> getslotTimeSubSectionList(String empNo, Date slotDate) {
query = "select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail " +
"where emp_no='"+empNo+"' and sh_dt="+slotDate+" order by 1";
---------------------------------------------------------------------------------------
Error query from console against above query
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail where emp_no='E011204000067' and sh_dt=Sun Aug 18 00:00:00 BDT 2013 order by 1
2//Start (string date pass through form)
Correct query
query = "select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail " +
"where emp_no='"+empNo+"' and sh_dt=to_date('"+slotDate+"','dd/mm/yyyy') order by 1";
select to_char(start_time,'HH:MI AM')start_time,to_char(end_time,'HH:MI AM')end_time
from hr_empshiftdetail
where emp_no='E011204000067'
and sh_dt = to_date('18/08/2013', 'DD/MM/YYYY')
order by 1
2//End
3//Start truncate date
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail
where emp_no='"+empNo+"' and sh_dt=trunc(sysdate) order by 1;
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail
where emp_no='E011204000067' and sh_dt=to_date('08/18/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS');
3//End truncate date
*********************************************************************************************************************************
Rakib
E011204000067
7/8/2013 9:00:00 AM
***********************************************************************************
For sql : public List<AppointmentPatientInfo> getslotTimeSubSectionList(String empNo, Date dob) {
For Load Action :slotTimeSubSection
DateFormating dateFormating = new DateFormating();
Date dob = dateFormating.dateFormatDB(dobActionsent);
slotTimeSubSectionListdb=asdf.getslotTimeSubSectionList(slotDrTimeActionsent,dob);
************************************************************************************
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail where emp_no='E011204000067' and sh_dt=Sun Aug 18 00:00:00 BDT 2013 order by 1
=============================
hpms_doctor
SELECT TO_CHAR(START_TIME,'HH:MI AM')START_TIME,TO_CHAR(END_TIME,'HH:MI AM')END_TIME
FROM OP_APPOINTMST
WHERE EMP_NO='E011302000083'
AND APPOINT_DATE='19-sep-2013'
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail
where emp_no='E011302000087' and sh_dt=to_date('08/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS') order by 1
select to_char(start_time,'HH:MI AM')start_time,to_char(end_time,'HH:MI AM')end_time
from hr_empshiftdetail
where emp_no='E011302000087'
and sh_dt = to_date('08/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
order by 1
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail
where emp_no='E011204000067' and sh_dt=to_date('18/08/2013','dd/mm/yyyy') order by 1
=======================================================================
=========================================================================
==========================================================================
=============================================================================
Rokon and Rahman slot query
===============================================================================
=================================================================================
slot multi query no dependent others
SELECT TO_CHAR(START_TIME,'HH:MI AM')START_TIME,TO_CHAR(END_TIME,'HH:MI AM')END_TIME
FROM OP_APPOINTMST
WHERE EMP_NO='E011302000083'
AND APPOINT_DATE='19-sep-2013'
and START_TIME between TO_CHAR(START_TIME,'01:45 PM')
and TO_CHAR(START_TIME,'02:30 PM')
select TO_CHAR('2:00:20 PM', 'HH24:MI:SS') from dual
E011204000067
SELECT TO_CHAR(START_TIME,'HH:MI AM')START_TIME,TO_CHAR(END_TIME,'HH:MI AM')END_TIME
FROM OP_APPOINTMST
WHERE EMP_NO='E011302000083'
AND APPOINT_DATE='19-sep-2013'
--and SHIFT_NO = 'GEN'
and SHIFT_NO = 'GEN2'
select SH_DT,to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time,SHIFT_NO from hr_empshiftdetail
where emp_no='E011001000426' and sh_dt=to_date('05/09/2013','dd/mm/yyyy')
-- and SHIFT_NO = 'GEN' order by 1
OP_APPOINTMST
hrv_empjobdetails
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time from hr_empshiftdetail
where emp_no='E011204000067' and sh_dt=to_date('18/08/2013','dd/mm/yyyy') order by 1
insert into OP_APPOINTMST()
=============================================================================================================
=============================================================================================================
shift_no (Multi slot call according to sub section slot)
=============================================================================================================
=============================================================================================================
select shift_no ,to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time,SHIFT_NO from hr_empshiftdetail
where emp_no='E011001000426' and sh_dt=to_date('05/09/2013','dd/mm/yyyy')
-- and SHIFT_NO = 'GEN' order by 1
OP_APPOINTMST
hrv_empjobdetails
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time,SHIFT_NO from op_appointmst
where emp_no='E011001000426' and APPOINT_DATE=to_date('05/09/2013','dd/mm/yyyy')
and SHIFT_NO = 'EV1' order by 1
...............................................................
update query for date update then run above query
...............................................................
update op_appointmst set APPOINT_DATE=to_date('05/10/2013','dd/mm/yyyy') where emp_no='E011001000426' and APPOINT_DATE=to_date('05/09/2013','dd/mm/yyyy')
Then Run Query
--------------
select to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time,SHIFT_NO from op_appointmst
where emp_no='E011001000426' and APPOINT_DATE=to_date('05/10/2013','dd/mm/yyyy')
and SHIFT_NO = 'EV1' order by 1
=============================================================================================================
=============================================================================================================
======================================================
+++++++++++++++++++++++++++++++++++++++++++++++++++++
=======================================================
select SH_DT,to_char(start_time,'HH:MI AM') start_time,to_char(end_time,'HH:MI AM') end_time,SHIFT_NO from hr_empshiftdetail
where emp_no='E011001000426' and sh_dt=to_date('05/09/2013','dd/mm/yyyy')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1/ Instruction 1 : Catch data From 2 table (1.op_registration 2.op_pataddr) N.B use alliase name system Learn
select r.salutation_id,r.full_name,r.dob,r.gender,pa.ADD1 from op_registration r, op_pataddr pa where r.reg_no = 'R011003000673';
..............................FOR WORK .........................................................
select r.national_id,r.salutation_id,r.full_name,r.dob,r.gender,pa.add1
from op_registration r, op_pataddr pa
where r.reg_no = pa.reg_no
and r.reg_no = 'R011003000673';
select * from op_registration where reg_no = 'R011106000281';
select * from op_pataddr where reg_no = 'R011106000281' and ADDR_TYPE = 'PS';
select * from op_pataddr where reg_no = 'R011106000281' and ADDR_TYPE = 'PR';
N.B:ps means
..............................For salutation search .............................................
select * from op_registration where reg_no = 'R011001000315'
select REG_NO,SALUTATION_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, (select salutation_name from hr_salutation where SALUTATION_ID =op_registration.SALUTATION_ID) salutation_name from op_registration where reg_no = 'R011001000310'
select salutation_name from hr_salutation where SALUTATION_ID ='12'
////////////////////////////////////////////////
select SEQ_OPDAPP_NO.nextval from dual;
/////////////////////////////////////////////////
***************************************************
Report Generate Query
......................
......................
......................
Select a.appoint_no, a.APPOINT_DATE, a.sc_no, s.sc_date, b.APP_BY,b.APP_BY_REL_W_PAT, b.REG_NO,
(CASE WHEN b.reg_no IS NULL THEN INITCAP((SELECT SALUTATION_NAME FROM HR_SALUTATION WHERE SALUTATION_ID=b.SALUTATION_ID)||' '||b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME)
ELSE (SELECT SALUTATION_NAME FROM HR_SALUTATION WHERE SALUTATION_ID=r.SALUTATION_ID)||' '||r.FIRST_NAME||' '||r.MIDDLE_NAME||' '||r.LAST_NAME
END) patient_name,
CASE
WHEN r.GENDER = 'M'
THEN 'Male'
WHEN r.GENDER = 'F'
THEN 'Female'
WHEN r.GENDER='O' THEN 'Other'
END gender,
CASE WHEN b.reg_no IS NOT NULL THEN fnc_patientage(r.reg_no,'D')
ELSE NVL(to_char(b.age_yy)||' Y '||to_char(b.age_mm)||' M '||to_char(b.age_dd)||' D',FNC_TOAGE(b.dob)) END age,
a.SL#,b.pat_sl#,
a.SCHEDULE_TYPE,
d.doctor_name,
a.start_time,a.end_time,
(SELECT TYPE_DES FROM OP_APPTYPE WHERE TYPE_CODE=b.APP_TYPE) appoint_type,
d.specialization_name,
d.QUALIFICATION,
d.ROOM_NO,
d.ROOM_NAME
From op_appointmst a,
op_appointment b,
hpms_resourceschedule s,
HPMSV_DOCCHEMBER D,
op_registration r
Where a.sc_no = s.sc_no(+)
AND a.APPOINT_NO = b.APPOINT_NO
AND s.RESOURCE_NO = d.DOCTOR_NO(+)
AND b.reg_no = r.reg_no(+)
AND a.appoint_no = nvl(:p_appoint_no, a.appoint_no)
AND b.pat_SL# = NVL(:P_SL, B.pat_SL#)
*****************************************************************************************************************************************************************************************************************
P011307333179
1
P011001000426
***************************************************
Jasper height and width
w = 20.990
H = 29.704
***************************************************
No comments:
Post a Comment