Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Urgent Ad Hoc Report


An Urgent Ad Hoc Report

Author
Message
Yakov Shlafman
Yakov Shlafman
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 516
Comments posted to this topic are about the item An Urgent Ad Hoc Report
qld_dba
qld_dba
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
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.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21105 Visits: 18259
Thanks for the article Yakov.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

rob mcnicol
rob mcnicol
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 679
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'

:-P
roachw
roachw
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 61
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...
irozenberg
irozenberg
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 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. :-P

SPASIBO
murraydan885
murraydan885
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Brett Berger
Brett Berger
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 536
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)
l543123
l543123
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 175
"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"
yakov shlafman-228008
yakov shlafman-228008
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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.
:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search