SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Basic Query Experience Only, Tricky Query Needed
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/12/2013 :  17:26:08  Show Profile  Reply with Quote
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."
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/13/2013 :  08:27:19  Show Profile  Reply with Quote
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
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/13/2013 :  10:49:30  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 01/13/2013 :  11:32:32  Show Profile  Reply with Quote
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
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/13/2013 :  11:37:49  Show Profile  Reply with Quote
Thanks, trying to figure out where to place it in my substring statement. RIGHT(REPLICATE('0', 3)

Jerry Haskins
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/13/2013 :  12:03:47  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/13/2013 :  22:04:04  Show Profile  Reply with Quote
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."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/13/2013 :  22:26:29  Show Profile  Reply with Quote
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/

Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/15/2013 :  18:03:37  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/15/2013 :  22:41:02  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/15/2013 :  23:18:08  Show Profile  Reply with Quote
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."
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/21/2013 :  09:01:54  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/21/2013 :  11:02:39  Show Profile  Reply with Quote
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
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/21/2013 :  11:50:29  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/21/2013 :  12:41:59  Show Profile  Reply with Quote
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
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/21/2013 :  12:49:04  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/23/2013 :  16:40:44  Show Profile  Reply with Quote
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
Go to Top of Page

JerryHD1
Starting Member

USA
21 Posts

Posted - 01/28/2013 :  08:23:10  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000