Add column based on condition in th row

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

New to SQL Server Programming

Add column based on condition in th row

Author

Topic

dannyel
Starting Member

2 Posts

Posted - 2010-06-10 : 13:45:20

Hi all ,
I’m new to Sql Queries; I have a requirement which involves Sql query,
Can anyone tell the How to proceed or provide me Sql
Here goes the requirement
Current Table
ID Month Data
1 Jan 10.00
2 Feb 20.00
3 Mar 30.00
4 Apr 50.00
5 May 60.00
6 Jun 70.00
7 Jul 100.00
8 Aug 110.00
9 Sep 120.00
10 Oct 140.00
11 Nov 150.00
12 Dec 170.00

Format needed
ID Month Data Condition
1 Jan 10.00 Jan
2 Feb 10.00 Feb-Jan
3 Mar 10.00 Mar - Feb
4 Apr 20.00 Apr - Mar
5 May 10.00 May - Apr
6 Jun 10.00 Jun - May
7 Jul 30.00 Jul - Aug
8 Aug 10.00 Aug - Jul
9 Sep 10.00 Sep -Aug
10 Oct 20.00 Oct -Sep
11 Nov 10.00 Nov - Oct
12 Dec 20.00 Dec - Nov

Thanks in advance
Astle

astle

Sachin.Nand

2937 Posts

Posted - 2010-06-10 : 14:06:14

[code]
declare @tbl as table(id int,month varchar(40),inventory decimal(18,2))
insert into @tbl
select 1, 'Jan' ,10.00 union all
select 2, 'Feb' ,20.00 union all
select 3, 'Mar' ,30.00 union all
select 4, 'Apr' ,50.00 union all
select 5, 'May' ,60.00 union all
select 6, 'Jun' ,70.0 union all
select 7, 'Jul' ,100.00 union all
select 8, 'Aug' ,110.00 union all
select 9, 'Sep', 120.00 union all
select 10,'Oct' ,140.00 union all
select 11,'Nov' ,150.00 union all
select 12,'Dec', 170.00

select t1.id,
t1.month,isnull(t1.inventory-t2.inventory,t1.inventory)as Data
,isnull(t2.month + '-' +ISNULL(t1.month,t2.month) ,t1.month) as Condition
from @tbl t1
left join(select * from @tbl)t2 on t1.id=t2.id+1

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

dannyel
Starting Member

2 Posts

Posted - 2010-06-10 : 18:29:36

Let me explain it in detail

Create table script (portion of the original table) which I have created in SQL Server

CREATE TABLE [Sample](
[ID] [int] NOT NULL,
[Month] [nchar](10) NOT NULL,
[data] [nchar](10) NULL,
CONSTRAINT [PK_Sample] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SQL to Populate

INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(1,'aa','Jan-10',10)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(2,'aa','Feb-10',20)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(3,'aa','Mar-10',30)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(4,'aa','Apr-10',50)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(5,'aa','May-10',60)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(6,'aa','Jun-10',70)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(7,'aa','Jul-10',100)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(8,'aa','Aug-10',110)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(9,'aa','Sep-10',120)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(10,'aa','Oct-10',140)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(11,'aa','Nov-10',150)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(12,'aa','Dec-10',170)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(13,'aa','Jan-09',180.67)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(14,'aa','Feb-09',194.67)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(15,'aa','Mar-09',208.67)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(16,'aa','Apr-09',222.67)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(17,'bb','Jan-10',45)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(18,'bb','Feb-10',80)
INSERT INTO [Sample] ([ID] ,[Account],[Month] ,[data]) VALUES(19,'bb','Mar-10',100)

And this goes on …Original table has more than million records

Result of the Sql Query should look like this

ID|--|Account|--|Month|--|Data|--|Data base on this condition
1|--|aa|--|Jan-10|--|10|--|Jan 10
2|--|aa|--|Feb-10|--|10|--|Feb10-Jan10
3|--|aa|--|Mar-10|--|10|--|Mar10 - Feb10
4|--|aa|--|Apr-10|--|20|--|Apr10 - Mar10
5|--|aa|--|May-10|--|10|--|May10 - Apr10
6|--|aa|--|Jun-10|--|10|--|Jun10 - May10
7|--|aa|--|Jul-10|--|30|--|Jul10 - Aug10
8|--|aa|--|Aug-10|--|10|--|Aug10 - Jul10
9|--|aa|--|Sep-10|--|10|--|Sep10 -Aug10
10|--|aa|--|Oct-10|--|20|--|Oct10 -Sep10
11|--|aa|--|Nov-10|--|10|--|Nov10 - Oct10
12|--|aa|--|Dec-10|--|20|--|Dec10 - Nov10
13|--|aa|--|Jan-09|--|181|--|Jan 09
14|--|aa|--|Feb-09|--|14|--|Feb09-Jan09
15|--|aa|--|Mar-09|--|14|--|Mar09 - Feb09
16|--|aa|--|Apr-09|--|14|--|Apr09 - Mar09
17|--|bb|--|Jan-10|--|45|--|Jan 10
18|--|bb|--|Feb-10|--|35|--|Feb10-Jan10
19|--|bb|--|Mar-10|--|20|--|Mar10 - Feb10

Explanation
1 .for all Jan month, query should return Jan data
2. For rest of the month, it should subtract with previous month within same year and same accounts and return the result (which is specified in Data base on this condition which I have included for the understanding)

Please let me know if u require any more information,

Thanks in advance
Astle

astle

Sachin.Nand

2937 Posts

Posted - 2010-06-11 : 01:50:49

Do you want a spoonfeeded answer?
Since you are are new to SQL you need to try and show us your attempt at the problem.If you are stuck at something let us know.There are lot of SQL gurus here who will be more than glad to help you.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-11 : 03:42:03

Hi All,

Have a Look at it, it generates in one query.
--------------------------
Select id, month, inventory ,
row_number() over(order by id) as GenRowNumber,
(Select inventory from W PrevVal
WHERE
PrevVal.id =S.id-1 ) AS prevRowVal,
inventory - (Select inventory from W prevVal
WHERE
PrevVal.id =S.id-1) AS CurrRowVal
From W S

if your id column is Primarykey and is placed in the order this will work fine,
else you need to generate a number for the month using Rank().

Let me know if any issue.

Lets unLearn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources