Invoice Forecasting - 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)

Invoice Forecasting

Author

Topic

duleepN
Starting Member

12 Posts

Posted - 2008-10-22 : 08:39:11

All;
In our Client table there is a contract charge amount. We invoice that amount to the client, for some every month, some every 3 months and some clients every 4 weeks and some every week.
Now how can i forecast total invoice amount per month?
===============
Client_Ref
Contract_Charge
Invoice_Every (format:1week, 4 weeks, 1 month, 3month)
===============
Can anybody please help?

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-10-22 : 09:13:54

Hi duleep,
Is there any datetime data in your client table for recording for each invoice

duleepN
Starting Member

12 Posts

Posted - 2008-10-22 : 09:43:43

Soz, Yes. Last_Invoice_Date (Datetime)
tx

duleepN
Starting Member

12 Posts

Posted - 2008-10-28 : 08:10:26

OK! So i guess above is not possible???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 08:13:14

Yes it is possible.
See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 08:25:09

[code]SELECT Total
FROM
(
SELECT SUM(Contract_Charge) AS Total,
CASE WHEN Invoice_Every LIKE '%weeks%' THEN DATEADD(wk,LEFT(Invoice_Every,CHARINDEX(' ',Invoice_Every)-1)*1,Last_Invoice_date)
WHEN Invoice_Every LIKE '%months%' THEN DATEADD(mm,LEFT(Invoice_Every,CHARINDEX(' ',Invoice_Every)-1)*1,Last_invoice_Date) END AS Date
FROM YourTable
)t
WHERE Date >DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND Date< DATEADD(mm,DATEDIFF,mm,0,GETDATE()),1)[/code]

duleepN
Starting Member

12 Posts

Posted - 2008-11-11 : 08:04:48

Thanks for this! but what i want is data in below format..
Jan - £250.00
Feb - £350.00
March - £375.00 ect for all 12 months.

It seems very difficult...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources