Stack Overflow Asked by Ziegler199 on November 22, 2021
I am looking to count a number of instances for a group of people that had a request per month however I only want to count this once rather than however many times the person requested something. Here is an example of my data table so this might make more sense
Client Customer ID Request ID RequestStartDate RequestEndDate
1 A1 9991 03/04/19 07/03/19
1 A1 9992 07/05/19 08/05/19
1 A1 9993 08/09/19 10/01/19
There are around 30k results but I wrote a query to grab the instances per month when a request was asked, this is what I came up with:
Select Distinct
CLient,
(SELECT COUNT(*) from #Testdata21234 where '2019-07-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.RequestStartDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.BRequestEndDate), 0) AND CLient = g.Client) as 'July19',
(SELECT COUNT(*) from #Testdata21234 where '2019-08-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.RequestStartDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.RequestEndDate), 0) AND CLient = g.Client) as 'August19',
(SELECT COUNT(*) from #Testdata21234 where '2019-09-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.RequestStartDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.RequestEndDate), 0) AND CLient = g.Client) as 'September19'
FROM #testdata21234 g
group by client
My results end up like this if using the above example
July19 August19 September19
2 2 1
I would like my output to get the following:
July19 August19 September19
1 1 1
Essentially I want this to count once because although multiple requests, it is under the same Customer ID and I am not counting separate instances, just when the Customer actively pursued a request.
I hope this made sense and please, any help would be great.
Edit:
To further explain, lets try this example
Client Customer ID RequestID RequestStartDate RequestEndDate
1 A1 9991 03/03/19 07/03/19
1 A1 9992 07/05/19 08/05/19
1 A1 9993 08/09/19 10/01/19
1 A2 9994 07/02/19 07/10/19
1 A2 9995 07/15/19 08/06/19
1 A3 9996 02/01/19 07/01/19
1 A4 9997 08/05/19 08/16/19
1 A4 9998 08/30/19 09/01/19
For the above example, I need the outcome of the following counts by month
July19 August19 Septemeber19
4 3 2
I am counting based on clientID however need to filter based on a requestID. I hope this makes more sense, sorry for any confusion.
I think that hardcoding each month is not good SQL logic, you should use group by
and format
(see date format). So if, for example, you are trying to count the numbers of clients started each month, you should do something like this:
SELECT
FORMAT(g."RequestStartDate",'MMMM yy') month_year,
COUNT(DISTINCT g."Customer ID") count_clients
FROM #testdata21234 g
GROUP BY 1
Now, there is still logic to add the g."RequestEndDate"
and other things you want, but this is the direction I suggest..
Answered by Chananel P on November 22, 2021
You acces the same table multiple times, this can be simplified using conditional aggregation. Additionally you probably want to check if request start/end overlaps with a given month. Regarding your once per customer, this can be done using DISTNCT:
Select client,
count(distinct CASE WHEN RequestEndDate >= '2019-07-01'
AND RequestStartDate < '2019-08-01'
then "Customer ID" end) AS July19, -- don't know if this matches your logic, once per customer?
count(distinct CASE WHEN RequestEndDate >= '2019-08-01' -- overlap calculation
AND RequestStartDate < '2019-09-01'
then "Customer ID" end) AS Aug19,
count(distinct CASE WHEN RequestEndDate >= '2019-09-01'
AND RequestStartDate < '2019-10-01'
then "Customer ID" end) AS Sep19
from #testdata21234
group by client;
Answered by dnoeth on November 22, 2021
You can use count(distinct)
and radically simplify the date comparison logic. I'm pretty sure you want clients that overlap at all with the months, so:
Select client,
(select count(*)
from #Testdata21234 g2
where g2.RequestStartDate < '2019-08-01' and
g2.RequestEndDate >= '2019-07-01'
) July19,
(select count(*)
from #Testdata21234 g2
where g2.RequestStartDate < '2019-09-01' and
g2.RequestEndDate >= '2019-08-01'
) Aug19,
(select count(*)
from #Testdata21234 g2
where g2.RequestStartDate < '2019-10-01' and
g2.RequestEndDate >= '2019-09-01'
) Sep19
from (select distinct client #testdata21234) g;
Answered by Gordon Linoff on November 22, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP