Is this workable

Question

Is this workable

Nilssond

Hall of Fame

Points: 3285
More actions
September 27, 2007 at 4:09 pm

#120620

I have a table (MyTable) with multiple eligibility segments for members. Typical data looks like this:
MemberNumber StartDate EndDate Role
12345 20070101 20070131 A
12345 20070201 20070331 A
12345 20070401 0 A
98765 19991201 0 A
Is there any inherent problem with the following TSQL which attempts to find all members who were in Role A for a specific date. The UDF dbo.ZtoEOT transforms 0 to 20991231, if present, otherwise returns the input date/ The criterion date is 20070801
Select MemberNumber, StartDate, EndDate from Mytable where
20070801 between StartDate and dbo.ZtoEOT(EndDate) or StartDate 20070801
I would expect the last 2 records to appear in the results

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 1

C'mon... you know better than to make us ask you for the table/column schema... it looks like your dates are Integer based, but who can tell??? :blink: And, to make sure it's not the function, you should post the function, as well.

All that not with standing...

1. You should not be using character based or integer based data for the dates... you should be using the DATETIME datatype. You really need to fix your database.

2. What is the bold code below supposed to do?

Select MemberNumber, StartDate, EndDate from Mytable where

20070801 between StartDate and dbo.ZtoEOT(EndDate) or StartDate 20070801

Aren't you in need of an equal sign between that "date" and the StartDate?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 2

1. You should not be using character based or integer based data for the dates... you should be using the DATETIME datatype. You really need to fix your database.

Tell it to the guys who designed msdb. :Whistling:

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

You mean the same ones that RPAD in SysProcesses? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 4

Jeff Moden (9/28/2007)
You mean the same ones that RPAD in SysProcesses? 😀

yeah... and all stored procedures that touch it 😀 😀

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

September 28, 2007 at 10:36 pm

#737331

Translation request : RPAD?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

MSDB is a perfect example of how not to design a database. Every time I have to get data from that DB I end up tearing more hair out.

The designers of that DB should be taken out and shot. Repeatedly. Then a few more times cause they didn't fix it in 2005

:angry::crazy:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

You know what guys, I think she REALLY means it :w00t:.

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 8

Sure... somehow they managed to save trailing spaces in NVARCHAR columns... Use DATALENGTH to check... some of the columns are defined as 100 characters wide... and even if they only have 5 siginificant characters, they Right PADded (RPAD) with 95 spaces. (RPAD is an old BASIC command and it makes good shorthand for "filled with trailing spaces" 😛 )

One of our guys make a trigger to store some of that information... after three years, the target of the trigger contained 55 Gig... of mostly spaces. Reduced to about 3 gig once I "deflated" the table by removing the trailing spaces... (RPAD).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Wow, 52 gig of space!!!! That's a lot of crap to put up with ;).

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 10

GilaMonster (9/29/2007)
MSDB is a perfect example of how not to design a database. Every time I have to get data from that DB I end up tearing more hair out.
The designers of that DB should be taken out and shot. Repeatedly. Then a few more times cause they didn't fix it in 2005
:angry::crazy:

Heh... I agreeeeeeeee!!!! When we find them, I'll hold 'em... you hit 'em. Those folks should be shot butt first out of a cannon into a stone wall!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 11

Ninja's_RGR'us (9/29/2007)
Wow, 52 gig of space!!!! That's a lot of crap to put up with ;).

Ouch! Brain freeze! You just reminded me of a girl I used to date... lot's of space between the ears and gave lot's of crap 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Please don't mention any names... don't want to hurt anyone's feelings.

:hehe:.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

I'm going to go get a sweater - it just got chilly in here;)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Why in heavens name would you say something like that?? :hehe:.