Hi, can someone help please? I have a list like the one below that got multiple cost for different activities within a row and would like to get the sum of the total cost of those multiple activities using SQL Server.
create table #Temp (id int, Rate nvarchar(500), Value_Expected int)
insert into #Temp values
('1', '1 x E1 (L),As above (E1) but xyz (inflated rate),5.77 6 x E10(L),As above (E10) but xyz (vvv rate),1.235 1 x E11(L),As above (E11) but xyz (vvv rate),0.93',14.11),
('2', '1 x E1,"xyz, new spur, xyz/water/xyz (if required), xyz (xyz)",4.67 1 x E10,Day rate per hour,1 ',5.67),
('3', '1 x E1,"z, z, z gas/water/z (if required), z (z)",4.67 1 x E6b,z (z)11 - 20m,2 1 x E6c,z (z) 21 - 30m,3 1 x E7b,xyz (z)11 - 20m,2 1 x E8,z,0.5 2 x E10,z,1 1 x E11,z,0.75',6.34)
select * from #Temp
drop table #Temp
Looking at row 1 for instance the expected total result should be 14.11 after addition and multiplication.
"1 x E1 (L) - 5.77
6 x E10(L) - 1.235
1 x E11(L) - 0.93"
Thanks in advance
;with cte as (
SELECT
Rate,
CASE
WHEN delim_1 > 0 THEN LEFT(Rate, delim_1 +3)
ELSE Rate
END AS first_column,
CASE
WHEN delim_2 > 0 THEN SUBSTRING(Rate, delim_1 + 3, delim_2 - delim_1 +3)
WHEN delim_1 > 0 THEN SUBSTRING(Rate, delim_1 + 3, 500)
ELSE ''
END AS Second_column,
CASE
WHEN delim_3 > 0 THEN SUBSTRING(Rate, delim_2 + 3, delim_3 - delim_2 + 3)
WHEN delim_2 > 0 THEN SUBSTRING(Rate, delim_2 + 3, 500)
ELSE ''
END AS Third_column
FROM #Temp
CROSS APPLY (SELECT CHARINDEX('.', Rate) AS delim_1) AS ca1
CROSS APPLY (SELECT CASE WHEN delim_1 = 0 THEN 0 ELSE CHARINDEX('.', Rate, delim_1 + 3) END AS delim_2) AS ca2
CROSS APPLY (SELECT CASE WHEN delim_2 = 0 THEN 0 ELSE CHARINDEX('.', Rate, delim_2 + 3) END AS delim_3) AS ca3
CROSS APPLY (SELECT CASE WHEN delim_3 = 0 THEN 0 ELSE CHARINDEX('.', Rate, delim_2 + 3) END AS delim_4) AS ca4
),cte2 as (
select
left(substring(first_column,charindex('x',first_column)-2, charindex('x',first_column)),1) Quantity_1
--,convert(float, left(first_column,1)) Quantity_1
,convert(float,substring(first_column,charindex('.',first_column)-1,4)) as Price1
,left(substring(Second_column,charindex('x',Second_column)-2, charindex('x',Second_column)),1) Quantity_2
,convert(float,substring(Second_column,charindex('.',Second_column)-1,4)) as Price2
,left(substring(Third_column,charindex('x',Third_column)-2, charindex('x',Third_column)),1) Quantity_3
,convert(float,substring(Third_column,charindex('.',Third_column)-1,4)) as Price3
,Third_column
from
cte
),cte3 as (
select
Quantity_1
,case when price1 not like '%.%' then null else price1 end as price1
,Quantity_2
,case when Price2 not like '%.%' then null else Price2 end as Price2
,Quantity_3
,case when Price3 not like '%.%' then null else Price3 end as Price3
,Quantity_1 * Price1 as [1]
,Quantity_2 * Price2 as [2]
,Quantity_3 * Price3 as [3]
from cte2
) select
*,
[1]+[2]+[3] as total
from cte3
i was able to use user defined function to split the string into small pieces
from this we have to ...find before x one and identify and multiply value ..
seems complicated ..
simple cursor .. character by character LOOP of string .. with logic ... if then else ..
will do it
i think ...
please click arrow to the left for UDF and SQL ...
drop function [dbo].[fn_split_string_to_column]
go
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0
SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END
WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)
INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]
SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END
RETURN
END
GO
;with cte as
(
select rate ,value
from Temp cross apply [dbo].[fn_split_string_to_column](rate,' ')
)
select a.Rate,b.value from cte a cross apply [dbo].[fn_split_string_to_column](value,',') b
please click arrow to the left for Drop CREATE Data
drop table Temp
go
create table Temp (id int, Rate nvarchar(500), Value_Expected int)
go
insert into Temp values
('1', '1 x E1 (L),As above (E1) but xyz (inflated rate),5.77 6 x E10(L),As above (E10) but xyz (vvv rate),1.235 1 x E11(L),As above (E11) but xyz (vvv rate),0.93',14.11),
('2', '1 x E1,"xyz, new spur, xyz/water/xyz (if required), xyz (xyz)",4.67 1 x E10,Day rate per hour,1 ',5.67),
('3', '1 x E1,"z, z, z gas/water/z (if required), z (z)",4.67 1 x E6b,z (z)11 - 20m,2 1 x E6c,z (z) 21 - 30m,3 1 x E7b,xyz (z)11 - 20m,2 1 x E8,z,0.5 2 x E10,z,1 1 x E11,z,0.75',6.34)
go
select * from Temp
go
Here's the cursor .. going though character by character ...
declare @string varchar(500) = '' , @i int , @Cursor as CURSOR;
set @Cursor = cursor for select rate from temp
open @Cursor; FETCH NEXT FROM @Cursor INTO @string
WHILE @@FETCH_STATUS = 0
BEGIN
select @i = 0
while @i < len(@string)
begin
select @i = @i + 1
select substring(@string, @i, 1)
end
FETCH NEXT FROM @Cursor INTO @string
END
CLOSE @Cursor;
DEALLOCATE @Cursor;
Thought I'd go whole-hawg on this one for a little fun.
First, here's the data from the OP's original post with one little change to test for quantities that had more than 1 digit.
create table #Temp (id int, Rate nvarchar(500), Value_Expected DECIMAL(9,4))
insert into #Temp values
('1', '1 x E1 (L),As above (E1) but xyz (inflated rate),5.77 6 x E10(L),As above (E10) but xyz (vvv rate),1.235 1 x E11(L),As above (E11) but xyz (vvv rate),0.93',14.11),
('2', '1 x E1,"xyz, new spur, xyz/water/xyz (if required), xyz (xyz)",4.67 1 x E10,Day rate per hour,1 ',5.67),
('3', '1 x E1,"z, z, z gas/water/z (if required), z (z)",4.67 1 x E6b,z (z)11 - 20m,2 13 x E6c,z (z) 21 - 30m,3 1 x E7b,xyz (z)11 - 20m,2 1 x E8,z,0.5 2 x E10,z,1 1 x E11,z,0.75',6.34)
-- ^^ Changed above to 13
;
The following code converts the train-wreck of data found in the Rate column to a "manifest" of separate items by ID, breaks out the quantity and unit price, calculates the extended price and subtotals of the extended price by ID as well as a grand total. Of course, since some form of splitting was involved, I used a function that takes the place of a Tally table to avoid all the Scalar functions, Cursors, While loops, and other forms of RBAR.
WITH
cteRevRate AS (--===== Reverse the string so we can find the only common pattern in the string.
SELECT ID, RevRate = REVERSE(RTRIM(Rate)), LenRate = LEN(RTRIM(Rate)) FROM #Temp
)
,cteDelimit AS (--===== Figure out where to split the reversed string based on a common pattern
SELECT ID
,Delimit = t.N+4+ABS(CHARINDEX(' ',SUBSTRING(RevRate,t.N+4,10))-1)
,RevRate
--,t.n,Np4=t.n+4,FindSpace=CHARINDEX(' ',SUBSTRING(RevRate,t.N+4,10))-1
FROM cteRevRate
CROSS APPLY fnTally(1,LenRate) t
WHERE SUBSTRING(RevRate,t.N,5) LIKE 'E x [0-9]' --The Common Pattern
)
,cteStrParse AS (--==== Split the reversed string into pieces based on the pattern.
SELECT ID
,String = RTRIM(SUBSTRING(RevRate
,LAG(Delimit,1,1) OVER (PARTITION BY ID ORDER BY Delimit)
,Delimit-LAG(Delimit,1,1) OVER (PARTITION BY ID ORDER BY Delimit)+1))
FROM cteDelimit
)
,cteDryStr AS (--===== DRY out the reverse of the string
SELECT ID, String, Details = REVERSE(String)
FROM cteStrParse
)
,cteDryCalcs AS (--===== Parse and DRY out the quanity and unit prices
SELECT ID, Details
,Quantity = CONVERT(DECIMAL(9,2),LEFT(Details,CHARINDEX('x',Details)-1))
,UnitPrice = CONVERT(DECIMAL(9,2),REVERSE(LEFT(String,CHARINDEX(',',String)-1)))
FROM cteDryStr
)
--===== Create the final output including subtotals and a grandtotal for the ExtendedPrice and other labeling.
SELECT ID = CASE WHEN GROUPING(ID) = 0 THEN ID ELSE 0 END
,Details = CASE
WHEN GROUPING(UnitPrice) = 0 THEN Details
WHEN GROUPING(ID) = 1 THEN '** Grand Total'
WHEN GROUPING(Details) = 1 THEN '* Sub-Total for ID'
END
,Quantity = CASE WHEN GROUPING(UnitPrice) = 0 THEN CONVERT(VARCHAR(10),Quantity) ELSE '----------' END
,UnitPrice = CASE WHEN GROUPING(UnitPrice) = 0 THEN CONVERT(VARCHAR(10),UnitPrice) ELSE '----------' END
,ExtendedPrice = SUM(CONVERT(DECIMAL(9,2),Quantity*UnitPrice))
FROM cteDryCalcs
GROUP BY ID, Details, Quantity, UnitPrice WITH ROLLUP
HAVING GROUPING(UnitPrice) = 0 --The HAVING limits the output to only detail lines, subtotals, and the grand total
OR GROUPING(Details) = 1
;
Last but not least, here's where to get the script for the fnTally function. If you don't have one of those in your tool box, you're missing the proverbial "Swiss Army Knife of SQL" to avoid cursors, while loops, Recursive CTEs, and other forms of RBAR.
Here's the original article I wrote about the Tally Table.
Just as a suggestion, you should look up "DelimitedSplit8k" and "DelimitedSplitN4k" so that you can avoid slow splitter functions in the future. Any function that has BEGIN or uses a Recursive CTE in it is going to be slow.
Here's the link for the original article on the subject.
A good friend of mine made a mode for people that use SQL Server 2012 or better... it's now almost as fast as a CLR. Here's the link...
No. And its DelimitedSplit8K. DelimitedSplit8K splits on a single character and there's no need to bust up each string at the word level in this case especially since we'd just have to put it back together again.but that would prevent us from making the itemized list that I did.
