SQLTeam.com | Weblogs | Forums

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"
Thanks in advance

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  [1]
				,Quantity_2	 * Price2 as [2]
				,Quantity_3	 * Price3 as [3]
				from cte2
) select

*,
[1]+[2]+[3] 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

@harishgg1,

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