CODEWITHSUNDEEP
sql-servert-sql
vidooo
vidooo

Reputation: 135

SQL how to check prefix in cell with two prefixes

I am learning SQL and I have a table where there are certain cells with two prefixes like this :

example1(cell) : R:8days; U:5$;
example2(cell) : R:8days;
example3(cell) : U:5$;

I want to check for that U:5$ after the first prefix, as I know how to check for prefix R:8days;. So I need to check for U:5$ and then make a new column in table.

My code looks like this:

;with cte as (
select
Employer, AmountPayd, AmountPayd as Payd
from data
where TipeOfTransaction like 'Offline Prepaid%' AND Note like '%R:8%' **HERE I WANT TO CHECK FOR PREFIX NR2. 'U:5$' AND MAKE NEW COLUMN FOR WHICH EMPLOYER HAS U:5$ NOTE.**
)
select
Employer,
     [4.00] = ISNULL([4.00],0)
    ,[5.00] = ISNULL([5.00],0)
    ,[9.00] = ISNULL([9.00],0)
    ,[10.00] = ISNULL([10.00],0)
    ,[15.00] = ISNULL([15.00],0)
    ,[Sum] =ISNULL([4.00],0) + ISNULL([5.00],0) + ISNULL([9.00],0) + ISNULL([10.00],0) + ISNULL([15.00],0)
    from cte
    pivot (
    sum(AmountPayd) for Payd in ([4.00],[5.00],[9.00], [10.00], [15.00], [20.00]))pvt;

Upvotes: 7

Views: 1287

Answers (2)

Orchidoris
Orchidoris

Reputation: 188

(I've corrected some mistakes in column names and refactored the query.)

I believe, you are looking for something like this:

WITH CTE AS (
    SELECT
        Employer,
        AmountPaid,
        Paid
    FROM Data
    LEFT JOIN (VALUES (4, 5, 9, 10, 15, 20)) V(Paid)
        ON Note LIKE '%U:' + CAST(Paid AS VARCHAR(10)) + '$;%'
    WHERE TypeOfTransaction LIKE 'Offline Prepaid%'
        AND Note LIKE '%R:8%'
)
SELECT
    Employer,
    ISNULL([4.00], 0) AS [4.00],
    ISNULL([5.00], 0) AS [5.00],
    ISNULL([9.00], 0) AS [9.00],
    ISNULL([10.00], 0) AS [10.00],
    ISNULL([15.00], 0) AS [15.00],
    ISNULL([4.00] + [5.00] + [9.00] + [10.00] + [15.00], 0) AS Sum
FROM CTE
PIVOT( SUM(AmountPaid)
    FOR Paid IN ([4.00],[5.00],[9.00], [10.00], [15.00], [20.00])) PVT;

Upvotes: 1

IVNSTN
IVNSTN

Reputation: 9299

This?

select
  Employer, AmountPayd, AmountPayd as Payd,
  CASE WHEN Note like '%R:8%;%U:5$%' THEN 'U:5' END U5Note
from data
where TipeOfTransaction like 'Offline Prepaid%' AND Note like '%R:8%'

Upvotes: 1

Related Questions