SQL Views Syntax any simple way

[nector]

Maybe I'm not getting the SQL Server operation properly , is there a simple way to write an SQL server View formula other than typing paragraphs of data?

SELECT
  tblEmployees.EmpID,
  tblEmployees.Fname,
  tblEmployees.Lname,
  tblEmployees.StaffNumber,
  tblEmployees.JobTitle,
  tblEmployees.SocialSecurity,
  tblEmployees.NRC,
  tblEmployees.PayMethod,
  tblEmployees.BankName,
  tblEmployees.AccountCode,
  tblEmployees.PaymentType,
  tblPayslip.BasicSalary,
  tblPayslip.HousingAllowance,
  tblPayslip.TransportAllowance,
  tblPayslip.LunchAllowance,
  tblOtherSalary.TTDate,
  tblOtherSalary.XMasBonus,
  tblOtherSalary.OtherBonuses,
  tblOtherSalary.Gratuity,
  tblOtherSalary.LeavePay,
  tblOtherSalary.OtherArears,
  tblOtherSalary.Overtime,
  tblOtherSalary.DoubleTime,
  tblOtherSalary.OtherIncentive,
  tblOtherSalary.StaffLoans,
  tblOtherSalary.OtherDeductions,
  tblOtherSalary.Advances,
  tblNAPSA.Rate,
  tblNAPSA.CelingRate,
  tblNAPSA.NAPSAFree,
  tblTaxation.FreeTax,
  tblTaxation.BandOne,
  tblTaxation.BandTwo,
  tblTaxation.TaxRateone,
  tblTaxation.TaxRateTwo,
  tblTaxation.TaxRateThree,
  tblTaxation.EducationLevy,
  COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((XMasBonus), 0) + COALESCE((OtherBonuses), 0) + COALESCE((([Gratuity] + [PriorGratuity])), 0) + COALESCE((LeavePay), 0) + COALESCE((OtherArears), 0) +(
    COALESCE((Overtime), 0) *(
      (
        (
          COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
        ) / 208
      ) * 1.5
    )
  ) +(
    COALESCE((DoubleTime), 0) *(
      (
        (
          COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
        ) / 208
      ) * 2
    )
  ) + COALESCE((OtherIncentive), 0) + COALESCE((Covid19Allowance), 0) AS Gross,
  IIf(
    (
      (
        COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((XMasBonus), 0) + COALESCE((Covid19Allowance), 0) + COALESCE((OtherBonuses), 0) + COALESCE((Gratuity), 0) + COALESCE((LeavePay), 0) + COALESCE((OtherArears), 0) + COALESCE((Overtime), 0) + COALESCE((DoubleTime), 0) + COALESCE((OtherIncentive), 0)
      ) *(Rate)
    ) > NAPSAFree,
    NAPSAFree,(
      (
        COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((XMasBonus), 0) + COALESCE((Covid19Allowance), 0) + COALESCE((OtherBonuses), 0) + COALESCE((Gratuity), 0) + COALESCE((LeavePay), 0) + COALESCE((OtherArears), 0) + COALESCE((Overtime), 0) + COALESCE((DoubleTime), 0) + COALESCE((OtherIncentive), 0)
      ) *(Rate)
    )
  ) AS Pension,
  (
    (
      COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((XMasBonus), 0) + COALESCE((OtherBonuses), 0) + COALESCE((Gratuity), 0) + COALESCE((LeavePay), 0) + COALESCE((OtherArears), 0) +(
        COALESCE((Overtime), 0) *(
          (
            (
              COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
            ) / 208
          ) * 1.5
        )
      ) +(
        COALESCE((DoubleTime), 0) *(
          (
            (
              COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
            ) / 208
          ) * 2
        )
      ) + COALESCE((OtherIncentive), 0)
    ) - COALESCE(Gratuity, 0)
  ) AS Taxable,
  IIf(
    DateDiff(YEAR, DateofBirth, GetDate()) >= 55,
    0,(
      IIf(
        (
          (
            COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((XMasBonus), 0) + COALESCE((OtherBonuses), 0) + COALESCE((([Gratuity] + [PriorGratuity])), 0) + COALESCE((Covid19Allowance), 0) + COALESCE((LeavePay), 0) + COALESCE((OtherArears), 0) +(
              COALESCE((Overtime), 0) *(
                (
                  (
                    COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
                  ) / 208
                ) * 1.5
              )
            ) +(
              COALESCE((DoubleTime), 0) *(
                (
                  (
                    COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
                  ) / 208
                ) * 2
              )
            ) + COALESCE((OtherIncentive), 0)
          ) *(Rate)
        ) > CelingRate,
        CelingRate,(
          (
            COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((XMasBonus), 0) + COALESCE((OtherBonuses), 0) + COALESCE((([Gratuity] + [PriorGratuity])), 0) + COALESCE((Covid19Allowance), 0) + COALESCE((LeavePay), 0) + COALESCE((OtherArears), 0) +(
              COALESCE((Overtime), 0) *(
                (
                  (
                    COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
                  ) / 208
                ) * 1.5
              )
            ) +(
              COALESCE((DoubleTime), 0) *(
                (
                  (
                    COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
                  ) / 208
                ) * 2
              )
            ) + COALESCE((OtherIncentive), 0)
          ) *(Rate)
        )
      )
    )
  ) AS HHPension,
  tblPayslip.Closures,
  (
    COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((XMasBonus), 0) + COALESCE((OtherBonuses), 0) + COALESCE((([Gratuity] + [PriorGratuity])), 0) + COALESCE((LeavePay), 0) + COALESCE((OtherArears), 0) +(
      COALESCE((Overtime), 0) *(
        (
          (
            COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
          ) / 208
        ) * 1.5
      )
    ) +(
      COALESCE((DoubleTime), 0) *(
        (
          (
            COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
          ) / 208
        ) * 2
      )
    ) + COALESCE((OtherIncentive), 0) + COALESCE((Covid19Allowance), 0)
  ) AS FFGross,
  tblCostCentre.CostID,
  tblCostCentre.CostName,
  tblOtherSalary.PersonalLevy,
  tblOtherSalary.UniDues,
  tblOtherSalary.NHI,
  tblOtherSalary.Absentism,
  (
    COALESCE((Overtime), 0) *(
      (
        (
          COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
        ) / 208
      ) * 1.5
    )
  ) AS OTTIME,
  (
    COALESCE((DoubleTime), 0) *(
      (
        (
          COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)
        ) / 208
      ) * 2
    )
  ) AS DTTIME,
  tblEmployees.GratuityRate,
  tblOtherSalary.LeaveDays,
  tblOtherSalary.LeaveTaken,
  COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0) AS GrossBasic,
  DateDiff(YEAR, DateofBirth, GetDate()) AS NAPSAYEAR,
  tblOtherSalary.PriorGratuity,
  (Gratuity + PriorGratuity) AS FinGratuity,
  tblEmployees.NHIAccount,
  tblPayslip.Covid19Allowance,
  tblOtherSalary.Loans
