Tax Base Amount

Hi,

Currently, in Work Period Report, I am able to see the Sales Total and the Tax Amount for SR - 6% and ZR - 0%.

However, I would like to ask can I see the Sales in breakdown, that is Sales for SR - 6% and Sales for ZR - 0%?

For example, I have $106 Sales whereby the $100 is the Sales Total and $6 is GST Amount. Similarly, I have $50 Sales with Zero Rated. I would like to know at the end of the day, how much sales are for SR - 6% and how much sales are for ZR - 0%.

Kindly help and advise.

Thnaks.

You will need to use the Custom Reports Module to be able to derive those values and/or use SQL script.

The tables on the left are generated using SQL, while the table on the right is the built-in WorkPeriod Report.

Here is the SQL script used to generate the ZED Report (outlined in black above):

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** ZED REPORT                                                                         ***/
/***                                                                                    ***/
/*** see USER SECTION (after declarations) to set:                                      ***/
/***  Date for Report                                                                   ***/
/***  Tax Rates via Template Names                                                      ***/
/***  Foreign Currency Exchange (optional)                                              ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/

/*****************************************/
/************* DO NOT TOUCH **************/
/***** Declarations & Initialization *****/
/*****************************************/

USE [SambaPOS4]

declare @int_WorkPeriod_ID int = 0
declare @var_WorkPeriod_ID varchar(7) = '0'
declare @dat_WorkPeriod_Beg Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_Beg varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_End varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End_Check Datetime = '9999-12-31 23:59:59'

declare @int_Ticket_Beg int = 0 
declare @var_Ticket_Beg varchar(7) = '0'
declare @int_Ticket_End int = 0
declare @var_Ticket_End varchar(7) = '0'
declare @int_N int = 0
declare @dec_XR money = 1
declare @T1 money = 1
declare @T2 money = 1
declare @T3 money = 1

declare @T1_TTL_c varchar(19) = ''
declare @T2_TTL_c varchar(19) = ''
declare @T3_TTL_c varchar(19) = ''
declare @Tax_TTL_c varchar(19) = ''
declare @SalesTaxExcl_TTL_c varchar(19) = ''
declare @SalesNoTax_TTL_c varchar(19) = ''
declare @Sales_TTL_c varchar(19) = ''

declare @T1_TTL money = 0
declare @T2_TTL money = 0
declare @T3_TTL money = 0
declare @Tax_TTL money = 0
declare @SalesTaxExcl_TTL money = 0
declare @SalesNoTax_TTL money = 0
declare @Sales_TTL money = 0

declare @T1_taxout money = 0
declare @T2_taxout money = 0
declare @T3_taxout money = 0

declare @T1_taxout_c varchar(10) = ''
declare @T2_taxout_c varchar(10) = ''
declare @T3_taxout_c varchar(10) = ''

declare @int_PrintZED int = 0

declare @BASEPATH varchar(100) = ''

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** USER SECTION (Begin)                                                               ***/
/*** set WorkPeriod Begin & End Dates                                                   ***/
/*** select Tax Rates via Template Names                                                ***/
/*** select Foreign Currency Exchange Rate                                              ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/

--set @BASEPATH = 'C:\D\Programs\POS\SALES\'
set @int_PrintZED = 0

-- TODAY
--set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)

-- OTHER DAY
--set @dat_WorkPeriod_Beg = '2014-01-24'
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
--set @dat_WorkPeriod_End = '2014-01-25 00:17:40.347'

-- DATE CONSTRAINED
--select @int_WorkPeriod_ID = [Id] FROM [dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End

-- LATEST CLOSED WorkPeriod
select @int_WorkPeriod_ID = max([Id]) FROM [dbo].[WorkPeriods] WHERE [StartDate] != [EndDate]
select @dat_WorkPeriod_Beg = [StartDate] FROM [dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID
select @dat_WorkPeriod_End_Check = [EndDate] FROM [dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID

-- check end date: if the workperiod is still open, end date will be the same as beg date, so change end to be 9999-12-31
select @dat_WorkPeriod_End = 
case @dat_WorkPeriod_End_Check
when @dat_WorkPeriod_Beg then @dat_WorkPeriod_End
else @dat_WorkPeriod_End_Check
end

-- ForeignCurrency ExchangeRate
--SELECT @dec_XR = [ExchangeRate] FROM [dbo].[ForeignCurrencies] WHERE [Name] = 'USD'

-- Taxes
SELECT @T1 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T1'
SELECT @T2 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T2'
SELECT @T3 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T3'

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** USER SECTION (End)                                                                 ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/







/*****************************************/
/************* DO NOT TOUCH **************/
/** Declarations for (in-memory) Tables **/
/*****************************************/

declare @tbl_Transactions table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[TicketNumber] int
   ,[Date] Datetime
   ,[TotalAmount] money
   ,[TransactionDocument_Id] int
   ,[Amount] money
   ,[Amount_USD] money
   ,[Name] varchar(255)
    )
    
declare @tbl_Sales_Tx table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[Date] Datetime
   ,[Ticket] int
   ,[T1] money
   ,[T2] money
   ,[T3] money
   ,[Tax] money
   ,[Amount] money
   ,[Tip] money
   ,[GCsale] money
   ,[TotalAmount] money
    )

declare @tbl_Tx_Z table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[Date] Datetime
   ,[Ticket] int
   ,[T1] money
   ,[T2] money
   ,[T3] money
   ,[Tax] money
   ,[Amount] money
   ,[TotalAmount] money
    )

declare @tbl_ZED table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[WPID] int
   ,[F1] varchar(255)
   ,[F2] varchar(255)
    )
    

/******************************************************************************************/
/************* CREATE REPORT **************************************************************/
/******************************************************************************************/

set @var_WorkPeriod_ID = right('0000000'+convert(varchar(7),@int_WorkPeriod_ID),7)
set @var_WorkPeriod_Beg = convert(varchar(19),@dat_WorkPeriod_Beg, 120)
set @var_WorkPeriod_End = convert(varchar(19),@dat_WorkPeriod_End, 120)

print @int_WorkPeriod_ID
print @dat_WorkPeriod_Beg
print @dat_WorkPeriod_End
print @dat_WorkPeriod_End_Check



/*****************************************/
/************* Transactions **************/
/*****************************************/

INSERT INTO @tbl_Transactions
SELECT
   tkt.[TicketNumber]
  ,tkt.[Date]
  ,tkt.[TotalAmount]
  ,tkt.[TransactionDocument_Id]

  ,tx.[Amount]
  ,tx.[Amount]/@dec_XR
  ,tx.[Name]
FROM [dbo].[Tickets] tkt
    left join [dbo].[AccountTransactions] tx
    on tkt.TransactionDocument_Id = tx.AccountTransactionDocumentId
WHERE tkt.[Date] >= @dat_WorkPeriod_Beg AND tkt.[Date] <= @dat_WorkPeriod_End

SELECT
    @int_Ticket_Beg = min([TicketNumber])
   ,@int_Ticket_End = max([TicketNumber])
FROM @tbl_Transactions

set @var_Ticket_Beg = right('0000000'+convert(varchar(7),@int_Ticket_Beg),7)
set @var_Ticket_End = right('0000000'+convert(varchar(7),@int_Ticket_End),7)

print @int_Ticket_Beg
print @int_Ticket_End


/*****************************************/
/******************* Tx ******************/
/*****************************************/

INSERT INTO @tbl_Sales_Tx
SELECT DISTINCT
    [Date] --[Date] Datetime
   ,[TicketNumber] --[Ticket] int
   ,0 --[T1] money
   ,0 --[T2] money
   ,0 --[T3] money
   ,0 --[Tax] money
   ,0 --[Amount] money
   ,0 --[Tip] money
   ,0 --[GCsale] money
   ,0 --[TotalAmount] money
FROM @tbl_Transactions
WHERE coalesce([TotalAmount],0) > 0
ORDER BY [TicketNumber]

set @int_N = @int_Ticket_Beg
WHILE @int_N <= @int_Ticket_End
BEGIN
UPDATE @tbl_Sales_Tx SET
  [TotalAmount] = (SELECT CASE WHEN EXISTS (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    THEN (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    ELSE (SELECT 0)
                    END)
, [GCsale]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
                    ELSE (SELECT 0)
                    END)
, [Tip]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
                    ELSE (SELECT 0)
                    END)
, [Amount]      = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    ELSE (SELECT 0)
                    END)
, [T1]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    ELSE (SELECT 0)
                    END)
, [T2]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    ELSE (SELECT 0)
                    END)
, [T3]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    ELSE (SELECT 0)
                    END)
, [Tax]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    ELSE (SELECT 0)
                    END)
                + (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    ELSE (SELECT 0)
                    END)
                + (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    ELSE (SELECT 0)
                    END)
WHERE [Ticket] = @int_N
set @int_N = @int_N + 1
END

update @tbl_Sales_Tx set [TotalAmount] = [TotalAmount] - ([Tip] + [GCsale]) where [TotalAmount] > 0

--select * from @tbl_Transactions
--select * from @tbl_Sales_Tx

INSERT INTO @tbl_Tx_Z
SELECT
    [Date]
   ,[Ticket]
   ,[T1]
   ,[T2]
   ,[T3]
   ,[Tax]
   ,[Amount]
   ,[TotalAmount]
FROM @tbl_Sales_Tx
ORDER BY [Ticket]

--select * from @tbl_Tx_Z


set @T1_taxout = (select isnull(sum([T1]),0)/@T1*100 from @tbl_Tx_Z)
set @T1_taxout_c = right('        '+convert(varchar(10),@T1_taxout),8)
set @T2_taxout = (select isnull(sum([T2]),0)/@T2*100 from @tbl_Tx_Z)
set @T2_taxout_c = right('        '+convert(varchar(10),@T2_taxout),8)
set @T3_taxout = (select isnull(sum([T3]),0)/@T3*100 from @tbl_Tx_Z)
set @T3_taxout_c = right('        '+convert(varchar(10),@T3_taxout),8)

