December 13, 2011 at 12:30 pm
Hello,
A few months ago, I got involved in a project to provide our company with specific details relating to time spent vs budget hours, customer satisfaction averages, etc. At the time we were using a static front end that showed the data for current month solely based on some views I created. However, I recently started coding our own front end in VB to allow more dynamic viewing of data (history, by location, company, etc), so now the datepart(m, xxx) = datepart(m, getdate()) that was coded into the views no longer does the job. That being said, I am trying to migrate the view logic into stored procedures. I have been mostly successful in at least obtaining the data but the code is not pretty.
I would like to, as much as possible, use stored procedures since it allows for easier manipulation/interaction from the app/user to get the desired date. While I am not opposed to having the proc pull data from the view, in my current situation the date comparisons in the WHERE clause are dynamic and if I pull the WHERE out of the view and add it to the proc, the SUM functions calculate improperly so I think I need to pull the data using some form of stored procedure. I was wondering if someone could take a look at the below stored proc and give me some pointers on how to accomplish my goals more efficiently.
Example:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_TMHB]
@location AS varchar(20),
@customer AS varchar(50) = NULL,
@dataformat AS varchar(20),
@startdate AS datetime,
@enddate AS datetime
AS
IF @location = 'All' AND @customer IS NULL AND @dataformat = 'Percentage' --returns Month, Year, Percentage Hours Used of Budget--
BEGIN
WITH t1 AS (SELECT CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS Hours1,
CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('38', '39', '47', '48', '50', '51', '54', '55', '61', '62', '63') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS Hours2,
CASE Month(Date_Start)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END AS Month,
Year(Date_Start) As Year,
dbo.Company.Company_Name,
dbo.AGR_Detail.AGD_Date,
dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel
FROM dbo.AGR_Header INNER JOIN
dbo.AGR_Detail ON dbo.AGR_Header.AGR_Header_RecID = dbo.AGR_Detail.AGR_Header_RecID INNER JOIN
dbo.Time_Entry ON dbo.Time_Entry.Agr_Header_RecID = dbo.AGR_Header.AGR_Header_RecID INNER JOIN
dbo.Company ON dbo.Company.Company_RecID = dbo.AGR_Header.Company_RecID INNER JOIN
dbo.IV_Item ON dbo.AGR_Detail.IV_Item_RecID = dbo.IV_Item.IV_Item_RecID INNER JOIN
dbo.Owner_Level ON dbo.Owner_Level.Owner_Level_RecID = dbo.Company.Owner_Level_RecID
WHERE (IV_Item.Item_ID = 'TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND Date_Start >= AGD_Date
AND Date_Start <= ISNULL(AGD_Cancel, 0)
OR (IV_Item.Item_ID = 'TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND AGD_Date <= Date_Start
AND AGD_Date >= ISNULL(AGD_Cancel, 0)
GROUP BY dbo.Owner_Level.Owner_Level_RecID, dbo.Company.Company_Name, dbo.AGR_Detail.AGD_Date, dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel, Month(Date_Start), Year(Date_Start))
SELECT Month, Year,(SUM(ISNULL(Hours1,0)) + SUM(ISNULL(Hours2,0))) / SUM(AGD_Qty) * 100.00 As 'Percent'
FROM t1 As t2
GROUP BY Month, Year
ORDER BY Year, Month DESC
END
exec usp_TMHB 'All', '', 'Percentage', '2-1-2011', '12-13-2011' results in the correct data.
If I take the SELECT statement from above and place it into a view, removing the WHERE (adding it to the IF statement in the sproc instead), the SUM(AGD_Qty) is incorrect.
The only option I have found to get my results with any accuracy given the multiple possibilities for the user input options is to copy the same select statement above but changing the "SELECT FROM t1 as t2" logic based on user input. It would be much nicer to be able to have the large block of logic that is the original table always be available and then the IF statements just return different data based on the user input
Dilemma:
ELSE IF @location = 'All' AND @customer IS NULL AND @dataformat = 'Hours' --returns Month, Year, Hours Used, Total Hours Budget--
BEGIN
WITH t1 AS(SELECT CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS Hours1,
CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('38', '39', '47', '48', '50', '51', '54', '55', '61', '62', '63') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS Hours2,
CASE Month(Date_Start)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END AS Month,
Year(Date_Start) As Year,
dbo.Company.Company_Name,
dbo.AGR_Detail.AGD_Date,
dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel
FROM dbo.AGR_Header INNER JOIN
dbo.AGR_Detail ON dbo.AGR_Header.AGR_Header_RecID = dbo.AGR_Detail.AGR_Header_RecID INNER JOIN
dbo.Time_Entry ON dbo.Time_Entry.Agr_Header_RecID = dbo.AGR_Header.AGR_Header_RecID INNER JOIN
dbo.Company ON dbo.Company.Company_RecID = dbo.AGR_Header.Company_RecID INNER JOIN
dbo.IV_Item ON dbo.AGR_Detail.IV_Item_RecID = dbo.IV_Item.IV_Item_RecID INNER JOIN
dbo.Owner_Level ON dbo.Owner_Level.Owner_Level_RecID = dbo.Company.Owner_Level_RecID
WHERE (IV_Item.Item_ID = 'TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND Date_Start >= AGD_Date
AND Date_Start <= ISNULL(AGD_Cancel, 0)
OR (IV_Item.Item_ID = 'TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND AGD_Date <= Date_Start
AND AGD_Date >= ISNULL(AGD_Cancel, 0)
GROUP BY dbo.Owner_Level.Owner_Level_RecID, dbo.Company.Company_Name, dbo.AGR_Detail.AGD_Date, dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel, Month(Date_Start), Year(Date_Start))
SELECT Month, Year,(SUM(ISNULL(Hours1,0)) + SUM(ISNULL(Hours2,0))) AS 'Hours Used', SUM(AGD_Qty) As 'Budget Hours'
FROM t1 as t2
GROUP BY Year, Month
ORDER BY Year, Month DESC
END
ELSE IF @location != 'All' AND @customer IS NULL AND @dataformat = '' --Populates form combo box with company names based on location--
BEGIN
WITH t1 AS(SELECT CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS Hours1,
CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('38', '39', '47', '48', '50', '51', '54', '55', '61', '62', '63') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS Hours2,
CASE Month(Date_Start)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END AS Month,
Year(Date_Start) As Year,
dbo.Company.Company_Name,
dbo.AGR_Detail.AGD_Date,
dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel,
CASE WHEN Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN 'Chicago' ELSE 'Cincinnati' END AS Location
FROM dbo.AGR_Header INNER JOIN
dbo.AGR_Detail ON dbo.AGR_Header.AGR_Header_RecID = dbo.AGR_Detail.AGR_Header_RecID INNER JOIN
dbo.Time_Entry ON dbo.Time_Entry.Agr_Header_RecID = dbo.AGR_Header.AGR_Header_RecID INNER JOIN
dbo.Company ON dbo.Company.Company_RecID = dbo.AGR_Header.Company_RecID INNER JOIN
dbo.IV_Item ON dbo.AGR_Detail.IV_Item_RecID = dbo.IV_Item.IV_Item_RecID INNER JOIN
dbo.Owner_Level ON dbo.Owner_Level.Owner_Level_RecID = dbo.Company.Owner_Level_RecID
WHERE (IV_Item.Item_ID = 'TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND Date_Start >= AGD_Date
AND Date_Start <= ISNULL(AGD_Cancel, 0)
OR (IV_Item.Item_ID = 'TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND AGD_Date <= Date_Start
AND AGD_Date >= ISNULL(AGD_Cancel, 0)
GROUP BY Owner_Level.Owner_Level_RecID,dbo.Company.Company_Name, dbo.AGR_Detail.AGD_Date, dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel, Month(Date_Start), Year(Date_Start))
SELECT DISTINCT Company_Name, Location
FROM t1 as t2
WHERE Location = @location
END
I realize that by posting here I am opening myself up to criticism in general that doesn't necessarily apply to the question at hand. Since this is my first attempt at a stored procedure, I appreciate any help in becoming more efficient in my practices.
Thanks
December 13, 2011 at 1:24 pm
jsmith08 (12/13/2011)
I realize that by posting here I am opening myself up to criticism in general that doesn't necessarily apply to the question at hand. Since this is my first attempt at a stored procedure, I appreciate any help in becoming more efficient in my practices.Thanks
Relax, we don't bite. Well, much. Well, most of us.. 😉
Your code at first glance seems alright, but I'd have to dig into the full structure to really nail it down. However, something like this is incredibly difficult to troubleshoot without sample data to illustrate the failure. You're also showing incomplete code which makes it a little harder.
You mention taking the select statement and placing it in a view, which of the select statements? I'm assuming it's the one with all the parameters being referenced in the WHERE clause.
The first thing I'd mention regarding the reason you're getting inaccurate results is the fact that your group by is doing the equivalent of a distinct. You're losing rows. This may be the purpose of your code so I'm not sure. What this in effect does is this:
Row 1 ABC $40
Row 2 ABC $40
Row 3 DEF $90
Now, if you collapse them first, you get $130, if you sum on the full count (prior to the distinction) you get $170.
If you want these collapsed, you're going to be forced to use a subquery of some kind, as you've done here with the CTE. You could make your life a little easier and make it more obvious by simply using SELECT DISTINCT instead of the group by, however. If you want the real totals, you don't want to use a group by in the first query and allow the duplicate rows to flow through. This would explain your discrepency. With more data and more information, we can probably help you nail down any other issues you may be having.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 13, 2011 at 2:35 pm
Thanks Craig. Letting that soak in. Meanwhile, per your request...
Here is a sample of the 'raw' data I had coming out of the view, with nothing in the WHERE clause other than AGD_Qty > 0 and Item_ID = 'ITM TMHB' information:
ChiHours CinHours MonthYearCompany_NameAGD_Date AGD_Qty AGD_Cancel
NULL 91.18 August2011National xxx 2011-10-01 00:00:00.000 60.50 NULL
NULL 67.36September2011National xxx 2011-10-01 00:00:00.000 60.50 NULL
NULL 130.57 October2011National xxx 2011-10-01 00:00:00.000 60.50 NULL
NULL 95.38November2011National xxx 2011-10-01 00:00:00.000 60.50 NULL
NULL 41.63December2011National xxx 2011-10-01 00:00:00.000 60.50 NULL
NULL 98.78July 2011xxxx Group 2011-10-01 00:00:00.000 74.50 NULL
NULL 123.09 August2011xxxx Group 2011-10-01 00:00:00.000 74.50 NULL
NULL 116.45 September2011xxxx Group 2011-10-01 00:00:00.000 74.50 NULL
NULL 56.80October2011xxxx Group 2011-10-01 00:00:00.000 74.50 NULL
NULL 73.42November2011xxxx Group 2011-10-01 00:00:00.000 74.50 NULL
NULL 17.80December2011xxxx Group 2011-10-01 00:00:00.000 74.50 NULL
Getting current month was easy enough and removed any room for duplicates in the results, causing the AGD_Qty to get skewed.
Historically, I would simply run:
SELECT CAST(SUM(ISNULL(ChicagoHours, 0) + ISNULL(CincinnatiHours, 0)) / SUM(AGD_Qty) * 100 AS decimal(18,2)) AS TotalPercentage FROM TIG_TMHB
Or
SELECT SUM(ISNULL(ChicagoHours, 0) + ISNULL(CincinnatiHours, 0)) AS 'Actual Hours', SUM(AGD_Qty) AS TMHB FROM TIG_v_TMHB_ByLoc
Against this view:
WITH t1 AS (SELECT CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN UM(ISNULL(dbo.Time_Entry.Hours_Actual, 0))
END AS ChicagoHours, CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('38', '39', '47', '48', '50', '51', '54', '55', '61', '62', '63')
THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS CincinnatiHours, dbo.Company.Company_Name, dbo.IV_Item.Item_ID,
dbo.AGR_Detail.AGD_Date, dbo.AGR_Detail.AGD_Qty, dbo.AGR_Detail.AGD_Cancel, dbo.Owner_Level.Owner_Level_RecID,
dbo.Company.Company_RecID
FROM dbo.AGR_Header INNER JOIN
dbo.AGR_Detail ON dbo.AGR_Header.AGR_Header_RecID = dbo.AGR_Detail.AGR_Header_RecID INNER JOIN
dbo.Time_Entry ON dbo.Time_Entry.Agr_Header_RecID = dbo.AGR_Header.AGR_Header_RecID INNER JOIN
dbo.Company ON dbo.Company.Company_RecID = dbo.AGR_Header.Company_RecID INNER JOIN
dbo.IV_Item ON dbo.AGR_Detail.IV_Item_RecID = dbo.IV_Item.IV_Item_RecID INNER JOIN
dbo.Owner_Level ON dbo.Owner_Level.Owner_Level_RecID = dbo.Company.Owner_Level_RecID
WHERE (dbo.IV_Item.Item_ID = 'ITM TMHB') AND (DATEPART(m, dbo.Time_Entry.Date_Start) = DATEPART(m, GETDATE())) AND (DATEPART(yyyy,
dbo.Time_Entry.Date_Start) = DATEPART(yyyy, GETDATE())) AND (DATEPART(yyyy, dbo.AGR_Detail.AGD_Date) <= DATEPART(yyyy, GETDATE())) AND
(DATEPART(m, dbo.AGR_Detail.AGD_Date) <= DATEPART(m, GETDATE())) AND (dbo.AGR_Detail.AGD_Qty > 0)
GROUP BY dbo.Owner_Level.Owner_Level_RecID, dbo.Company.Company_Name, dbo.IV_Item.Item_ID, dbo.AGR_Detail.AGD_Date, dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel, dbo.Company.Company_RecID)
SELECT Company_RecID, Company_Name, ChicagoHours, CincinnatiHours, AGD_Qty, CASE WHEN Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59')
THEN 'Chicago' ELSE 'Cincinnati' END AS Location
FROM t1 as t2
This resulted in the following since I did not need to pull Month/Year at the time:
TotalPercentage
61.67
Actual HoursTMHB
698.62 1132.05
Fast forward to now, I had to add in the additional logic for the date checking so that I can pull historical data as well as current and I've yet to make some more needed tweaks for setting "Location" (as seen in the group by from the original view).
So this:
exec usp_TIG_TMHB 'All', NULL,'Percentage', '11-01-2011', '12-13-2011'
Against:
ALTER procedure [dbo].[usp_TIG_TMHB]
@location AS varchar(20),
@customer AS varchar(50) = NULL,
@dataformat AS varchar(20),
@startdate AS datetime,
@enddate AS datetime
AS
IF @location = 'All' AND @customer IS NULL AND @dataformat = 'Percentage'
BEGIN
WITH t1 AS (SELECT CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS ChiHours,
CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('38', '39', '47', '48', '50', '51', '54', '55', '61', '62', '63') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS CinHours,
CASE Month(Date_Start)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END AS Month,
Year(Date_Start) As Year,
dbo.Company.Company_Name,
dbo.AGR_Detail.AGD_Date,
dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel
FROM dbo.AGR_Header INNER JOIN
dbo.AGR_Detail ON dbo.AGR_Header.AGR_Header_RecID = dbo.AGR_Detail.AGR_Header_RecID INNER JOIN
dbo.Time_Entry ON dbo.Time_Entry.Agr_Header_RecID = dbo.AGR_Header.AGR_Header_RecID INNER JOIN
dbo.Company ON dbo.Company.Company_RecID = dbo.AGR_Header.Company_RecID INNER JOIN
dbo.IV_Item ON dbo.AGR_Detail.IV_Item_RecID = dbo.IV_Item.IV_Item_RecID INNER JOIN
dbo.Owner_Level ON dbo.Owner_Level.Owner_Level_RecID = dbo.Company.Owner_Level_RecID
WHERE (IV_Item.Item_ID = 'ITM TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND Date_Start >= AGD_Date
AND Date_Start <= ISNULL(AGD_Cancel, 0)
OR (IV_Item.Item_ID = 'ITM TMHB')
AND AGD_Qty > 0
AND Date_Start >= @startdate
AND Date_Start <= @enddate
AND AGD_Date <= Date_Start
AND AGD_Date >= ISNULL(AGD_Cancel, 0)
GROUP BY dbo.Owner_Level.Owner_Level_RecID, dbo.Company.Company_Name, dbo.AGR_Detail.AGD_Date, dbo.AGR_Detail.AGD_Qty,
dbo.AGR_Detail.AGD_Cancel, Month(Date_Start), Year(Date_Start))
SELECT Month, Year,(SUM(ISNULL(ChiHours,0)) + SUM(ISNULL(CinHours,0))) / SUM(AGD_Qty) * 100.00 As 'Percent TMHB'
FROM t1 As t2
GROUP BY Month, Year
ORDER BY Year
Results in:
MonthYearPercent TMHB
December201161.693300
November2011138.240700
Everything jives there (someone may have entered a time entry while I have been typing so the percentage is slightly different), so no complaints in functionality. Only complaint is storing the bulk of the logic "somewhere else", like another procedure, so that I don't need to copy/paste in each IF/ElseIf.
Edit for more details on the discrepancy when removing where from the sproc and leveraging a/the view:
View:
SELECT CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0)) END AS ChiHours,
CASE WHEN dbo.Owner_Level.Owner_Level_RecID IN ('38', '39', '47', '48', '50', '51', '54', '55', '61', '62', '63') THEN SUM(ISNULL(dbo.Time_Entry.Hours_Actual, 0))
END AS CinHours, dbo.Company.Company_Name, dbo.AGR_Detail.AGD_Date, dbo.Time_Entry.Date_Start, dbo.AGR_Detail.AGD_Qty, dbo.AGR_Detail.AGD_Cancel,
CASE WHEN Owner_Level.Owner_Level_RecID IN ('2', '44', '45', '46', '52', '53', '58', '59') THEN 'Chicago' ELSE 'Cincinnati' END AS Location, CASE Month(Date_Start)
WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN
8 THEN 'August' WHEN 9 THEN 'September' WHEN 10 THEN 'October' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END AS Month,
YEAR(dbo.Time_Entry.Date_Start) AS Year
FROM dbo.AGR_Header INNER JOIN
dbo.AGR_Detail ON dbo.AGR_Header.AGR_Header_RecID = dbo.AGR_Detail.AGR_Header_RecID INNER JOIN
dbo.Time_Entry ON dbo.Time_Entry.Agr_Header_RecID = dbo.AGR_Header.AGR_Header_RecID INNER JOIN
dbo.Company ON dbo.Company.Company_RecID = dbo.AGR_Header.Company_RecID INNER JOIN
dbo.IV_Item ON dbo.AGR_Detail.IV_Item_RecID = dbo.IV_Item.IV_Item_RecID INNER JOIN
dbo.Owner_Level ON dbo.Owner_Level.Owner_Level_RecID = dbo.Company.Owner_Level_RecID
WHERE (dbo.IV_Item.Item_ID = 'ITM TMHB') AND (dbo.AGR_Detail.AGD_Qty > 0)
GROUP BY dbo.Owner_Level.Owner_Level_RecID, dbo.Company.Company_Name, dbo.AGR_Detail.AGD_Date, dbo.AGR_Detail.AGD_Qty, dbo.AGR_Detail.AGD_Cancel,
dbo.Time_Entry.Date_Start
Query that would in theory be passed from the sproc if I updated the code in the IF.....SELECT statement to include the WHERE clause:
SELECT Month, Year,(SUM(ISNULL(ChiHours,0)) + SUM(ISNULL(CinHours,0))) AS 'Hours Used', SUM(AGD_Qty) As 'Budget Hours'
FROM TIG_v_TMHB
WHERE Date_Start >= '02-11-2011'
AND Date_Start <= '12-11-2011'
AND Date_Start >= AGD_Date
AND Date_Start <= ISNULL(AGD_Cancel, 0)
OR Date_Start >= '02-11-2011'
AND Date_Start <= '12-11-2011'
AND AGD_Date <= Date_Start
AND AGD_Date >= ISNULL(AGD_Cancel, 0)
GROUP BY Month, YEAR
ORDER BY YEAR, MONTH DESC
Theoretical Exec:
Exec usp_TIG_TMHB 'All', NULL, 'Hours', '02-11-2011', '12-11-2011'
Results:
MonthYearHours UsedBudget Hours
October20111718.2521573.56
November20111573.9420870.34
December2011594.497077.64
The "Hours Used" appears to be correct but since the 'raw' data from the view potentially has multiple rows/results per customer, the AGD_Qty gets added multiple times. I'd gladly use DISTINCT in this scenario but since not every company has a unique number of budgeted hours, I would be losing data.
So what ultimately needs to happen is some manageable way to have any one (or more) of the following happen with some easy to use/manage/read SQL logic. :
Between dates mm-dd-yyyy and mm-dd-yyyy, SUM all hours spent, separated by office location (Chicago/Cincinnati), then divide by the total budgeted hours.
Between dates mm-dd-yyyy and mm-dd-yyyy, SUM all hours spent for Chicago, then divide by the total budgeted hours for chicago
Between dates mm-dd-yyyy and mm-dd-yyyy, SUM all hours spent for customer xyz, then divide by the total budgeted hours for customer xyz
Thanks in advance!
December 14, 2011 at 12:53 pm
yap thats why your company remove this mistake i can understand that
and good improvment by you and your company
thanks for share it here
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply