Amazing GYan: SQL Server

Basic SQL Query

USE Britain

SELECT * FROM DBO.Britain

***********************************************************************************

SQL Query with filter condition (Where)

(Two filters)

SELECT * FROM DBO.Britain WHERE City = 'LONDON' AND State = 'ENGLAND'

(Between Condition)

SELECT * FROM DBO.Britain WHERE Salary between 12000 and 30000

SELECT COUNT(Salary) FROM DBO.Britain WHERE Salary between 12000 and 30000

***********************************************************************************

SQL Query with count

***********************************************************************************

SELECT COUNT(City) FROM dbo.Britain

SELECT COUNT(City) FROM dbo.Britain WHERE City = 'LONDON' AND State = 'ENGLAND'

SQL Query Backup table

***********************************************************************************

SELECT * INTO TEMPBRITAIN FROM DBO.Britain

Update table with Case...end based on another table

***********************************************************************************

UPDATE Flowslake

SET LOAN_SUBTYPE =

CASE

WHEN LOANTYPES = 'Secured Loan' THEN 'SL'

WHEN LOANTYPES = 'Unsecured Loan' THEN 'UL'

ELSE LOAN_SUBTYPE -- If not 'Secured Loan' or 'Unsecured Loan, leave it as is

END;

--------------------------------------------------------------------------------------

-- Declare and initialize the variable

DECLARE @Loantypes NVARCHAR(100)

-- Initialize the variable with 'Unsecured Loan'

SET @Loantypes = 'LOANTYPES'

-- Retrieve the value from the table based on the condition

SELECT @Loantypes = LOANTYPES FROM Flowslake

-- Check the value of the variable and update the table accordingly

IF @Loantypes = 'Secured Loan'

BEGIN

UPDATE Flowslake SET LOAN_SUBTYPE = 'SL' WHERE LOANTYPES = 'Secured Loan'

END

ELSE

BEGIN

UPDATE Flowslake SET LOAN_SUBTYPE = 'UL' WHERE LOANTYPES = 'Unsecured Loan'

END

Update table with Joints

***********************************************************************************

UPDATE TBLN

SET TBLN.[01-Nov] = TBLB.Status

FROM tblNovember_2023 AS TBLN

INNER JOIN Book7 AS TBLB ON TBLN.AHA_ID = TBLB.AHA_ID

Insert the data in new table

***********************************************************************************

--Insert data in new table

select * into BackUp_tblStudent from tblStudent

Insert the data in already existing table which has data

***********************************************************************************

insert into tblStudent select * from Dataset_P

Vlookup Like connection in SQL

***********************************************************************************

SELECT Sht3.*, Sht1.[Name of Student]

FROM Sheet3 AS Sht3

LEFT JOIN Sheet1 AS Sht1 ON Sht3.[Name of Student] = Sht1.[Name of Student]

WHERE Sht1.[Name of Student] IS NULL

ORDER BY Sht1.[Name of Student];

SELECT

Sht3.*,

Sht1.[Name of Student] AS [Sheet1_Name_of_Student] -- Rename this column uniquely

INTO #JointInserted

FROM Sheet3 AS Sht3

LEFT JOIN Sheet1 AS Sht1 ON Sht3.[Name of Student] = Sht1.[Name of Student]

WHERE Sht1.[Name of Student] IS NULL

ORDER BY Sht1.[Name of Student];

select * from #JointInserted

Real time Join & Insert

***********************************************************************************

use ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS

select * from ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS order by [Visit Date and Time] desc

SELECT ADVENTHEALTH_ALTAMONTE_SPRINGS06042026.*,ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS.Patient_ID AS TPATIENTID

INTO #JOINADVEN

FROM ADVENTHEALTH_ALTAMONTE_SPRINGS06042026

LEFT JOIN ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS

ON ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS.Patient_ID = ADVENTHEALTH_ALTAMONTE_SPRINGS06042026.Patient_ID

WHERE ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS.Patient_ID IS NULL

SELECT * FROM #JOINADVEN

ALTER TABLE #JOINADVEN

DROP COLUMN TPATIENTID

INSERT INTO ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS

SELECT * FROM #JOINADVEN

Delete the duplicate Data with Group by Max

***********************************************************************************

Select SHT2.*, SHT1.BRANCH into #TempAdded2

from Sheet2 as SHT2

INNER JOIN Sheet1 as SHT1

ON SHT2.STATE = SHT1.STATE

order by STATE

select * from #TempAdded2 order by ID

select distinct State,branch,ID from #TempAdded2 order by ID

select State, branch, count(*) from #TempAdded2

group by State, branch

having count (*)>1

Delete from #TempAdded2 where ID not in ( select Max(ID) from #TempAdded2 group by State, branch)

Delete the duplicate Data with CTE and ROW Function

***********************************************************************************

With TempAdded_CTE As

(

select *, ROW_NUMBER() over (partition by Branch order by Branch) as RowNumber

from #TempAdded

)

select * from TempAdded_CTE

Delete from TempAdded_CTE where RowNumber >1

Find all the store procedures associated to particular table

**************************************************************************************************

SELECT

OBJECT_NAME(object_id) AS ProcedureName

FROM

sys.sql_modules

WHERE

definition LIKE '%STUDENT_MASTER_TABLE%'

AND

OBJECTPROPERTY(object_id, 'IsProcedure') = 1;

Select/ Find the version of sql server

**************************************************************************************************

SELECT @@VERSION;

Check SQL Server Instance Name

**************************************************************************************************

SELECT @@SERVERNAME AS ServerName;

Check Current Database Info

**************************************************************************************************

SELECT

DB_NAME() AS CurrentDatabase,

DATABASEPROPERTYEX(DB_NAME(), 'Status') AS Status,

DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS RecoveryModel;

List All Databases on the Server

**************************************************************************************************

SELECT

name AS DatabaseName,

state_desc AS Status,

recovery_model_desc AS RecoveryModel

FROM

sys.databases;

List All Tables in a Specific Database

**************************************************************************************************

SELECT

TABLE_SCHEMA,

TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE';

Find SQL Server Login Info

**************************************************************************************************

SELECT

name AS LoginName,

type_desc AS LoginType,

create_date,

modify_date,

is_disabled

FROM

sys.sql_logins;

Find SQL Randomize

**************************************************************************************************

CREATE TABLE salesTable

(

SalesPersonName VARCHAR(100),

[Month] VARCHAR(20),

ProductName VARCHAR(50),

Qty INT,

City VARCHAR(50),

[Year] INT,

Rate INT

);

WHILE 1 = 1

BEGIN

DECLARE @RowCount INT;

SET @RowCount = (ABS(CHECKSUM(NEWID())) % 7) + 1; -- 1 to 7 rows

;WITH Numbers AS

(

SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n

FROM sys.objects

)

INSERT INTO salesTable

(

SalesPersonName,

[Month],

ProductName,

Qty,

City,

[Year],

Rate

)

SELECT

-- Random Sales Person

(SELECT TOP 1 Name FROM

(VALUES

('Asfiya Pathan'),

('Priyanshu Somani'),

('Rajni.Waghmare'),

('Akshay Vibhute'),

('Abhishek Jadhav'),

('Krushna Khedkar'),

('Ujvala Shamrav Shete'),

('Rohini Deshmukh')

) AS Names(Name)

ORDER BY NEWID()),

-- Random Month

DATENAME(MONTH, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, 0)),

-- Random Product

(SELECT TOP 1 Product FROM

(VALUES

('Laptop'),

('Refriderator'),

('Washing Machine'),

('Television'),

('Microwave Owen'),

('Steam Iron')

) AS Products(Product)

ORDER BY NEWID()),

-- Random Qty (1–10)

(ABS(CHECKSUM(NEWID())) % 10) + 1,

-- Random City

(SELECT TOP 1 City FROM

(VALUES

('Mumbai'),

('Delhi'),

('Bengalore'),

('Pune'),

('Chennai'),

('Hyderabad')

) AS Cities(City)

ORDER BY NEWID()),

-- Random Year (2018–2024)

2018 + (ABS(CHECKSUM(NEWID())) % 7),

-- Random Rate

CASE

WHEN (ABS(CHECKSUM(NEWID())) % 6) = 0 THEN 55000

WHEN (ABS(CHECKSUM(NEWID())) % 6) = 1 THEN 25000

WHEN (ABS(CHECKSUM(NEWID())) % 6) = 2 THEN 30000

WHEN (ABS(CHECKSUM(NEWID())) % 6) = 3 THEN 40000

WHEN (ABS(CHECKSUM(NEWID())) % 6) = 4 THEN 15000

ELSE 2000

END

FROM Numbers;

-- Wait for 2 seconds

WAITFOR DELAY '00:00:02';

END

---------------------------------------

CREATE OR ALTER PROCEDURE GenerateSalesData

BEGIN

SET NOCOUNT ON;

DECLARE @RowCount INT;

SET @RowCount = (ABS(CHECKSUM(NEWID())) % 7) + 1;

;WITH Numbers AS

(

SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n

FROM sys.objects

)

INSERT INTO salesTable

(

SalesPersonName, [Month], ProductName, Qty, City, [Year], Rate

)

SELECT

(SELECT TOP 1 Name FROM (VALUES

('Asfiya Pathan'), ('Priyanshu Somani'), ('Rajni.Waghmare'),

('Akshay Vibhute'), ('Abhishek Jadhav'), ('Krushna Khedkar'),

('Ujvala Shamrav Shete'), ('Rohini Deshmukh')

) AS N(Name) ORDER BY NEWID()),

DATENAME(MONTH, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, 0)),

(SELECT TOP 1 Product FROM (VALUES

('Laptop'), ('Refriderator'), ('Washing Machine'),

('Television'), ('Microwave Owen'), ('Steam Iron')

) AS P(Product) ORDER BY NEWID()),

(ABS(CHECKSUM(NEWID())) % 10) + 1,

(SELECT TOP 1 City FROM (VALUES

('Mumbai'), ('Delhi'), ('Bengalore'),

('Pune'), ('Chennai'), ('Hyderabad')

) AS C(City) ORDER BY NEWID()),

2018 + (ABS(CHECKSUM(NEWID())) % 7),

CASE ABS(CHECKSUM(NEWID())) % 6

WHEN 0 THEN 55000

WHEN 1 THEN 25000

WHEN 2 THEN 30000

WHEN 3 THEN 40000

WHEN 4 THEN 15000

ELSE 2000

END

FROM Numbers;

END

SET NOCOUNT ON;

SET QUERY_GOVERNOR_COST_LIMIT 0;

WHILE 1 = 1

BEGIN

EXEC GenerateSalesData;

WAITFOR DELAY '00:00:02';

END

Call Center

**************************************************************************************************

Create database Test2

use Test2

select * from CallCenterLive

CREATE TABLE CallCenterLive

(

AgentName VARCHAR(100),

Status VARCHAR(20),

IncomingCalls INT,

ActiveCalls INT,

CallsOnHold INT,

AHT INT, -- in seconds

QueueLength INT,

EventTime DATETIME DEFAULT GETDATE()

);

WHILE 1 = 1

BEGIN

DECLARE @RowCount INT;

SET @RowCount = (ABS(CHECKSUM(NEWID())) % 5) + 5; -- 5 to 9 rows per batch

;WITH Numbers AS

(

SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n

FROM sys.objects

)

INSERT INTO CallCenterLive

(

AgentName,

Status,

IncomingCalls,

ActiveCalls,

CallsOnHold,

AHT,

QueueLength

)

SELECT

-- Random Agent (15 agents)

(SELECT TOP 1 Name FROM

(VALUES

('Amit Sharma'),

('Neha Patil'),

('Rahul Verma'),

('Sneha Kulkarni'),

('Vikas Singh'),

('Pooja Mehta'),

('Rohit Patil'),

('Anjali Desai'),

('Karan Shah'),

('Priya Nair'),

('Sandeep Yadav'),

('Meena Joshi'),

('Arjun Reddy'),

('Kavita More'),

('Deepak Gupta')

) AS Agents(Name)

ORDER BY NEWID()),

