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.
by