Click here to Skip to main content
15,844,387 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,
Below is my tables structure and data.

SQL
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
Posted
Updated 28-Sep-13 2:22am
v5

1 solution

As you formulated your clauses really good (well, almost) - I am wondering what have you stopped in formulating it in sql.
Look here:
SQL
declare @year int;
set @year=2011;
SELECT distinct(code), @year,
 (select sum(Quantity) from PM_Ledger I where Type='R' and "Year" < @year and I.code=L.code) - (select sum(Quantity) from PM_Ledger I where Type='I' and "Year" < @year and I.code=L.code) as OpeningQuantity,
 (select sum(Quantity) from PM_Ledger I where Type='R' and "Year" <= @year and I.code=L.code) - (select sum(Quantity) from PM_Ledger I where Type='I' and "Year" <= @year and I.code=L.code) as ClosingQuantity
from PM_Ledger L
 
Share this answer
 
Comments
sampath1750 29-Sep-13 5:40am    
Thanks
Zoltán Zörgő 1-Oct-13 13:51pm    
If I have brought you nearer to the solution of your problem, feel free to accept my answer!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900