39 lines
1.4 KiB
Transact-SQL
39 lines
1.4 KiB
Transact-SQL
USE [Gaia]
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
|
|
GO
|
|
DECLARE @date_separator CHAR(1)='-'
|
|
SELECT
|
|
COUNT(1) AS cnt
|
|
--, DATEPART(DAY, [c].[StartConversationDate]) AS [day]
|
|
--, DATEPART(MONTH, [c].[StartConversationDate]) AS [month]
|
|
, RIGHT('0000'+CAST(DATEPART(YEAR, [c].[StartConversationDate]) AS VARCHAR(4)),4)+@date_separator
|
|
+RIGHT('00'+CAST(DATEPART(MONTH, [c].[StartConversationDate]) AS VARCHAR(2)), 2)+@date_separator
|
|
+RIGHT('00'+CAST(DATEPART(DAY, [c].[StartConversationDate]) AS VARCHAR(2)), 2) AS [date]
|
|
, r.[requestType]
|
|
, c.[PharmacyCode]
|
|
FROM [crs].[Conversation] c
|
|
OUTER APPLY (
|
|
SELECT req.value('@RequestType', 'nvarchar(50)') AS requestType
|
|
FROM [crs].[Conversation] x
|
|
OUTER APPLY x.[Request].nodes('Request') xx(req)
|
|
WHERE x.[ConversationId] = c.[ConversationId]
|
|
) r
|
|
WHERE [c].[StartConversationDate] > '20251201'
|
|
AND [c].[PharmacyCode]='ama553'
|
|
GROUP BY
|
|
DATEPART(DAY, [c].[StartConversationDate])
|
|
, DATEPART(MONTH, [c].[StartConversationDate])
|
|
, DATEPART(YEAR, [c].[StartConversationDate])
|
|
, r.[requestType]
|
|
, c.[PharmacyCode]
|
|
ORDER BY
|
|
DATEPART(YEAR, [c].[StartConversationDate]) ASC
|
|
, DATEPART(MONTH, [c].[StartConversationDate]) ASC
|
|
, DATEPART(DAY, [c].[StartConversationDate]) ASC
|
|
, [c].[PharmacyCode] ASC
|
|
, COUNT(1) DESC
|
|
;
|
|
|
|
|
|
--137'131 conversations since 1st of december 2025
|