pLEASE LOOK AT sql 2 AND ADVISE…
SQL 1
SELECT
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.DRTN_NB,
dbo.ZLDT030_CRS.EFF_DT,
dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
dbo.ZLDT030_CRS.CRS_DLVRY_CD,
dbo.ZLDT075_CRRCLM_CRS.CRS_NB,
dbo.ZLDT075_CRRCLM_CRS.CRS_VRSN_NB
FROM
dbo.ZLDT030_CRS,
dbo.ZLDT800_CRS_DLVRY,
dbo.ZLDT075_CRRCLM_CRS,
dbo.ZLDT860_CORE_BUS
WHERE
( dbo.ZLDT075_CRRCLM_CRS.CRS_NB=dbo.ZLDT030_CRS.CRS_NB and dbo.ZLDT075_CRRCLM_CRS.CRS_VRSN_NB=dbo.ZLDT030_CRS.CRS_VRSN_NB )
AND ( dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD=dbo.ZLDT030_CRS.CRS_DLVRY_CD )
AND ( dbo.ZLDT860_CORE_BUS.CORE_BUS_AREA_CD=dbo.ZLDT075_CRRCLM_CRS.CORE_BUS_AREA_CD )
AND (
dbo.ZLDT030_CRS.CRS_STAT_CD = 1
AND dbo.ZLDT860_CORE_BUS.CORE_BUS_AREA_CD IN (2, 1)
)
SQL 2
SELECT
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.DRTN_NB,
dbo.ZLDT030_CRS.EFF_DT,
dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
dbo.ZLDT030_CRS.CRS_DLVRY_CD,
Count ( CASE
WHEN (year(ZLDT180_SCR.SCR_DT) =2002 AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85) OR (year(ZLDT180_SCR.SCR_DT) =2002 AND dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 )) Then dbo.ZLDT180_SCR.STDNT_NB
End)
,
Count (CASE
WHEN year(ZLDT180_SCR.SCR_DT) =2003 AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 ) Then dbo.ZLDT180_SCR.STDNT_NB
End)
,
Count (CASE
WHEN year(ZLDT180_SCR.SCR_DT) =2004 AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85 OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 ) Then dbo.ZLDT180_SCR.STDNT_NB
End)
,
Avg( CASE When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2002 AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC END) ,
Avg( CASE When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2002 AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC END) ,
Avg( CASE When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2003 AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC END) ,
Avg( CASE When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2004 AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC END) ,
Avg( CASE When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2004 AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC END) ,
Avg( CASE When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2003 AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC END)
FROM
dbo.ZLDT030_CRS,
dbo.ZLDT800_CRS_DLVRY,
dbo.ZLDT180_SCR
WHERE
( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB )
AND ( dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD=dbo.ZLDT030_CRS.CRS_DLVRY_CD )
AND (
dbo.ZLDT030_CRS.CRS_STAT_CD = 1
)
GROUP BY
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.DRTN_NB,
dbo.ZLDT030_CRS.EFF_DT,
dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
dbo.ZLDT030_CRS.CRS_DLVRY_CD
SQL 3
SELECT
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.DRTN_NB,
dbo.ZLDT030_CRS.EFF_DT,
dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
dbo.ZLDT030_CRS.CRS_DLVRY_CD,
count(dbo.ZLDT025_STDNTRSPNS.STDNT_NB),
dbo.ZLDT025_STDNTRSPNS.ANSW_OPT_CD,
Count (CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =1 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =2 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =3 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =4 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =5 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
,
Count (CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =6 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =7 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =8 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =9 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =10 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
,
Count ( CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (1,2,3,4,5) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2002 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
,
Count (CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (1,2,3,4,5) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2003 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
,
Count (CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (1,2,3,4,5) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2004 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
,
Count (CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (6,7,8,9,10) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2002 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
,
Count (CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (6,7,8,9,10) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2003 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
,
Count (CASE
WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (6,7,8,9,10) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2004 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)
FROM
dbo.ZLDT030_CRS,
dbo.ZLDT800_CRS_DLVRY,
dbo.ZLDT025_STDNTRSPNS
WHERE
( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT025_STDNTRSPNS.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT025_STDNTRSPNS.CRS_VRSN_NB )
AND ( dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD=dbo.ZLDT030_CRS.CRS_DLVRY_CD )
AND (
dbo.ZLDT030_CRS.CRS_STAT_CD = 1
)
GROUP BY
dbo.ZLDT030_CRS.CRS_NM,
dbo.ZLDT030_CRS.DRTN_NB,
dbo.ZLDT030_CRS.EFF_DT,
dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
dbo.ZLDT030_CRS.CRS_DLVRY_CD,
dbo.ZLDT025_STDNTRSPNS.ANSW_OPT_CD
kprasadreddy (BOB member since 2004-07-02)