Author |
Topic  |
Jeff Moden
Aged Yak Warrior
USA
652 Posts |
Posted - 01/12/2013 : 17:26:08
|
quote: Originally posted by JerryHD1
Select obj_usr_num into #mytable from some_obj where obj_usr_num between '2011-10-01 00:00:00.000' and '2013-10-01 00:00:00.000' Order by obj_usr_num
Select obj_usr_num from #mytable
JD2313-10-R-0002-P00002 K33312-13-L-0001 S22133-12-W-0004-P00001 S22133-12-Z-0001 QQQ123-11-Q-0004 PPP123-12-M-0008-P00003 PPP123-12-K-0010 PPP123-12-L-0001 PPP123-98-D-0001 PPPXXX-07-L-0003-P00002
SELECT PARSENAME(REPLACE(obj_usr_num,'-','.'),4) AS first6, PARSENAME(REPLACE(obj_usr_num,'-','.'),3) AS Next2, PARSENAME(REPLACE(obj_usr_num,'-','.'),2) AS IgnorePart, PARSENAME(REPLACE(obj_usr_num,'-','.'),1) AS lastPart FROM #mytable
Is this what you are looking for? Again, the last - and 6 characters do not matter, if exist. Only concerned with information up to the first character of the 4. SO information could stop at xxxxxx-xx-x-x. The remaining 003-P00002 is not included in count.
Jerry Haskins
Not what I meant by "readily consumable", Jerry. Take the time to build your data using INSERT/SELECT statements so we can easily load the data into a table like I told you Visakh did. I just do't have the time to do it and I usually won't post coded answers unless I can test my code.
--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code: "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind: "If you want it real bad, that's the way you'll likely get it." |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/13/2013 : 08:27:19
|
Totally get what you are saying now. Thanks for the brick to the head, to help me fully understand. I have things working now and should be set. I appreciate your and others help here. I'll remember your advice for the next time. Again, many thanks.
Jerry Haskins |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/13/2013 : 10:49:30
|
visakh16 code worked great. I added a second substring and tweeked it to produce the results I needed per below example of the code and results.
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC], SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY, SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1) AS Series,
COUNT(*) AS [Award Count] FROM #awardtable GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1)
DODAAC/UIC, FY, Series, Award Count JD2313, 10, 0, 1 K33312, 13, 0, 1 S22133, 12, 0, 2 QQQ123, 11, 0, 1 PPP123, 12, 0, 3 PPP123, 98, 0, 1 PPPXXX, 07, 0, 1
In the second substring portion I added to produce the Series results, is there a way to use something like oracles RPAD or something similar to pad 3 additional 000 to the right of the series result?
Jerry Haskins |
Edited by - JerryHD1 on 01/13/2013 11:03:09 |
 |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 01/13/2013 : 11:32:32
|
quote: ...is there a way to use something like oracles RPAD or something similar to pad 3 additional 000 to the right of the series result
Use replicate function |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/13/2013 : 11:37:49
|
Thanks, trying to figure out where to place it in my substring statement. RIGHT(REPLICATE('0', 3)
Jerry Haskins |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/13/2013 : 12:03:47
|
I've tried several placements of the replicate function, but still not working. Not sure how to place it or where, in order to pad 3 000 onto the second substring result. Right now it results to 0 I need it to be padded with 000 to the right. If result is 0 then should display 0000, if result is 4, it should display as 4000. I am guessing that the padding should be in both the select statement and group by, is this correct?
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC], SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY, SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1), COUNT(*) AS [Award Count] FROM #awardtable GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)
Results now are: DODAAC/UIC, FY, Series, Award Count JD2313, 10, 0, 1 K33312, 13, 0, 1 S22133, 12, 0, 2 QQQ123, 11, 0, 1 PPP123, 12, 0, 3 PPP123, 98, 0, 1 PPPXXX, 07, 0, 1
Although third column series is 0, it could be 2, 4, 5, 8 with padding would be 2000, 4000, 5000, 8000. Hoping the replicate function would do this, such as RIGHT(REPLICATE('0', 3)
I need it to read: DODAAC/UIC, FY, Series, Award Count JD2313, 10, 0000, 1 K33312, 13, 0000, 1 S22133, 12, 0000, 2 QQQ123, 11, 0000, 1 PPP123, 12, 0000, 3 PPP123, 98, 0000, 1 PPPXXX, 07, 0000, 1
Thanks, Jerry Haskins |
Edited by - JerryHD1 on 01/13/2013 15:02:45 |
 |
|
Jeff Moden
Aged Yak Warrior
USA
652 Posts |
Posted - 01/13/2013 : 22:04:04
|
quote: Originally posted by JerryHD1
I've tried several placements of the replicate function, but still not working. Not sure how to place it or where, in order to pad 3 000 onto the second substring result. Right now it results to 0 I need it to be padded with 000 to the right. If result is 0 then should display 0000, if result is 4, it should display as 4000. I am guessing that the padding should be in both the select statement and group by, is this correct?
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC], SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY, SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1), COUNT(*) AS [Award Count] FROM #awardtable GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)
Results now are: DODAAC/UIC, FY, Series, Award Count JD2313, 10, 0, 1 K33312, 13, 0, 1 S22133, 12, 0, 2 QQQ123, 11, 0, 1 PPP123, 12, 0, 3 PPP123, 98, 0, 1 PPPXXX, 07, 0, 1
Although third column series is 0, it could be 2, 4, 5, 8 with padding would be 2000, 4000, 5000, 8000. Hoping the replicate function would do this, such as RIGHT(REPLICATE('0', 3)
I need it to read: DODAAC/UIC, FY, Series, Award Count JD2313, 10, 0000, 1 K33312, 13, 0000, 1 S22133, 12, 0000, 2 QQQ123, 11, 0000, 1 PPP123, 12, 0000, 3 PPP123, 98, 0000, 1 PPPXXX, 07, 0000, 1
Thanks, Jerry Haskins
BWAAA-HAAA!!!! Apparently, I didn't throw the brick hard enough. ;-)
--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code: "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind: "If you want it real bad, that's the way you'll likely get it." |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 01/13/2013 : 22:26:29
|
quote: Originally posted by JerryHD1
I've tried several placements of the replicate function, but still not working. Not sure how to place it or where, in order to pad 3 000 onto the second substring result. Right now it results to 0 I need it to be padded with 000 to the right. If result is 0 then should display 0000, if result is 4, it should display as 4000. I am guessing that the padding should be in both the select statement and group by, is this correct?
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC], LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) + REPLICATE('0',4),4) AS FY, SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1), COUNT(*) AS [Award Count] FROM #awardtable GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1), LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) + REPLICATE('0',4),4), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)
Results now are: DODAAC/UIC, FY, Series, Award Count JD2313, 10, 0, 1 K33312, 13, 0, 1 S22133, 12, 0, 2 QQQ123, 11, 0, 1 PPP123, 12, 0, 3 PPP123, 98, 0, 1 PPPXXX, 07, 0, 1
Although third column series is 0, it could be 2, 4, 5, 8 with padding would be 2000, 4000, 5000, 8000. Hoping the replicate function would do this, such as RIGHT(REPLICATE('0', 3)
I need it to read: DODAAC/UIC, FY, Series, Award Count JD2313, 10, 0000, 1 K33312, 13, 0000, 1 S22133, 12, 0000, 2 QQQ123, 11, 0000, 1 PPP123, 12, 0000, 3 PPP123, 98, 0000, 1 PPPXXX, 07, 0000, 1
Thanks, Jerry Haskins
Make modification as above
and you're correct that whatever you do in select needs to be repeated in group by
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/15/2013 : 18:03:37
|
Thanks Visakh16, everythng worked out great on this. I've been able to tweak it a bit as the requirement changed on a few items. No way could I have gotten this done without your help.
As for Jeff, Yes, you did hit hard enough with the brick, no cinderblock needed. I saw no reason to post more data when Visakh16 was nice enough to have already done so previously in his post. However, I did enjoy reading your BWAAA-HAAA!!!!
Jerry Haskins |
 |
|
Jeff Moden
Aged Yak Warrior
USA
652 Posts |
Posted - 01/15/2013 : 22:41:02
|
I was looking for the extended data in one of your later posts so I could show a much different method. Guess I could use Visakh's code to do so. Just thought you might be interested in helping me help you. It is your question, after all.
--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code: "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind: "If you want it real bad, that's the way you'll likely get it." |
Edited by - Jeff Moden on 01/15/2013 22:43:35 |
 |
|
Jeff Moden
Aged Yak Warrior
USA
652 Posts |
Posted - 01/15/2013 : 23:18:08
|
Here's my rendition.
Jerry (the OP) has very clearly stated that the data will be in a certain format for the first 4 "fields" of the data and has given several examples. They all show up in the following test data. I don't use table variables for this because I find it easier to work with persistent data but that's a personal choice.
I also took the liberty of doctoring up the 4th field to show the OP's latest requirement.
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL DROP TABLE #MyHead;
--===== Build and populate the test table on the fly.
SELECT *
INTO #MyHead
FROM (
SELECT 'JD2313-10-R-0002' UNION ALL
SELECT 'K33312-13-L-0001' UNION ALL
SELECT 'S22133-12-W-0004' UNION ALL
SELECT 'S22133-12-Z-0001' UNION ALL
SELECT 'QQQ123-11-Q-0004' UNION ALL
SELECT 'PPP123-12-M-0008' UNION ALL
SELECT 'PPP123-12-K-0010' UNION ALL
SELECT 'PPP123-12-L-0001' UNION ALL
SELECT 'PPP123-98-D-0001' UNION ALL
SELECT 'PPPXXX-07-L-0003' UNION ALL
SELECT 'JD2313-10-R-0002-P00002' UNION ALL
SELECT 'K33312-13-L-0001' UNION ALL
SELECT 'S22133-12-W-1004-P00001' UNION ALL
SELECT 'S22133-12-Z-1001' UNION ALL
SELECT 'QQQ123-11-Q-2004' UNION ALL
SELECT 'PPP123-12-M-2008-P00003' UNION ALL
SELECT 'PPP123-12-K-2010' UNION ALL
SELECT 'PPP123-12-L-3001' UNION ALL
SELECT 'PPP123-98-D-5001' UNION ALL
SELECT 'PPPXXX-07-L-1003-P00002'
) d (obj_usr_num)
;
Given the OP's requirements, here's some very simple code with the understanding that the fields lengths are, in fact, fixed for this data.
--===== Solve the given problem
WITH
cteParse AS
(
SELECT [DODAAC/UIC] = SUBSTRING(obj_usr_num,1,6)
, FY = SUBSTRING(obj_usr_num,8,2)
, Series = SUBSTRING(obj_usr_num,13,1) + '000'
FROM #MyHead
)
SELECT [DODAAC/UIC]
, FY
, Series
, [Award Count] = COUNT(*)
FROM cteParse
GROUP BY [DODAAC/UIC],FY,Series
ORDER BY [DODAAC/UIC],FY,Series
Here's what the output looks like for the given data...
DODAAC/UIC FY Series Award Count
---------- ---- ------ -----------
JD2313 10 0000 2
K33312 13 0000 2
PPP123 12 0000 3
PPP123 12 2000 2
PPP123 12 3000 1
PPP123 98 0000 1
PPP123 98 5000 1
PPPXXX 07 0000 1
PPPXXX 07 1000 1
QQQ123 11 0000 1
QQQ123 11 2000 1
S22133 12 0000 2
S22133 12 1000 2
(13 row(s) affected)
Let me know if the requirements have changed again by posting the data in a readily consumable format as I have done above. 
--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code: "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind: "If you want it real bad, that's the way you'll likely get it." |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/21/2013 : 09:01:54
|
Since I've seen no advice given, I figured I would repost. Perhaps visakh16 could help, since I am using most of his helpfull information.
--Creating the test table
create table #awardtable (obj_usr_num varchar(30) null)
-- Seed the test table
insert #awardtable values ( 'JD2313-10-R-0002' ) insert #awardtable values ( 'K33312-13-L-0001' ) insert #awardtable values ( 'K33312-13-L-0001-1000' ) insert #awardtable values ( 'K33312-13-L-2001-4000' ) insert #awardtable values ( 'S22133-12-W-0004' ) insert #awardtable values ( 'S22133-12-Z-0001' ) insert #awardtable values ( 'QQQ123-11-Q-0004' ) insert #awardtable values ( 'PPP123-12-M-0008' ) insert #awardtable values ( 'PPP123-12-K-0010' ) insert #awardtable values ( 'PPP123-98-D-0001' ) insert #awardtable values ( 'JD2313-10-R-0002-P00002' ) insert #awardtable values ( 'S22133-12-W-1004-P00001' ) insert #awardtable values ( 'S22133-12-Z-1001' ) insert #awardtable values ( 'QQQ123-11-Q-2004' ) insert #awardtable values ( 'PPP123-12-M-2008-P00003' ) insert #awardtable values ( 'PPPXXX-07-L-1003-P00002' )
--Pull needed data
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC],
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY,
LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1) + REPLICATE('0',4),4) AS Series , COUNT(*) AS [Award Count]
FROM #awardtable
GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1),
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) ,
LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)+ REPLICATE('0',4),4)
--Output/Results
DODAAC/UIC FY Series Count __________ ___ ______ _____ JD2313 10 0000 2 K33312 13 0000 2 K33312 13 2000 1 PPP123 12 0000 2 PPP123 12 2000 1 PPP123 98 0000 1 PPPXXX 07 1000 1 QQQ123 11 0000 1 QQQ123 11 2000 1 S22133 12 0000 2 S22133 12 1000 2
If you run a Select * From #awardtable You receive the following data.
obj_usr_num
JD2313-10-R-0002 K33312-13-L-0001 K33312-13-L-0001-1000 K33312-13-L-2001-4000 S22133-12-W-0004 S22133-12-Z-0001 QQQ123-11-Q-0004 PPP123-12-M-0008 PPP123-12-K-0010 PPP123-98-D-0001 JD2313-10-R-0002-P00002 S22133-12-W-1004-P00001 S22133-12-Z-1001 QQQ123-11-Q-2004 PPP123-12-M-2008-P00003 PPPXXX-07-L-1003-P00002
New Requirement needed.
If the inserted value is 16 characters/spaces in length, or if the 18th character/space is a P, it goes into lets's say a Contracts Result Table. We would still keep the column names and selected data the same as they now are. So if we look at the values JD2313-10-R-0002, JD2313-10-R-0002-P00002, and K33312-13-L-0001-4000. The first two values would be in the Contracts Result Table, but the last value would be ignored and not counted in our first Contracts Results Table. The reason is because the 18th character/space is numeric and not a P.
Anything else (18th character is a number), call it a Delivery Order Results Table with the same columns and selected data we now use. So any obj_sur_num which has a numeric value in the 18th position, would only appear in the second Delivery Orders Table.
I hope this makes sense to everyone.
New Requirement Output
Contracts Report Table
DODAAC/UIC FY Series Count __________ ___ ______ _____
JD2313 10 0000 2 K33312 13 0000 1 PPP123 12 0000 2 PPP123 12 2000 1 PPP123 98 0000 1 PPPXXX 07 1000 1 QQQ123 11 0000 1 QQQ123 11 2000 1 S22133 12 0000 2 S22133 12 1000 2
Delivery Order Report Table
DODAAC/UIC FY Series Count __________ ___ ______ _____
K33312 13 0000 1 K33312 13 2000 1
I hope this all makes sense. Sorry the table format is not correcting as the previous one did.
Jerry Haskins |
Edited by - JerryHD1 on 01/21/2013 09:03:54 |
 |
|
Jeff Moden
Aged Yak Warrior
USA
652 Posts |
Posted - 01/21/2013 : 11:02:39
|
quote: Originally posted by JerryHD1
Since I've seen no advice given...
Seriously? You didn't look at my post just above yours?
Also, start posting readily consumable data like I suggested several posts back. You'll get better participation that way.
--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code: "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind: "If you want it real bad, that's the way you'll likely get it." |
Edited by - Jeff Moden on 01/21/2013 11:03:49 |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/21/2013 : 11:50:29
|
I did see your post and I responded, but since I saw no reply's, I desided to post again. I had stated that there is an error with the WITH Keyword. Apparently, Sybase does not like it.
I guess I am not understand what you consider readily consummable data. I took this to be readily consummable date.
create table #awardtable (obj_usr_num varchar(30) null)
-- Seed the test table
insert #awardtable values ( 'JD2313-10-R-0002' ) insert #awardtable values ( 'K33312-13-L-0001' ) insert #awardtable values ( 'K33312-13-L-0001-1000' ) insert #awardtable values ( 'K33312-13-L-2001-4000' ) insert #awardtable values ( 'S22133-12-W-0004' ) insert #awardtable values ( 'S22133-12-Z-0001' ) insert #awardtable values ( 'QQQ123-11-Q-0004' ) insert #awardtable values ( 'PPP123-12-M-0008' ) insert #awardtable values ( 'PPP123-12-K-0010' ) insert #awardtable values ( 'PPP123-98-D-0001' ) insert #awardtable values ( 'JD2313-10-R-0002-P00002' ) insert #awardtable values ( 'S22133-12-W-1004-P00001' ) insert #awardtable values ( 'S22133-12-Z-1001' ) insert #awardtable values ( 'QQQ123-11-Q-2004' ) insert #awardtable values ( 'PPP123-12-M-2008-P00003' ) insert #awardtable values ( 'PPPXXX-07-L-1003-P00002' )
--Pull needed data
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC],
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY,
LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1) + REPLICATE('0',4),4) AS Series ,
COUNT(*) AS [Award Count]
FROM #awardtable
GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1),
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) ,
LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)+ REPLICATE('0',4),4)
Sorry, just not getting it.
Jerry Haskins |
 |
|
jimf
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 01/21/2013 : 12:41:59
|
You may get better help in a Sybase forum, this is MS SQL Server forum. Visakh and Jeff both gave legitimate answers for SQL, but they may not translate to Sybase.
Jim
Everyday I learn something that somebody else already knew |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/21/2013 : 12:49:04
|
You have a good point Jim, Visakh and Jeff both gave legitimate answers to my questions. I have thus far been able to tweak their responses to fit Sybase, but figured I would post my additional requirement.
Thanks,
Jerry Haskins |
 |
|
Jeff Moden
Aged Yak Warrior
USA
652 Posts |
Posted - 01/23/2013 : 16:40:44
|
quote: Originally posted by JerryHD1
I did see your post and I responded, but since I saw no reply's, I desided to post again. I had stated that there is an error with the WITH Keyword. Apparently, Sybase does not like it.
I guess I am not understand what you consider readily consummable data. I took this to be readily consummable date.
create table #awardtable (obj_usr_num varchar(30) null)
-- Seed the test table
insert #awardtable values ( 'JD2313-10-R-0002' ) insert #awardtable values ( 'K33312-13-L-0001' ) insert #awardtable values ( 'K33312-13-L-0001-1000' ) insert #awardtable values ( 'K33312-13-L-2001-4000' ) insert #awardtable values ( 'S22133-12-W-0004' ) insert #awardtable values ( 'S22133-12-Z-0001' ) insert #awardtable values ( 'QQQ123-11-Q-0004' ) insert #awardtable values ( 'PPP123-12-M-0008' ) insert #awardtable values ( 'PPP123-12-K-0010' ) insert #awardtable values ( 'PPP123-98-D-0001' ) insert #awardtable values ( 'JD2313-10-R-0002-P00002' ) insert #awardtable values ( 'S22133-12-W-1004-P00001' ) insert #awardtable values ( 'S22133-12-Z-1001' ) insert #awardtable values ( 'QQQ123-11-Q-2004' ) insert #awardtable values ( 'PPP123-12-M-2008-P00003' ) insert #awardtable values ( 'PPPXXX-07-L-1003-P00002' )
Yeah... that's more like it.
I know squat about Sybase except that it's the "father" of SQL Server and that the two products have diverged quite a bit. Since CTEs (the "WITH" thing) aren't working for ya, I'll see what I can do about using a similar "derived table" solution for you when I get home from work.
--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code: "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind: "If you want it real bad, that's the way you'll likely get it." |
Edited by - Jeff Moden on 01/23/2013 16:41:28 |
 |
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/28/2013 : 08:23:10
|
Thanks for all your help Jeff and visakh16. I was able to complete my task with the additional requirement. The following is my completed script which I hope you can apply and see the result sets I receive. Although SQL Server and Sybase Server are indeed different, they are enough alike I think to have made it possible to take your help and apply it to achieve my gaol. Thanks again.
create table #awardtable (obj_usr_num varchar(30) null)
-- Seed the test table
insert #awardtable values ( 'JD2313-10-R-0002' ) insert #awardtable values ( 'K33312-13-L-0001' ) insert #awardtable values ( 'K33312-13-L-0001-1000' ) insert #awardtable values ( 'K33312-13-L-2001-4000' ) insert #awardtable values ( 'S22133-12-W-0004' ) insert #awardtable values ( 'S22133-12-Z-0001' ) insert #awardtable values ( 'QQQ123-11-Q-0004' ) insert #awardtable values ( 'PPP123-12-M-0008' ) insert #awardtable values ( 'PPP123-12-K-0010' ) insert #awardtable values ( 'PPP123-98-D-0001' ) insert #awardtable values ( 'JD2313-10-R-0002-P00002' ) insert #awardtable values ( 'S22133-12-W-1004-P00001' ) insert #awardtable values ( 'S22133-12-Z-1001' ) insert #awardtable values ( 'QQQ123-11-Q-2004' ) insert #awardtable values ( 'PPP123-12-M-2008-P00003' ) insert #awardtable values ( 'PPPXXX-07-L-1003-P00002' )
--Pull needed data
--CONTRACTS select Distinct obj_usr_num into #awardtable1 from #awardtable where (substring(obj_usr_num, 18, 1) not in ('0','1','2','3','4','5','6','7','8','9') or substring(obj_usr_num, 18, 1) is null) Order by obj_usr_num asc
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC], SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY, LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1) + REPLICATE('0',4),4) AS Series,
COUNT(*) AS [Award Count] FROM #awardtable1 GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) , LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)+ REPLICATE('0',4),4)
Select * From #awardtable1
Drop Table #awardtable1
--Delivery Orders select Distinct obj_usr_num into #awardtable2 from #awardtable Where substring(obj_usr_num, 18, 1) in ('0','1','2','3','4','5','6','7','8','9') Order by obj_usr_num asc
SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC], SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY, LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1) + REPLICATE('0',4),4) AS Series,
COUNT(*) AS [Award Count] FROM #awardtable2 GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1), SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) , LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)+ REPLICATE('0',4),4)
Select * From #awardtable2
Drop Table #awardtable2
Drop Table #awardtable
Jerry Haskins |
 |
|
Topic  |
|
|
|