November 27, 2014 at 2:57 pm
The following works in query if I specify one student (PlanDetailUID) when running query.
If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries.
I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?
----------------------------------------
CREATE TABLE #AWP (
[TransDate] [datetime] NULL,
[Description] [varchar](1000) NULL,
[Amount] [float] NULL,
[TotalDueNow] [float] NULL,
[NotYetDue] [float] NULL,
[CurrentDue] [float] NULL,
[_1stMonth] [decimal](15, 2) NULL,
[_2ndMonth] [decimal](15, 2) NULL,
[_3rdMonth] [decimal](15, 2) NULL,
[_Over3Months] [decimal](15, 2) NULL
) ON [PRIMARY]
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2011-07-13 00:00:00.000' AS DateTime), N'Fin Application Fee', 300, 300, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(300.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-03-16 00:00:00.000' AS DateTime), N'Fin DEPOSIT', 13500, 13500, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(13500.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-03-16 00:00:00.000' AS DateTime), N'Fin Material', 4800, 4800, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(4800.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-04-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-05-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-06-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-07-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-08-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-09-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-10-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-11-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2012-12-01 00:00:00.000' AS DateTime), N'Payment_Plan', 3720, 3720, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(3720.00 AS Decimal(15, 2)))
INSERT #AWP ([TransDate], [Description], [Amount], [TotalDueNow], [NotYetDue], [CurrentDue], [_1stMonth], [_2ndMonth], [_3rdMonth], [_Over3Months]) VALUES (CAST(N'2014-07-01 00:00:00.000' AS DateTime), N'Payment_Plan', 24860, 24860, 0, 0, CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(0.00 AS Decimal(15, 2)), CAST(24860.00 AS Decimal(15, 2)))
-----------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[Billing](
[StudentUID] [int] NOT NULL,
[BillingID] [int] NOT NULL,
[TransDate] [nvarchar](11) NULL,
[Amount] [Money] NOT NULL,
[Description] [nvarchar](300) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 635924, N'27 Jan 2012', 300.0000, N'Fee Credit - Application Fee 002333')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 781239, N'14 Feb 2012', 10000.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 896014, N'01 Mar 2012', 4000.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 886334, N'20 Mar 2012', 4300.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 949832, N'30 May 2012', 7000.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 976221, N'02 Jul 2012', 4000.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 1006084, N'03 Aug 2012', 4000.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 1013751, N'15 Aug 2012', 4000.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 1034360, N'03 Sep 2012', 3000.0000, N'Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 1058926, N'17 Oct 2012', 4400.0000, N'Payment - DIRECT DEPOSIT 11000047298 - 2')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 1074917, N'15 Nov 2012', 3500.0000, N'11000047298 - 2012 Payment - DIRECT DEPO')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 1472899, N'31 May 2013', 3580.0000, N'11047298 - 2012 Payment - DIRECT DEPOSIT')
GO
INSERT [dbo].[Billing] ([StudentUID], [BillingID], [TransDate], [Amount], [Description]) VALUES (134857, 2098408, N'01 Sep 2014', 20000.0000, N'163880 13/03 Payment - DIRECT DEPOSIT')
GO
-----------------------------------------------------------------------------------------------------------
Declare @StudentUID Nvarchar(MAX)
Declare @BillingPaid Decimal(15,2)
Declare @FTotalDueNow Decimal(15,2)
Declare @FNotYetDue Decimal(15,2)
Declare @FCurrentDue Decimal(15,2)
Declare @F_1stMonth Decimal(15,2)
Declare @F_2ndMonth Decimal(15,2)
Declare @F_3rdMonth Decimal(15,2)
Declare @F_Over3Months Decimal(15,2)
PART 1:
Create Table #AAP
(
PlanDetailUID int,
TransDate datetime,
[Description] varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Amount float,
TotalDueNow float,
NotYetDue float,
CurrentDue float,
_1stMonth Decimal(15,2),
_2ndMonth Decimal(15,2),
_3rdMonth Decimal(15,2),
_Over3Months Decimal(15,2)
)
Insert into #AAP
Select Distinct
PlanDetailUID
,TransDate
,[Description]
,Amount
,SUM(TotalDueNow) as TotalDueNow
,SUM(NotYetDue) as NotYetDue
,SUM(CurrentDue) as CurrentDue
,SUM(_1stMonth) as _1stMonth
,SUM(_2ndMonth) as _2ndMonth
,SUM(_3rdMonth) as _3rdMonth
,SUM(_Over3Months) as _Over3Months
from #AWP
WHERE #AWP.PlanDetailUID in (@StudentUID) ------- I use multiple StudentUID's
Group by
PlanDetailUID
,TransDate
,[Description]
,Amount
-------------------------------------------------------------------
-------------------------------------------------------------------
PART 2:
SET @BillingPaid =
(
Select
case when sum(Amount) IS null OR sum(Amount) = ''
then
0
else
sum(Amount)
end
from [dbo].[Billing]
)
if @BillingPaid < 0
Begin
SET @BillingPaid = (@BillingPaid * -1)
End
else
Begin
SET @BillingPaid = (@BillingPaid * 1)
End
SET @FTotalDueNow = (Select sum(TotalDueNow) from #AAP)
SET @FNotYetDue = (Select sum(NotYetDue) from #AAP)
SET @FCurrentDue = (Select sum(CurrentDue) from #AAP)
SET @F_1stMonth = (Select sum(_1stMonth) from #AAP)
SET @F_2ndMonth = (Select sum(_2ndMonth) from #AAP)
SET @F_3rdMonth = (Select sum(_3rdMonth) from #AAP)
SET @F_Over3Months = (Select sum(_Over3Months) from #AAP)
if @BillingPaid > 0.00
Begin
--Step 1
SET @FTotalDueNow = @FTotalDueNow - @BillingPaid
--Step 2
--Test oldest aging the lowest
if @F_Over3Months > 0.00
Begin
Select @F_Over3Months = @F_Over3Months - @BillingPaid
SET @BillingPaid = @F_Over3Months
if @F_Over3Months < 0
Begin
SET @F_Over3Months = 0.00
End
if @F_Over3Months > 0
Begin
SET @BillingPaid = 0
End
End
--First (Oldest)
--Step 3
if @F_3rdMonth > 0.00
Begin
if @BillingPaid < 0
Begin
SET @BillingPaid = (@BillingPaid * -1)
End
else
Begin
SET @BillingPaid = (@BillingPaid * 1)
End
SET @F_3rdMonth = (@F_3rdMonth) - (@BillingPaid)
SET @BillingPaid = @F_3rdMonth
if @F_3rdMonth < 0
Begin
SET @F_3rdMonth = 0.00
End
if @F_3rdMonth > 0
Begin
SET @BillingPaid = 0
End
End
--Step 4
--Next
if @F_2ndMonth > 0.00 and @F_3rdMonth <= 0.00
Begin
if @BillingPaid < 0
Begin
SET @BillingPaid = (@BillingPaid * -1)
End
else
Begin
SET @BillingPaid = (@BillingPaid * 1)
End
SET @F_2ndMonth = @F_2ndMonth - @BillingPaid
SET @BillingPaid = @F_2ndMonth
if @F_2ndMonth < 0
Begin
SET @F_2ndMonth = 0.00
End
if @F_2ndMonth > 0
Begin
SET @BillingPaid = 0
End
End
--Step 5
--Next
if @F_1stMonth > 0.00 and @F_2ndMonth <= 0.00
Begin
if @BillingPaid < 0
Begin
SET @BillingPaid = (@BillingPaid * -1)
End
else
Begin
SET @BillingPaid = (@BillingPaid * 1)
End
SET @F_1stMonth = @F_1stMonth - @BillingPaid
SET @BillingPaid = @F_1stMonth
if @F_1stMonth < 0
Begin
SET @F_1stMonth = 0.00
End
if @F_1stMonth > 0
Begin
SET @BillingPaid = 0
End
End
--Step 6
--Next
if @FCurrentDue > 0.00 and @F_1stMonth <= 0.00
Begin
if @BillingPaid < 0
Begin
SET @BillingPaid = (@BillingPaid * -1)
End
else
Begin
SET @BillingPaid = (@BillingPaid * 1)
End
SET @FCurrentDue = @FCurrentDue - @BillingPaid
SET @BillingPaid = @FCurrentDue
if @FCurrentDue < 0
Begin
SET @FCurrentDue = 0.00
End
if @FCurrentDue > 0
Begin
SET @BillingPaid = 0
End
End
--Step 7
--Next
if @FNotYetDue > 0.00 and @FCurrentDue <= 0.00
Begin
if @BillingPaid < 0
Begin
SET @BillingPaid = (@BillingPaid * -1)
End
else
Begin
SET @BillingPaid = (@BillingPaid * 1)
End
SET @FNotYetDue = @FNotYetDue - @BillingPaid
SET @BillingPaid = @FNotYetDue
End
End
November 27, 2014 at 4:07 pm
That's a lot to process without some sample data. You're doing procedural programming in SQL which is optimized for declarative programming. You seem to have more code than what you have shown. I suggest that you give us the complete scenario to be able to help you.
December 3, 2014 at 11:28 pm
Hi Luis, added data script to my post.
December 4, 2014 at 9:46 am
Could you post the definition of the function Adv_Returntranscationsbytype or what should it do?
December 4, 2014 at 4:15 pm
Hi Luis, updated the original script. Can you assist ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply