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