June 21, 2010 at 2:21 pm
I am trying to build a report that is going to list (in different columns) fees/costs from two different date ranges.
I have the following and I can't make it work 🙁 Any help would be greatly appreciated. Thank you.
DECLARE @Year INT
SELECT @Year = '2010'
SELECT (YEAR(Invoice.InvoiceDate))AS InvYear, (MONTH(Invoice.InvoiceDate))AS InvMonth,
/*begin year prior*/
(SELECT (SUM(CASE WHEN AllWorkOrderComponentView.IsFee = 1 THEN AllWorkOrderComponentView.Total ELSE 0 END)) AS FeeAmount,
SUM(CASE WHEN AllWorkOrderComponentView.IsFee = 0 THEN AllWorkOrderComponentView.Total ELSE 0 END) AS CostAmount
FROM AllWorkOrderComponentView INNER JOIN
Invoice ON AllWorkOrderComponentView.InvoiceID = Invoice.InvoiceID
WHERE (YEAR(Invoice.InvoiceDate) = DATEADD(y, -1, @Year))
AND AllWorkOrderComponentView.IsCancelled = 0 and AllWorkOrderComponentView.InvoiceID IS NOT NULL
) AS YearPrior,
/*end year prior*/
/*begin ytd*/
(SELECT (SUM(CASE WHEN AllWorkOrderComponentView.IsFee = 1 THEN AllWorkOrderComponentView.Total ELSE 0 END)) AS FeeAmount,
SUM(CASE WHEN AllWorkOrderComponentView.IsFee = 0 THEN AllWorkOrderComponentView.Total ELSE 0 END) AS CostAmount
FROM AllWorkOrderComponentView INNER JOIN
Invoice ON AllWorkOrderComponentView.InvoiceID = Invoice.InvoiceID
WHERE (YEAR(Invoice.InvoiceDate) = @Year)
AND AllWorkOrderComponentView.IsCancelled = 0 and AllWorkOrderComponentView.InvoiceID IS NOT NULL) AS YTD
/*end ytd*/
FROM AllWorkOrderComponentView INNER JOIN
Invoice ON AllWorkOrderComponentView.InvoiceID = Invoice.InvoiceID
WHERE (dbo.DateOnly(Invoice.CreatedOn) = CASE DATEPART(DW, dbo.dateonly(getdate())) WHEN 2 THEN DATEADD(dd, - 3,
dbo.dateonly(getdate())) WHEN 1 THEN DATEADD(dd, - 2, dbo.dateonly(getdate())) ELSE DATEADD(dd, - 1, dbo.dateonly(getdate())) END) AND AllWorkOrderComponentView.IsCancelled = 0 and AllWorkOrderComponentView.InvoiceID IS NOT NULL
GROUP BY (YEAR(Invoice.InvoiceDate)), (MONTH(Invoice.InvoiceDate))
ORDER BY (MONTH(Invoice.InvoiceDate))
June 21, 2010 at 2:44 pm
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
In the first (prior year) query, you have this line:
WHERE (YEAR(Invoice.InvoiceDate) = DATEADD(y, -1, @Year))
By having the column inside a function, you will NOT utilize any index that might be on that field.
It would be better to change this to:
declare @StartDate datetime,
@EndDate datetime
select @StartDate = @Year + '0101', @EndDate = DateAdd(year, 1, @StartDate)
....
WHERE Invoice.InvoiceDate >= @StartDate
AND Invoice.InvoiceDate < @EndDate
You might find the "Common Date/Time Routines" link in my signature worth reading.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 22, 2010 at 8:43 am
Thank you, I apologize for being vague and appreciate your response.
The intended goal was to have Five columns of data that would include the month, the costs and fees of the year prior and the costs and fees of the current year to date.
I would love to be able to send the DDL scripts along with this request, I don't have any experience in creating these scripts. I do sincerely appreciate all of the time that is taken to consider and take on these requests and would love to be able to make my questions more clear. Is there a place I can go to learn how to create these scripts?
~iklektic
June 23, 2010 at 3:53 pm
iklektic,
Your post is very similar to a post from a few weeks ago that I used in an article that I submitted and has been approved for publication here on SSC. The article is on Calendar tables and how to use them to avoid excessive date calculations and get better performance.
As to your problem, without the actual tables it's hard to come up with a real solution. I can, however, point you in the right direction I think.
I'm not going to use my calendar table solution since that is the subject of the article. There are some things you can do to get this type of thing to work.
Since you are using variables and setting them I know this is a procedural type query script or procedure. Rather than setting just 1 variable and using that to calculate the date ranges (which will result in the optimizer not being able to use an index), why not set all the variables you need at the beginning? Use actual date variables for the comparison and if there is an index on invoice date, then it will be likely be used.
-- In this example I only did the fee part. You can figure out the Cost.
DECLARE
@PriorYearStart DATETIME
, @ThisYearStart DATETIME
SET @PriorYearStart = '1/1/2009'
SET @ThisYearStart = DATEADD(year, 1, @PriorYearStart)
SELECT MonthNum AS InvMonth
, MIN(TheYear) AS PriorYear
, SUM(CASE WHEN YearNum = 1 THEN FeeAmt ELSE 0 END) AS PriorYearFee
, MAX(TheYear) AS CurrentYear
, SUM(CASE WHEN YearNum = 2 THEN FeeAmt ELSE 0 END) AS CurrYearFee
FROM
(SELECT 1 AS YearNum, 2009 AS TheYear
, CASE WHEN AWC.IsFee = 1 THEN AWC.Total ELSE 0 END AS FeeAmt
, DATEPART(Month, I.InvoiceDate) AS MonthNum
FROM AllWorkOrderComponentView AWC
INNER JOIN Invoice I ON
AWC.InvoiceID = I.InvoiceID
WHERE I.InvoiceDate >= @PriorYearStart
AND I.InvoiceDate < @ThisYearStart
AND AWC.IsCancelled = 0 -- You don't have to check for non null
-- InvoiceID since a null won't join to anything
UNION ALL
SELECT 2 AS YearNum, 2010 AS TheYear
, CASE WHEN AWC.IsFee = 1 THEN AWC.Total ELSE 0 END
, DATEPART(Month, I.InvoiceDate)
FROM AllWorkOrderComponentView AWC
INNER JOIN Invoice I ON
AWC.InvoiceID = I.InvoiceID
WHERE I.InvoiceDate >= @ThisYearStart
AND AWC.IsCancelled = 0
) AS X
GROUP BY MonthNum
Something like this should work.
Todd Fifield
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply