Suggestions as well as help... 1

[fattyfatpants]

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.

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 tkdept

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!!!

 

[donutman]

The reason I didn't point you toward Esquared well written FAQ on parsing a string is because I think it would be silly to use SQL to build the string only to parse it with more SQL. However, the FAQ is also a good learning tool as suggested by SQLSister.
User Defined Functions should be avoided if possible and your situation shouldn't require them. If you want alternative code let us know otherwise I'll assume that you're going to use the UDFs.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I would point out I didn't write the FAQs. And no need to apologize; I wasn't insulted.

Questions about posting. See faq183-874

 

[fattyfatpants]

Thanks again for pointing me in the right direction on this...I have come across something new; in two of my SELECT statements I want all departments returned along with a current and past employee count regardless if they have employees in them...naturally I use a LEFT JOIN to create this effect but it isn't working

The following is part of a UNION ALL statement:

.
.
.
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, tkdept, head1, isnull(COUNT(tkinit), 0), isnull(NULL, 0)
  FROM deptlab LEFT JOIN (timewahs INNER JOIN timekeep ON timewahs.thtk = timekeep.tkinit) ON deptlab.delcode = timekeep.tkdept
WHERE thper = @Period
AND (thhrwkdw > 0 OR thhrnbdw > 0)
 GROUP BY tkdept, head1

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, tkdept, head1, NULL, isnull(COUNT(tkinit), 0)
  FROM deptlab LEFT JOIN (timewahs INNER JOIN timekeep ON timewahs.thtk = timekeep.tkinit) ON deptlab.delcode = timekeep.tkdept
WHERE thper = @PrevYrMonth
AND (thhrwkdw > 0 OR thhrnbdw > 0)
 GROUP BY tkdept, head1

Basically these statements are saying give me the department number, department name, and a count of all employees in those departments for the current month in the current year as well as the current month last year. I only want to count an employee if he/she has any worked hours or billable hours for the periods and if there are none then I want to return 0.

If I leave out the AND clause it returns the departments I want but it also gives bad results. For example, Information Technology Services for the current year (1st statement) should return 0 but it returns 1, which is incorrect. Doing this on the previous year (second statement) will also return 1 for Information Techology Services but this is correct. Anything I am missing here??