Issue with date field calculations

Question

Post reply

Issue with date field calculations

Viewing 7 posts - 16 through 21 (of 21 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Is it something like this you're looking for?

Problem: get key + latest date - ie select key, max(date) as maxDate

select h.col1,

h.col2,

h.col3,

.......

fromhistory h

join

(

select cust_id,

max(sold_date) as maxDate

from history

) x

onh.cust_id = x.custid

andh.sold_date = x.maxDate

/Kenneth

Terry Gamble Ten Centuries Points: 1072 More actions · Answer 2

Kenneth,

That would be a good way to accomplish this task and one I hadn't thought of honestly.

But what I was talking about was using @@Error to try to catch the error in a try - catch block of TSQL and handle it internally, such that even using the SSMS client, I'd never see "duplicate row ignored" which is what myself and Jeff Moden were talkinga bout.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Ah, I see...

Well, I was mainly thinking about how to avoid the dupe issue in the first place 😉

/Kenneth

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

Jeff Moden (1/19/2008)
No, no... it won't fail if the IGNORE DUPLICATES option is turned on for the unique index. It will just give a warning that at least one duplicate was ignored.

I don't really see the point of an unique index with ignore duplicates on. Am I missing something?

Sorry, Terry... I don't know enough about the client known as SSIS to flip a nickel.

SQL Server Integration Services. The 2005 replacement for DTS.

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

Terry Gamble Ten Centuries Points: 1072 More actions · Answer 5

Bad assumptions and logic were what you might have missed.

I was trying to use an order by clause to give me the data in the order I wanted it and was having issues.

I ended up re-writing the logic to guarantee that this issue wouldn't crop up again and removed my bad assumptions.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

don't really see the point of an unique index with ignore duplicates on. Am I missing something?

It's a cheater method for removing dupes during a Bulk Insert or BCP.

SQL Server Integration Services. The 2005 replacement for DTS.

Heh... thanks, Gail... I knew that much. I just meant to say that I don't use it anymore than I use DTS... which is on a totally adhoc basis. I normally do all my ETL with BULK INSERT or BCP because of it's speed and the fact that it can all be done in a proc. Yeah, I know... I can call DTS jobs from a proc... I just don't feel like going there because of the speed thing, again.

--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)

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

Jeff Moden (1/23/2008)
don't really see the point of an unique index with ignore duplicates on. Am I missing something?
It's a cheater method for removing dupes during a Bulk Insert or BCP.

*shudder*

I just meant to say that I don't use it anymore than I use DTS... which is on a totally adhoc basis.

Me neither. I've used it for a stats-gathering job, but nothing else. Used to use DTS quite a bit, but not recently. Some of the devs are going gaga over SSIS, to the point that I'm having to remiond them that sometimes a hammer is not the best tool for the job. (Invoke SSIS package from proc called by front end to do a fairly simple update)

Wasn't sure how familiar you were with the pieces of 2005, since I know you're still mainly using 2000. (or are you upgrading now?)

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