This stored procedure allows you to create formatted report files that can be distributed to a reporting system by using stored procedures and ISQL. These can then be scheduled as a job to run monthly or even daily.
The example below is specific to our database of course, so you'll need to modify it to suit your needs. The first section shows the stored procedure and the second section shows the command line that runs in a batch file; the batch file is then scheduled. This one runs monthly and calculates values based on last month's data.
All the data gathered by the SP is printed into a flat file called RANKAR. We then import this file into our reporting system (REVEAL) which allows all of our users to view the report. Notice the PRINT CHAR(12) statements in the SP. The PRINT CHAR(12) causes a page break to occur when the export file is read by the reporting system.
This is great for information that gets reported over and over again, and keeps your users from running large adhoc queries that produce the same results month after month. Also I did some converting of dates so that they are dynamic and the SP can be run any day of the month and it will always report the previous months totals. This is especially important at the end of the year where you might usually just use something like:
Datepart(mm, dateadd(mm, -1, INVOICE_DATE) =
Datepart(mm, dateadd(mm, -1, Getdate()) and datepart(yy, invoice_date) = Datepart(yy, getdate())
This would work OK most months, but in January, when you are trying to report December totals, the system would be looking for December of the current year and not last year.
-- Stored Procedure
CREATE procedure RPT_Top_50_Customers_By_Area as
-- This one is uglier than some of the others, mainly because of
-- the cursor open/close cycling that is going on. Basically, the
-- idea was to get the top 50 customers for each area, in a single
-- report ... but grouping by the area turned out to be the fun part.
--
-- Coding Reminders:
-- - Handle the "Rank" calculations where two customers can be the same
-- rank due to identical sales (i.e. 1,2,2,4,5,5,5,8,9...)
-- - Do not lop off number 51 if it is tied with 50.
begin
declare @c_MaxLineCount int
-- A "Constant" that will be set, showing the
-- maximum number of lines per page
declare @m_CurrDate smalldatetime,
@m_MonthName varchar(10),
@m_YearName char(4),
-- Procedure Flow control
@m_LineCount int,
-- how many lines have been printed to a "page"
@m_PageCount int,
-- which page is being printed
-- Outer Cursor Values
@m_CurrentRegion varchar(30),
@m_CurrentArea varchar(30),
-- Inner Cursor Values
@m_Customer varchar(8),
@m_CustomerName varchar(28),
@m_MonthlySales int,
@m_YTDSales int,
@m_YTDCosts int,
@m_PrevYrMonthlySales int,
@m_PrevYrYTDSales int,
@m_PrevYrYTDCosts int,
-- Rank tracking Variables
@m_CurrentLine int,
@m_CurrentRank int,
@m_SavedYTDSales int,
-- Calculated values
@m_YTDPct int,
@m_PrevYrYTDPct int
-- Initialize the "constants"
select @c_MaxLineCount = 55
-- was 65, but reserved two lines for the 'totals' at the end of each page
-- The date is used in the report calculations, and is displayed
-- in the heading.
-- Save it to a variable here, rather than recalculating it for
-- each page, as it
-- would look really strange to see part of the report say "January
-- 28" while another part of the report says "January 29"
select @m_CurrDate = getdate()
-- Set the initial variable values
select @m_MonthName = datename (mm, dateadd(mm, -1, @m_CurrDate)),
@m_YearName = datepart (yyyy, dateadd(mm, -1, @m_CurrDate)),
@m_LineCount = @c_MaxLineCount + 20,
-- Initial value this high forces the columns to print on page 1
@m_PageCount = 0
-- First get the Region/Area listing, and use it to drive the reports
declare AreaCursor cursor fast_forward for
select distinct
cc.cc1_level3_desc,
cc.cc1_level1_desc
from
cost_center cc
where exists (
select 1
from invoice_header ih
where ih.cost_center = cc.cost_center
)
order by
cc.cc1_level3_desc,
cc.cc1_level1_desc
open AreaCursor
fetch next from AreaCursor into @m_CurrentRegion, @m_CurrentArea
Print Char(12) -- Prints a return character on the first page
while @@fetch_status = 0 begin
-- Reset the the Saved YTD Sales value each time that the inner
-- cursor is opened
-- This will solve some truly odd printed values when the last
-- YTD sales value of
-- one Area happens to be the same YTD sales number as the first
-- value of the next Area.
select @m_SavedYTDSales = -10000000
-- Force a page eject, as well
select @m_LineCount = @c_MaxLineCount + 1;
declare MonthlyDataCursor cursor fast_forward for
select top 50 with ties
c.customer_number as Customer,
c.name as Customer_Name,
ceiling (sum (case when ih.invoice_date >= CONVERT(char(6), dateadd (mm, -1, getdate()), 112) + '01' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'
then case when ih.total_sales is null then 0 else ih.total_sales end
else 0 end)) as Monthly_Sales,
ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'
then case when ih.total_sales is null then 0 else ih.total_sales end
else 0 end)) as YTD_Sales,
ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'
then case when ih.total_cost is null then 0 else ih.total_cost end
else 0 end)) as YTD_Costs,
ceiling (sum (case when ih.invoice_date >= CONVERT(char(6), dateadd (mm, -13, getdate()), 112) + '01' and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01'
then case when ih.total_sales is null then 0 else ih.total_sales end
else 0 end)) as Prev_Year_Monthly_Sales,
ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -13, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01'
then case when ih.total_sales is null then 0 else ih.total_sales end
else 0 end)) as Prev_Year_YTD_Sales,
ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -13, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01'
then case when ih.total_cost is null then 0 else ih.total_cost end
else 0 end)) as Prev_Year_YTD_Costs
from
cost_center cc,
invoice_header ih,
customer c
where
(
(CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' <= ih.invoice_date and ih.invoice_date < CONVERT(char(6), dateadd(mm, 0, getdate()), 112) + '01') or
(CONVERT(char(4), dateadd (mm, -13, getdate()), 112) + '0101' <= ih.invoice_date and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01')
) and
ih.customer_number not like 'ZZ%' and
c.customer_number = ih.customer_number and
cc.cost_center = ih.cost_center and
cc.cc1_level3_desc = @m_CurrentRegion and
cc.cc1_level1_desc = @m_CurrentArea
group by
c.customer_number,
c.name
having
ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'
then case when ih.total_sales is null then 0 else ih.total_sales end
else 0 end)) > 0
order by
ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'
then case when ih.total_sales is null then 0 else ih.total_sales end
else 0 end)) desc
open MonthlyDataCursor
-- get the first line
fetch next from MonthlyDataCursor into @m_Customer, @m_CustomerName,
@m_MonthlySales, @m_YTDSales, @m_YTDCosts,
@m_PrevYrMonthlySales, @m_PrevYrYTDSales, @m_PrevYrYTDCosts
-- And record that this *was* the first line for this area
select @m_CurrentLine = 1
while @@fetch_status = 0 begin
-- Print the page header information
if ((@m_LineCount >= @c_MaxLineCount)) begin
-- If a previous page has been printed, print the form
-- feed before starting the next page
if (@m_PageCount > 0) begin
print char(12)
end
-- Increment the page count
select @m_PageCount = @m_PageCount + 1
-- Print the page heading
print @m_MonthName + ', ' + @m_YearName + ' Top 50 Customers by Area - ' + @m_CurrentRegion + ' page: ' + cast(@m_PageCount as varchar(5))
print 'Region: ' + cast(case when @m_CurrentRegion is null then ' ' else @m_CurrentRegion end as char(10)) + ' ' + 'Area: ' + cast(case when @m_CurrentArea is null then ' ' else @m_CurrentArea end as char(30))
print ' ' -- Title sep
print 'Customer ' + ' ' + 'Customer ' + ' ' + ' Monthly' + ' ' + ' YTD ' + ' ' + ' YTD' + ' ' + ' YTD' + ' ' + ' Prior Year' + ' ' + ' Prior Year' + ' ' + ' Prior Year' + ' ' + ' Prior Year' + ' ' + ' Area'
print 'Number ' + ' ' + 'Name ' + ' ' + ' Sales ' + ' ' + ' Sales' + ' ' + ' PC' + ' ' + ' PC %' + ' ' + ' Monthly Sales' + ' ' + ' YTD Sales' + ' ' + ' YTD PC' + ' ' + ' YTD PC %' + ' ' + ' Rank'
print '----------' + ' ' + '----------------------------' + ' ' + '---------------' + ' ' + '---------------' + ' ' + '------------' + ' ' + '-------' + ' ' + '---------------' + ' ' + '---------------' + ' ' + '------------' + ' ' + '------------' + ' ' + '----------'
-- Finally, update the line count to show the number of lines that have been printed since the ^L
select @m_LineCount = 6
end
-- Figure out this customer's rank in this area. Since
-- SQL Server is doing the sorting
-- for us, just use the current line number. The trick
-- used here is that you leave the
-- Rank value alone if the YTD sales for this line is the
-- same as the YTD sales for the
-- previous line. This will give you a
-- pattern ,2,2,5,6,7,7,9,10 ... if such a pattern is appropriate
if (@m_SavedYTDSales != @m_YTDSales)
select @m_CurrentRank = @m_CurrentLine
select @m_SavedYTDSales = @m_YTDSales
-- Perform the percent calculations
if (@m_YTDSales = 0)
select @m_YTDPct = 0
else
select @m_YTDPct = 100 * (@m_YTDSales - @m_YTDCosts ) / @m_YTDSales
if (@m_PrevYrYTDSales = 0)
select @m_PrevYrYTDPct = 0
else
select @m_PrevYrYTDPct = 100 * (@m_PrevYrYTDSales - @m_PrevYrYTDCosts ) / @m_PrevYrYTDSales
-- Start printing out the line results
print cast((case when @m_Customer is null then '(null)' else @m_Customer end) as char(10)) + ' ' +
cast((case when @m_CustomerName is null then '(null)' else @m_CustomerName end) as char(28)) + ' ' +
str(@m_MonthlySales, 15) + ' ' +
str(@m_YTDSales, 15) + ' ' +
str((@m_YTDSales - @m_YTDCosts), 12) + ' ' +
str(@m_YTDPct, 7) + ' ' +
str(@m_PrevYrMonthlySales, 15) + ' ' +
str(@m_PrevYrYTDSales, 15) + ' ' +
str((@m_PrevYrYTDSales - @m_PrevYrYTDCosts), 12) + ' ' +
str(@m_PrevYrYTDPct, 12) + ' ' +
str(@m_CurrentRank,10)
-- Increment the line counters for the row that was just printed
select @m_LineCount = @m_LineCount + 1,
-- Lines printed on the page
@m_CurrentLine = @m_CurrentLine + 1
-- rows retrieved from inner cursor
-- Get the next record
fetch next from MonthlyDataCursor into @m_Customer, @m_CustomerName,
@m_MonthlySales, @m_YTDSales, @m_YTDCosts,
@m_PrevYrMonthlySales, @m_PrevYrYTDSales, @m_PrevYrYTDCosts
end
close MonthlyDataCursor
deallocate MonthlyDataCursor
-- Get the next Area to process
fetch next from AreaCursor into @m_CurrentRegion, @m_CurrentArea
end
close AreaCursor
deallocate AreaCursor
end
GO
--- Batch Command Line
isql -S servername -U username -P "password" -w 200 -d database -Q "exec RPT_Top_50_Customers_By_Area" > d:syscorereportsrankar
For More Information
- What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
