Hi Friend,
I have found a solution.
We can do it by using PIVOT Concept.
I'm having a table named as Purchase with columns(ID,DATE,Purchased,Sold,ProfitOrLoss)
I have created a stored procedure with a parameter @Year
I'm going to show the month wise details of total number of purchased items,Total number of Sold items and Average Profit/Loss for a year
Code:-
ALTER PROCEDURE SP_GET_PURCHASE_ORDER
@YEAR INT
AS
BEGIN
SELECT 'PURCHASE' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT PURCHASE,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(SUM(PURCHASE) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT
UNION ALL
SELECT 'SALES' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT SALES,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(SUM(SALES) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT
UNION ALL
SELECT 'Profit/Loss' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT PROFITLOSS,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(AVG(PROFITLOSS) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT
END
--Please Keep me in Touch
Aravinth.G
SQL Developer,
Kadamba Technologies Pvt.Ltd,
Chennai
Email:- aravinth.it04@gmail.com