Design Stored Procedure - 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

Database Design and Application Architecture

Design Stored Procedure

Author

Topic

ladak
Starting Member

13 Posts

Posted - 2010-02-23 : 11:22:35

Hi,

I need your urgent help to design stored procedure as per my given requirement.

I have a Table 'InvDet' in which i have sales information for 5 years, major fields are as follows :

1. InvoiceDate
2. InvoiceNumber
3. ItemCode
4. Quantity
5. Rate
6. Amount

what i need is to design a stored procedure so that i can get the information of sales quantity in columns year wise

like :
Year Year Year year year
Item Code 2005 2006 2007 2008 2009
----------------------------------------------
00001 6000 17000 15000 10000 8000
00002 16000 10000 15000 10000 8000

Please provide me 'Stored Procedure' for the same.

Thanks
Ladak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 12:18:52

[code]CREATE PROC GetYearlySalesData
AS
SELECT ItemCode,
SUM(CASE WHEN YEAR(InvoiceDate) = 2005 THEN Quantity ELSE 0 END) AS [2005],
SUM(CASE WHEN YEAR(InvoiceDate) = 2006 THEN Quantity ELSE 0 END) AS [2006],
SUM(CASE WHEN YEAR(InvoiceDate) = 2007 THEN Quantity ELSE 0 END) AS [2007],
SUM(CASE WHEN YEAR(InvoiceDate) = 2008 THEN Quantity ELSE 0 END) AS [2008],
SUM(CASE WHEN YEAR(InvoiceDate) = 2009 THEN Quantity ELSE 0 END) AS [2009]
FROM Table
GROUP BY ItemCode
[/code]

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

ladak
Starting Member

13 Posts

Posted - 2010-02-23 : 13:04:42

Thanks brother, i will test it...
Ladak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 09:47:52

welcome

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

ladak
Starting Member

13 Posts

Posted - 2010-02-24 : 12:44:38

It works Perfectly, Thanks for your help

Ladak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:20:26

welcome again

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-02-25 : 09:24:36

note the hardcodings re the year. see what it will give you for 2010....and onwards into the future.

it either becomes an ongoing maintenance issue or you sacrifice your need to have all this data on "one-row per item".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:55:19

quote:
Originally posted by AndrewMurphy

note the hardcodings re the year. see what it will give you for 2010....and onwards into the future.

it either becomes an ongoing maintenance issue or you sacrifice your need to have all this data on "one-row per item".

You can make it dynamic if you want

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

ladak
Starting Member

13 Posts

Posted - 2010-02-25 : 12:26:11

Actually i replaced all years with @Year1, @year2 etc.. variables, now user can enter years from form and get the year wise reports.

Thanks
Ladak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources