Need help in SQL , Subtract column based on the condition in the Row

Question

Post reply

Need help in SQL , Subtract column based on the condition in the Row

danielprabhu

SSC Enthusiast

Points: 191
More actions
June 10, 2010 at 12:38 pm

#222008

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|-|10.00

2|-|Feb-10|-|20.00

3|-|Mar-10|-|30.00

4|-|Apr-10|-|50.00

5|-|May-10|-|60.00

6|-|Jun-10|-|70.00

7|-|Jul-10|-|100.00

8|-|Aug-10|-|110.00

9|-|Sep-10|-|120.00

10|-|Oct-10|-|140.00

11|-|Nov-10|-|150.00

12|-|Dec-10|-|170.00

13|-|Jan-09|-|180.67

14|-|Feb-09|-|194.67

15|-|Mar-09|-|208.67

16|-|Apr-09|-|222.67

Format needed

ID |-|Month|-|Data|-|Base on this condition

1|-|Jan-10|-|10.00|-|Jan 10

2|-|Feb-10|-|10.00|-|Feb10-Jan10

3|-|Mar-10|-|10.00|-|Mar10 - Feb10

4|-|Apr-10|-|20.00|-|Apr10 - Mar10

5|-|May-10|-|10.00|-|May10 - Apr10

6|-|Jun-10|-|10.00|-|Jun10 - May10

7|-|Jul-10|-|30.00|-|Jul10 - Aug10

8|-|Aug-10|-|10.00|-|Aug10 - Jul10

9|-|Sep-10|-|10.00|-|Sep10 -Aug10

10|-|Oct-10|-|20.00|-|Oct10 -Sep10

11|-|Nov-10|-|10.00|-|Nov10 - Oct10

12|-|Dec-10|-|20.00|-|Dec10 - Nov10

13|-|Jan-09|-|10.67|-|Jan 09

14|-|Feb-09|-|14.00|-|Feb09-Jan09

15|-|Mar-09|-|14.00|-|Mar09 - Feb09

16|-|Apr-09|-|14.00|-|Apr09 - Mar09

Thanks in advance

Astle

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Sounds like homework to me...

Anyway... you need to perform an outer self-join based on ID=ID+1.

Use the CASE statement to take care of NULL values and different years.

If those

Give it a try and post back if you get stuck. Please include what you've tried so far.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

Lutz has done better than I. I am not sure I understand your requirements based on what you posted. Could you elaborate, and also provide samples of what you have tried?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 3

I assume that id is an autonumber so let's not depend on that to be a perfect sequence, but rather to define the order ok?

Then, self-join and account for null case with coalesce thusly:

select

post.id

,post.monthyr

,coalesce((post.amount - pre.amount),post.amount) amount

from

(

select

id,

(ROW_NUMBER() over (order by id)) - 1 rownum,

monthyr,

amount

from dbo.tbl_3

)post left join

(

select

id,

ROW_NUMBER() over (order by id) rownum,

monthyr,

amount

from dbo.tbl_3

)pre on

pre.rownum = post.rownum

this will help you visualize the strategy:

select

post.id postid, pre.id preid

,post.rownum postrownum, pre.rownum prerownum

,post.monthyr postmonthyr, pre.monthyr premonthyr

,post.amount postamt, pre.amount preamt

,coalesce((post.amount - pre.amount),post.amount) amount

from

(

select

id,

(ROW_NUMBER() over (order by id)) - 1 rownum,

monthyr,

amount

from dbo.tbl_3

)post left join

(

select

id,

ROW_NUMBER() over (order by id) rownum,

monthyr,

amount

from dbo.tbl_3

)pre on

pre.rownum = post.rownum

and hey, if it's homework, please don't plagiarize. but DO benefit from the example. this is how we learn!

[font="Courier New"]ZenDada[/font]

danielprabhu SSC Enthusiast Points: 191 More actions · Answer 4

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 2 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)

Since I have basic knowledge in SQL. stuck up @ start dont know how to proceed

and it is not the homework im struggling with 2 million rows here 🙂

Please let me know if u require any more information,

Thanks in advance

Astle

danielprabhu SSC Enthusiast Points: 191 More actions · Answer 5

if that is homework here is the answer 🙂

SQL> with test as

2 (

3 select 1 id ,to_date ('Jan-10', 'Mon-rr') mon,10.00 data from dual union all

4 select 2,to_date ('Feb-10', 'Mon-rr'),20.00 data from dual union all

5 select 3,to_date ('Mar-10', 'Mon-rr'),30.00 data from dual union all

6 select 4,to_date ('Apr-10', 'Mon-rr'),50.00 data from dual union all

7 select 5,to_date ('May-10', 'Mon-rr'),60.00 data from dual union all

8 select 6,to_date ('Jun-10', 'Mon-rr'),70.00 data from dual union all

9 select 7,to_date ('Jul-10', 'Mon-rr'),100.00 data from dual union all

10 select 8,to_date ('Aug-10', 'Mon-rr'),110.00 data from dual union all

11 select 9,to_date ('Sep-10', 'Mon-rr'),120.00 data from dual union all

12 select 10,to_date ('Oct-10', 'Mon-rr'),140.00 data from dual union all

13 select 11,to_date ('Nov-10', 'Mon-rr'),150.00 data from dual union all

14 select 12,to_date ('Dec-10', 'Mon-rr'),170.00 data from dual union all

15 select 13,to_date ('Jan-09', 'Mon-rr'),180.67 data from dual union all

16 select 14,to_date ('Feb-09', 'Mon-rr'),194.67 data from dual union all

17 select 15,to_date ('Mar-09', 'Mon-rr'),208.67 data from dual union all

18 select 16,to_date ('Apr-09', 'Mon-rr'),222.67 data from dual

19 )

