Effective Dating Query

Question

Effective Dating Query

Viewing 8 posts - 16 through 22 (of 22 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

longobardia (1/30/2009)
Could you explain? Please.
I don't follow.
AL

If you look at your code you will see inconsistancies between column names in different parts of the code. Without looking back myself I will use the following.

In a case insensitive collation, 'a' = 'A' is true. In a case sensitive collation, 'a' = 'A' is false. This also is true when naming columns, etc. If the database is using a case sensitive collation and you name a column TransDate, you have to refere to it as TransDate in all your queries, etc. My SnadBox database is case sensitive, so your code had issues when I first tried to use it.

longobardia SSC Eights! Points: 957 More actions · Answer 2

OH. Got Ya.

Yes, I am using case insensitive collation.

Sorry,

AL

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I'm not adding any data to your sample data provided. I don't fully understand your business rules and would prefer you add the data and provide the expected results based there on so we can simply write and test.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Also, I don't have an employee table or sample data. We could use that as well to help you with your query.

Be sure to include any indexes that are on these tables as well.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is the code I was working with. Please see if any of it helps you out.

Edit: For some reason, I can't cut and paste code from SSMS even if I go through a text editor. That's why the upload.

longobardia SSC Eights! Points: 957 More actions · Answer 6

Hey There,

Pretty nifty query. It actually works if you change the following:

group by

tT.employeeid,

tT.TranDte

having

max(ah.updatedate) <= TranDte

)

If you run the query as you sent in and you add another employeeid like so:

insert into address_history

select 2, '1/1/2008', '333 MAIN STREET','','New York', 'NY', '11001'

union all

select 2, '1/15/2008', '125 W. 7th Ave','','New York', 'NY', '11002'

insert into tranTable

select 2, '1/1/2008'

union all

select 2, '1/16/2008'

It will display the following:

[font="Courier New"]12008-07-14 00:00:00.000123 NW 3rd AveNew YorkNY11001

12008-08-16 00:00:00.00078 E. 5th AveNew YorkNY11002

22008-01-16 00:00:00.000125 W. 7th AveNew YorkNY11002[/font]

which leaves one row from employeeid # 2 out. After you make the correction to the query as specified above, you'll get this:

[font="Courier New"]12008-07-14 00:00:00.000123 NW 3rd AveNew YorkNY11001

12008-08-16 00:00:00.00078 E. 5th AveNew YorkNY11002

22008-01-01 00:00:00.000333 MAIN STREETNew YorkNY11001

22008-01-16 00:00:00.000125 W. 7th AveNew YorkNY11002[/font]

Which is correct.

Thanks a million,

Al

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

longobardia (1/30/2009)
Hey There,
Pretty nifty query. It actually works if you change the following:
group by
tT.employeeid,
tT.TranDte
having
max(ah.updatedate) <= TranDte
)
If you run the query as you sent in and you add another employeeid like so:
insert into address_history
select 2, '1/1/2008', '333 MAIN STREET','','New York', 'NY', '11001'
union all
select 2, '1/15/2008', '125 W. 7th Ave','','New York', 'NY', '11002'
insert into tranTable
select 2, '1/1/2008'
union all
select 2, '1/16/2008'
It will display the following:
[font="Courier New"]12008-07-14 00:00:00.000123 NW 3rd AveNew YorkNY11001
12008-08-16 00:00:00.00078 E. 5th AveNew YorkNY11002
22008-01-16 00:00:00.000125 W. 7th AveNew YorkNY11002[/font]
which leaves one row from employeeid # 2 out. After you make the correction to the query as specified above, you'll get this:
[font="Courier New"]12008-07-14 00:00:00.000123 NW 3rd AveNew YorkNY11001
12008-08-16 00:00:00.00078 E. 5th AveNew YorkNY11002
22008-01-01 00:00:00.000333 MAIN STREETNew YorkNY11001
22008-01-16 00:00:00.000125 W. 7th AveNew YorkNY11002[/font]
Which is correct.
Thanks a million,
Al

Which is why I asked you for more test data and expected output.

😀

longobardia SSC Eights! Points: 957 More actions · Answer 8

You were correct for asking for more data. It's been a crazy day for me

and didn't get a chance to connect to the other db server.

Anyhow, It actually works well and I will try and implement in test and see how it performs with 1/2 million rows of data.

I'll keep you posted. Again.. Many thanks and enjoy your weekend.

Al

ps. you guys are awesome!