locked
Only count once of each group RRS feed

  • Question

  • I am using a while loop to set startpage and endpage variables in the following manner to achieve a page number column for each group I have in my report. The table of contents is grouped on OfficeCounty > ProviderType > SpecialtyName (Then all the providers that are present in that combination of groups) ... Repeat. I've limited each page of the report to display 9 records, and I have correctly calculated and set the amount of pages each one of these record groups will take up in the report (divided records returned by 9 and then ran a ceiling function on it.

    I am getting the correct kind of response from my SQL, which is updating the table at each record, with a page range that is calculated from the previous records end page + counts of the records returned by the current GROUP.

    My problem is I do not need my code to update EVERY records page range, I need it to perform the below calculation only on the necessary groups (officecounty>providertype>specialtyname) - not every record. So instead of ending up with a final page range of around 250 - I'm getting a final page range of about 10,000.

    Can somebody please help me with my update statement or wherever the problem lies. here's my loop:

    DECLARE @i int = 0 -- Incrementing variable
    	DECLARE @RecordCount int = (SELECT COUNT(SpecialtyName) FROM tmp_ProviderDirectory_Results) -- Count of result set
    
    WHILE (@RecordCount > @i)
    BEGIN
    	PRINT 'Record # ' + CONVERT(varchar, @i)
    
    	SET @i += 1
    	SET @StartPage = @EndPage + 1
    	SET @EndPage = ( @StartPage + ( SELECT DISTINCT [PageCount] FROM tmp_ProviderDirectory_Results pd WHERE RowNumber = @i ) )
    	SET @PageRange = CONVERT(varchar, @StartPage) + '-' + CONVERT(varchar, @EndPage)
    
    	PRINT 'Start Page ' +CONVERT(varchar, @i) + ' = ' + CONVERT(varchar, @StartPage)
    	PRINT 'End Page ' + CONVERT(varchar, @i) + ' = ' + CONVERT(varchar, @EndPage)
    	PRINT 'Page Range ' + CONVERT(varchar, @i) + ' = ' + CONVERT(varchar, @PageRange)
    
    	UPDATE
    		tmp_ProviderDirectory_Results
    	SET
    		PageRange = @PageRange
    	FROM
    		tmp_ProviderDirectory_Results pd
    	WHERE
    		RowNumber = @i -- Without this, PageRange is the same final PageRange for all records

    Here are the results. The 8's and 3's are both part of two seperate groups that should only be tallied once in the pageRange field but are calculated every time:

    Thanks


    • Edited by bitzoid Wednesday, August 21, 2013 1:31 PM
    Wednesday, August 21, 2013 1:27 PM

Answers

All replies

  • I think you are looking for ROW_NUMBER OVER PARTITION BY:

    http://www.sqlusa.com/bestpractices2005/overpartitionby/

    In SQL Server 2012 you can use aggregate functions such as SUM in conjunction with OVER PARTITION BY.

    You can use CTE in conjunction with UPDATE:

    http://pratchev.blogspot.com/2008/03/updates-with-cte.html


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    Wednesday, August 21, 2013 2:58 PM
  • >> I am using a while loop to set startpage and endpage variables in the following manner to achieve a page number column for each group I have in my report. <<

    This is a display issue. SQL is for queries in the database layer, it has nothing to do with the presentation layers. Please read how tiered architectures and Client/Server works. 

    SQL is also a declarative language. Declarative languages do not use loops; that is procedural code. You are writing COBOL with T-SQL! We would use a report writing tool of some kind.  

    Your code is full of other errors. You COBOL programmers will use temp table to mimic a scratch tape. I see that you do not know that CONVERT is dialect and that SQL programmers use CAST. We also use VARCHAR(n) since VARCHAR is the same as VARCHAR(1)! That is pretty useless. 

    COBOL keeps all its data in characters, so you have string handling in your code that would never occur in good SQL. You are building COBOL-style printouts! 

    If you can explain why this is a logical attribute of the data and not part of the physical display, then look at “ROW_NUMBER() OVER ()” and some math. But this still bad programming. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, August 21, 2013 4:13 PM