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 in already existing table which already have 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
AS
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)
AS
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)
AS
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:


    

  1. 
Static query

    2. 

  2. 
Simple dynamic (EXEC)

    3. 

  3. 
Concatenation

    4. 

  4. 
Problems (quotes, injection)

    5. 

  5. 
Then introduce sp_executesql

    6. 





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;


https://www.hostize.com/s/9lbEy6wypE
https://www.hostize.com/s/CixEU42PRe   All Medical





No comments:

Post a Comment

Subscribe to: Posts (Atom)