Join a table and calculate a percentage from this new table

Question

I'm trying to make a report of financial datas for my company:

I have actually two two tables:

___BillableDatas:

|--------|------------|----------|----------|--------------|---------------------|
| BIL_Id | BIL_Date   | BIL_Type | BIL_Rate | BIL_Quantity | BIL_ApplicableTaxes |
|--------|------------|----------|----------|--------------|---------------------|
| 1      | 2017-01-01 | Night    | 95       | 1            | 1                   |
| 2      | 2017-01-02 | Night    | 95       | 1            | 1                   |
| 3      | 2017-01-15 | Night    | 105      | 1            | 1                   |
| 4      | 2017-01-15 | Item     | 8        | 2            | 1,2                 |
| 5      | 2017-02-14 | Night    | 95       | 1            | 1                   |
| 6      | 2017-02-15 | Night    | 95       | 1            | 1                   |
| 7      | 2017-02-16 | Night    | 95       | 1            | 1                   |
| 8      | 2017-03-20 | Night    | 89       | 1            | 1                   |
| 9      | 2017-03-21 | Night    | 89       | 1            | 1                   |
| 10     | 2017-03-21 | Item     | 8        | 3            | 1,2                 |
|--------|------------|----------|----------|--------------|---------------------|

___SalesTaxes:

|--------|------------|
| STX_Id | STX_Amount |
|--------|------------|
| 1      | 14.00      |
| 2      | 5.00       |
|--------|------------|

I need to know for each month the sum of my revenue with and without taxes.

Actually I can make the report but don't know how to loop into the ___SalesTaxes table.

What I have actually:

SELECT month(BIL_Date) AS month,
sum(BIL_Rate * BIL_Quantity) AS sumval
FROM `___BillableDatas`
WHERE BIL_Date BETWEEN "2017-01-01" AND "2017-12-31"
AND BIL_Type = "Night" OR BIL_Type = "Item"
GROUP BY year(BIL_Date), month(BIL_Date)

Thanks for your help.

Answer 1

This will work, I've created following example will help you lot for debugging and implementation. try to implement as below :

If(OBJECT_ID('tempdb..#___BillableDatas') Is Not Null)
Begin
    Drop Table #___BillableDatas
End
If(OBJECT_ID('tempdb..#___SalesTaxes') Is Not Null)
Begin
    Drop Table #___SalesTaxes
End

CREATE TABLE #___BillableDatas
(
    BIL_Id INT IDENTITY (1,1),
    BIL_Date DATETIME,
    BIL_Type VARCHAR(50),
    BIL_Rate FLOAT,
    BIL_Quantity INT,
    BIL_ApplicableTaxes VARCHAR(10)
);

INSERT INTO #___BillableDatas (BIL_Date,BIL_Type,BIL_Rate,BIL_Quantity,BIL_ApplicableTaxes) 
VALUES  ('2017-01-01','Night',95,1,'1'),
        ('2017-01-02','Night',95,1,'1'),
        ('2017-01-15','Night',105,1,'1'),
        ('2017-01-15','Item',8,2,'1,2'),
        ('2017-02-14','Night',95,1,'1'),
        ('2017-02-15','Night',95,1,'1'),
        ('2017-02-16','Night',95,1,'1'),
        ('2017-03-20','Night',89,1,'1'),
        ('2017-03-21','Night',89,1,'1'),
        ('2017-03-21','Item',8,1,'1,2')

CREATE TABLE #___SalesTaxes
(
    STX_Id INT IDENTITY (1,1),
    STX_Amount FLOAT
);
INSERT INTO #___SalesTaxes (STX_Amount) VALUES (14.00),(5.00)

-----------------------------------------------------------------

SELECT * FROM #___BillableDatas
SELECT * FROM #___SalesTaxes


SELECT MONTH(BD.BIL_Date) AS [Month],SUM(BD.BIL_Rate * BD.BIL_Quantity) AS 'Without Tax'
,(SUM(BD.BIL_Rate * BD.BIL_Quantity)+((SUM(BD.BIL_Rate * BD.BIL_Quantity)/100)*BD.Tax1)) AS 'With Tax 1'
,(SUM(BD.BIL_Rate * BD.BIL_Quantity)+((SUM(BD.BIL_Rate * BD.BIL_Quantity)/100)*BD.Tax2)) AS 'With Tax 2'
FROM 
(
SELECT *,
(SELECT ST1.STX_Amount FROM Func_Split(BIL_ApplicableTaxes,',') AS F LEFT JOIN #___SalesTaxes AS ST1 ON F.Element=ST1.STX_Id WHERE F.Element='1') AS Tax1 ,
(SELECT ST1.STX_Amount FROM Func_Split(BIL_ApplicableTaxes,',') AS F LEFT JOIN #___SalesTaxes AS ST1 ON F.Element=ST1.STX_Id WHERE F.Element='2') AS Tax2
FROM #___BillableDatas) AS BD
WHERE BD.BIL_Date BETWEEN '2017-01-01' AND '2017-12-31' AND BD.BIL_Type = 'Night' OR BD.BIL_Type = 'Item'
GROUP BY YEAR(BD.BIL_Date), MONTH(BD.BIL_Date),BD.Tax1,BD.Tax2

You will require function Func_Split for above solution, use this :

CREATE FUNCTION [dbo].[func_Split] 
    (   
    @DelimitedString    varchar(8000),
    @Delimiter              varchar(100) 
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = LEN(@Delimiter)

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(@Delimiter, @DelimitedString)

        IF @Index = 0
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END

Answer 2

as kbball mentioned you have an unresolved many to many relationship in your main table. A proper table should never be designed to have more than one value per field. Resolving many to many relationships is quite simple. You will need to create a new table something like bill_taxType or some relation like that. The new table would have two fields as well as the standard primary key, it will have bill_id and applicable tax id. In the case of your 1,2 fields like bill id 4 in the new table it will look like

primary key,  bill id,  applicable tax id
1             4         1
2             4         2

In your final query you will join all three together on the appropriate primary key-foreign key relationship. This final query should have the data that you need.