set @T1_TTL = (SELECT isnull(sum([T1]),0) FROM @tbl_Tx_Z)
set @T1_TTL_c = right('                   '+convert(varchar(19),@T1_TTL),19)
set @T2_TTL = (SELECT isnull(sum([T2]),0) FROM @tbl_Tx_Z)
set @T2_TTL_c = right('                   '+convert(varchar(19),@T2_TTL),19)
set @T3_TTL = (SELECT isnull(sum([T3]),0) FROM @tbl_Tx_Z)
set @T3_TTL_c = right('                   '+convert(varchar(19),@T3_TTL),19)
set @Tax_TTL = (SELECT isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Tax_TTL_c = right('                   '+convert(varchar(19),@Tax_TTL),19)

set @SalesTaxExcl_TTL = @T1_taxout + @T2_taxout + @T3_taxout
set @SalesTaxExcl_TTL_c = right('                   '+convert(varchar(19),@SalesTaxExcl_TTL),19)

set @SalesNoTax_TTL = (SELECT isnull(sum([TotalAmount]),0)-(@SalesTaxExcl_TTL + @Tax_TTL) FROM @tbl_Tx_Z)
set @SalesNoTax_TTL_c = right('                   '+convert(varchar(19),@SalesNoTax_TTL),19)

set @Sales_TTL = (SELECT isnull(sum([Amount]),0)+isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Sales_TTL_c = right('                   '+convert(varchar(19),@Sales_TTL),19)


INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '///////////////////','\\\\\\\\\\\\\\\\\\\'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '   Your Business   ',' Your Corporation  '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'Z-num:      ' + @var_WorkPeriod_ID,'REG:  #############'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_Beg,'Ticket_Beg: '+@var_Ticket_Beg
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_End,'Ticket_End: '+@var_Ticket_End
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '[------------TYPE-]','[----------AMOUNT-]'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T1['+substring(convert(varchar(5),@T1),1,4)+']('+@T1_taxout_c+'):', @T1_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T2['+substring(convert(varchar(5),@T2),1,4)+']('+@T2_taxout_c+'):', @T2_TTL_c
--T3 INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T3['+substring(convert(varchar(5),@T3),1,4)+']('+@T3_taxout_c+'):', @T3_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '         Tax Total:', @Tax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '  Sales (Tax Excl):', @SalesTaxExcl_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '  Sales (NonTaxed):', @SalesNoTax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '       Sales Total:', @Sales_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '\\\\\\\\\\\\\\\\\\\','///////////////////'

SELECT [F1],[F2] FROM @tbl_ZED ORDER BY [ID]

Using Custom Reports and the above SQL (slightly modified) you can accomplish this:

Output:

Template:

[Z Report:50, 50]
@@Zreport

SQL script:

This is slightly modified for Date selection in the Reports module so that it uses parameters for {Start} and {End}.

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** ZED REPORT                                                                         ***/
/***                                                                                    ***/
/*** see USER SECTION (after declarations) to set:                                      ***/
/***  Date for Report                                                                   ***/
/***  Tax Rates via Template Names                                                      ***/
/***  Foreign Currency Exchange (optional)                                              ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/

/*****************************************/
/************* DO NOT TOUCH **************/
/***** Declarations & Initialization *****/
/*****************************************/

--USE [SambaPOS4]

declare @int_WorkPeriod_ID int = 0
declare @var_WorkPeriod_ID varchar(7) = '0'
declare @dat_WorkPeriod_Beg Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_Beg varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_End varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End_Check Datetime = '9999-12-31 23:59:59'

declare @int_Ticket_Beg int = 0 
declare @var_Ticket_Beg varchar(7) = '0'
declare @int_Ticket_End int = 0
declare @var_Ticket_End varchar(7) = '0'
declare @int_N int = 0
declare @dec_XR money = 1
declare @T1 money = 1
declare @T2 money = 1
declare @T3 money = 1

declare @T1_TTL_c varchar(19) = ''
declare @T2_TTL_c varchar(19) = ''
declare @T3_TTL_c varchar(19) = ''
declare @Tax_TTL_c varchar(19) = ''
declare @SalesTaxExcl_TTL_c varchar(19) = ''
declare @SalesNoTax_TTL_c varchar(19) = ''
declare @Sales_TTL_c varchar(19) = ''

declare @T1_TTL money = 0
declare @T2_TTL money = 0
declare @T3_TTL money = 0
declare @Tax_TTL money = 0
declare @SalesTaxExcl_TTL money = 0
declare @SalesNoTax_TTL money = 0
declare @Sales_TTL money = 0

declare @T1_taxout money = 0
declare @T2_taxout money = 0
declare @T3_taxout money = 0

declare @T1_taxout_c varchar(10) = ''
declare @T2_taxout_c varchar(10) = ''
declare @T3_taxout_c varchar(10) = ''

declare @int_PrintZED int = 0

declare @BASEPATH varchar(100) = ''

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** USER SECTION (Begin)                                                               ***/
/*** set WorkPeriod Begin & End Dates                                                   ***/
/*** select Tax Rates via Template Names                                                ***/
/*** select Foreign Currency Exchange Rate                                              ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/

--set @BASEPATH = 'C:\D\Programs\POS\SALES\'
set @int_PrintZED = 0

-- TODAY
--set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)

-- OTHER DAY
set @dat_WorkPeriod_Beg = '{Start}'
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
set @dat_WorkPeriod_End = '{End}'
set @dat_WorkPeriod_End = dateadd(Second,1,@dat_WorkPeriod_End)

-- DATE CONSTRAINED
select @int_WorkPeriod_ID = [Id] FROM [dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End

-- LATEST CLOSED WorkPeriod
--select @int_WorkPeriod_ID = max([Id]) FROM [dbo].[WorkPeriods] WHERE [StartDate] != [EndDate]
--select @dat_WorkPeriod_Beg = [StartDate] FROM [dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID
--select @dat_WorkPeriod_End_Check = [EndDate] FROM [dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID

-- check end date: if the workperiod is still open, end date will be the same as beg date, so change end to be 9999-12-31
--select @dat_WorkPeriod_End = 
--case @dat_WorkPeriod_End_Check
--when @dat_WorkPeriod_Beg then @dat_WorkPeriod_End
--else @dat_WorkPeriod_End_Check
--end

-- ForeignCurrency ExchangeRate
--SELECT @dec_XR = [ExchangeRate] FROM [dbo].[ForeignCurrencies] WHERE [Name] = 'USD'

-- Taxes
SELECT @T1 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T1'
SELECT @T2 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T2'
SELECT @T3 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T3'

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** USER SECTION (End)                                                                 ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/







/*****************************************/
/************* DO NOT TOUCH **************/
/** Declarations for (in-memory) Tables **/
/*****************************************/

declare @tbl_Transactions table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[TicketNumber] int
   ,[Date] Datetime
   ,[TotalAmount] money
   ,[TransactionDocument_Id] int
   ,[Amount] money
   ,[Amount_USD] money
   ,[Name] varchar(255)
    )
    
declare @tbl_Sales_Tx table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[Date] Datetime
   ,[Ticket] int
   ,[T1] money
   ,[T2] money
   ,[T3] money
   ,[Tax] money
   ,[Amount] money
   ,[Tip] money
   ,[GCsale] money
   ,[TotalAmount] money
    )

declare @tbl_Tx_Z table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[Date] Datetime
   ,[Ticket] int
   ,[T1] money
   ,[T2] money
   ,[T3] money
   ,[Tax] money
   ,[Amount] money
   ,[TotalAmount] money
    )

declare @tbl_ZED table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[WPID] int
   ,[F1] varchar(255)
   ,[F2] varchar(255)
    )
    

/******************************************************************************************/
/************* CREATE REPORT **************************************************************/
/******************************************************************************************/

set @var_WorkPeriod_ID = right('0000000'+convert(varchar(7),@int_WorkPeriod_ID),7)
set @var_WorkPeriod_Beg = convert(varchar(19),@dat_WorkPeriod_Beg, 120)
set @var_WorkPeriod_End = convert(varchar(19),@dat_WorkPeriod_End, 120)

print @int_WorkPeriod_ID
print @dat_WorkPeriod_Beg
print @dat_WorkPeriod_End
print @dat_WorkPeriod_End_Check



/*****************************************/
/************* Transactions **************/
/*****************************************/

INSERT INTO @tbl_Transactions
SELECT
   tkt.[TicketNumber]
  ,tkt.[Date]
  ,tkt.[TotalAmount]
  ,tkt.[TransactionDocument_Id]

  ,tx.[Amount]
  ,tx.[Amount]/@dec_XR
  ,tx.[Name]
FROM [dbo].[Tickets] tkt
    left join [dbo].[AccountTransactions] tx
    on tkt.TransactionDocument_Id = tx.AccountTransactionDocumentId
WHERE tkt.[Date] >= @dat_WorkPeriod_Beg AND tkt.[Date] <= @dat_WorkPeriod_End

SELECT
    @int_Ticket_Beg = min([TicketNumber])
   ,@int_Ticket_End = max([TicketNumber])
FROM @tbl_Transactions

set @var_Ticket_Beg = right('0000000'+convert(varchar(7),@int_Ticket_Beg),7)
set @var_Ticket_End = right('0000000'+convert(varchar(7),@int_Ticket_End),7)

print @int_Ticket_Beg
print @int_Ticket_End


/*****************************************/
/******************* Tx ******************/
/*****************************************/

INSERT INTO @tbl_Sales_Tx
SELECT DISTINCT
    [Date] --[Date] Datetime
   ,[TicketNumber] --[Ticket] int
   ,0 --[T1] money
   ,0 --[T2] money
   ,0 --[T3] money
   ,0 --[Tax] money
   ,0 --[Amount] money
   ,0 --[Tip] money
   ,0 --[GCsale] money
   ,0 --[TotalAmount] money
FROM @tbl_Transactions
WHERE coalesce([TotalAmount],0) > 0
ORDER BY [TicketNumber]

set @int_N = @int_Ticket_Beg
WHILE @int_N <= @int_Ticket_End
BEGIN
UPDATE @tbl_Sales_Tx SET
  [TotalAmount] = (SELECT CASE WHEN EXISTS (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    THEN (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    ELSE (SELECT 0)
                    END)
, [GCsale]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
                    ELSE (SELECT 0)
                    END)
, [Tip]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
                    ELSE (SELECT 0)
                    END)
, [Amount]      = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    ELSE (SELECT 0)
                    END)
, [T1]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    ELSE (SELECT 0)
                    END)
, [T2]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    ELSE (SELECT 0)
                    END)
, [T3]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    ELSE (SELECT 0)
                    END)
, [Tax]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
                    ELSE (SELECT 0)
                    END)
                + (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
                    ELSE (SELECT 0)
                    END)
                + (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
                    ELSE (SELECT 0)
                    END)
WHERE [Ticket] = @int_N
set @int_N = @int_N + 1
END

update @tbl_Sales_Tx set [TotalAmount] = [TotalAmount] - ([Tip] + [GCsale]) where [TotalAmount] > 0

