December 18, 2007 at 9:45 am
Hi I have two tables.
The first table has the following fields:
(pk)id, name, ticket_details
The second table has these two fields:
(fk)id, ticket_details, update_time
In the second table there can be multiple updates
I'd like to produce a query that gives me a one line item of each record in the first table. I would also like to see all additional ticket details that have been added in the second table that is bound by the id field.
So I want to see either in a view or a temp table:
id, name ticket_details (from tbl1) + ticket_details(from tbl2) + update_time
This is what I've got so far
with cte as
(
select *,
row_number() over(partition by b.id order by b.update_time desc) as rn
tbl1 as a
left join tbl2 as b
on a.id = b.id
)
select *
into #tcktdetails
from cte
declare @rn as int
set @rn = 4
select @rn = max(rn) from #tcktdetails where id = id
--show last 4 updates only
while @rn <= 4
begin
update a
set a.ticket_details = a.pldetails + ' !! ' + convert(varchar, b.update_time, 114) ' !! ' + b.pldetails
from #tcktdetails as a
left join #tcktdetails as b
on a.id = b.id
where b.rn >= @rn
set @rn = @rn +1
end
select *
from #tcktdetails
The problem is that I am only getting the last update from tbl2 to update and it adds it multiple times depending on the quantity of the rn. I'd like to add the updates only once per update.
I hope this makes sense,
Thanks in advance for any help with this query or a better way to do it and any other suggestions.
Marcus.
December 18, 2007 at 10:18 am
Have you tried to join them?
SELECT *
FROM TABLE1 a
INNER JOIN TABLE2 b ON A.id = B.id
December 18, 2007 at 10:24 am
Hi Adam, thanks for the reply. Yes I can join them but then I'll get a line record for every update, so if there are four updates on a record I'll end up with 4 line records. What I'd like to do is take all the update statements in tbl2 and add them to the ticket_details field in tbl1 in a query to produce a report that shows one line per record but includes all ticket details in the ticket details fields from both tbl1 + tbl2.
December 18, 2007 at 10:26 am
After reading the post again, my previous post did not give you the result you are trying to achieve.
Let me look at it a little harder.
December 18, 2007 at 10:27 am
I just read you post again :hehe:
Let me have another look.
-Adam
December 18, 2007 at 10:30 am
Thanks Adam, I appreciate your time. I know what I'm asking for is convoluted. It's the format my boss wants the report, but I'm having trouble doing what he wants.
December 18, 2007 at 10:43 am
Adam, I'm trying to make it a little less complicated, is there a way to take the first update from tbl2 and then join tbl2 to itself to update the first update, with all subsequent udpates.
So here is a sample record set.
id details uptime rn
a up1 time1 1
a up2 time2 2
a up3 time3 3
a up4 time4 4
The end result I would like to see in a temp table or view would be:
id details
a up1 time1, up2 time2, up3 time3, up4 time4
I hope this makes what I was originally trying to do and explain easier.
December 18, 2007 at 11:08 am
This sample file should help you get to where you need to go. I have successfully tested it on my machine.
declare @tbl1 table(
id int,
descr varchar(25)
)
declare @tbl2 table(
id int,
descr varchar(25)
)
insert into @tbl1
select 1, 'test1' union all
select 2, 'test2'
insert into @tbl2
select 1, 'test1 additional' union all
select 1, 'test1 additional2' union all
select 2, 'test2 additional'
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT id, descr
FROM @tbl2
--ADD WHERE AND ORDER BY CLAUSE HERE IF NEEDED
ORDER BY id
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @id int
DECLARE @descr varchar(25)
--set starting value of the running total
DECLARE @prevID INT
SET @prevID = -1
DECLARE @STR varchar(500)
SET @STR = ''
--hold our results data
DECLARE @Results TABLE
(
id varchar(20),
descr varchar(500)
)
FETCH NEXT FROM RunningTotalCursor
INTO @id, @descr
WHILE @@FETCH_STATUS = 0
BEGIN
-- if it is the first u_subconta dont worry about checking this
-- otherwise compare the previous subconta with the current to see if
-- we need to start from scratch.
IF @id <> @prevID
BEGIN
SET @STR = ''
END
SET @STR = @STR + @descr +',' + ' '
SET @prevID = @ID
--insert values into the results table
INSERT @Results
VALUES (@id, @STR)
FETCH NEXT FROM RunningTotalCursor
INTO @id, @descr
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
select a.id, a.descr,b.descr
from @tbl1 a inner join
(select id, max(descr) [descr]
from @results
group by id
) AS b on a.id = b.id
December 18, 2007 at 11:10 am
Please note that this particular solution uses a cursor, against my better judgement :w00t:
If you have a substantial amount of records this query could perform very poorly, as it goes through the result set one record at a time.
December 18, 2007 at 11:13 am
Thanks Adam,
Although I've read up on them a bit, I've never used a cursor before. I'll give it a try and let you know how it goes.
December 18, 2007 at 11:17 am
It behaves just like a cte which also walk through a set of data one row at a time.
December 18, 2007 at 11:19 am
Forgot to mention that some of the notation is irrelevant. This is a cursor that I helped someone else on this forum with and forgot to delete my comments.
just an FYI. 😉
December 18, 2007 at 11:29 am
You can also use what is calledl the poor man's cursor to loop through the recordset as well. It's a little bit more code but is almost as efficient and less resources.
DECLARE @ASSESSMENT_VARIABLES TABLE (
RowNum INT IDENTITY,
ASSESSMENT_NAME VARCHAR(25),
SUBJECT_CD VARCHAR(5),
SUBJECT_DESC VARCHAR(50)
)
INSERT INTO @ASSESSMENT_VARIABLES
SELECT ASSESSMENT_NAME_SHORT, SUBJECT_CD, SUBJECT_DESC
FROM WK_ASSESSMENT_SUBJECT, WK_ASSESSMENT_TYPES
SELECT @RowCount = 1
SELECT @MaxRows = COUNT(*) FROM @ASSESSMENT_VARIABLES
WHILE @RowCount <= @MaxRows
BEGIN
SELECT
@SUBJECT_CD = SUBJECT_CD,
@SUBJECT_DESC = SUBJECT_DESC
FROM @ASSESSMENT_VARIABLES
WHERE RowNum = @RowCount
Code to execute
SELECT @RowCount = @RowCount + 1
END
Happy coding
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
December 18, 2007 at 2:07 pm
Since you're using 2005, you might try this method (I stole the table variable code from Adam Haines, since I'm lazy), tweaking it as needed:
declare @tbl1 table( id int, descr varchar(25))
declare @tbl2 table( id int, descr varchar(25))
insert into @tbl1
select 1, 'test1' union all
select 2, 'test2'
insert into @tbl2
select 1, 'test1 additional' union all
select 1, 'test1 additional2' union all
select 2, 'test2 additional'
SELECT
t1.id
,Stuff((',' + t1.descr + (SELECT ',' + descr FROM @tbl2 AS t2 WHERE t1.id = t2.id FOR XML PATH(''))),1,1,'')
FROM
@tbl1 AS t1
GROUP BY
t1.id
,t1.descr
December 18, 2007 at 2:27 pm
Cool solution! Using xquery to parse the string is great. Good job, this code should be substantially faster.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply