Here are a few gems I just found in out JobTitle table, which stores contact
job titles in our database. If we have a job title, it's because it came in
a bulk-insert data file from a client.
The best
the big dawg
the boss
the man
The Master Himself
THE ONLY PERSON IN IT DEPARTMENT
The Simpsons
The Veritable Bugeater
ROFL!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:emwFdG2zFHA.904@.tk2msftngp13.phx.gbl...
> The Veritable Bugeater
I'd like to hear the story behind this one.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e8znzZ2zFHA.664@.tk2msftngp13.phx.gbl...
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:emwFdG2zFHA.904@.tk2msftngp13.phx.gbl...
>
> I'd like to hear the story behind this one.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
Probably not the real story, but anyway:
http://mynptv.org/sportsFeat/pionee..._lessplan1.html|||What a nightmare (it annoys me when I find data like this in a database.
Why the heck even have a column if you allow stuff like this, obviously more
annoyed by the original people to enter the data. I know if you get the
data there is probably an expectation that it is right (which is obviously
false :). I wonder if any ETL product would catch this sort of thing and
exception it out before it was entered into the database?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:emwFdG2zFHA.904@.tk2msftngp13.phx.gbl...
> Here are a few gems I just found in out JobTitle table, which stores
> contact job titles in our database. If we have a job title, it's because
> it came in a bulk-insert data file from a client.
> The best
> the big dawg
> the boss
> the man
> The Master Himself
> THE ONLY PERSON IN IT DEPARTMENT
> The Simpsons
> The Veritable Bugeater
> ROFL!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> What a nightmare (it annoys me when I find data like this in a database.
> Why the heck even have a column if you allow stuff like this, obviously
> more annoyed by the original people to enter the data. I know if you get
> the data there is probably an expectation that it is right (which is
> obviously false :). I wonder if any ETL product would catch this sort of
> thing and exception it out before it was entered into the database?
heh.
If you worked here, you would hurt someone.
I could list snips from all the databases in the company that are all
similarly stupid. They don't want any automation, either: "Don't program
anything, we don't have the time. Just hurry up and copy/paste these 700
values into this Word template."
A homeless dude off the street could do this work. Why hire a guy that can
write code in 6 languages?!?
[bonking head on desk]
Anybody need a MCSD MCT? I'll be there in the morning!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Mike, I'll follow you :-)
Showing posts with label stores. Show all posts
Showing posts with label stores. Show all posts
Monday, March 26, 2012
gaps in integer ranges
hello, i have quite a challenge on my hands here and would appreciate any help. :confused:
I have a table variable that stores integer ranges representing times of the day:
select * from @.reservations
room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000
I need to be able to return the minutes that are open for each room. The @.reservations table shows me the times that are blocked.
I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.
Based on the @.reservations table above, I'd like to write something that returns:
room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440
This result represents the times in minutes that are available.
I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @.reservations table.
I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)in the results, do you also want
1 2004-12-12 0 1440
1 2004-12-13 0 1440
1 2004-12-14 0 1440
1 2004-12-15 0 1440
2 2004-12-13 0 1440
2 2004-12-14 0 1440
2 2004-12-15 0 1440
3 2004-12-11 0 1440
3 2004-12-12 0 1440
3 2004-12-13 0 1440
3 2004-12-15 0 1440
4 2004-12-11 0 1440
4 2004-12-12 0 1440
4 2004-12-13 0 1440
4 2004-12-14 0 1440
or do you just want the available ranges for dates where some part of the date is already booked?|||I want the available ranges for the dates where some part of the date is already booked
and I just thought that I will also need to know the available times if there is nothing booked in the room...yikes...
so if I have no row in my @.reservations table for room 1 on 2004-12-21 then I would want to return
1 2004-12-21 0 1440
Hmmm...would something like this work for that scenario??
I have a stored procedure that going to give me these results. I am writing a search. In the stored procedure I pass in the start date of the search and the end date of the search. So i could potentially, check that if there are no rows in the @.reservations table for that date and room, i would need to return 0-1440
thank you!|||The best way I have found to handle this functionality it to create a table in your database that consists of nothing but incrementing value from zero to ...well, whatever the highest number you end up needing.
By supplying a starting date to a SELECT query based upon this table, you can use the values to create a list of time intervals of whatever duration you choose. From this dataset, left join your Reservations table and return only the records where no corresponding Reservation exists. Voila! Your resultset shows all the unbooked times.
I have a table variable that stores integer ranges representing times of the day:
select * from @.reservations
room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000
I need to be able to return the minutes that are open for each room. The @.reservations table shows me the times that are blocked.
I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.
Based on the @.reservations table above, I'd like to write something that returns:
room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440
This result represents the times in minutes that are available.
I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @.reservations table.
I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)in the results, do you also want
1 2004-12-12 0 1440
1 2004-12-13 0 1440
1 2004-12-14 0 1440
1 2004-12-15 0 1440
2 2004-12-13 0 1440
2 2004-12-14 0 1440
2 2004-12-15 0 1440
3 2004-12-11 0 1440
3 2004-12-12 0 1440
3 2004-12-13 0 1440
3 2004-12-15 0 1440
4 2004-12-11 0 1440
4 2004-12-12 0 1440
4 2004-12-13 0 1440
4 2004-12-14 0 1440
or do you just want the available ranges for dates where some part of the date is already booked?|||I want the available ranges for the dates where some part of the date is already booked
and I just thought that I will also need to know the available times if there is nothing booked in the room...yikes...
so if I have no row in my @.reservations table for room 1 on 2004-12-21 then I would want to return
1 2004-12-21 0 1440
Hmmm...would something like this work for that scenario??
I have a stored procedure that going to give me these results. I am writing a search. In the stored procedure I pass in the start date of the search and the end date of the search. So i could potentially, check that if there are no rows in the @.reservations table for that date and room, i would need to return 0-1440
thank you!|||The best way I have found to handle this functionality it to create a table in your database that consists of nothing but incrementing value from zero to ...well, whatever the highest number you end up needing.
By supplying a starting date to a SELECT query based upon this table, you can use the values to create a list of time intervals of whatever duration you choose. From this dataset, left join your Reservations table and return only the records where no corresponding Reservation exists. Voila! Your resultset shows all the unbooked times.
Sunday, February 26, 2012
Full-Text-Search
Hi,
I have a full-text search enabled table and an image data type column that stores the extracts from the documents stored in the files. I have created a full text catalog that indexes few columns in the table and image data type column as well and I assigned the extension column to this image data type column.
Everything works fine except that search on the image data type column called, in my case "body" does not return anything. The documents exist, there is an information in the "body" column.
My code goes like this:
select FT_TBL.resourceID, FT_TBL.lang, FT_TBL.keywords, FT_TBL.description, FT_TBL.extension, b.rank
from full_text_search as FT_TBL inner join containstable(full_text_search, *, 'Microsoft') as b
on b.[key] = FT_TBL.resourceID
order by b.rank desc
any help appreciated,
thanksI figured the solution to the previous post. I was trying to search on the .pdf files, but I do not have filter installed on the server to filter through these files, other files extension work fine.
Another problem:
I have two tables that are full-text search enabled. And I need to search one table first, and than the columns in the second one. This works fine if the search criteria is found in both tables, but if not the query returns nothing.
help much appreciated!
Thanks|||use union all.
I have a full-text search enabled table and an image data type column that stores the extracts from the documents stored in the files. I have created a full text catalog that indexes few columns in the table and image data type column as well and I assigned the extension column to this image data type column.
Everything works fine except that search on the image data type column called, in my case "body" does not return anything. The documents exist, there is an information in the "body" column.
My code goes like this:
select FT_TBL.resourceID, FT_TBL.lang, FT_TBL.keywords, FT_TBL.description, FT_TBL.extension, b.rank
from full_text_search as FT_TBL inner join containstable(full_text_search, *, 'Microsoft') as b
on b.[key] = FT_TBL.resourceID
order by b.rank desc
any help appreciated,
thanksI figured the solution to the previous post. I was trying to search on the .pdf files, but I do not have filter installed on the server to filter through these files, other files extension work fine.
Another problem:
I have two tables that are full-text search enabled. And I need to search one table first, and than the columns in the second one. This works fine if the search criteria is found in both tables, but if not the query returns nothing.
help much appreciated!
Thanks|||use union all.
Subscribe to:
Posts (Atom)