query syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query syntax

Author

Topic

tips54
Starting Member

5 Posts

Posted - 2011-03-25 : 12:59:52

I have a couple tables that has the following data and I would like to query by Date. One table has Invoice data (CustID, Invoice#, TotalAmt, Net-Due-Date). The other table has % data (CustID, %Paid7DaysEarlyOrMore, %PaidOnTime, %Paid7DaysLateOrMore).

Percent table:
CustID, %Paid7DaysEarlyOrMore %PaidOnTime %Paid7DaysLateOrMore).
101 15% 0% 85%

Invoice table Data:
CustID Invoice# Total-Amt Net-Due-Date
101 2 100 12-25-10
101 3 107 2-25-11
101 4 120 3-25-11
101 5 500 4-12-11
101 6 500 4-25-11
I would like extract each invoice and apply the percentage to each one. But if the Net-Due-Date compared to current date has past I want to apply future percentage and show the data in that column, in other words, if the Net-due-date is 12-25-10 I would like to see that 100 in the On time column. If it's past due role it roll it forward.

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-03-25 : 14:02:44

Try this ...

Select
CASE
WHEN InvoiceTable.Net-Due-Date<GetDate() THEN
PercentTable.PaidOnTime
WHEN InvoiceTable.Net-Due-Date>GetDate() THEN
PercentTable.Paid7DaysEarlyOrMore
WHEN InvoiceTable.Net-Due-Date=GetDate() THEN
PercentTable.Paid7DaysLateOrMore ELSE ''END as YourColumnName
From PercentTable
Inner join InvoiceTable ON PercentTable.CustId=InvoiceTable.CustID

Please change Case Statements as per your requirements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources