A Venue requested to have all the info in the Till Summaries report combined in one custom report different from the format of the Daily Till Balance Report.
A Venue requested to have all the info in the Till Summaries report combined in one custom report different from the format of the Daily Till Balance Report.
Custom SQL Query
SELECT '01 SALES' as NAME, NULL as TILLDATA UNION SELECT '02 Food' AS NAME, SUM(CAST(ProdType4 AS decimal(10, 2)) + CAST(ProdType5 AS decimal(10, 2)) + CAST(ProdType6 AS decimal(10, 2)) + CAST(ProdType7 AS decimal(10, 2)) + CAST(ProdType8 AS decimal(10, 2)) + CAST(ProdType9 AS decimal(10, 2)) + CAST(ProdType10 AS decimal(10, 2)) + CAST(ProdType11 AS decimal(10, 2)) + CAST(ProdType12 AS decimal(10, 2)) + CAST(ProdType13 AS decimal(10, 2)) + CAST(ProdType14 AS decimal(10, 2)) + CAST(ProdType15 AS decimal(10, 2)) + CAST(ProdType16 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '03 Beverage' AS NAME, SUM(CAST(ProdType0 AS decimal(10, 2)) + CAST(ProdType1 AS decimal(10, 2)) + CAST(ProdType2 AS decimal(10, 2)) + CAST(ProdType3 AS decimal(10, 2)) + CAST(ProdType17 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '04 Golf Fees' as NAME, SUM(CAST(ProdType18 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '05 Rentals' as NAME, SUM(CAST(ProdType19 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '06 Sundries' as NAME, SUM(CAST(ProdType20 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '07 Clothing' as NAME, SUM(CAST(ProdType21 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '08 Non-clothing' as NAME, SUM(CAST(ProdType22 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '09 Gift Cards' as NAME, SUM(CAST(ProdType23 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '10 GROSS SALES' as NAME, SUM(CAST((GrossSales + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '11 Discounts' as NAME, SUM(CAST((DiscItem_1 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_2 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_3 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_4 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_5 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_6 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_7 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_8 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_9 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_10 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_11 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_12 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_13 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_14 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_15 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_16 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_17 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_18 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_19 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_20 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_21 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_22 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_23 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_24 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_25 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_26 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_27 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_28 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_29 + 0.01) / 100 AS decimal(10, 2)) + CAST((DiscItem_30 + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '12 NETT SALES' as NAME, SUM(CAST((NettSales + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '13 Member Charges' as NAME, -SUM(CAST((AccountCharged + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '14 Room Charges' as NAME, -SUM(CAST((Transferred + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '15 Tips Card' as NAME, SUM(CAST((TipsCard + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '16 Tips Cash' as NAME, SUM(CAST((TipsCash + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '17 Tips Paid Out' as NAME, SUM(CAST((CashPayout0 + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '18 Tax' as NAME, SUM(CAST([TaxTotals_1] AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '19 DRAWER TOTAL' as NAME, SUM(CAST((TotalinDrawer + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '20 PAYMENT DETAILS' as NAME, NULL as TILLDATA UNION SELECT '21 Cash' as NAME, SUM(CAST((Cash + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '22 Credit Cards' as NAME, SUM(CAST((EFTPOS + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '23 Coupons' as NAME, SUM(CAST((Card_9 + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '24 Gift Cards' as NAME, SUM(CAST((Card_6 + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO}) UNION SELECT '25 TOTALS' as NAME, SUM(CAST((Cash + 0.01) / 100 AS decimal(10, 2)) + CAST((EFTPOS + 0.01) / 100 AS decimal(10, 2)) + CAST((Card_9 + 0.01) / 100 AS decimal(10, 2)) + CAST((Card_8 + 0.01) / 100 AS decimal(10, 2)) + CAST((Card_6 + 0.01) / 100 AS decimal(10, 2))) AS TILLDATA FROM dbo.TillSummaries WHERE (DateTimeWrite > {DATEFROM}) AND (DateTimeWrite < {DATETO})
View Layout
