Return only records where the first 2 digit of Acct. Numbers = 20

Transact-SQL
1

Hi Experts,
I am new to SQL programming and would appreciate some help. I have a table called Table1 with Account numbers. All the records are in a single table.

Acct_Numbers
10-1210-000-0000
10-1310-000-0000
20-1000-032-0000
20-1000-035-0000
20-3200-026-0000
20-3214-214-0000
20-4411-231-0000
20-4430-359-0800
30-1210-000-0000
20-1000-045-0000
20-1000-046-0000
20-4000-260-0000
60-1990-910-0070
60-1990-910-0090
60-1990-910-3130
60-1990-910-3200
60-1990-910-3208
60-1330-000-0572
10-3177-000-0000
10-3178-000-0000
20-3200-360-0000
20-1000-027-0000
30-5200-000-0000
20-4511-451-0000
20-4513-452-0000
20-4514-453-0000

I would like a result set of only the records where the first segment, (first two digits) of the accounts equal 20 ..
SUBSTRING(Acct_Numbers,1,2) AS Fund .. and then group them by the third segments.. SUBSTRING(Acct_Numbers,9,3) AS Program..

The first result set should be like
Acct_Numbers
20-1000-032-0000
20-1000-035-0000
20-3200-026-0000
20-3214-214-0000
20-4411-231-0000
20-4430-359-0800
20-1000-045-0000
20-1000-046-0000
20-4000-260-0000
20-3200-360-0000
20-1000-027-0000
20-4511-451-0000
20-4513-452-0000
20-4514-453-0000

then Group by 3rd segment

I am not sure which is the most efficient way to accomplish this, do I need to use a temp table, CTE ..Etc.

Thanks
Hogie

2

What exactly do you mean by:

Please show end result.

3

Hi Bitsmed,

I will need to use ( SUBSTRING(Acct_Numbers,9,3) AS Program )
and them group by results of this SUBSTRING.

032 - is the 3rd segment of the account number that is return from this ( SUBSTRING(Acct_Numbers,9,3)
20-1000-032-0000
20-1000-032-0001
20-1000-032-1003

035 - is the 3rd segment of the account number that is return from this ( SUBSTRING(Acct_Numbers,9,3)
20-2000-035-8762
20-2316-035-1234
20-4623-035-3459

This is going to be used in SSRS report so I can probably user SUBSTRING(Acct_Numbers,9,3) AS Program
and group and sub-total by program ( from the SUBSTRING Alias) , unless there is an easier way to do this in SQL,
If the grouping by the 3rd segment is complicated then I will really appreciate a nice way of returning only the
record where the first two digits of the Acct_Numbere = 20..

Thank You

4

If this is going to be output to SSRS - then just create the columns as you need and do the grouping/totaling in the report.

SELECT SUBSTRING(Acct_Numbers,9,3) As Program, ...

In the report - group on Program

If you must group/total in SQL (which will not allow you to drill-down in the report) - then:

SELECT SUBSTRING(Acct_Numbers,9,3) As Program, ...
...
GROUP BY SUBSTRING(Acct_Numbers,9,3)

I prefer setting up stored procedures that return the detail data to the report and then building the report to perform grouping/totals. This way, I can easily create multiple reports - a detail report, summary report by program, summary report by fund, etc... - and can setup drilldown or drill-through reports or subreports as needed with a single source (can be a shared dataset if needed - with appropriate filters available for each report).

5

Thank you for your suggestions,
I also need a result set which contain only those Account which have 20- as the first two digits of the accounts numbers

20-1000-035-0000
20-3200-026-0000
20-3214-214-0000
20-4411-231-0000
20-4430-359-0800
20-1000-045-0000
20-1000-046-0000
20-4000-260-0000
20-3200-360-0000
20-1000-027-0000
20-4511-451-0000
20-4513-452-0000
20-4514-453-0000

what's the best way to accomplish this... Thanks

6 

DECLARE @t table(acct varchar(20));
INSERT @t ( acct )
VALUES ( '10-1210-000-0000' )
     , ( '10-1310-000-0000' )
     , ( '20-1000-032-0000' )
     , ( '20-1000-035-0000' )
     , ( '20-3200-026-0000' )
     , ( '20-3214-214-0000' )
     , ( '20-4411-231-0000' )
     , ( '20-4430-359-0800' )
     , ( '30-1210-000-0000' )
     , ( '20-1000-045-0000' )
     , ( '20-1000-046-0000' )
     , ( '20-4000-260-0000' )
     , ( '60-1990-910-0070' )
     , ( '60-1990-910-0090' )
     , ( '60-1990-910-3130' )
     , ( '60-1990-910-3200' )
     , ( '60-1990-910-3208' )
     , ( '60-1330-000-0572' )
     , ( '10-3177-000-0000' )
     , ( '10-3178-000-0000' )
     , ( '20-3200-360-0000' )
     , ( '20-1000-027-0000' )
     , ( '30-5200-000-0000' )
     , ( '20-4511-451-0000' )
     , ( '20-4513-452-0000' )
     , ( '20-4514-453-0000' );
SELECT
     Max(t.acct) max_acct
   , Substring(t.acct, 1, 2) Segment1
   , Substring(t.acct, 9, 3) Program
FROM
   @t t
WHERE  t.acct LIKE  '20%'
GROUP BY Substring(t.acct, 1, 2),Substring(t.acct, 9, 3);
7

You don't have duplicate rows for any section 3 value, so I don't know for sure how you plan to resolve dups there. Not even really sure exactly what you mean. But just to be able to write some code, I'll assume you want the MIN section 2 value and MAX section 4 value if the section 3 values for 2 or more rows match.

SELECT Acct_Number
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY SUBSTRING(Acct_Number, 9, 3) 
        ORDER BY SUBSTRING(Acct_Number, 4, 4), SUBSTRING(Acct_Number, 13, 4) DESC) AS row_num
    FROM dbo.Table1
    WHERE Acct_Number LIKE '20%'
) AS derived
WHERE row_num = 1
8

Thank you all for your expertise, I really appreciate the help..

9

You're welcome. Glad it helped.