August 4, 2014 at 1:58 pm
Hello,
I'm trying to divide two values from separate rows. Each row is a separate UNION statement.
2014-08-03 00:00:00.000 N KBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000 N KBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000 N KBB (N) - Without Leads 917 UNION (C)
In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)
What would be the best way to accomplish this?
Thank you for your answers.
August 4, 2014 at 2:25 pm
PFlorenzano-641896 (8/4/2014)
Hello,I'm trying to divide two values from separate rows. Each row is a separate UNION statement.
2014-08-03 00:00:00.000 N KBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000 N KBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000 N KBB (N) - Without Leads 917 UNION (C)
In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)
What would be the best way to accomplish this?
Thank you for your answers.
Hey there!
So, first, it'd be great if you could have a read of this (http://qa.sqlservercentral.com/articles/Best+Practices/61537/)[/url], then set up some sample data, DDL and expected results based on your sample data. Otherwise, we're guessing at data-types, where your data comes from etc etc. You say that your data is from a UNION query, are all of the UNIONs querying the same tables but with a slightly different WHERE clause? If so, it's likely that we can help you to improve the query and make this whole job a little simpler in the process 🙂
For now, an initial (and pretty poor) way to do what you've requested would be something like this: -
WITH CTE ([WHATEVER], [ANOTHER THING], [SOMETHING ELSE], [A NUMBER THIS TIME!]) AS
(
SELECT '2014-08-03 00:00:00.000','N','KBB (N) - Total Offers',1218 UNION
SELECT '2014-08-03 00:00:00.000','N','KBB (N) - With Lead',301 UNION
SELECT '2014-08-03 00:00:00.000','N','KBB (N) - Without Leads',917
)
SELECT [WHATEVER], [ANOTHER THING], [SOMETHING ELSE], [A NUMBER THIS TIME!],
([A NUMBER THIS TIME!]*1.0)/[Total Offers] AS [DIVIDE]
FROM CTE a
OUTER APPLY (SELECT [A NUMBER THIS TIME!]
FROM CTE b
WHERE a.[SOMETHING ELSE] = 'KBB (N) - With Lead'
AND b.[SOMETHING ELSE] = 'KBB (N) - Total Offers'
) ca([Total Offers]);
Which returns: -
WHATEVER ANOTHER THING SOMETHING ELSE A NUMBER THIS TIME! DIVIDE
----------------------- ------------- ----------------------- ------------------- ---------------------------------------
2014-08-03 00:00:00.000 N KBB (N) - Total Offers 1218 NULL
2014-08-03 00:00:00.000 N KBB (N) - With Lead 301 0.247126436781
2014-08-03 00:00:00.000 N KBB (N) - Without Leads 917 NULL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply