Query to find the monthly payment in SQL Server

Hello everyone

I am working on sim management tool, that keeps history of the sim cards, there were manage by the below points (These are not on production computer so any modifications can be done)

1. SimMaster- It contains all the sim related information

2. Active and Deactive History - Keeps history of the sim when it was activated and deactivated (based on that payment will be calculated)

3. TarrifPlan History - It contains the tarrif plan associated with the mobile number

4. Payment Table - needs to be calculated

Output required- Which mobile number is active from x to y dates and what plans were there (based on these 2 & 3, the payment for that month needs to be calculated

Here's the table and with sample data (there were several columns as well but as a sample I only shared the column to work with)

Database Digram

Table Structure

/****** Object: Table [dbo].[PaymentTable] Script Date: 03/03/2021 12:46:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PaymentTable](
[ID] [bigint] NOT NULL,
[SimInventoryID] [bigint] NOT NULL,
[PaymentFrom] [date] NULL,
[PaymentTo] [date] NULL,
[Amount] [decimal](18, 3) NULL,
CONSTRAINT [PK_PaymentTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PlanActivateHistory] Script Date: 03/03/2021 12:46:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PlanActivateHistory](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[SimInventoryID] [bigint] NOT NULL,
[TarrifPlanMasterID] [bigint] NOT NULL,
[PlanActivatedDate] [date] NULL,
[PlanDeactivatedDate] [date] NULL,
CONSTRAINT [PK_PlanActivateHistory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SimActivateHistory] Script Date: 03/03/2021 12:46:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SimActivateHistory](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[SimInventoryID] [bigint] NOT NULL,
[ActivatedDate] [date] NULL,
[DeactivatedDate] [date] NULL,
CONSTRAINT [PK_SimActivateHistory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SimInventoryMaster] Script Date: 03/03/2021 12:46:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SimInventoryMaster](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[MobileNumber] [bigint] NOT NULL,
CONSTRAINT [PK_SimInventoryMaster] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TarrifPlanMaster] Script Date: 03/03/2021 12:46:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TarrifPlanMaster](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[PlanName] [nvarchar](50) NULL,
[BasicPrice] [decimal](18, 3) NULL,
[GST] [decimal](18, 3) NULL,
[Amount] [decimal](18, 3) NULL,
CONSTRAINT [PK_TarrifPlanMaster] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[PlanActivateHistory] ON
GO
INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (1, 1, 1, CAST(0x11420B00 AS Date), CAST(0x13420B00 AS Date))
GO
INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (2, 1, 2, CAST(0x14420B00 AS Date), CAST(0x18420B00 AS Date))
GO
INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (3, 1, 3, CAST(0x1A420B00 AS Date), CAST(0x1B420B00 AS Date))
GO
INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (4, 2, 1, CAST(0xFE410B00 AS Date), NULL)
GO
SET IDENTITY_INSERT [dbo].[PlanActivateHistory] OFF
GO
SET IDENTITY_INSERT [dbo].[SimActivateHistory] ON
GO
INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (2, 1, CAST(0x12420B00 AS Date), CAST(0x14420B00 AS Date))
GO
INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (3, 2, CAST(0xFE410B00 AS Date), NULL)
GO
INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (4, 1, CAST(0x17420B00 AS Date), CAST(0x18420B00 AS Date))
GO
INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (5, 1, CAST(0x1A420B00 AS Date), CAST(0x1B420B00 AS Date))
GO
SET IDENTITY_INSERT [dbo].[SimActivateHistory] OFF
GO
SET IDENTITY_INSERT [dbo].[SimInventoryMaster] ON
GO
INSERT [dbo].[SimInventoryMaster] ([ID], [MobileNumber]) VALUES (1, 9999912398)
GO
INSERT [dbo].[SimInventoryMaster] ([ID], [MobileNumber]) VALUES (2, 9999923199)
GO
INSERT [dbo].[SimInventoryMaster] ([ID], [MobileNumber]) VALUES (3, 9999910100)
GO
SET IDENTITY_INSERT [dbo].[SimInventoryMaster] OFF
GO
SET IDENTITY_INSERT [dbo].[TarrifPlanMaster] ON
GO
INSERT [dbo].[TarrifPlanMaster] ([ID], [PlanName], [BasicPrice], [GST], [Amount]) VALUES (1, N'plan45', CAST(45.000 AS Decimal(18, 3)), CAST(0.000 AS Decimal(18, 3)), CAST(45.000 AS Decimal(18, 3)))
GO
INSERT [dbo].[TarrifPlanMaster] ([ID], [PlanName], [BasicPrice], [GST], [Amount]) VALUES (2, N'plan35', CAST(35.000 AS Decimal(18, 3)), CAST(0.000 AS Decimal(18, 3)), CAST(35.000 AS Decimal(18, 3)))
GO
INSERT [dbo].[TarrifPlanMaster] ([ID], [PlanName], [BasicPrice], [GST], [Amount]) VALUES (3, N'plan25', CAST(25.000 AS Decimal(18, 3)), CAST(0.000 AS Decimal(18, 3)), CAST(25.000 AS Decimal(18, 3)))
GO
SET IDENTITY_INSERT [dbo].[TarrifPlanMaster] OFF
GO
ALTER TABLE [dbo].[PlanActivateHistory] WITH CHECK ADD CONSTRAINT [FK_PlanActivateHistory_SimInventoryMaster] FOREIGN KEY([SimInventoryID])
REFERENCES [dbo].[SimInventoryMaster] ([ID])
GO
ALTER TABLE [dbo].[PlanActivateHistory] CHECK CONSTRAINT [FK_PlanActivateHistory_SimInventoryMaster]
GO
ALTER TABLE [dbo].[PlanActivateHistory] WITH CHECK ADD CONSTRAINT [FK_PlanActivateHistory_TarrifPlanMaster] FOREIGN KEY([TarrifPlanMasterID])
REFERENCES [dbo].[TarrifPlanMaster] ([ID])
GO
ALTER TABLE [dbo].[PlanActivateHistory] CHECK CONSTRAINT [FK_PlanActivateHistory_TarrifPlanMaster]
GO
ALTER TABLE [dbo].[SimActivateHistory] WITH CHECK ADD CONSTRAINT [FK_SimActivateHistory_SimInventoryMaster] FOREIGN KEY([SimInventoryID])
REFERENCES [dbo].[SimInventoryMaster] ([ID])
GO
ALTER TABLE [dbo].[SimActivateHistory] CHECK CONSTRAINT [FK_SimActivateHistory_SimInventoryMaster]
GO

Here's the table with data

Suppose input was given 01-01-2021 to 31-01-2021

Calculation

Can any expert help me to solve this

Thanks

Devendra

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Devendra Kumar5y ago
Thanks Amira for the reply and sorry for late reply due to covid. as I couldnot able to edit question. I am sharing you the detailed and fresh calculation (ignore the calculation given on question)

Please check and help me to solve this
Thanks
Devendra
0
Amira Bedhiafi5y ago
Can you explain this row in the calculation table:

Because when I check the [PlanActivateHistory] table, I get the following :

0