Yesterday SQL Server

I know very little SQL and have been asked to fix a problem in existing code. The code is PHP but the SQL causing the problem is:

$sql = "INSERT INTO Intranet.dbo.DailyBilling (Date, JobCode, SubJob, TotalTTC, TotalATTC, CompletedBillableHours, WIP, CurrencyCode, ContractValue, Invoiced, BillableTotal, BillableToday)
            SELECT
                Date = CONVERT(VARCHAR(10), dateadd(day,-1, getdate()), 111),
                JobCode,
                vwJobValueVsInvoiced.SubJob,
                TotalTTC,
                TotalATTC = ATTC,
                CompletedBillableHours,
                WIP,
                vwJobValueVsInvoiced.CurrencyCode,
                ContractValue,
                vwJobValueVsInvoiced.Invoiced,
                BillableTotal =  IIF(TotalTTC <> 0,((CompletedBillableHours/TotalTTC)*ContractValue), 0),
                BillableToday = IIF(TotalTTC <> 0,
                                     IIF(Yesterday.InvoicedYesterday <> vwJobValueVsInvoiced.Invoiced,
                                                      ((CompletedBillableHours/TotalTTC)*ContractValue)-vwJobValueVsInvoiced.Invoiced ,
                                                     (((CompletedBillableHours/TotalTTC)*ContractValue)-vwJobValueVsInvoiced.Invoiced)- (Yesterday.BillableTotal-Yesterday.InvoicedYesterday)), 
                                  0)
            FROM
                Intranet.dbo.vwJobValueVsInvoiced
            LEFT JOIN
                Intranet.dbo.vwCurrentRate ON vwJobValueVsInvoiced.CurrencyCode = vwCurrentRate.CurrencyCode
            LEFT JOIN
                (SELECT
                    SubJob,
                    BillableTotal,
                    BillableToday,
                    InvoicedYesterday = Invoiced
                FROM
                    Intranet.dbo.DailyBilling
                WHERE 
                    Date =  CONVERT(VARCHAR(10), dateadd(day,-2, getdate()), 111)) Yesterday ON vwJobValueVsInvoiced.SubJob = Yesterday.SubJob
            WHERE 
                vwJobValueVsInvoiced.Status <> 'Complete' AND IIF(TotalTTC <> 0,((CompletedBillableHours/TotalTTC)*ContractValue)-vwJobValueVsInvoiced.Invoiced, 0) <> 0";

The tables look like this:

dbo.dailybilling

vwJobValueVSInvoice

vwCurrencyRate

SQL returns this error:

Array ( [0] => Array ( [0] => 42S22 [SQLSTATE] => 42S22 [1] => 207 [code] => 207 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'WIP'. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'WIP'. ) ) 

In working through it the first question I have is What is Yesterday.InvoicedYesterday? It isn’t a table I can find and is not in the outer PHP.

Second questions, why the problem with WIP? It appears in order.

Assistance much appreciated.

Answer

Yesterday is an alias given to a subquery, and InvoicedYesterday is an alias given to a column in that subquery.

LEFT JOIN (
    SELECT
        SubJob
      , BillableTotal
      , BillableToday
      ,     InvoicedYesterday    = Invoiced
    FROM Intranet.dbo.DailyBilling
    WHERE Date = CONVERT(varchar(10), DATEADD(DAY, -2, GETDATE()), 111)
    )   Yesterday    ON vwJobValueVsInvoiced.SubJob = Yesterday.SubJob

All results of that subquery in the larger query will now be referenced through that alias, so all the columns of the subquery become

Yesterday.SubJob
Yesterday.BillableTotal
Yesterday.BillableToday
Yesterday.InvoicedYesterday

If you trace Yesterday.InvoicedYesterday backwards through that query it is sourced from the table Intranet.dbo.DailyBilling and from the column [Invoiced] in that table, but the subquery is filteing data using WHERE Date = CONVERT(varchar(10), DATEADD(DAY, -2, GETDATE()), 111)

If you run this

select CONVERT(varchar(10), DATEADD(DAY, -2, GETDATE()), 111)

you will see that this is “2 days ago” or “the day before yesterday” e.g. if today is 2018-10-27 the query returns ‘2018/10/25’ (as a string, literally in that format)

---

I generally would not recommend use of date style 111 in your query (it is used twice). Instead I would recommend using style number 112 instead (this has no delimiter and is the safest format to use).