September 12, 2014 at 7:47 am
Hi all,
I doubt this is possible, but thought I'd ask anyway 🙂
I am working with a database linked to some software and trying to write a query to produce some data. Due to the way data is stored in the database I'm having to do some conversions and then create new columns using these converted columns.
So to start with I insert data into a temporary table. Convert the frequency into months (it's current recorded as the number of charges per year), and calculate the number of months since the last charge due to this not being recorded in the database.
SELECT ChargeRef, PolicyRef, LastChargedDate,
CASE
WHEN Freq = 1 THEN 12
WHEN Freq = 2 THEN 6
WHEN Freq = 4 THEN 3
WHEN Freq = 12 THEN 1
END AS [FrequencyMonths],
DATEDIFF(month, LastChargedDate, GETDATE()) AS Difference_Months
INTO TMP_Charges
FROM Charges
Then using the temporary table as a reference, I return a list of the charges where they are now due, making sure to not return charges that have expired.
SELECT Customers.Surname,
Customers.Forenames,
Plans.PolicyNumber AS [Policy Number],
Plans.CurrentValue AS [Fund Value £],
Products.ProductName AS [Product Name],
Charges.ChargeDescription AS [Charge Description],
CASE
WHEN Charges.BasedOn IS NULL THEN 'Fixed Amount'
WHEN Charges.BasedOn = 1 THEN 'Premium %'
WHEN Charges.BasedOn = 2 THEN 'Plan Value %'
ELSE '' END AS [Based On Type],
CASE
WHEN Charges.BasedOn IS NULL THEN '£' + CAST(Charges.Amount AS VARCHAR(20))
WHEN Charges.BasedOn IN (1,2) THEN CAST(Charges.Amount AS VARCHAR(20)) + '%'
ELSE '' END AS [Based On Amount],
CASE
WHEN Charges.Freq = 1 THEN 'Anually'
WHEN Charges.Freq = 2 THEN 'Half Yearly'
WHEN Charges.Freq = 4 THEN 'Quarterly'
WHEN Charges.Freq = 12 THEN 'Monthly'
END AS [Payment Frequency],
CONVERT(VARCHAR(10),Charges.LastChargedDate,103) AS [Last Charged],
CONVERT(VARCHAR(10),
DATEADD(mm, TMP_Charges.FrequencyMonths, Charges.LastChargedDate),103) AS [Next Charge Due],
CASE
WHEN Charges.BasedOn = 2 THEN Charges.Amount*Plans.CurrentValue/100
ELSE Charges.Amount
END AS [Amount Owed £]
FROM Customers
LEFT JOIN Plans ON Plans.ClientRef = Customers.ClientRef
LEFT JOIN Charges ON Charges.PolicyRef = Plans.PolicyRef
LEFT JOIN Products ON Plans.ProductRef = Products.ProductRef
INNER JOIN TMP_Charges ON TMP_Charges.ChargeRef = Charges.ChargeRef
WHERE TMP_Charges.Difference_Months >= TMP_Charges.FrequencyMonths
AND (Charges.EndDate IS NULL OR Charges.EndDate > GETDATE())
I then get rid of the temporary table.
DROP TABLE TMP_Charges
This works a treat but I guess my question is, "Is there any way to do this all in one SQL statement without the need to use a temporary table?"
Many thanks in advance.
Regards
Steve
Regards
Steve
September 12, 2014 at 8:14 am
Should be (and btw, that's not a temp table. It's a permanent table)
WITH TempCharges AS (
SELECT ChargeRef, PolicyRef, LastChargedDate,
CASE
WHEN Freq = 1 THEN 12
WHEN Freq = 2 THEN 6
WHEN Freq = 4 THEN 3
WHEN Freq = 12 THEN 1
END AS [FrequencyMonths],
DATEDIFF(month, LastChargedDate, GETDATE()) AS Difference_Months
FROM Charges
)
SELECT Customers.Surname,
Customers.Forenames,
Plans.PolicyNumber AS [Policy Number],
Plans.CurrentValue AS [Fund Value £],
Products.ProductName AS [Product Name],
Charges.ChargeDescription AS [Charge Description],
CASE
WHEN Charges.BasedOn IS NULL THEN 'Fixed Amount'
WHEN Charges.BasedOn = 1 THEN 'Premium %'
WHEN Charges.BasedOn = 2 THEN 'Plan Value %'
ELSE '' END AS [Based On Type],
CASE
WHEN Charges.BasedOn IS NULL THEN '£' + CAST(Charges.Amount AS VARCHAR(20))
WHEN Charges.BasedOn IN (1,2) THEN CAST(Charges.Amount AS VARCHAR(20)) + '%'
ELSE '' END AS [Based On Amount],
CASE
WHEN Charges.Freq = 1 THEN 'Anually'
WHEN Charges.Freq = 2 THEN 'Half Yearly'
WHEN Charges.Freq = 4 THEN 'Quarterly'
WHEN Charges.Freq = 12 THEN 'Monthly'
END AS [Payment Frequency],
CONVERT(VARCHAR(10),Charges.LastChargedDate,103) AS [Last Charged],
CONVERT(VARCHAR(10),
DATEADD(mm, TMP_Charges.FrequencyMonths, Charges.LastChargedDate),103) AS [Next Charge Due],
CASE
WHEN Charges.BasedOn = 2 THEN Charges.Amount*Plans.CurrentValue/100
ELSE Charges.Amount
END AS [Amount Owed £]
FROM Customers
LEFT JOIN Plans ON Plans.ClientRef = Customers.ClientRef
LEFT JOIN Charges ON Charges.PolicyRef = Plans.PolicyRef
LEFT JOIN Products ON Plans.ProductRef = Products.ProductRef
INNER JOIN TempCharges ON TempCharges.ChargeRef = Charges.ChargeRef
WHERE TempCharges.Difference_Months >= TempCharges.FrequencyMonths
AND (Charges.EndDate IS NULL OR Charges.EndDate > GETDATE());
That's a CTE, Common Table Expression. Make sure that the statement before the WITH (if any) is terminated with a ;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2014 at 8:24 am
Hi Steve,
Overall; there is a whole lot of wrong with that code. The data you're trying to get can be had with a much simpler solution. You need to check the forum posting rules; supply some scripts to create the tables, populate some sample data, etc. You'll have a much better response from the community in doing so.
Glancing at what you've provided, there is no reason your table (let's call it just that; as you've not actually created a temp table) can't be a sub-query in the larger query, something like this:
SELECT
Customers.Surname
, Customers.Forenames
, Plans.PolicyNumber AS [Policy Number]
, Plans.CurrentValue AS [Fund Value £]
, Products.ProductName AS [Product Name]
, Charges.ChargeDescription AS [Charge Description]
, CASE
WHEN Charges.BasedOn IS NULL THEN 'Fixed Amount'
WHEN Charges.BasedOn = 1 THEN 'Premium %'
WHEN Charges.BasedOn = 2 THEN 'Plan Value %'
ELSE ''
END AS [Based On Type]
, CASE
WHEN Charges.BasedOn IS NULL THEN '£' + CAST(Charges.Amount AS VARCHAR(20))
WHEN Charges.BasedOn IN (1,2) THEN CAST(Charges.Amount AS VARCHAR(20)) + '%'
ELSE ''
END AS [Based On Amount]
, CASE
WHEN Charges.Freq = 1 THEN 'Anually'
WHEN Charges.Freq = 2 THEN 'Half Yearly'
WHEN Charges.Freq = 4 THEN 'Quarterly'
WHEN Charges.Freq = 12 THEN 'Monthly'
END AS [Payment Frequency]
, CONVERT(VARCHAR(10), Charges.LastChargedDate, 103) AS [Last Charged]
, CONVERT(VARCHAR(10), DATEADD(mm, TMP_Charges.FrequencyMonths, Charges.LastChargedDate), 103) AS [Next Charge Due]
, CASE
WHEN Charges.BasedOn = 2 THEN Charges.Amount * Plans.CurrentValue / 100
ELSE Charges.Amount
END AS [Amount Owed £]
FROM
Customers
LEFT JOIN
Plans ON Plans.ClientRef = Customers.ClientRef
LEFT JOIN
Charges ON Charges.PolicyRef = Plans.PolicyRef
LEFT JOIN
Products ON Plans.ProductRef = Products.ProductRef
INNER JOIN
(
SELECT
ChargeRef
, PolicyRef
, LastChargedDate
, CASE
WHEN Freq = 1 THEN 12
WHEN Freq = 2 THEN 6
WHEN Freq = 4 THEN 3
WHEN Freq = 12 THEN 1
END AS [FrequencyMonths]
, DATEDIFF(month, LastChargedDate, GETDATE()) AS Difference_Months
FROM
Charges
WHERE
DATEDIFF(month, LastChargedDate, GETDATE()) >= FrequencyMonths
) TMP_Charges ON TMP_Charges.ChargeRef = Charges.ChargeRef
WHERE
(
Charges.EndDate IS NULL
OR
Charges.EndDate > GETDATE()
)
;
I would recommend you re-post your question with the proper resources and you'll get a better answer.
Cheers!
September 12, 2014 at 8:29 am
Hi Steve,
I think that you can move the definition of TMP_Charges into join (in your main query), like this:
SELECT Customers.Surname,
Customers.Forenames,
Plans.PolicyNumber AS [Policy Number],
Plans.CurrentValue AS [Fund Value £],
Products.ProductName AS [Product Name],
Charges.ChargeDescription AS [Charge Description],
CASE
WHEN Charges.BasedOn IS NULL THEN 'Fixed Amount'
WHEN Charges.BasedOn = 1 THEN 'Premium %'
WHEN Charges.BasedOn = 2 THEN 'Plan Value %'
ELSE '' END AS [Based On Type],
CASE
WHEN Charges.BasedOn IS NULL THEN '£' + CAST(Charges.Amount AS VARCHAR(20))
WHEN Charges.BasedOn IN (1,2) THEN CAST(Charges.Amount AS VARCHAR(20)) + '%'
ELSE '' END AS [Based On Amount],
CASE
WHEN Charges.Freq = 1 THEN 'Anually'
WHEN Charges.Freq = 2 THEN 'Half Yearly'
WHEN Charges.Freq = 4 THEN 'Quarterly'
WHEN Charges.Freq = 12 THEN 'Monthly'
END AS [Payment Frequency],
CONVERT(VARCHAR(10),Charges.LastChargedDate,103) AS [Last Charged],
CONVERT(VARCHAR(10),
DATEADD(mm, TMP_Charges.FrequencyMonths, Charges.LastChargedDate),103) AS [Next Charge Due],
CASE
WHEN Charges.BasedOn = 2 THEN Charges.Amount*Plans.CurrentValue/100
ELSE Charges.Amount
END AS [Amount Owed £]
FROM Customers
LEFT JOIN Plans
ON Plans.ClientRef = Customers.ClientRef
LEFT JOIN Charges
ON Charges.PolicyRef = Plans.PolicyRef
LEFT JOIN Products
ON Plans.ProductRef = Products.ProductRef
INNER JOIN
(SELECT ChargeRef, PolicyRef, LastChargedDate,
CASE
WHEN Freq = 1 THEN 12
WHEN Freq = 2 THEN 6
WHEN Freq = 4 THEN 3
WHEN Freq = 12 THEN 1
END AS [FrequencyMonths],
DATEDIFF(month, LastChargedDate, GETDATE()) AS Difference_Months
FROM Charges
) AS TMP_Charges
ON TMP_Charges.ChargeRef = Charges.ChargeRef
WHERE TMP_Charges.Difference_Months >= TMP_Charges.FrequencyMonths
AND (Charges.EndDate IS NULL OR Charges.EndDate > GETDATE())
Regards,
Rafal
September 12, 2014 at 3:56 pm
+1 to Gail (GilaMonster)
The CTE should do the trick for you. As was said, calling the table Tmp doesn't make it temporary. Using an INTO #Tmp .... is temporary. The way it was defined is a permanent table that was used and then dropped. Depending on the volume of data, you can bloat your db with unnecessary growth.
September 13, 2014 at 12:16 am
Quick thoughts, there is quite some room for improvements such as using a single variable instead of the getdate function, a case statement can be replaced by simple arithmetic "12 / Charges.Freq AS [FrequencyMonths]" and the conditionals/filtering. If you post DDL for the tables and sample data we can take it from there.
😎
September 15, 2014 at 2:50 am
Thanks all. The CTE and rbednarek's solution both worked.
Regards
Steve
September 16, 2014 at 1:36 am
CTEs are just so much more elegant and readable and easier to test each step - I've ended up with 6 in one query to build up data and do running totals!
September 16, 2014 at 2:19 am
I agree. I must confess I'd never heard of them before, but I'm pretty new to SQL development and admin.
In this case, the SQL statement needed to run with a VB6 based application and not through SQL Management Studio. The application didn't support the CTE so I had to use the sub select in the end.
Regards
Steve
September 16, 2014 at 8:10 am
smw147 (9/15/2014)
Thanks all. The CTE and rbednarek's solution both worked.
The question now is, what did the changes do to performance and resource usage? The reason I ask is that a common technique for fixing performance and resource usage problems associated with many "all in one queries" is to split up the query and put the essential "driver rows" into a temp table and include that in the join.
To be sure, "Set Based" coding is the way to go but it doesn't mean "All in one query".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply