Post
Ask Question
Devendra Kumar

Devendra Kumar

  • NA
  • 500
  • 157.9k

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
 
  1. /****** Object:  Table [dbo].[PaymentTable]    Script Date: 03/03/2021 12:46:26 PM ******/  
  2. SET ANSI_NULLS ON  
  3. GO  
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6. CREATE TABLE [dbo].[PaymentTable](  
  7.     [ID] [bigint] NOT NULL,  
  8.     [SimInventoryID] [bigint] NOT NULL,  
  9.     [PaymentFrom] [date] NULL,  
  10.     [PaymentTo] [date] NULL,  
  11.     [Amount] [decimal](18, 3) NULL,  
  12.  CONSTRAINT [PK_PaymentTable] PRIMARY KEY CLUSTERED   
  13. (  
  14.     [ID] ASC  
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  16. ) ON [PRIMARY]  
  17.   
  18. GO  
  19. /****** Object:  Table [dbo].[PlanActivateHistory]    Script Date: 03/03/2021 12:46:26 PM ******/  
  20. SET ANSI_NULLS ON  
  21. GO  
  22. SET QUOTED_IDENTIFIER ON  
  23. GO  
  24. CREATE TABLE [dbo].[PlanActivateHistory](  
  25.     [ID] [bigint] IDENTITY(1,1) NOT NULL,  
  26.     [SimInventoryID] [bigint] NOT NULL,  
  27.     [TarrifPlanMasterID] [bigint] NOT NULL,  
  28.     [PlanActivatedDate] [date] NULL,  
  29.     [PlanDeactivatedDate] [date] NULL,  
  30.  CONSTRAINT [PK_PlanActivateHistory] PRIMARY KEY CLUSTERED   
  31. (  
  32.     [ID] ASC  
  33. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  34. ) ON [PRIMARY]  
  35.   
  36. GO  
  37. /****** Object:  Table [dbo].[SimActivateHistory]    Script Date: 03/03/2021 12:46:26 PM ******/  
  38. SET ANSI_NULLS ON  
  39. GO  
  40. SET QUOTED_IDENTIFIER ON  
  41. GO  
  42. CREATE TABLE [dbo].[SimActivateHistory](  
  43.     [ID] [bigint] IDENTITY(1,1) NOT NULL,  
  44.     [SimInventoryID] [bigint] NOT NULL,  
  45.     [ActivatedDate] [date] NULL,  
  46.     [DeactivatedDate] [date] NULL,  
  47.  CONSTRAINT [PK_SimActivateHistory] PRIMARY KEY CLUSTERED   
  48. (  
  49.     [ID] ASC  
  50. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  51. ) ON [PRIMARY]  
  52.   
  53. GO  
  54. /****** Object:  Table [dbo].[SimInventoryMaster]    Script Date: 03/03/2021 12:46:26 PM ******/  
  55. SET ANSI_NULLS ON  
  56. GO  
  57. SET QUOTED_IDENTIFIER ON  
  58. GO  
  59. CREATE TABLE [dbo].[SimInventoryMaster](  
  60.     [ID] [bigint] IDENTITY(1,1) NOT NULL,  
  61.     [MobileNumber] [bigint] NOT NULL,  
  62.  CONSTRAINT [PK_SimInventoryMaster] PRIMARY KEY CLUSTERED   
  63. (  
  64.     [ID] ASC  
  65. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  66. ) ON [PRIMARY]  
  67.   
  68. GO  
  69. /****** Object:  Table [dbo].[TarrifPlanMaster]    Script Date: 03/03/2021 12:46:26 PM ******/  
  70. SET ANSI_NULLS ON  
  71. GO  
  72. SET QUOTED_IDENTIFIER ON  
  73. GO  
  74. CREATE TABLE [dbo].[TarrifPlanMaster](  
  75.     [ID] [bigint] IDENTITY(1,1) NOT NULL,  
  76.     [PlanName] [nvarchar](50) NULL,  
  77.     [BasicPrice] [decimal](18, 3) NULL,  
  78.     [GST] [decimal](18, 3) NULL,  
  79.     [Amount] [decimal](18, 3) NULL,  
  80.  CONSTRAINT [PK_TarrifPlanMaster] PRIMARY KEY CLUSTERED   
  81. (  
  82.     [ID] ASC  
  83. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  84. ) ON [PRIMARY]  
  85.   
  86. GO  
  87. SET IDENTITY_INSERT [dbo].[PlanActivateHistory] ON   
  88.   
  89. GO  
  90. INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (1, 1, 1, CAST(0x11420B00 AS Date), CAST(0x13420B00 AS Date))  
  91. GO  
  92. INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (2, 1, 2, CAST(0x14420B00 AS Date), CAST(0x18420B00 AS Date))  
  93. GO  
  94. INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (3, 1, 3, CAST(0x1A420B00 AS Date), CAST(0x1B420B00 AS Date))  
  95. GO  
  96. INSERT [dbo].[PlanActivateHistory] ([ID], [SimInventoryID], [TarrifPlanMasterID], [PlanActivatedDate], [PlanDeactivatedDate]) VALUES (4, 2, 1, CAST(0xFE410B00 AS Date), NULL)  
  97. GO  
  98. SET IDENTITY_INSERT [dbo].[PlanActivateHistory] OFF  
  99. GO  
  100. SET IDENTITY_INSERT [dbo].[SimActivateHistory] ON   
  101.   
  102. GO  
  103. INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (2, 1, CAST(0x12420B00 AS Date), CAST(0x14420B00 AS Date))  
  104. GO  
  105. INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (3, 2, CAST(0xFE410B00 AS Date), NULL)  
  106. GO  
  107. INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (4, 1, CAST(0x17420B00 AS Date), CAST(0x18420B00 AS Date))  
  108. GO  
  109. INSERT [dbo].[SimActivateHistory] ([ID], [SimInventoryID], [ActivatedDate], [DeactivatedDate]) VALUES (5, 1, CAST(0x1A420B00 AS Date), CAST(0x1B420B00 AS Date))  
  110. GO  
  111. SET IDENTITY_INSERT [dbo].[SimActivateHistory] OFF  
  112. GO  
  113. SET IDENTITY_INSERT [dbo].[SimInventoryMaster] ON   
  114.   
  115. GO  
  116. INSERT [dbo].[SimInventoryMaster] ([ID], [MobileNumber]) VALUES (1, 9999912398)  
  117. GO  
  118. INSERT [dbo].[SimInventoryMaster] ([ID], [MobileNumber]) VALUES (2, 9999923199)  
  119. GO  
  120. INSERT [dbo].[SimInventoryMaster] ([ID], [MobileNumber]) VALUES (3, 9999910100)  
  121. GO  
  122. SET IDENTITY_INSERT [dbo].[SimInventoryMaster] OFF  
  123. GO  
  124. SET IDENTITY_INSERT [dbo].[TarrifPlanMaster] ON   
  125.   
  126. GO  
  127. 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)))  
  128. GO  
  129. 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)))  
  130. GO  
  131. 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)))  
  132. GO  
  133. SET IDENTITY_INSERT [dbo].[TarrifPlanMaster] OFF  
  134. GO  
  135. ALTER TABLE [dbo].[PlanActivateHistory]  WITH CHECK ADD  CONSTRAINT [FK_PlanActivateHistory_SimInventoryMaster] FOREIGN KEY([SimInventoryID])  
  136. REFERENCES [dbo].[SimInventoryMaster] ([ID])  
  137. GO  
  138. ALTER TABLE [dbo].[PlanActivateHistory] CHECK CONSTRAINT [FK_PlanActivateHistory_SimInventoryMaster]  
  139. GO  
  140. ALTER TABLE [dbo].[PlanActivateHistory]  WITH CHECK ADD  CONSTRAINT [FK_PlanActivateHistory_TarrifPlanMaster] FOREIGN KEY([TarrifPlanMasterID])  
  141. REFERENCES [dbo].[TarrifPlanMaster] ([ID])  
  142. GO  
  143. ALTER TABLE [dbo].[PlanActivateHistory] CHECK CONSTRAINT [FK_PlanActivateHistory_TarrifPlanMaster]  
  144. GO  
  145. ALTER TABLE [dbo].[SimActivateHistory]  WITH CHECK ADD  CONSTRAINT [FK_SimActivateHistory_SimInventoryMaster] FOREIGN KEY([SimInventoryID])  
  146. REFERENCES [dbo].[SimInventoryMaster] ([ID])  
  147. GO  
  148. ALTER TABLE [dbo].[SimActivateHistory] CHECK CONSTRAINT [FK_SimActivateHistory_SimInventoryMaster]  
  149. 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 
 
 
 

Answers (2)