Grouping Issue

EugeneRequilet · July 19, 2023, 7:56am

Hi There, I am trying to Select the number of days that an index (H_IDX) has had calls, no matter how many calls were made in a day. i.e. if there is more than one call per day then the day counts as one.
I have created a #Temp file with data and a script that I tried to use but I can't get the correct answer.
Answer I am looking for is: If H_IDX = 1 Then DaysCalled = 3, If H_IDX = 2 Then DaysCalled = 2

IF OBJECT_ID('tempdb..#TempFile') IS NOT NULL DROP TABLE #TempFile;
Go

CREATE TABLE [dbo].[#TempFile](
[H_IDX] Int NOT NULL,
[H_DateTime] DateTime NOT NULL,
[H_HA_ID] Int NOT NULL
);
Go

INSERT INTO #TempFile VALUES (1,'2023-07-15 11:14:54.060',6);
INSERT INTO #TempFile VALUES (1,'2023-07-17 11:14:54.060',6);
INSERT INTO #TempFile VALUES (1,'2023-07-17 11:15:54.060',3);
INSERT INTO #TempFile VALUES (1,'2023-07-17 11:17:54.060',6);
INSERT INTO #TempFile VALUES (1,'2023-07-18 08:17:54.060',3);
INSERT INTO #TempFile VALUES (1,'2023-07-18 08:18:54.060',4);
INSERT INTO #TempFile VALUES (1,'2023-07-19 08:11:54.060',6);
INSERT INTO #TempFile VALUES (1,'2023-07-19 08:12:54.060',7);
INSERT INTO #TempFile VALUES (1,'2023-07-19 08:13:54.060',6);
INSERT INTO #TempFile VALUES (1,'2023-07-19 08:14:54.060',6);
INSERT INTO #TempFile VALUES (1,'2023-07-19 08:15:54.060',6);
INSERT INTO #TempFile VALUES (1,'2023-07-19 08:16:54.060',6);
INSERT INTO #TempFile VALUES (2,'2023-07-15 10:14:54.060',5);
INSERT INTO #TempFile VALUES (2,'2023-07-17 11:14:54.060',6);
INSERT INTO #TempFile VALUES (2,'2023-07-17 12:15:54.060',3);
INSERT INTO #TempFile VALUES (2,'2023-07-18 09:17:54.060',3);
INSERT INTO #TempFile VALUES (2,'2023-07-18 10:18:54.060',4);
INSERT INTO #TempFile VALUES (2,'2023-07-19 08:12:54.060',6);
INSERT INTO #TempFile VALUES (2,'2023-07-19 08:13:54.060',6);
INSERT INTO #TempFile VALUES (2,'2023-07-19 08:14:54.060',3);
INSERT INTO #TempFile VALUES (2,'2023-07-19 08:15:54.060',3);
INSERT INTO #TempFile VALUES (2,'2023-07-19 08:16:54.060',3);
INSERT INTO #TempFile VALUES (2,'2023-07-19 08:17:54.060',3);
Go

Select H_IDX, CAST(H_DateTime As Date) As 'Date',
Sum(CASE When H_HA_ID = 6 Then 1 Else 0 End) As CallsMade From #TempFile
Where H_IDX = 1
Group By H_IDX, CAST(H_DateTime As Date)
Order By H_IDX, CAST(H_DateTime As Date)

I get this answer:
|H_IDX|Date|CallsMade|
|1|2023-07-15|1|
|1|2023-07-17|2|
|1|2023-07-18|0|
|1|2023-07-19|5|

I need this Answer
|H_IDX|DaysCalled|
|1||3|

Please help.

gdl · July 19, 2023, 8:32am

Hi.
I don't understand the equal to 6 rule...
Can you verify that this query works?

;WITH CTE AS
(
Select H_IDX, CAST(H_DateTime As Date) As 'Date',
Sum(CASE When H_HA_ID = 6 Then 1 Else 0 End) As CallsMade From #TempFile
Group By H_IDX, CAST(H_DateTime As Date)
)
SELECT H_IDX, COUNT(CallsMade) AS CALLSMADE
FROM CTE
WHERE CallsMade>0
GROUP BY H_IDX
Order By H_IDX

EugeneRequilet · July 19, 2023, 8:51am

gdl:

WITH CTE AS
(
Select H_IDX, CAST(H_DateTime As Date) As 'Date',
Sum(CASE When H_HA_ID = 6 Then 1 Else 0 End) As CallsMade From #TempFile
Group By H_IDX, CAST(H_DateTime As Date)
)
SELECT H_IDX, COUNT(CallsMade) AS CALLSMADE
FROM CTE
WHERE CallsMade>0
GROUP BY H_IDX
Order By H_IDX

Hi gdl, Yes your code does work.
The equal to 6 rule is: 6 = phone call, 3 = SMS, etc

gdl · July 19, 2023, 9:57am

Hi Eugene.
Thank you for the clarification.

Therefore, I think that the code can be further simplified because you are interested in aggregating the tuples by H_IDX and data and, subsequently, counting the rows aggregated by H_IDX. The sum is useless.

I send you a solution that expects CTE, but if you don't like CTE, then the second one doesn't use it.

With CTE

;WITH CTE AS
(
	SELECT
		H_IDX
	FROM
		#TempFile
	WHERE
		H_HA_ID = 6
	GROUP BY
		H_IDX
		, CAST(H_DateTime AS Date)
)
SELECT
	H_IDX
	, COUNT(H_IDX)
FROM
	CTE
GROUP BY
	H_IDX
ORDER BY
	H_IDX

Without CTE

SELECT
	H_IDX
	, COUNT(H_IDX)
FROM
(
	SELECT
		H_IDX
	FROM
		#TempFile
	WHERE
		H_HA_ID = 6
	GROUP BY
		H_IDX
		, CAST(H_DateTime AS Date)
) AS T
GROUP BY
	H_IDX
ORDER BY
	H_IDX

EugeneRequilet · July 20, 2023, 7:44am

gdl:

WITH CTE AS
(
Select H_IDX, CAST(H_DateTime As Date) As 'Date',
Sum(CASE When H_HA_ID = 6 Then 1 Else 0 End) As CallsMade From #TempFile
Group By H_IDX, CAST(H_DateTime As Date)
)
SELECT H_IDX, COUNT(CallsMade) AS CALLSMADE
FROM CTE
WHERE CallsMade>0
GROUP BY H_IDX
Order By H_IDX

Thanks for the help gdl.
I could not get the CTE to work because it is a script within a Left Join (and I am not that clever).
The solution I found using ChatGPT was:
SELECT H_IDX,
COUNT(DISTINCT CAST(H_DateTime AS DATE)) AS DaysCalled
From #TempFile
WHERE H_HA_ID = 6
GROUP BY H_IDX

I feel so stupid.

I also had a speed issue when I added this to my script the run time went to over an hour.
What I did was save the results of my script to a #Temp file then used the above script on the #Temp file which reduced the run time to 6 seconds.
Thanks again.
Eugene