May 2, 2009 at 1:16 pm
Hello...
I'm hoping that someone can help me calculate a monthly billing total. I'm writing a report where I need to account for a minimum price in the total bill. Not all of the records have a minimum and some of the records the minimum has been reached. I'm trying the following...LastMonthsTotal * Price + Minimum (if minimum exists) and if the Minimum exists and has already been reached...I don't want to add it to the TotalMonthlyBill.
Here's a sample of the data...
IF OBJECT_ID('TempDB..#MonthlyBilling','U') IS NOT NULL
DROP TABLE #MonthlyBilling
CREATE TABLE [dbo].[#MonthlyBilling](
[Recordid] [int] identity NOT NULL,
[Partner] varchar (100) NOT NULL,
[LastMonthsTotal] [int] NULL,
[Price] [money] NULL,
[Minimum] [money] NULL,
[TotalMonthlyBill] [money] NULL,
CONSTRAINT [pk_MonthlyBilling] PRIMARY KEY CLUSTERED
(
[Recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT #MonthlyBilling ON
INSERT INTO #MonthlyBilling
(Recordid, Partner, LastMonthsTotal, Price, Minimum,
TotalMonthlyBill)
SELECT '101','Some Company 1','1000','0.50','175.00',NULL UNION ALL
SELECT '102','Some Company 2','1000','1.00','175.00',NULL UNION ALL
SELECT '103','Some Company 3','1000','1.00','0.00',NULL UNION ALL
SELECT '104','Some Company 4',null,'1.00','175.00',NULL UNION ALL
SELECT '105','Some Company 5',null,'0.00','100.00',NULL UNION ALL
SELECT '106','Some Company 6',null,'0.00','100.00',NULL UNION ALL
SELECT '107','Some Company 7','50','.50','50.00',NULL
SET IDENTITY_INSERT #MonthlyBilling OFF
Thanks in advance!
May 2, 2009 at 5:33 pm
I've almost got it figured out. Here's what I came up with. Please note recordid 107...that's where I'm just not getting it.
DECLARE @recordid varchar(50), @sql2 varchar(1000)
DECLARE crs CURSOR FOR
SELECT recordid FROM #MonthlyBilling
OPEN crs
FETCH NEXT FROM crs INTO @recordid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql2 ='
UPDATE #MonthlyBilling
SET TotalMonthlyBill =
(CASE
WHEN LastMonthsTotal * Price + Minimum > Minimum
THEN LastMonthsTotal * Price
WHEN LastMonthsTotal * Price + Minimum < Minimum
THEN Minimum
WHEN LastMonthsTotal = 0 or LastMonthsTotal IS NULL
THEN Minimum
END)
WHERE Recordid = '''+@recordid+''''
EXEC (@sql2)
FETCH NEXT FROM crs INTO @recordid
END
CLOSE crs
DEALLOCATE crs
SELECT * FROM #MonthlyBilling
May 2, 2009 at 6:53 pm
dave b (5/2/2009)
Hello...I'm hoping that someone can help me calculate a monthly billing total. I'm writing a report where I need to account for a minimum price in the total bill. Not all of the records have a minimum and some of the records the minimum has been reached. I'm trying the following...LastMonthsTotal * Price + Minimum (if minimum exists) and if the Minimum exists and has already been reached...I don't want to add it to the TotalMonthlyBill.
Here's a sample of the data...
IF OBJECT_ID('TempDB..#MonthlyBilling','U') IS NOT NULLDROP TABLE #MonthlyBilling
CREATE TABLE [dbo].[#MonthlyBilling](
[Recordid] [int] identity NOT NULL,
[Partner] varchar (100) NOT NULL,
[LastMonthsTotal] [int] NULL,
[Price] [money] NULL,
[Minimum] [money] NULL,
[TotalMonthlyBill] [money] NULL,
CONSTRAINT [pk_MonthlyBilling] PRIMARY KEY CLUSTERED
(
[Recordid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT #MonthlyBilling ON
INSERT INTO #MonthlyBilling
(Recordid, Partner, LastMonthsTotal, Price, Minimum,
TotalMonthlyBill)
SELECT '101','Some Company 1','1000','0.50','175.00',NULL UNION ALL
SELECT '102','Some Company 2','1000','1.00','175.00',NULL UNION ALL
SELECT '103','Some Company 3','1000','1.00','0.00',NULL UNION ALL
SELECT '104','Some Company 4',null,'1.00','175.00',NULL UNION ALL
SELECT '105','Some Company 5',null,'0.00','100.00',NULL UNION ALL
SELECT '106','Some Company 6',null,'0.00','100.00',NULL UNION ALL
SELECT '107','Some Company 7','50','.50','50.00',NULL
SET IDENTITY_INSERT #MonthlyBilling OFF
Thanks in advance!
Based on the data provided, what are the expected results?
May 2, 2009 at 7:17 pm
Thanks for the reply Lynn!
I'm trying to update the [TotalMonthlyBill] with the [Minimum] if the [Minimum] [Minimum]...I only want the results of (LastMonthsTotal * Price). Make sense?
If you run that cursor you'll see that recordid 107 is only calculating the (LastMonthsTotal * Price) as $25.00...but the [Minimum] for this record is $50.00. So the [TotalMonthlyBill] for recordid 107 should be $50.00.
Am I just missing something simple? The rest of the records are correct.
Dave
May 2, 2009 at 7:48 pm
Your criteria is wrong. You shouldn't add minimum to the total and the price because in cases there there is anything >0, it will always exceed the minimum which will not give you what you want.
Also, there is absolutely no need for a cursor to do this. In SQL Server, cursors and most While Loops take a huge toll on performance and code readability.
The following code should do what you want it to do and includes the test data you were very kind to include (very well done there, by the way, thanks)...
[font="Courier New"]--===== Conditionally drop the test table
IF OBJECT_ID('TempDB..#MonthlyBilling','U') IS NOT NULL
DROP TABLE #MonthlyBilling
--===== Create the test table
CREATE TABLE dbo.#MonthlyBilling
(
Recordid INT IDENTITY(1,1),
Partner VARCHAR(100) NOT NULL,
LastMonthsTotal INT NULL,
Price MONEY NULL,
Minimum MONEY NULL,
TotalMonthlyBill MONEY NULL,
CONSTRAINT pk_MonthlyBilling
PRIMARY KEY CLUSTERED (Recordid ASC)
)
--===== Populate the test table with test data
SET IDENTITY_INSERT #MonthlyBilling ON
INSERT INTO #MonthlyBilling
(Recordid, Partner, LastMonthsTotal, Price, Minimum)
SELECT '101','Some Company 1','1000','0.50','175.00' UNION ALL
SELECT '102','Some Company 2','1000','1.00','175.00' UNION ALL
SELECT '103','Some Company 3','1000','1.00','0.00' UNION ALL
SELECT '104','Some Company 4',NULL ,'1.00','175.00' UNION ALL
SELECT '105','Some Company 5',NULL ,'0.00','100.00' UNION ALL
SELECT '106','Some Company 6',NULL ,'0.00','100.00' UNION ALL
SELECT '107','Some Company 7','50' ,'.50' ,'50.00'
SET IDENTITY_INSERT #MonthlyBilling OFF
--===== Calculate and save the total monthly bill using the "minimum"
UPDATE #MonthlyBilling
SET TotalMonthlyBill = CASE
WHEN ISNULL(LastMonthsTotal,0) * Price >= Minimum
THEN LastMonthsTotal * Price
WHEN ISNULL(LastMonthsTotal,0) * Price < Minimum
THEN Minimum
END
--===== Display the results
SELECT * FROM #MonthlyBilling
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2009 at 8:03 pm
Jeff...Thank you so much! This works great.
BTW...I just started reading your post about the "Tally" table.
I am now officially on a quest to eliminate my cursors and loops. 🙂
May 2, 2009 at 8:27 pm
dave b (5/2/2009)
Jeff...Thank you so much! This works great.BTW...I just started reading your post about the "Tally" table.
I am now officially on a quest to eliminate my cursors and loops. 🙂
And you, Sir, just made my day. Thank you for the great feedback.
Anytime you post a test table and data population script like you did for this post, you'll find people who will trip over each other trying to help you... especially if you need help eliminating cursors. I wish more people would take the time you did because it made my life a lot easier because I could focus on helping you. Thanks for taking the time to post correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply