How to display data using sql server [Resolved]

Posted by Sampath1750 under ASP.NET on 9/28/2013 | Points: 10 | Views : 1936 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
Below is my tables structure and data.

<pre lang="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</pre>

I want the data in below format,

<b>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</b>

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 9/28/2013 [Member] [MVP] Platinum | Points: 50

0	select code, Sum(case when type='R' and year=@year-1 then Quantity end) - Sum(case when type='I' and year=@year-1 then Quantity end ) as OpeningQuant, Sum(case when type='R' and year = @year then Quantity end) - Sum(case when type='I' and year = @year then Quantity end) as ClosingQuant from PM_Ledger where year in (@year, @year-1) group by code Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sampath1750, if this helps please login to Mark As Answer. \| Alert Moderator

select code, Sum(case when type='R' and year=@year-1 then Quantity end) - Sum(case when type='I' and year=@year-1 then Quantity end ) as OpeningQuant,
Sum(case when type='R' and year = @year then Quantity end) - Sum(case when type='I' and year = @year then Quantity end) as ClosingQuant
from PM_Ledger
where year in (@year, @year-1)
group by code

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/30/2013 [Member] [MVP] Platinum | Points: 50

0	Hi Sampath, Small typo was there in my earlier post DECLARE @year int = 2012; -- Passing Year value SELECT code, @year ,COALESCE(Sum(case when type='R' and year<=@year-1 then Quantity end) - Sum(case when type='I' and year<=@year-1 then Quantity end ),0) as OpeningQuant ,Sum(case when type='R' and year <= @year then Quantity end) - Sum(case when type='I' and year <= @year then Quantity end) as ClosingQuant FROM PM_Ledger GROUP BY code NOTE: I forgot to mention <= symbol for open and Close balance calculation /* Sample output: for year 2012 code (No column name) OpeningQuant ClosingQuant 1001 2012 2900 3400 1002 2012 600 1200/ Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sampath1750, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Sampath,
Small typo was there in my earlier post

DECLARE @year int = 2012; -- Passing Year value
SELECT code, @year
	,COALESCE(Sum(case when type='R' and year<=@year-1 then Quantity end) - Sum(case when type='I' and year<=@year-1 then Quantity end ),0) as OpeningQuant
	,Sum(case when type='R' and year <= @year then Quantity end) - Sum(case when type='I' and year <= @year then Quantity end) as ClosingQuant
FROM PM_Ledger
GROUP BY code

NOTE: I forgot to mention <= symbol for open and Close balance calculation

/* Sample output: for year 2012
code (No column name) OpeningQuant ClosingQuant
1001 2012 2900 3400
1002 2012 600 1200*/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/28/2013 [Member] [MVP] Platinum | Points: 25

0	have you got the solution? "mark as answer" Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sampath1750, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts