Random Number, Multiple Updates, Date Variation, and TSQL Concepts

Random_Number()
SELECT    CAST((Rand(CHECKSUM(NEWID()))* 100)
AS NUMERIC(18,0)) High
, CAST((Rand(CHECKSUM(NEWID()))* 100)
AS NUMERIC(18,0)) Low
,
GETDATE()
        FROM Weather
        
Multiple Update for the table data
DECLARE @RowCount INT, @RowId INT
SELECT @RowCount =
(SELECT
COUNT(RowId)
FROM #TempWeather)
SET @RowCount = 100
SET @RowId = 0
WHILE(@RowId < @RowCount)
BEGIN
    SET @RowId = @RowId +1
UPDATE #TempWeather SET Average =
(
    SELECT ((High+LOW)/2)
FROM #TempWeather
    WHERE RowId = @RowId
    )
END

 
Last Week data
DECLARE @StartDateTime DATE, @EndDateTime DATE
SELECT @StartDateTime =
DATEADD(week,-1,GETDATE())
-
DATEPART(weekday,DATEADD(week,-1,getdate()))+1
, @EndDateTime =
DATEADD("dd", 6, @StartDateTime)

 
SELECT @StartDateTime, @EndDateTime
SELECT
LEFT
(DATENAME(dw,[date]),3)
AS DisplayText
FROM TableName
WHERE [DATE] between @StartDateTime and @EndDateTime

 
Last 14 Weeks
DECLARE @StartDateTime DATE, @EndDateTime DATE
SELECT @StartDateTime =
DATEADD(WEEK,
-14,
CONVERT(DATE,GETDATE())),
        @EndDateTime =
CONVERT(DATE,GETDATE())
SELECT @StartDateTime =
DATEADD(DAY,-1,DATEADD(WEEK,
DATEDIFF(WEEK,0,@StartDateTime),0)),
@EndDateTime =
DATEADD(DAY,5,DATEADD(WEEK,
DATEDIFF(WEEK,0,@EndDateTime),0))
SELECT @StartDateTime, @EndDateTime                    

 
SELECT    'Wk-' + CONVERT(VARCHAR(50),DATEPART(WEEK, [Date])) AS DisplayText
FROM TableName
WHERE [DATE] between @StartDateTime and @EndDateTime

 
FUNCTION CREATION
CREATE
FUNCTION [dbo].[fn_Sample_50days]()
RETURNS @Values TABLE (AccountNum NVARCHAR(40)
, INVOICEID NVARCHAR(40)
, VOUCHER NVARCHAR(40)
, DueDate VARCHAR(10)
        , CURRENCYCODE NVARCHAR(8)
, LINEAMOUNTMST NUMERIC(17,5)
, LINEAMOUNT NUMERIC(13,5))
AS
BEGIN
    DECLARE @today DATETIME
    DECLARE @StartDate DATETIME --(20)
    DECLARE @EndDate DATETIME
    SET @today = GETDATE()
SET @StartDate = CASE WHEN DAY(@today) > 1 THEN
CONVERT(VARCHAR(10),MONTH(@today))+'/'+'1/'+CONVERT(VARCHAR(10)
,YEAR(@today))
    
SELECT @EndDate = DATEADD(day, 50, @StartDate)
    INSERT INTO @Values
SELECT Statement
RETURN
END

VIEW CREATION
CREATE VIEW [dbo].[vw_Sample_50days]
AS
    SELECT * FROM dbo.fn_Billing_50days()
GO

 
TRIGGER CREATION
CREATE TRIGGER [dbo].[EventTrigger]

ON [dbo].[Event]
AFTER INSERT , UPDATE
AS
DECLARE @PersonId INT, @EventType INT, @DetectorId INT
BEGIN
    SET NOCOUNT ON;
    SELECT @PersonId = PersonID
    FROM inserted    
    SELECT @EventType = EventTypeID FROM inserted