USE [IST278EagleCorp13-1]

GO

CREATE PROCEDURE spInvReductLetter
AS
DECLARE @EmployeeCount INT
DECLARE @CurrentEmployeeID varchar(10)
DECLARE @LastName varchar(20)
DECLARE @FirstName varchar(15) 
DECLARE @CSO varchar(20)
DECLARE @Date as varchar(12)
DECLARE @RegState as varchar(2);

SET @CurrentEmployeeID = '            '
SET @Date = CONVERT(char(12), GetDate(), 107)

SELECT DISTINCT @RegState =
CASE State
	WHEN 'SC' THEN 'South Carolina'
	WHEN 'NC' THEN 'North Carolina'
	WHEN 'GA' THEN 'Georgia'
	WHEN 'FL' THEN 'Florida'
	WHEN 'MS' THEN 'Mississippi'
	WHEN 'TN' THEN 'Tennessee'
	WHEN 'AL' THEN 'Alabama'
	WHEN 'KY' THEN 'Kentucky'
	WHEN 'VA' THEN 'Virginia'
ELSE 'Not In District'
END
FROM Customer;

SELECT @CSO = FirstName + ' ' + LastName
FROM Employee WHERE JobTitle = 'Chief Sales Officer'

SELECT @EmployeeCount = COUNT(*) FROM Employee
WHERE ReleaseDate IS NULL

If @EmployeeCount > 0
		BEGIN
	WHILE @EmployeeCount > 0
		BEGIN

SELECT @CurrentEmployeeID = MIN(EmployeeID)
FROM Employee WHERE ReleaseDate IS NULL 
AND EmployeeID > @CurrentEmployeeID
 SELECT @LastName = LastName, @FirstName = FirstName
 FROM Employee WHERE  EmployeeID = @CurrentEmployeeID

PRINT ' '
PRINT 'Date: ' + @Date
PRINT ' '
PRINT 'Dear, ' + @FirstName + ' ' + @LastName + ','
PRINT ' '
PRINT 'Eagle is pleased to offer you a 20% discount on any purchase you
make prior to 11:55 p.m. on '+ CONVERT(varchar, DATEADD(dd, 14, GETDATE()), 107) + 
'.  This limited time offer is our best 
offer of the year!  You can view our entire product line and place your order at 
Eagle20Deal.com.  Make sure you place your order by '+ CONVERT(varchar, DATEADD(dd, 14, GETDATE()), 107)+' 
because this offer expires at 11:55 p.m. on that day.'
PRINT ' '
PRINT 'Sincerely, ' + @CSO
PRINT 'Chief Sales Officer'
PRINT 'Eagle Corporation';

SET @EmployeeCount = @EmployeeCount - 1
	END
END
ELSE
BEGIN
	PRINT ' '
	PRINT 'There are no more employees in this region'
	PRINT ' '
END;

GO

EXEC spInvReductLetter;


Date: Oct 07, 2014
 
Dear, Jim Manaugh,
 
Eagle is pleased to offer you a 20% discount on any purchase you
make prior to 11:55 p.m. on Oct 21, 2014.  This limited time offer is our best 
offer of the year!  You can view our entire product line and place your order at 
Eagle20Deal.com.  Make sure you place your order by Oct 21, 2014 
because this offer expires at 11:55 p.m. on that day.
 
Sincerely, Gary German
Chief Sales Officer
Eagle Corporation
 
Date: Oct 07, 2014
 
Dear, Joanne Rosner,
 
Eagle is pleased to offer you a 20% discount on any purchase you
make prior to 11:55 p.m. on Oct 21, 2014.  This limited time offer is our best 
offer of the year!  You can view our entire product line and place your order at 
Eagle20Deal.com.  Make sure you place your order by Oct 21, 2014 
because this offer expires at 11:55 p.m. on that day.
 
Sincerely, Gary German
Chief Sales Officer
Eagle Corporation
 
Date: Oct 07, 2014
 
Dear, Rita Bush,
 
Eagle is pleased to offer you a 20% discount on any purchase you
make prior to 11:55 p.m. on Oct 21, 2014.  This limited time offer is our best 
offer of the year!  You can view our entire product line and place your order at 
Eagle20Deal.com.  Make sure you place your order by Oct 21, 2014 
because this offer expires at 11:55 p.m. on that day.
 
Sincerely, Gary German
Chief Sales Officer
Eagle Corporation