Rolling figure - 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)

Rolling figure

Author

Topic

duleepnagahawatte
Starting Member

16 Posts

Posted - 2007-12-27 : 08:09:31

Greetings All;
In our production database, There is a "contract" table. Contracts has got an "invoice period" column which says the period to invoice as 4 weesk, 1 month, 3 months ect: And there is another column which has got the "Invoice figure". Third column has got the "last invoice date".
Now i need to run a query to bring invoice dates for the whole year since the "last invoice date", calculate the total "invoice figure" for each month
--------------
I cant get my head around this, can someone please help!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-27 : 08:19:29

Not sure what you need. Please support your question with sample data, table structure and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-27 : 08:23:16

search for running total around here.
also note that this is bst done in the presentation layer.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

duleepnagahawatte
Starting Member

16 Posts

Posted - 2007-12-27 : 09:12:43

"contract" table
===========================================================================
"invoice period" ---"Invoice figure" --- "last invoice date" --- "Contract"
===========================================================================
every 4 weesk --- £400.00 --- 01/12/2007 --- Con1
every 1 month, --- £500.00 --- 30/11/2007 --- Con2
every 3 months --- £600.00 --- 20/10/2007 --- Con3
==============================================================

Expected Out put
=================
Future invoice dates for Con1
01/12/2007+4 weeks ------ 01/12/2007+4 weeks+4weeks ------ 01/12/2007+4 weeks+4weeks+4weeks
Future invoice dates for Con2
30/11/2007 -------- 30/12/2007 --------- 30/01/2008
Future invoice dates for Con3
20/01/2008 ----- 20/04/2008 ------- 20/07/2007

Hope above make sense! Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-27 : 10:16:46

>>Please support your question with sample data, table structure and expected output
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

- Is there a finite number of possible [invoice period] values?
- Are there any rules to follow for future dates
ie: if [last invoice date] = '2008-01-31' and [invoice period] = "every 1 month", what should the next date be? The last day in Feb for 2008 (Feb 29 2008)?

Please be precise on your expected output, your first post says "calc invoice figure" but your sample output above only contains dates.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 14:28:28

Is this what you are looking for?

CREATE PROC ForecastDates 
@Contract varchar(10)
AS

DECLARE @Invoicedate datetime,@InvoicePeriod  varchar(50),@Results varchar(1000),@NewDate datetime

SELECT @InvoiceDate=lastinvoicedate,
       @InvoicePeriod =invoice period,
       @NewDate=NULL,@Results=NULL
FROM Table where Contract=@Contract

WHILE @NewDate <=DATEADD(m,1,@InvoiceDate)
BEGIN
SELECT @NewDate= CASE WHEN @InvoicePeriod='every 4 weeks' THEN DATEADD(wk,4,ISNULL(@NewDate,@InvoiceDate))
            WHEN @InvoicePeriod='every 1 month' THEN DATEADD(m,1,ISNULL(@NewDate,@InvoiceDate))
            WHEN @InvoicePeriod='every 3 month' THEN DATEADD(m,3,ISNULL(@NewDate,@InvoiceDate))
...
      END
SELECT @Results=COALESCE(@Results,'')  +CAST(@NewDate AS varchar(11)) + '---'
END

SELECT @Results='Future invoice dates for '+ @Contract + ' :' + @Results  
GO

duleepnagahawatte
Starting Member

16 Posts

Posted - 2007-12-28 : 07:12:59

Visakh
This is exactly what im looking for, but it does not print any results at the moment.
This is how i have changed it.......
======================================
ALTER PROC [dbo].[ForecastDates]
@Contract varchar(10)
AS

DECLARE @Invoicedate datetime,@InvoicePeriod varchar(50),@Results varchar(1000),@NewDate datetime

SELECT @InvoiceDate=Last_Invoice_Date,--lastinvoicedate,
@InvoicePeriod =Invoiced_Every,--invoice period,
@NewDate=NULL,@Results=NULL
FROM dbo.Contract_Headers where Contract_No=@Contract

WHILE @NewDate <=DATEADD(m,1,@InvoiceDate)
BEGIN
SELECT @NewDate= CASE WHEN @InvoicePeriod='every 4 weeks' THEN DATEADD(wk,4,ISNULL(@NewDate,@InvoiceDate))
WHEN @InvoicePeriod='1 Month' THEN DATEADD(m,1,ISNULL(@NewDate,@InvoiceDate))
WHEN @InvoicePeriod='every 3 month' THEN DATEADD(m,3,ISNULL(@NewDate,@InvoiceDate))
--...
END
SELECT @Results=COALESCE(@Results,'') +CAST(@NewDate AS varchar(11)) + '---'
END

BEGIN
SELECT @Results='Future invoice dates for '+ @Contract + ' :' + @Results
END
===============================================

But this does not reply any out put
====
DECLARE @return_value int

EXEC @return_value = [dbo].[ForecastDates]
@Contract = N'1004-4'

SELECT 'Return Value' = @return_value

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-28 : 09:29:07

I had in fact missed the last portion to print out the output

. Anyways happy that it gave you desired result. Cheers.

duleepnagahawatte
Starting Member

16 Posts

Posted - 2007-12-28 : 09:48:16

Visakh
What is the last portion to print out the output
appreciate it! thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-28 : 09:52:58

just add a SELECT @Results at the end. Also you dont require that last BEGIN END around SELECT

duleepnagahawatte
Starting Member

16 Posts

Posted - 2008-01-02 : 06:53:19

guys;
Happy New year to everyone!
---------------------------------------------------------------------
now i have following stored procedure in place to get contract invoice dates, it takes a Contract number as a parameter and then excute the stored procedure.
Now i want to pass more than one Contract number from a select ststement to this Stored procedure, so it could execute once per contrcat number. How can i acheive that??
================================================================
Here is the stored proc:

ALTER PROC [dbo].[ForecastDates]
@Contract varchar(10)
AS

DECLARE @Invoicedate datetime,@InvoicePeriod varchar(50),
@Results varchar(1000),@NewDate datetime

SELECT @InvoiceDate=Last_Invoice_Date,@InvoicePeriod =Invoiced_Every,
@NewDate=NULL,@Results=NULL
FROM dbo.Contract_Headers where Contract_No=@Contract

Set @Newdate=@InvoiceDate
while Year(@NewDate) <'2009'
begin

Set @NewDate= CASE WHEN @InvoicePeriod='4 weeks' THEN DATEADD(wk,4,@NewDate)
WHEN @InvoicePeriod='1 Month' THEN DATEADD(m,1,@NewDate)
WHEN @InvoicePeriod='3 Month' THEN DATEADD(m,3,@NewDate)
END

print 'Invoice dates for ' + @Contract + ' is ' + CAST(@NewDate AS varchar(11))
End
==============================================================

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 09:12:28

ALTER PROC [dbo].[ForecastDates]
@ContractNos varchar(max)AS

DECLARE @Invoicedate datetime,@InvoicePeriod varchar(50),
@Results varchar(1000),@NewDate datetime,@ContractNo varchar(50)

WHILE @ContractNos IS NOT NULL
BEGIN
SELECT @ContractNo=CASE
WHEN CHARINDEX(',',@ContractNos)>0 THEN LEFT(@ContractNos,CHARINDEX(',',@ContractNos)-1)
ELSE @ContractNos
END,
@ContractNos=CASE
WHEN CHARINDEX(',',@ContractNos)>0 THEN RIGHT(@ContractNos,CHARINDEX (',',@ContractNos)+1)
ELSE NULL
END

SELECT @InvoiceDate=Last_Invoice_Date,
@InvoicePeriod =Invoiced_Every,
@NewDate=NULL,@Results=NULL
FROM dbo.Contract_Headers where Contract_No=@ContractNo

Set @Newdate=@InvoiceDate
while Year(@NewDate) <'2009'
begin

Set @NewDate= CASE WHEN @InvoicePeriod='4 weeks' THEN DATEADD(wk,4,@NewDate)
WHEN @InvoicePeriod='1 Month' THEN DATEADD(m,1,@NewDate)
WHEN @InvoicePeriod='3 Month' THEN DATEADD(m,3,@NewDate)
END

print 'Invoice dates for ' + @ContractNo + ' is ' + CAST(@NewDate AS varchar(11))
End

End

Pass params like this

Exec ForecastDates @ContractNos='val1,val2,....'

duleepnagahawatte
Starting Member

16 Posts

Posted - 2008-01-02 : 09:41:32

Thanks, but it does not output any results! wonder why? Appreciate your assistance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 09:51:02

How did you invoke the SP?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 09:58:04

Try with this:-

ALTER PROC [dbo].[ForecastDates] 
@ContractNos varchar(max)
AS

DECLARE @Invoicedate datetime,@InvoicePeriod varchar(50),@Results varchar(1000),@NewDate datetime,@ContractNo varchar(50)
WHILE @ContractNos IS NOT NULL
BEGIN
	SELECT @ContractNo=CASE
	WHEN CHARINDEX(',',@ContractNos)>0 THEN LEFT(@ContractNos,CHARINDEX(',',@ContractNos)-1)
	ELSE @ContractNos
	END,
	@ContractNos=CASE
	WHEN CHARINDEX(',',@ContractNos)>0 THEN RIGHT(@ContractNos,CHARINDEX (',',@ContractNos)+1)
	ELSE NULL
	END


	SELECT @InvoiceDate=Last_Invoice_Date,--lastinvoicedate,
	@InvoicePeriod =Invoiced_Every,--invoice period,
	@NewDate=NULL,@Results=NULL
	FROM dbo.Contract_Headers where Contract_No=@ContractNo

	WHILE @NewDate <=DATEADD(m,1,@InvoiceDate)
	BEGIN
		SELECT @NewDate= CASE WHEN @InvoicePeriod='every 4 weeks' THEN DATEADD(wk,4,ISNULL(@NewDate,@InvoiceDate))
		WHEN @InvoicePeriod='1 Month' THEN DATEADD(m,1,ISNULL(@NewDate,@InvoiceDate))
		WHEN @InvoicePeriod='every 3 month' THEN DATEADD(m,3,ISNULL(@NewDate,@InvoiceDate))
		--...
		END
		SELECT @Results=COALESCE(@Results,'') +CAST(@NewDate AS varchar(11)) + '---'
	END


	SELECT @Results='Future invoice dates for '+ @ContractNo + ' :' + @Results
        Print  @Results
