Date Parameters - 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)

Date Parameters

Author

Topic

mholman
Starting Member

8 Posts

Posted - 2009-07-31 : 16:37:39

Hi I'm trying to run a SQL statement that will select overdue payments from the tables based on their due date and the current date. I also want to split it into multiple columns (i.e. 1-3 months late, 3-6, 6+).

Since The due date is not a specific date and is instead a "due month." I have tried to do something like this:
SELECT * FROM invoices WHERE YEAR(invoices.date_due)<=YEAR(GETDATE()) AND (MONTH(invoices.date_due)+MONTH(3))<MONTH(GETDATE())

The problem occurs when I am adding months. adding MONTH(3) doesn't seem right, and doesn't produce good results. Adding 3 also doesn't produce correct results.

What can I do to fix this statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-31 : 17:02:47

See DATEDIFF statement.

DATEDIFF(MONTH, {any date previous month}, GETDATE()) = 1
DATEDIFF(MONTH, {any date this month}, GETDATE()) = 0
DATEDIFF(MONTH, {any date next month}, GETDATE()) = -1

N 56°04'39.26"
E 12°55'05.63"

mholman
Starting Member

8 Posts

Posted - 2009-08-03 : 09:42:33

Thank you, that really helped move me along.
I'm having a hard time joining them together because all of the queries are from the same table. Can anyone advise how to revise my query so it will work for what I want? (i want 1-3 months late as 1 column, 3-6 as another and 6+ as a last)
Anyway, this is what my statement is looking like right now, just trying to get something so work with but its failing miserably.
SELECT fund_id, SUM(total_due)
FROM invoices inv1
WHERE (DATEDIFF(MONTH, date_due, GETDATE()) => 1) AND (DATEDIFF(MONTH, date_due, GETDATE()) <= 3)
FULL OUTER JOIN invoices inv2 ON inv2.id=inv1.id WHERE (DATEDIFF(MONTH, date_due, GETDATE()) > 3)
GROUP BY fund_id

Also I have realized that my sum will not work correctly either because there are active and inactive invoices. how do i do the sum of only total_due where invoices.active='true'? I tried putting a where in, but it doesn't seem to effect the sum.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 11:04:27

[code]SELECT inv1.fund_id,
SUM(CASE WHEN inv1.Active = 'True' THEN inv1.total_due ELSE 0 END)
FROM invoices AS inv1
WHERE DATEDIFF(MONTH, inv1.date_due, GETDATE()) BETWEEN 1 AND 3
FULL JOIN invoices AS inv2 ON inv2.id = inv1.id
AND DATEDIFF(MONTH, inv2.date_due, GETDATE()) > 3
GROUP BY inv1.fund_id[/code]

N 56°04'39.26"
E 12°55'05.63"

mholman
Starting Member

8 Posts

Posted - 2009-08-03 : 12:01:53

Thank you very much for your reply. I have one more question when I use this technique on another query (I apologize, I'm just getting used to working heavily with SQL). When I add more payments, the summation of the transactions becomes incorrect and I suspect it is the other way too. The if there is 1 transaction and 1 payment it is right if there are 2 payments and still 1 transaction, it doubles, with 3 triples, etc.
I think it must have to do with non unique columns being summed but I'm not sure how to fix it. My current statement looks as follows:
SELECT invoices.paid, invoices.inv_id, funds.name, invoices.date_due, invoices.total_due,
SUM(CASE WHEN transactions.active = 'True' THEN transactions.amount ELSE 0 END) As total_invoice,
SUM(CASE WHEN payments.active = 'True' THEN payments.pay_amount ELSE 0 END) As paid_amount
FROM invoices
LEFT JOIN transactions ON invoices.inv_id=transactions.inv_id
LEFT JOIN payments ON invoices.inv_id=payments.inv_id
LEFT JOIN funds ON invoices.fund_id=funds.fund_id
GROUP BY invoices.inv_id,invoices.date_due,invoices.total_due, funds.name, invoices.paid
ORDER BY invoices.inv_id DESC

So I was thinking I would put something like transactions.trans_id != (SELECT TOP 1 transactions.trans_id from transactions ORDER BY trans_id DESC) in the case but I cannot put a query in the statement.

I just realized this would not work anyway (would never return the last transaction, duh!) and would be better if I could use DISTINCT or something similar in the case, still looking though

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-03 : 13:40:39

Can you provide some sample data and the output that you expect.
Based on your description, it could be possible we are missing a condition in the JOINs, but if we have sample data and expected output, it would help.

mholman
Starting Member

8 Posts

Posted - 2009-08-03 : 14:07:09

Sure This is something I will get now.
The column names are very ambiguous, as the direction of the project changed a few times. Paid represents whether the invoice is paid, inv_id represents the invoice, name is the name of the client, date due is when the invoice is due, (heres where it gets confusing) total_due is the amount total price of the invoice minus the amount paid, essentially the amount outstanding. The total_invoice is the total of the items (known in my db as transactions) on the invoice and paid_amount is total of the payments on the invoice.

Paid inv_id name date_due total_due total_invoice paid_amount
0 10004 Tony's Fund 2009-08-01 00:00:00.000 5100 5100 0
1 10003 Mike's Fund 2009-06-01 00:00:00.000 0 4900 2450
0 10002 Holman inc. 2009-05-01 00:00:00.000 20940 20940 0
0 10001 Holman inc. 2009-06-01 00:00:00.000 20940 20940 0
0 10000 Holman inc. 2009-01-01 00:00:00.000 9650 83760 11290

The problem arises when I put in multiple payments. The payments table for the last invoice looks as follows:

pay_id pay_amount inv_id reference active
21 100 10000 True
22 10000 10000 True
23 1000 10000 True
24 190 10000 True

the transactions looks like this:

trans_id amount ttype_id inv_id active
215 20940 1 10000 True

I expect what is in payments and transactions, but my result from the query is not correct for a couple rows, what I should get is this:

Paid inv_id name date_due total_due total_invoice paid_amount
0 10004 Tony's Fund 2009-08-01 00:00:00.000 5100 5100 0
1 10003 Mike's Fund 2009-06-01 00:00:00.000 0 2450 2450
0 10002 Holman inc. 2009-05-01 00:00:00.000 20940 20940 0
0 10001 Holman inc. 2009-06-01 00:00:00.000 20940 20940 0
0 10000 Holman inc. 2009-01-01 00:00:00.000 9650 20940 11290

sorry the spacing is bad, i don't know how to get multiple spaces

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 14:23:02

[code]SELECT invoices.paid,
invoices.inv_id,
funds.name,
invoices.date_due,
invoices.total_due,
SUM(COALESCE(tr.amount, 0)) As total_invoice,
SUM(COALESCE(py.amount, 0)) As paid_amount
FROM invoices
LEFT JOIN (
SELECT inv_id,
SUM(amount) AS amount
FROM transactions
WHERE Active = 'True'
GROUP BY inv_id
AS tr ON invoices.inv_id = tr.inv_id
LEFT JOIN (
SELECT inv_id,
SUM(pay_amount) AS amount
FROM payments
WHERE Active = 'True'
GROUP BY inv_id
) AS py ON invoices.inv_id = py.inv_id
LEFT JOIN funds ON invoices.fund_id = funds.fund_id
GROUP BY invoices.inv_id,
invoices.date_due,
invoices.total_due,
funds.name,
invoices.paid
ORDER BY invoices.inv_id DESC[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources