Solved

Returning 100 rows of data

Posted on 2004-09-16
10
214 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
  • 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
ssms - object execution statistics 12 37
SQL Script to find duplicates 16 20
Square brackets 4 12
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now