END

duleepnagahawatte
Starting Member

16 Posts

Posted - 2008-01-02 : 10:09:02

Visakh;
EXEC [dbo].[ForecastDates]
@ContractNos = '1000-4,1434-4'

It just says query executed successfully

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 10:30:51

quote:
Originally posted by duleepnagahawatte

Visakh;
EXEC [dbo].[ForecastDates]
@ContractNos = '1000-4,1434-4'

It just says query executed successfully

Try changing it like this:-

ALTER PROC [dbo].[ForecastDates]
@ContractNos varchar(max)
AS

DECLARE @Invoicedate datetime,@InvoicePeriod varchar(50),@Results varchar(1000),@NewDate datetime,@ContractNo varchar(50),@FinalResult varchar(8000)

WHILE @ContractNos IS NOT NULL
BEGIN
SELECT @Results=''
SELECT @ContractNo=CASE
WHEN CHARINDEX(',',@ContractNos)>0 THEN LEFT(@ContractNos,CHARINDEX(',',@ContractNos)-1)
ELSE @ContractNos
END,
@ContractNos=CASE
WHEN CHARINDEX(',',@ContractNos)>0 THEN RIGHT(@ContractNos,CHARINDEX (',',@ContractNos)+1)
ELSE NULL
END

SELECT @InvoiceDate=Last_Invoice_Date,--lastinvoicedate,
@InvoicePeriod =Invoiced_Every,--invoice period,
@NewDate=NULL,@Results=NULL
FROM dbo.Contract_Headers where Contract_No=@ContractNo

WHILE @NewDate <=DATEADD(m,1,@InvoiceDate)
BEGIN
SELECT @NewDate= CASE WHEN @InvoicePeriod='every 4 weeks' THEN DATEADD(wk,4,ISNULL(@NewDate,@InvoiceDate))
WHEN @InvoicePeriod='1 Month' THEN DATEADD(m,1,ISNULL(@NewDate,@InvoiceDate))
WHEN @InvoicePeriod='every 3 month' THEN DATEADD(m,3,ISNULL(@NewDate,@InvoiceDate))
--...
END
SELECT @Results=COALESCE(@Results,'') +CAST(@NewDate AS varchar(11)) + '---'
END

SELECT @FinalResult=COALESCE(@FinalResult,'') + chr(13)+'Future Invoice dates for'+ @ContractNo + ' :' + @Results

END
SELECT @FinalResult

GO

duleepnagahawatte
Starting Member

16 Posts

Posted - 2008-01-02 : 19:40:41

No luck yet! still it just says query executed

duleepnagahawatte
Starting Member

16 Posts

Posted - 2008-01-03 : 04:04:59

This is working fine as below. Support was remarkable. Thank you very much everybody
====================================================================================
ALTER PROC [dbo].[ForecastDates]
@ContractNos varchar(1000)
AS

DECLARE @Invoicedate datetime,@InvoicePeriod varchar(50),@Results varchar(1000),@NewDate datetime,@ContractNo varchar(50),@FinalResult varchar(8000)

WHILE @ContractNos IS NOT NULL
BEGIN
SELECT @Results=''
SELECT @ContractNo=CASE
WHEN CHARINDEX(',',@ContractNos)>0 THEN LEFT(@ContractNos,CHARINDEX(',',@ContractNos)-1)
ELSE @ContractNos
END,
@ContractNos=CASE
WHEN CHARINDEX(',',@ContractNos)>0 THEN RIGHT(@ContractNos,CHARINDEX (',',@ContractNos)+1)
ELSE NULL
END

SELECT @InvoiceDate=Last_Invoice_Date,--lastinvoicedate,
@InvoicePeriod =Invoiced_Every,--invoice period,
@NewDate=NULL,@Results=NULL
FROM dbo.Contract_Headers where Contract_No=@ContractNo

Set @Newdate=@InvoiceDate
while Year(@NewDate) <'2009'
begin
--set @NewDate = DATEADD(wk,4,@NewDate)
Set @NewDate= CASE WHEN @InvoicePeriod='4 weeks' THEN DATEADD(wk,4,@NewDate)
WHEN @InvoicePeriod='1 Month' THEN DATEADD(m,1,@NewDate)
WHEN @InvoicePeriod='3 Month' THEN DATEADD(m,3,@NewDate)
END

SELECT @Results=COALESCE(@Results,'') +CAST(@NewDate AS varchar(11)) + '---'
END

SELECT @FinalResult=COALESCE(@FinalResult,'') + char(13)+'Future Invoice dates for'+ @ContractNo + ' :' + @Results

END
SELECT @FinalResult

GO
====================================================================================

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources