Forecast ? - 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

General SQL Server Forums

New to SQL Server Programming

Forecast ?

Author

Topic

GnR_Slash
Starting Member

14 Posts

Posted - 2010-09-15 : 13:20:04

Hi guys,

I don't know the name of my question because i never heard about it.
Is it difficult to implement inside SQL Server something like this?

- I have a table were i put information:
. Date (payment date)
. Value (money)
. Periodic (boolean indicating if this value must be repeated every month at same date)
. paid (boolean indicating that this ticket was paid)

How can i execute a query (or stored procedure), that returns the tickets that should be paid between 01/05/2010 and 30/05/2010 (for an example), but includes itens that are periodic. An example is: i have an item inside the table like this:

date = 01/01/2010
Value = 220,00
Periodic = 1
paid = "doesn't matter"

this item must appear at my results for period: 01/05/2010 and 30/05/2001

is it possible and easy?

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-15 : 14:27:36

[code]SELECT date, value, Periodic, Paid
FROM YourTable
WHERE date BETWEEN '20100501' And '20100530'
And Periodic = 0
UNION ALL
SELECT date, value, Periodic, Paid
FROM YourTable
WHERE Periodic = 0[/code]

GnR_Slash
Starting Member

14 Posts

Posted - 2010-09-16 : 06:44:48

thanks for your reply, i didn't think this way and you open new things in my mind :)

But, this does not solve the problem because if you pay a ticket that is periodic, this ticket will show twice if you execute a new query. Example: If you have a ticket that starts on January/10 and ends on December/10 value=250,00, when i execute this query:

SELECT	date, Paid, value, Periodic
FROM	YourTable
WHERE	date BETWEEN '20100201' And '20100530'
And	Periodic = 0
UNION ALL
SELECT	date, Paid, value, Periodic
FROM	YourTable
WHERE	Periodic = 1

I will get this:

Date       Paid       Value     Periodic
2010-05-01 NULL	      74,00	0
2010-05-10 NULL	      1078,00	0
2010-01-01 2010-01-01 250,00	1

You can see 2 records that needs to pay month 5, and you see 1 record with 'wrong' date and seens to be already paid.

What i really want is something like this:

Date       Paid       Value     Periodic
2010-05-01 NULL	      74,00	0
2010-05-10 NULL	      1078,00	0
2010-02-01 NULL       250,00	1
2010-03-01 NULL       250,00	1
2010-04-01 NULL       250,00	1
2010-05-01 NULL       250,00	1

I think this is complicated to deal with a query, maybe a stored procedure with some code and loops.

Is it better to solve this inside the code at user interface (C#.NET) or inside stored procedure?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 12:33:15

sorry didnt understand why you need to return records for other months when pay is periodic? isnt it outside your range?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources