Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JJ297
Aged Yak Warrior
940 Posts |
 Posted - 2008-10-21 : 11:44:31
|
| How would I write this...I have a total of 2840 records in table Diary10_3I want to add 1 more record to the total of records if field Recno = 02. Is this possible to do via SQL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-21 : 11:49:44
|
| You mean insert to table or return along with resultset?Also wat should be its values? |
 |
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-21 : 12:05:14
|
| Yes I want to return along with result set. Don't know what you mean by values? I am missing 35 records because it's not counting duplicates and I can tell if it's a duplicate if the field RECNO='02'RGN AREA DIST PAN RECNO 03 H23 202 144865 02 So this should add another number to the count. I hope this makes sense. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-21 : 13:01:26
|
| you need provide some data sample to make us understand based on what you determine duplicates. Can you provide it please? |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-21 : 14:21:38
|
| okay here's some sample data.In the first record below I need to pull out 5, 7 and 9W's (you've heard this one before). I'm doing this all fine in SSIS but my count is off because this record has a 5W and 7W on the same line so I need to really count this record twice. So if Recno ='02'(Recno is the field in front of the name) then that record should be counted twice. So I should have a total of 6 records because of the 5W and 7W on the same line. Does this make sense? 03 H20 245 242 02 SAL ZOH 025W2008090317W200809111 03 H20 245 242 05 NAT FLO 01MR201506240 03 H20 245 242 03 DEM DIX 01MR200801210 03 H20 245 242 02 DEV MART 025W2008020107W200809071 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 00:47:56
|
| How are you currently taking the records in SSIS? |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-22 : 09:33:25
|
| 1. Flat File2. Conditional Split to weed out 5W, 7W, 9W's3. Non-rows go to Discard Rows Distination4. Valid Rows do to Weekly DiaryI have 16 diaries to look up so the fields are called DryCd1 - DryCd-16If I have a 5W in DryCd1 and a 7W in DryCd2 it's not picking up the count for DryCD2.The data looks like this from the flat file055W2008091312E2008101307W2008091715H2008092905B200810040This is DryCd1 - DryCd3 fields. SSIS is only picking up the 5W and not counting the 7W. So this is making my count off. The file is coming from the mainframe. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 10:54:03
|
| what you could do here is to include a sql command task to add the count in each of output paths if string contain occurance of any other pattern. |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-22 : 11:00:05
|
How would I write that  Something like...If DryDt2 = '5W', '7W' or '9W' then add row to weeklyDiaries or add count to weeklydiaries? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 11:12:59
|
quote:
Originally posted by JJ297
How would I write that Something like...If DryDt2 = '5W', '7W' or '9W' then add row to weeklyDiaries or add count to weeklydiaries?
What i was suggesting was to include the additinal check in each of the output to look for others. Like in 5W output path check if PATINDEX('%7W%',field) >0 then count=count+ 1 also PATINDEX('%9W%',field) >0 then count=count+ 1. Similarly for other two o/ps |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-22 : 11:26:03
|
| Okay will give it a try stay tuned... Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 11:42:57
|
quote:
Originally posted by JJ297
Okay will give it a try stay tuned... Thanks!
cheers |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-22 : 12:17:52
|
| Okay I'm back...Tried writing it but it's missing something...FINDSTRING(DryCd1,"5W",1) > 0 || FINDSTRING(DryCd1,"7W",1) > 0 || FINDSTRING(DryCd1,"9W",1) > 0 || FINDSTRING(DryCd2,"5W",1) > 1 || FINDSTRING(DryCd2,"7W",1) > 1 || FINDSTRING(DryCd2,"9W",1) > 1 || if PATINDEX('%5W%',DryCd2) > 0 then count=count +1 end if || FINDSTRING(DryCd3,"5W",1) > 1 || FINDSTRING(DryCd3,"7W",1) > 1 || FINDSTRING(DryCd3,"9W",1) > 1 || FINDSTRING(DryCd4,"5W",1) > 1 || FINDSTRING(DryCd4,"7W",1) > 1 || FINDSTRING(DryCd4,"9W",1) > 1 || FINDSTRING(DryCd5,"5W",1) > 1 || FINDSTRING(DryCd5,"7W",1) > 1 || FINDSTRING(DryCd5,"9W",1) > 1 || FINDSTRING(DryCd6,"5W",1) > 1 || FINDSTRING(DryCd6,"7W",1) > 1 || FINDSTRING(DryCd6,"9W",1) > 1 || FINDSTRING(DryCd7,"5W",1) > 1 || FINDSTRING(DryCd7,"7W",1) > 1 || FINDSTRING(DryCd7,"9W",1) > 1 || FINDSTRING(DryCd8,"5W",1) > 1 || FINDSTRING(DryCd8,"7W",1) > 1 || FINDSTRING(DryCd8,"9W",1) > 1 || FINDSTRING(DryCd9,"5W",1) > 1 || FINDSTRING(DryCd9,"7W",1) > 1 || FINDSTRING(DryCd9,"9W",1) > 1 || FINDSTRING(DryCd10,"5W",1) > 1 || FINDSTRING(DryCd10,"7W",1) > 1 || FINDSTRING(DryCd10,"9W",1) > 1 || FINDSTRING(DryCd11,"5W",1) > 1 || FINDSTRING(DryCd11,"7W",1) > 1 || FINDSTRING(DryCd11,"9W",1) > 1 || FINDSTRING(DryCd12,"5W",1) > 1 || FINDSTRING(DryCd12,"7W",1) > 1 || FINDSTRING(DryCd12,"9W",1) > 1 || FINDSTRING(DryCd13,"5W",1) > 1 || FINDSTRING(DryCd13,"7W",1) > 1 || FINDSTRING(DryCd13,"9W",1) > 1 || FINDSTRING(DryCd14,"5W",1) > 1 || FINDSTRING(DryCd14,"7W",1) > 1 || FINDSTRING(DryCd14,"9W",1) > 1 || FINDSTRING(DryCd15,"5W",1) > 1 || FINDSTRING(DryCd15,"7W",1) > 1 || FINDSTRING(DryCd15,"9W",1) > 1 || FINDSTRING(DryCd16,"5W",1) > 1 || FINDSTRING(DryCd16,"7W",1) > 1 || FINDSTRING(DryCd16,"9W",1) > 1 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 12:42:22
|
| where are you giving above expression? |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-22 : 12:59:05
|
| in the conditional split.Do I get rid fo the Findstring and only use Patindex if so do I put an if in front of it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 13:50:14
|
quote:
Originally posted by JJ297
in the conditional split.Do I get rid fo the Findstring and only use Patindex if so do I put an if in front of it?
nope dont give the enter check in main conditional split. the additional checks should be given in various outputs from conditional splits. |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2008-10-22 : 13:55:08
|
| Okay thanks got it! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-23 : 00:26:18
|
quote:
Originally posted by JJ297
Okay thanks got it!
cheers  |
|
|
|
|
|
|
|
|
Writing stored procedure