October 18, 2012 at 2:31 am
Hi Team,
Small change in my requirement.
having 5 columns with 5 columns expiry date, each column will have a expiry date,
and there is a final column called summary, which includes
col1 : expiry date
col2 : expiry date
col3 : expiry date
col4 : expiry date
col5 : expiry date
*** Searching for string should be happen in Summary column, not in all columns
Please help
October 18, 2012 at 3:10 am
More than happy to help if you follow this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 18, 2012 at 3:46 am
Hi Eugene,
I agree with what ur saying,
here am giving some more details,
--
;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5)
AS
(SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL
SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL
SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL
SELECT 104, 'D', 'C', 'D', 'D','D' UNION ALL
SELECT 105,'AA','A','A','A','A'UNION ALL
SELECT 106,'B','C','A','AA','D')
SELECT Record_id, Newcol
FROM Mysample
CROSS APPLY (
SELECT Newcol = CASE WHEN MAX(Newcol) = MIN(Newcol) THEN MAX(Newcol) ELSE NULL END
FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol)
) d
WHERE Newcol IS NOT NULL
above query i got from SSC team - Really thanks.
now my requirement is i've to search in final column only, final column is the concatenation of 10 columns
(col1 + col1_expiry_date + Col1 + col2_expiry_date......+col5_expiry_date)
finally i want the search should be happen from the final_column
Please help...
October 18, 2012 at 3:59 am
Sorry mate, your sample is very obscure to me and I don't have my Crystal Ball with me today to see what you can see on your site.
Your sample doesn't correspond to what you posted in your first request. I cannot see what really problem you have, If I try to translate of what you have said I got the following:
-- that what I can see as your table:
declare @tbl table
(
Col1 VARCHAR(10), ExpDate1 DATETIME,
Col2 VARCHAR(10), ExpDate2 DATETIME,
Col3 VARCHAR(10), ExpDate3 DATETIME,
Col4 VARCHAR(10), ExpDate4 DATETIME,
Col5 VARCHAR(10), ExpDate5 DATETIME)
-- If you want to search for string in "CONCATENATED" value of above columns you can do the following:
;with cte
as
(
select *
,Col1+' '+CONVERT(varchar(30),ExpDate1, [whateverformatyouwant]) + ' '
+Col2+' '+CONVERT(varchar(30),ExpDate2, [whateverformatyouwant]) + ' '
+Col3+' '+CONVERT(varchar(30),ExpDate3, [whateverformatyouwant]) + ' '
+Col4+' '+CONVERT(varchar(30),ExpDate4, [whateverformatyouwant]) + ' '
+Col5+' '+CONVERT(varchar(30),ExpDate5, [whateverformatyouwant]) AS ConcatenatedCol
from @tbl
)
select * from cte where ConcatenatedCol LIKE '%search word%'
Something tells me, that the above is not what you are looking for. But again, your explanations too obscure (at least to me).
Now, if for any reason the sample I gave is exactly what you trying to do, than I can give you another advise: If your table is significantly large and you want such search to be acceptably quick, you better use Free-text index search feature.
October 18, 2012 at 4:20 am
Hi,
am trying to give some more brief..
my previous requirement is
I having a table with 5 columns
Record_idCol1Col2Col3Col4Col5
------------------------------------------------------
101AABAA
102BBNULLNULLB
103CCCNULLC
104DCDDD
105AAAAAA
106BCAAAD
if record 101 contains all 5 columns same (i.e:A) then only "A" should be inserted into another
table
in record 101, col3 contains "B" then it should be skipped.
in record 102 - 3 columns contains B and 2 columns contain NULL, then it should be inserted.
Record 101 - SKIP
Record 102 - Insert B in to another table.
Record 103 - Insert C in to another table.
Record 104 - SKIP
* if in any record that contains "AA" in any column, it should return "AA" (records 105, 106)
Now i've inserted a new column called summary (summary column is a concatenation of all columns
*** i want the same above logic should be implemented in summary column.
October 18, 2012 at 4:57 am
;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5)
AS
(SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL
SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL
SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL
SELECT 104, 'D', 'C', 'D', 'D','D' UNION ALL
SELECT 105,'AA','A','A','A','A'UNION ALL
SELECT 106,'B','C','A','AA','D')
, CT
AS
(
SELECT DISTINCT Record_id, Newcol
FROM Mysample
CROSS APPLY (SELECT Newcol FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol) ) n
WHERE Newcol IS NOT NULL
)
SELECT Record_id, MAX(Newcol) AS Newcol
FROM CT
GROUP BY Record_id
HAVING COUNT(*) = 1
UNION
SELECT Record_id, Newcol
FROM CT WHERE Newcol = 'AA'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply