TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Devendra Kumar
NA
500
163.5k
Query to find the monthly payment in SQL Server
Mar 3 2021 7:43 AM
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
Reply
Answers (
2
)
Improve the performance of an Query.
how to calculate SQL standard edition licensing If I have 1 vm with 7