March 12, 2012 at 10:12 am
I have 3 tables:
*** there can be some issue with data since this tables are created just to make the understandinh
about the view or issue.
create table rooky(rid int,rname varchar(50))
create table nooky(nid int,nname varchar(100),rid int)
create table cooky(rid int,nid int,total decimal(18,0))
insert into rooky
select 1,'hell'
union
select 2,'dell'
union
select 3,'bell'
union
select 4,'shell'
insert into nooky
select 1,'microsoft',2
union
select 2,'Apple',1
union
select 3,'SunMicro',1
union
select 4,'Goos' ,3
union
select 5,'google',2
union
select 6,'Arc',4
union
select 7,'Penpaper', 1
insert into cooky
select 1,2,1000
union
select 1,2,2000
union
select 1,3,200
union
select 1,3,100
union
select 1,4,200
union
select 4,2,300
union
select 4,2,100
**below is the query that i am using to get data.
select rooky.rid,cooky.nid,nooky.nname,cooky.total,(select top 1 x.total from cooky x join nooky y
on x.rid=y.rid where y.rid<rooky.rid and nooky.nid=cooky.nid order by y.rid desc) as new_col
from rooky inner join cooky on rooky.rid=cooky.rid inner join nooky on cooky.nid=cooky.nid
the desired output is that for same nid would like to have previous total from
previous rid.
but there is a problem with the inline query that for the value or rid 1 its doesnot
have any previous rid i.e there is nothing called rid<1 so it shows null for the new
col. I would like to see something like where new_col is null there show value=total
for same rid and nid i.e where rid=1 or new_col=null there new_col=total
therfore if rid=1 and nid=2 and total=1000 and new_col=null then new_col=1000
how to achive that in inline query since i am getting ull for new_col column which i dont want.
March 12, 2012 at 10:19 am
Excellent job with ddl and sample data. However each time I read your description of the desired output I am more confused than I was after the previous reading.
Can you explain clearly what you want for desired output, it would also be helpful if you manually created the desired output so there is something to validate with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2012 at 10:33 am
yup sure,
there is a rid , nid and name and total.
i want to get total for previous rid but for same nid.
ie. if
rid nid name total new_col
----------------------------------
1 2 abc 100 null
1 3 xyz 200 null
2 2 abc 300 100
2 3 xyz 400 200
3 2 abc 900 300
3 3 xyz 600 400
now i want that in my inline query if i get null then keep new_col=total since there is no
value for rid<1
ie.
rid nid name total new_col
----------------------------------
1 2 abc 100 100
1 3 xyz 200 200
2 2 abc 300 100
2 3 xyz 400 200
3 2 abc 900 300
3 3 xyz 600 400
got it?!!
March 12, 2012 at 10:42 am
Not 100% sure this is what you are looking for since you sample output had nothing to do with the original data.
Something like this?
select rooky.rid,cooky.nid,nooky.nname,cooky.total,isnull(
(
select top 1 x.total from cooky x join nooky y
on x.rid = y.rid where y.rid < rooky.rid and nooky.nid = cooky.nid order by y.rid desc
), cooky.total) as new_col
from rooky
inner join cooky on rooky.rid = cooky.rid
inner join nooky on cooky.nid = cooky.nid
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2012 at 3:28 pm
Your sample data had flaws. i have fixed them and here is the new set of data
if object_id('rooky') is not null
drop table rooky
if object_id('nooky') is not null
drop table nooky
if object_id('cooky') is not null
drop table cooky
GO
create table rooky(rid int,rname varchar(50))
create table nooky(nid int,nname varchar(100),rid int)
create table cooky(rid int,nid int,total decimal(18,0))
insert into rooky
select 1,'hell'
union
select 2,'dell'
union
select 3,'bell'
union
select 4,'shell'
insert into nooky
select 1,'microsoft',2
union
select 2,'Apple',1
union
select 3,'SunMicro',1
union
select 4,'Goos' ,3
union
select 5,'google',2
union
select 6,'Arc',4
union
select 7,'Penpaper', 1
insert into cooky
select 1,1,1000
union
select 1,2,2000
union
select 1,4,200
union
select 2,1,900
union
select 2,2,100
union
select 2,4,200
union
select 3,4,200
union
select 3,2,500
union
select 4,2,300
union
select 4,3,100
And the query
; WITH CTE AS
(
SELECT C.rid , C.nid , R.rname , N.nname , C.total
FROM cooky C
INNER JOIN rooky R
ON r.rid = C.rid
INNER JOIN nooky N
ON N.nid = C.nid
)
SELECT OutTab.rid , OutTab.rname ,OutTab.nid , OutTab.nname
, Total = ISNULL (CrsApp.total , OutTab.total )
FROM CTE OutTab
OUTER APPLY
(SELECT *
FROM CTE InrTab
WHERE InrTab.rid = OutTab.rid - 1
AND InrTab.nid = OutTab.nid
) CrsApp
Output:
rid rname nid nname Total
----------- ----- ----------- --------------- -------
1 hell 1 microsoft 1000
1 hell 2 Apple 2000
1 hell 4 Goos 200
2 dell 1 microsoft 900
2 dell 2 Apple 100
2 dell 4 Goos 200
3 bell 2 Apple 500
3 bell 4 Goos 200
4 shell 2 Apple 300
4 shell 3 SunMicro 100
code clean up
if object_id('rooky') is not null
drop table rooky
if object_id('nooky') is not null
drop table nooky
if object_id('cooky') is not null
drop table cooky
GO
March 12, 2012 at 3:38 pm
inline query:
SELECT C.rid , C.nid , R.rname , N.nname , C.total
, total = ISNULL( (SELECT InrTab.total
FROM cooky InrTab
WHERE InrTab.rid = R.rid - 1
AND InrTab.nid = N.nid
) , C.total )
FROM cooky C
INNER JOIN rooky R
ON r.rid = C.rid
INNER JOIN nooky N
ON N.nid = C.nid
order by c.rid , c.nid
Similar to Sean's!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply