August 30, 2016 at 6:46 am
HI,
Here my sample data and query. I am getting error while using the count with sub queries.
CREATE TABLE incidents (incident_id int,date_logged datetime,usr_id int,dept_id int,item_id int, cust_id int)
insert into incidents values
('1001', '8/20/2016','190','3','800','10'),
('1002', '8/21/2016','111','4','810','12'),
('1003', '8/22/2016','190','3','800','10'),
('1004', '8/23/2016','111','4','822','12'),
('1005', '8/24/2016','190','3','700','11'),
('1006', '8/24/2016','180','3','700','11'),
('1007', '8/24/2016','180','3','700','11'),
('1008', '8/24/2016','190','3','800','10'),
('1009', '8/25/2016','180','3','500','13'),
('1010', '8/26/2016','180','3','500','13'),
('1011', '8/27/2016','180','3','500','13'),
('1012', '8/28/2016','180','3','500','13')
Create TABLE actions ( act_id int, act_type varchar(50) ,incident_id int,usr_id int ,date_actioned datetime,
service_time money)
Insert into actions VALUES
('1', 'TRAVEL',1001,190,8/20/2016,20),
('2', 'ASSIGN',1001,2,8/21/2016,1),
('3', 'TRAVEL',1001,190, 8/22/2016,10),
('4', 'REMOTE',1001,190,8/23/2016,30),
('5', 'TRAVEL',1002,111,8/21/2016,40),
('6', 'ASSIGN',1002,2,8/22/2016, 1),
('7', 'REMOTE',1002,111,8/23/2016,30),
('8', 'TRAVEL',1002,111,8/24/2016,60),
('9', 'TRAVEL',1003,190,8/22/2016,45),
('10', 'ASSIGN',1003,2,8/23/2016, 1),
('11', 'REMOTE',1003,190, 8/23/2016, 10),
('12', 'REMOTE',1003,190, 8/23/2016, 20),
('13', 'ASSIGN',1004,2, 8/23/2016, 1),
('14', 'TRAVEL',1004,111,8/23/2016,20),
('15', 'TRAVEL',1004,111,8/23/2016,20),
('16', 'REMOTE',1004,111,8/23/2016,20),
('16', 'REMOTE',1005,190,8/24/2016,10),
('17', 'TRAVEL',1005,190,8/24/2016,10),
('18', 'TRAVEL',1006,180,8/24/2016,10),
('19', 'REMOTE',1006,180,8/24/2016,10),
('20', 'TRAVEL',1007,180,8/24/2016,10),
('21', 'REMOTE',1007,180,8/24/2016, 10),
('22', 'REMOTE',1008,190,8/24/2016, 20),
('23', 'REMOTE',1009,180,8/25/2016, 20),
('24', 'REMOTE',1010,180,8/26/2016, 20),
('25', 'REMOTE',1011,180,8/27/2016, 20),
('26', 'REMOTE',1012,180,8/28/2016, 20)
CREATE TABLE inc_data (incident_id int,Rep1 char(1), Rep2 char(1),Rep3 char(1),Res1 char(1), Res2 char(1),
Res3 char(1))
insert into inc_data values
(1001, 'y', 'y', 'y', 'y', 'y', 'n'),
(1002, 'n', 'n', 'n', 'n', 'n', 'n'),
(1003, 'y', 'y', 'n', 'n', 'n', 'n'),
(1004, 'y', 'y', 'y', 'y', 'y', 'n'),
(1005, 'y', 'y', 'y', 'y', 'y', 'n'),
(1006, 'n', 'n', 'n', 'n', 'n', 'n'),
(1007, 'y', 'y', 'n', 'n', 'n', 'n'),
(1008, 'y', 'y', 'y', 'n', 'n', 'n'),
(1009, 'y', 'y', 'y', 'y', 'y', 'y'),
(1010, 'y', 'y', 'y', 'y', 'y', 'y'),
(1011, 'y', 'y', 'y', 'y', 'y', 'y'),
(1012, 'y', 'y', 'y', 'n', 'n', 'n')
My Query:
SELECT i.usr_id,
MAX(CASE WHEN id.Rep3 = 'y' THEN 1 ELSE 0 END) AS RespBreach,
MAX(CASE WHEN id.Res3 = 'y' THEN 1 ELSE 0 END) AS ResBreach,
COUNT(DISTINCT i.incident_id) as CallCount,
SUM(CASE WHEN a.act_type = 'TRAVEL' THEN a.service_time ELSE 0 END) AS Travel_Time,
SUM(CASE WHEN a.act_type = 'REMOTE' THEN a.service_time ELSE 0 END) AS Remote_Time,
(SELECT COUNT(*) AS similar_inc_count
FROM (SELECT inc.item_id
FROM incident inc
WHERE inc.user_id = i.user_id
GROUP BY inc.item_id
HAVING COUNT(inc.incident_id) > 1)
) inc
) AS similar_Call
FROM incidents i INNER JOIN
inc_data id
ON i.incident_id = id.incident_id INNER JOIN
actions a
ON i.incident_id = a.incident_id
WHERE i.date_logged BETWEEN '2016-08-20' AND '2016-08-29'
GROUP BY i.usr_id;
Expected Result:
usr_idRespBreach ResBreachCallCountTravel_TimeRemote_TimeSimiler_Call
111 1 0 2 140 50 0
180 4 3 6 20 100 5
190 3 0 4 85 90 2
(As per my query , when join incidents and inc_data i can sum() or count RespBreach and ResBreach. But after joining actions to it (1:many relation) i can't do this anymore.
Help me on this...
August 30, 2016 at 7:59 am
Looks like you are having a problem with the "Similar_Call" column but I'm not sure exactly what you are trying to accomplish...or at least your logic.
I've broken part of the table below to simplify things regarding the column you are trying to create. What is your logic for getting 0, 5 and 2 Similar Calls respectively for the sample data given?
usr_iditem_idNumOfItems
1118101
1118221
1805004
1807002
1907001
1908003
August 30, 2016 at 8:33 am
I think this is what you want, but with the data supplied, the code below is producing slightly different results.
Get used to using APPLY for subqueries, just like you would a table valued function.
SELECT i.usr_id,
MAX(CASE WHEN id.Rep3 = 'y' THEN 1 ELSE 0 END) AS RespBreach,
MAX(CASE WHEN id.Res3 = 'y' THEN 1 ELSE 0 END) AS ResBreach,
COUNT(DISTINCT i.incident_id) as CallCount,
SUM(CASE WHEN a.act_type = 'TRAVEL' THEN a.service_time ELSE 0 END) AS Travel_Time,
SUM(CASE WHEN a.act_type = 'REMOTE' THEN a.service_time ELSE 0 END) AS Remote_Time,
max(isnull(sim.similar_inc_count,0)) as Similar_Call
FROM #incidents i
INNER JOIN #inc_data id ON i.incident_id = id.incident_id
INNER JOIN #actions a ON i.incident_id = a.incident_id
OUTER apply (SELECT COUNT(*) AS similar_inc_count
FROM #incidents inc
WHERE inc.usr_id = i.usr_id
GROUP BY inc.item_id
HAVING COUNT(inc.incident_id) > 1) sim
WHERE i.date_logged BETWEEN '2016-08-20' AND '2016-08-29'
GROUP BY i.usr_id;
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply