sql server - Having serious issues with functions, dates, CASE and IIF statements in SQL -

i have been working on these queries , some, cannot seem these 3 quite right. not trying people work me, help/tips extremely appreciated.

this query supposed return 4 columns: vendorname, invoicetotal, invoicedate , invoiceage (use appropriate function return number of days between invoicedate , 12/1/2008). filter results return rows there balance due , invoiceage greater 132. sort results vendorname. no errors, not quite right.

 select vendorname, invoicetotal, invoicedate, datediff(day, invoicedate, '12/1/2008') invoiceage vendors join invoices on vendors.vendorid = invoices.vendorid invoicetotal - paymenttotal - credittotal > 0  , invoicedate > 132 order vendorname;-- not showing invoices on 132 

this query 1 has been giving me trouble; should return 4 columns: vendorname, invoicenumber, invoicetotal, , potentialdiscount. potentialdiscount column contain result expression case statement contains 4 conditionals based on invoicetotal column:

    select vendorname, invoicenumber, invoicetotal, potentialdiscount = case invoicetotal when invoicetotal < 100 'no discount consideration' when invoicetotal 101-500 'discount potential 3' when invoicetotal > 501-1000 'discount potential 2' when invoicetotal > 1000 'discount potential 1' end potentialdiscount vendors join invoices on vendors.vendorid = invoices.vendorid order invoicetotal; 

however, error:

msg 102, level 15, state 1, line 3 incorrect syntax near '<'.

and lastly, 1 should return 3 columns: vendorname, balancedue: balance due calculated column using sum function, debtlevel: nested iif functions, filter results include vendors balance due , sort results sum of largest balance smallest.

    select vendorname, sum(invoicetotal - paymenttotal - credittotal) balancedue, iif(sum(invoicetotal) > 11000, 'very high',  iif(sum(invoicetotal) between 11000 , 500, 'high', iif(sum(invoicetotal) between 500 , 200, 'medium', iif(sum(invoicetotal) <= 500, 'low') debtlevel vendors join invoices on vendors.vendorid = invoices.invoiceid invoicetotal > 0 order vendorname desc; 

but error:

msg 102, level 15, state 1, line 6 incorrect syntax near ')'.

even slightest assistance extremely helpful.... thank you.

query 1

seemingly comparing invoicedate 132, whereas need invoiceage. noting can't use newly created alias directly in where predicate of same query, either need repeat whole datediff expression in clause:

and datediff(day, '2008-01-12', invoicedate) > 132 

you save repetition wrapping derived table or cte, e.g.

with invoicecte (    select vendorname, invoicetotal, invoicedate,           datediff(day, '2008-01-12', invoicedate) invoiceage,           invoicetotal - paymenttotal - credittotal invoicenett    vendors join invoices         on vendors.vendorid = invoices.vendorid ) select * invoicecte invoicenett > 0        , invoiceage > 132 order vendorname; 

also noting datediff datediff ( datepart , startdate , enddate ), , advise use iso 8601 style date formatting prevent confusion on local date formats).

query 2

as others have mentioned, can't use case x when y z syntax ranges of data. you'll need use case when condition then format instead. also, noting cases match on first, can eliminate gaps in ranges simplifying to:

select vendorname, invoicenumber, invoicetotal,   case     when invoicetotal < 100 'no discount consideration'     when invoicetotal < 500 'discount potential 3'     when invoicetotal < 1000 'discount potential 2'     else 'discount potential 1'   end potentialdiscount vendors join invoices   on vendors.vendorid = invoices.vendorid order invoicetotal; 

query 3

like first query, you'll able prevent repeated sum(invoicetotal) cte or derived table, , second query, nested iif's need check lower bound, since upper bound have been handled previous nesting.

you'll want fix join condition on vendors.vendorid = invoices.vendorid. i'm assuming 'low' fallback classification, otherwise you'll provide additional value last iif if low criterion isn't met:

with mycte (     select          vendorname, sum(invoicetotal - paymenttotal - credittotal) balancedue,         sum(invoicetotal) suminvoicetotal     vendors        join invoices       on vendors.vendorid = invoices.vendorid     invoicetotal > 0     group vendorname ) select vendorname, balancedue, suminvoicetotal,     iif(suminvoicetotal > 11000, 'very high',          iif(suminvoicetotal > 500, 'high',             iif(suminvoicetotal > 200, 'medium',                 'low'))) debtlevel mycte     order vendorname desc;