The attached rather complex SQL statement has been completely generated by Google AI in Chrome. I did not write a single line. Of course everybody’s situation is different but I was surprised that the Google AI knows the Samba Database structure. The only things that I had to advise to Google was that it should only include orders with CalculatePrice = 1 and that it should include the OrderTags prices.
What it does is that it exports daily sales data split into the respective Sales subaccounts that I have defined, as well as the Cash statement and the Philippine Senior Citizen discount as journal entries that I can subsequently import into Zoho. And it works. So if you have a similar requirement - just ask Google or any other AI.
WITH DailyData AS (
-- Section 1: Menu Item Sales (Base Price + Validated Order Tags JSON)
SELECT
CONVERT(VARCHAR(10), t.[Date], 120) AS [SalesDate],
CASE
WHEN mi.[GroupCode] = 'Steak - USDA' THEN 'USDA Steak'
WHEN mi.[GroupCode] = 'Beer' THEN 'Beer'
WHEN mi.[GroupCode] = 'Bar' THEN 'Bar'
WHEN mi.[GroupCode] IN ('Soft', 'Lemonade', 'Shake') THEN 'no alcohol'
ELSE 'Food'
END AS [SummaryGroup],
SUM(o.[Quantity]) AS [Qty],
-- Adds Base Item Revenue + parsed Modifier Revenue safely
SUM((o.[Price] * o.[Quantity]) + ISNULL(ModifierRevenue.TagTotal, 0)) AS [Revenue]
FROM [Orders] o
INNER JOIN [Tickets] t ON o.[TicketId] = t.[Id]
INNER JOIN [MenuItems] mi ON o.[MenuItemId] = mi.[Id]
-- 👇 ISJSON check added here protects the query from crashing on bad data strings
OUTER APPLY (
SELECT SUM(TRY_CAST(JSON_VALUE(value, '$.PR') AS DECIMAL(18,2)) * TRY_CAST(JSON_VALUE(value, '$.Q') AS DECIMAL(18,2))) AS TagTotal
FROM OPENJSON(CASE WHEN ISJSON(o.[OrderTags]) = 1 THEN o.[OrderTags] ELSE NULL END)
) ModifierRevenue
WHERE t.[Date] >= '2026-03-25 00:00:00'
AND t.[Date] <= '2026-06-30 23:59:59'
AND t.[TotalAmount] > 0
AND o.[CalculatePrice] = 1
GROUP BY
CONVERT(VARCHAR(10), t.[Date], 120),
CASE
WHEN mi.[GroupCode] = 'Steak - USDA' THEN 'USDA Steak'
WHEN mi.[GroupCode] = 'Beer' THEN 'Beer'
WHEN mi.[GroupCode] = 'Bar' THEN 'Bar'
WHEN mi.[GroupCode] IN ('Soft', 'Lemonade', 'Shake') THEN 'no alcohol'
ELSE 'Food'
END
UNION ALL
-- Section 2: SCPWD Discounts from Calculations Table
SELECT
CONVERT(VARCHAR(10), t.[Date], 120) AS [SalesDate],
'SCPWD Discount' AS [SummaryGroup],
1 AS [Qty],
SUM(c.[CalculationAmount]) AS [Revenue]
FROM [Calculations] c
INNER JOIN [Tickets] t ON c.[TicketId] = t.[Id]
WHERE t.[Date] >= '2026-03-25 00:00:00'
AND t.[Date] <= '2026-06-30 23:59:59'
AND c.[Name] = 'SCPWD Discount'
AND t.[TotalAmount] > 0
GROUP BY CONVERT(VARCHAR(10), t.[Date], 120)
),
OriginalSQLSummary AS (
-- This captures your exact final output format as a source table
SELECT
[SalesDate] AS [Date],
ISNULL([SummaryGroup], '>> DAILY TOTAL') AS [SummaryGroup],
SUM([Qty]) AS [TotalQtySold],
SUM([Revenue]) AS [TotalRevenue]
FROM DailyData
GROUP BY ROLLUP([SalesDate], [SummaryGroup])
HAVING [SalesDate] IS NOT NULL
),
JournalEntriesBase AS (
-- SECTION 1: Generate CREDIT Rows for Sales
SELECT
[Date] AS [Journal Date],
' ' AS [Reference Number],
' ' AS [Journal Number Prefix],
'Daily Sales' AS [Notes],
'both' AS [Journal Type],
'PHP' AS [Currency],
CASE
WHEN [SummaryGroup] = 'Food' THEN 'Food Sales'
WHEN [SummaryGroup] = 'Bar' THEN 'Bar Sales'
WHEN [SummaryGroup] = 'Beer' THEN 'Beer Sales'
WHEN [SummaryGroup] = 'USDA Steak' THEN 'Food Sales - USDA Steaks'
WHEN [SummaryGroup] = 'no alcohol' THEN 'Non Alcoholic Sales'
END AS [Account],
'Sales Summary: ' + [SummaryGroup] AS [Description],
' ' AS [Contact Name],
CAST(0.00 AS DECIMAL(18,2)) AS [Debit],
CAST([TotalRevenue] AS DECIMAL(18,2)) AS [Credit]
FROM OriginalSQLSummary
WHERE [SummaryGroup] IN ('Food', 'Bar', 'Beer', 'USDA Steak', 'no alcohol')
UNION ALL
-- SECTION 2: Generate DEBIT Row for SCPWD Discount
SELECT
[Date],
' ',
' ',
'Daily Sales',
'both',
'PHP',
'SCPWD Discount',
'Discount Summary',
' ',
CAST(ABS([TotalRevenue]) AS DECIMAL(18,2)) AS [Debit],
0.00 AS [Credit]
FROM OriginalSQLSummary
WHERE [SummaryGroup] = 'SCPWD Discount'
UNION ALL
-- SECTION 3: Generate DEBIT Row for Petty Cash
SELECT
[Date],
' ',
' ',
'Daily Sales',
'both',
'PHP',
'Petty Cash',
'Daily Balanced Deposit',
' ',
CAST([TotalRevenue] AS DECIMAL(18,2)) AS [Debit],
0.00 AS [Credit]
FROM OriginalSQLSummary
WHERE [SummaryGroup] = '>> DAILY TOTAL'
)
-- FINAL SELECT: Applies chronological day-by-day sequencing for Suffix
SELECT
[Journal Date],
[Reference Number],
[Journal Number Prefix],
-- 👇 Computes sequential number (1, 2, 3...) based on the transaction date order
CAST(DENSE_RANK() OVER (ORDER BY [Journal Date] ASC) AS VARCHAR(10)) AS [Journal Number Suffix],
[Notes],
[Journal Type],
[Currency],
[Account],
[Description],
[Contact Name],
[Debit],
[Credit]
FROM JournalEntriesBase
ORDER BY [Journal Date] ASC, [Debit] DESC, [Account] ASC;