 # SQL Server

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"

what is E1 (L), E10 (L) and E11 (L)??

this might give you a start :-

``````;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  
,Quantity_2	 * Price2 as 
,Quantity_3	 * Price3 as 
from cte2
) select

*,
++ as total

from cte3``````

hi

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
``````

Wow! This's great. Let me try it. Thank you very much

Thanks a lot. Let me try it

You are right.
Data not in a properly machine-readable format. I'm only new in the company.
Thanks

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
;
``````

Here's what the output looks like...

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.

You are not using delimitedsplit2k?!?!?!
dbo.DelimitedSplit8K

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...

1 Like

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.

1 Like

Sir Jeff, by the way dbo.DelimitedSplit8K is not friendly to non Roman scripts

I don't know what you mean by "non Roman scripts". Please explain.

N'በየጊዜው,እያደረግን,ያለነው,እድገት' because varchar in function..I suspect

So have you tried the DelimitedSplitN4K function from the same article?

1 Like

Thanks,
Jeff

This's great! Thanks a lot