Oct 8, 2006
I'm very new to SQL.
YOur help is appreciated.
Ok system_user is the main table.
All the CREATED_BY and LAST_UPDATED_BY columns in all tables are supposed to reference back
to the system_user table.
Anything with _TYPE (i.e. CONTACT_TYPE, ADDRESS_TYPE ) is supposed to map to common_lookup
table.
Heres the error I get:
ERROR at line 12:
no mathiching unique or primary key for this column-list.
Heres where I I think I have the mistakes.
Pks and FKs
Thank you.
CREATE TABLE system_user
( SYSTEM_USER_ID NUMBER
, SYSTEM_USER_NAME VARCHAR2(20)
, SYSTEM_USER_GROUP_ID NUMBER
, SYSTEM_USER_TYPE NUMBER
, LAST_NAME VARCHAR2(20)
, FIRST_NAME VARCHAR2(20)
, MIDDLE_INITIAL VARCHAR2(1)
, CREATED_BY NUMBER CONSTRAINT nn_system_user_1 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_system_user_2 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_system_user_1 PRIMARY KEY (SYSTEM_USER_ID, CREATED_BY, LAST_UPDATED_BY));
CREATE TABLE common_lookup
( COMMON_LOOKUP_ID NUMBER
, COMMON_LOOKUP_CONTEXT VARCHAR2(30)
, COMMON_LOOKUP_TYPE VARCHAR2(30)
, COMMON_LOOKUP_MEANING VARCHAR2(30)
, CREATED_BY NUMBER CONSTRAINT common_lookup_1 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT common_lookup_2 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_common_lookup_1 PRIMARY KEY (COMMON_LOOKUP_ID)
, CONSTRAINT fk_common_lookup_1 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY)
, CONSTRAINT fk_common_lookup_2 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
CREATE TABLE contact
( CONTACT_ID NUMBER
, MEMBER_ID NUMBER
, CONTACT_TYPE NUMBER CONSTRAINT nn_contact_1 NOT NULL
, LAST_NAME VARCHAR2(20)
, FIRST_NAME VARCHAR2(20)
, MIDDLE_INITIAL VARCHAR2(1)
, CREATED_BY NUMBER CONSTRAINT nn_contact_2 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_contact_3 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_contact_1 PRIMARY KEY (CONTACT_ID)
, CONSTRAINT fk_contact_1 FOREIGN KEY (CONTACT_TYPE)
REFERENCES common_lookup (COMMON_LOOKUP_ID)
, CONSTRAINT fk_contact_2 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY)
, CONSTRAINT fk_contact_3 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
CREATE TABLE address
( ADDRESS_ID NUMBER
, CONTACT_ID NUMBER CONSTRAINT nn_address_1 NOT NULL
, ADDRESS_TYPE NUMBER CONSTRAINT nn_address_2 NOT NULL
, CITY VARCHAR2(30)
, STATE_PROVINCE VARCHAR2(30)
, POSTAL_CODE VARCHAR2(20)
, CREATED_BY NUMBER CONSTRAINT nn_address_3 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_address_4 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_address_1 PRIMARY KEY (ADDRESS_ID)
, CONSTRAINT fk_address_1 FOREIGN KEY (CONTACT_ID)
REFERENCES contact (CONTACT_ID)
, CONSTRAINT fk_address_2 FOREIGN KEY (ADDRESS_TYPE)
REFERENCES system_user (COMMON_LOOKUP_ID)
, CONSTRAINT fk_address_3 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY);
, CONSTRAINT fk_address_4 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
CREATE TABLE telephone
( TELEPHONE_ID NUMBER
, CONTACT_ID NUMBER CONSTRAINT nn_telephone_1 NOT NULL
, ADDRESS_ID NUMBER CONSTRAINT nn_telephone_2 NOT NULL
, TELEPHONE_TYPE NUMBER CONSTRAINT nn_telephone_3 NOT NULL
, COUNTRY_CODE VARCHAR2(3)
, AREA_CODE VARCHAR2(6)
, TELEPHONE_NUMBER VARCHAR2(10)
, CREATED_BY NUMBER CONSTRAINT nn_telephone_4 NOT NULL
, CREATION_DATE DATE
, LAST_UPDATED_BY NUMBER CONSTRAINT nn_telephone_5 NOT NULL
, LAST_UPDATE_DATE DATE
, CONSTRAINT pk_telephone_1 PRIMARY KEY (TELEPHONE_ID)
, CONSTRAINT fk_telephone_1 FOREIGN KEY (CONTACT_ID)
REFERENCES contact (CONTACT_ID)
, CONSTRAINT fk_telephone_2 FOREIGN KEY (ADDRESS_ID)
REFERENCES address (ADDRESS_ID)
, CONSTRAINT fk_telephone_3 FOREIGN KEY (TELEPHONE_TYPE)
REFERENCES system_user (COMMON_LOOKUP_ID)
, CONSTRAINT fk_telephone_4 FOREIGN KEY (CREATED_BY)
REFERENCES system_user (CREATED_BY);
, CONSTRAINT fk_telephone_5 FOREIGN KEY (LAST_UPDATED_BY)
REFERENCES system_user (LAST_UPDATED_BY));
View 3 Replies
View Related
Nov 16, 2007
I am getting an error Msg 8155, Level 16, State 2, Procedure usp_ImportNewASC_ParticipantPlanFundBalances, Line 1No column was specified for column 49 of 'a'.I think its something to do with the line number 48 and 102 ALLOC_PER is char(9) so i want to to cast it as a int and insert into the prodction data. 1 UPDATE
2 Statements..ParticipantPlanFundBalances1
3 SET
4 Act1 = ACT_ID1,
5 TotAct1 = TOT_ACT1,
6 Act2 = ACT_ID2,
7 TotAct2 = TOT_ACT2,
8 Act3 = ACT_ID3,
9 TotAct3 = TOT_ACT3,
10 Act4 = ACT_ID4,
11 TotAct4 = TOT_ACT4,
12 Act5 = ACT_ID5,
13 TotAct5 = TOT_ACT5,
14 Act6 = ACT_ID6,
15 TotAct6 = TOT_ACT6,
16 Act7 = ACT_ID7,
17 TotAct7 = TOT_ACT7,
18 Act8 = ACT_ID8,
19 TotAct8 = TOT_ACT8,
20 Act9 = ACT_ID9,
21 TotAct9 = TOT_ACT9,
22 Act10 = ACT_ID10,
23 TotAct10 = TOT_ACT10,
24 Act11 = ACT_ID11,
25 TotAct11 = TOT_ACT11,
26 Act12 = ACT_ID12,
27 TotAct12 = TOT_ACT12,
28 Act13 = ACT_ID13,
29 TotAct13 = TOT_ACT13,
30 Act14 = ACT_ID14,
31 TotAct14 = TOT_ACT14,
32 Act15 = ACT_ID15,
33 TotAct15 = TOT_ACT15,
34 Act16 = ACT_ID16,
35 TotAct16 = TOT_ACT16,
36 Act17 = ACT_ID17,
37 TotAct17 = TOT_ACT17,
38 Act18 = ACT_ID18,
39 TotAct18 = TOT_ACT18,
40 Act19 = ACT_ID19,
41 TotAct19 = TOT_ACT19,
42 Act20 = ACT_ID20,
43 TotAct20 = TOT_ACT20,
44 OpeningUnits = UNIT_OP,
45 OPricePerUnit = PRICE_OP,
46 ClosingUnits = UNIT_CL,
47 CPricePerUnit = PRICE_CL,
48 AllocationPercent = i.ALLOC_PER1
49
50 FROM
51 Statements..ParticipantPlanFundBalances1 pfb
52 JOIN (
53 Select
54 cp.PlanId,
55 p.ParticipantId,
56 @PeriodId Period,
57 f.FundId,
58 a.ACT_ID1,
59 a.TOT_ACT1,
60 a.ACT_ID2,
61 a.TOT_ACT2,
62 a.ACT_ID3,
63 a.TOT_ACT3,
64 a.ACT_ID4,
65 a.TOT_ACT4,
66 a.ACT_ID5,
67 a.TOT_ACT5,
68 a.ACT_ID6,
69 a.TOT_ACT6,
70 a.ACT_ID7,
71 a.TOT_ACT7,
72 a.ACT_ID8,
73 a.TOT_ACT8,
74 a.ACT_ID9,
75 a.TOT_ACT9,
76 a.ACT_ID10,
77 a.TOT_ACT10,
78 a.ACT_ID11,
79 a.TOT_ACT11,
80 a.ACT_ID12,
81 a.TOT_ACT12,
82 a.ACT_ID13,
83 a.TOT_ACT13,
84 a.ACT_ID14,
85 a.TOT_ACT14,
86 a.ACT_ID15,
87 a.TOT_ACT15,
88 a.ACT_ID16,
89 a.TOT_ACT16,
90 a.ACT_ID17,
91 a.TOT_ACT17,
92 a.ACT_ID18,
93 a.TOT_ACT18,
94 a.ACT_ID19,
95 a.TOT_ACT19,
96 a.ACT_ID20,
97 a.TOT_ACT20,
98 a.UNIT_OP,
99 a.PRICE_OP,
100 a.UNIT_CL,
101 a.PRICE_CL,
102 Cast(i.ALLOC_PER1 as int)
103 FROM
104 ASDBF a
105 -- Derive the unique PlanId from the Statements ClientPlan table
106 INNER JOIN Statements..ClientPlan cp
107 ON a.PLAN_NUM = cp.ClientPlanId
108 AND
109 cp.ClientId = @ClientId
110 -- Derive the unique ParticipantId from the Statements Participant table
111 INNER JOIN Statements..Participant p
112 ON a.PART_ID = p.PartId
113 --Derive the unique FundID from the Statements Fund Table...
114 INNER JOIN Statements..Fund f
115 ONa.FUND_ID = f.Cusip
116 OR
117 a.FUND_ID = f.Ticker
118 OR
119 a.FUND_ID = f.ClientFundId
120 -- get the allocation percent from the INVSRC
121 LEFT Outer JOIN INVSRC i
122 ONa.FUND_ID = i.INV_ID
123 AND
124 a.PLAN_NUM = i.Plan_Number
125 AND
126 a.PART_ID = i.PART_ID
127 --INNER JOIN Statements..AscActCodes as
128 --ON
129
130 WHERE
131 a.Import = 1
132 )a
133 ON pfb.PlanId = a.PlanId
134 AND
135 pfb.ParticipantId = a.ParticipantId
136 AND
137 pfb.PeriodId = PeriodId
138 AND
139 pfb.FundId = a.FundId
140
Any help will be appreciated
Regards
Karen
View 6 Replies
View Related
Dec 28, 2007
When I attempt to run the following select statement in Aqua I get 2 errors:
- Incorrect syntax near the keyword 'SELECT'.
- Incorrect syntax near ')'.
I belive they both refer to the creation of the subquery (i.e. "From (" and ") t" ) but I can't for the life of me discover anything wrong with the statement. If you run the multiple union subquery by itself, it works just fine. (In case your wondering, the eventual the purpose of creating a sub query is so that I can use the UnPivot statement)
SELECT DATE_DAY
, METRIC
, NUM_OF_TICKETS
, SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NUM_DISPATCH_TO
, DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO_G_L
, NUM_DISPATCH_FROM
, DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM_G_L
, NUM_MTTR
, DURATION_MTTR
, SLA_MTTR
, SLA_MTTR_G_L
, COUNT_CLOSED_MET_DUE_DATE
, NUM_CLOSED_MET_DUE_DATE
--Costs
, OVERTIME
, COUNT_NTF
, NUM_DISPATCH_NTF
, NUM_CTS_DISPATCH
, TICKET_NOT_DISPATCHED
, REMOTE_RESTORAL
--Productivity
, NUM_TICKET_RATIO
, NUM_TECHS_RATIO
, NUM_DISPATCH_TICKET_RATIO
--FMS
, NUM_MET_DISPATCH_TO
, MET_SLA_DISPATCH_TO
, NUM_MET_DISPATCH_FROM
, MET_SLA_DISPATCH_FROM
, NUM_WIP
, DURATION_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE_G_L
, NUM_MET_WIP
, MET_SLA_WIP_TO_RESOLVE
, NUM_MET_MTTR
, MET_SLA_MTTR
, COUNT_RESOLVE_FD
, NUM_RESOLVE_FD
--Action Reports
, AR_NUM_CREATED
, AR_NUM_CLOSED
, AR_NUM_OPEN
, COUNT_METTING_DUE_DATE
, NUM_OPEN_IN_DUE_DATE
--All Tickets
, COUNT_DISPATCHED
, NUM_AWAITING_DISPATCH
, NUM_IN_JEOPARDY
FROM (
--FMS
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, sum( C_NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO
, sum( C_DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L
, sum( C_NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM
, sum( C_DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L
, sum( NUM_CLOSED ) AS NUM_MTTR
, sum( DURATION_MTTR ) AS DURATION_MTTR
, max( SLA_MTTR ) AS SLA_MTTR
, max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, 0 AS OVERTIME
, sum( NUM_CLOSED ) AS COUNT_NTF
, sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, 0 AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, sum( C_NUM_DISPATCH_TO ) AS NUM_MET_DISPATCH_TO
, sum( C_MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO
, sum( C_NUM_DISPATCH_FROM ) AS NUM_MET_DISPATCH_FROM
, sum( C_MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM
, sum( NUM_CLOSED ) AS NUM_WIP
, sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L
, sum( NUM_CLOSED ) AS NUM_MET_WIP
, sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE
, sum( NUM_CLOSED ) AS NUM_MET_MTTR
, sum( MET_SLA_MTTR ) AS MET_SLA_MTTR
, sum( NUM_CLOSED ) AS COUNT_RESOLVE_FD
, sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, sum( NUM_DISPATCH_FROM ) AS COUNT_DISPATCHED
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( CASE WHEN upper(FORCE_STATUS) = 'JEOPARDY' THEN NUM_OPEN ELSE NULL END ) AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Trouble Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
AND SEVERITY IN ( 'Critical','Major' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--AR
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, sum( NUM_CLOSED ) AS COUNT_CLOSED_MET_DUE_DATE
, sum( AR_NUM_MET_DUE_DATE ) AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, sum( NUM_OPEN ) AS COUNT_METTING_DUE_DATE
, sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, sum( NUM_DISPATCH_FROM ) AS COUNT_DISPATCHED
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( CASE WHEN upper(FORCE_STATUS) = 'JEOPARDY' THEN NUM_OPEN ELSE NULL END ) AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Action Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--CTS
SELECT CTS_DATE AS DATE_DAY
, CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, sum( Closed_Tickets_External_NotDispatched + Closed_Tickets_NOC_NotDispatched ) AS NUM_CTS_DISPATCH
, sum( Closed_Tickets_NOC_NotDispatched ) AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.FMS_Daily_Summary_Regional
WHERE CTS_DATE >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND CTS_DATE <= cast(convert(varchar(8),getdate(),1) as datetime)
AND upper( EQ_REGION ) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY CTS_DATE-- AS DATE_DAY
, CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--Tickets Resolved per Tech
SELECT DATE_DAY
, ENTRY_TYPE AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, sum( NUM_OF_TICKETS ) AS NUM_CLOSED_TICKET_RATIO
, sum( NUM_OF_TECHS ) AS NUM_CLOSED_TECHS_RATIO
, 0 AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.vw_CLOSED_TICKET_TO_TECH_RATIO
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, ENTRY_TYPE-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--Tickets Resolved per Tech - Total
SELECT DATE_DAY
, 'Total' AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS COUNT_RFD
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
--Productivity
, sum( NUM_OF_TICKETS ) AS NUM_TICKET_RATIO
, sum( NUM_OF_TECHS ) AS NUM_TECHS_RATIO
, 0 AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.vw_CLOSED_TICKET_TO_TECH_RATIO
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, EQ_MARKET_CLUSTER
--AR Count
SELECT DATE_DAY
, 'All Severities' AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS COUNT_RFD
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, sum( NUM_CREATED ) AS AR_NUM_CREATED
, sum( NUM_CLOSED ) AS AR_NUM_CLOSED
, sum( NUM_OPEN ) AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Action Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER
) t
View 1 Replies
View Related