20 select id

21 , mon

22 , data

23 , data - lag (data, 1, 0) over (partition by trunc (mon, 'yyyy')

24 order by mon)

25 from test

26 order by id

27 ;

Here i would close my book and sleep 🙂 my local time is 2.30 pm still searching and learning Sql

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

Just building on what Zendada did a bit.



/*

drop table sample

CREATE TABLE [Sample](

[ID] [int] NOT NULL,

[account] char(2),

[DataMonth] [varchar](10) NOT NULL,

[InvQuantity] decimal(12,5) 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],[DataMonth] ,[InvQuantity]) VALUES(1,'aa','Jan-10',10)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(2,'aa','Feb-10',20)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(3,'aa','Mar-10',30)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(4,'aa','Apr-10',50)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(5,'aa','May-10',60)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(6,'aa','Jun-10',70)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(7,'aa','Jul-10',100)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(8,'aa','Aug-10',110)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(9,'aa','Sep-10',120)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(10,'aa','Oct-10',140)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(11,'aa','Nov-10',150)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(12,'aa','Dec-10',170)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(13,'aa','Jan-09',180.67)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(14,'aa','Feb-09',194.67)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(15,'aa','Mar-09',208.67)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(16,'aa','Apr-09',222.67)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(17,'bb','Jan-10',45)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(18,'bb','Feb-10',80)

INSERT INTO [Sample] ([ID] ,[Account],[DataMonth] ,[InvQuantity]) VALUES(19,'bb','Mar-10',100)

select * from sample

*/

select post.id,post.DataMonth,coalesce((post.InvQuantity - pre.InvQuantity),post.InvQuantity) Amount

	,Isnull(pre.DataMonth + ' - ' + post.DataMonth,post.DataMonth) as TimeFrame

from

		(select id, account,(ROW_NUMBER() over (order by id)) - 1 rownum,DataMonth,InvQuantity

			from dbo.sample

		) post

	Left Outer Join

		(select id, account,ROW_NUMBER() over (order by id) rownum,DataMonth,InvQuantity

			from dbo.sample

		) pre

		on pre.rownum = post.rownum

		And pre.account = post.account

I renamed your columns in the Sample table to make a little more sense for me.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ben Teraberry SSChampion Points: 11815 More actions · Answer 7

This is basically the same as Jason's but it's checking to make sure that the year is the same and the month is literally the next month. So depending on how you would want that to work (say for instance there is a 10-Jan record and a 10-Mar record but no 10-Feb), you may want to do some modification.

with cteTemp(ID, Account, [Month], MyMonth, MyYear, Data)



as

	(

	select s.[ID],

		s.[Account],

		s.[Month],

		'MyMonth' = DATEPART(mm, cast('01-' + s.[Month] as datetime)),

		'MyYear' = DATEPART(yy, cast('01-' + s.[Month] as datetime)),

		'Data' = CAST(s.[data] as decimal(6,2))

	from #sample s

	)

select c2.Account,

	c2.[Month],

	'Data' = case

		when c1.MyMonth is null then cast(round(c2.Data, 0) as int)

		else cast(round(c2.Data - c1.Data, 0) as int)

	end

from cteTemp c2

left join cteTemp c1

	on c2.Account = c1.Account

	and c2.MyYear = c1.MyYear

	and c2.MyMonth = c1.MyMonth + 1

order by c2.Account, c2.[ID]

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

danielprabhu SSC Enthusiast Points: 191 More actions · Answer 8

danielprabhu

SSC Enthusiast

Points: 191

June 10, 2010 at 4:51 pm

#1178979

Thank you very much Jason,

although it doesn't complete my current , its the first step and i feel confident of performing other parts task,

given the amount of time i spent on SQL learning

my thanks, to all who replied to my post 🙂

danielprabhu SSC Enthusiast Points: 191 More actions · Answer 9

danielprabhu

SSC Enthusiast

Points: 191

June 10, 2010 at 5:30 pm

#1178987

thanks bteraberry,

Your Query was perfect for all the condition,im just expanding your query to accommodate all conditionn

SQLRNNR SSC Guru Points: 281324 More actions · Answer 10

SQLRNNR

SSC Guru

Points: 281324

June 10, 2010 at 8:25 pm

#1178999

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

danielprabhu (6/10/2010)

Here i would close my book and sleep 🙂 my local time is 2.30 pm still searching and learning Sql

BWAA-HAAA!!!! Nope... you're NOT learning SQL... you're learning Oracle's SQL/PL-SQL some of which also works in Microsoft's T-SQL. This is a Microsoft SQL Server forum and you just need to be made aware that there can be huge differences between the two for the next time you post. You would probably do better on "Ask TOM" or another Oracle-base forum for "tougher" problems.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions