May 21, 2013 at 1:30 am
Dear Team ,
Kindly advise , How to create query in sqlserver for running updations,
Qustions :
1 . 1st row Total comes to second row Value1
then calculate 2nd row total . then its comes to 3rd row Value1 column. and its continue for all rows.
how to write the query for this issue?
Note : Link between all rows ==> Id , PreviousRowId
Refer the attachment
Thanks,
Chandrahasan S
May 21, 2013 at 1:58 am
Hope this helps:
with CTE1(Id, name, value1, value2, value3)
AS(
SELECT Id, name, value1, value2, value1+value2 AS value3
FROM dbo.T1 a
WHERE a.Id = 1
UNION all
SELECT a.Id, a.name, a.value1, a.value2, b.value3+a.value2
FROM dbo.T1 a
inner join CTE1 b ON a.Id = b.Id+1
)
select * from CTE1
May 21, 2013 at 2:06 am
Here it is with PreviousRowId:
with CTE1(Id, name, value1, value2, value3, PreviousRowId)
AS(
SELECT Id, name, value1, value2, value1+value2 AS value3, 0 as PreviousRowId
FROM dbo.T1 a
WHERE a.Id = 1
UNION all
SELECT a.Id, a.name, a.value1, a.value2, b.value3+a.value2, PreviousRowId+1
FROM dbo.T1 a
inner join CTE1 b ON a.Id = b.Id+1
)
select * from CTE1
May 21, 2013 at 2:08 am
Thanks For your Reply🙂
But Still Value1 shows 0 , It should be show previous row total .
And one more important thing,
inner join CTE1 b ON a.Id = b.Id+1
id+1 will get issue , because in actual id may differ not 1,2,3 order .
Kindly check and advise me ,
Note : combination of all rows is id, previousrowid
refer the attachment.
Thanks,
Chandrahasan S
May 21, 2013 at 2:48 am
Kindly refer the attachment ,
Id will not coming with order .
Note : Combination of each row is ID and previousrowid
May 21, 2013 at 3:07 am
okies. Got it. For the table with following records:
Idnamevalue1value2totalPreviousRowId
111A 100100
24B 050111
57C 06024
80D 07057
100E 08080
112F 090100
The query is like this:
;with CTE1(Id, name, value1, value2, value3, PreviousRowId)
AS(
SELECT Id, name, value1, value2, value1+value2 AS value3, a.PreviousRowId
FROM dbo.T1 a
WHERE a.PreviousRowId = 0
UNION all
SELECT a.Id, a.name, b.value1+b.value2, a.value2, b.value1+b.value2+a.value2, a.PreviousRowId
FROM dbo.T1 a
inner join CTE1 b on a.PreviousRowId = b.Id
)
select * from CTE1
and the output is like this:
Idnamevalue1value2value3PreviousRowId
111A 100100
24B 10515111
57C 1562124
80D 2172857
100E 2883680
112F 36945100
Let me know if this looks good.
May 21, 2013 at 3:36 am
Really awesome ! you are a genius !!!
Its working ....Thanks a looooooooooooot
May 21, 2013 at 3:45 am
Glad it helped. 🙂
May 21, 2013 at 5:25 am
Hi Team,
Now I have issue in with CTE ,
Msg 245, Level 16, State 1, Procedure usp_utilservice, Line 885
Conversion failed when converting the varchar value 'GrpCall-399900' to data type int.
Kindly refer the attachment
Kindly provide me solution ASAP.
May 21, 2013 at 6:11 am
This seems to be a normal issue with some datatype mismatch. Just check column by column data mapping in both the queries joined using UNION ALL and also the final result with the CTE1 definition. There you'll find some catch.
Additionally check the data in the column within your temp table.
May 21, 2013 at 6:40 am
Thanks for your response.
May 21, 2013 at 7:45 am
Did it got sorted ? What was the issue ?
May 21, 2013 at 8:56 pm
Yes 🙂 , I fixed . before i just assigned 0 instead of '0' for Groupcallid.So its considered as Integer . now i changed ,its working fine . Thanks.
May 22, 2013 at 1:18 am
Hi Sir,
Now i have another issue in relating with CTE
Kindly refer the attachment and advise me .
I have to add another table with left join , This is need to be change the value1 with case .
In attachment I boxed , Kindly check and advise
Thanks,
Chandrahasan S
May 22, 2013 at 1:45 am
Hi Chandra, first of all I'm only sqlnaive and definitely not "sir".
Secondly, just a small analysis by yourself will tell you that outer joins are not allowed in recursive CTEs and throw the same error as mentioned. To overcome this error, you can do two things:
1. Try using the LEFT JOIN outside the CTE. (this should be easy)
2. Create a table valued function with passing groupcallid as parameter and use that table valued function with OUTER APPLY.
Now learn little bit about the above and try yourself first. This will be a learning curve for you as I had mine. 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply