CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2014
prasanth
prasanth

Reputation: 483

Fastest way to calculate the count of records in child table based on a condition

Table Schema:

CREATE TABLE [dbo].[VMaster](
    [VID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [VName] [varchar](30) NOT NULL
)
GO

CREATE TABLE [dbo].[TblMaster](
    [SID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [VID] [int] NOT NULL,
    [CreatedDate] [datetime] default (getdate()) NOT NULL,
    [CharToAdd] [varchar](10) NOT NULL,
    [Start] [int]  NOT NULL,
    [End] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblDetails](
    [DetailsID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [SID] [int] NOT NULL,
    [Sno] [int] NOT NULL,
    [ConcatenatedText] [varchar](20) NOT NULL,
    [isIssued] [bit] default (0) NOT NULL,
    [isUsed] [bit] default (0) NOT NULL
)
GO

ALTER TABLE [dbo].[TblMaster]  WITH CHECK ADD CONSTRAINT [fk_SI_id] FOREIGN KEY([VID])
REFERENCES [dbo].[VMaster] ([VID])
GO

ALTER TABLE [dbo].[TblMaster] CHECK CONSTRAINT [fk_SI_id]
GO

Sample data:

Insert into dbo.VMaster Values ('A1')
Insert into dbo.VMaster Values ('A2')
GO

Insert into dbo.TblMaster Values (1,default, 'ABC', 1, 5)
Insert into dbo.TblMaster Values (1,default, 'XYZ', 10, 20)
Insert into dbo.TblMaster Values (2,default, 'P1', 10, 15)
GO

Insert into dbo.TblDetails values(1, 1, 'ABC1', 1,0)
Insert into dbo.TblDetails values(1, 2, 'ABC2', 1,0)
Insert into dbo.TblDetails values(1, 3, 'ABC3', 1,0)
Insert into dbo.TblDetails values(1, 4, 'ABC4', 0,0)
Insert into dbo.TblDetails values(1, 5, 'ABC5', 0,0)
Insert into dbo.TblDetails values(2, 10, 'XYZ10', 1,0)
Insert into dbo.TblDetails values(2, 11, 'XYZ11', 1,0)
Insert into dbo.TblDetails values(2, 12, 'XYZ12', 1,0)
Insert into dbo.TblDetails values(2, 13, 'XYZ13', 1,0)
Insert into dbo.TblDetails values(2, 14, 'XYZ14', 1,0)
Insert into dbo.TblDetails values(2, 15, 'XYZ15', 0,0)
Insert into dbo.TblDetails values(2, 16, 'XYZ16', 0,0)
Insert into dbo.TblDetails values(2, 17, 'XYZ17', 0,0)
Insert into dbo.TblDetails values(2, 18, 'XYZ18', 0,0)
Insert into dbo.TblDetails values(2, 19, 'XYZ19', 0,0)
Insert into dbo.TblDetails values(2, 20, 'XYZ20', 0,0)
Insert into dbo.TblDetails values(3, 10, 'P110', 1,0)
Insert into dbo.TblDetails values(3, 11, 'P111', 1,0)
Insert into dbo.TblDetails values(3, 12, 'P112', 1,0)
Insert into dbo.TblDetails values(3, 13, 'P113', 1,0)
Insert into dbo.TblDetails values(3, 14, 'P114', 1,0)
Insert into dbo.TblDetails values(3, 15, 'P115', 1,0)
    GO

Expected Output:

enter image description here

Query which I have as of now:

SELECT 
    TM.VID, VM.VName, TM.CharToAdd, TM.Start, TM.[End],
    (SELECT COUNT(*) FROM dbo.TblDetails TD where TD.SID=TM.SID and isIssued = 0 ) Balance
FROM 
dbo.TblMaster TM, dbo.VMaster VM
Where VM.VID = TM.VID

I am trying to calculate total matching records in TblDetails for each VID whose isIssued value is 0.

  1. User could select a period (date range) for which they want to see this output. For that TblMaster.CreatedDate is what we have to use.
  2. Also user can select a specific VID and like to see the result only for that.
  3. Just in case if date range or VID is not provided then this has to work against the whole table! Per month there might be 500000 records in TblDetails and we will hold 12 months of data.

Upvotes: 3

Views: 155

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

For the summary of the whole table your query looks OK.

It is easy to add an optional filtering on top of that.

If parameter is NULL, it will be ignored. If parameter is not NULL, it will limit the result set:

DECLARE @ParamStart datetime = '2016-01-01';
DECLARE @ParamEnd datetime = '2017-01-01';
DECLARE @ParamVID int = NULL;

SELECT 
    TM.VID, VM.VName, TM.CharToAdd, TM.Start, TM.[End],
    (SELECT COUNT(*) FROM dbo.TblDetails TD where TD.SID=TM.SID and isIssued = 0) Balance
FROM 
    dbo.TblMaster AS TM
    INNER JOIN dbo.VMaster AS VM ON VM.VID = TM.VID
WHERE
    (TM.CreatedDate >= @ParamStart OR @ParamStart IS NULL)
    AND (TM.CreatedDate < @ParamEnd OR @ParamEnd IS NULL)
    AND (TM.VID = @ParamVID OR @ParamVID IS NULL)
ORDER BY TM.SID
OPTION(RECOMPILE);

To make it efficient, it should help to have this index:

CREATE NONCLUSTERED INDEX [IX_] ON [dbo].[TblDetails]
(
    [isIssued] ASC,
    [SID] ASC
)

And also indexes on dbo.TblMaster.CreatedDate and dbo.TblMaster.VID.

Upvotes: 3

Related Questions