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

🚀 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
)




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;