fattyfatpants
Programmer
Platform: SQL Server 2000
I work for a CPA firm and the managers and Partners want to know that their staff are not only working a desired amount of hours but that the hours they are working are considered billable time.
The following Stored Procedure tries to perform an hours analysis for Curren Month of Current Year, Current Month of Prior Year, Current Year To Date, Prior Year To Date, and 2 Years Prior Year To Date.
First of all, it doesn't like my CAST statements up near the top on @PriorYr and @TwoPriorYr.
Second, I plan on using all of this in a single Crystal Report (CR version 9) as opposed to multiple subreports so should I use a UNION statement between all of the SELECT statements or how could I go about doing it?
Lastly, could you offer me any improvements on the structure of the procedure?
Thanks a million!!!
I work for a CPA firm and the managers and Partners want to know that their staff are not only working a desired amount of hours but that the hours they are working are considered billable time.
The following Stored Procedure tries to perform an hours analysis for Curren Month of Current Year, Current Month of Prior Year, Current Year To Date, Prior Year To Date, and 2 Years Prior Year To Date.
Code:
CREATE PROCEDURE sp_test
@Year varchar(4), --will hold the fiscal year wanted
@CurrentMonth varchar(2) --will hold the current month in fiscal year
AS
Declare @CurrentYrMonth varchar(4), --will hold the current month and two digit fiscal year
@PriorYrMonth varchar(4), --will hold the current month and two digit fiscal year of the prior year
@CurrentYr varchar(2), --will hold the two digit value of the current fiscal year
@PriorYr varchar(2), --will hold the prior two digit fiscal year
@TwoPriorYr varchar(2), --will hold the two digit fiscal year for 2 years ago
@CurrentYrToDate varchar(85), --will hold the current fiscal year up to date so if I want to see from 0604 to 0904 it will hold 0604, 0704, 0804, 0904
@PriorYrToDate varchar(85), --will hold the previous fiscal year up to date
@TwoPriorYrToDate varchar(85), --will hold the fiscal year two years ago up to date
@NumMonths int, --will be used for the YTD computations, will calculate the number of months from June to current month
@DefaultMonth varchar(2), --will be used as the base for the fiscal year, default to 06 for June
@LoopCount int --will be used as incrementer in the While loop
Set @CurrentYr = RIGHT(@Year, 2) --Ex. 04 for 2004 if @Year = 2004
Set @PriorYr = RIGHT(CAST(CAST(@Year AS int) -1) AS varchar(2)), 2) --Ex. 03 for 2003 if @Year = 2004
Set @TwoPriorYr = RIGHT(CAST(CAST(@Year AS int) - 2) AS varchar(2)), 2) --Ex. 02 for 2002 if @Year = 2004
Set @CurrentYrMonth = @CurrentMonth + @CurrentYr --Ex. 0904 if @CurrentMonth = 09 and @CurrentYr = 04
Set @PriorYrMonth = @CurrentMonth + @PriorYr --Ex. 0903 if @CurrentMonth = 09 and @PriorYr = 03
Set @DefaultMonth = 06 --be sure to start with June since it is the beginning of the fiscal year
Set @LoopCount = 1 --set the value of the loop
Set @NumMonths = CAST(@CurrentMonth AS int) - 6 --Ex. if we want year to date and this month is 0904 we want to have 0604, 0704, 0804, 0904 included in the query so @NumMonths = 3
Set @CurrentYrToDate = @DefaultMonth + @CurrentYr --Ex. will hold 0604 if @DefaultMonth holds 06 and @CurrentYr holds 04
Set @PriorYrToDate = @DefaultMonth + @PriorYr --Ex. will hold 0603 if @DefaultMonth holds 06 and @PriorYr holds 03
Set @TwoPriorYrToDate = @DefaultMonth + @TwoPriorYr --Ex. will hold 0602 if @Default holds 06 and @TwoPriorYr holds 02
--While loop is used to build the strings for the queries
WHILE @LoopCount <= @NumMonths
BEGIN
Set @CurrentYrToDate = @CurrentYrToDate + ', ' + (@DefaultMonth + @LoopCount) + @CurrentYr
Set @PriorYrToDate = @PriorYrToDate + ', ' + (@DefaultMonth + @LoopCount) + @PriorYr
Set @TwoPriorYrToDate = @TwoPriorYrToDate + ', ' + (@DefaultMonth + @LoopCount) + @TwoPriorYr
Set @LoopCount = @LoopCount + 1
END
/*PRINT @CurrentYrToDate
PRINT @PriorYrToDate
PRINT @TwoPriorYrToDate*/
SELECT SUM(timewahs.thhrwkdw) CurrentChargeable, SUM(timewahs.thhrwkdw) + SUM(timewahs.thhrnbdw) as CurrentTotal, tkdept
FROM timekeep INNER JOIN timewahs ON timekeep.tkinit = timewahs.thtk
WHERE thper = @CurrentYrMonth
GROUP BY tkdept
SELECT SUM(timewahs.thhrwkdw) PriorChargeable, SUM(timewahs.thhrwkdw) + SUM(timewahs.thhrnbdw) as PriorTotal, tkdept
FROM timekeep INNER JOIN timewahs ON timekeep.tkinit = timewahs.thtk
WHERE thper = @PriorYrMonth
GROUP BY tkdept
SELECT SUM(timewahs.thhrwkdw) YTDChargeable, SUM(timewahs.thhrwkdw) + SUM(timewahs.thhrnbdw) as YTDTotal, tkdept
FROM timekeep INNER JOIN timewahs ON timekeep.tkinit = timewahs.thtk
WHERE thper IN (@CurrentYrToDate)
GROUP BY tkdept
SELECT SUM(timewahs.thhrwkdw) PriorYTDChargeable, SUM(timewahs.thhrwkdw) + SUM(timewahs.thhrnbdw) as PriorYTDTotal, tkdept
FROM timekeep INNER JOIN timewahs ON timekeep.tkinit = timewahs.thtk
WHERE thper IN (@PriorYrToDate)
GROUP BY tkdept
SELECT SUM(timewahs.thhrwkdw) TwoYTDChargeable, SUM(timewahs.thhrwkdw) + SUM(timewahs.thhrnbdw) as TwoYTDTotal, tkdept
FROM timekeep INNER JOIN timewahs ON timekeep.tkinit = timewahs.thtk
WHERE thper IN (@TwoPriorYrToDate)
GROUP BY tkdeptFirst of all, it doesn't like my CAST statements up near the top on @PriorYr and @TwoPriorYr.
Second, I plan on using all of this in a single Crystal Report (CR version 9) as opposed to multiple subreports so should I use a UNION statement between all of the SELECT statements or how could I go about doing it?
Lastly, could you offer me any improvements on the structure of the procedure?
Thanks a million!!!
