Help with Query - 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 2008 Forums

Transact-SQL (2008)

Help with Query

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-11-25 : 10:26:39

Hi,

In the table there are columns : date,amount.
So the data is inserted in to the table on daily basis and the date value would have inserted date.

I am creating a stored proc with the input parameters "from month", "from year" "to month" and "to year"

So the stored proc should return the amount month wise between the from month, from year and to month, to year.

For example. The table has values

Date Amount
------
01/12/2010 100
12/12/2010 200
01/01/2011 300
01/01/2011 400
02/01/2011 500
01/02/2011 600
01/02/2011 700
01/03/2011 800
02/03/2011 900

So if the input of the stored proc is
From month = 12
From year = 2010
To month = 2
To year = 2011

Then the output has to be

12/ 2010 amount - 300
1 /2011 amount - 1200
2 / 2011 amount - 1300

Please let me know the procedure to achieve this.

Thanks,
Sandesh

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 11:19:41

declare @strtdte datetime
declare @enddte datetime
select @strtdte = convert(varchar(8),@fromyear*10000 + @frommonth * 100 + 1)
select @enddte = convert(varchar(8),@endyear*10000 + @endmonth * 100 + 1)
select @enddte = dateadd(mm,1,@enddate)-1

select convert(varchar(6),date,112), sum(amount)
from tbl
where date between @strtdte and @enddte
group by convert(varchar(6),date,112)
order by convert(varchar(6),date,112)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-11-28 : 05:02:56

Thank you...

Thanks,
Sandesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources