July 12, 2010 at 12:42 pm
I have an unusual requirement to make sure that there are at least 17 rows on a report. Sometimes there are only 10 sometimes 40; is there a way to select a minimum number of rows for a report?
I know I could select them into a db and add the appropriate number of rows but that seems like a waste.
Any thoughts would be greatly appreciated.
JD>
July 12, 2010 at 1:12 pm
What are you supposed to do if there are less than 17? Include zeros? Or not run the report?
How is the report being run?
July 12, 2010 at 2:09 pm
If there are less than 17 I need to print blank rows like
15 John
16
17
July 12, 2010 at 2:25 pm
This is usually something you'd do in the client. You could potentially check for the row count (select count(*)) and if it's < 17, then you can add in a join to a table that has blank rows.
July 12, 2010 at 2:28 pm
One way would be to use a tally table. Create a (cte, inline view, table variable, or temp table) with the columns for your report, adding a row number column with the appropriate RANKING function. Then select from your tally table where n <=17 and left join to the report (temp table, table var, or cte).
A search on tally table will return a lot of info about them. I figure 17 rows would be plenty quick for a dynamic one...<grin>
jg
July 12, 2010 at 2:37 pm
Ya, Crystal Reports is the client and I just don't know how to do it there. I think I will just use a cursor and be done with this.
July 13, 2010 at 10:52 am
OK I thought I would show my hacked answer. Thanks
declare @ClassID int
set @ClassID = 2
declare @runTime datetime
set @runTime = (select GetDate())
declare @rowCount int
declare @rowsToAdd int
declare @constRowCount int
set @constRowCount = 17
set @rowsToAdd = 0
set @rowCount = 0
declare @PartID int
declare @PartLName nvarchar(30)
declare @PartFName nvarchar(30)
declare @DayText nvarchar(30)
declare @ClassTime nvarchar(30)
CREATE TABLE tmpResults
(
[PartID] [int] NULL,
[PartLName] [nvarchar](30) NULL,
[PartFName] [nvarchar](30) NULL,
[DayText] [nvarchar] (30) NULL,
[ClassTime] [nvarchar] (30) NULL
)
Declare #tmpDB CURSOR for (Select PartID, PartLName, PartFName, DayText, d.ClassTime
from Participants p
inner join DUIClasses d on d.ClassID = p.ClassID
inner join Programs pgm on pgm.ProgramID = p.ProgramID
inner join DaysOfTheWeek dotw on p.ClassDay = dotw.DayId
left outer join Educations ed on ed.duiclassid = @ClassID
where (p.PartStatus = 1 or p.PartStatus = 7)
and (p.ClassID = @ClassID or p.AltClassID = @ClassID)
and (ed.duiclassid = @ClassID)
and (@runTime not between edStart and edEnd)
)
OPEN #tmpDB
FETCH NEXT FROM #tmpDB INTO @PartID, @PartLName, @PartFName, @DayText, @ClassTime
While @@Fetch_Status = 0
begin
Insert into tmpResults(PartID, PartLName, PartFName, DayText, ClassTime)
values (@PartID, @PartLName, @PartFName, @DayText, @ClassTime)
FETCH NEXT FROM #tmpDB INTO @PartID, @PartLName, @PartFName, @DayText, @ClassTime
PRINT @rowCount
END
deallocate #tmpDB
set @rowCount = (select count(*) from tmpResults)
--print 'RowCount before: ' + convert(nvarchar, @rowCount)
if @rowCount < @constRowCount
begin
set @rowsToAdd = @constRowCount - @rowCount
--print 'RowsToAdd ' + convert(nvarchar, @rowsToAdd)
while @rowCount < @constRowCount
begin
Insert into tmpResults(PartID, PartLName, PartFName, DayText, ClassTime)
values (0, '', '', '', '')
set @rowCount = @rowCount + 1
end
end
set @rowCount = (select count(*) from tmpResults)
--print 'RowCount after: ' + convert(nvarchar, @rowCount)
select * from tmpResults
drop table tmpResults
July 13, 2010 at 1:22 pm
Here is a simple example of what I was attempting to explain prior:
;WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 as a, L0 as b),
L2 AS (SELECT 1 AS c FROM L1 as a, L1 as b),
L3 AS (SELECT 1 AS c FROM L2 as a, L2 as b),
L4 AS (SELECT Row_NUMBER() over(order by c) as n FROM L3),
Nums AS (Select * from L4 where n<=17) --dynamic tally table with 17 rows
select *
from Nums
left join (--report sql with row_number
select top 10
Row_NUMBER() over(order by object_id) as rownumber
from sys.columns) as r ON Nums.n=r.rownumber
jg
July 13, 2010 at 2:22 pm
:w00t: That is completely over my head bu thanks
July 13, 2010 at 3:18 pm
oops...I did not notice this was in the SQL Newbies section. My bad. I would not have just thrown code your way.
If you would like an explanation of my code, I will be happy to explain more clearly...I won't type it up unless you request...
jg
July 13, 2010 at 3:51 pm
I would love to understand your code.
July 14, 2010 at 12:33 am
john-902052 (7/13/2010)
I would love to understand your code.
The code makes use of an on-the-fly "Tally" Table and it's a powerful tool/skill to have under your belt. Take a look at the following article for an introduction to how it can be used to replace While Loops along with a couple of simple examples.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2010 at 8:01 am
The thought behind the sample query is to have one table of exactly 17 rows, then LEFT OUTER JOIN to the report detail rows. That will result in exactly 17 rows with NULLS in the report detail columns where there is no match. I have used this idea many times where there needs to be the exact number of detail lines in a report - data or not.
We have a permanent Nums (Tally) table on each db instance, so normally I would not use the Common Table Expression to build the 17 row table. I would normally just select from the nums table where n <17 and include the LEFT JOIN to the report detail.
The key concepts to understand the example query:
The Tally table - use the link that Mr. Moden provided.
A LEFT OUTER JOIN
A Common Table Expression.
The Row_Number ranking function (it applied a row-number in both the cte and the inline SELECT statement so that there is something to JOIN on)
hope this helps.
jg
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply