mardi 21 février 2017

SQL - If Statement Inside DateAdd

I am extremely new to SQL but really enjoy it and want to get better at it.

In my code, I am taking 3 tables and merging them together then creating a few columns for that merge. I have created a column "DaysOfStock" and want to add on the number in each column to the current date. The problem is the the "DaysOfStock" is an "if" function and I am not sure how to add it into the "DayAdd" function. The section of my code I am having trouble w/ is below.

SELECT Cust.Article,
    IsNull(stock.StockQuantity,0) StockQuantity,
    IsNull(orders.OpenQty,0) OpenOrders,
    IsNull(usage.[3MonUsage], 0) [3MonUsage], IsNull(usage.EAU, 0) [EAU],
    ROUND(IsNull(usage.EAU, 0)/366,0) DailyUsage,
    CASE WHEN IsNull(usage.EAU, 0)/366 = 0 THEN ROUND(IsNull(stock.StockQuantity,0)/(IsNull(orders.OpenQty, 0)/366),2)
    ELSE ROUND(IsNull(stock.StockQuantity,0)/(IsNull(usage.EAU, 0)/366),2) END AS DaysOfStock
    DateAdd(day, **DaysOfStock**, GetDate())
FROM TeslaArts 
        LEFT OUTER JOIN usage ON CustArts.Article = usage.Article
        LEFT OUTER JOIN stock ON CustArts.Article = stock.Article
        LEFT OUTER JOIN orders ON CustArts.Article = orders.Article
WHERE (orders.OpenQty > 0 AND usage.[3MonUsage] > 0)
    OR (orders.OpenQty = 0 AND usage.[3MonUsage] > 0)
    OR (orders.OpenQty > 0 AND usage.[3MonUsage] = 0)

Please let me know if something is unclear. Thanks for your help in advance. -alex

Aucun commentaire:

Enregistrer un commentaire