May 19, 2011 at 10:55 am
Hello, I'm really new to writing SQL queries and could use a little assistance. I hope I'm in the correct forum.
I have a case statement as follows:
(CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END) AS RESULTa,
Now what I need to be able to do is place those results in buckets, similar to this:
(CASE WHEN RESULTa < 0 THEN '<0'
WHEN RESULTa between -1 AND 6 THEN '<=5'
WHEN RESULTa >5 THEN '>5'
ELSE ''
END) AS BUCKETa
I understand that I can't call an alias from a previous case statement to get these desired results and I'm confused on how I could combine the two statements to get the desired bucket.
Any assistance or advice anyone could provide would be most appreciated.
Thanks!
tp
May 19, 2011 at 11:00 am
SELECT dta.CaseColName FROM (base query here) dta
May 19, 2011 at 11:06 am
Not to sound too stupid, but, huh? 🙂
May 19, 2011 at 11:08 am
You could use a cte as well, but here's my original idea.
SELECT
CASE WHEN ColName = 1 THEN 2
ELSE 1
END AS Case2
, Name
FROM
(
SELECT
CASE WHEN 1 <> 2 THEN 1
ELSE NULL
END AS ColName
, Name
FROM
sys.tables
) dta
May 19, 2011 at 11:09 am
I think he means something like this
select
CASE WHEN t.RESULTa < 0 THEN '<0'
WHEN t.RESULTa between -1 AND 6 THEN '<=5'
WHEN t.RESULTa >5 THEN '>5'
ELSE ''
END AS BUCKETa
from
(select
CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END AS RESULTa
from
) t
May 19, 2011 at 11:36 am
This is one of those times when CROSS APPLY makes your life much easier....
SELECT RESULTa, BUCKETa
FROM <your table>
CROSS APPLY (
SELECT CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END
) AS X(RESULTa)
CROSS APPLY (
CASE WHEN X.RESULTa < 0 THEN '<0'
WHEN X.RESULTa between -1 AND 6 THEN '<=5'
WHEN X.RESULTa >5 THEN '>5'
ELSE ''
END
) AS Y(BUCKETa)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 19, 2011 at 12:26 pm
Sorry, I think I may not have given you all of the information needed. The datea & dateb are from 2 different table. The query should read like this:
(CASE WHEN A.DATEa=B.DATEb THEN 0
WHEN A.DATEa> B.DATEb THEN NETWORKDAYS(B.DATEb, A.DATEa)
WHEN A.DATEa < B.DATEb THEN NETWORKDAYS(A.DATEa, B.DATEb)
WHEN A.STATUS='PENDING' THEN NULL
ELSE NULL
END) AS RESULTa,
Now what I need to be able to do is place those results in buckets, similar to this:
(CASE WHEN RESULTa < 0 THEN '<0'
WHEN RESULTa between -1 AND 6 THEN '<=5'
WHEN RESULTa >5 THEN '>5'
ELSE ''
END) AS BUCKETa
May 19, 2011 at 12:30 pm
That makes no difference to the code I posted, except you are not selecting just from one table....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 19, 2011 at 12:37 pm
I guess I'm confused what I would put in the "your table" then..
SELECT RESULTa, BUCKETa
FROM <your table>(???)
May 19, 2011 at 12:39 pm
tdpearce (5/19/2011)
I guess I'm confused what I would put in the "your table" then..SELECT RESULTa, BUCKETa
FROM <your table>(???)
You might want to post each query in full in order to progress this - at the moment it is too hard to guess....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 19, 2011 at 12:43 pm
Listen, I gotta get some chow now, so I will post this and hope you can take it from there:
SELECT RESULTa, BUCKETa
FROM <your first table>
<some kind of JOIN> <your second table>
ON <some key columns>
CROSS APPLY (
SELECT CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END
) AS X(RESULTa)
CROSS APPLY (
CASE WHEN X.RESULTa < 0 THEN '<0'
WHEN X.RESULTa between -1 AND 6 THEN '<=5'
WHEN X.RESULTa >5 THEN '>5'
ELSE ''
END
) AS Y(BUCKETa)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 19, 2011 at 12:44 pm
Maybe it'd be better if I posted the entire code:
SELECT A.REPORT_DATE, A.REPORT_TYPE, A.ORD_NUM, A.CKT_QTY, A.SUPP_VER, A.ORD_TYPE, A.ORDERPRO_TIN, A.BILLABLE, A.VENDOR, A.E_MRV_FLAG, A.MRV, A.ORDER_EXPEDITE, A.ORDER_EXPEDITE_TYPE, A.PROJECT, A.NASPID, A.CONV_NASPID, A.CUST_NAME, '' AS RYG, REGION, A.DOMESTIC_INTL, A.CPE_FLAG, A.MNGD_FLAG, A.SYSTEM, A.PRODUCT,
(CASE WHEN A.CKT_TYPE LIKE 'EPL%' THEN 'EPL'
WHEN A.CKT_TYPE LIKE '10G%' THEN 'EPL'
ELSE ''
END) AS SUB_CATEGORY,
A.SPEED, A.SPEED_CAT,
(CASE WHEN A. PROV_SUPERVISOR ='JONES, BOB' THEN 'JONES'
WHEN A. PROV_SUPERVISOR ='SMITH, JOHN' THEN 'SMITH'
WHEN (A.PRODUCT='IDE' Or A.PRODUCT='INET') AND A.SYSTEM='OTS' AND A.SPEED<>'DS0' AND A.SPEED<>'DS1' THEN 'WILLIAMS'
WHEN A. PROV_SUPERVISOR = 'JAMES, LEBRON' THEN 'JAMES'
WHEN A. PROV_SUPERVISOR = 'WALTERS, BARBARA' THEN 'JONES'
ELSE ''
END) AS TEAM,
A.PROV_SUPERVISOR, A.PRODUCT_FAMILY, A.NEW_TELCO, A.STATUS, A.PEND_RSN,
(CASE WHEN A.PEND_RSN='CNCC' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='FIRM' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='CSCH' THEN 'FIRM/CSCH/INSTALL'
WHEN A. POM_STATUS='NATIONAL LOCAL INSTALL ' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='SRMS' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='CCPE' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='CORD' THEN 'FIRM/CSCH/INSTALL'
WHEN A. POM_STATUS='Ntwk Eng [&] Install' THEN 'FIRM/CSCH/INSTALL'
WHEN A. POM_STATUS='Test [&] Install' THEN 'FIRM/CSCH/INSTALL'
ELSE 'OTHER'
END) AS PEND_CATEGORY,
A.POM_STATUS, A.POM_SUBSTATUS, A.ACTIVE_FLAG, A.BASELINE_DESC, A.DATE_STRT, A.DATE_CRDD,
(CASE WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>15 THEN '16+'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>10 THEN '11-15'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>5 THEN '6-10'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>0 THEN '0-5'
ELSE 'PAST DUE'
END) AS BUCKET,
A.DATE_CMIT, A.DATE_ICOM, A.DATE_BCOM, A.ORD_BD, A.CRDD_BD, A.TARGET, A.NEW_PPD_FLAG, A.MISS_MET_COMMIT, A.MISS_MET_CRDD, A.MISS_MET_TARGET, A.CENTER, A.JR_SVC_VP, A.SVC_AREA, A.SOLD_SEGMENT, A.SOLD_BRANCH, A.SOLD_DIRECTOR, A.SOLD_JR_SALES_VP, A.SOLD_SR_SALES_VP, A.PROV_CENTER, B.HSC, A.PROVISIONER, A.PROV_MANAGER, A.PROV_SR_MANAGER, A.PROV_DIRECTOR, A.PROV_EXEC_DIRECTOR, A.TEST_INSTALL_CENTER, C.INSTALL_TECH, C.INSTALL_MANAGER, A.TEST_INSTALL_SR_MANAGER, A.TEST_INSTALL_DIRECTOR, A.TEST_INSTALL_EXEC_DIRECTOR, A.IM_CENTER, A.IM_NAME, A.IM_SUP, A.IM_MANAGER_NAME, A.IM_GRP_MANAGER, A.IM_DIRECTOR, A.IM_VP, A.SERVICE_LOOKUP, A.ETH_TYPE, A.CKT_TYPE, A.CKT_TYPE2,
(CASE WHEN (A.REPORT_DATE=A.DATE_CRDD AND A.STATUS='PEND') THEN 'MISSED'
WHEN A.STATUS='PEND' THEN 'PEND'
WHEN A.STATUS='CANC' THEN 'CANC'
WHEN A.MISS_MET_CRDD='EXEMPT' THEN 'EXEMPT'
WHEN A.DATE_CRDD<A.DATE_BCOM THEN 'MISSED'
ELSE 'MET'
END) AS ACTUAL_CDDD,
C.INSTALL_TECH_ASGN_DATE,
(CASE WHEN A.DATE_CMIT IS NULL THEN ''
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1>15 THEN '16+'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1> 10 THEN '11-15'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1> 5 THEN '6-10'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1>05 THEN '0-5'
ELSE 'PAST DUE'
END) AS COMMIT_BUCKET,
(CASE WHEN (A.REPORT_DATE=A.DATE_CRDD AND A.STATUS='PEND' AND A.STATUS='COMP') AND ENTERPRISE.UTIL.NETWORKDAYS(A.DATE_CRDD, A.DATE_BCOM)-1<11 THEN 'Y'
ELSE 'N'
END) AS OVER_10,
(CASE WHEN A.DATE_CMIT IS NULL THEN ''
WHEN A.DATE_CRDD>A.DATE_CMIT THEN 'CRDD>COMMIT'
ELSE ''
END) AS CRDD_GREATER_THAN_COMMIT,
A.MAX_ER_NO, A.MAX_ER_RFT,
(CASE WHEN A.MAX_ER_RFT>A.DATE_CRDD THEN 'Y'
ELSE ''
END) AS ER_GREATER_THAN_CRDD,
C.BLANK, C.BLANK,
(CASE WHEN C.INSTALL_TECH_ASGN_DATE=A.DATE_CRDD THEN 0
WHEN C.INSTALL_TECH_ASGN_DATE> A.DATE_CRDD THEN ENTERPRISE.UTIL.NETWORKDAYS(A.DATE_CRDD, C.INSTALL_TECH_ASGN_DATE)-1
WHEN C.INSTALL_TECH_ASGN_DATE < A.DATE_CRDD THEN ENTERPRISE.UTIL.NETWORKDAYS(C.INSTALL_TECH_ASGN_DATE, A.DATE_CRDD)-1
WHEN A.STATUS='PENDING' THEN NULL
ELSE NULL
END) AS CSCH_TO_CDDD
FROM ERP.BASELINE_PPD_PIVOT_HISTORY_TBL A
LEFT JOIN TPEARCE.HSC_TABLE B
ON A.ORD_NUM=B.ORDER_NO
LEFT JOIN TPEARCE.WFM_TABLES C
ON A.ORD_NUM=C.ORDER_NUMBER
WHERE REPORT_DATE ='17-may-2011' AND JR_SVC_VP='SMITH' AND PROV_SR_MANAGER='SMITH' AND REPORT_TYPE='D' AND ORD_TYPE<>'O' AND BILLABLE='Y' AND PRODUCT <>'IDE' AND A.STATUS <>'CANC'
Or REPORT_DATE ='17-may-2011' AND JR_SVC_VP='SMITH' AND PROV_SR_MANAGER='SMITH' AND REPORT_TYPE='D' AND ORD_TYPE<>'O' AND BILLABLE='Y' AND PRODUCT ='IDE' AND SPEED<>'DS3' AND SPEED<>'OC3' AND A.STATUS <>'CANC'
Or REPORT_DATE ='17-may-2011' AND JR_SVC_VP='SMITH' AND PROV_SR_MANAGER='SMITH' AND REPORT_TYPE='D' AND ORD_TYPE<>'O' AND BILLABLE='Y' AND PRODUCT ='IDE' AND SPEED='DS3' AND SPEED='OC3' AND A.STATUS <>'CANC' AND DATE_STRT<'13-mar-2010'
Group By A.REPORT_DATE, A.REPORT_TYPE, A.ORD_NUM, A.CKT_QTY, A.SUPP_VER, A.ORD_TYPE, A.ORDERPRO_TIN, A.BILLABLE, A.VENDOR, A.E_MRV_FLAG, A.MRV, A.ORDER_EXPEDITE, A.ORDER_EXPEDITE_TYPE, A.PROJECT, A.NASPID, A.CONV_NASPID, A.CUST_NAME, A.REGION, A.DOMESTIC_INTL, A.CPE_FLAG, A.MNGD_FLAG, A.SYSTEM, A.PRODUCT, A.SPEED, A.SPEED_CAT, PROV_SUPERVISOR, A.PRODUCT_FAMILY, A.NEW_TELCO, A.STATUS, A.PEND_RSN, A.POM_STATUS, A.POM_SUBSTATUS, A.ACTIVE_FLAG, A.BASELINE_DESC, A.DATE_STRT, A.DATE_CRDD, A.DATE_CMIT, A.DATE_ICOM, A.DATE_BCOM, A.ORD_BD, A.CRDD_BD, A.TARGET, A.NEW_PPD_FLAG, A.MISS_MET_COMMIT, A.MISS_MET_CRDD, A.MISS_MET_TARGET, A.CENTER, A.JR_SVC_VP, SVC_AREA, SOLD_SEGMENT, A.SOLD_BRANCH, A.SOLD_DIRECTOR, A.SOLD_JR_SALES_VP, A.SOLD_SR_SALES_VP, A.PROV_CENTER, B.HSC, A.PROVISIONER, A.PROV_MANAGER, A.PROV_SR_MANAGER, A.PROV_DIRECTOR, A.PROV_EXEC_DIRECTOR, A.TEST_INSTALL_CENTER, C.INSTALL_TECH, C.INSTALL_MANAGER, A.TEST_INSTALL_SR_MANAGER, A.TEST_INSTALL_DIRECTOR, A.TEST_INSTALL_EXEC_DIRECTOR, A.IM_CENTER, A.IM_NAME, A.IM_SUP, A.IM_MANAGER_NAME, A.IM_GRP_MANAGER, A.IM_DIRECTOR, A.IM_VP, A.SERVICE_LOOKUP, A.ETH_TYPE, A.CKT_TYPE, A.CKT_TYPE2, C.INSTALL_TECH_ASGN_DATE, A.MAX_ER_NO, A.MAX_ER_RFT,C.BLANK, C.BLANK
After the last CASE I need to return a field alias called "BUCKET". Similar to this:
(CASE WHEN CSCH_TO_CDDD< 0 THEN '<0'
WHEN CSCH_TO_CDDD between -1 AND 6 THEN '<=5'
WHEN CSCH_TO_CDDD>5 THEN '>5'
ELSE ''
END) AS BUCKET
May 19, 2011 at 2:05 pm
Yes, that helps...
Try this (I have added some comments where I changed the code):
SELECT A.REPORT_DATE, A.REPORT_TYPE, A.ORD_NUM, A.CKT_QTY, A.SUPP_VER, A.ORD_TYPE, A.ORDERPRO_TIN, A.BILLABLE, A.VENDOR, A.E_MRV_FLAG, A.MRV, A.ORDER_EXPEDITE, A.ORDER_EXPEDITE_TYPE, A.PROJECT, A.NASPID, A.CONV_NASPID, A.CUST_NAME, '' AS RYG, REGION, A.DOMESTIC_INTL, A.CPE_FLAG, A.MNGD_FLAG, A.SYSTEM, A.PRODUCT,
(CASE WHEN A.CKT_TYPE LIKE 'EPL%' THEN 'EPL'
WHEN A.CKT_TYPE LIKE '10G%' THEN 'EPL'
ELSE ''
END) AS SUB_CATEGORY,
A.SPEED, A.SPEED_CAT,
(CASE WHEN A. PROV_SUPERVISOR ='JONES, BOB' THEN 'JONES'
WHEN A. PROV_SUPERVISOR ='SMITH, JOHN' THEN 'SMITH'
WHEN (A.PRODUCT='IDE' Or A.PRODUCT='INET') AND A.SYSTEM='OTS' AND A.SPEED<>'DS0' AND A.SPEED<>'DS1' THEN 'WILLIAMS'
WHEN A. PROV_SUPERVISOR = 'JAMES, LEBRON' THEN 'JAMES'
WHEN A. PROV_SUPERVISOR = 'WALTERS, BARBARA' THEN 'JONES'
ELSE ''
END) AS TEAM,
A.PROV_SUPERVISOR, A.PRODUCT_FAMILY, A.NEW_TELCO, A.STATUS, A.PEND_RSN,
(CASE WHEN A.PEND_RSN='CNCC' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='FIRM' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='CSCH' THEN 'FIRM/CSCH/INSTALL'
WHEN A. POM_STATUS='NATIONAL LOCAL INSTALL ' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='SRMS' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='CCPE' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN='CORD' THEN 'FIRM/CSCH/INSTALL'
WHEN A. POM_STATUS='Ntwk Eng [&] Install' THEN 'FIRM/CSCH/INSTALL'
WHEN A. POM_STATUS='Test [&] Install' THEN 'FIRM/CSCH/INSTALL'
ELSE 'OTHER'
END) AS PEND_CATEGORY,
A.POM_STATUS, A.POM_SUBSTATUS, A.ACTIVE_FLAG, A.BASELINE_DESC, A.DATE_STRT, A.DATE_CRDD,
(CASE WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>15 THEN '16+'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>10 THEN '11-15'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>5 THEN '6-10'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD)-1>0 THEN '0-5'
ELSE 'PAST DUE'
END) AS BUCKET,
A.DATE_CMIT, A.DATE_ICOM, A.DATE_BCOM, A.ORD_BD, A.CRDD_BD, A.TARGET, A.NEW_PPD_FLAG, A.MISS_MET_COMMIT, A.MISS_MET_CRDD, A.MISS_MET_TARGET, A.CENTER, A.JR_SVC_VP, A.SVC_AREA, A.SOLD_SEGMENT, A.SOLD_BRANCH, A.SOLD_DIRECTOR, A.SOLD_JR_SALES_VP, A.SOLD_SR_SALES_VP, A.PROV_CENTER, B.HSC, A.PROVISIONER, A.PROV_MANAGER, A.PROV_SR_MANAGER, A.PROV_DIRECTOR, A.PROV_EXEC_DIRECTOR, A.TEST_INSTALL_CENTER, C.INSTALL_TECH, C.INSTALL_MANAGER, A.TEST_INSTALL_SR_MANAGER, A.TEST_INSTALL_DIRECTOR, A.TEST_INSTALL_EXEC_DIRECTOR, A.IM_CENTER, A.IM_NAME, A.IM_SUP, A.IM_MANAGER_NAME, A.IM_GRP_MANAGER, A.IM_DIRECTOR, A.IM_VP, A.SERVICE_LOOKUP, A.ETH_TYPE, A.CKT_TYPE, A.CKT_TYPE2,
(CASE WHEN (A.REPORT_DATE=A.DATE_CRDD AND A.STATUS='PEND') THEN 'MISSED'
WHEN A.STATUS='PEND' THEN 'PEND'
WHEN A.STATUS='CANC' THEN 'CANC'
WHEN A.MISS_MET_CRDD='EXEMPT' THEN 'EXEMPT'
WHEN A.DATE_CRDD<A.DATE_BCOM THEN 'MISSED'
ELSE 'MET'
END) AS ACTUAL_CDDD,
C.INSTALL_TECH_ASGN_DATE,
(CASE WHEN A.DATE_CMIT IS NULL THEN ''
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1>15 THEN '16+'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1> 10 THEN '11-15'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1> 5 THEN '6-10'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT)-1>05 THEN '0-5'
ELSE 'PAST DUE'
END) AS COMMIT_BUCKET,
(CASE WHEN (A.REPORT_DATE=A.DATE_CRDD AND A.STATUS='PEND' AND A.STATUS='COMP') AND ENTERPRISE.UTIL.NETWORKDAYS(A.DATE_CRDD, A.DATE_BCOM)-1<11 THEN 'Y'
ELSE 'N'
END) AS OVER_10,
(CASE WHEN A.DATE_CMIT IS NULL THEN ''
WHEN A.DATE_CRDD>A.DATE_CMIT THEN 'CRDD>COMMIT'
ELSE ''
END) AS CRDD_GREATER_THAN_COMMIT,
A.MAX_ER_NO, A.MAX_ER_RFT,
(CASE WHEN A.MAX_ER_RFT>A.DATE_CRDD THEN 'Y'
ELSE ''
END) AS ER_GREATER_THAN_CRDD,
C.BLANK, C.BLANK,
CSCH_TO_CDDD,
-- This is the new BUCKET
(CASE WHEN CSCH_TO_CDDD< 0 THEN '<0'
WHEN CSCH_TO_CDDD between -1 AND 6 THEN '<=5'
WHEN CSCH_TO_CDDD>5 THEN '>5'
ELSE ''
END) AS BUCKET
FROM ERP.BASELINE_PPD_PIVOT_HISTORY_TBL A
LEFT JOIN TPEARCE.HSC_TABLE B
ON A.ORD_NUM=B.ORDER_NO
LEFT JOIN TPEARCE.WFM_TABLES C
ON A.ORD_NUM=C.ORDER_NUMBER
-- This has moved from the SELECT to a CROSS APPLY
CROSS APPLY
(SELECT CASE WHEN C.INSTALL_TECH_ASGN_DATE=A.DATE_CRDD THEN 0
WHEN C.INSTALL_TECH_ASGN_DATE> A.DATE_CRDD THEN ENTERPRISE.UTIL.NETWORKDAYS(A.DATE_CRDD, C.INSTALL_TECH_ASGN_DATE)-1
WHEN C.INSTALL_TECH_ASGN_DATE < A.DATE_CRDD THEN ENTERPRISE.UTIL.NETWORKDAYS(C.INSTALL_TECH_ASGN_DATE, A.DATE_CRDD)-1
WHEN A.STATUS='PENDING' THEN NULL
ELSE NULL
END) AS X(CSCH_TO_CDDD)
WHERE REPORT_DATE ='17-may-2011' AND JR_SVC_VP='SMITH' AND PROV_SR_MANAGER='SMITH' AND REPORT_TYPE='D' AND ORD_TYPE<>'O' AND BILLABLE='Y' AND PRODUCT <>'IDE' AND A.STATUS <>'CANC'
Or REPORT_DATE ='17-may-2011' AND JR_SVC_VP='SMITH' AND PROV_SR_MANAGER='SMITH' AND REPORT_TYPE='D' AND ORD_TYPE<>'O' AND BILLABLE='Y' AND PRODUCT ='IDE' AND SPEED<>'DS3' AND SPEED<>'OC3' AND A.STATUS <>'CANC'
Or REPORT_DATE ='17-may-2011' AND JR_SVC_VP='SMITH' AND PROV_SR_MANAGER='SMITH' AND REPORT_TYPE='D' AND ORD_TYPE<>'O' AND BILLABLE='Y' AND PRODUCT ='IDE' AND SPEED='DS3' AND SPEED='OC3' AND A.STATUS <>'CANC' AND DATE_STRT<'13-mar-2010'
Group By A.REPORT_DATE, A.REPORT_TYPE, A.ORD_NUM, A.CKT_QTY, A.SUPP_VER, A.ORD_TYPE, A.ORDERPRO_TIN, A.BILLABLE, A.VENDOR, A.E_MRV_FLAG, A.MRV, A.ORDER_EXPEDITE, A.ORDER_EXPEDITE_TYPE, A.PROJECT, A.NASPID, A.CONV_NASPID, A.CUST_NAME, A.REGION, A.DOMESTIC_INTL, A.CPE_FLAG, A.MNGD_FLAG, A.SYSTEM, A.PRODUCT, A.SPEED, A.SPEED_CAT, PROV_SUPERVISOR, A.PRODUCT_FAMILY, A.NEW_TELCO, A.STATUS, A.PEND_RSN, A.POM_STATUS, A.POM_SUBSTATUS, A.ACTIVE_FLAG, A.BASELINE_DESC, A.DATE_STRT, A.DATE_CRDD, A.DATE_CMIT, A.DATE_ICOM, A.DATE_BCOM, A.ORD_BD, A.CRDD_BD, A.TARGET, A.NEW_PPD_FLAG, A.MISS_MET_COMMIT, A.MISS_MET_CRDD, A.MISS_MET_TARGET, A.CENTER, A.JR_SVC_VP, SVC_AREA, SOLD_SEGMENT, A.SOLD_BRANCH, A.SOLD_DIRECTOR, A.SOLD_JR_SALES_VP, A.SOLD_SR_SALES_VP, A.PROV_CENTER, B.HSC, A.PROVISIONER, A.PROV_MANAGER, A.PROV_SR_MANAGER, A.PROV_DIRECTOR, A.PROV_EXEC_DIRECTOR, A.TEST_INSTALL_CENTER, C.INSTALL_TECH, C.INSTALL_MANAGER, A.TEST_INSTALL_SR_MANAGER, A.TEST_INSTALL_DIRECTOR, A.TEST_INSTALL_EXEC_DIRECTOR, A.IM_CENTER, A.IM_NAME, A.IM_SUP, A.IM_MANAGER_NAME, A.IM_GRP_MANAGER, A.IM_DIRECTOR, A.IM_VP, A.SERVICE_LOOKUP, A.ETH_TYPE, A.CKT_TYPE, A.CKT_TYPE2, C.INSTALL_TECH_ASGN_DATE, A.MAX_ER_NO, A.MAX_ER_RFT,C.BLANK, C.BLANK
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 19, 2011 at 2:24 pm
Thanks so much for helping me out on this.
I'm getting a ORA-00905: missing keyword error at CROSS APPLY
May 19, 2011 at 2:27 pm
tdpearce (5/19/2011)
Thanks so much for helping me out on this.I'm getting a ORA-00905: missing keyword error at CROSS APPLY
Ok, that is an ORACLE error message - you have posted this on a SQL server forum :crazy:
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply