how to program for period-to-period comparisons ?

how to program for period-to-period comparisons ?

Post by Michael Coughli » Tue, 24 Oct 2000 04:00:00



Hi

What's the best way to generate period-to-period comparisons (e.g. for
variance analysis) where you want to generate 2 columns (with different
names, but otherwise similar data-type & values).  For example,

    SalesCurrent    SalesPreviousYear    Difference
    -------------    -------------------    -----------

The difference column, I can handle in the report writer; that part's easy
enough.

I was going to generate the "current" and "previous" columns from
parameterized stored procedures, since, assuming large data volumes, I don't
want to simply limit a huge, generic View on the (e.g. VB) client front-end.

In Access (with small data volume) I could join two queries in a third
query; but, Transact_SQL doesn't want parameters in Views (bummer  <G> ) ...
and I want to limit the initial recordsets, rather than use a parameterized
stored procedure (step 2) on huge, generic Views (step 1).

Although I have access to SQL Server 2000 (as an MSDN Developer), the data
resides on SQL Server 7 (precluding UDFs ? ).

I'm about to attempt this with "table variables" (i.e. declare variables of
data-type "Table"), but can't find examples in my books ("Guru's Guide to
Transact-SQL"/Ken Henderson, "Programming VB6"/Francesco Balena or the
"Hitchhiker's Guide to VB & SQL Server"/Bill Vaughn).  So, it occurred to me
that I might be approaching it all wrong?

The (spreadsheet) need to generate period-to-period variance comparisons is
so common that I was surprised to find NO Transact-SQL examples on it !!!

thanks, in advance, for whatever insights ...

Michael Coughlin

 
 
 

how to program for period-to-period comparisons ?

Post by Tore » Tue, 24 Oct 2000 04:00:00


I don't know if it would be the most efficient approach, but I believe
something like the following may do the trick:

SELECT SUM(CASE
        WHEN SalesDate BETWEEN CurrentYearStart AND CurrentYearEnd
        THEN SalesAmount
        ELSE 0) as SalesCurrent,
    SUM(CASE
        WHEN SalesDate BETWEEN PreviousYearStart AND PreviousYearEnd
        THEN SalesAmount
        ELSE 0) as SalesPreviousYear
FROM Sales
WHERE SalesDate BETWEEN PreviousYearStart AND CurrentYearEnd

If you wanted the difference calculated by SQL, you can SUM the difference
between the two case statements.

HTH,
Tore.


Quote:> Hi

> What's the best way to generate period-to-period comparisons (e.g. for
> variance analysis) where you want to generate 2 columns (with different
> names, but otherwise similar data-type & values).  For example,

>     SalesCurrent    SalesPreviousYear    Difference
>     -------------    -------------------    -----------

> The difference column, I can handle in the report writer; that part's easy
> enough.

> I was going to generate the "current" and "previous" columns from
> parameterized stored procedures, since, assuming large data volumes, I
don't
> want to simply limit a huge, generic View on the (e.g. VB) client
front-end.

> In Access (with small data volume) I could join two queries in a third
> query; but, Transact_SQL doesn't want parameters in Views (bummer  <G> )
...
> and I want to limit the initial recordsets, rather than use a
parameterized
> stored procedure (step 2) on huge, generic Views (step 1).

> Although I have access to SQL Server 2000 (as an MSDN Developer), the data
> resides on SQL Server 7 (precluding UDFs ? ).

> I'm about to attempt this with "table variables" (i.e. declare variables
of
> data-type "Table"), but can't find examples in my books ("Guru's Guide to
> Transact-SQL"/Ken Henderson, "Programming VB6"/Francesco Balena or the
> "Hitchhiker's Guide to VB & SQL Server"/Bill Vaughn).  So, it occurred to
me
> that I might be approaching it all wrong?

> The (spreadsheet) need to generate period-to-period variance comparisons
is
> so common that I was surprised to find NO Transact-SQL examples on it !!!

> thanks, in advance, for whatever insights ...

> Michael Coughlin


 
 
 

how to program for period-to-period comparisons ?

Post by Michael Coughli » Wed, 25 Oct 2000 04:00:00


Thanks for the reply, Tore ... I'll look into it, today.

Stated another way, what I was really wrestling with, below, was the best
way to:
    1.  make 2 passses through the same parameterized stored procedure

    2.  join the resulting 2 recordsets (via a View or something) to feed a
banded report writer.

Michael Coughlin
*********************************************************************


> I don't know if it would be the most efficient approach, but I believe
> something like the following may do the trick:

> SELECT SUM(CASE
>         WHEN SalesDate BETWEEN CurrentYearStart AND CurrentYearEnd
>         THEN SalesAmount
>         ELSE 0) as SalesCurrent,
>     SUM(CASE
>         WHEN SalesDate BETWEEN PreviousYearStart AND PreviousYearEnd
>         THEN SalesAmount
>         ELSE 0) as SalesPreviousYear
> FROM Sales
> WHERE SalesDate BETWEEN PreviousYearStart AND CurrentYearEnd

> If you wanted the difference calculated by SQL, you can SUM the difference
> between the two case statements.

> HTH,
> Tore.



> > Hi

> > What's the best way to generate period-to-period comparisons (e.g. for
> > variance analysis) where you want to generate 2 columns (with different
> > names, but otherwise similar data-type & values).  For example,

> >     SalesCurrent    SalesPreviousYear    Difference
> >     -------------    -------------------    -----------

> > The difference column, I can handle in the report writer; that part's
easy
> > enough.

> > I was going to generate the "current" and "previous" columns from
> > parameterized stored procedures, since, assuming large data volumes, I
> don't
> > want to simply limit a huge, generic View on the (e.g. VB) client
> front-end.

> > In Access (with small data volume) I could join two queries in a third
> > query; but, Transact_SQL doesn't want parameters in Views (bummer  <G> )
> ...
> > and I want to limit the initial recordsets, rather than use a
> parameterized
> > stored procedure (step 2) on huge, generic Views (step 1).

> > Although I have access to SQL Server 2000 (as an MSDN Developer), the
data
> > resides on SQL Server 7 (precluding UDFs ? ).

> > I'm about to attempt this with "table variables" (i.e. declare variables
> of
> > data-type "Table"), but can't find examples in my books ("Guru's Guide
to
> > Transact-SQL"/Ken Henderson, "Programming VB6"/Francesco Balena or the
> > "Hitchhiker's Guide to VB & SQL Server"/Bill Vaughn).  So, it occurred
to
> me
> > that I might be approaching it all wrong?

> > The (spreadsheet) need to generate period-to-period variance comparisons
> is
> > so common that I was surprised to find NO Transact-SQL examples on it
!!!

> > thanks, in advance, for whatever insights ...

> > Michael Coughlin

 
 
 

how to program for period-to-period comparisons ?

Post by Tore » Wed, 25 Oct 2000 04:00:00


Forgive me for being a little dense, but I'm still not sure I understand
what you are after (or why).

If you want two separate passes (invocations) through the same SP, you'll
need to store the intermediate result somehow (Select or Insert Into a not
quite temporary table...) and be able to relate each entry in the two runs.

If you want to use two separate queries in the same invocation of the SP,
you can select or insert into two temp tables, and join them on some common
data item (required) for instance product, branch, or whatever.  If each
query is a scalar query, you can just display the cartesian product, which
will then be a single row.

If you are looking to compare a number of years' results, use a Group By for
the year to produce a vertical result set, and then apply some method for
crosstabulation (may be available in your report generator?).  One possible
approach is shown below (you'll have to debug it... :->):


AS
BEGIN






As PeriodSales
INTO #TempSales
FROM Sales

GROUP BY DatePart(year, SalesDate)
ORDER BY DatePart(year, SalesDate)'



        WHEN Period = <iPeriod> THEN PeriodSales
        ELSE 0) as [Sales <iPeriod>]'








END



RETURN 0

END
GO

HTH,
Tore.


> Thanks for the reply, Tore ... I'll look into it, today.

> Stated another way, what I was really wrestling with, below, was the best
> way to:
>     1.  make 2 passses through the same parameterized stored procedure

>     2.  join the resulting 2 recordsets (via a View or something) to feed
a
> banded report writer.

> Michael Coughlin
> *********************************************************************



> > I don't know if it would be the most efficient approach, but I believe
> > something like the following may do the trick:

> > SELECT SUM(CASE
> >         WHEN SalesDate BETWEEN CurrentYearStart AND CurrentYearEnd
> >         THEN SalesAmount
> >         ELSE 0) as SalesCurrent,
> >     SUM(CASE
> >         WHEN SalesDate BETWEEN PreviousYearStart AND PreviousYearEnd
> >         THEN SalesAmount
> >         ELSE 0) as SalesPreviousYear
> > FROM Sales
> > WHERE SalesDate BETWEEN PreviousYearStart AND CurrentYearEnd

> > If you wanted the difference calculated by SQL, you can SUM the
difference
> > between the two case statements.

> > HTH,
> > Tore.



> > > Hi

> > > What's the best way to generate period-to-period comparisons (e.g. for
> > > variance analysis) where you want to generate 2 columns (with
different
> > > names, but otherwise similar data-type & values).  For example,

> > >     SalesCurrent    SalesPreviousYear    Difference
> > >     -------------    -------------------    -----------

> > > The difference column, I can handle in the report writer; that part's
> easy
> > > enough.

> > > I was going to generate the "current" and "previous" columns from
> > > parameterized stored procedures, since, assuming large data volumes, I
> > don't
> > > want to simply limit a huge, generic View on the (e.g. VB) client
> > front-end.

> > > In Access (with small data volume) I could join two queries in a third
> > > query; but, Transact_SQL doesn't want parameters in Views (bummer
 <G> )
> > ...
> > > and I want to limit the initial recordsets, rather than use a
> > parameterized
> > > stored procedure (step 2) on huge, generic Views (step 1).

> > > Although I have access to SQL Server 2000 (as an MSDN Developer), the
> data
> > > resides on SQL Server 7 (precluding UDFs ? ).

> > > I'm about to attempt this with "table variables" (i.e. declare
variables
> > of
> > > data-type "Table"), but can't find examples in my books ("Guru's Guide
> to
> > > Transact-SQL"/Ken Henderson, "Programming VB6"/Francesco Balena or the
> > > "Hitchhiker's Guide to VB & SQL Server"/Bill Vaughn).  So, it occurred
> to
> > me
> > > that I might be approaching it all wrong?

> > > The (spreadsheet) need to generate period-to-period variance
comparisons
> > is
> > > so common that I was surprised to find NO Transact-SQL examples on it
> !!!

> > > thanks, in advance, for whatever insights ...

> > > Michael Coughlin

 
 
 

how to program for period-to-period comparisons ?

Post by Michael Coughli » Wed, 25 Oct 2000 04:00:00


Wow ... Tore, you are amazing !!!

Quote:> Forgive me for being a little dense, but I'm still not sure I understand

what you are after (or why).  <

... au contraire; `tis I who am more than a little dense.  In trying to be
brief perhaps I wasn't clear (I left out the Departments breakout, rather
than needlessly complicate the question).

I'm trying to learn an optimal approach for gathering (summing) something
(e.g. costs or sales) by department from two periods and comparing them in
one report, rather than (for example) write one stored procedure pass to a
spreadsheet, then write a second pass for period #2 and use the spreadsheet
to compute the differences (variance).

My first thoughts were to approach it, as you said, here:

Quote:> If you want two separate passes (invocations) through the same SP, you'll
> need to store the intermediate result somehow (Select or Insert Into a not
> quite temporary table...) and be able to relate each entry in the two

runs.

Using your "Case" approach to, in effect, zero out (sales or cost) values

that, this afternoon, and when I left out the date column from the output,
it worked, as follows:

***************************************************************






SELECT
 dbo.Depts.Department,
/* dbo.TimeDetailADI.Report_Date, */
 PreviousCost = SUM(CASE

         THEN (dbo.EmployeeADI.Primary_Pay_Rate *
(dbo.EmployeeADI.Minutes_Worked / 60))
  ELSE 0
  END),
 CurrentCost = SUM(CASE

         THEN (dbo.EmployeeADI.Primary_Pay_Rate *
(dbo.EmployeeADI.Minutes_Worked / 60))
  ELSE 0
  END)
FROM
 dbo.EmployeeADI INNER JOIN  dbo.TimeDetailADI ON
 dbo.EmployeeADI.SSN = dbo.TimeDetailADI.SSN INNER JOIN  dbo.Depts ON
 dbo.EmployeeADI.Pos_ID = dbo.Depts.DeptNum
where

GROUP BY
 dbo.Depts.Department
/* dbo.TimeDetailADI.Report_Date */
*******************************************************************

Quote:> If you want to use two separate queries in the same invocation of the SP,
> you can select or insert into two temp tables, and join them on some
common
> data item (required) for instance product, branch, or whatever.  <

This has been my second inclination - until I read in SQL Server Books
Online that one should use "table variables", instead of temporary tables.
Sooooo, I figured I could try to join the two table variables on the
departments (but, I'm "not there", yet  <G> ).

This strikes me as a very worthwhile exercise (even those the "Case
approach" worked) - just to learn how to do it.  So, I'll give it "a go ...
"

*************************************************************

Quote:> If you are looking to compare a number of years' results, use a Group By
for
> the year to produce a vertical result set, and then apply some method for
> crosstabulation (may be available in your report generator?).

As to on-the-fly cross-tabulation, you've impressed me that you "eat, sleep
& drink" SQL, but lost me a bit.
Did you write a book ?  (If so, I'm buyin'  <G> )

I don't know how to thank you for taking the time to rough out these
approaches?  If it took you but a few minutes, I'm even more astonished.

But, I    T H A N K you, sincerely.

/Mike Coughlin

*******************************************************

> One possible approach is shown below (you'll have to debug it... :->):
> Create Procedure sp_CompareAnnualSales


> AS
> BEGIN






Sum(SalesAmount)
> As PeriodSales
> INTO #TempSales
> FROM Sales

> GROUP BY DatePart(year, SalesDate)
> ORDER BY DatePart(year, SalesDate)'



>         WHEN Period = <iPeriod> THEN PeriodSales
>         ELSE 0) as [Sales <iPeriod>]'









> END



> RETURN 0

> END
> GO

> HTH,
> Tore.

 
 
 

how to program for period-to-period comparisons ?

Post by Tore » Wed, 25 Oct 2000 04:00:00


Thanks!

Responses like this are what makes posting worthwhile...  Although I have to
admit I don't know as much as a want to (and some times think I do...).

I use responding to posts as a way to educate myself as much as anything.
Having to solve somebody else's problem at least makes you exercise what you
have already seen.

See further comments below.


Quote:> Wow ... Tore, you are amazing !!!

> > Forgive me for being a little dense, but I'm still not sure I understand
> what you are after (or why).  <

> ... au contraire; `tis I who am more than a little dense.  In trying to be
> brief perhaps I wasn't clear (I left out the Departments breakout, rather
> than needlessly complicate the question).

> I'm trying to learn an optimal approach for gathering (summing) something
> (e.g. costs or sales) by department from two periods and comparing them in
> one report, rather than (for example) write one stored procedure pass to a
> spreadsheet, then write a second pass for period #2 and use the
spreadsheet
> to compute the differences (variance).

I figured as much... :->

> My first thoughts were to approach it, as you said, here:
> > If you want two separate passes (invocations) through the same SP,
you'll
> > need to store the intermediate result somehow (Select or Insert Into a
not
> > quite temporary table...) and be able to relate each entry in the two
> runs.

> Using your "Case" approach to, in effect, zero out (sales or cost) values


> that, this afternoon, and when I left out the date column from the output,
> it worked, as follows:

<snip>

Great.

Quote:> > If you want to use two separate queries in the same invocation of the
SP,
> > you can select or insert into two temp tables, and join them on some
> common
> > data item (required) for instance product, branch, or whatever.  <

> This has been my second inclination - until I read in SQL Server Books
> Online that one should use "table variables", instead of temporary tables.
> Sooooo, I figured I could try to join the two table variables on the
> departments (but, I'm "not there", yet  <G> ).

> This strikes me as a very worthwhile exercise (even those the "Case
> approach" worked) - just to learn how to do it.  So, I'll give it "a go
...
> "

... You found something I'm not familiar with in SQL Server (assuming you
aren't referring to the ADO table object).

Quote:> *************************************************************

> > If you are looking to compare a number of years' results, use a Group By
> for
> > the year to produce a vertical result set, and then apply some method
for
> > crosstabulation (may be available in your report generator?).

> As to on-the-fly cross-tabulation, you've impressed me that you "eat,
sleep
> & drink" SQL, but lost me a bit.

All it does is build a dynamic SQL statement similar to the original
solution to compare two periods using a SUM(CASE..., but with a variable
number of periods in the select list, and executes it against a temp table
which was built with the sums grouped by the periods wanted.

Quote:> Did you write a book ?  (If so, I'm buyin'  <G> )

Not yet... <VBG>

Quote:> I don't know how to thank you for taking the time to rough out these
> approaches?  If it took you but a few minutes, I'm even more astonished.

> But, I    T H A N K you, sincerely.

> /Mike Coughlin

I think faster than I type, but it didn't take too long.

You are quite welcome.

Tore Bostrup

<snip>

 
 
 

how to program for period-to-period comparisons ?

Post by Michael Coughli » Wed, 25 Oct 2000 04:00:00


Quote:> I have to admit I don't know as much as a want to (and some times think I

do...).  <

Neither does Bill Gates; it's all relative    <G>
... maybe when we make 'heaven' ?  (Will it matter, then?  <G> )

Quote:> I use responding to posts as a way to educate myself as much as anything.

<

So, who supports you, while you're doing this ?  <G>

Quote:>    I read in SQL Server Books
> > Online that one should use "table variables", instead of temporary

tables.

Quote:> ... You found something I'm not familiar with in SQL Server (assuming you
> aren't referring to the ADO table object).

nope ... until last night I hadn't noticed that there's a SQL 'data type'
called "Table".
" table
A special data type that can be used to store a result set for later
processing. Its primary use is for temporary storage of a set of rows, which
are to be returned as the result set of a table-valued function."

I'll send you the BoL version.  I hadn't seen it mentioned in any of my 800
page tomes, either.  Its potential uses look to be substantial.  I'll send
you the BoL pages, separately.

************************************************************

Quote:> Did you write a book ?  (If so, I'm buyin'  <G> )
> Not yet... <VBG>

Well, I think there are some folks at Wrox or MS Press who are dyin' to talk
to you !

/Mike

 
 
 

how to program for period-to-period comparisons ?

Post by Jed Klande » Wed, 25 Oct 2000 04:00:00


Guys,

I believe that you may be reading the BOL from SQL Server 2000.  The new
table data type did not exists until SQL 2K.  But you are quite right, it is
a great new datatype, especially when working with User-Defined Functions.
Good Luck.

Quote:> > I have to admit I don't know as much as a want to (and some times think
I
> do...).  <

> Neither does Bill Gates; it's all relative    <G>
> ... maybe when we make 'heaven' ?  (Will it matter, then?  <G> )

> > I use responding to posts as a way to educate myself as much as
anything.
> <

> So, who supports you, while you're doing this ?  <G>

> >    I read in SQL Server Books
> > > Online that one should use "table variables", instead of temporary
> tables.

> > ... You found something I'm not familiar with in SQL Server (assuming
you
> > aren't referring to the ADO table object).

> nope ... until last night I hadn't noticed that there's a SQL 'data type'
> called "Table".
> " table
> A special data type that can be used to store a result set for later
> processing. Its primary use is for temporary storage of a set of rows,
which
> are to be returned as the result set of a table-valued function."

> I'll send you the BoL version.  I hadn't seen it mentioned in any of my
800
> page tomes, either.  Its potential uses look to be substantial.  I'll send
> you the BoL pages, separately.

> ************************************************************
> > Did you write a book ?  (If so, I'm buyin'  <G> )
> > Not yet... <VBG>

> Well, I think there are some folks at Wrox or MS Press who are dyin' to
talk
> to you !

> /Mike

 
 
 

how to program for period-to-period comparisons ?

Post by Michael Coughli » Thu, 26 Oct 2000 13:10:55


Thanks for the clarification, Jed.  Yes, I am running the SQL Server 2000
version and that's where I spotted the "table" data type.

Michael Coughlin


Quote:> Guys,

> I believe that you may be reading the BOL from SQL Server 2000.  The new
> table data type did not exists until SQL 2K.  But you are quite right, it
is
> a great new datatype, especially when working with User-Defined Functions.
> Good Luck.