June 10, 2010 at 12:38 pm
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
June 10, 2010 at 1:03 pm
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.
June 10, 2010 at 1:09 pm
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
June 10, 2010 at 1:20 pm
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]
June 10, 2010 at 3:44 pm
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
June 10, 2010 at 3:49 pm
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
June 10, 2010 at 4:02 pm
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
June 10, 2010 at 4:41 pm
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]
June 10, 2010 at 4:51 pm
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 🙂
June 10, 2010 at 5:30 pm
thanks bteraberry,
Your Query was perfect for all the condition,im just expanding your query to accommodate all conditionn
June 10, 2010 at 8:25 pm
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
June 11, 2010 at 6:59 am
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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply