@Zane Just do what 3 companies back did, they used a developer license in production. I assume ignorance versus malice but when the auditors came that was an expensive Whoopsie to fix since they used Enterprise Edition features
@AaronBertrand @Lamak And you can pretty easily always use DAY, CAST to decimal/divide by 365.0 etc. to meet whatever less obvious business requirement you have for age of something. It's really too bad that the complete scope of date issues are just so difficult for most people to comprehend.
Just last night I was cleaning up a client query with CONVERT(CHAR(10), datetimecol, 101) ORDER BY CONVERT(CHAR(10), datetimecol, 101) - noting that January was coming before October... (Conversion they had done to remove the time part).
@AaronBertrand Because no one uses yyyy-mm-dd (except the Japanese). I actually like that Oracle's DD-MMM-YY like airline tickets - beat the ambiguity with letters - it's what we do in our app which is internationalized and you look at things in the international setting for the site to avoid sharing reports about a country which are different for different users. Either way it's confusing, but this is what we picked.
@Lamak There's nothing to understand, we use dates everyday, there's nothing to understand. Just like we walk everyday and there's never a reason to study the human body
@billinkc I really started to get into yyyymmdd for filenames years ago, and you'd think with the proliferation of people with thousands of digital photos generated by their phones would appreciate that kind of convention.
@CadeRoux the complaint I hear? "It's too hard to read when it's all munged together like that." I kind of agree, 20121121 takes an extra second to parse.
The first nine months of the year are a little easier on the eyes and brain.
@CadeRoux for DATE that is perfect, and always safe. Sadly most people still use the old types because they're stubborn or stupid, where yyyy-mm-dd can fail miserably.
I'm using EF with SQL Server and when I'm fetching a DateTime field that is DateTime on the MSSQL and on EF, I would get something like this:
"UpdateTime":"\/Date(1358716865533+0200)\/"
I have no idea what format is this (only that it shows some time with GMT+2 offset).
Any suggestions?
Seco...
It's funny you don't often hear experts complaining about this garbage - I suspect it's because they're wise enough to not touch it with a 10-foot pole.
I commented on a couple of EF/CodeFirst questions yesterday something like "this is not the last limitation this thing will place on you. Are you sure you want to keep using it?"
@alex this is a community volunteer forum. As such, it seems to me that it's not appropriate to initiate professional contact unless invited to do so. — swasheck15 secs ago
The code I have is
SUM(CASE WHEN RRDD NOT LIKE '12%' AND RRDD NOT LIKE '13%' AND RRDD NOT LIKE '16%' AND RRDD NOT LIKE '17%' AND RRDD NOT LIKE '2706%' AND RRDD NOT LIKE '2707%' AND RRDD NOT LIKE '2331%' THEN CAST([2011 Total] AS FLOAT)*CASE WHEN IsNumeric([DUR_IN_HOURS]) =1 THEN CAST([DUR_IN_H...
I am embarrassed to say this is my weak spot in SQL Server, I cannot figure out how to get this code to function properly. It is a few different codes in one:
SUM(CASE WHEN RRDD Not Like '12%' AND RRDD Not Like '13%' AND RRDD Not Like '16%' AND RRDD
Not Like '17%' AND RRDD Not Like '2706%' AND R...
Well he seems to have fixed that code by adding the parens, and is now getting a different error (sounds like the error might be coming from a different line of code than the one he posted).
You are missing an END on the first CASE:
SUM(CASE
WHEN RRDD NOT LIKE '12%'
AND RRDD NOT LIKE '13%'
AND RRDD NOT LIKE '16%'
AND RRDD NOT LIKE '17%'
AND RRDD NOT LIKE '2706%'
AND RRDD NOT LIKE '2707%'
AND RRDD...
@swasheck, my apologies for breaking forum rules, it's just that this thread was getting long and I had a note pop up to take things to a private conversion but I"m too new to initiate one. — alex1 min ago
Yes, that is true. Also berating them for answering a question instead of commenting, when they don't have enough rep to comment. That seems backwards to me. I can come to the site with no rep and post an answer, but I can't post a comment?
@swasheck I'd like to see these related chats more integrated on the page instead of just with the link - like a scrolly thing or something embedded along side the question.
@AaronBertrand And then again, this is something that we can discuss here.....but don't even think about posting it on META, you will be obliterated with downvotes
For questions which are really chatty. Like when Aaron and some idiot are going back and forth - we can watch it while we compose our own answer and then get the accept as we modify our answer to accommodate the questioner's idiocy while Aaron keeps him distracted.
Something as simple as on my activity tab showing me the up-ticks next to my comments. "Too expensive to pull that from the database." Really, you are pulling the ENTIRE COMMENT TEXT and putting it on the page. You can't bring a tinyint along with it?
Unless you are using SQL Server 2012, the easiest way to do a cumulative sum is with a correlated subquery. Here is the template for the code:
select t.*,
(select sum(val) from t t2 where t2.ordercol <= t.ordercol) as cumsum
from t
In 2012, you can do:
select t.*,
sum(val) o...
I'm not an expert at SQL and i'm not even sure if this type of query is doable.
I want to return a count(*) for each "MediaTypeID" for each Month based off of "MediaDate".
Any help would be greatly appreciated!
Thanks.
My Table looks like:
The Table Data looks like:
1 | 1 | Funny Cat ...
@bluefeet One of the terms everyone, business and developers, use is CUMDISC (cumulative discount). It's their concept of householding accounts to apply discounted pricing. I love seeing new devs when they see those columns
@MikeFal You can use the drop down arrow on the "Run SQL" button to choose "Markdown output". Just copy and paste the output to the answer and it will be nicely formatted for you with table setup, query and output. Something like this: stackoverflow.com/a/14365753/569436
@AaronBertrand haha. I think that he used it because of my previous comment there
@Stuave Well, this query assumes that the column chainid of the table Venues is unique, obviously. You said on your question that we should use chainid as the common key — Lamak18 mins ago
Anyway this could probably be fixed using DISTINCT, as @bluefeet said. I thought of updating my answer with that in mind previously, but now I refuse to do it
@MikeFal Think I need to count the joined rows for a given m.mid, though and drop any with more than 2 entries. Oh, and reverse the columns in the select statement S
@Roy It's tricky and not something that's particularly evident. I honestly didn't learn that trick until 2 years ago, and I've been working with SQL Server a lot longer than that.
@MikeFal There are sample data in an SQLite database that Stanford uses to validate the answers, aye. It's not possible to get multiple rows with that data set. In fact, there is only one movie that matches, and none that have more than two reviews by the same reviewer.
@MikeFal Hehe, considering I'm the lead DBA I should have posted the question anonymously ;)
Truth is, we spend most of our time on infrastructure related stuff like clustering, replication and integration and almost no time thinking about actual queries.
Not to mention the effort required to tell Oracle why we don't owe them half as much money as they think we do :p
@Roy I do the same, man. I'm mostly infrastructure, but every once in a while I get drawn into query tuning. Usually, though, it's after someone has written terrible SQL and there's only so much I can do to fix it.
Sweet Lloyd Christmas, "the ID its a float datatype" .. and they are using it as a join criteria in SSIS and having issues. I have the feeling they are having inequalities due to floating point precision. Poor bastard
Although, if you give devs the solution for free, they're only gonna be back with more work for you. Might just throw them the problem query and let them sort it out. :P
Well, you do not get it. Point. The trigger is coubpled to the update, it has no relationship to the SP (which may not even exist). Stack traces are not available to you at that level. Simple like that.
I guess that user is generally an asshat. I though maybe today was just an offday and they needed to take the piss out of someone stackoverflow.com/questions/14432405/…
@CadeRoux Not sure what the solution is but I see so many homegrown macguyvered solutions that all work fine except for this one thing and it always seems to be a permissions thing.
@TomTom You do not get it. Point. I'm assuming he is using this for troubleshooting only to find the process that is updating the table but shouldn't. — Aaron Bertrand10 secs ago
@billinkc Doesn't every job in the agent effectively have to run under agent's permissions without other fanciness like shelling out with the equivalent of a RUNAS.
The names in my table appear like so,
Select fname-ab, fname-wxyz, lname-ab, lname-wxyz
From table
Is there a way to write a query that returns the results from all
fields whose field name end with -ab
@billinkc Regardless, you don't want any of that stuff running from IIS. Microsoft will disavow any responsibility. Like using Office automation in IIS etc.
@CadeRoux There's also the fact that to get SSIS to run on a box, that's a license and lots of folks don't realize that (or my source was mis-informed)
@ConcernedOfTunbridgeWells Not really. Used to do it in Delphi using MSXML. T-SQL is so much easier and faster when the the goal of the parsing is to finally end up with rows in some table.
"No, I get it. You eat funny things again that make you have illusions what people use the answer for, while I answer the question at hand. THis could as well be an audit requirement that really slows down everything. I do not know, and contrary to you I refuse to react on illusional ideas."
It is impossible - there is no fast way around it, regardless of your requirements. The trigger is coupled to the update, it has no relationship to the SP (which may not even exist). Stack traces are not available to you at that level.
There was confusion in what the "correct" has tag was for the big conference this year. Marketing decided to drop the century specifier. MarkVSQL proposed the memorable #marvsqlishandsomeandwitty and for unknown reasons, it never trended
@ConcernedOfTunbridgeWells Depends on how you do it. stackoverflow.com/questions/14318350/… It also helps that I am designing the XML schema I need to interpret myself. The main part of XML stuff I have done in SQL Server is with OPENXML since I started building it in SQL Server 2000. It is easier to mess things up performance wise with the XML variable.
@swasheck I've decided the TomTom user needs a talking to and wanted to flag the moderator on the last question. A quick slice through their most recent answers shows a trend of asshattery gist.github.com/fe0366412c1908564469
@MikaelEriksson i've been bothering others about their countries, now it's your turn. are there fun and interesting things to do in Sweden for younger kids (7 years, 5 years) on holiday?
I have a stored procedure that takes around 2 minutes to execute. The execution plan suggested me to create a non clustered index on a table (which is a high traffic table with millions of records in it, plus it gets a constant stream of data every single second).
Please help me decide on whethe...
Based on your note it sounds like adding a non-clustered index to that table is the right way to go. Play with the order of the columns in the development system, and add any needed columns as included columns and see if the query gets any faster.
The general rule of thumb is that is a query is...
I'm using EF with SQL Server and when I'm fetching a DateTime field that is DateTime on the MSSQL and on EF, I would get something like this:
"UpdateTime":"\/Date(1358716865533+0200)\/"
I have no idea what format is this (only that it shows some time with GMT+2 offset).
Any suggestions?
Seco...
You are missing an END on the first CASE:
SUM(CASE
WHEN RRDD NOT LIKE '12%'
AND RRDD NOT LIKE '13%'
AND RRDD NOT LIKE '16%'
AND RRDD NOT LIKE '17%'
AND RRDD NOT LIKE '2706%'
AND RRDD NOT LIKE '2707%'
AND RRDD...
Unless you are using SQL Server 2012, the easiest way to do a cumulative sum is with a correlated subquery. Here is the template for the code:
select t.*,
(select sum(val) from t t2 where t2.ordercol <= t.ordercol) as cumsum
from t
In 2012, you can do:
select t.*,
sum(val) o...
I'm not an expert at SQL and i'm not even sure if this type of query is doable.
I want to return a count(*) for each "MediaTypeID" for each Month based off of "MediaDate".
Any help would be greatly appreciated!
Thanks.
My Table looks like:
The Table Data looks like:
1 | 1 | Funny Cat ...
Well, you do not get it. Point. The trigger is coubpled to the update, it has no relationship to the SP (which may not even exist). Stack traces are not available to you at that level. Simple like that.
The names in my table appear like so,
Select fname-ab, fname-wxyz, lname-ab, lname-wxyz
From table
Is there a way to write a query that returns the results from all
fields whose field name end with -ab
imagine i have this table named "test_table" in the "test_db" in MySQL :
field1 field2
google http://google.com
apple http://apple.com
python ...
