Solved

Returning 100 rows of data

Posted on 2004-09-16
10
219 Views
Last Modified: 2012-06-27
The attached query below works fine. I just want to modify it to pull exactly 100 rows of data. For instance if there is 4 different types of bill types then it should return 25 for each bill type to return 100, but if there 4 different bill types and only 2 have over 25 rows and the other two are short of 25 I want it to grab additional rows from the two that have additiional rows to spare to equal 100 total. There is no set number of bill types that can return it is random so the query has to figure out how many are present and divide evenly to pull 100 rows even if there are 7 bill types.


DROP TABLE HNS
CREATE TABLE HNS (PHS_CMPNY_NUM VARCHAR(2), SUBSCBR_NUM INT,
MBR_SUFFIX VARCHAR(2), CLAIM_NUM SMALLINT, ADJUSTER_ID VARCHAR(3),
CLAIM_TYPE VARCHAR(2), CURR_CLAIM_STATUS VARCHAR(2), TYPE_OF_BILL NVARCHAR(50),
PLAN_TYPE VARCHAR(2), PROCESS_DATE DATETIME, PAYMENTS VARCHAR(53))
DECLARE MYCURS CURSOR FOR
SELECT DISTINCT A.TYPE_OF_BILL
FROM [AUDIT REPORT (5)] A, [AUDIT REPORT (8)] C
WHERE A.ADJUSTER_ID = 'SYS' AND A.CLAIM_TYPE IN ('OB', 'OC', 'OJ',
'OM', 'OU', 'IA', 'ID', 'IG', 'OG')  AND A.CURR_CLAIM_STATUS LIKE '5%'
AND CAST(A.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(A.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(A.CLAIM_NUM AS VARCHAR(3)) <>  CAST(C.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(C.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(C.CLAIM_NUM AS VARCHAR(3))
AND A.TYPE_OF_BILL <> ''

DECLARE @TYPE_OF_BILL NVARCHAR(50)
OPEN MYCURS
FETCH NEXT FROM MYCURS INTO @TYPE_OF_BILL
WHILE @@FETCH_STATUS <> -1
BEGIN
INSERT INTO HNS
SELECT DISTINCT TOP 22 PERCENT A.PHS_CMPNY_NUM, A.SUBSCBR_NUM, A.MBR_SUFFIX, A.CLAIM_NUM, A.ADJUSTER_ID,
A.CLAIM_TYPE, A.CURR_CLAIM_STATUS, A.TYPE_OF_BILL, A.PLAN_TYPE, A.PROCESS_DATE, A.PAYMENTS
FROM [AUDIT REPORT (5)] A, [AUDIT REPORT (8)] C
WHERE A.ADJUSTER_ID = 'SYS' AND A.CLAIM_TYPE IN ('OB', 'OC', 'OJ',
'OM', 'OU', 'IA', 'ID', 'IG', 'OG')  AND A.CURR_CLAIM_STATUS LIKE '5%' AND
CAST(A.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(A.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(A.CLAIM_NUM AS VARCHAR(3)) <>  CAST(C.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(C.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(C.CLAIM_NUM AS VARCHAR(3))
AND A.TYPE_OF_BILL = @TYPE_OF_BILL

FETCH NEXT FROM MYCURS INTO @TYPE_OF_BILL
END
CLOSE MYCURS
DEALLOCATE MYCURS

SELECT * FROM HNS
0
Comment
Question by:groyal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12077874
You're probably going to need to run through data more than once to do this and might have to resort to dynamic sql.

You need to know distinct # billtypes, and # of records for each bill type. I'd probably do this into a table temp with billtype and #records and a column for the number of records to return for this billtype

Then you can calculate desired # of records for each as 100/#bills
Then you would need to sum the # records you will return for those billtypes with less than the desired number
Take this number from 100 would then need to be divided by those bill types that you have over the mininum
Repeat above 2 steps until you get to 100

Say you have this
Billtype # records
1        100
2        100
3        10
4        40

Desired #/billtype = 25
total where less than 25 = 10
Desired #2/billtype2 = 90/3 = 30

End results
Billtype # records Return#
1        100           30
2        100           30
3        10             10
4        40             30

You could then write a dynamic sql statement to return the top return# for each billtype.

Another way would be to first calculate the desired # per if they all returned the max
Select these records (select top x for each group) into your table
New Total # - # returned above
Insert again and keep repeating until you get the total number you want.

You wont be able to get out of looping (although looping can be done without a cursor)

Chris



0
 
LVL 2

Expert Comment

by:KhunJean
ID: 12078064
Hmm.

At least you should know how many billtypes there are and how many records per bill type
create table #Temp (
  Type_of_Bill int,
  RecordCount int,
  GetRecordCount int
)
insert into #Temp ( Type_of_Bill, RecordCount)
select Type_of_Bill, count(1)
from AuditReport
group by Type_of_Bill

set @RowsPerType = 100 / @@ROWCOUNT -- Calculate then number of rows per billtype

-- Now update #Temp and set the GetRecordCount to this number unless there are not enough records.
update #Temp
    set GetRecordCount = case when RecordCount >= @RowsPertype then
                                          @RowsPerType
                                     else
                       RecordCount
                                     end
--Get the total count of records to get.
select @Total = sum(GetRecordCount) from #Temp


if @Total < 100 begin
  -- This is the case when there are bill types with not enough records.
   set @Remaining = 100 - @Total
--   Evenly divide the remaining over the billtypes that have more records available.
   update #Temp
      set GetRecordNumber = GetRecordNumber +
           case when RecordCount - GetRecordNumber > 0 then
                 @Remaining / (RecordCount - GetRecordCount)
           else
                 0
           end
end

declare  MyCursor cursor for
select Type_of_Bill, GetRecordCount
from   #Temp

declare @Type_of_Bill int, @GetRecordCount int
open myCursor
fetch next from myCursor into @Type_of_Bill, @GetRecordCount

while  @@FETCH_STATUS <> -1 begin
   set rowcount @GetRecordCount

--    Do your select into HNS from the table where Type_of_Bill = @Type_of_Bill
 
    fetch next from myCursor into @Type_of_Bill, @GetRecordCount
end
set rowcount 0
0
 
LVL 2

Expert Comment

by:KhunJean
ID: 12078085
Chris,

I was typing a lot but you are a little faster.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Expert Comment

by:KhunJean
ID: 12078186
Sorry about the error.

Substitue the if statement part with this.

declare @Remaining decimal(9,4), @Factor decimal(9,4)
if @Total < 100 begin
   set @Remaining = 100 - @Total
   set @Factor = @Remaining / ( select sum(GetRecordCount) from #Temp)
   update #Temp
     set GetRecordNumber = GetRecordNumber +
         Case when RecordCount - GetRecordNumber > 0 then
                   @Factor * (RecordCount - GetRecordCount)
         else
                   0
         end
end
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12078331
No problem, mine was just the logic, yours includes the code!
0
 

Author Comment

by:groyal
ID: 12078736
My syntax could be wrong could you please check it for me and make any corrections needed. Thanks.


CREATE TABLE TEMPCOUNT (TYPE_OF_BILL INT, RECORDCOUNT INT, GETRECORDCOUNT INT)
INSERT INTO TEMPCOUNT (TYPE_OF_BILL, RECORDCOUNT)
SELECT A.TYPE_OF_BILL, COUNT(1)
FROM [AUDIT REPORT (5)] A, [AUDIT REPORT (8)] C
WHERE A.ADJUSTER_ID = 'SYS' AND A.CLAIM_TYPE IN ('OB', 'OC', 'OJ',
'OM', 'OU', 'IA', 'ID', 'IG', 'OG')  AND A.CURR_CLAIM_STATUS LIKE '5%'
AND CAST(A.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(A.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(A.CLAIM_NUM AS VARCHAR(3)) <>  CAST(C.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(C.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(C.CLAIM_NUM AS VARCHAR(3))
AND A.TYPE_OF_BILL <> ''
GROUP BY TYPE_OF_BILL

SET @@RowPerType = 100/@@ROWCOUNT

UPDATE TEMPCOUNT
SET GETRECORDCOUNT = CASE WHEN RecordCount >= @RowPerType then
@RowPerType
else
RecordCount
end

SELECT @TOTAL = SUM(GetRecordCount) From TempCount

declare @Remaining decimal(9,4), @Factor decimal(9,4)
if @Total < 100 begin
   set @Remaining = 100 - @Total
   set @Factor = @Remaining / ( select sum(GetRecordCount) from #Temp)
   update #Temp
     set GetRecordNumber = GetRecordNumber +
         Case when RecordCount - GetRecordNumber > 0 then
                   @Factor * (RecordCount - GetRecordCount)
         else
                   0
         end
end



CREATE TABLE HNS (PHS_CMPNY_NUM VARCHAR(2), SUBSCBR_NUM INT,
MBR_SUFFIX VARCHAR(2), CLAIM_NUM SMALLINT, ADJUSTER_ID VARCHAR(3),
CLAIM_TYPE VARCHAR(2), CURR_CLAIM_STATUS VARCHAR(2), TYPE_OF_BILL NVARCHAR(50),
PLAN_TYPE VARCHAR(2), PROCESS_DATE DATETIME, PAYMENTS VARCHAR(53))
DECLARE MYCURS CURSOR FOR
SELECT DISTINCT A.TYPE_OF_BILL
FROM [AUDIT REPORT (5)] A, [AUDIT REPORT (8)] C
WHERE A.ADJUSTER_ID = 'SYS' AND A.CLAIM_TYPE IN ('OB', 'OC', 'OJ',
'OM', 'OU', 'IA', 'ID', 'IG', 'OG')  AND A.CURR_CLAIM_STATUS LIKE '5%'
AND CAST(A.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(A.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(A.CLAIM_NUM AS VARCHAR(3)) <>  CAST(C.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(C.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(C.CLAIM_NUM AS VARCHAR(3))
AND A.TYPE_OF_BILL <> ''

DECLARE @TYPE_OF_BILL NVARCHAR(50), @GetRecordCount Int
OPEN MYCURS
FETCH NEXT FROM MYCURS INTO @TYPE_OF_BILL, @GetRecordCount

WHILE @@FETCH_STATUS <> -1

set rowcount @GetRecordCount

BEGIN
INSERT INTO HNS
SELECT DISTINCT TOP 22 PERCENT A.PHS_CMPNY_NUM, A.SUBSCBR_NUM, A.MBR_SUFFIX, A.CLAIM_NUM, A.ADJUSTER_ID,
A.CLAIM_TYPE, A.CURR_CLAIM_STATUS, A.TYPE_OF_BILL, A.PLAN_TYPE, A.PROCESS_DATE, A.PAYMENTS
FROM [AUDIT REPORT (5)] A, [AUDIT REPORT (8)] C
WHERE A.ADJUSTER_ID = 'SYS' AND A.CLAIM_TYPE IN ('OB', 'OC', 'OJ',
'OM', 'OU', 'IA', 'ID', 'IG', 'OG')  AND A.CURR_CLAIM_STATUS LIKE '5%' AND
CAST(A.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(A.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(A.CLAIM_NUM AS VARCHAR(3)) <>  CAST(C.SUBSCBR_NUM AS VARCHAR(7)) + '-' + CAST(C.MBR_SUFFIX AS CHAR(2)) + '-' + CAST(C.CLAIM_NUM AS VARCHAR(3))
AND A.TYPE_OF_BILL = @TYPE_OF_BILL

FETCH NEXT FROM MYCURS INTO @TYPE_OF_BILL, @GetRecordCount
END
Set RowCount 0
CLOSE MYCURS
DEALLOCATE MYCURS

SELECT * FROM HNS
0
 
LVL 2

Expert Comment

by:KhunJean
ID: 12093177
SET @@RowPerType = 100/@@ROWCOUNT

should be
Declare @RowPerType int
SET @RowPerType = 100/@@ROWCOUNT


WHILE @@FETCH_STATUS <> -1
set rowcount @GetRecordCount
BEGIN

should be
WHILE @@FETCH_STATUS <> -1
BEGIN
set rowcount @GetRecordCount

SELECT DISTINCT TOP 22 PERCENT A.PHS_CMPNY_NUM, A.SUBSCBR_NUM, A.MBR_SUFFIX,
should be
Select distinct PERCENT A.PHS_CMPNY_NUM, A.SUBSCBR_NUM, A.MBR_SUFFIX,
(rowcount will take care of the number of rows that will be returned.)

Rest looks ok.

I can give you another tip.
Please do not write everything in capitals. It makes reading it a lot harder.
If you working on this alone it is ok. But if there are more people. I for one find it hard to read.

Let me know if it works.
Jean
0
 

Author Comment

by:groyal
ID: 12098878
Thanks for the advise, everything is working fine except I am getting more than 100. What do I need to change from the following code below to equal a sum of 100.

IF @Total < 100 BEGIN
      SET @Remaining = 100 - @Total
      SET @Factor = @Remaining / ( SELECT SUM(GetRecordCount) FROM TempCount)
      UPDATE TempCount
      SET GetRecordCount = GetRecordCount +
      CASE WHEN RecordCount - GetRecordCount > 0 THEN
      @Factor * (RecordCount - GetRecordCount)
      ELSE
      0
      END
END
0
 
LVL 2

Accepted Solution

by:
KhunJean earned 250 total points
ID: 12100667
I think the problem is in these lines

SET @Factor = @Remaining / ( SELECT SUM(GetRecordCount) FROM TempCount)

@Factor * (RecordCount - GetRecordCount)

This multiplication can give you a value of say 4.6 will be converted to an int resulting in 5. Actually you can also end up with less than 100 records. (4.4 to in as 4)
Sorry about that. It was all done from the head.

If that happens you can get a few records more or less.

You can accept to live with it and use
select top 100 * from HNS
or better we can change the result.

I have to think a little about it. As i have no time at this very moment i come back to you soon.
0
 

Author Comment

by:groyal
ID: 12102686
Cool thanks.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question