These are the values that I need to return from a stored procedure:
Description
Week 1 Usage
Week 2 Usage
Usage Variance
Week 1 Price
Week 2 Price
Price Variance
% of Price Variance
The three fields with "Variance" in their name are calculated fields based on the other values, and the "Week" partitions have to be calculated based on date parameters. So the actual (most pertinent) fields that reside in the database tables are:
Description
Usage
Price
InvoiceDate
The parameters provided by the user are:
Unit
DateBegin
DateEnd
The variables I think I will need to declare in the stored procedure are:
DECLARE
@Week1Begin datetime,
@Week1End datetime,
@Week2Begin datetime,
@Week2End datetime,
@Week1Usage varchar(30),
@Week2Usage varchar(30),
@Week1Price varchar(30),
@Week2Price varchar(30),
@UsageVariance varchar(30),
@PriceVariance varchar(30),
@PercentageOfPriceVariance varchar(30)
Week1Begin and Week1End will have to be computed based on the value provided by the user in the DateBegin parameter (e.g., if DateBegin is 12/27/15, Week1Begin would be 12/27/2015 and Week1End would be 1/2/2016);
Week2Begin and Week2End would be similar, but their span could be fewer than seven days and if longer than that 7 days at most (Week2End would be "reduced" to a prior date as needed).
So although there's a lot of calculating going on, the raw data is pretty sparse. It's basically the four fields mentioned above (Description, Usage, Price, and InvoiceDate), which are available from the InvoiceDetail table.
My biggest dilemma is that much of this data is returned from an existing stored procedure that might be best to copy and extend, but I am no SQL expert by any means - I can write simple SELECT and UPDATE, etc., statements, but dancing with temp tables and such relational gyrations are pretty much beyond me.
So creating a new stored procedure based on an existing one seems fraught with danger; OTOH, I wonder if it would be possible to retrieve the data I need using a rather minimal stored procedure with a complex query statement containing multiple sub-selects.
Again, complex SQL is not my forte by any means, so I may be "way off" here, but here is my pseudo-SQL:
CREATE Procedure [dbo].[myFunkySP]
@Unit varchar(25),
@BegDate datetime,
@EndDate datetime
As
// Create a mostly-empty-for-now temp table
Select Description,
Week1Begin,
Week1End,
Week2Begin,
Week2End,
Week1Usage,
Week2Usage,
Week1Price,
Week2Price,
UsageVariance,
PriceVariance
PercentageOfPriceVariance
Into #TempItems
From InvoiceDetail Ind
Where Ind.Unit = @Unit
and Ind.InvoiceDate Between @BegDate and @EndDate
begin
Update #TempItems set
Week1Begin = // val from@BegDate
Week1End = // do some date math to determine; maybe a Function called GetEndOfWeek(Week1Begin)?
Week2Begin = // do some date math to determine (1 day beyond Week1End val)
Week2End = // val from@EndDate
Week1Usage = SELECT SUM(USAGE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week1Begin AND Week1End
Week2Usage = SELECT SUM(USAGE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week2Begin AND Week2End
Week1Price = SELECT SUM(PRICE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week1Begin AND Week1End
Week2Price = SELECT SUM(PRICE) FROM InvoiceDetail WHERE UNIT = @UNIT, DATE BETWEEN Week2Begin AND Week2End
UsageVariance = (Week2Usage - Week1Usage)
PriceVariance = (Week2Price - Week1Price)
PercentageOfPriceVariance = (PriceVariance div week2Price)
// now select * from the temp table to return everything?
Otherwise (if the above won't work or is not feasible), I was thinking I might have to do some 9-X-Uglier-than-a-bag-of-butts query polluted with beaucoup subqueries, something like:
SELECT DESCRIPTION,
(SELECT SUM(USAGE) FROM InvoiceDetail
WHERE UNIT = @UNIT, InvoiceDate BETWEEN :firstWeekBegin AND :firstWeekEnd as Week1Usage),
(SELECT SUM(USAGE) FROM InvoiceDetail
WHERE UNIT = @UNIT, InvoiceDate BETWEEN :secondWeekBegin AND :secondWeekEnd as Week2Usage),
SELECT PRICE FROM InvoiceDetail
WHERE UNIT = @UNIT, InvoiceDate = firstWeekBegin as Week1Price),
(SELECT PRICE FROM InvoiceDetail
WHERE UNIT = @UNIT, InvoiceDate = secondWeekBegin as Week2Price),
FROM InvoiceDetail
WHERE UNIT = @UNIT, InvoiceDate BETWEEN @BEGDATE AND @ENDATE ORDER BY PRODUCTS
Say it aint' so, Joe!
Even if that convoluted query is sort of workable, it still doesn't give me the calculated values I need (UsageVariance, PriceVariance, and PercentageOfPriceVariance).
So what do I need to do to create a stored procedure that will return the results I need?
