September 6, 2016 at 12:17 pm
I have created below query for SSRS with one to many relation join.
Basically my logic is
=>First check the inc_id in incident table reference to the usr_id
=>Retrieve the data from actions table related to usr_id in incident table
Please help me out to get the result as sample like below,
Table:incident
+--------+-------------+--------+---------+
| inc_id | date_logged | usr_id | dept_id |
+--------+-------------+--------+---------+
| 100 | 1-Aug-15 | 1 | 10 |
| 101 | 1-Sep-15 | 2 | 20 |
| 102 | 25-Aug-15 | 1 | 10 |
+--------+-------------+--------+---------+
Table:Actions
+--------+-----------+----------+--------------+--------+---------+
| inc_id | date_act | act_type | servicr_time | usr_id | dept_id |
+--------+-----------+----------+--------------+--------+---------+
| 100 | 2-Aug-15 | Travel | 300 | 1 | 10 |
| 100 | 3-Aug-15 | Remote | 200 | 3 | 30 |
| 100 | 5-Aug-15 | Travel | 200 | 1 | 10 |
| 100 | 10-Sep-15 | Travel | 250 | 1 | 10 |
| 101 | 1-Sep-15 | Travel | 150 | 2 | 20 |
| 101 | 1-Sep-16 | Travel | 120 | 3 | 30 |
| 102 | 29-Aug-15 | Travel | 150 | 1 | 10 |
| 102 | 30-Aug-15 | Remote | 120 | 1 | 10 |
+--------+-----------+----------+--------------+--------+---------+
My Query:
SELECT COUNT(distinct inc.inc_id) AS [inc_count], inc.inc_id, inc.dept_id, inc.usr_id,
DATEPART(YEAR, a.date_act) 'act_year',
DATENAME(MONTH, a.date_act) 'act_month',
( SELECT ISNULL(SUM(ac.service_time), 0) AS Expr1
FROM actions as ac
WHERE (ac.act_type IN ('Travel Time'))
AND (ac.inc_id = inc.inc_id)
AND (DATEPART(YEAR, ac.date_act) = DATEPART(YEAR, a.date_act))
AND (DATENAME(MONTH, ac.date_act) = DATENAME(MONTH, a.date_act))
AND ac.date_act BETWEEN '2015-08-01' AND DATEADD(day, 1, '2015-09-30')
) / 60 AS [Travel_Time],
( SELECT ISNULL(SUM(ac.service_time), 0) AS Expr1
FROM actions as ac
WHERE (ac.act_type IN ('Remote'))
AND (ac.inc_id = inc.inc_id)
AND (DATEPART(YEAR, ac.date_act) = DATEPART(YEAR, a.date_act))
AND (DATENAME(MONTH, ac.date_act) = DATENAME(MONTH, a.date_act))
AND ac.date_act BETWEEN '2015-08-01' AND DATEADD(day, 1, '2015-09-30')
) / 60 AS [Remote_Time]
FROM incident AS inc
INNER JOIN inc_data as id ON inc.inc_id = id.inc_id
INNER JOIN actions as a ON a.inc_id = inc.inc_id
WHERE a.dept_id IN (10)
AND a.date_act BETWEEN '2015-08-01' AND DATEADD(day, 1, '2015-09-30')
GROUP BY inc.inc_id,a.date_act,inc.dept_id, inc.usr_id
Expected Result Table:
+-----------+--------+------+-----------+--------+---------+-------------+-------------+
| inc_count | inc_id | Year | Month | usr_id | dept_id | Travel_Time | Remote_Time |
+-----------+--------+------+-----------+--------+---------+-------------+-------------+
| 1 | 100 | 2015 | August | 1 | 10 | 500 | 0 |
| 1 | 100 | 2015 | September | 1 | 10 | 250 | 0 |
| 1 | 102 | 2015 | August | 1 | 10 | 150 | 120 |
+-----------+--------+------+-----------+--------+---------+-------------+-------------+
September 6, 2016 at 2:29 pm
There are several issues here.
1) Your data is not in a format that people can cut and paste it into SSMS to get started. Follow the instructions in the first link in my signature to find out how to post readily consumable data.
2) You missing one of the tables in your query.
3) Your data is not in a format that people can cut and paste it into SSMS to get started. This is so important it bears repeating.
4) Your expected outcome is grouped by the year and month, but your GROUP BY statement is grouped by the date which includes the year, month, and DAY.
5) You're using both a JOIN and a subquery to find the same data. Pick one or the other. The JOIN is going to give you better performance in most cases. If you use the JOIN, you'll want to use a CASE expression to separate the travel time from the remote time.
6) You're subquery is filtering based on 'Travel Time', but your data has simply 'Travel'.
7) You're using integer division instead of decimal division.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply