• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
msalam65
Asked:
msalam65
1 Solution
 
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 SARICACommented:

LowsFat +1, Shogun +1




0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now