Jeff Moden

Forum Replies Created

Viewing 15 posts - 42,931 through 42,945 (of 59,063 total)

RE: Returning accumulated totals from multiple rows

kylejw (7/18/2009)
Thanks guys,
Yes, I've used Group By but until now I've never looked at Sum from a SQL perspective. Like everything, its quite obvious when you know how 🙂
No problem......
--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)

July 18, 2009 at 7:17 am

#1026548
RE: Calculate a Percentage

dave b (7/17/2009)
Yeah...I do understand...kinda. 😉 I like the CTE solution. I was just thinking that there was a simpler way with less code.
Heh... nah... I was talking about why...
--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)

July 18, 2009 at 7:05 am

#1026546
RE: How To Format Phone Number

tsmith (7/18/2009)
Thank you for your answer. I will try it on Monday. I'm sorry for the double post, but after I asked about it here, I thought maybe I should...
--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)

July 18, 2009 at 7:02 am

#1026544
RE: Not sure how to get my subquery right

Of course, this hasn't been tested because I don't have the table structures nor the test data... but it should be close...
select FiscalMonthDTTM, left(datename(month,FiscalMonthDTTM),3) Mth,
Sum(case when TimeType='YVW Crib' then...
--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)

July 17, 2009 at 11:21 pm

#1026506
RE: Calculate a Percentage

dave b (7/17/2009)
Thanks Bruce! I'll give it a try.
I'm just making sure... you understand why Bruce's solution will work, correct?
--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)

July 17, 2009 at 11:14 pm

#1026505
RE: Stripping all non-numeric chars from phone number and then formatting it.

tsmith (7/17/2009)
Hi. I would like to add a problem that we have at work. Writing a query is not an option. The report we need to generate runs off...
--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)

July 17, 2009 at 11:11 pm

#1026504
RE: Group by 15 minutes not Hour

TRACEY (7/17/2009)
How to sort this by every 15 minutes. Or Every 10 minutes.
I'll have to remember that's how you ask for a "nudge" in the future.
--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)

July 17, 2009 at 11:07 pm

#1026503
RE: How To Format Phone Number

Almost forgot... if you don't know what a Tally table is or how it replaces a loop, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--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)

July 17, 2009 at 10:53 pm

#1026500
RE: How To Format Phone Number

tsmith (7/17/2009)
Hi. Since you are talking about phone number formatting, I would like to add a problem that we have at work. Writing a query is not an option. 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)

July 17, 2009 at 10:51 pm

#1026499
RE: Group by 15 minutes not Hour

TRACEY (7/17/2009)
I take a look, i only wanted the command not the entire program 🙂
Heh... understood. Would you have been able to figure it out if I were to...
--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)

July 17, 2009 at 9:51 pm

#1026497
RE: Use sp_MSforeachtable to gen COUNT(*) for each table in MyDBname

Now... if you want to know a bit more about each table including the row counts, try this...
--_______________________________________________________________________________________________________________________
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all...
--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)

July 17, 2009 at 9:45 pm

#1026496
RE: Use sp_MSforeachtable to gen COUNT(*) for each table in MyDBname

You bet. Thanks for the feedback, TJ.
But I wasn't the first one to suggest it on this thread. Go back and look at what dobrzak wrote earlier. ...
--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)

July 17, 2009 at 9:31 pm

#1026493
RE: Read a non-delimited file and insert into table

It's not possible with the current information because there's nothing to identify the difference between the street and the city.
--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)

July 17, 2009 at 4:20 pm

#1026473
RE: remove duplicate rows

Heh... and the read up on proper table design so you can tell the folks who designed that table how to fix it. 😛
--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)

July 17, 2009 at 4:17 pm

#1026472
RE: sql query

TAman (7/17/2009)
I need to create a report to show a list of employees that have made an entry on a file more than once within any 60 minute window. ...
--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)

July 17, 2009 at 4:14 pm

#1026471

Viewing 15 posts - 42,931 through 42,945 (of 59,063 total)