Calculate date in SQL Query

Question

I am trying to calculate a date within an SQL query which is the date_required add the number of days in despatchdate - I am currently getting the error Invalid column name 'despatchdays'. If the despatchdays is null or "" then I want it to default to -14.

SELECT order_no, order_line_no, currency, product, address1,description, date_required, 
(SELECT despatchdays 
 FROM scheme.PDBtblSuppliers 
 WHERE SupplierIDFK = dbo.qryArrears2.supplier) AS despatchdays,
DATEADD(d, despatchdays, date_required) AS despatchdate
FROM  dbo.qryArrears2

How can I optimise this to get it working ?

I am using SQL 2000.

Answer 1

You can use:

SELECT order_no
    , order_line_no
    , currency
    , product
    , address1
    ,description
    , date_required 
    , DATEADD(day, coalesce(pd.despatchdays, -14), date_required) AS despatchdate
FROM  dbo.qryArrears2 qa
INNER JOIN scheme.PDBtblSuppliers pd On pd.SupplierIDFK = qa.supplier

But it may give you more rows if there are several rows in PDBtblSuppliers for a supplier in qryArrears2.

You can also move despatchdate query inside the DATEADD:

SELECT * FROM (    
    SELECT order_no
    , order_line_no
        , currency
        , product
        , address1
        , description
        , date_required
        , DATEADD(day
            ,  coalesce((SELECT despatchdays FROM scheme.PDBtblSuppliers WHERE SupplierIDFK = qa.supplier), -14)
            , date_required
        ) AS despatchdate
    FROM  dbo.qryArrears2 qa
) as d 
WHERE despatchdate = '20150101'

coalesce(despatchdays, -14) will replace despatchdays by -14 if despatchdays is NULL.

If date_required is a (var)char, you should replace it by a date in your table or cast is to data(time): CAST(date_required as date)