-- Random Status

(SELECT TOP 1 Status FROM

(VALUES

('Available'),

('Busy'),

('On Call'),

('Break')

) AS StatusList(Status)

ORDER BY NEWID()),

-- Incoming Calls (0–20)

ABS(CHECKSUM(NEWID())) % 21,

-- Active Calls (0–10)

ABS(CHECKSUM(NEWID())) % 11,

-- Calls On Hold (0–5)

ABS(CHECKSUM(NEWID())) % 6,

-- AHT (Average Handling Time: 60–600 sec)

(ABS(CHECKSUM(NEWID())) % 540) + 60,

-- Queue Length (0–25)

ABS(CHECKSUM(NEWID())) % 26

FROM Numbers;

-- Wait 2 seconds

WAITFOR DELAY '00:00:02';

END

KMr Enterprise Sim seller

**************************************************************************************************

Create database CallCenter

use CallCenter

IF OBJECT_ID('SimSalesLive') IS NOT NULL

DROP TABLE SimSalesLive;

CREATE TABLE SimSalesLive

(

AgentID VARCHAR(10),

AgentName VARCHAR(100),

Team VARCHAR(50),

Shift VARCHAR(10),

SimType VARCHAR(20),

Operator VARCHAR(20),

EnquiryID UNIQUEIDENTIFIER,

CustomerName VARCHAR(100),

CustomerMobile BIGINT,

City VARCHAR(50),

LeadSource VARCHAR(20),

EnquiryDateTime DATETIME,

ActivationStatus VARCHAR(20),

ActivationDateTime DATETIME,

PlanName VARCHAR(50),

KYCStatus VARCHAR(20),

DocumentType VARCHAR(50),

CommissionAmount DECIMAL(10,2),

SaleAmount INT,

FollowUpRequired VARCHAR(5),

Remarks VARCHAR(200)

);

select * from SimSalesLive

WHILE 1 = 1

BEGIN

DECLARE @RowCount INT = (ABS(CHECKSUM(NEWID())) % 5) + 5;

;WITH Numbers AS

(

SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n

FROM sys.objects

)

INSERT INTO SimSalesLive

SELECT

-- AgentID

CONCAT('SNS00', RIGHT('00' + CAST(ABS(CHECKSUM(NEWID())) % 12 + 1 AS VARCHAR),2)),

-- AgentName

(SELECT TOP 1 Name FROM (VALUES

('Amit Sharma'),('Neha Patil'),('Rahul Verma'),('Sneha Kulkarni'),

('Vikas Singh'),('Pooja Mehta'),('Rohit Patil'),('Anjali Desai'),

('Karan Shah'),('Priya Nair'),('Sandeep Yadav'),('Meena Joshi')

) A(Name) ORDER BY NEWID()),

-- Team

(SELECT TOP 1 Team FROM (VALUES ('Alpha'),('Beta'),('Gamma')) T(Team) ORDER BY NEWID()),

-- Shift

(SELECT TOP 1 Shift FROM (VALUES ('1st'),('2nd'),('3rd')) S(Shift) ORDER BY NEWID()),

-- SimType

(SELECT TOP 1 SimType FROM (VALUES ('Prepaid'),('Postpaid')) ST(SimType) ORDER BY NEWID()),

OP.Operator,

-- EnquiryID

NEWID(),

-- CustomerName

NULL,

-- CustomerMobile

CAST((RAND(CHECKSUM(NEWID())) * (9999999999 - 7777777777) + 7777777777) AS BIGINT),

-- City

(SELECT TOP 1 City FROM (VALUES

('Mumbai'),('Pune'),('Nagpur'),('Nashik'),('Aurangabad'),

('Solapur'),('Kolhapur'),('Thane'),('Amravati'),('Nanded'),

('Jalgaon'),('Akola'),('Latur'),('Satara'),('Sangli')

) C(City) ORDER BY NEWID()),

-- LeadSource

(SELECT TOP 1 LS FROM (VALUES ('Walk-in'),('Online'),('Referral')) L(LS) ORDER BY NEWID()),

-- EnquiryDateTime

GETDATE(),

S1.Status,

-- ActivationDateTime

CASE

WHEN S1.Status = 'Activated'

THEN DATEADD(MINUTE, -1 * (ABS(CHECKSUM(NEWID())) % 60), GETDATE())

ELSE NULL

END,

-- PlanName

(SELECT TOP 1 PN FROM (VALUES

('299 Plan'),('399 Plan'),('499 Plan'),('599 Plan'),('699 Plan')

) P(PN) ORDER BY NEWID()),

-- KYCStatus

CASE

WHEN S1.Status = 'Activated' THEN 'Verified'

WHEN S1.Status = 'Rejected' THEN 'Failed'

ELSE 'Pending'

END,

-- DocumentType

(SELECT TOP 1 DT FROM (VALUES

('Aadhaar'),('PAN'),('DL'),('VoterID'),('Passport'),('Driving License')

) D(DT) ORDER BY NEWID()),

-- CommissionAmount

CASE

WHEN S1.Status = 'Activated' THEN OP.SaleAmount * 0.10

ELSE 0

END,

-- SaleAmount

OP.SaleAmount,

-- FollowUpRequired

CASE

WHEN S1.Status = 'Pending' THEN 'Yes'

ELSE 'No'

END,

-- Remarks (SMART)

CASE

WHEN S1.Status = 'Activated' THEN

(SELECT TOP 1 Remark FROM (VALUES

('Activation successful'),

('Documents verified'),

('SIM activated successfully'),

('Customer onboarded')

) R(Remark) ORDER BY NEWID())

WHEN S1.Status = 'Rejected' THEN

(SELECT TOP 1 Remark FROM (VALUES

('Customer declined offer'),

('Wrong number'),

('KYC failed'),

('Switched to competitor')

) R(Remark) ORDER BY NEWID())

ELSE

(SELECT TOP 1 Remark FROM (VALUES

('Follow-up required'),

('Call back later'),

('Not reachable'),

('Interested, pending decision')

) R(Remark) ORDER BY NEWID())

END

FROM Numbers

-- Operator + Sale mapping

CROSS APPLY (

SELECT TOP 1 *

FROM (VALUES

('Jio',200),

('Airtel',350),

('Vi',150),

('BSNL',300)

) OP(Operator, SaleAmount)

ORDER BY NEWID()

) OP

-- Status

CROSS APPLY (

SELECT TOP 1 Status

FROM (VALUES ('Pending'),('Activated'),('Rejected')) S(Status)

ORDER BY NEWID()

) S1;

-- 🔥 Control table size (last 10 minutes only)

DELETE FROM SimSalesLive

WHERE EnquiryDateTime < DATEADD(MINUTE, -10, GETDATE());

-- ⏱ Wait 10 seconds

WAITFOR DELAY '00:00:10';

END

With Table Fixed values randomize

**************************************************************************************************

CREATE DATABASE SimulationData;

CREATE DATABASE RealDatabase;

USE SimulationData;

CREATE TABLE SimSeller

(

SellerID INT IDENTITY PRIMARY KEY,

SellerName VARCHAR(100)

);

CREATE TABLE SimProduct

(

ProductID INT IDENTITY PRIMARY KEY,

ProductName VARCHAR(100),

Price INT

);

CREATE TABLE SimCity

(

CityID INT IDENTITY PRIMARY KEY,

CityName VARCHAR(100)

);

-- insert manual data in all the tables above manually

select * from SimCity

select * from SimProduct

select * from SimSeller

USE RealDatabase;

CREATE TABLE RealSale

(

SaleID INT IDENTITY PRIMARY KEY,

SellerName VARCHAR(100),

ProductName VARCHAR(100),

City VARCHAR(100),

Qty INT,

Price INT,

SaleAmount AS (Qty * Price),

SaleDate DATETIME DEFAULT GETDATE()

);

select * from RealSale

-- with ProductName ≠ Price (may mismatch)

WHILE 1 = 1

BEGIN

DECLARE @RowCount INT;

SET @RowCount = (ABS(CHECKSUM(NEWID())) % 5) + 1;

;WITH Numbers AS

(

SELECT TOP (@RowCount) 1 AS n

FROM sys.objects

)

INSERT INTO RealDatabase.dbo.RealSale

(

SellerName,

ProductName,

City,

Qty,

Price

)

SELECT

(SELECT TOP 1 SellerName FROM SimulationData.dbo.SimSeller ORDER BY NEWID()),

(SELECT TOP 1 ProductName FROM SimulationData.dbo.SimProduct ORDER BY NEWID()),

(SELECT TOP 1 CityName FROM SimulationData.dbo.SimCity ORDER BY NEWID()),

(ABS(CHECKSUM(NEWID())) % 10) + 1,

(SELECT TOP 1 Price FROM SimulationData.dbo.SimProduct ORDER BY NEWID())

FROM Numbers;

WAITFOR DELAY '00:00:03';

END

--with ProductName = Price (smatch)

WHILE 1 = 1

BEGIN

DECLARE @RowCount INT;

SET @RowCount = (ABS(CHECKSUM(NEWID())) % 15) + 1;

;WITH Numbers AS (SELECT TOP (@RowCount) 1 AS n FROM sys.objects)

INSERT INTO RealDatabase.dbo.RealSale (SellerName, ProductName, City, Qty, Price)

SELECT (SELECT TOP 1 SellerName FROM SimulationData.dbo.SimSeller ORDER BY NEWID()),

ProductData.ProductName,

(SELECT TOP 1 CityName FROM SimulationData.dbo.SimCity ORDER BY NEWID()),

(ABS(CHECKSUM(NEWID())) % 10) + 1,

ProductData.Price

FROM Numbers

CROSS APPLY (SELECT TOP 1 ProductName, Price FROM SimulationData.dbo.SimProduct ORDER BY NEWID()) AS ProductData;

WAITFOR DELAY '00:00:10';

END

use RealDatabase

truncate table RealSale

select * from RealSale

CMD Loop

**************************************************************************************************

Open CMD.

Type color 0a and press Enter.

Run the Infinite Tree command from above: :loop & tree C:\ /f & goto loop

Join Query

**************************************************************************************************

Use ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Select CENTURA_LONGMONT_UNITED_HOSPITAL30032026.*, ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL.Patient_ID AS TPATIETID

Into #Joinedtable16000

From CENTURA_LONGMONT_UNITED_HOSPITAL30032026

Left Join ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

on ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL.Patient_ID = CENTURA_LONGMONT_UNITED_HOSPITAL30032026.patient_ID

where ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL.Patient_ID is Null

Select * From #Joinedtable16000

Alter Table #Joinedtable16000

DROP Column TPATIETID

Insert Into ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Select * From #Joinedtable16000

Select * From ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL order by Reference_Add_Date DESC

Autdit MIS

**************************************************************************************************

use ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Select reference_Add_Date, Discharge_Summary From ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Select * From ENZYMES98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

Where Reference_Add_Date Between '2026-03-20' And '2026-03-30'

Above Solution

**************************************************************************************************

⚡ Better Solution: Use Dynamic SQL + Date Parameter

You can make the table name dynamic based on date.

DECLARE @Date VARCHAR(8)

DECLARE @TableName VARCHAR(100)

DECLARE @SQL NVARCHAR(MAX)

-- Set date (DDMMYYYY format)

SET @Date = FORMAT(GETDATE(), 'ddMMyyyy')

-- Or manually:

-- SET @Date = '30032026'

SET @TableName = 'CENTURA_LONGMONT_UNITED_HOSPITAL' + @Date

SET @SQL = '

SELECT A.*, B.Patient_ID AS TPATIETID

INTO #Temp

FROM ' + @TableName + ' A

LEFT JOIN ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL B

