SQLTeam.com | Weblogs | Forums

Need to modify WHERE Clause


#1

I have a monthly report, only has 1 month data. I want it to be ​from 2/1/2016 through 12/31/2016..Thanks in Advance...

WHERE A.e_addr_type IN ( 'Warehouse2' ))
SELECT DISTINCT A.emplid "Staff ID",
A.name "Staff Name",
A.pref_first_name "Preferred First Name",
Floor (( Add_months(Trunc(SYSDATE, 'MM'), 1) -
A.service_dt ) / 365.25)
"Years of Service",
A.service_dt "Service Date",
A.email_addr2 "External Warehouse E-Mail ID",
A.deptid "Bill To Cost Center Number",
A.deptname "Bill To Cost Center Descr",
A.Warehouse_busn_unit_desc "Line of Service",
A.Warehouse_sub_los_2_name "Sub LoS 2 Name",
CASE
WHEN A.empl_status IN ( 'A' ) THEN 'Active'
WHEN A.empl_status IN ( 'L' ) THEN 'Leave of Absence'
WHEN A.empl_status IN ( 'P' ) THEN 'Leave With Pay'
WHEN A.empl_status IN ( 'S' ) THEN 'Suspended'
ELSE 'UNKNOWN'
END "Employee Status",
A.jobcode "Job Code",
A.jobtitle "Job Title",
A.job_function "Role",
A.Warehouse_jobfunc_descr "Role Descr",
A.location "Work Location",
A.Warehouse_loctn_descr "Work Location Description",
A.Warehouse_st_mkt_descr "Strategic Market",
A.Warehouse_mktcls_descr "Geo market",
C.descr "HC Designation",
A.Warehouse_fin_region "Strategic Region",
A.Warehouse_fin_rgn_descr "Strategic Region Descr",
A.address1 "Address 1",
A.address2 "Address 2",
A.city "City",
A.state "State",
A.postal "Postal",
A.Warehouse_ptnr_rel_id "Partner ID",
A.Warehouse_ptnr_rel_nm "Partner Name",
P.email_addr "Partner Email",
A.Warehouse_coach_rel_id "Coach ID",
A.Warehouse_coach_rel_nm "Coach Name",
FROM ps_Warehouse_employees A,
ps_Warehouse_hc_ee_vw B,
ps_Warehouse_hc_mkt_tbl C,
email P,
email CO
WHERE ( A.effdt = (SELECT Max(A_ED.effdt)
FROM ps_Warehouse_employees A_ED
WHERE A.emplid = A_ED.emplid
AND A.empl_rcd = A_ED.empl_rcd
AND A_ED.effdt <= SYSDATE)
AND A.effseq = (SELECT Max(A_ES.effseq)
FROM ps_Warehouse_employees A_ES
WHERE A.emplid = A_ES.emplid
AND A.empl_rcd = A_ES.empl_rcd
AND A.effdt = A_ES.effdt)
AND Extract(month FROM A.service_dt) = Extract(month FROM SYSDATE)


#2

I'm guessing this is Oracle? and this is the where clause?

AND Extract(month FROM A.service_dt) = Extract(month FROM SYSDATE)

You'll need some Oracle date math. Not my forte. Note that this is mostly an MS SQL Server forum


#3

Yes, and thanks...