Looking for share of each Agent
's rows where task
= 'Stalking' in table Stalking
compared to occurrences in table Assigned
during Min and Max stalkDate
in table Stalking
.
Table Assigned
| Agent | task | taskDate |
| ------------ | ------------ | ------------ |
| dbl07 | Stalking | 2019-12-01 |
| dbl07 | patrol | 2020-06-01 |
| dbl07 | travel | 2020-06-24 |
| dbl07 | Stalking | 2021-10-01 |
| dbl07 | Stalking | 2021-12-01 |
| dbl07 | Stalking | 2022-12-24 |
| dbl07 | travel | 2022-12-01 |
| mrBourne | train | 2021-06-06 |
| mrBourne | Stalking | 2022-06-06 |
| mrSmart | Stalking | 2018-01-01 |
| mrSmart | idle | 2021-06-06 |
| mrSmart | Stalking | 2021-01-01 |
| mrSmart | Stalking | 2022-06-06 |
| mrEnglish | lurk | 2022-06-06 |
| mrEnglish | jest | 2022-06-06 |
Table Stalking
, -- stalkDate
min-max = 2020-01-31
- 2022-12-31
Agent | stalkDate |
---|---|
dbl07 | 2020-01-31 |
dbl07 | 2022-12-31 |
mrBourne | 2022-06-30 |
mrSmart | 2022-06-30 |
mrEnglish | 2022-06-30 |
mrPowers | 2022-06-06 |
Result should be:
Agent | prcnt |
---|---|
dbl07 | 66.66 |
mrBourne | 100.00 |
mrSmart | 50.00 |
mrEnglish | 100.00 |
mrPowers | 00.00 |
note: taskDate
in Assigned
and stalkDate in Stalking
are not in sync, the search is made by selecting MIN and MAX stalkDate
from Stalking
.
SELECT
s.Agent,
(CONVERT(DECIMAL(10,2),COUNT(s.Agent))/
CONVERT(DECIMAL(10,2),COUNT(a.Agent))
) * 100 as prcnt
FROM
Stalking s
FULL OUTER JOIN
Assigned a ON s.Agent = a.Agent
WHERE
a.task = 'Stalking'
AND a.taskDate >= (SELECT MIN(stalkDate) FROM Stalking)
AND a.taskDate <= (SELECT MAX(stalkDate) FROM Stalking)
GROUP BY
s.Agent, a.task, a.taskDate
I have 3 problems
- For each
Agent
query results inprcnt
= 100.00 - How to handle "divide by zero" in case of mrEnglish and assign him
prcnt
= 100.00? - How to get
prcnt
= 00.00 to mrPowers? He has no record inAssigned
.
1条答案
按热度按时间iaqfqrcu1#
Using your example data:
You can get do something like:
which gives the output:
This actually differs from your stated expected output, but looking at your example data, I believe 50% is the correct percentage for
dbl07
as they have 6 records inAssigned
which fall into their range of dates inStalking
, three of which have thetask
value of"Stalking"
.There might be a more elegant way to do this without
OUTER APPLY
(perhaps using Window Functions), but this is just the method which came to mind.