Hi,
Below is my tables structure and data.
CREATE TABLE [dbo].[PM_Ledger](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Type] [varchar](5) NULL,
[code] [int] NULL,
[Quantity] [float] NULL,
CONSTRAINT [PK_PM_Ledger] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[PM_Ledger] ON
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (1, 2010, N'R', 1001, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (3, 2010, N'R', 1002, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (4, 2010, N'I', 1001, 2100)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (5, 2010, N'R', 1001, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (6, 2010, N'I', 1002, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (7, 2011, N'R', 1001, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (8, 2011, N'I', 1001, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (9, 2011, N'R', 1002, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (10, 2011, N'I', 1002, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (11, 2012, N'R', 1001, 2000)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (12, 2012, N'I', 1001, 1500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (13, 2012, N'R', 1002, 1800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (14, 2012, N'I', 1002, 1200)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (15, 2013, N'R', 1001, 1500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (16, 2013, N'I', 1001, 1800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (17, 2013, N'R', 1002, 1200)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (18, 2013, N'I', 1002, 1450)
SET IDENTITY_INSERT [dbo].[PM_Ledger] OFF
I want the data in below format,
Opening Quantity is (Sum(Quantity) where Type='R' - Sum(Quantity) where Type='I') up to previous year(User entered Year -1)
Closing Qunatity is Opening Quantity is (Sum(Quantity) where Type='R' - Sum(Quantity) where Type='I') for User entered Year
If i enter 2010, i want the data in below format(before 2010 we have no data, thats why OpeningQuantity is 0)
Code Year OpeningQuantity ClosingQuantity
1001 2010 0 3200
1002 2010 0 300
If i enter 2011, i want the data in below format
Code YearOpeningQuantity ClosingQuantity
1001 2011 3200 2900
1002 2011 300 600
i enter 2012, i want the data in below format
Code Year OpeningQuantity ClosingQuantity
1001 2012 2900 3400
1002 2012 600 1200
If i enter 2013, i want the data in below format
Code Year OpeningQuantity ClosingQuantity
1001 2013 3400 3100
1002 2013 600 950
Thanks