--select * from @tbl_Transactions
--select * from @tbl_Sales_Tx

INSERT INTO @tbl_Tx_Z
SELECT
    [Date]
   ,[Ticket]
   ,[T1]
   ,[T2]
   ,[T3]
   ,[Tax]
   ,[Amount]
   ,[TotalAmount]
FROM @tbl_Sales_Tx
ORDER BY [Ticket]

--select * from @tbl_Tx_Z


set @T1_taxout = (select isnull(sum([T1]),0)/@T1*100 from @tbl_Tx_Z)
set @T1_taxout_c = right('        '+convert(varchar(10),@T1_taxout),8)
set @T2_taxout = (select isnull(sum([T2]),0)/@T2*100 from @tbl_Tx_Z)
set @T2_taxout_c = right('        '+convert(varchar(10),@T2_taxout),8)
set @T3_taxout = (select isnull(sum([T3]),0)/@T3*100 from @tbl_Tx_Z)
set @T3_taxout_c = right('        '+convert(varchar(10),@T3_taxout),8)

set @T1_TTL = (SELECT isnull(sum([T1]),0) FROM @tbl_Tx_Z)
set @T1_TTL_c = right('                   '+convert(varchar(19),@T1_TTL),19)
set @T2_TTL = (SELECT isnull(sum([T2]),0) FROM @tbl_Tx_Z)
set @T2_TTL_c = right('                   '+convert(varchar(19),@T2_TTL),19)
set @T3_TTL = (SELECT isnull(sum([T3]),0) FROM @tbl_Tx_Z)
set @T3_TTL_c = right('                   '+convert(varchar(19),@T3_TTL),19)
set @Tax_TTL = (SELECT isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Tax_TTL_c = right('                   '+convert(varchar(19),@Tax_TTL),19)

set @SalesTaxExcl_TTL = @T1_taxout + @T2_taxout + @T3_taxout
set @SalesTaxExcl_TTL_c = right('                   '+convert(varchar(19),@SalesTaxExcl_TTL),19)

set @SalesNoTax_TTL = (SELECT isnull(sum([TotalAmount]),0)-(@SalesTaxExcl_TTL + @Tax_TTL) FROM @tbl_Tx_Z)
set @SalesNoTax_TTL_c = right('                   '+convert(varchar(19),@SalesNoTax_TTL),19)

set @Sales_TTL = (SELECT isnull(sum([Amount]),0)+isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Sales_TTL_c = right('                   '+convert(varchar(19),@Sales_TTL),19)


INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '///////////////////','\\\\\\\\\\\\\\\\\\\'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '   Your Business   ',' Your Corporation  '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'Z-num:      ' + @var_WorkPeriod_ID,'REG:  #############'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_Beg,'Ticket_Beg: '+@var_Ticket_Beg
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_End,'Ticket_End: '+@var_Ticket_End
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '[------------TYPE-]','[----------AMOUNT-]'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T1['+substring(convert(varchar(5),@T1),1,4)+']('+@T1_taxout_c+'):', @T1_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T2['+substring(convert(varchar(5),@T2),1,4)+']('+@T2_taxout_c+'):', @T2_TTL_c
--T3 INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T3['+substring(convert(varchar(5),@T3),1,4)+']('+@T3_taxout_c+'):', @T3_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '         Tax Total:', @Tax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '  Sales (Tax Excl):', @SalesTaxExcl_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '  Sales (NonTaxed):', @SalesNoTax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '       Sales Total:', @Sales_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '\\\\\\\\\\\\\\\\\\\','///////////////////'

SELECT [F1],[F2] FROM @tbl_ZED ORDER BY [ID]

Thank you for this Report @QMcKay
But not working on my DB. I stiil using V5 and 2 tax rate.
I change TaxTemplate Name on SQL Script User Section.

Where I do have another correction?

The SQL looks for specific descriptions (the [Name] column) in the Transaction tables, so if you have changed them in your setup then the report will not work.

For example:

[TotalAmount] = (SELECT CASE WHEN EXISTS (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    THEN (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
                    ELSE (SELECT 0)
                    END)

I could modify it to be parameterized for specific Accounts/Names, but that will take some time.

2 Likes

Thank you for helping.
It’s working fine.

2 Likes