An Urgent Ad Hoc Report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Yakov Shlafman » An Urgent Ad Hoc Report

39 posts, Page 1 of 4

1 2 3 4 »»»

An Urgent Ad Hoc Report

Rate Topic

Display Mode

Topic Options

Author

Message

Yakov Shlafman

Posted Tuesday, March 16, 2010 10:22 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:20 AM
Points: 33, Visits: 324

Comments posted to this topic are about the item An Urgent Ad Hoc Report

Post #884387

qld_dba

Posted Tuesday, March 16, 2010 11:59 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 7:07 PM
Points: 46, Visits: 55

Well done Yakov.

This is an excellent example of what our everyday work load is like.
Are you listening newbies? And even better the solution is a great example of the use of 2005 CTE features.

5 stars to you Yakov. Wink

Regards, Greg.

Post #884422

SQLRNNR

Posted Wednesday, March 17, 2010 12:09 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

Thanks for the article Yakov.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #884425

rob mcnicol

Posted Wednesday, March 17, 2010 12:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 7:30 AM
Points: 149, Visits: 553

great article - thanks heaps.

oh, might it benefit from the addition of this little tweak near the start?

'you tell your boss he's off his rocker for suggesting that you work past your usual time but you will happily do the report next morning on receipt of a box of chocolates and a nice bunch of flowers by way of apology'

Tongue

Post #884430

roachw

Posted Wednesday, March 17, 2010 3:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2012 6:46 AM
Points: 3, Visits: 59

I must be missing somthing here, I'm no SQL expert (in fact I'd never come accross CTE's before) but that seems like an awfull lot of code for what, on the face of it, seems like a pretty straight forward report. There's reams of it man!! I'd have been there till midnight producing that lot Smile

Not only would I have missed dropping the kids off I would have missed picking em up too. Might look a bit closer at the CTE's though...

Post #884465

irozenberg

Posted Wednesday, March 17, 2010 4:19 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 8:44 PM
Points: 30, Visits: 145

Thanks for article, Yakov.
Would not usually bother with sample data in a sandbox environment - usually would copy entire tables and create views with 1000 rows,
this would prevent NASTY surprises later on. Subject to having access to PRODUCTION environment and be allowed to copy data across.
If you do not have either - politely explain you manager that you did everything you COULD tonight and he will get his urgent report first thing in the morning. Tongue

SPASIBO

Post #884510

murraydan885

Posted Wednesday, March 17, 2010 5:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 5:21 AM
Points: 1, Visits: 6

Ad Hoc Report

Alternatively you purchase Tableau Software and the whole effort requires about 60 seconds. Alternative 2, your user is able to create their own ad hoc report with Tableau.

Post #884552

Brett Berger

Posted Wednesday, March 17, 2010 6:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 2, Visits: 485

Another option for ensuring the length of the individual dateparts is to left pad the value and retain the rightmost characters, thereby eliminating the case statement. This has come in handy in various situations.

Thanks, Brett

Declare @x int
Set @x = 1
Select Right('00' + Convert(varchar(2), @x), 2)

Set @x = 12
Select Right('00' + Convert(varchar(2), @x), 2)

Post #884607

l543123

Posted Wednesday, March 17, 2010 8:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:27 AM
Points: 42, Visits: 170

"First of all, you run a script to find Contractor and Expense tables across all databases on you production server"

sorry if I am wrong, but why are you searching all databases and if you dont know the tables then what are you searching Smile

also I think its a lot of code for something like,
select contractor name, max of expense and date from contractor
inner join expense on expense.contract_id = contrator.id"

Post #884690

yakov shlafman-228008

Posted Wednesday, March 17, 2010 8:50 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 05, 2010 2:07 PM
Points: 17, Visits: 281

Thanks SSC Rookie for your input.
I do describe a real environment. When I got a request like this
and I do not have any input or explanation this is my only choice.
The worst case scenario if Contractor table is not called Contractor.
Then no options until you can get table names.
Smile

Post #884745

« Prev Topic | Next Topic »

39 posts, Page 1 of 4

1 2 3 4 »»»

Permissions