Get the total number of attachments of a group ID

Question

Post reply

Get the total number of attachments of a group ID

shogunSQL

Say Hey Kid

Points: 689
More actions
September 10, 2015 at 1:43 pm

#304241

Hi,
Please copy and paste the code to review sample:
Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)
)
;
insert into #temp(ID, ID_BegAttach, ID_EndAttach, ID_AttachLvl,ID_NumofAttach) values
('PM-0000320','PM-0000320','PM-0000322','0','2'),('PM-0000321','PM-0000320','PM-0000322','1',''),('PM-0000322','PM-0000320','PM-0000322','1',''),
('PM-0000420','PM-0000420','PM-0000423','0','3'),('PM-0000421','PM-0000420','PM-0000423','1',''),('PM-0000422','PM-0000420','PM-0000423','1',''),('PM-0000423','PM-0000420','PM-0000423','1','')
;
select *
from #temp
;
drop table #temp
;
I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.
Can you help please? Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Something like this?

--UPDATE t SET ID_NumofAttach = x.ID_NumofAttach

select *

from #temp t

CROSS APPLY (

SELECT SUM( CAST( i.ID_AttachLvl AS int)) ID_NumofAttach

FROM #temp i

WHERE i.ID BETWEEN t.ID_BegAttach AND t.ID_EndAttach)x;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shogunSQL Say Hey Kid Points: 689 More actions · Answer 2

Thank you Luis! But, We only need to update two rows where AttachLevel is zero. Not every row. Sorry if I didn't make it clear enough. Thanks!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

That's easy, just add a where clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shogunSQL Say Hey Kid Points: 689 More actions · Answer 4

Thank you very much for your effort Luis! I got it now!! 🙂 Cheers!

vu_tran764 SSC Journeyman Points: 78 More actions · Answer 5

Create table #temp(

DocNum nvarchar (50),

BegAttach nvarchar (50),

EndAttach nvarchar (50),

AttachLevel nvarchar (50),

AttachmentID nvarchar (50)

)

;

insert into #temp(DocNum, BegAttach, EndAttach, AttachLevel, AttachmentID) values

('ABC001','ABC001','ABC003','0','ABC002; ABC003'),('ABC002','ABC001','ABC003','1',''),('ABC003','ABC001','ABC003','1',''),

('DCF001','DCF001','DCF004','0','DCF002; DCF003; DCF004'),('DCF002','DCF001','DCF004','1',''),('DCF003','DCF001','DCF004','1',''),('DCF004','DCF001','DCF004','1','')

;

select *

from #temp

;

drop table #temp

Hi Luis,

I have another request. Can you please look into this sample data? I need to get the AttachmentID updated with all rows of the DocNum's values(with Attachlevel= 1) and have same group of BegAttach and EndAttach.

The scenario is you are provided with the first four columns , and we want to have the last column (AttachmentID) to get updated just like how you see the code above.

I know there is a function called [DelimitedSplit8K] at this site but I am not sure if the function works on this. Please let me know. Thank you so much!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I'm sorry, I can't help with that because it goes against my beliefs. You shouldn't be storing concatenated values like that. You can generate the string when querying the table, but if you store it, it will be a pain to use it for anything useful and to maintain it and keep it up to date.

So, it's possible, but don't do it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shogunSQL Say Hey Kid Points: 689 More actions · Answer 7

OK. Thank you Luis! I really do need it. I'll figure it out.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Check this for help: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shogunSQL Say Hey Kid Points: 689 More actions · Answer 9

Thank you very much Luis! I figured it out now. FOR XML PATH and STUFF to concatenate the multiple rows into a single row does do the trick. You're the BEST! Luis!