Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,941 through 35,955 (of 59,099 total)

RE: Converting Quartz.Net time columns to datetime

This will do it...
DECLARE @Ticks BIGINT
SELECT @Ticks = 634262130000000000
SELECT Converted = CAST(@Ticks/864000000000.0 - 693595.0 AS DATETIME)
--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)

November 25, 2010 at 12:18 am

#1255455
RE: Converting Rows into Columns

Craig Farrell (11/23/2010)
For a fast explaination, a fast answer. You're looking for the PIVOT command. Quite handy.
If you'd like more tested code, please see the first link in...
--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)

November 24, 2010 at 11:53 pm

#1255450
RE: Are the posted questions getting worse?

It's a little out of character for me but I know you'll recognize the rhetoric. I just couldn't resist... it was too perfect. Enjoy... 😛
http://www.sqlservercentral.com/Forums/Topic1025567-392-1.aspx#bm1026289
--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)

November 24, 2010 at 8:10 pm

#1255422
RE: Need help building a query.

{edit} wrong post.
--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)

November 24, 2010 at 8:08 pm

#1255420
RE: Are the posted questions getting worse?

LutzM (11/24/2010)
To all of you folks in the U.S.:
May the turkey be tender and juicy and the people you're celebrating with be the ones you love and care about.
My very...
--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)

November 24, 2010 at 8:01 pm

#1255418
RE: Remove duplicate records based on record id, annoying GROUP BY statement

petervdkerk (11/24/2010)
I want to remove duplicates from the DB. here's my code for that:
DECLARE @Count int
DECLARE @companyname nvarchar(50)
DECLARE @id int
DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT companyname, Count(*) - 1
FROM prospects
GROUP BY...
--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)

November 24, 2010 at 7:46 pm

#1255416
RE: Need help building a query.

CELKO (11/24/2010)
Can I re-write your DDL to ISO-11179 rules, add keys. I hope you know why NUMERIC(s,p) is not like DECIMAL(s,p). T-SQL has a DATE data type now and...
--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)

November 24, 2010 at 7:14 pm

#1255414
RE: Need help building a query.

Remember the old ways before CROSS APPLY and ROW_NUMBER?
In days of old,
When Knights were bold,
And Windowing was not invented,
He wrapped his code
In a familiar mode
And RBAR was prevented. 😀
To wit...
CREATE...
--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)

November 24, 2010 at 7:01 pm

#1255413
RE: Are the posted questions getting worse?

WayneS (11/24/2010)
Craig Farrell (11/24/2010)
CirquedeSQLeil (11/24/2010)
jcrawf02 (11/24/2010)
Should we point out that his view solution doesn't *just* give you the duplicates? 😉
Yes - fire away!!!!!!!!!
I just ended up having to thump Joe...
--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)

November 24, 2010 at 5:12 pm

#1255405
RE: Need help building a query.

Ram2002 (11/24/2010)
Thanx Criag. I appreciate all you ppl in this post time.
This was the first post so went wrong. Next time will be in appropriate forum.
Except for it being...
--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)

November 24, 2010 at 4:17 pm

#1255386
RE: Remove duplicate records based on record id, annoying GROUP BY statement

CELKO (11/24/2010)
>> I want to remove duplicates from the DB. here's my code for that: <<
Your code is terrible and using a cursor is really bad SQL programming. What you...
--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)

November 24, 2010 at 3:11 pm

#1255357
RE: Big table 50MB containing just 1000 records ( a few small fields each record)

Iulian -207023 (11/24/2010)
Hi Jeff, it was about 50MB - not sure exactly.
Now the size is 0.28MB - which is great.
I dropped one unclustered index and left only the...
--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)

November 24, 2010 at 2:45 pm

#1255350
RE: Dynamic SQL issue

Heh... not to worry, Lutz. You've seen the mess I can make of things if I PBC (Post Before Coffee). 😀
--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)

November 24, 2010 at 2:38 pm

#1255347
RE: Logging by TSQL

Ray K (11/23/2010)
Please do NOT waste people's time by cross-posting!!!
All: do not respond to this post; please use http://www.sqlservercentral.com/Forums/Topic1025402-391-1.aspx.
Whoa! No need to yell, Ray. And considering the OP...
--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)

November 23, 2010 at 10:39 pm

#1254891
RE: Need help with some code..

WayneS (11/22/2010)
jguerra2 (11/22/2010)
Hey thanks alot. Havent learned and thing about Cross Tabs and Pivot Tables. I am checking you article out now 🙂
Thanks for the code. I will try...
--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)

November 23, 2010 at 10:36 pm

#1254890

Viewing 15 posts - 35,941 through 35,955 (of 59,099 total)