ON B.Patient_ID = A.Patient_ID

WHERE B.Patient_ID IS NULL;

ALTER TABLE #Temp DROP COLUMN TPATIETID;

INSERT INTO ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

SELECT * FROM #Temp;

SELECT *

FROM ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL

ORDER BY Reference_Add_Date DESC;

EXEC sp_executesql @SQL

Store Procedure & Parameter

**************************************************************************************************

-1 parameter-----------------------------------------------

DECLARE @City VARCHAR(100) = 'Olathe';

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '

SELECT *

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER

WHERE City = @CityParam';

EXEC sp_executesql

@SQL,

N'@CityParam VARCHAR(100)',

@CityParam = @City;

DECLARE @City VARCHAR(100) = 'Olathe';

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '

SELECT *

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER

WHERE City = ''' + @City + '''';

EXEC(@SQL);

CREATE PROCEDURE sp_Get_Data_ByCity_Dynamic

@City VARCHAR(100)

BEGIN

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '

SELECT *

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER

WHERE City = @CityParam';

EXEC sp_executesql

@SQL,

N'@CityParam VARCHAR(100)',

@CityParam = @City;

END;

EXEC sp_Get_Data_ByCity_Dynamic 'Olathe';

2 para-----------------------------------------------------

DECLARE @City VARCHAR(100) = 'Olathe';

DECLARE @Procedure VARCHAR(100) = 'Colonoscopy';

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '

SELECT *

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER

WHERE City = @CityParam

AND Surgical_Procedure = @ProcParam';

EXEC sp_executesql

@SQL,

N'@CityParam VARCHAR(100), @ProcParam VARCHAR(100)',

@CityParam = @City,

@ProcParam = @Procedure;

---------------------------------------------------------------------

CREATE PROCEDURE sp_Get_Data_ByCity_Procedure

@City VARCHAR(100),

@Procedure VARCHAR(100)

BEGIN

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '

SELECT *

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER

WHERE City = @CityParam

AND Surgical_Procedure = @ProcParam';

EXEC sp_executesql

@SQL,

N'@CityParam VARCHAR(100), @ProcParam VARCHAR(100)',

@CityParam = @City,

@ProcParam = @Procedure;

END;

🚀 Benefits

✅ No need to rewrite query daily

Just change:


SET @Date = '31032026'



LEFT JOIN ... WHERE NULL

You can also use:
WHERE NOT EXISTS (
    SELECT 1 
    FROM ENZYME98DKQ60_CENTURA_LONGMONT_UNITED_HOSPITAL B
    WHERE B.Patient_ID = A.Patient_ID
)


Windows functions
**************************************************************************************************
🔥 Windows functions ( Important)
Follow this exact order:
✅ ROW_NUMBER()

✅ RANK(), DENSE_RANK()

✅ NTILE()

✅ PARTITION BY

✅ LAG(), LEAD()

✅ FIRST_VALUE(), LAST_VALUE()

✅ SUM() OVER(), AVG() OVER(), COUNT() OVER()

✅ Running Total

✅ PERCENT_RANK(), CUME_DIST()

✅ Frame Clause


Use data of Statstical function in Excel

--simple row number gives number

SELECT *,
ROW_NUMBER() OVER (ORDER BY Total_Marks DESC) AS RowNum
FROM tbl_Students;


--ranking formula
SELECT *,
RANK() OVER (ORDER BY Total_Marks DESC) AS RankValue
FROM tbl_Students;


SELECT *,
DENSE_RANK() OVER (ORDER BY Total_Marks DESC) AS DenseRankValue
FROM tbl_Students;


SELECT *,
SUM(Total_Marks) OVER (ORDER BY SNO) AS RunningTotal
FROM tbl_Students;


SELECT *,
ROW_NUMBER() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRank
FROM tbl_Students;


--Rank students within each city
SELECT *,
ROW_NUMBER() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRank
FROM tbl_Students;


SELECT *,
RANK() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRank
FROM tbl_Students;

--Rank students inside each year
SELECT *,
RANK() OVER (PARTITION BY Year ORDER BY Total_Marks DESC) AS YearRank
FROM tbl_Students;


SELECT
    Name_of_Student,
    City,
    Total_Marks,
    COUNT(*) OVER (PARTITION BY City) AS StudentsInCity
FROM tbl_Students;


SELECT *,
COUNT(*) OVER (PARTITION BY City) AS StudentsInCity
FROM tbl_Students;





-- Ranking per student (overall based on Total_Marks)
SELECT *,
RANK() OVER (ORDER BY Total_Marks DESC) AS OverallRank
FROM tbl_Students;



-- Row number per student (unique ranking, no duplicates)
SELECT *,
ROW_NUMBER() OVER (ORDER BY Total_Marks DESC) AS RowNum
FROM tbl_Students;


-- Dense rank per student (no gaps in ranking)
SELECT *,
DENSE_RANK() OVER (ORDER BY Total_Marks DESC) AS DenseRank
FROM tbl_Students;


-- Ranking students within each city
SELECT *,
RANK() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRank
FROM tbl_Students;


-- Row number per city (unique sequence within each city)
SELECT *,
ROW_NUMBER() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRowNum
FROM tbl_Students;


-- Dense rank per city (no gap ranking within city)
SELECT *,
DENSE_RANK() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityDenseRank
FROM tbl_Students;


-- Count of students in each city (shown on every row)
SELECT *,
COUNT(*) OVER (PARTITION BY City) AS StudentsInCity
FROM tbl_Students;


-- Average marks of each city (shown on every row)
SELECT *,
AVG(Total_Marks) OVER (PARTITION BY City) AS CityAverage
FROM tbl_Students;


-- Highest marks in each year
SELECT *,
MAX(Total_Marks) OVER (PARTITION BY Year) AS MaxMarksInYear
FROM tbl_Students;


-- Running total of marks within each city
SELECT *,
SUM(Total_Marks) OVER (PARTITION BY City ORDER BY SNO) AS RunningTotal
FROM tbl_Students;



-- City topper + ranking of all students within city
SELECT *,
RANK() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRank
FROM tbl_Students;


-- Top student per city (without CTE)
SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRank FROM tbl_Students ) t
WHERE CityRank = 1;


-- Top 2 students in each city
SELECT *
FROM (
    SELECT *,
    DENSE_RANK() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS drnk
    FROM tbl_Students
) t
WHERE drnk <= 2;

-- Top 3 students per city without gaps
SELECT *
FROM (
    SELECT *,
    DENSE_RANK() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS drnk
    FROM tbl_Students
) t
WHERE drnk <= 3;


-- Compare each student with top scorer in their city
SELECT *,
MAX(Total_Marks) OVER (PARTITION BY City) AS CityTopScore,
MAX(Total_Marks) OVER (PARTITION BY City) - Total_Marks AS GapFromTop
FROM tbl_Students;


-- Each student's contribution to city total marks
SELECT *,
SUM(Total_Marks) OVER (PARTITION BY City) AS CityTotal,
ROUND(
    (Total_Marks * 100.0) / SUM(Total_Marks) OVER (PARTITION BY City),
    2
) AS PercentContribution
FROM tbl_Students;


-- Rank + cumulative marks inside city
SELECT *,
ROW_NUMBER() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS CityRank,
SUM(Total_Marks) OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS RunningCityScore
FROM tbl_Students;


-- Mark students above or below city average
SELECT *,
AVG(Total_Marks) OVER (PARTITION BY City) AS CityAvg,
CASE 
    WHEN Total_Marks >= AVG(Total_Marks) OVER (PARTITION BY City) 
    THEN 'Above Average'
    ELSE 'Below Average'
END AS PerformanceStatus
FROM tbl_Students;


-- Top student per city using ROW_NUMBER
SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY City ORDER BY Total_Marks DESC) AS rn
    FROM tbl_Students
) t
WHERE rn = 1;


-- Year-wise topper list
SELECT *
FROM (
    SELECT *,
    RANK() OVER (PARTITION BY Year ORDER BY Total_Marks DESC) AS rnk
    FROM tbl_Students
) t
WHERE rnk = 1;



-------------------------Sub query-------------------

-- Students scoring above average marks
SELECT *
FROM tbl_Students
WHERE Total_Marks > (
    SELECT AVG(Total_Marks)
    FROM tbl_Students
);

-- Student(s) with highest marks
SELECT *
FROM tbl_Students
WHERE Total_Marks = (
    SELECT MAX(Total_Marks)
    FROM tbl_Students
);


-- Students from Delhi city
SELECT *
FROM tbl_Students
WHERE City = (
    SELECT City
    FROM tbl_Students
    WHERE SNO = 4
);


-- Cities with high average performance
SELECT *
FROM tbl_Students
WHERE City IN (
    SELECT City
    FROM tbl_Students
    GROUP BY City
    HAVING AVG(Total_Marks) > 300
);







-- Students scoring above overall average marks
SELECT *
FROM tbl_Students
WHERE Total_Marks > (
    SELECT AVG(Total_Marks)
    FROM tbl_Students
);

-- Student(s) with highest marks in entire table
SELECT *
FROM tbl_Students
WHERE Total_Marks = (
    SELECT MAX(Total_Marks)
    FROM tbl_Students
);

-- Students scoring above their city average
SELECT *
FROM tbl_Students s
WHERE Total_Marks > (
    SELECT AVG(Total_Marks)
    FROM tbl_Students
    WHERE City = s.City
);


-- Second highest marks in the class
SELECT MAX(Total_Marks) AS SecondHighest
FROM tbl_Students
WHERE Total_Marks < (
    SELECT MAX(Total_Marks)
    FROM tbl_Students
);


-- Top 3 marks cutoff using subquery
SELECT *
FROM tbl_Students
WHERE Total_Marks >= (
    SELECT MIN(Total_Marks)
    FROM (
        SELECT DISTINCT TOP 3 Total_Marks
        FROM tbl_Students
        ORDER BY Total_Marks DESC
    ) t
);


select * from tbl_Students

select SNO, Name_of_Student,marathi,science,So_Science,English,Sanskrit,Total_Marks,Year,City,Months,Week
from tbl_Students
group by Year, Months
where Year = 2016  and months = 'March'



SELECT SNO, Name_of_Student, Marathi, Science, So_Science, English, Sanskrit,
       Total_Marks, Year, City, Months, Week
FROM tbl_Students
WHERE Year = 2016
AND Months = 'March';
group by Year, months



SELECT SNO, Name_of_Student, Marathi, Science, So_Science, English, Sanskrit,
       Total_Marks, Year, City, Months, Week
FROM tbl_Students
group by year, months



Medview Scenarios
**************************************************************************************************

SELECT *,
COUNT(Surgical_Procedure) OVER (PARTITION BY State) AS PatientinCity
FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
where Reference_Received_Date ='2024-02-23'


SELECT State,
       COUNT(Surgical_Procedure) AS PatientinCity
FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
WHERE Reference_Received_Date = '2024-02-23'
GROUP BY State;



SELECT State,
       Surgical_Procedure,

       COUNT(*) OVER (PARTITION BY State) AS TotalPatients,

       ROW_NUMBER() OVER (PARTITION BY State ORDER BY Surgical_Procedure) AS RowNum,

       RANK() OVER (PARTITION BY State ORDER BY Surgical_Procedure) AS Ranking

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;


SELECT *,
       COUNT(Surgical_Procedure) OVER (PARTITION BY State) AS PatientinCity
FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
WHERE Reference_Received_Date = '2024-02-23' and Surgical_Procedure = 'Lung Resection';




--ROW_NUMBER() → Unique numbering inside each State
SELECT Patient_ID,
       Patient_Name,
       State,
       Surgical_Procedure,

       ROW_NUMBER() OVER
       (
           PARTITION BY State
           ORDER BY Patient_ID
       ) AS StateWiseRowNumber

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;


--RANK() → Rank patients by Satisfaction Rating
SELECT Patient_Name,
       State,
       Patient_Satisfaction_Survey_Ratings,

       RANK() OVER
       (
           PARTITION BY State
           ORDER BY Patient_Satisfaction_Survey_Ratings DESC
       ) AS PatientRank

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;


--4️⃣ NTILE() → Divide patients into groups
SELECT Patient_Name,
       State,
       Body_Weight,

       NTILE(4) OVER
       (
           ORDER BY Body_Weight
       ) AS WeightQuartile

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;



--5️⃣ COUNT() OVER() → Total surgeries in each State

SELECT Patient_Name,
       State,
       Surgical_Procedure,

       COUNT(*) OVER
       (
           PARTITION BY State
       ) AS TotalPatientsInState

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;


--6️⃣ AVG() OVER() → Average Satisfaction by State
SELECT Patient_Name,
       State,
       Patient_Satisfaction_Survey_Ratings,

       AVG(Patient_Satisfaction_Survey_Ratings) OVER
       (
           PARTITION BY State
       ) AS AvgStateRating

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;


--7️⃣ Running Total using SUM()
SELECT Patient_ID,
       State,
       Patient_Satisfaction_Survey_Ratings,

       SUM(Patient_Satisfaction_Survey_Ratings) OVER
       (
           PARTITION BY State
           ORDER BY Patient_ID
       ) AS RunningTotal

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;


--1️⃣5️⃣ Complex Example (Multiple Windows Together)
WITH CTE AS
(
    SELECT Patient_Name,
           State,
           Patient_Satisfaction_Survey_Ratings,

           ROW_NUMBER() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS RN

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
)

SELECT *
FROM CTE
WHERE RN = 1;




--👉 What it does
--Divides data into separate groups for each State
--Sorts patients by highest satisfaction rating
--Gives unique sequential numbers

SELECT Patient_ID,
       Patient_Name,
       State,
       Surgical_Procedure,
       Patient_Satisfaction_Survey_Ratings,

       ROW_NUMBER() OVER
       (
           PARTITION BY State
           ORDER BY Patient_Satisfaction_Survey_Ratings DESC
       ) AS RowNum,

       RANK() OVER
       (
           PARTITION BY State
           ORDER BY Patient_Satisfaction_Survey_Ratings DESC
       ) AS Ranking,

       AVG(Patient_Satisfaction_Survey_Ratings) OVER
       (
           PARTITION BY State
       ) AS AvgRating,

       COUNT(*) OVER
       (
           PARTITION BY State
       ) AS TotalPatients

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;


--1️⃣ Top 3 Highest Rated Patients in Every State
WITH PatientRanking AS
(
    SELECT Patient_ID,
           Patient_Name,
           State,
           Surgical_Procedure,
           Patient_Satisfaction_Survey_Ratings,

           ROW_NUMBER() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS RowNum,

           RANK() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS Ranking

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
)

SELECT *
FROM PatientRanking
WHERE RowNum <= 3;

--2️⃣ Detect Lowest Performing Patients State-wise
WITH PoorPatients AS
(
    SELECT Patient_ID,
           Patient_Name,
           State,
           Patient_Satisfaction_Survey_Ratings,

           ROW_NUMBER() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings ASC
           ) AS LowestRow,

           RANK() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings ASC
           ) AS LowestRank

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
)

SELECT *
FROM PoorPatients
WHERE LowestRow <= 5;




--3️⃣ Latest Patients by State using Date
WITH LatestPatients AS
(
    SELECT Patient_ID,
           Patient_Name,
           State,
           Reference_Received_Date,

           ROW_NUMBER() OVER
           (
               PARTITION BY State
               ORDER BY Reference_Received_Date DESC
           ) AS LatestRow

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
)

SELECT *
FROM LatestPatients
WHERE LatestRow = 1;

--4️⃣ Surgery-wise Ranking inside each State
SELECT Patient_Name,
       State,
       Surgical_Procedure,
       Patient_Satisfaction_Survey_Ratings,

       ROW_NUMBER() OVER
       (
           PARTITION BY State, Surgical_Procedure
           ORDER BY Patient_Satisfaction_Survey_Ratings DESC
       ) AS SurgeryRowNum,

       RANK() OVER
       (
           PARTITION BY State, Surgical_Procedure
           ORDER BY Patient_Satisfaction_Survey_Ratings DESC
       ) AS SurgeryRank

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;



--5️⃣ Complex CASE + Window Function Example
WITH RatingCTE AS
(
    SELECT Patient_ID,
           Patient_Name,
           State,
           Surgical_Procedure,
           Patient_Satisfaction_Survey_Ratings,

           CASE
               WHEN Patient_Satisfaction_Survey_Ratings >= 90 THEN 'Excellent'
               WHEN Patient_Satisfaction_Survey_Ratings >= 75 THEN 'Good'
               WHEN Patient_Satisfaction_Survey_Ratings >= 50 THEN 'Average'
               ELSE 'Poor'
           END AS RatingCategory,

           ROW_NUMBER() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS RowNum,

           RANK() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS Ranking

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
)

SELECT *
FROM RatingCTE;



--9️⃣ Running Count inside Each State
SELECT Patient_ID,
       Patient_Name,
       State,
       Reference_Received_Date,

       COUNT(*) OVER
       (
           PARTITION BY State
           ORDER BY Reference_Received_Date
       ) AS RunningPatients

FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER;



--🔟 Final Advanced Classroom Example

WITH HospitalAnalytics AS
(
    SELECT Patient_ID,
           Patient_Name,
           State,
           Surgical_Procedure,
           Reference_Received_Date,
           Patient_Satisfaction_Survey_Ratings,

           CASE
               WHEN Patient_Satisfaction_Survey_Ratings >= 90 THEN 'Excellent'
               WHEN Patient_Satisfaction_Survey_Ratings >= 70 THEN 'Good'
               ELSE 'Needs Improvement'
           END AS RatingStatus,

           ROW_NUMBER() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS RowNum,

           RANK() OVER
           (
               PARTITION BY State
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS Ranking,

           AVG(Patient_Satisfaction_Survey_Ratings) OVER
           (
               PARTITION BY State
           ) AS AvgStateRating,

           COUNT(*) OVER
           (
               PARTITION BY State
           ) AS TotalPatients

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
)

SELECT *
FROM HospitalAnalytics
WHERE Ranking <= 5;



--1️⃣ Department-wise Patient Ranking with Tie Handling

WITH DepartmentAnalytics AS
(
    SELECT Patient_ID,
           Patient_Name,
           State,
           Surgical_Procedure,
           Patient_Satisfaction_Survey_Ratings,

           CASE
               WHEN Surgical_Procedure LIKE '%Heart%' THEN 'Cardiology'
               WHEN Surgical_Procedure LIKE '%Lung%' THEN 'Pulmonology'
               WHEN Surgical_Procedure LIKE '%Brain%' THEN 'Neurology'
               ELSE 'General'
           END AS Department,

           ROW_NUMBER() OVER
           (
               PARTITION BY State,
                            CASE
                                WHEN Surgical_Procedure LIKE '%Heart%' THEN 'Cardiology'
                                WHEN Surgical_Procedure LIKE '%Lung%' THEN 'Pulmonology'
                                WHEN Surgical_Procedure LIKE '%Brain%' THEN 'Neurology'
                                ELSE 'General'
                            END
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS RowNum,

           RANK() OVER
           (
               PARTITION BY State,
                            CASE
                                WHEN Surgical_Procedure LIKE '%Heart%' THEN 'Cardiology'
                                WHEN Surgical_Procedure LIKE '%Lung%' THEN 'Pulmonology'
                                WHEN Surgical_Procedure LIKE '%Brain%' THEN 'Neurology'
                                ELSE 'General'
                            END
               ORDER BY Patient_Satisfaction_Survey_Ratings DESC
           ) AS Ranking

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
)

SELECT *
FROM DepartmentAnalytics
WHERE Ranking <= 3;


--2️⃣ State-wise Daily Admission Trend Analysis
WITH DailyAdmissions AS
(
    SELECT State,
           Reference_Received_Date,

           COUNT(*) AS DailyPatients

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
    GROUP BY State,
             Reference_Received_Date
)

SELECT State,
       Reference_Received_Date,
       DailyPatients,

       ROW_NUMBER() OVER
       (
           PARTITION BY State
           ORDER BY DailyPatients DESC
       ) AS PeakDayRow,

       RANK() OVER
       (
           PARTITION BY State
           ORDER BY DailyPatients DESC
       ) AS PeakDayRank

FROM DailyAdmissions;




--6️⃣ Find Most Common Surgery per State
WITH SurgeryCounts AS
(
    SELECT State,
           Surgical_Procedure,

           COUNT(*) AS TotalSurgeries

    FROM ENZYMES35OOY91_MEADOWVIEW_REGIONAL_MEDICAL_CENTER
    GROUP BY State,
             Surgical_Procedure
),

RankedSurgeries AS
(
    SELECT *,

           ROW_NUMBER() OVER
           (
               PARTITION BY State
               ORDER BY TotalSurgeries DESC
           ) AS RowNum,

           RANK() OVER
           (
               PARTITION BY State
               ORDER BY TotalSurgeries DESC
           ) AS Ranking

    FROM SurgeryCounts
)

SELECT *
FROM RankedSurgeries
WHERE RowNum = 1;



Jersy Scenarios
**************************************************************************************************


  select *, rank() over(partition by Chief_Complaint order by Visit_Date_and_Time ) as Ranks from ENZYME42IDG24_JERSEY_CITY_MEDICAL_CENTER where Visit_Date_and_Time = '2026-03-22'

  SELECT *,
       ROW_NUMBER() OVER (
           PARTITION BY Primary_Care_Physician
           ORDER BY Visit_Date_and_Time
       ) AS VisitOrder
FROM ENZYME42IDG24_JERSEY_CITY_MEDICAL_CENTER;



SELECT *,
       SUM(Past_Emergency_Visits) OVER (
           PARTITION BY Patient_ID
       ) AS Total_Billing,

       AVG(Past_Emergency_Visits) OVER (
           PARTITION BY Patient_ID
       ) AS Avg_Billing,

       COUNT(*) OVER (
           PARTITION BY Patient_ID
       ) AS Visit_Count
FROM ENZYME42IDG24_JERSEY_CITY_MEDICAL_CENTER;

Dynamic Query
**************************************************************************************************

Great 👍 let’s start with simple and clear examples so your students can understand the concept of Dynamic SQL step by step.

🔹 What is Dynamic SQL?
Dynamic SQL means building a SQL query as a string and executing it at runtime.
Instead of writing:
SELECT * FROM Students WHERE City = 'Pune'

We create it dynamically:
DECLARE @Query NVARCHAR(MAX)

SET @Query = 'SELECT * FROM Students WHERE City = ''Pune'''

EXEC(@Query)


🔹 1. Basic Dynamic Query (Hardcoded Value)
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM Employees'

EXEC(@SQL)

👉 Simple example to show how EXEC works.

🔹 2. Dynamic WHERE Condition (User Input)
DECLARE @City NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @City = 'Mumbai'

SET @SQL = 'SELECT * FROM Employees WHERE City = ''' + @City + ''''

EXEC(@SQL)

👉 Teach:


String concatenation


Quotes handling ('')



🔹 3. Dynamic Column Selection
DECLARE @ColumnName NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @ColumnName = 'Salary'

SET @SQL = 'SELECT ' + @ColumnName + ' FROM Employees'

EXEC(@SQL)

👉 Useful when user selects column from UI.

🔹 4. Dynamic Table Name
DECLARE @TableName NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @TableName = 'Employees'

SET @SQL = 'SELECT * FROM ' + @TableName

EXEC(@SQL)

👉 Explain:


Useful in multi-table systems



🔹 5. Dynamic ORDER BY
DECLARE @SortColumn NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @SortColumn = 'Salary'

SET @SQL = 'SELECT * FROM Employees ORDER BY ' + @SortColumn

EXEC(@SQL)


🔹 6. Using sp_executesql (BEST PRACTICE ✅)
DECLARE @City NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @City = 'Pune'

SET @SQL = 'SELECT * FROM Employees WHERE City = @City'

EXEC sp_executesql 
    @SQL,
    N'@City NVARCHAR(50)',
    @City

👉 Teach this clearly:


Prevents SQL Injection


Better performance



🔹 7. Dynamic Query with Multiple Filters
DECLARE @City NVARCHAR(50) = 'Pune'
DECLARE @Dept NVARCHAR(50) = 'IT'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM Employees WHERE 1=1'

IF @City IS NOT NULL
    SET @SQL = @SQL + ' AND City = ''' + @City + ''''

IF @Dept IS NOT NULL
    SET @SQL = @SQL + ' AND Department = ''' + @Dept + ''''

EXEC(@SQL)

👉 Important concept:


WHERE 1=1 trick for dynamic conditions



🔹 Teaching Tips for Your Class 🎯
Start in this order:


Static query


Simple dynamic (EXEC)


Concatenation


Problems (quotes, injection)


Then introduce sp_executesql



If you want next level 🚀

I can create:


Real-world stored procedure example


Student practice questions


Dynamic pivot example (very useful in interviews)


Just tell me 👍
create dynamic queries bases on excel file I iuploaed
Got it 👍 — I couldn’t reliably read your .xls file in this environment, but no problem. I’ll still create realistic dynamic SQL examples based on a typical Excel structure, and you can easily map them to your file.

🎯 Assume your Excel table looks like this (very common)
Table: StudentsData
StudentID | Name | City | Course | Fees | AdmissionDate


🔥 Dynamic SQL Examples (Based on Excel Data)

🔹 1. Dynamic Filter (Based on Excel Input Cell)
👉 Example: User selects City in Excel → pass to SQL
DECLARE @City NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @City = 'Pune'   -- coming from Excel

SET @SQL = 'SELECT * FROM StudentsData WHERE City = ''' + @City + ''''

EXEC(@SQL)


🔹 2. Dynamic Multiple Filters (Excel Form Scenario)
👉 Suppose Excel has filters:


City


Course


DECLARE @City NVARCHAR(50) = 'Mumbai'
DECLARE @Course NVARCHAR(50) = 'SQL'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM StudentsData WHERE 1=1'

IF @City IS NOT NULL
    SET @SQL = @SQL + ' AND City = ''' + @City + ''''

IF @Course IS NOT NULL
    SET @SQL = @SQL + ' AND Course = ''' + @Course + ''''

EXEC(@SQL)

👉 Perfect for teaching search forms

🔹 3. Dynamic Column Selection (User selects column in Excel)
👉 Example: User selects "Fees" or "Name"
DECLARE @Column NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)

SET @Column = 'Fees'

SET @SQL = 'SELECT ' + @Column + ' FROM StudentsData'

EXEC(@SQL)


🔹 4. Dynamic Sorting (Excel Dropdown → Sort Option)
DECLARE @SortColumn NVARCHAR(50) = 'Fees'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM StudentsData ORDER BY ' + @SortColumn

EXEC(@SQL)


🔹 5. Dynamic Date Filter (Very Practical 🔥)
DECLARE @StartDate DATE = '2024-01-01'
DECLARE @EndDate DATE = '2024-12-31'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
SELECT * 
FROM StudentsData 
WHERE AdmissionDate BETWEEN ''' 
+ CAST(@StartDate AS NVARCHAR) + ''' AND ''' 
+ CAST(@EndDate AS NVARCHAR) + ''''

EXEC(@SQL)


🔹 6. Best Practice Version (sp_executesql ✅)
👉 Teach this as professional way
DECLARE @City NVARCHAR(50) = 'Pune'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM StudentsData WHERE City = @City'

EXEC sp_executesql 
    @SQL,
    N'@City NVARCHAR(50)',
    @City


🔹 7. Dynamic Table (Multiple Excel Sheets)
👉 Suppose:


Sheet1 → Students2023


Sheet2 → Students2024


DECLARE @TableName NVARCHAR(50) = 'Students2024'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * FROM ' + @TableName

EXEC(@SQL)


🎓 Classroom Teaching Idea (Very Powerful 💡)
Give students this task:
👉 “Create a dynamic search query where user can filter by:


City


Course


Fees range”


This combines everything:


WHERE 1=1


Conditions


Concatenation



⚠️ Important Concept to Teach
❌ Bad (SQL Injection risk)
'... WHERE Name = ''' + @Name + ''''

✅ Good
sp_executesql


🚀 If you want next level
I can:


Use your actual Excel file columns (if you re-upload as .xlsx)


Create stored procedure for your exact data


Build real-time Excel + SQL integration example


Give student assignment sheet + answers


Just tell me 👍


For providence Hospi
**************************************************************************************************

select * from ENZYME24TWA02_PROVIDENCE_NEWBERG_MEDICAL_CENTER order by Reference_Added_Date desc

--🔹 1. Select Patients
SELECT Patient_ID, [Patient_Name ], Age, Gender
FROM tblPatient_Infomation;

--🔹 2. Filter Patients (Age > 40)
SELECT [Patient_Name ], Age, Gender
FROM tblPatient_Infomation
WHERE Age > 40
group by [Patient_Name ], Age, Gender


--🔹 3. Procedures with High Cost
SELECT [Procedure_ID ], Cost
FROM [tbProcedures Table]
WHERE Cost > 5000;



--🔹 4. Paid Bills Only
SELECT *
FROM [tblBiling Table]
WHERE [Payment_Status ] = 'Paid';



--🔹 4. Paid Bills Only
SELECT 
    [Patient_ID ],
    SUM([Total_Amount ]) AS Total_Billing
FROM [tblBiling Table]
GROUP BY [Patient_ID ];

--🔹 6. Average Procedure Cost
SELECT 
    AVG(Cost) AS Avg_Procedure_Cost
FROM [tbProcedures Table];

--🔹 7. Count Reports by Status

SELECT 
    [Report_Status ],
    COUNT(*) AS Total_Reports
FROM [tblReports Table]
GROUP BY [Report_Status ];


--🔹 8. Doctors with Experience > 10 Years
SELECT 
    [Doctor_Name ], [Experience_Years ]
FROM [tblDoctore Information]
WHERE [Experience_Years ] > 10;


-------------------------------------JOINS----------------------

--🔹 9. Patient + Procedure

SELECT 
    P.[Patient_Name ],
    Pr.[Procedure_Name ],
    Pr.Cost
FROM [tblPatient_Infomation] P
JOIN [tbProcedures Table] Pr
    ON P.Patient_ID = Pr.[Patient_ID ];



--🔹 10. Patient + Billing

SELECT 
    P.[Patient_Name ],
    B.[Total_Amount ],
    B.[Payment_Status ]
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ];


--🔹 11. Procedure + Report
SELECT 
    Pr.[Procedure_Name ],
    R.[Report_Status ],
    R.[Severity_Score ]
FROM [tbProcedures Table] Pr
JOIN [tblReports Table] R
    ON Pr.[Procedure_ID ] = R.[Procedure_ID ];

--🔹 12. Total Revenue + Avg Procedure Time per Patient ( Errr0r)
SELECT 
    P.[Patient_Name ],
    SUM(B.[Total_Amount ]) AS Total_Revenue,
    AVG(Pr.[Duration_Minutes ]) AS Avg_Time
FROM [tblPatient_Infomation] P
JOIN [tbProcedures Table] Pr
    ON P.Patient_ID = Pr.[Patient_ID ]
JOIN [tblBiling Table] B
    ON Pr.[Procedure_ID ] = B.[Procedure_ID ]
GROUP BY P.[Patient_Name ];



--🔹 13. Patients with Pending Payments
SELECT 
    P.[Patient_Name ],
    SUM(B.[Total_Amount ] - B.[Paid_Amount ]) AS Pending_Amount
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY P.[Patient_Name ]
HAVING SUM(B.[Total_Amount ] - B.[Paid_Amount ]) > 0;


--🔹 14. Most Expensive Procedure per Patient
SELECT 
    [Patient_ID ],
    MAX(Cost) AS Max_Cost
FROM [tbProcedures Table]
GROUP BY [Patient_ID ];



--🔹 15. Ranking Patients by Billing (Window Function 🔥)
SELECT 
    [Patient_ID ],
    SUM([Total_Amount ]) AS Total_Billing,
    RANK() OVER (ORDER BY SUM([Total_Amount ]) DESC) AS Rank_By_Billing
FROM [tblBiling Table]
GROUP BY [Patient_ID ];


--🔹 16. Reports with High Severity
SELECT 
    [Patient_ID ],
    [Severity_Score ],
    [Report_Status ]
FROM [tblReports Table]
WHERE [Severity_Score ] > 7;



SELECT 
    [Imaging Modality],
    AVG([Radiation Dose]) AS Avg_Dose
FROM [ENZYME24TWA02_PROVIDENCE_NEWBERG_MEDICAL_CENTER]
GROUP BY [Imaging Modality];



------------------------Advance Level-----------------


--🧠 Q1. Find total revenue, total bills, and average bill amount per patient

SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    COUNT(B.Bill_ID) AS Total_Bills,
    SUM(B.[Total_Amount ]) AS Total_Revenue,
    AVG(B.[Total_Amount ]) AS Avg_Bill
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ]
ORDER BY Total_Revenue DESC;



--🧠 Q2. Find patient procedure stats (count, avg cost, max cost)

SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    COUNT(Pr.[Procedure_ID ]) AS Total_Procedures,
    AVG(Pr.Cost) AS Avg_Cost,
    MAX(Pr.Cost) AS Max_Cost
FROM [tblPatient_Infomation] P
LEFT JOIN [tbProcedures Table] Pr
    ON P.Patient_ID = Pr.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ]
ORDER BY Total_Procedures DESC;




--🧠 Q3. Combine billing + procedures (very powerful)

SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    COUNT(DISTINCT Pr.[Procedure_ID ]) AS Total_Procedures,
    SUM(B.[Total_Amount ]) AS Total_Billing,
    AVG(Pr.Cost) AS Avg_Procedure_Cost
FROM [tblPatient_Infomation] P
JOIN [tbProcedures Table] Pr
    ON P.Patient_ID = Pr.[Patient_ID ]
JOIN [tblBiling Table] B
    ON Pr.[Procedure_ID ] = B.[Procedure_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ]
ORDER BY Total_Billing DESC;


--🧠 Q4. Pending amount + number of pending bills per patient
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    COUNT(B.Bill_ID) AS Total_Bills,
    SUM(B.[Total_Amount ] - B.[Paid_Amount ]) AS Pending_Amount,
    AVG(B.[Total_Amount ] - B.[Paid_Amount ]) AS Avg_Pending
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ]
HAVING SUM(B.[Total_Amount ] - B.[Paid_Amount ]) > 0
ORDER BY Pending_Amount DESC;

--🧠 Q5. Report analysis per patient (with severity) Error
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    COUNT(R.Report_ID) AS Total_Reports,
    AVG(R.[Severity_Score ]) AS Avg_Severity,
    MAX(R.[Severity_Score ]) AS Max_Severity
FROM [tblPatient_Infomation] P
JOIN [tblReports Table] R
    ON P.Patient_ID = R.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ]
ORDER BY Avg_Severity DESC;




--🧠 Q6. Patients with no billing (LEFT JOIN case)
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    COUNT(B.Bill_ID) AS Total_Bills,
    ISNULL(SUM(B.[Total_Amount ]),0) AS Total_Billing,
    ISNULL(AVG(B.[Total_Amount ]),0) AS Avg_Billing
FROM [tblPatient_Infomation] P
LEFT JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ]
HAVING COUNT(B.Bill_ID) = 0;



--🧠 Q7. Full report with date filter (VERY IMPORTANT) Error

SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    COUNT(R.Report_ID) AS Total_Reports,
    AVG(R.[Severity_Score ]) AS Avg_Severity,
    SUM(B.[Total_Amount ]) AS Total_Billing
FROM [tblPatient_Infomation] P
JOIN [tblReports Table] R
    ON P.Patient_ID = R.[Patient_ID ]
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
WHERE R.[Reference_Added_Date ] 
      BETWEEN '2026-03-01' AND '2026-03-03'
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ]
ORDER BY Total_Billing DESC;




--🧠 Q1. Find total billing per patient (with patient details)
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Gender,
    P.Age,
    SUM(B.[Total_Amount ]) AS Total_Billing
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Gender,
    P.Age
ORDER BY Total_Billing DESC;


--🧠 Q2. Count number of procedures per patient
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Gender,
    Pr.[Procedure_Name ],
    COUNT(Pr.[Procedure_ID ]) AS Total_Procedures
FROM [tblPatient_Infomation] P
LEFT JOIN [tbProcedures Table] Pr
    ON P.Patient_ID = Pr.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Gender,
    Pr.[Procedure_Name ]
ORDER BY Total_Procedures DESC;


--🧠 Q3. Average procedure cost per patient
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Age,
    Pr.[Procedure_Name ],
    AVG(Pr.Cost) AS Avg_Cost
FROM [tblPatient_Infomation] P
JOIN [tbProcedures Table] Pr
    ON P.Patient_ID = Pr.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Age,
    Pr.[Procedure_Name ]
ORDER BY Avg_Cost DESC;



--🧠 Q4. Find patients having more than 2 bills
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Gender,
    B.[Payment_Status ],
    COUNT(B.Bill_ID) AS Total_Bills
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ],
    P.Gender,
    B.[Payment_Status ]
HAVING COUNT(B.Bill_ID) > 2;


--🧠 Q5. Find average severity score by report status Error

SELECT 
    R.[Report_Status ],
    R.[Patient_ID ],
    P.[Patient_Name ],
    R.[Findings ],
    AVG(R.[Severity_Score ]) AS Avg_Severity
FROM [tblReports Table] R
JOIN [tblPatient_Infomation] P
    ON R.[Patient_ID ] = P.Patient_ID
GROUP BY 
    R.[Report_Status ],
    R.[Patient_ID ],
    P.[Patient_Name ],
    R.[Findings ];


--🧠 Q6. Find total pending amount per patient

SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    B.[Payment_Status ],
    B.[Billing_Date ],
    SUM(B.[Total_Amount ] - B.[Paid_Amount ]) AS Pending_Amount
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ],
    B.[Payment_Status ],
    B.[Billing_Date ]
HAVING SUM(B.[Total_Amount ] - B.[Paid_Amount ]) > 0;

--🧠 Q7. Count reports within a date range

SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    R.[Report_Status ],
    R.[Reference_Added_Date ],
    COUNT(R.Report_ID) AS Total_Reports
FROM [tblPatient_Infomation] P
JOIN [tblReports Table] R
    ON P.Patient_ID = R.[Patient_ID ]
WHERE R.[Reference_Added_Date ] 
      BETWEEN '2026-03-01' AND '2026-03-03'
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ],
    R.[Report_Status ],
    R.[Reference_Added_Date ];


--🧠 Q8. CONCAT example (Full name style + aggregation)
	SELECT 
    P.Patient_ID,
    CONCAT(P.[Patient_Name ], ' - ', P.Gender) AS Patient_Info,
    B.[Payment_Status ],
    B.[Billing_Date ],
    SUM(B.[Total_Amount ]) AS Total_Billing
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    CONCAT(P.[Patient_Name ], ' - ', P.Gender),
    B.[Payment_Status ],
    B.[Billing_Date ];


--🧠 Q9. Find procedures with high average cost (> 3000)
SELECT 
    Pr.[Procedure_Name ],
    Pr.[Patient_ID ],
    P.[Patient_Name ],
    Pr.[Procedure_Date ],
    AVG(Pr.Cost) AS Avg_Cost
FROM [tbProcedures Table] Pr
JOIN [tblPatient_Infomation] P
    ON Pr.[Patient_ID ] = P.Patient_ID
GROUP BY 
    Pr.[Procedure_Name ],
    Pr.[Patient_ID ],
    P.[Patient_Name ],
    Pr.[Procedure_Date ]
HAVING AVG(Pr.Cost) > 3000;


--🧠 Q10. Count patients by gender (with extra columns)

SELECT 
    P.Gender,
    P.Age,
    P.[Patient_Name ],
    P.[Registration_Date ],
    COUNT(P.Patient_ID) AS Total_Count
FROM [tblPatient_Infomation] P
GROUP BY 
    P.Gender,
    P.Age,
    P.[Patient_Name ],
    P.[Registration_Date ]
ORDER BY Total_Count DESC;



--🧠 Q1. Categorize patients based on total billing
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    SUM(B.[Total_Amount ]) AS Total_Billing,
    CASE 
        WHEN SUM(B.[Total_Amount ]) > 10000 THEN 'High Value'
        WHEN SUM(B.[Total_Amount ]) BETWEEN 5000 AND 10000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS Patient_Category
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
GROUP BY 
    P.Patient_ID,
    P.[Patient_Name ];


--🧠 Q2. Payment status summary (Paid vs Pending)
SELECT 
    B.[Payment_Status ],
    COUNT(B.Bill_ID) AS Total_Bills,
    CASE 
        WHEN B.[Payment_Status ] = 'Paid' THEN 'Completed'
        ELSE 'Pending Review'
    END AS Status_Remark
FROM [tblBiling Table] B
GROUP BY 
    B.[Payment_Status ];



--🧠 Q3. Procedure cost category
SELECT 
    Pr.[Procedure_Name ],
    Pr.[Patient_ID ],
    Pr.Cost,
    CASE 
        WHEN Pr.Cost > 5000 THEN 'Expensive'
        WHEN Pr.Cost BETWEEN 2000 AND 5000 THEN 'Moderate'
        ELSE 'Low Cost'
    END AS Cost_Category,
    COUNT(Pr.[Procedure_ID ]) AS Total_Count
FROM [tbProcedures Table] Pr
GROUP BY 
    Pr.[Procedure_Name ],
    Pr.[Patient_ID ],
    Pr.Cost;


--🧠 Q4. Find patients whose billing is above average
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    B.[Total_Amount ]
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
WHERE B.[Total_Amount ] > (
    SELECT AVG([Total_Amount ]) 
    FROM [tblBiling Table]
);


--🧠 Q7. Find patients with maximum bill amount
SELECT 
    P.Patient_ID,
    P.[Patient_Name ],
    B.[Total_Amount ]
FROM [tblPatient_Infomation] P
JOIN [tblBiling Table] B
    ON P.Patient_ID = B.[Patient_ID ]
WHERE B.[Total_Amount ] = (
    SELECT MAX([Total_Amount ]) 
    FROM [tblBiling Table]
);

Queries without alias
**************************************************************************************************
🧠 Q1. Find total revenue, total bills, and average bill amount per patient

SELECT 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ],
    COUNT([tblBiling Table].Bill_ID) AS Total_Bills,
    SUM([tblBiling Table].[Total_Amount ]) AS Total_Revenue,
    AVG([tblBiling Table].[Total_Amount ]) AS Avg_Bill
FROM tblPatient_Infomation
JOIN [tblBiling Table]
    ON tblPatient_Infomation.Patient_ID = [tblBiling Table].[Patient_ID ]
GROUP BY 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ]
ORDER BY Total_Revenue DESC;


🧠 Q2. Find patient procedure stats (count, avg cost, max cost)

SELECT 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ],
    COUNT([tbProcedures Table].[Procedure_ID ]) AS Total_Procedures,
    AVG([tbProcedures Table].Cost) AS Avg_Cost,
    MAX([tbProcedures Table].Cost) AS Max_Cost
FROM tblPatient_Infomation
LEFT JOIN [tbProcedures Table]
    ON tblPatient_Infomation.Patient_ID = [tbProcedures Table].[Patient_ID ]
GROUP BY 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ]
ORDER BY Total_Procedures DESC;


🧠 Q3. Combine billing + procedures (very powerful)

SELECT 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ],
    COUNT(DISTINCT [tbProcedures Table].[Procedure_ID ]) AS Total_Procedures,
    SUM([tblBiling Table].[Total_Amount ]) AS Total_Billing,
    AVG([tbProcedures Table].Cost) AS Avg_Procedure_Cost
FROM tblPatient_Infomation
JOIN [tbProcedures Table]
    ON tblPatient_Infomation.Patient_ID = [tbProcedures Table].[Patient_ID ]
JOIN [tblBiling Table]
    ON [tbProcedures Table].[Procedure_ID ] = [tblBiling Table].[Procedure_ID ]
GROUP BY 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ]
ORDER BY Total_Billing DESC;


🧠 Q4. Pending amount + number of pending bills per patient

SELECT 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ],
    COUNT([tblBiling Table].Bill_ID) AS Total_Bills,
    SUM([tblBiling Table].[Total_Amount ] - [tblBiling Table].[Paid_Amount ]) AS Pending_Amount,
    AVG([tblBiling Table].[Total_Amount ] - [tblBiling Table].[Paid_Amount ]) AS Avg_Pending
FROM tblPatient_Infomation
JOIN [tblBiling Table]
    ON tblPatient_Infomation.Patient_ID = [tblBiling Table].[Patient_ID ]
GROUP BY 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ]
HAVING SUM([tblBiling Table].[Total_Amount ] - [tblBiling Table].[Paid_Amount ]) > 0
ORDER BY Pending_Amount DESC;


🧠 Q5. Report analysis per patient (with severity)

SELECT 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ],
    COUNT([tblReports Table].Report_ID) AS Total_Reports,
    AVG([tblReports Table].[Severity_Score ]) AS Avg_Severity,
    MAX([tblReports Table].[Severity_Score ]) AS Max_Severity
FROM tblPatient_Infomation
JOIN [tblReports Table]
    ON tblPatient_Infomation.Patient_ID = [tblReports Table].[Patient_ID ]
GROUP BY 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ]
ORDER BY Avg_Severity DESC;


🧠 Q6. Patients with no billing (LEFT JOIN case)

SELECT 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ],
    COUNT([tblBiling Table].Bill_ID) AS Total_Bills,
    ISNULL(SUM([tblBiling Table].[Total_Amount ]),0) AS Total_Billing,
    ISNULL(AVG([tblBiling Table].[Total_Amount ]),0) AS Avg_Billing
FROM tblPatient_Infomation
LEFT JOIN [tblBiling Table]
    ON tblPatient_Infomation.Patient_ID = [tblBiling Table].[Patient_ID ]
GROUP BY 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ]
HAVING COUNT([tblBiling Table].Bill_ID) = 0;


🧠 Q7. Full report with date filter (VERY IMPORTANT)

SELECT 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ],
    COUNT(DISTINCT [tblReports Table].Report_ID) AS Total_Reports,
    AVG([tblReports Table].[Severity_Score ]) AS Avg_Severity,
    SUM([tblBiling Table].[Total_Amount ]) AS Total_Billing
FROM tblPatient_Infomation
JOIN [tblReports Table]
    ON tblPatient_Infomation.Patient_ID = [tblReports Table].[Patient_ID ]
JOIN [tblBiling Table]
    ON tblPatient_Infomation.Patient_ID = [tblBiling Table].[Patient_ID ]
WHERE [tblReports Table].[Reference_Added_Date ] 
      BETWEEN '2026-03-01' AND '2026-03-03'
GROUP BY 
    tblPatient_Infomation.Patient_ID,
    tblPatient_Infomation.[Patient_Name ]
ORDER BY Total_Billing DESC;

------------------------------------------------------------------------------------------------------------------------------------

SQL SERVER — COMPLETE ROADMAP
╔══════════════════════════════════════════════════════════════════════╗
║                         SQL SERVER                                  ║
╚══════════════════════════════════════════════════════════════════════╝

│
├── 01. QUERY BASICS
│   │
│   ├── SELECT
│   │   ├── SELECT
│   │   ├── DISTINCT
│   │   ├── TOP
│   │   ├── Column Aliases (AS)
│   │   ├── Expressions
│   │   ├── CASE
│   │   ├── CAST
│   │   ├── CONVERT
│   │   └── Functions
│   │       ├── String Functions
│   │       ├── Date/Time Functions
│   │       ├── Numeric Functions
│   │       ├── NULL Functions
│   │       └── Conversion Functions
│   │
│   ├── FROM
│   │   ├── Tables
│   │   ├── Views
│   │   ├── Derived Tables
│   │   ├── Table-Valued Functions
│   │   └── Subqueries
│   │
│   └── Query Structure
│       ├── SELECT
│       ├── FROM
│       ├── JOIN
│       ├── WHERE
│       ├── GROUP BY
│       ├── HAVING
│       └── ORDER BY
│
│
├── 02. JOINS
│   │
│   ├── INNER JOIN
│   ├── LEFT JOIN
│   ├── RIGHT JOIN
│   ├── FULL OUTER JOIN
│   ├── CROSS JOIN
│   ├── SELF JOIN
│   └── JOIN Conditions
│       └── ON
│
│
├── 03. FILTERING
│   │
│   └── WHERE
│       │
│       ├── Comparison
│       │   ├── =
│       │   ├── <>
│       │   ├── !=
│       │   ├── >
│       │   ├── <
│       │   ├── >=
│       │   └── <=
│       │
│       ├── Logical
│       │   ├── AND
│       │   ├── OR
│       │   └── NOT
│       │
│       ├── Range
│       │   ├── BETWEEN
│       │   └── NOT BETWEEN
│       │
│       ├── List
│       │   ├── IN
│       │   └── NOT IN
│       │
│       ├── Pattern
│       │   ├── LIKE
│       │   └── NOT LIKE
│       │
│       ├── NULL
│       │   ├── IS NULL
│       │   └── IS NOT NULL
│       │
│       └── Subquery Predicates
│           ├── EXISTS
│           ├── NOT EXISTS
│           ├── ANY
│           ├── SOME
│           └── ALL
│
│
├── 04. GROUPING & AGGREGATION
│   │
│   ├── GROUP BY
│   │   ├── Single Column
│   │   ├── Multiple Columns
│   │   ├── Expressions
│   │   └── ROLLUP
│   │
│   ├── Aggregate Functions
│   │   ├── COUNT()
│   │   ├── COUNT_BIG()
│   │   ├── SUM()
│   │   ├── AVG()
│   │   ├── MIN()
│   │   ├── MAX()
│   │   ├── STDEV()
│   │   ├── STDEVP()
│   │   ├── VAR()
│   │   └── VARP()
│   │
│   └── HAVING
│       ├── Aggregate Conditions
│       ├── GROUP BY Conditions
│       ├── AND
│       ├── OR
│       └── NOT
│
│
├── 05. SORTING & PAGINATION
│   │
│   ├── ORDER BY
│   │   ├── ASC
│   │   ├── DESC
│   │   ├── Multiple Columns
│   │   └── Expressions
│   │
│   ├── TOP
│   │   └── TOP WITH TIES
│   │
│   └── Pagination
│       ├── OFFSET
│       └── FETCH NEXT
│
│
├── 06. SET OPERATORS
│   │
│   ├── UNION
│   ├── UNION ALL
│   ├── INTERSECT
│   └── EXCEPT
│
│
├── 07. SUBQUERIES
│   │
│   ├── By Result Type
│   │   ├── Scalar Subquery
│   │   ├── Single-Row Subquery
│   │   └── Multi-Row Subquery
│   │
│   ├── By Relationship
│   │   ├── Correlated Subquery
│   │   └── Non-Correlated Subquery
│   │
│   ├── By Location
│   │   ├── SELECT
│   │   ├── FROM
│   │   ├── WHERE
│   │   └── HAVING
│   │
│   └── Subquery Operators
│       ├── EXISTS
│       ├── NOT EXISTS
│       ├── IN
│       ├── NOT IN
│       ├── ANY
│       ├── SOME
│       └── ALL
│
│
├── 08. CTE — COMMON TABLE EXPRESSIONS
│   │
│   ├── WITH
│   ├── Non-Recursive CTE
│   ├── Recursive CTE
│   └── Multiple CTEs
│
│
├── 09. WINDOW / ANALYTIC FUNCTIONS
│   │
│   ├── OVER()
│   │   ├── PARTITION BY
│   │   └── ORDER BY
│   │
│   ├── Ranking
│   │   ├── ROW_NUMBER()
│   │   ├── RANK()
│   │   ├── DENSE_RANK()
│   │   └── NTILE()
│   │
│   ├── Offset
│   │   ├── LAG()
│   │   └── LEAD()
│   │
│   └── Window Aggregates
│       ├── SUM() OVER()
│       ├── AVG() OVER()
│       ├── MIN() OVER()
│       ├── MAX() OVER()
│       └── COUNT() OVER()
│
│
├── 10. DATA MANIPULATION — DML
│   │
│   ├── INSERT
│   │   ├── INSERT INTO
│   │   ├── VALUES
│   │   ├── INSERT ... SELECT
│   │   ├── Multiple Rows
│   │   ├── DEFAULT VALUES
│   │   └── OUTPUT
│   │
│   ├── UPDATE
│   │   ├── UPDATE
│   │   ├── SET
│   │   ├── WHERE
│   │   ├── UPDATE + JOIN
│   │   ├── UPDATE + Subquery
│   │   └── OUTPUT
│   │
│   ├── DELETE
│   │   ├── DELETE
│   │   ├── WHERE
│   │   ├── DELETE + JOIN
│   │   ├── DELETE + Subquery
│   │   └── OUTPUT
│   │
│   └── MERGE
│       ├── WHEN MATCHED
│       ├── WHEN NOT MATCHED
│       └── WHEN NOT MATCHED BY SOURCE
│
│
├── 11. DATA TYPES & EXPRESSIONS
│   │
│   ├── Numeric
│   ├── Character / String
│   ├── Date / Time
│   ├── Binary
│   ├── Boolean-like / BIT
│   ├── XML
│   ├── JSON
│   └── User-Defined Types
│
│
├── 12. STRING FUNCTIONS
│   │
│   ├── CONCAT
│   ├── CONCAT_WS
│   ├── LEFT
│   ├── RIGHT
│   ├── SUBSTRING
│   ├── LEN
│   ├── DATALENGTH
│   ├── LOWER
│   ├── UPPER
│   ├── LTRIM
│   ├── RTRIM
│   ├── TRIM
│   ├── REPLACE
│   ├── CHARINDEX
│   ├── PATINDEX
│   └── STRING_SPLIT
│
│
├── 13. DATE & TIME FUNCTIONS
│   │
│   ├── GETDATE()
│   ├── GETUTCDATE()
│   ├── SYSDATETIME()
│   ├── DATEADD()
│   ├── DATEDIFF()
│   ├── DATEDIFF_BIG()
│   ├── DATEPART()
│   ├── DATENAME()
│   ├── DAY()
│   ├── MONTH()
│   ├── YEAR()
│   ├── EOMONTH()
│   ├── DATEFROMPARTS()
│   └── DATETIMEFROMPARTS()
│
│
├── 14. NULL & CONDITIONAL LOGIC
│   │
│   ├── NULL
│   │   ├── IS NULL
│   │   ├── IS NOT NULL
│   │   ├── ISNULL()
│   │   ├── COALESCE()
│   │   └── NULLIF()
│   │
│   └── Conditional
│       ├── CASE
│       │   ├── Simple CASE
│       │   └── Searched CASE
│       ├── IIF()
│       └── CHOOSE()
│
│
├── 15. DATA CONVERSION
│   │
│   ├── CAST()
│   ├── CONVERT()
│   ├── TRY_CAST()
│   └── TRY_CONVERT()
│
│
├── 16. DATABASE OBJECTS — DDL
│   │
│   ├── DATABASE
│   │   ├── CREATE DATABASE
│   │   ├── ALTER DATABASE
│   │   └── DROP DATABASE
│   │
│   ├── TABLE
│   │   ├── CREATE TABLE
│   │   ├── ALTER TABLE
│   │   └── DROP TABLE
│   │
│   ├── VIEW
│   │   ├── CREATE VIEW
│   │   ├── ALTER VIEW
│   │   └── DROP VIEW
│   │
│   ├── INDEX
│   │   ├── CREATE INDEX
│   │   ├── ALTER INDEX
│   │   └── DROP INDEX
│   │
│   ├── PROCEDURE
│   │   ├── CREATE PROCEDURE
│   │   ├── ALTER PROCEDURE
│   │   └── DROP PROCEDURE
│   │
│   ├── FUNCTION
│   │   ├── CREATE FUNCTION
│   │   ├── ALTER FUNCTION
│   │   └── DROP FUNCTION
│   │
│   └── TRIGGER
│       ├── CREATE TRIGGER
│       ├── ALTER TRIGGER
│       └── DROP TRIGGER
│
│
├── 17. CONSTRAINTS
│   │
│   ├── PRIMARY KEY
│   ├── FOREIGN KEY
│   ├── UNIQUE
│   ├── NOT NULL
│   ├── CHECK
│   └── DEFAULT
│
│
├── 18. INDEXES
│   │
│   ├── Clustered Index
│   ├── Nonclustered Index
│   ├── Unique Index
│   ├── Composite Index
│   ├── Included Columns
│   ├── Filtered Index
│   └── Index Fragmentation
│
│
├── 19. VIEWS
│   │
│   ├── Regular Views
│   ├── Indexed Views
│   ├── View Limitations
│   └── View Modification
│
│
├── 20. STORED PROCEDURES
│   │
│   ├── CREATE PROCEDURE
│   ├── ALTER PROCEDURE
│   ├── EXEC / EXECUTE
│   ├── Input Parameters
│   ├── Output Parameters
│   ├── Return Values
│   └── Dynamic SQL
│
│
├── 21. FUNCTIONS
│   │
│   ├── Built-in Functions
│   ├── Scalar Functions
│   ├── Inline Table-Valued Functions
│   ├── Multi-Statement Table-Valued Functions
│   └── User-Defined Functions
│
│
├── 22. TEMPORARY OBJECTS
│   │
│   ├── Temporary Tables
│   │   ├── Local #TempTable
│   │   └── Global ##TempTable
│   │
│   ├── Table Variables
│   └── Temporary Objects
│
│
├── 23. VARIABLES & CONTROL FLOW
│   │
│   ├── Variables
│   │   ├── DECLARE
│   │   ├── SET
│   │   ├── SELECT Assignment
│   │   └── Table Variables
│   │
│   └── Control Flow
│       ├── IF
│       ├── ELSE
│       ├── BEGIN
│       ├── END
│       ├── WHILE
│       ├── BREAK
│       ├── CONTINUE
│       └── RETURN
│
│
├── 24. TRANSACTIONS
│   │
│   ├── BEGIN TRANSACTION
│   ├── COMMIT
│   ├── ROLLBACK
│   ├── SAVE TRANSACTION
│   └── Isolation Levels
│
│
├── 25. ERROR HANDLING
│   │
│   ├── TRY / CATCH
│   │   ├── BEGIN TRY
│   │   ├── END TRY
│   │   ├── BEGIN CATCH
│   │   └── END CATCH
│   │
│   ├── ERROR_NUMBER()
│   ├── ERROR_MESSAGE()
│   ├── ERROR_LINE()
│   └── THROW
│
│
├── 26. DYNAMIC SQL
│   │
│   ├── EXEC()
│   ├── sp_executesql
│   ├── Dynamic Parameters
│   └── SQL Injection
│
│
└── 27. ADVANCED SQL SERVER
    │
    ├── APPLY
    │   ├── CROSS APPLY
    │   └── OUTER APPLY
    │
    ├── PIVOT
    ├── UNPIVOT
    │
    ├── Advanced GROUP BY
    │   ├── GROUPING SETS
    │   ├── ROLLUP
    │   └── CUBE
    │
    ├── Recursive Queries
    ├── JSON
    ├── XML
    ├── Full-Text Search
    ├── Temporal Tables
    ├── Sequences
    ├── Identity
    ├── Synonyms
    └── Query Optimization
SQL SERVER — LEARNING LEVELS
╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 1 — SQL QUERY FUNDAMENTALS                                    ║
╠══════════════════════════════════════════════════════════════════════╣
║ SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY        ║
║                                                                      ║
║ Learn:                                                               ║
║ • SELECT / DISTINCT / TOP                                            ║
║ • FROM                                                                ║
║ • JOIN                                                                ║
║ • WHERE                                                               ║
║ • GROUP BY                                                            ║
║ • Aggregate Functions                                                 ║
║ • HAVING                                                              ║
║ • ORDER BY                                                            ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 2 — FILTERING & QUERY LOGIC                                   ║
╠══════════════════════════════════════════════════════════════════════╣
║ WHERE                                                                 ║
║ ├── Comparison                                                        ║
║ ├── AND / OR / NOT                                                    ║
║ ├── BETWEEN                                                           ║
║ ├── IN / NOT IN                                                       ║
║ ├── LIKE / NOT LIKE                                                   ║
║ ├── IS NULL / IS NOT NULL                                             ║
║ └── EXISTS / NOT EXISTS / ANY / SOME / ALL                            ║
║                                                                      ║
║ Also: CASE • NULL handling • CAST • CONVERT                          ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 3 — ADVANCED QUERYING                                         ║
╠══════════════════════════════════════════════════════════════════════╣
║ • SET OPERATORS                                                       ║
║   ├── UNION                                                           ║
║   ├── UNION ALL                                                       ║
║   ├── INTERSECT                                                       ║
║   └── EXCEPT                                                          ║
║                                                                      ║
║ • SUBQUERIES                                                          ║
║ • CTE                                                                  ║
║ • WINDOW FUNCTIONS                                                    ║
║ • ROW_NUMBER / RANK / DENSE_RANK                                     ║
║ • LAG / LEAD                                                          ║
║ • OVER / PARTITION BY                                                 ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 4 — SQL FUNCTIONS                                              ║
╠══════════════════════════════════════════════════════════════════════╣
║ • String Functions                                                    ║
║ • Date & Time Functions                                               ║
║ • Numeric Functions                                                   ║
║ • NULL Functions                                                      ║
║ • Conditional Functions                                               ║
║ • Conversion Functions                                                ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 5 — DATA MANIPULATION (DML)                                   ║
╠══════════════════════════════════════════════════════════════════════╣
║ • INSERT                                                              ║
║ • UPDATE                                                              ║
║ • DELETE                                                              ║
║ • MERGE                                                               ║
║ • OUTPUT                                                               ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 6 — DATABASE STRUCTURE (DDL)                                  ║
╠══════════════════════════════════════════════════════════════════════╣
║ • CREATE                                                              ║
║ • ALTER                                                               ║
║ • DROP                                                                ║
║                                                                      ║
║ Objects:                                                              ║
║ • Database                                                             ║
║ • Table                                                                ║
║ • View                                                                 ║
║ • Index                                                                ║
║ • Procedure                                                            ║
║ • Function                                                             ║
║ • Trigger                                                              ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 7 — DATABASE DESIGN                                            ║
╠══════════════════════════════════════════════════════════════════════╣
║ • Data Types                                                           ║
║ • Primary Key                                                          ║
║ • Foreign Key                                                          ║
║ • UNIQUE                                                                ║
║ • NOT NULL                                                             ║
║ • CHECK                                                                ║
║ • DEFAULT                                                              ║
║ • Normalization                                                        ║
║ • Relationships                                                        ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 8 — SQL SERVER PROGRAMMING                                    ║
╠══════════════════════════════════════════════════════════════════════╣
║ • Variables                                                            ║
║ • IF / ELSE                                                            ║
║ • WHILE                                                                ║
║ • Stored Procedures                                                    ║
║ • User-Defined Functions                                               ║
║ • Temporary Tables                                                      ║
║ • Table Variables                                                       ║
║ • Dynamic SQL                                                           ║
║ • Error Handling                                                        ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 9 — TRANSACTIONS & CONCURRENCY                                ║
╠══════════════════════════════════════════════════════════════════════╣
║ • BEGIN TRANSACTION                                                    ║
║ • COMMIT                                                               ║
║ • ROLLBACK                                                             ║
║ • SAVE TRANSACTION                                                      ║
║ • Isolation Levels                                                     ║
║ • Locking                                                              ║
║ • Blocking                                                             ║
║ • Deadlocks                                                             ║
╚══════════════════════════════════════════════════════════════════════╝


╔══════════════════════════════════════════════════════════════════════╗
║ LEVEL 10 — PERFORMANCE & ADVANCED SQL SERVER                        ║
╠══════════════════════════════════════════════════════════════════════╣
║ • Clustered / Nonclustered Indexes                                    ║
║ • Composite Indexes                                                    ║
║ • Included Columns                                                     ║
║ • Filtered Indexes                                                     ║
║ • Index Fragmentation                                                  ║
║ • Execution Plans                                                      ║
║ • Query Optimization                                                   ║
║ • PIVOT / UNPIVOT                                                      ║
║ • CROSS APPLY / OUTER APPLY                                            ║
║ • GROUPING SETS / ROLLUP / CUBE                                        ║
║ • JSON / XML                                                           ║
║ • Temporal Tables                                                      ║
║ • Full-Text Search                                                     ║
║ • Sequences                                                            ║
╚══════════════════════════════════════════════════════════════════════╝
THE BIG PICTURE
                         ┌───────────────────────┐
                         │      SQL SERVER       │
                         └───────────┬───────────┘
                                     │
          ┌──────────────────────────┼──────────────────────────┐
          │                          │                          │
          ▼                          ▼                          ▼
 ┌─────────────────┐       ┌─────────────────┐       ┌─────────────────┐
 │  QUERYING       │       │ DATA CHANGES    │       │ DATABASE DESIGN │
 └────────┬────────┘       └────────┬────────┘       └────────┬────────┘
          │                         │                         │
          ├── SELECT               ├── INSERT                ├── TABLE
          ├── FROM                 ├── UPDATE                ├── CONSTRAINTS
          ├── JOIN                 ├── DELETE                ├── DATA TYPES
          ├── WHERE                └── MERGE                 └── RELATIONSHIPS
          ├── GROUP BY
          ├── HAVING
          └── ORDER BY
                    │
                    ▼
          ┌─────────────────────┐
          │ ADVANCED QUERYING   │
          └──────────┬──────────┘
                     │
          ├── SUBQUERIES
          ├── CTE
          ├── WINDOW FUNCTIONS
          ├── SET OPERATORS
          ├── APPLY
          └── PIVOT / UNPIVOT
                     │
                     ▼
          ┌─────────────────────┐
          │ SQL PROGRAMMING     │
          └──────────┬──────────┘
                     │
          ├── VARIABLES
          ├── IF / ELSE
          ├── WHILE
          ├── PROCEDURES
          ├── FUNCTIONS
          ├── TEMP TABLES
          ├── DYNAMIC SQL
          └── ERROR HANDLING
                     │
                     ▼
          ┌─────────────────────┐
          │ TRANSACTIONS        │
          └──────────┬──────────┘
                     │
          ├── COMMIT
          ├── ROLLBACK
          ├── SAVEPOINT
          ├── LOCKING
          ├── BLOCKING
          └── DEADLOCKS
                     │
                     ▼
          ┌─────────────────────┐
          │ PERFORMANCE         │
          └──────────┬──────────┘
                     │
          ├── INDEXES
          ├── EXECUTION PLANS
          ├── QUERY OPTIMIZATION
          └── PERFORMANCE TUNING
MOST IMPORTANT LEARNING ORDER
01 → SELECT
02 → FROM
03 → JOIN
04 → WHERE
05 → GROUP BY
06 → Aggregate Functions
07 → HAVING
08 → ORDER BY
09 → CASE
10 → NULL Handling
11 → String Functions
12 → Date/Time Functions
13 → Subqueries
14 → UNION / INTERSECT / EXCEPT
15 → CTE
16 → Window Functions
17 → INSERT / UPDATE / DELETE
18 → Constraints
19 → Tables / Views
20 → Stored Procedures
21 → Functions
22 → Temporary Tables
23 → Transactions
24 → Error Handling
25 → Indexes
26 → Execution Plans
27 → Query Optimization
28 → Advanced SQL Server

CSS

SQL Server

Basic SQL Query

SQL Query with filter condition (Where)

SQL Query with count

SQL Query Backup table

Update table with Case...end based on another table

Update table with Joints

Insert the data in new table

Insert the data in already existing table which has data

Vlookup Like connection in SQL

Real time Join & Insert

Delete the duplicate Data with Group by Max

Delete the duplicate Data with CTE and ROW Function

Find all the store procedures associated to particular table

Select/ Find the version of sql server

Check SQL Server Instance Name

Check Current Database Info

List All Databases on the Server

List All Tables in a Specific Database

Find SQL Server Login Info

Find SQL Randomize

Call Center

KMr Enterprise Sim seller

With Table Fixed values randomize

CMD Loop

Join Query

Autdit MIS

Above Solution

⚡ Better Solution: Use Dynamic SQL + Date Parameter

Store Procedure & Parameter

🚀 Benefits

✅ No need to rewrite query daily

Windows functions

🔥 Windows functions ( Important)

Medview Scenarios

Jersy Scenarios

Dynamic Query

🔹 What is Dynamic SQL?

🔹 1. Basic Dynamic Query (Hardcoded Value)

🔹 2. Dynamic WHERE Condition (User Input)

🔹 3. Dynamic Column Selection

🔹 4. Dynamic Table Name

🔹 5. Dynamic ORDER BY

🔹 6. Using sp_executesql (BEST PRACTICE ✅)

🔹 7. Dynamic Query with Multiple Filters

🔹 Teaching Tips for Your Class 🎯

🎯 Assume your Excel table looks like this (very common)

🔥 Dynamic SQL Examples (Based on Excel Data)

🔹 1. Dynamic Filter (Based on Excel Input Cell)

🔹 2. Dynamic Multiple Filters (Excel Form Scenario)

🔹 3. Dynamic Column Selection (User selects column in Excel)

🔹 4. Dynamic Sorting (Excel Dropdown → Sort Option)

🔹 5. Dynamic Date Filter (Very Practical 🔥)

🔹 6. Best Practice Version (sp_executesql ✅)

🔹 7. Dynamic Table (Multiple Excel Sheets)

🎓 Classroom Teaching Idea (Very Powerful 💡)

⚠️ Important Concept to Teach

🚀 If you want next level

For providence Hospi

Queries without alias

🧠 Q1. Find total revenue, total bills, and average bill amount per patient

🧠 Q2. Find patient procedure stats (count, avg cost, max cost)

🧠 Q3. Combine billing + procedures (very powerful)

🧠 Q4. Pending amount + number of pending bills per patient

🧠 Q5. Report analysis per patient (with severity)

🧠 Q6. Patients with no billing (LEFT JOIN case)

🧠 Q7. Full report with date filter (VERY IMPORTANT)

SQL SERVER — COMPLETE ROADMAP

SQL SERVER — LEARNING LEVELS

THE BIG PICTURE

MOST IMPORTANT LEARNING ORDER

No comments:

Post a Comment