FROM
  (
    (
      tblCostCentre
      INNER JOIN tblEmployees ON tblCostCentre.CostID = tblEmployees.CostID
    )
    INNER JOIN (
      tblNAPSA
      INNER JOIN (
        tblTaxation
        INNER JOIN tblPayslip ON tblTaxation.TaxID = tblPayslip.TaxID
      ) ON tblNAPSA.NAPSAID = tblPayslip.NAPSAID
    ) ON tblEmployees.EmpID = tblPayslip.EmpID
  )
  INNER JOIN tblOtherSalary ON tblPayslip.IDPAY = tblOtherSalary.IDPAY

I'm afraid those who will come after me, may find it harder to maintain this code, the code above deals with payroll cumulative tax (PAYE) calculation even if they may be Chartered Certified Accountants as well. The above formula works excellently and very fast in returning results.

 

[Minty]

Short answer No.

Longer answer - where you use a calculation repeatedly do it once in a sub-query then you can refer to that in the outer query.

So you use

COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0)

Very frequently in your calculations, you could create the GrossBasic in an initial query, then refer to it as simply GrossBasic in the outer query. SQL handles this very efficiently.

SELECT
q1.AllYourFields 

--- Now do your sums
,
 (
    COALESCE((Overtime), 0) *(
      (
        ( GrossBasic )
        ) / 208
      ) * 1.5
    )
  ) AS OTTIME

FROM
(

     SELECT *
     , COALESCE((BasicSalary), 0) + COALESCE((HousingAllowance), 0) + COALESCE((TransportAllowance), 0) + COALESCE((LunchAllowance), 0) + COALESCE((Covid19Allowance), 0) AS GrossBasic


 
    FROM
  (
    (
      tblCostCentre
      INNER JOIN tblEmployees ON tblCostCentre.CostID = tblEmployees.CostID
    )
    INNER JOIN (
      tblNAPSA
      INNER JOIN (
        tblTaxation
        INNER JOIN tblPayslip ON tblTaxation.TaxID = tblPayslip.TaxID
      ) ON tblNAPSA.NAPSAID = tblPayslip.NAPSAID
    ) ON tblEmployees.EmpID = tblPayslip.EmpID
  )
  INNER JOIN tblOtherSalary ON tblPayslip.IDPAY = tblOtherSalary.IDPAY
) as Q1

 

[Minty]

As a further typing shortcut - if you need to generate a full field list without all the typing to case a new view around, simply use the select top 1000 , and it will produce a complete list of all fields you can copy and paste.

 

[Minty]

A third suggestion - you seem to have imported Access habit of going bracket mad.
This has a ton of unnecessary bracketing that makes it very unreadable.

 (
    COALESCE((Overtime), 0) *(
      (
        ( GrossBasic )
        ) / 208
      ) * 1.5
    )
  ) AS OTTIME

Could simply be

COALESCE(Overtime, 0) * (GrossBasic / 208  ) * 1.5  AS OTTIME

 

[nector]

Many thanks Minty I will work on that.

 

[Galaxiom]

FROM ( ( tblCostCentre INNER JOIN tblEmployees ON tblCostCentre.CostID = tblEmployees.CostID ) INNER JOIN ( tblNAPSA INNER JOIN ( tblTaxation INNER JOIN tblPayslip ON tblTaxation.TaxID = tblPayslip.TaxID ) ON tblNAPSA.NAPSAID = tblPayslip.NAPSAID ) ON tblEmployees.EmpID = tblPayslip.EmpID )

(Sorry the format got trashed.)

That nested join structure is Access syntax.
Nesting joins is completely unnecessary in T-SQL joins. All that matters is that the table or subquery it is joined to is listed prior to the join statement. This is one of the most important advantages of T-SQL over Access and it will vastly simplify the layout of your query.

There are a lot of unnecessary brackets around your column names. (Access styling again.)

I prefer the commas before the column name rather than after. They are much easier to see there.

Avoid long lines. Wrap those COALESCE before the addition operators and step them in a tab stop.

I much prefer CASE over nested IIF.
I've never actually used an IIF in T-SQL at all.

I recommend rather than this layout:

INNER JOIN tblEmployees ON tblCostCentre.CostID = tblEmployees.CostID

use this:

INNER JOIN 
      tblEmployees 
      ON tblCostCentre.CostID = tblEmployees.CostID

 

[Minty]

I much prefer CASE over nested IIF.
I've never actually used an IIF in T-SQL at all.

I didn't even know IIF worked in T-SQL until about a month ago... #Blush#
I too always use CASE it's just a better layout, doesn't require most of the horrendous bracketed nesting, and much easier to see what is happening.

 

[GPGeorge]

I almost hesitate to point out that a properly normalized table design would also go a looooong way towards reducing the need for all those Coalesce() functions....

 

[Minty]

If I have that problem in any system I'm taking over, the first thing I do is either update the tables to remove the null problem, or If I can't do that create a "helper view" on the table that replaces the table without the null values, and keeps the same field names for processing.

Then use that for any of the heavy lifting.

 

[sonic8]

As a further typing shortcut - if you need to generate a full field list without all the typing to case a new view around, simply use the select top 1000 , and it will produce a complete list of all fields you can copy and paste.

You can also expand the table node in the tree and then drag the Columns folder into the query to get a complete, comma separated list of the columns.

 

[Minty]

@sonic8 I always forget that one. I don't know why.
Old age or lack of beer I suspect