Search a date in the field and group by

Hi,

I am trying to create a SQL statement that will search within a field called description and find the DATE. Once the Date is found, it needs to be GROUP BY DATE & the field after Date (See below example 01/01/04-06/30/04 is Date & $0-19 E is the second data that needs to be GROUP BY). The issue is the DATE is not always in the same position, so I cannot use a substring field. Also, the Date data is typed by Customer Service Rep. and it is not always in the same format, meaning
the date 07/01/03 can be 7/1/03 or 7/01/03 or 07/1/03. Below is the data in the description field how it looks like.

CASUAL GIFT BYRS ONLY 01/01/04-06/30/04 $0-$19 E
CASUAL GIFT BYRS ONLY 01/01/04-06/30/04 $0-$19 R

CASUAL GIFT BYRS ONLY 7/01/03-12/31/03 $100+ R
CASUAL GIFT BYRS ONLY 7/01/03-12/31/03 $100+ E

CASUAL GIFT BYRS ONLY 1/01/03-06/30/03 $100+ R
CASUAL GIFT BYRS ONLY 1/01/03-06/30/03 $100+ E


Please advise.

Thanks,
Mohammed
msalam65Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
unless you can match on this CASUAL GIFT BYRS ONLY  exactly
then i don't thinks it's possible....

since presumably  Customer Service Rep could contain numerics and or /'s

you'll be better off going back to the design or the source tables that constructed the description
column in the first place... you need these as actual columns.

 
0
ShogunWadeCommented:
I concur with lowfat.    It is virtually imposible to do this without your data being in a consistent or normalised form.
0
Melih SARICAOwnerCommented:

LowsFat +1, Shogun +1




0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

muzzy2003Commented:
You could return the data to C#/VB code and use some sort of regular expression search to find the date, then split up the data. You could do this either as a one off to assist with normalising your data, or as part of the process you were originally looking for. But basically, I agree with the other guys.
0
Ken SelviaRetiredCommented:
Even though I agree with everyone, even if you do redesign your table, you need the script to do it if you are to keep your existing data, so this should get you started.  

create table mytable (data varchar(255))

insert mytable select 'CASUAL GIFT BYRS ONLY 01/01/04-06/30/04 $0-$19 E'
insert mytable select 'CASUAL GIFT BYRS ONLY 01/01/04-06/30/04 $0-$19 R'
insert mytable select 'CASUAL GIFT BYRS ONLY 7/01/03-12/31/03 $100+ R'
insert mytable select 'CASUAL GIFT BYRS ONLY 7/01/03-12/31/03 $100+ E'
insert mytable select 'CASUAL GIFT BYRS ONLY 1/01/03-06/30/03 $100+ R'
insert mytable select 'CASUAL GIFT BYRS ONLY 1/01/03-06/30/03 $100+ E'

alter table mytable add p1 int, p2 int, p3 int, p4 int

declare @p1 int, @p2 int, @p3 int, @p4 int

-- Search from end of data backwards delimiting by spaces and dash. If that is not always the case this will have to be more complicated (or different)

update mytable
set @p4 = p4 = Len(data) - charindex(' ',reverse(data)), -- End of amount field
     @p3 = p3 = Len(data) - charindex(' ',reverse(data),len(data) - @p4+1), -- End of second date
     @p2 = p2 = Len(data) - charindex('-',reverse(data),len(data) - @p3), -- End of first date
     @p1 = p1 = Len(data) - charindex(' ',reverse(data),len(data) - @p2)  -- End of description

select  Item = substring(data,1,p1),
          StartDate = convert (varchar, cast ( substring(data,p1+1, p2 - p1) as datetime) , 110),
          EndDate = convert (varchar, cast ( substring(data,p2+2, p3 - p2) as datetime) , 110),
         Amount = substring(data,p3+1, p4 - p3)
From mytable

You don't actually have to add the p1,p2... columns to your table but it makes this easier.  You could build really complex substring() functions to achieve it without adding them.
0
BillAn1Commented:
AS the others have said, you need to have a rule that will uniquely define what the date actually is, since there could be numerics in your description, which may look like a date, etc.
However, I am guessing that a safe bet is that any pattern of the form xx/xx/xx-xx/xx/xx will be the date. If that is the case, then you use PATINDEX to find this pattern
 e.g.
select
substring ( description, patindex ( '%__/__/__-__/__/__%' , description) , 17) as daterange,
right ( description, patindex ( '%__/__/__-__/__/__%' , description) + 17, 8000) as value,
count(*)
from MyTable
group by
substring ( description, patindex ( '%__/__/__-__/__/__%' , description) , 17),
right ( description, patindex ( '%__/__/__-__/__/__%' , description) + 17, 8000)

depending on exactly what youw ant to do with the data
0
LowfatspreadCommented:
good point billan1
but he said he's got 1 and 2 character date day/month representations...
 
0
BillAn1Commented:
Oops! sorry, I missed that complication.

the most robust method would be to use regular expressions, but you can create a parser that should find your date, depending on exactly what you want. e.g. the following will find any pattern of the forn N/N/N-N/N/N where N is a  1 or 2 digit number. You could add further complexity to check they are actually true dates if you want, and/or since this is manually typed data, you might also need to allow for spaces etc?


select dbo.fnParseDates(data),
substring(data, 1+charindex(dbo.fnParseDates(data),data) + len(dbo.fnParseDates(data)),8000)
from Mytable


create function fnParseDates( @data varchar(120))
returns varchar(17)
as
begin
      
      declare @dt varchar(17)
      declare @state integer
      set @state = 0
-- use a state variable to keep track of where you are in the date string
-- dd/mm/yy-dd/mm/yy
-- 123456789abcdefgh

      declare @ch char(1)

      while @data <> ''
      begin
            set @ch = left(@data,1)
            if @state = 0 or @state = 3 or @state = 6 or @state = 9 or @state = 12 or @state = 15
            begin
                  if @ch like '[0-9]'
                  begin
                        set @dt = @dt + @ch
                        set @state = @state + 1
                  end
                  else set @state = 0
            end
            else if @state = 1 or @state = 4 or @state = 7 or @state = 10 or @state = 13
            begin
                  if @ch like '[0-9]'
                  begin
                        set @dt = @dt + @ch
                        set @state = @state + 1
                  end
                  else if @ch = '/'
                  begin
                        set @dt = @dt + @ch
                        set @state = @state + 2
                  end
                  else set @state = 0
            end
            else if @state = 2 or @state = 5 or @state = 11 or @state = 14
            begin
                  if @ch = '/'
                  begin
                        set @dt = @dt + @ch
                        set @state = @state + 1
                  end
                  else set @state = 0
            end
            else if @state = 8
                  begin
                  if @ch = '-'
                  begin
                        set @dt = @dt + @ch
                        set @state = @state + 1
                  end
                  else set @state = 0

            end
            else if @state = 16
            begin
                  if @ch like '[0-9]'
                  begin
                        set @dt = @dt + @ch
                  end
                  return @dt
            end
            if @state = 0 set @dt = ''
            set @data = right(@data, len(@data)-1)
      end
      if @state = 16
            return @dt
      return ''
end


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.