Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

An Urgent Ad Hoc Report Expand / Collapse
Author
Message
Posted Tuesday, March 16, 2010 10:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:23 PM
Points: 33, Visits: 386
Comments posted to this topic are about the item An Urgent Ad Hoc Report
Post #884387
Posted Tuesday, March 16, 2010 11:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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.
Regards, Greg.
Post #884422
Posted Wednesday, March 17, 2010 12:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:30 PM
Points: 21,290, Visits: 14,982
Thanks for the article Yakov.



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


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
Posted Wednesday, March 17, 2010 12:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:45 PM
Points: 149, Visits: 558
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'

Post #884430
Posted Wednesday, March 17, 2010 3:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 8:28 AM
Points: 3, Visits: 60
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 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
Posted Wednesday, March 17, 2010 4:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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.

SPASIBO
Post #884510
Posted Wednesday, March 17, 2010 5:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 17, 2010 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:03 AM
Points: 36, Visits: 517
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
Posted Wednesday, March 17, 2010 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:52 AM
Points: 42, Visits: 172
"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

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
Posted Wednesday, March 17, 2010 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 5, 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.
Post #884745
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse