Comparing column values

Transact-SQL
1

Is there a way to compare column 1 and column 3 after the 'ordered as' ? the values are from different tables.

2

well of course, using patindex and substring. But really the table design looks bad. WHy not a column called OrderedAs?

3

Of course the table design is bad, here is the sql to retrieve this

SELECT

CIT.Name,
  CIT.Description,
     
  O.SummaryLine

from CV3Order o,
cv3ordertask ot,
cv3catalogitemNAME CIT
where o.guid = ot.OrderGUID
--and o.guid=3803024100680
AND CIT.ORDERMASTERITEMGUID =OT.OrdercatalogmasteritemGUID
AND CIT.Description LIKE 'ACETamin%'
AND cit.IsPrimary=0 and IsForOrder=1
AND O.TouchedBy='ba8908ee_mdmonster'

4

I want to compare the Name and the string after 'Ordered as:' and if it is same, want to select that row, otherwise not

5

Had to guess at which table/column to compare to, but this should be very close at least:

SELECT
  CIT.Name,
  CIT.Description,
  O.SummaryLine
  
from CV3Order o,
cv3ordertask ot,
cv3catalogitemNAME CIT

cross apply (
    select CHARINDEX('Ordered as', O.SummaryLine) AS o_ord_as,
        CHARINDEX('Ordered as', CIT.SummaryLine) AS CIT_ord_as
) as assign_alias_names

where o.guid = ot.OrderGUID
--and o.guid=3803024100680
AND CIT.ORDERMASTERITEMGUID =OT.OrdercatalogmasteritemGUID
AND CIT.Description LIKE 'ACETamin%'
AND cit.IsPrimary=0 and IsForOrder=1
AND O.TouchedBy='ba8908ee_mdmonster'
AND CASE WHEN o_ord_as = 0 
         THEN O.SummaryLine 
         ELSE SUBSTRING(O.SummaryLine, CHARINDEX(')', O.SummaryLine, o_ord_as) + 1, 8000) 
         END =
    CASE WHEN CIT_ord_as = 0 
         THEN CIT.SummaryLine 
         ELSE SUBSTRING(CIT.SummaryLine, CHARINDEX(')', CIT.SummaryLine, CIT_ord_as) + 1, 8000) 
         END
1 Like
6

Thank you , getting the following error though,
The multi-part identifier "O.SummaryLine" could not be bound.

the cit.name has to be compared against substring(o.SummaryLine, 14,13) The trick is I cannot use a specific number like 13, as the CIT.Name is varchar(125)

7

Odd, since you used "O.SummaryLine" in your SELECT list (!). Not sure what is causing that. But I've adjusted the code for the correct CIT column, CIT.Name (rather than CIT.SummaryLine).

SELECT
  CIT.Name,
  CIT.Description,
  O.SummaryLine
  
from CV3Order O,
cv3ordertask ot,
cv3catalogitemNAME CIT

cross apply (
    select CHARINDEX('Ordered as', O.SummaryLine) AS o_ord_as,
        CHARINDEX('Ordered as', CIT.Name) AS CIT_ord_as
) as assign_alias_names

where O.guid = ot.OrderGUID
--and O.guid=3803024100680
AND CIT.ORDERMASTERITEMGUID =OT.OrdercatalogmasteritemGUID
AND CIT.Description LIKE 'ACETamin%'
AND cit.IsPrimary=0 and IsForOrder=1
AND O.TouchedBy='ba8908ee_mdmonster'
AND CASE WHEN o_ord_as = 0 
         THEN O.SummaryLine 
         ELSE SUBSTRING(O.SummaryLine, CHARINDEX(')', O.SummaryLine, o_ord_as) + 1, 8000) 
         END =
    CASE WHEN CIT_ord_as = 0 
         THEN CIT.Name 
         ELSE SUBSTRING(CIT.Name, CHARINDEX(')', CIT.Name, CIT_ord_as) + 1, 8000) 
         END
1 Like
8

Getting the same error

cross apply (
select CHARINDEX('Ordered as', O.SummaryLine) AS o_ord_as,

The multi-part identifier "O.SummaryLine" could not be bound.

On the other hand, I am just trying to compare the below way cant use number 13, as it picks up medications only with characters 13.

SELECT
CIT.NAME AS SYNONYM, CIT.Description,O.SummaryLine

from CV3Order o,
cv3ordertask ot,
cv3catalogitemNAME CIT
where o.guid = ot.OrderGUID
AND CIT.ORDERMASTERITEMGUID =OT.OrdercatalogmasteritemGUID
and cit.name like substring(o.SummaryLine, 14,13)
AND cit.IsPrimary=0 and IsForOrder=1
order by o.CreatedWhen desc

Pasted image782×141 4.28 KB

9

I've answered this in your other thread.

10

Scott, I have executed the following

SELECT
CIT.Name,
CIT.Description,
O.SummaryLine

from CV3Order o
inner join cv3ordertask ot on o.guid = ot.OrderGUID
inner join cv3catalogitemNAME CIT on CIT.ORDERMASTERITEMGUID = OT.OrdercatalogmasteritemGUID

cross apply (
select CHARINDEX('Ordered as', O.SummaryLine) AS o_ord_as,
CHARINDEX('Ordered as', CIT.Name) AS cit_ord_as
) as assign_alias_names

where

cit.IsPrimary=0 and IsForOrder=1
and o.Name like 'acetamin%'

AND CASE WHEN o_ord_as = 0
THEN O.SummaryLine
ELSE SUBSTRING(O.SummaryLine, CHARINDEX(')', O.SummaryLine, o_ord_as) + 1, 8000)
END =
CASE WHEN CIT_ord_as = 0
THEN o.SummaryLine
ELSE SUBSTRING(CIT.Name, CHARINDEX(')', CIT.Name, CIT_ord_as) + 1, 8000)
END
order by o.TouchedWhen desc

but still gives me the duplicates,

Pasted image986×161 5.78 KB

11

"Still" gives you duplicates?? The issue was comparing the latter part of the strings, nothing before about "duplicates". At any rate, I don't see true duplicates there. Do you want to see only one row per Description and SummaryLine description? If so, you need to GROUP BY on those and use MIN/MAX on the Name.

12

I cannot see the full summary line, also I have given one order, which is TYLENOL, but gives me atasol also

13

I am thinking of another way,

please see the query, and I have posted the results, the O.GUID 3806240100680 is giving me 2 rows.
How can I get the one row from the table OT where the OT.GUID= 3806240100680 and if I change the query it gives me nothing.

SELECT
O.TouchedBy,
O.CreatedWhen,
O.GUID,
O.ClientGUID,
O.ClientGUID,
O.Name 'order item',
O.SummaryLine,
CIT.NAME AS SYNONYM,
CIT.Description
FROM CV3Order O
INNER JOIN CV3OrderTask OT
ON O.ClientGUID = OT.ClientGUID AND O.ChartGUID=OT.ChartGUID AND O.GUID=OT.OrderGUID

INNER JOIN CV3CatalogItemName CIT
ON CIT.OrderMasterItemGUID = O.OrdercatalogmasteritemGUID
AND CIT.IsPrimary=0 AND IsForOrder=1
AND O.GUID = 3806240100680
ORDER BY O.CreatedWhen DESC

Pasted image1174×61 4.25 KB