Real-World SQL: Examples

-- GOAL: Get User IDs / Preview data

-- Input the username to check...
-- THEN HIGHLIGHT FROM HERE...
DECLARE @username VARCHAR(50);
SET @username = '___'; -- <<=== username for one user, or ALL for all users.
-- no updates below this point
-- ----------------------------
DECLARE @count INT;
DECLARE @dbName AS VARCHAR(15);
SET @count = 0;
WHILE @count < 2
BEGIN
	IF @count = 0
	BEGIN
		USE FirstDatabase;
		SET @dbname = 'FirstDatabase';
	END
	ELSE
	BEGIN
		USE SecondDatabase;
		SET @dbname = 'SecondDatabase';
	END;
	SET @count = @count + 1;
	SELECT @dbName AS 'Database', u.username, c.userid AS CompanyUserId, c.companyid, u.createdt AS UserTblCreateDate, u.updatedt AS UserTblUpdateDate, u.deleteflag As UserTblDeleteFlg, c.deleteflag AS CompanyTblDeleteFlg
	FROM dbo.Users AS u
	INNER JOIN usercompany AS c ON u.userid = c.userid
	WHERE username LIKE
		CASE WHEN LOWER(@username) = 'all' THEN
				'%'
			ELSE
				LTRIM(RTRIM(@username))
		END
	ORDER BY username ASC;
END;

SELECT TOP(@TopVal) * 
	FROM 
		(
		SELECT 
			Summary_Id, 
			Chapter_no, 
			last_name, 
			First_name, 
			convert(nvarchar(12),test_date, 101) as TestDate, 
			round(cast (correct_questions as float)/ (case Total_Questions when 0 then 1 else total_questions end)*100,0) as score, 
			Section,  
			Total_Questions,
			Correct_Questions, 
			Row_Number() over(Order by 
			-- Below we use case instead of using dynamic sql
			-- CHAPTER
			CASE WHEN @SortField = 'Chapter_No' 
				THEN Chapter_no
				END ASC,
			-- LASTNAME
			CASE WHEN @SortField = 'Last_Name' 
				THEN last_name
				END ASC,
			-- FIRSTNAME
			CASE WHEN @SortField = 'First_Name' 
				THEN First_name
				END ASC,
			-- DATE
			CASE WHEN @SortField = 'Test_Date' 
				THEN test_date
				END ASC
				) as RowNum from Summary
		WHERE 
			    Pro_email = @pro_email 
			AND Book_no = @Book_No 
			AND test_date BETWEEN @DateFrom  AND @DateTo
			AND Chapter_no IN(SELECT * FROM [dbo].[Split](',', @Chapters))
			AND (@LastName = '' OR  Last_Name LIKE '%' + @LastName + '%')
			AND (Section = @Section OR @section = '' OR @section = 'all')
		)
		AS a 
WHERE RowNum > @RowNum

-- protecting from accidental run
SET NOEXEC ON;

-- To Run:
-- 1. Update the values below.
-- 2. Highlight from the start of this line down to step 3.

DECLARE @intThisPeriod AS INT;
SET @intThisPeriod = ##; -- <== SET THIS to the period #

DECLARE @IntMyId AS INT;
SET @IntMyId = ####; -- <== SET THIS with your user ID

DECLARE @FirstBatchId AS INT;
SET @FirstBatchId = ####; -- <== FIND THIS in Batch table

DECLARE @SecondBatchId AS INT;
SET @SecondBatchId = ####; -- <== FIND THIS in Batch table

DECLARE @ThirdBatchId AS INT;
SET @ThirdBatchId = ####; -- <== FIND THIS in Batch table

-- ===========================
-- DO NOT EDIT BELOW THIS LINE
-- ===========================

-- Define earliest time to start
DECLARE @StartAfter AS DATETIME;
SET @StartAfter = CONVERT(datetime,FLOOR(CONVERT(float,GETDATE()))) + '02:00:00';

-- Define latest time to start
DECLARE @FinishBy As DateTime;
SET @FinishBy = CONVERT(datetime,FLOOR(CONVERT(float,GETDATE()))) + '23:00:00';

-- Uncomment below and TEST FIRST
 --IF (GETDATE() > @StartAfter AND GETDATE() < @FinishBy)
 --BEGIN
 --print 'the time has come!'
 --END
 --ELSE
 --BEGIN
 --print 'no you cannot do this now!'
 --END;

/* ================= */

-- IMPRINT 1

IF (GETDATE() > @StartAfter AND GETDATE() < @FinishBy)
BEGIN
exec [dbo].[Accrual_PostToLedger] @intCompanyid = 1, @intPeriodid = @intThisPeriod, @intBatchId = @FirstBatchId, @intCreateBy = @IntMyId;
print ' ***** Imprint1 Posting DONE ***** ';
END
ELSE
BEGIN
print 'XXXXX Too early or late to post Imprint1 accruals! XXXXX ';
END;

-- IMPRINT 2

IF (GETDATE() > @StartAfter AND GETDATE() < @FinishBy)
BEGIN
exec [dbo].[Accrual_PostToLedger] @intCompanyid = 2, @intPeriodid = @intThisPeriod, @intBatchId = @SecondBatchId, @intCreateBy = @IntMyId;
print ' ***** Imprint2 Posting DONE ***** ';
END
ELSE
BEGIN
print 'XXXXX Too early or late to post Imprint2 accruals! XXXXX ';
END;

-- IMPRINT 3

IF (GETDATE() > @StartAfter AND GETDATE() < @FinishBy)
BEGIN
exec [dbo].[Accrual_PostToLedger] @intCompanyid = 5, @intPeriodid = @intThisPeriod, @intBatchId = @ThirdBatchId, @intCreateBy = @IntMyId;
print ' ***** Imprint3 Posting DONE ***** ';
END
ELSE
BEGIN
print 'XXXXX Too early or late to post Imprint3 accruals! XXXXX ';
END;

 
-- 3. Extend the highlight down to the end of this line. 
-- 4. Click "!Execute"


-- protecting from accidental run
SET NOEXEC OFF;