ALL HAND CODED T-SQL - SAMPLES
All Code is for Demo Use Only
*** USER LOGIN STORED PROC FIRED OFF BY ASP SCRIPTLET | |
1 | CREATE Proc asp_AddCustomer @EmailAddress varchar(150), @ServiceCode char(2) As Set NoCount On Update Customer Set Status = 4, EditDate = Convert(char(12),GetDate(),1) Where EmailAddress = @EmailAddress Insert Into Customer(EmailAddress,SubscriptionCode,SignupDate,Status,EditDate) Values(@EmailAddress,@ServiceCode,Convert(char(12),GetDate(),1),1,Convert(char(12),GetDate(),1)) Set NoCount Off |
*** STORED PROCEDURE SNIPPET [OF 1250 LINES OF CODE] *** CALCULATES STATISTICS AND POPULATES DATAMART *** CURSOR FIRED OFF BY TIMED TRIGGER |
|
2 | ------------------------------------------------------ Delete YDayStatsSummary -- Clears "Old" stats SET DATEFIRST 1 -- Sets Sunday as the first day of the week ------------------------------------------------------ -- Cursor to perform statistical calculation routine on all "UserID's" Declare Hcpr Cursor For Select Distinct UserID From UserTable Where SecurityID = '2' AND Status = '1' Open Hcpr Fetch Next From Hcpr Into @HcprID While @@Fetch_Status = 0 Begin If Exists(Select UserID From UserTable Where UserID = @HcprID ) Begin ------------------------------------------------------ -- Previous SEASON MLB STATS SET @Temp = ( SELECT SUM(ALL dayNumofPicksMLBTotal) FROM DailyStats d Join UserTable u On d.HcprID = u.UserID WHERE d.HcprID = @HcprID AND d.StatDate >= @MLBSeasonStartPrevious AND @MLBSeasonEndPrevious <= d.StatDate ) IF @Temp <> 0 BEGIN SET @LastSeasonMLBWinP = ( SELECT CAST(ROUND(SUM(ALL dayNumofWinsMLB)/SUM(ALL dayNumofPicksMLBTotal)*100, 0) AS int) FROM DailyStats d Join UserTable u On d.HcprID = u.UserID WHERE d.HcprID = @HcprID AND d.StatDate >= @MLBSeasonStartPrevious AND @MLBSeasonEndPrevious <= d.StatDate ) END ELSE BEGIN SET @LastSeasonMLBWinP = 0 END SELECT @LastSeasonMLBWins = SUM(ALL dayNumofWinsMLB), @LastSeasonMLBLoss = SUM(ALL dayNumofLossMLB), @LastSeasonMLBPicks = SUM(ALL dayNumofPicksMLBTotal) FROM DailyStats d Join UserTable u On d.HcprID = u.UserID WHERE d.HcprID = @HcprID AND d.StatDate >= @MLBSeasonStartPrevious AND @MLBSeasonEndPrevious <= d.StatDate ------------------------------------------------------ Insert Into YDayStatsSummary( HcprID, ThisWeekWins, ThisWeekLoss, ThisWeekPicks, ThisWeekWinP, ... ) Values( @LastSeasonCBBWins, @LastSeasonCBBLoss, @LastSeasonCBBPicks, @LastSeasonCBBWinP, ... ) ------------------------------------------------------ END -- Start Loop Again Fetch Next From Hcpr Into @HcprID END -- End Loop Close Hcpr -- Close Cursor Deallocate Hcpr -- END |
*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE] *** AUTO EMAILS SPECIFIED USERS THAT HAVE NOT COMPLETED A REQUIRED TASK *** EMAILER FIRED OFF BY TIMED TRIGGER |
|
3 | -- EMAIL ADMIN & HANDICAPPERS CODE DECLARE @em1 varchar(80), @bdy1 varchar(100), @queryCode varchar(980), @subj varchar(85), @cpy_recpt varchar(28) SET @em1 = 'user1@company.com;user2@company.com;user3@company.com' SET @bdy1 = 'SGC SYSTEM WARNING: Please Update Previous Pick Results ASAP. Do Not reply to this email.' SET @subj = 'SGC SYSTEM WARNING: Previous Pick Results Require Immediate Attention' SET @cpy_recpt = 'user4@company.com' SET @queryCode = 'SELECT Convert(char(11),p.Play_Date) as Play_Date, Pick_Sport, u.FirstName AS HndCpr, SGCTM.TM_Name as FavoriteTeam, HMTEAM.TM_Name as HomeTeam, AWAYTM.TM_Name as AwayTeam, win_loss AS Result, PickType, PickSign, Pick_Spread FROM SportsTeller..Picks p Join SportsTeller..UserTable u On p.UserID = u.UserID INNER JOIN SportsTeller..Teams AS HMTEAM ON p.HM_TM_ID = HMTEAM.Team_ID INNER JOIN SportsTeller..Teams AS AWAYTM ON p.Opp_ID = AWAYTM.Team_ID INNER JOIN SportsTeller..Teams AS SGCTM ON p.SGCPickTM = SGCTM.Team_ID WHERE win_loss = 0 ORDER BY Play_Date ASC' EXEC master.dbo.xp_sendmail @recipients = @em1, @message = @bdy1, @query = @queryCode, @subject = @subj, @copy_recipients = @cpy_recpt PRINT 'Request to Update Pick Results Have Been Emailed' |
*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE] *** CALCULATES STATISTICS AND RETURNS TO ASP PAGE *** ROUTINE FIRED ON REQUEST BY ASP SCRIPTLET |
|
4 | ---------------------------------------- CREATE PROCEDURE Get_AllCurrentCBBSeasonStats AS DECLARE @CBBSeasonStart datetime DECLARE @CBBSeasonEnd datetime SET @CBBSeasonStart = (SELECT Season_Start FROM Seasons WHERE Sport = 'CBB' AND Season_Type = 'Regular' AND Season = 'Current') SET @CBBSeasonEnd = (SELECT Season_End FROM Seasons WHERE Sport = 'CBB' AND Season_Type = 'Regular' AND Season = 'Current') SELECT d.HcprID, u.FirstName AS HndCpr, SUM(ALL dayNumofWinsCBB) AS CBBSeasonWins, SUM(ALL dayNumofLossCBB) AS CBBSeasonLoss, SUM(ALL dayNumofPicksCBBTotal) AS CBBSeasonPicksTotal, CAST(ROUND(SUM(ALL dayNumofWinsCBB)/SUM(ALL dayNumofPicksCBBTotal)*100, 0) AS int) AS [CBBWin% This Season] From DailyStats d Join UserTable u On d.HcprID = u.UserID WHERE d.StatDate >= @CBBSeasonStart AND @CBBSeasonEnd > GetDate() GROUP BY u.FirstName, HcprID GO |
*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE] *** CALCULATES DAILY WEBSITE HITS (FROM SRVR LOGS) AND POPULATES DATAMART *** ROUTINE FIRED OFF BY TIMED TRIGGER |
|
5 | ---------------------------------------- CREATE PROC usp_updateSGCLogsdaily AS DECLARE @StartDate Char(11) -- e.g. Mar 15 2003 DECLARE @avail numeric BEGIN TRANSACTION SET @StartDate = (SELECT MAX(Convert(char(11),LogTime)) FROM SGCLogs..IISLog) SET @avail = (SELECT Count(*) FROM IISLogs..IISLog i HAVING MAX(Convert(char(11),i.LogTime)) > @StartDate) IF @avail <> '0' BEGIN INSERT INTO SGCLogs..IISLog SELECT * FROM IISLogs..IISLog i WHERE i.LogTime > @StartDate IF (@@ERROR <> 0) GOTO on_error -- DELETE -- FROM IISLogs..IISLog i -- WHERE i.LogTime > @StartDate -- IF (@@ERROR <> 0) GOTO on_error COMMIT TRANSACTION SELECT 'Weekly Hits and Sessions summary succeeded.' RETURN (0) on_error: ROLLBACK TRANSACTION SELECT 'Error - Weekly Hits and Sessions summary aborted.' RETURN (1) END GO |
*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE] *** CALCULATES WEEKLY MULTI-WEBSITE TRAFFIC + SESSION TOTALS (FROM SRVR LOGS) AND *** POPULATES DATAMART *** ROUTINE FIRED OFF BY TIMED TRIGGER |
|
6 | ---------------------------------------- BEGIN TRANSACTION SELECT @ToYear = DATEPART(year, GETDATE()), @ToWeek = DATEPART(week, GETDATE()) SELECT @ThisYear = MAX(TYearNumber), @ThisWeek = MAX(TWeekNumber) FROM WeekSummary SELECT @ThisWeek = @ThisWeek + 1 IF (@ThisWeek = 54) BEGIN SELECT @ThisWeek = 1 SELECT @ThisYear = @ThisYear + 1 END WHILE (@ThisYear = @ToYear AND @ThisWeek < @ToWeek) OR (@ThisYear < @ToYear) BEGIN SELECT @WDHits = SUM(THitCount), @WDKBytes = SUM(TKBytes) FROM DaySummary WHERE DATEPART(year, TSumDate) = @ThisYear AND DATEPART(week, TSumDate) = @ThisWeek AND TSiteIP = '192.168.0.228' IF (@@ERROR <> 0) GOTO on_error SELECT @WDSessions = COUNT(EventDateTime) FROM Sessions WHERE DATEPART(year, EventDateTime) = @ThisYear AND DATEPART(week, EventDateTime) = @ThisWeek AND EventType = 'New Session' AND HostIP = '192.168.0.228' IF (@@ERROR <> 0) GOTO on_error SELECT @CDHits = SUM(THitCount), @CDKBytes = SUM(TKBytes) FROM DaySummary WHERE DATEPART(year, TSumDate) = @ThisYear AND DATEPART(week, TSumDate) = @ThisWeek AND TSiteIP = '192.168.0.229' IF (@@ERROR <> 0) GOTO on_error SELECT @CDSessions = COUNT(EventDateTime) FROM Sessions WHERE DATEPART(year, EventDateTime) = @ThisYear AND DATEPART(week, EventDateTime) = @ThisWeek AND EventType = 'New Session' AND HostIP = '192.168.0.229' IF (@@ERROR <> 0) GOTO on_error SELECT @WAHits = SUM(THitCount), @WAKBytes = SUM(TKBytes) FROM DaySummary WHERE DATEPART(year, TSumDate) = @ThisYear AND DATEPART(week, TSumDate) = @ThisWeek AND TSiteIP = 'dtpro.dantepro.com' -- AND TSiteIP = '192.168.0.230' IF (@@ERROR <> 0) GOTO on_error SELECT @WASessions = COUNT(EventDateTime) FROM Sessions WHERE DATEPART(year, EventDateTime) = @ThisYear AND DATEPART(week, EventDateTime) = @ThisWeek AND EventType = 'New Session' AND HostIP = '192.168.0.230' IF (@@ERROR <> 0) GOTO on_error INSERT INTO WeekSummary SELECT @ThisYear, @ThisWeek, ISNULL(@WDHits, 0), ISNULL(@WDKBytes, 0), ISNULL(@CDHits, 0), ISNULL(@CDKBytes, 0), ISNULL(@WAHits, 0), ISNULL(@WAKBytes, 0), ISNULL(@WDSessions, 0), ISNULL(@CDSessions, 0), ISNULL(@WASessions, 0) IF (@@ERROR <> 0) GOTO on_error SELECT @ThisWeek = @ThisWeek + 1 IF (@ThisWeek = 54) BEGIN SELECT @ThisWeek = 1 SELECT @ThisYear = @ThisYear + 1 END END INSERT INTO RefererSummary SELECT RefYear = MAX(DATEPART(year, EventDateTime)), RefWeek = MAX(DATEPART(week, EventDateTime)), RefURL=MAX( CASE WHEN CHARINDEX('?', Referer) > 5 THEN SUBSTRING(Referer, 1, CHARINDEX('?', Referer) - 1) ELSE Referer END), RefCount=COUNT(Referer), RefHostIP = MAX(HostIP) FROM Sessions WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0 AND Referer IS NOT NULL AND Referer <> '' AND CHARINDEX('.wrox.co', Referer) = 0 AND CHARINDEX('file:', Referer) = 0 GROUP BY CASE WHEN CHARINDEX('?', Referer) > 5 THEN SUBSTRING(Referer, 1, CHARINDEX('?', Referer) - 1) ELSE Referer END, DATEPART(year, EventDateTime), DATEPART(week, EventDateTime), HostIP IF (@@ERROR <> 0) GOTO on_error INSERT INTO SessionTargetSummary SELECT TargYear = MAX(DATEPART(year, EventDateTime)), TargWeek = MAX(DATEPART(week, EventDateTime)), TargText = MAX(URL), TargCount = COUNT(URL), TargHostIP = MAX(HostIP) FROM Sessions WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0 AND URL IS NOT NULL AND URL <> '' GROUP BY URL, DATEPART(year, EventDateTime), DATEPART(week, EventDateTime), HostIP IF (@@ERROR <> 0) GOTO on_error INSERT INTO UserAgentSummary SELECT UAYear = MAX(DATEPART(year, EventDateTime)), UAWeek = MAX(DATEPART(week, EventDateTime)), UAText = MAX(UserAgent), UACount = COUNT(UserAgent), UAHostIP = MAX(HostIP) FROM Sessions WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0 AND UserAgent IS NOT NULL AND UserAgent <> '' GROUP BY UserAgent, DATEPART(year, EventDateTime), DATEPART(week, EventDateTime), HostIP IF (@@ERROR <> 0) GOTO on_error INSERT INTO CountrySummary SELECT CoYear = MAX(DATEPART(year, EventDateTime)), CoWeek = MAX(DATEPART(week, EventDateTime)), CoText = MAX( CASE WHEN CHARINDEX(',', UALanguage) > 1 THEN SUBSTRING(UALanguage, 1, CHARINDEX(',', UALanguage) - 1) ELSE UALanguage END), CoCount = COUNT(UALanguage), CoHostIP = MAX(HostIP) FROM Sessions WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0 AND UALanguage IS NOT NULL AND UALanguage <> '' GROUP BY CASE WHEN CHARINDEX(',', UALanguage) > 1 THEN SUBSTRING(UALanguage, 1, CHARINDEX(',', UALanguage) - 1) ELSE UALanguage END, DATEPART(year, EventDateTime), DATEPART(week, EventDateTime), HostIP IF (@@ERROR <> 0) GOTO on_error DELETE FROM Sessions WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0 IF (@@ERROR <> 0) GOTO on_error COMMIT TRANSACTION SELECT 'Weekly Hits and Sessions summary succeeded.' RETURN (0) on_error: ROLLBACK TRANSACTION SELECT 'Error - Weekly Hits and Sessions summary aborted.'RETURN (1) GO |
** Data Tool Used to Populate OLAP TIME Dimension Table | |
7 | CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName
char(3)) GO SET NOCOUNT ON DECLARE @FirstSat datetime, @x int SELECT @FirstSat = '1/1/2005', @x = 1 --Add WeekEnds WHILE @x < 52 BEGIN INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName) SELECT DATEADD(ww,@x,@FirstSat), 'SAT' UNION ALL SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN' SELECT @x = @x + 1 END SET NOCOUNT OFF GO -- SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate -- GO -- Add 2005 US Holidaze INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName, IsHoliday, IsUSFedHoliday, Comments) SELECT '1/1/2005', 'THU', '1', '1', 'New Years Day' UNION ALL -- SELECT '1/17/2005', 'MON', '1', '1', 'Birthday of Martin Luther King' UNION ALL -- SELECT '2/21/2005', 'MON', '1', '1', 'Washingtons Birthday' UNION ALL -- SELECT '5/30/2005', 'MON', '1', '1', 'Memorial Day' UNION ALL -- SELECT '7/4/2005', 'MON', '1', '1', 'Independence Day' UNION ALL -- SELECT '9/5/2005', 'MON', '1', '1', 'Labor Day' UNION ALL -- SELECT '10/10/2005', 'MON', '1', '1', 'Columbus Day' UNION ALL -- SELECT '11/11/2005', 'FRI', '1', '1', 'Veterans Day' UNION ALL -- SELECT '11/24/2005', 'THU', '1', '1', 'Thanksgiving Day' UNION ALL -- SELECT '12/26/2005', 'MON', '1', '1', 'Christmas Day' GO -- SELECT * FROM WeekEndsAndHolidays -- Update WeekEndsAndHolidays Set IsUSFedHoliday = '0' -- Where IsUSFedHoliday IS NULL -- Update WeekEndsAndHolidays Set Comments = '' -- Where Comments IS NULL -- WeekDays in May DECLARE @Start datetime, @End datetime SELECT @Start = '5/1/2004', @End = '6/1/2004' SELECT DATEDIFF(dd,@Start,@End)- COUNT(*) AS WeekDays FROM WeekEndsAndHolidays WHERE DayOfWeekDate BETWEEN @Start AND @End GO |
** DATAPUMP SCRIPT - Created to Extract, Transform, & Load OLTP Data into a Data Mart for OLTP Analystics | |
8 | if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Time]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Time] GO CREATE TABLE [dbo].[Time] ( [TimeID] [int] NOT NULL , [Date] [datetime] NOT NULL , [SeasonID] [int] NOT NULL , [DayOfWeek] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Month] [numeric](10, 0) NULL , [Day] [numeric](18, 0) NULL , [Year] [numeric](10, 0) NULL , [Quarter] [numeric](10, 0) NULL , [DayOfYear] [numeric](10, 0) NULL , [Holiday] [numeric](18, 0) NULL , [Weekend] [numeric](18, 0) NULL , [YearMonth] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [WeekOfYear] [numeric](10, 0) NULL ) ON [PRIMARY] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO -------------------------------------------------- DECLARE @PickNum int DECLARE @SeasonID int DECLARE @Date datetime DECLARE @DayOfWeek char(20) DECLARE @YearMonth char(10) DECLARE @Day numeric DECLARE @Year numeric DECLARE @Quarter numeric DECLARE @DayOfYear numeric DECLARE @Holiday numeric DECLARE @Weekend numeric DECLARE @WeekOfYear numeric DECLARE @Month numeric -------------------------------------------------- Declare cPicks Cursor For Select p.Pick_Num AS [Pick#], p.SeasonID AS [SeasonID], p.Play_Date AS [Date], DATENAME ( dw , p.Play_Date ) AS DayOfWeek, DATENAME ( mm , p.Play_Date ) AS YearMonth, DATEPART ( dd , p.Play_Date ) AS [Day], DATEPART ( yy , p.Play_Date ) AS [Year], DATEPART ( qq , p.Play_Date ) AS [Quarter], DATEPART ( dy , p.Play_Date ) AS DayOfYear, Holiday = (Select COUNT(*) From SGCDataMart..WeekEndsAndHolidays Where DayOfWeekDate = p.Play_Date), DATEPART ( wk , p.Play_Date ) AS WeekOfYear, DATEPART ( mm , p.Play_Date ) AS [Month] From SportsTeller..Picks p -- Select * From SportsTeller..Picks p -------------------------------------------------- Open cPicks Fetch Next From cPicks Into @PickNum, @SeasonID, @Date, @DayOfWeek, @YearMonth, @Day, @Year, @Quarter, @DayOfYear, @Holiday, @WeekOfYear, @Month While @@Fetch_Status = 0 Begin -------------------------------------------------- -- Select -- @PickNum, @SeasonID, @Date, @DayOfWeek, -- @YearMonth, @Day, @Year, @Quarter, @DayOfYear, -- @Holiday, @WeekOfYear, @Month -- Select * From Time -- Delete From Time INSERT INTO SGCDataMart..Time (TimeID, SeasonID, [Date], DayOfWeek, YearMonth, [Day], [Year], [Quarter], DayOfYear, Holiday, WeekOfYear, [Month] -- Weekend, ) VALUES (@PickNum, @SeasonID, @Date, @DayOfWeek, @YearMonth, @Day, @Year, @Quarter, @DayOfYear, @Holiday, @WeekOfYear, @Month ) -- END -- Start Loop Again ------------------------------------------------- Fetch Next From cPicks Into @PickNum, @SeasonID, @Date, @DayOfWeek, @YearMonth, @Day, @Year, @Quarter, @DayOfYear, @Holiday, @WeekOfYear, @Month END -- End Loop Close cPicks -- Close Cursor Deallocate cPicks -------------------------------------------------- GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -------------------------------------------------- Update Time Set Time.Holiday = '1' -- Select * From Time t, WeekEndsAndHolidays w Where t.Date = w.DayOfWeekDate AND w.IsHoliday = '1' GO Update Time Set Time.Weekend = '1' -- Select * From Time t, WeekEndsAndHolidays w Where t.Date = w.DayOfWeekDate AND w.DayName = 'SAT' OR w.DayName = 'SUN' GO -------------------------------------------------- |
9 |