I know I need to use FOR JSON to make this work, I just unsure of how to do groups by a certain column for this.
I have the following dataset:
property confirmation exemptions taxReasonId taxId
0145 29SW8TW9F 2020-05-09: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 2QWKNZM8F 2020-08-07: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 2QWKNZM8F 2020-08-08: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-06-25: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-06-26: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-06-27: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-06-28: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-06-29: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-06-30: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-07-01: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-07-02: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-07-03: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3BM6SV9C3 2020-07-04: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-13: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-14: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-15: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-16: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-17: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-18: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-19: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-20: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-21: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-22: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-23: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-24: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-25: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
0145 3FY4HQ7RB 2020-05-26: ["occupancy tax","room tax"] 5e61836ed0687c000143d77e OTA Prepaid
I need to turn each group of "confirmation" into a JSON payload, preferably with an identifier column of "confirmation":
{
"taxId": "OTAPrepaid",
"taxReasonId": "5e61836ed0687c000143d77e",
"exemptions":
{
"2020-08-07": ["occupancy tax", "room tax"],
"2020-08-08": ["occupancy tax", "room tax"]
}
}
This is the query I am using to get my dataset:
SELECT res.property, res.confirmationId, CAST(date AS VARCHAR(12)) + ': ' + tax.taxClass AS exemptions, id AS taxReasonId, tax.reasonName AS taxId
FROM
(
SELECT property, date, confirmationId
FROM dbo.tb_rguest_reservation_records
WHERE CAST(property AS INT) IN
(
50,66,74,98,105,149, --Kentucky, Room Tax Exempt
132, --South Carolina, Room and Occupancy Tax Exempt
11,160,165,167, --Colorado, Room Tax Exempt
19,46,87,88,145,169,191, --Indiana, Room and Occupancy Tax Exempt
116,142,162 --New Mexico, Room Tax Exempt
)
AND ratePlanId IN ('NOPTS','ADVOTC','ADVPLA','ADVHT','OTAPLA','OTAPAO','OTAHT','OTAHOT')
AND arrivalDate >= CAST(GETDATE() AS DATE)
AND property = '0145'
) res
LEFT JOIN
(
SELECT property, reasonName, id, taxClass
FROM dbo.tb_rguest_tax_exempt_reasons
WHERE reasonName = 'OTA Prepaid'
) tax
ON res.property = tax.property
FOR JSON PATH
When I run the FOR JSON PATH it gives me the following, which is not what I am looking for exactly.
[
{
"property": "0145",
"confirmationId": "74XD3NMWF",
"exemptions": "2020-03-06: [\"occupancy tax\",\"room tax\"]",
"taxReasonId": "5e61836ed0687c000143d77e",
"taxId": "OTA Prepaid"
},
{
"property": "0145",
"confirmationId": "T6JYFMT6P",
"exemptions": "2020-03-06: [\"occupancy tax\",\"room tax\"]",
"taxReasonId": "5e61836ed0687c000143d77e",
"taxId": "OTA Prepaid"
},
{
"property": "0145",
"confirmationId": "92DR3M7DB",
"exemptions": "2020-03-11: [\"occupancy tax\",\"room tax\"]",
"taxReasonId": "5e61836ed0687c000143d77e",
"taxId": "OTA Prepaid"
},
etc...
So this is as ugly of logic as it comes to me, but it works...
IF OBJECT_ID('tempdb.dbo.##temp', 'U') IS NOT NULL DROP TABLE ##temp;
IF OBJECT_ID('tempdb.dbo.##temp2', 'U') IS NOT NULL DROP TABLE ##temp2;
DECLARE @query AS NVARCHAR(MAX)
DECLARE @jsonValue AS NVARCHAR(MAX)
DECLARE @dateChange DATE
DECLARE @property VARCHAR(4)
SET @property = '0145'
DECLARE @confirmation VARCHAR(12)
SET @confirmation = 'C3Q28J82B'
DECLARE @taxClass VARCHAR(100)
SET @taxClass =
(
SELECT taxClass
FROM dbo.tb_rguest_tax_exempt_reasons
WHERE property = @property
AND reasonName = 'OTA Prepaid'
)
DECLARE @dateColumns AS NVARCHAR(MAX)
SET @dateColumns =
STUFF(
(
SELECT DISTINCT ',' + QUOTENAME(date)
FROM dbo.tb_rguest_reservation_records
WHERE confirmationId = @confirmation
AND property = @property
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
DECLARE @dateCount INT
SET @dateCount =
(
SELECT COUNT(*)
FROM dbo.tb_rguest_reservation_records
WHERE confirmationId = @confirmation
AND property = @property
)
DECLARE @dateCounter INT
SET @dateCounter = 1
SET @query =
'SELECT *
INTO ##temp
FROM
(
SELECT res.confirmationId, tax.reasonName AS taxId, tax.id AS taxReasonId,
res.date, JSON_QUERY(tax.taxClass) AS taxClass
FROM dbo.tb_rguest_reservation_records res
LEFT JOIN dbo.tb_rguest_tax_exempt_reasons tax
ON res.property = tax.property
WHERE res.confirmationId = ' + '''' + @confirmation + '''' + '
) AS res
PIVOT
(
MIN(res.taxClass)
FOR [date] IN ('+ @dateColumns +')
) AS pvt'
EXEC (@query)
SET @query =
'DECLARE @jsonValue VARCHAR(MAX)
SET @jsonValue =
(
SELECT taxId,
taxreasonId,
' +
REPLACE(
REPLACE(
REPLACE(
@dateColumns, '[', 'JSON_QUERY(['
), ']', '])'
), ',', ' AS [exemptions.1], ') + ' AS [exemptions.2]' + '
FROM ##temp
FOR JSON PATH
)
SELECT @jsonValue AS jsonValue
INTO ##temp2'
EXEC (@query)
SET @jsonValue =
(
SELECT jsonValue
FROM ##temp2
)
WHILE @dateCounter <= @dateCount
BEGIN
SET @dateChange =
(
SELECT date
FROM
(
SELECT property, confirmationId, date, ROW_NUMBER() OVER(ORDER BY date) AS rowNumber
FROM dbo.tb_rguest_reservation_records
WHERE confirmationId = @confirmation
AND property = @property
) res
WHERE rowNumber = @dateCounter
)
SET @jsonValue =REPLACE(@jsonValue, '"' + CAST(@dateCounter AS VARCHAR(12)) + '"', '"' + CAST(@dateChange AS VARCHAR(12)) + '"')
SET @dateCounter = @dateCounter + 1
END
SELECT @jsonValue