The Nerd Herd - SQL guys out there?

[Lancer-AM]

So half of my day is spent in a DB at work. We use SQL Server along with SSIS and SSAS a lot. Wondering if there are any fellow DBA's out in the GTO lands. Figured it would be cool to share some really f'ed up queries and such that you have written. You know the stuff that when you are writing it you know if someone looks at it they will go :cursing::banghead:

[05ls7gto]

I thought this was about car audio... SQL = Sound Quality Level; to me.

[Lancer-AM]

Sorry even nerdier than that I'm afraid.

[derf]

I don't do databases but I really think I belong in the Nerd Herd. Right now I'm working on a virtual memory mapping scheme for a multiprocessing system on the B2 bomber. If that isn't nerdy I don't know what is.

BTW, the smiley zip file I sent in has a nerd smiley...

[Lancer-AM]

:eek: Why did you have to let us know that? Are the feds reading this now? This is top secret isn't it? What have you done?!?!?!?! :secret:

[derf]

:eek: Why did you have to let us know that? Are the feds reading this now? This is top secret isn't it? What have you done?!?!?!?! :secret:

Relax. I working on an unclassified system. You'll know when I start working on something that is classified. You won't hear a peep from me about what it is.

[gage]

I run an online madden league using a php/mysql setup I did myself in my spare time..... yes that would be SQL for fun lol

For $$$ I work in the game industry doing gameplay programming in C++ and a myriad of scripting languages.

and someone wanted a crazy sql statement:

SELECT COALESCE(krstats.teamid,defstats.teamid,
prstats.teamid,rushstats.teamid,recvstats.teamid) as teamid,
COALESCE(krstats.name,defstats.name,
prstats.name,rushstats.name,recvstats.name) as name, p.playerid,
CONCAT(p.firstname,' ',p.lastname) as playername,
(COALESCE(krstats.kr_yds,0)+COALESCE(defstats.def_ yds,0)+COALESCE(defstats.def_ret,0)+
COALESCE(prstats.pr_yds,0)+COALESCE(rushstats.rush _yds,0)+COALESCE(recvstats.recv_yds,0)) as total,
COALESCE(rushstats.rush_yds,0) as rushsum, COALESCE(recvstats.recv_yds,0) as recvsum,
COALESCE(krstats.kr_yds,0) as krsum, COALESCE(prstats.pr_yds,0) as prsum,
COALESCE(defstats.def_yds,0) as fumsum, COALESCE(defstats.def_ret,0) as intsum
FROM ld_players p
LEFT JOIN (SELECT t.teamid, t.name, p.playerid, SUM(def_yds) as def_yds, SUM(def_ret) as def_ret
FROM ld_players p INNER JOIN ld_defensestats ks ON p.playerid = ks.playerid
INNER JOIN ld_teamstats ts ON ks.teamstatsid = ts.teamstatsid
INNER JOIN ld_gamestats gs ON gs.statsid = ts.statsid
INNER JOIN ld_teams t ON t.teamid=ts.teamid
INNER JOIN ld_schedules s ON s.scheduleid = gs.scheduleid
WHERE s.season = %s AND {$schedule} AND {$teamid}
GROUP BY p.playerid) as defstats ON defstats.playerid = p.playerid
LEFT JOIN (SELECT t.teamid, t.name, p.playerid, SUM(kr_yds) as kr_yds
FROM ld_players p INNER JOIN ld_kickreturnstats ks ON p.playerid = ks.playerid
INNER JOIN ld_teamstats ts ON ks.teamstatsid = ts.teamstatsid
INNER JOIN ld_gamestats gs ON gs.statsid = ts.statsid
INNER JOIN ld_teams t ON ts.teamid=t.teamid
INNER JOIN ld_schedules s ON s.scheduleid = gs.scheduleid
WHERE s.season = %s AND {$schedule} AND {$teamid}
GROUP BY p.playerid) as krstats ON krstats.playerid = p.playerid
LEFT JOIN (SELECT t.teamid, t.name, p.playerid, SUM(pr_yds) as pr_yds
FROM ld_players p INNER JOIN ld_puntreturnstats ks ON p.playerid = ks.playerid
INNER JOIN ld_teamstats ts ON ks.teamstatsid = ts.teamstatsid
INNER JOIN ld_gamestats gs ON gs.statsid = ts.statsid
INNER JOIN ld_teams t ON ts.teamid=t.teamid
INNER JOIN ld_schedules s ON s.scheduleid = gs.scheduleid
WHERE s.season = %s AND {$schedule} AND {$teamid}
GROUP BY p.playerid) as prstats ON prstats.playerid = p.playerid
LEFT JOIN (SELECT t.teamid, t.name, p.playerid, SUM(rush_yds) as rush_yds
FROM ld_players p INNER JOIN ld_rushingstats ks ON p.playerid = ks.playerid
INNER JOIN ld_teamstats ts ON ks.teamstatsid = ts.teamstatsid
INNER JOIN ld_gamestats gs ON gs.statsid = ts.statsid
INNER JOIN ld_teams t ON t.teamid=ts.teamid
INNER JOIN ld_schedules s ON s.scheduleid = gs.scheduleid
WHERE s.season = %s AND {$schedule} AND {$teamid}
GROUP BY p.playerid) as rushstats ON rushstats.playerid = p.playerid
LEFT JOIN (SELECT t.teamid, t.name, p.playerid, SUM(recv_yds) as recv_yds
FROM ld_players p INNER JOIN ld_receivingstats ks ON p.playerid = ks.playerid
INNER JOIN ld_teamstats ts ON ks.teamstatsid = ts.teamstatsid
INNER JOIN ld_gamestats gs ON gs.statsid = ts.statsid
INNER JOIN ld_teams t ON t.teamid=ts.teamid
INNER JOIN ld_schedules s ON s.scheduleid = gs.scheduleid
WHERE s.season = %s AND {$schedule} AND {$teamid}
GROUP BY p.playerid) as recvstats ON recvstats.playerid = p.playerid
WHERE (COALESCE(krstats.kr_yds,0)+COALESCE(defstats.def_ yds,0)+COALESCE(defstats.def_ret,0)+
COALESCE(prstats.pr_yds,0)+COALESCE(rushstats.rush _yds,0)+COALESCE(recvstats.recv_yds,0)) > 0
AND {$playertype}
GROUP BY p.playerid
ORDER BY {$sort} {$order}, total {$order}

Calculates all purpose yards (passing rushing receiving interceptions etc) per player in the league, with some php string injection for customizing... Not nearly as bad as some of the sql i saw working in e-commerce but this one i wrote :rolleyes:

[Lancer-AM]

Interesting, I would love to see the tables, I think it could be cleaned up. You were nice to your DB an indexed a lot of that right? :-P


FUNCTION [dbo].[ctvf_Get_Cycle_Time_GRBO_AMER]
(
@Incoming_Ship_Date_Quarter varchar(8),
@Incoming_Ship_Date_Week int,
@Percentage_To_Keep int
)
RETURNS
@Result_Table TABLE
(
CT_OE_IP float (1)
, CT_IP_SHP float(1)
, CT_SHP_POD float(1)

)
AS
BEGIN


declare @Count_Total_Orders int
select @Count_Total_Orders = sum(order_count) from dbo.Final_Table_CT_GBL

where

Ship_Date_Quarter like @Incoming_Ship_Date_Quarter and Ship_Date_Week Like @Incoming_Ship_Date_Week
and segment_desc in ('Large Enterprise','Public') and Region_ID = 1

declare @Total_Number_Of_Orders_To_Keep int
set @Total_Number_Of_Orders_To_Keep = cast (((cast(@Count_Total_Orders as float) * cast(@Percentage_To_Keep as float)) / 100) as float)




declare @Running_Order_Count int
declare @Bottom_CT_Total int
declare @Current_Order_Count int
set @Running_Order_Count = 0
set @Current_Order_Count = 0
declare @CT_OE_IP float (1)
declare @CT_IP_SHP float (1)
declare @CT_SHP_POD float (1)
declare @Order_Count int
declare @Ship_Date_Quarter varchar(8)
declare @Ship_Date_Week int
declare @Segment_Desc varchar(50)

set @CT_OE_IP = 0
set @CT_IP_SHP = 0
set @CT_SHP_POD = 0
set @Order_Count = 0
set @Ship_Date_Quarter = ''
set @Segment_Desc = ''


declare @Das_Table table (
Auto_ID int identity(1,1) primary key
, CT_OE_IP float (1)
, CT_IP_SHP float (1)
, CT_SHP_POD float (1)
, Order_Count int
, Ship_Date_Quarter varchar(8)
, Ship_Date_Week int

)


declare das_cursor cursor for
select
CT_OE_IP
, CT_IP_SHP
, CT_SHP_POD
, Order_Count
, Ship_Date_Quarter
, Ship_Date_Week
from dbo.Final_Table_CT_GBL

where

Ship_Date_Quarter like @Incoming_Ship_Date_Quarter and Ship_Date_Week like @Incoming_Ship_Date_Week

and segment_desc in ('Large Enterprise','Public')and Region_ID = 1
order by (CT_OE_IP + CT_IP_SHP) desc, CT_IP_SHP, CT_SHP_POD, CT_OE_IP, Order_Count, Ship_Date_Quarter, Ship_Date_Week, Segment_Desc
open das_cursor
fetch next from das_cursor into @CT_OE_IP, @CT_IP_SHP, @CT_SHP_POD, @Order_Count, @Ship_Date_Quarter, @Ship_Date_Week

while @@FETCH_STATUS = 0
BEGIN
if (@Running_Order_Count < (@Total_Number_Of_Orders_To_Keep))
begin
insert into @Das_Table(CT_OE_IP, CT_IP_SHP, CT_SHP_POD, Order_Count, Ship_Date_Quarter, Ship_Date_Week)

values (@CT_OE_IP, @CT_IP_SHP, @CT_SHP_POD, @Order_Count, @Ship_Date_Quarter, @Ship_Date_Week)

set @Running_Order_Count = @Running_Order_Count + @Order_Count
end
fetch next from das_cursor into @CT_OE_IP, @CT_IP_SHP, @CT_SHP_POD, @Order_Count, @Ship_Date_Quarter, @Ship_Date_Week

END
close das_cursor
deallocate das_cursor



declare @Whee_Order_Count int
select @Whee_Order_Count = sum(order_count) from @Das_Table

update @Das_Table set Order_Count = Order_Count - (@Whee_Order_Count - @Total_Number_Of_Orders_To_Keep) where auto_id in (select max(auto_id) from @Das_Table)

select @Whee_Order_Count = sum(order_count) from @Das_Table


-----------------------------ONTO THE NEXT BIT! ---------------------
insert into @Result_Table
select
cast(sum(CT_OE_IP * Order_Count) as float(1) ) / cast(@Whee_Order_Count as float(1)) as CT_OE_IP
, cast(sum(CT_IP_SHP * Order_Count) as float(1) ) / cast(@Whee_Order_Count as float(1)) as CT_IP_SHP
, cast(sum(CT_SHP_POD * Order_Count) as float(1) ) / cast(@Whee_Order_Count as float(1)) as CT_SHP_POD
from @Das_Table





RETURN
END


This is one I wrote recently. It calculate the cycle time of orders in a table, you can pass in the percentage of orders you want to keep to calculate the percentiles. EG you pass in 5 and it keeps only the worst performing 5% of all all orders showing you the 95th Percentile.

[derf]

I don't really follow those SQL statements. But I still want to be part of the Nerd Herd. If it helps, I can post up the template class I wrote for handling fractional numbers on a processor with no floating point capability. Is that nerdy enough for y'all?

[camarochevy1970]

I work with the Jet database structure all day

[Lancer-AM]

HAHA You can be Nerd Herd too. Schuff is currently investigating the user groups We might have to creat a Nerd Herd user group.

[Lancer-AM]

The Nerd Herd official hangout
http://www.gastiresoil.com/group.php?groupid=3

[HRJ]

Usergroups are easy BUT.....be careful with permissions or you might get a nasty surprise....lol. "O hai...I'm banned but I can view the mod section now!"


lolz

[Lancer-AM]

Usergroups are easy BUT.....be careful with permissions or you might get a nasty surprise....lol. "O hai...I'm banned but I can view the mod section now!"

lolz

:eek: Then everyone would see us talking about the things we are going to do to HRJ!!!:eek:
:p

Not cool, must know the story behind this one and what went wrong. :)

[MuhThugga]

I handle communications for the IT department at my job.

They continuously get frustrated because they always want the communications to explain exactly what they are doing to what servers and so on, and I respond with, "So, the network will be down for an hour between x:xx and y:yy? All I need to know. Thanks."

[HRJ]

:eek: Then everyone would see us talking about the things we are going to do to HRJ!!!:eek:
:p

Not cool, must know the story behind this one and what went wrong. :)


We just had an issue once where the "banned" usergroup was able to view the mod section. Just something that had been missed. When DY made me Admin I played with a couple of usergroups and saw how easy it was to miss something.

Whatever one you build, just look at it real close before it goes live....lol.

[YaXMaN]

Interesting, I would love to see the tables, I think it could be cleaned up. You were nice to your DB an indexed a lot of that right? :-P

This is one I wrote recently. It calculate the cycle time of orders in a table, you can pass in the percentage of orders you want to keep to calculate the percentiles. EG you pass in 5 and it keeps only the worst performing 5% of all all orders showing you the 95th Percentile.

You must learn to not to use cursors, GrassHoppah.

[Robofuzz]

I like Star Trek.

[YaXMaN]

I like Star Trek.

Nerd. Oh wait, I only call MacDogg a nerd.

MacDogg!!! You're a the bestest nerd this forum will ever have!!!

[Robofuzz]

Usergroups are easy BUT.....be careful with permissions or you might get a nasty surprise....lol. "O hai...I'm banned but I can view the mod section now!"


lolz
We're actually talking about Social Groups, not Usergroups. But your warning is well heeded.

[HRJ]

We're actually talking about Social Groups, not Usergroups.
Whoops!

:D

[Lancer-AM]

You must learn to not to use cursors, GrassHoppah.
I know, it makes my brain hurt to use them too. Unfortunatly it is the difference between what I do and what IT does, I have to get the job done. :p Most of my DB work is (I will start spouting the acronyms because Yax know what I am talking about) prototyping for CSS's. I have a small amount of time to spit something out before moving on to something else. Cursors unfortunatly facilitate a down and dirty quick way to make something work. Doesn't matter too much any more.

SRI is killing all of the CSS servers so I won't have access to my precious SQL servers for much longer. I don't think the business understands the true impact of what is coming.

[YaXMaN]

I know, it makes my brain hurt to use them too. Unfortunatly it is the difference between what I do and what IT does, I have to get the job done. :p Most of my DB work is (I will start spouting the acronyms because Yax know what I am talking about) prototyping for CSS's. I have a small amount of time to spit something out before moving on to something else. Cursors unfortunatly facilitate a down and dirty quick way to make something work. Doesn't matter too much any more.

SRI is killing all of the CSS servers so I won't have access to my precious SQL servers for much longer. I don't think the business understands the true impact of what is coming.

You are wise in these matters. It's funny I didn't know what you did and we never really talked about it. We need to.

[Lancer-AM]

I dabbled in SQL before but when I took on this Project Manager gig in Feb I got a lot of it dumped on me. Luckily some of the guys on the team are great with SQL and have taught me a lot. SSAS is my next major project to tackle but like I said I am losing my servers so I will most likely do this at home. I started outputting things through SSAS into a report manager and it was heaven but I don't have much time to play with it.

[Burnsy]

F'n DBA's.....Always breakin' mah servers.

[YaXMaN]

I dabbled in SQL before but when I took on this Project Manager gig in Feb I got a lot of it dumped on me. Luckily some of the guys on the team are great with SQL and have taught me a lot. SSAS is my next major project to tackle but like I said I am losing my servers so I will most likely do this at home. I started outputting things through SSAS into a report manager and it was heaven but I don't have much time to play with it.

Yah. Know where you're coming from.

I've put two full years into building cubes with Analysis Services 2005 against a multi-hundred-million row datawarehouse which I architected and built all the ETLs. Refreshed data had to come out by 10am each day. It was a pain in ass, and even with all the lessons learned, I still don't feel I'm an expert at SSAS... so much to learn there. Been working with Reporting Services 2000, 2005 and 2008 report development and administration continuously since 2000... All great technologies there. SharePoint 2007 Reporting Services Integration, Report Builder and Excel Services are great as well. Used all of them as part of a consolidated reporting presentation repository / workflow layer.

It's a never ending process to keep up technologies, and no matter how far you go with it, there always somebody more knowledgeable about them. It's why I want to get out that game... apply the technologies to specific areas of business processes that sorely lack them and become an expert at rationalizing them and build true business intelligence around them. There's where some of the money lives... definitely not in the commoditized development space.

[Lancer-AM]

:popcorn:

Should have never said all of that Yax :D You just became my new best friend. I gots lots of questions for you.

[gage]

Yea i was nice and indexed the tables :) The new db plans to use more views to help consolidate common things

Joined da Nerd Herd

http://www.gametrailers.com/game/darksiders/5153

Thats my real pride though, make sure to scroll down a bit to see the Mark Hamill VO vid :D

[YaXMaN]

:popcorn:

Should have never said all of that Yax :D You just became my new best friend. I gots lots of questions for you.

LOL d'oh!!! But since I've been out of work past couple of months, I think I forgot most of it... all of it... who are you?
:D

[MacDogg]

Nerd. Oh wait, I only call MacDogg a nerd.

MacDogg!!! You're a the bestest nerd this forum will ever have!!!

Awww, thanks, Dave. I didn't know you had it in you!

If 'subject of thread'='nerd'
then 'bestest member' = 'MacDogg'
else 'There are no bestest members'

[gage]

bool winnarIsYou = (name==String("gage")) ? true : false;

[Lancer-AM]

Select top 1 [nerd]
From [tblGasTiresOil.com]
Where
[Nerdiness] = max[Nerdiness]

Results
1 row returned
Nerd
MacDogg

[derf]

bool winnarIsYou = (name==String("gage")) ? true : false;
Why would you have your ?: operator return the same values that the conditional expression returns? Seems a bit superfluous to me. Though I did have to work in an environment that would have forced me to write:

bool winnarIsYou;

if (string("gage") == name) {
winnarIsYou = true;
} else {
winnarIsYou = false;
}

Yes, the braces were required, as is putting the constant on the LHS (to catch accidental assignments when you forget the second "="). Not only that, the higher ups in the group didn't use C as their first language and got confused by the ?: operator so they banned it's use. :(

[MacDogg]

Select top 1 [nerd]
From [tblGasTiresOil.com]
Where
[Nerdiness] = max[Nerdiness]

Results
1 row returned
Nerd
MacDogg

Waffle! :)

[YaXMaN]

Why would you have your ?: operator return the same values that the conditional expression returns? Seems a bit superfluous to me. Though I did have to work in an environment that would have forced me to write:

bool winnarIsYou;

if (string("gage") == name) {
winnarIsYou = true;
} else {
winnarIsYou = false;
}

Yes, the braces were required, as is putting the constant on the LHS (to catch accidental assignments when you forget the second "="). Not only that, the higher ups in the group didn't use C as their first language and got confused by the ?: operator so they banned it's use. :(

Aw come on guys! Are we nerds here?

bool winnarIsYou=(string("gage")==name)

[Trents_goat5.7]

I have no clue what the hell you guys are talking about.

[derf]

Aw come on guys! Are we nerds here?

bool winnarIsYou=(string("gage")==name)

That's the way I do it. But one place where I worked, the person who wrote the coding standard didn't allow the results of the boolean comparison to the variable. While all of us who actually coded the software understood what was going on, the lead (who mostly did paperwork) got confused by it and wouldn't allow it so we had to build the if/else structure. :oldfogey: Of course, the lead had been programming in JOVIAL for 20+ years and wouldn't let you initialize a variable in the declaration in C/C++ because it didn't work the same as it did in JOVIAL and, again, they got confused. :cursing:

Where I am now, they're telling the old JOVIAL programmers to get over it since we're using C/C++. :pwnt:

[gage]

jovial, had to wiki that bad boy, once I read NORAD and C-130 it made sense why I had never heard/used it

And yax how dare you miss your statement completion semi-colon! It might be superfluous to fill in the ternary statement but at least it would compile! :cheers:

[gage]

I have no clue what the hell you guys are talking about.
START

LEA TRENTMSG,A1
MOVE.B #14,D0
TRAP #15 ; Print screen plz

TRENTMSG DC.B 'Trent is full of lolz today',0

MOVE.B #9,D0
TRAP #15 ; HALT! HAMERZEIT!

END START

[Lancer-AM]

This thread delivers

http://www.raykonline.com/csstinderbox/img/geek-nerd.png

[derf]

jovial, had to wiki that bad boy, once I read NORAD and C-130 it made sense why I had never heard/used it

Yup, if you work on planes for the gubment, you get exposed to JOVIAL. And that's what I've done for a significant portion of my career.

Fortunately, the Air Force has done upgrades that required dragging their computers into the 1980's so they could get rid of JOVIAL.

[Mickey21]

I can has nurdburger?

[gage]

I dont think you want a nurdburger :p

That silly gubmint technology for ya.... but even worse that the superiors want the other code to look like an older language :banghead: