Running business logic on messy data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running business logic on messy data

Author

Topic

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-07 : 15:51:59

MONTHYEAR DATE RATE ROW
August 2008 1 $399 1
August 2008 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 $399 2
August 2008 11 - 12 - 13 $399 3
August 2008 14 $399 4
August 2008 15 $399 5
August 2008 16 $399 6
August 2008 17 $399 7
August 2008 18 $399 8
August 2008 19 $399 9
August 2008 20 $399 10
August 2008 21 - 22 - 23 - 24 $399 11
August 2008 25 $399 12
August 2008 26 $399 13
August 2008 27 - 28 $399 14
August 2008 29 $399 15
August 2008 30 16

I have a tables worth of data where the status of a room is based on the rate and the way the Date column is labeled out. The logic is very weird and is as follows...

Row 1 August 1, 2008, the room is available at 399
Row 2 August 2 through 9, the rooms are sold out, but August 10th is available
Row 3 August 11 through 12 is sold out, but the 13th is available.
Row 4 through 10, August 14th through 20th are available
Row 11 Is the same as in row 2, August 21 to 23 are sold out, but 24th is available.
Row 14, Aug 27th is sold out but 28th is available.

for Row 16, if its the last day of the month and the rate is blank, then it is sold out, if the rate is there, then its available. in this case it is sold out.

I'm trying to write a query that can create 2 calculated fields. One that combines MonthYear and Date into a single column with datatype datetime, and the second that shows the either the rate of the room, or whether or not its sold out.

Basically something that looks like this,

8/1/2008 $399
8/2/2008 Sold Out
8/3/2008 Sold Out
8/4/2008 Sold Out
8/5/2008 Sold Out
8/6/2008 Sold Out
8/7/2008 Sold Out
8/8/2008 Sold Out
8/9/2008 Sold Out
8/10/2008 $399
8/11/2008 Sold Out
8/12/2008 Sold Out
8/13/2008 $399
8/14/2008 $399
8/15/2008 $399
8/16/2008 $399
8/17/2008 $399
8/18/2008 $399
8/19/2008 $399
8/20/2008 $399
8/21/2008 Sold Out
8/22/2008 Sold Out
8/23/2008 Sold Out
8/24/2008 $399
8/25/2008 $399
8/26/2008 $399
8/27/2008 Sold Out
8/28/2008 $399
8/29/2008 $399
8/30/2008 Sold Out

My initial thought is to do something with those dashes. If number has a dash after it, label it sold out, Then if rate is blank then label it sold out. I'm really unsure of how to approach this to be honest, if somebody could guide me in the right direction that would be great.

Here's the code to create the original table,

 
( monthyear nvarchar(200) null,
 date nvarchar(200) null,
rate nvarchar (200) null)
go

insert into testing(monthyear, date, rate) values('August 2008','1','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','11 - 12 - 13','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','14','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','15','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','16','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','17','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','18','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','19','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','20','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','21 - 22 - 23 - 24','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','25','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','26','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','27 - 28','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','29','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','30','');

go

pootle_flump

1064 Posts

Posted - 2008-08-07 : 16:28:52

Hi

Could you post the DDL and DML for us please so we can copy and paste into SSMS for writing the query?

This is doable with a tally table (a lá http://vyaskn.tripod.com/fun_with_numbers_in_t-sql_queries.htm). Every date is accounted for right?

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-07 : 17:09:13

quote:
Originally posted by pootle_flump

Hi

Could you post the DDL and DML for us please so we can copy and paste into SSMS for writing the query?

This is doable with a tally table (a lá http://vyaskn.tripod.com/fun_with_numbers_in_t-sql_queries.htm). Every date is accounted for right?

sorry about that, not sure what you mean but here's the code to import all that into a new table called testing.

to create the table:

create table testing

 
( monthyear nvarchar(200) null,
 date nvarchar(200) null,
rate nvarchar (200) null)
go

insert into testing(monthyear, date, rate) values('August 2008','1','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','11 - 12 - 13','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','14','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','15','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','16','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','17','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','18','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','19','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','20','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','21 - 22 - 23 - 24','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','25','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','26','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','27 - 28','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','29','$399 ');
insert into testing(monthyear, date, rate) values('August 2008','30','');

go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 01:04:54

create this function:-

CREATE FUNCTION ParseValues  
(@String varchar(8000),
@Delimiter char(2)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(1000)  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END,  
@String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

and use this like below


--your sample table
declare  @testing table
( monthyear nvarchar(200) null,
 date nvarchar(200) null,
rate nvarchar (200) null)

--sample data
insert into @testing(monthyear, date, rate) values('August 2008','1','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','11 - 12 - 13','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','14','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','15','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','16','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','17','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','18','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','19','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','20','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','21 - 22 - 23 - 24','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','25','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','26','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','27 - 28','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','29','$399 ');
insert into @testing(monthyear, date, rate) values('August 2008','30','');

--solution
;With CTE(date,datevalues,rate,ID) AS
(
select convert(datetime,ltrim(rtrim(b.Val)) + ' ' +  monthyear),t.date,t.rate,b.ID
from @testing t
cross apply ParseValues(t.date,'-')b
)

select c1.date,case when c2.datevalues is null or nullif(rate,'') is null then 'sold out' else rate end 
from CTE c1
LEFT JOIN (SELECT datevalues,MAX(Date) AS maxdate
           FROM CTE
           GROUP BY datevalues)c2
ON c2.datevalues=c1.datevalues
and c2.maxdate=c1.date

output
----------------------
date                    rate
----------------------- ---------------------------------------------
2008-08-01 00:00:00.000 $399 
2008-08-02 00:00:00.000 sold out
2008-08-03 00:00:00.000 sold out
2008-08-04 00:00:00.000 sold out
2008-08-05 00:00:00.000 sold out
2008-08-06 00:00:00.000 sold out
2008-08-07 00:00:00.000 sold out
2008-08-08 00:00:00.000 sold out
2008-08-09 00:00:00.000 sold out
2008-08-10 00:00:00.000 $399 
2008-08-11 00:00:00.000 sold out
2008-08-12 00:00:00.000 sold out
2008-08-13 00:00:00.000 $399 
2008-08-14 00:00:00.000 $399 
2008-08-15 00:00:00.000 $399 
2008-08-16 00:00:00.000 $399 
2008-08-17 00:00:00.000 $399 
2008-08-18 00:00:00.000 $399 
2008-08-19 00:00:00.000 $399 
2008-08-20 00:00:00.000 $399 
2008-08-21 00:00:00.000 sold out
2008-08-22 00:00:00.000 sold out
2008-08-23 00:00:00.000 sold out
2008-08-24 00:00:00.000 $399 
2008-08-25 00:00:00.000 $399 
2008-08-26 00:00:00.000 $399 
2008-08-27 00:00:00.000 sold out
2008-08-28 00:00:00.000 $399 
2008-08-29 00:00:00.000 $399 
2008-08-30 00:00:00.000 sold out

(30 row(s) affected)

pootle_flump

1064 Posts

Posted - 2008-08-08 : 03:26:31

Thanks fuzzy.

An alternative (using a numbers table as linked to):

SELECT	  da_date	= CAST(CAST(numbers.number AS NVARCHAR(2)) + N' ' + testing.monthyear AS SMALLDATETIME)
		, da_rate	= CASE
							WHEN testing.date LIKE N'%' + CAST(numbers.number AS NVARCHAR(2)) + N' -%' THEN
								'Sold Out'
							ELSE
								COALESCE(NULLIF(testing.rate, ''), 'Sold Out')
						END
FROM	dbo.numbers
INNER JOIN 
		dbo.testing
ON	N'- ' + testing.date + N' -' LIKE N'%- ' + CAST(numbers.number AS NVARCHAR(2)) + N' -%'
WHERE	numbers.number <= 31

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-08 : 13:18:50

Visakh

I'm trying to insert the values into the table I'm going to be running your logic on but it keeps saying

Msg 1087, Level 15, State 2, Line 18
Must declare the table variable "@greatwolflogic".

First I ran


declare @greatwolflogic table

(
location nvarchar(100) null,
roomtype nvarchar(100) null,
monthyear nvarchar(100) null,
date nvarchar(200) null,
rate nvarchar(20) null,
inputvalues nvarchar(50) null,
executiontime nvarchar(100) null,
id_num int not null

)

then I run


insert into @greatwolflogic (location, roomtype, monthyear, date, rate, inputvalues, executiontime, id_num)
select location, roomtype, monthyear, date, rate, inputvalues, executiontime, id_num
from greatwolf

and I get Msg 1087, Level 15, State 2, Line 18
Must declare the table variable "@greatwolflogic".

I thought I declared it in the first statement that I ran

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 13:25:26

quote:
Originally posted by fuzzyip

Visakh

I'm trying to insert the values into the table I'm going to be running your logic on but it keeps saying

Msg 1087, Level 15, State 2, Line 18
Must declare the table variable "@greatwolflogic".

First I ran

declare @greatwolflogic table

(
location nvarchar(100) null,
roomtype nvarchar(100) null,
monthyear nvarchar(100) null,
date nvarchar(200) null,
rate nvarchar(20) null,
inputvalues nvarchar(50) null,
executiontime nvarchar(100) null,
id_num int not null

)

then I run

insert into @greatwolflogic (location, roomtype, monthyear, date, rate, inputvalues, executiontime, id_num)
select location, roomtype, monthyear, date, rate, inputvalues, executiontime, id_num
from greatwolf

and I get Msg 1087, Level 15, State 2, Line 18
Must declare the table variable "@greatwolflogic".

I thought I declared it in the first statement that I ran

you should run them together. the scope of table variable is only in batch so it wont exist when you run insert alone. run them together and make sure you dont put a go after table creation

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-08 : 13:31:28

I also tried your method pootie and I get the following error

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-08 : 13:37:54

[/quote]
you should run them together. the scope of table variable is only in batch so it wont exist when you run insert alone. run them together and make sure you dont put a go after table creation
[/quote]

Oh ok,

I ran all of this together...

declare @greatwolflogic table

(
location nvarchar(100) null,
roomtype nvarchar(100) null,
monthyear nvarchar(100) null,
date nvarchar(200) null,
rate nvarchar(20) null,
inputvalues nvarchar(50) null,
executiontime nvarchar(100) null,
id_num int not null

)




insert into @greatwolflogic (location, roomtype, monthyear, date, rate, inputvalues, executiontime, id_num)
select location, roomtype, monthyear, date, rate, inputvalues, executiontime, id_num
from greatwolf


;With CTE(date,datevalues,rate,ID) AS
(
select convert(datetime,ltrim(rtrim(b.Val)) + ' ' +  monthyear),t.date,t.rate,b.ID
from @greatwolflogic t
cross apply ParseValues(t.date,'-')b
)

select c1.date,case when c2.datevalues is null or nullif(rate,'') is null then 'sold out' else rate end 
from CTE c1
LEFT JOIN (SELECT datevalues,MAX(Date) AS maxdate
           FROM CTE
           GROUP BY datevalues)c2
ON c2.datevalues=c1.datevalues
and c2.maxdate=c1.date

It looked like it was working but I get this error message,

(248263 row(s) affected)
Msg 8115, Level 16, State 2, Line 23
Arithmetic overflow error converting expression to data type datetime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 13:42:36

thats because you've some values in your monthyear field or date field which when concatenated is not giving you a valid datetime value. is it always of the form month year (August 2008) with a space in between?

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-08 : 13:42:42

I have a feeling its because some of the MonthYear entries have bad entries where the data is cutoff and may possible look like this,

August 200
or
September 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 13:43:46

quote:
Originally posted by fuzzyip

I have a feeling its because some of the MonthYear entries have bad entries where the data is cutoff and may possible look like this,

August 200
or
September 2

you're right

. just make sure they are in same format.

pootle_flump

1064 Posts

Posted - 2008-08-08 : 16:41:57

Like you said - messy data. You are right - this is a very poor design. Sorry if it is your design but still...

Is your plan to normalise this and sort out data types (i.e. dates in date domains not NVARCHAR)?

I could supply links passé Jeff Smith but I think you are coming round to the idea that dates should not be in text columns....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources