Thursday, March 22, 2012
Do I have the right to modify third-party applications stored procedures?
A lot of the vendors that I have dealt with in the past have allowed the changing of stored procedures and the addition of new ones on a "at your own risk" type of deal (eg. future versions will be be guaranteed to work, the database is no longer covered by their support etc).
I'd suggest having a talk to the vendor, don't tell them you have changed anything but ask them what their views on changing stored procedures etc are.
HTH|||If it clearly creates data inconsistency then I at least would have no second thaughts abut changing the sp's. I would sort of stick to the "what they don't know can't hurt'em"-plan if this was me...|||I'll try to describe better our situation.
Before I did the changes, the provider notified us that they were closing their doors. Thus, we didn't have any support for sometime. After a while, they came to us offering the source code which we rejected considering the fact that it was a very poor developed application. Afterwards, we decide to develop, with our own effort, an application to replace the existing. In short, there is a legal process running for last 4 years where they have alleged we have commited several violations to their rights which so far, we have disproved. Now, they are conducting their efforts toward the fact I modified 2 procs and also, created new ones. I know I didn't do wrong. But, how can I show the attorneys? Perhaps, I need leads to articles, statements or disclaims from, lets say, Microsoft that contain information about the pros and cons of leaving procs as text. How can a solution provider protect procs source code or even better, their know-how?
Originally posted by rokslide
It'd really depend on what sort of deal you have with the vendor of the third party app.
A lot of the vendors that I have dealt with in the past have allowed the changing of stored procedures and the addition of new ones on a "at your own risk" type of deal (eg. future versions will be be guaranteed to work, the database is no longer covered by their support etc).
I'd suggest having a talk to the vendor, don't tell them you have changed anything but ask them what their views on changing stored procedures etc are.
HTH|||This ha NOTHING to do with M$
Did you sign a contract with them?
Did you pay them anything?
Did they build this code for you exclusively?
If I contract out, any code I build belongs to the Client (usually)
It's all a matter of what's on paper...
Check this out:
The part about self employeement...
http://weblogs.sqlteam.com/markc/|||Wow, I didn't realize it was this serious. It is possible to encrypt sp's in sql-server and if this company has let you have direct access to the database then this would have been a good idea from their side. It is not common for software-vendors to expose their sourcecode and this company you are been in a dispute with should know this.
An sp can be encrypted like this:
CREATE PROCEDURE myProcName WITH ENCRYPTION
AS
...
This way you or any others will not be able to access the sorcecode of the procedure. You can try it yourself for verification:
EXEC sp_helptext myProcName
Now when it comes to your legal rights to change their sourcecode my belief is that you unfortunately have a weak case. Brett Kaiser is partially right I belive that it comes down to what you have on paper and not, but since you have used their software you automatically agree that their software is usable and if you hae paid for it aswell you aknowledge that they are the rightful owners. I would try to go down the lane of putting the blame on them for not fulfilling their duties as a software vendor, and because they didn't perform their duties you had to remedy the bugs yourself.
Good luck man, I'm sorry to say that I'll think you'll need it :(|||I apologize...I wasn't meaning to be offensive...
But I guess I was...
Sorry|||What are you suggesting? That they don't have laws in Venezuela? I really hope I misunderstood this message of yours because I found it to be quite rude, but I will give you the benifit of the doubt...|||Frettmaestro, I'm sure Brett didn't mean to be insulting. If you know the different parts of the US well, then, you know how those guys from Jersey can be. (Right Brett? :D )
j_shaw, not being a lawyer anywhere, much less knowledgeable about Venezuelan law, everything I say here is totally my opinion and not to be taken as legal advice. But here goes: Regardless of whether the procedures were encrypted or not, I think you overstepped the bounds by changing them without first establishing ownership or at least permission. If the work was done for you custom, then there's a good chance your company owns them, but if it was a commercial product, there's a good chance that you only had license to use, not change. And as Brett pointed out, this all comes down to what the paperwork says.
Just because someone doesn't encrypt their procedures doesn't mean you have the right to change them. It may be a stupid move on their part to leave them hanging out there so easy to read and altered, but it's not an excuse to say, "if you didn't want me to do it, you should have locked it". That's like saying if somebody leaves the door to their house unlocked, or a window open, then it's okay to enter and take anything you want. Wrong!
And nobody should be under the misperception that using SQL Server's WITH ENCRYPTION is a guaranteed secure lock. I've heard that it has been cracked. It's still a good idea, but not foolproof.
(P.S. Brett, thanks for the referral!)|||The story is a little longer. My company hired this guys to develop a solution for us. But, we are unable to prove it because in one update they changed our personalized version with one that they had previously registered.
Originally posted by Frettmaestro
Wow, I didn't realize it was this serious. It is possible to encrypt sp's in sql-server and if this company has let you have direct access to the database then this would have been a good idea from their side. It is not common for software-vendors to expose their sourcecode and this company you are been in a dispute with should know this.
An sp can be encrypted like this:
CREATE PROCEDURE myProcName WITH ENCRYPTION
AS
...
This way you or any others will not be able to access the sorcecode of the procedure. You can try it yourself for verification:
EXEC sp_helptext myProcName
Now when it comes to your legal rights to change their sourcecode my belief is that you unfortunately have a weak case. Brett Kaiser is partially right I belive that it comes down to what you have on paper and not, but since you have used their software you automatically agree that their software is usable and if you hae paid for it aswell you aknowledge that they are the rightful owners. I would try to go down the lane of putting the blame on them for not fulfilling their duties as a software vendor, and because they didn't perform their duties you had to remedy the bugs yourself.
Good luck man, I'm sorry to say that I'll think you'll need it :(|||Originally posted by AjarnMark
Frettmaestro, I'm sure Brett didn't mean to be insulting. If you know the different parts of the US well, then, you know how those guys from Jersey can be. (Right Brett? :D )
(P.S. Brett, thanks for the referral!)
Just being the ignorant American...
But to re-address in (hopefully) a different tone....
There were hardly ANY US laws for software and stuff till just recently...
How and what the laws are for Venezuala...I have no idea...
And wouldn't you want to counter-sue anyway for your time it took to do the data sanitation?
Don't you have the expectation/right to expect their product to work?|||My own cursory knowledge of law probably does not apply, but it does not sound like you made a profit by re-selling the application with your changes, so you should be safe from lawsuits by SCO. When you found that the application could invalidate your data, did you go to the software company and ask for a fix? If they refused, then you may be able to sue them (if you have in writing that the application promises to do so-and-such). Since they are bringing the suit, they have to prove that your changes have somehow damaged them.
In general, I do not allow any schema (or data) updates to a third party application database, as this can invalidate the service contract. Extra lawsuits have never come into the picture (as far as I know).|||A company I have been working for is currently in a legal dispute with a hosting provider. The case is not at all similar but this company did not have a signed agreement of any sort, and here in England that didn't matter because we supposedly accepted their terms and conditions automatically when they paid the first bill and made use of their services. The company I worked for will most likely get the case dismissed because the hosting company breached their own contract on several counts but my point is simply that even though no written contract has been signed you can still be legally obliged. This can offcourse be different in Venezuela, but I don't know that...
When it comes to altering software I do belive that you will have to make a case on the fact that their software was incapable of doing what it was supposed to and because they offered no real help, you had to change it yourself to save your business. You can't go bankrupt because some moron developer don't know what he's doing...|||Ok. Lets change the subject.
We also had to create an application to fix the already corrupted data. We created new sprocs and placed them in the same database. Do I have the right to add new objects to the existing database?
Originally posted by Frettmaestro
A company I have been working for is currently in a legal dispute with a hosting provider. The case is not at all similar but this company did not have a signed agreement of any sort, and here in England that didn't matter because we supposedly accepted their terms and conditions automatically when they paid the first bill and made use of their services. The company I worked for will most likely get the case dismissed because the hosting company breached their own contract on several counts but my point is simply that even though no written contract has been signed you can still be legally obliged. This can offcourse be different in Venezuela, but I don't know that...
When it comes to altering software I do belive that you will have to make a case on the fact that their software was incapable of doing what it was supposed to and because they offered no real help, you had to change it yourself to save your business. You can't go bankrupt because some moron developer don't know what he's doing...|||All in favor say 'eye'! EYE!
I don't think this will get you anywhere, we can say this and that but it all comes down to the laws of your country and your best bet is some venezuelan lawyers ;)|||A couple of things spring to light here...
The story is a little longer. My company hired this guys to develop a solution for us. But, we are unable to prove it because in one update they changed our personalized version with one that they had previously registered.
Okie,.. well if you hired them to develop a solution for you there should be documents showing it yes?
If they gave you a solution that was something they developed for others or that they in turn sold to others then they are in breach of contract (assuming standard contract laws apply).
If the application they gave you/developed for you is faulty as suggested...
We also had to create an application to fix the already corrupted data. We created new sprocs and placed them in the same database. Do I have the right to add new objects to the existing database?
...and you gave them the opportunity to fix it (which they didn't) then I don't see what the issue should be legally. Yes, you are on somewhat shakey ground for changing their code but they are on equally unstable ground for their practises during the development and delivery of the application.
You could also use the facts that their application was faulty and that they code was unprotected to show that they had made false claims about their abilities...
Of course I'm not a lawyer and I haven't studied law (except for what I have come across in the industry) and I don't have any leads/examples to point to, but I'm sure if you raised these points with an attorney he would be able to help you more.|||It's like the line from Animal House...
My advice to you is to drink heavily...
I didn't know you where in pre Med?
Pre Med, SQL Server DBA, what's the difference?
Do getDate() inside SQL Server OR do System.DateTime.Now inside application ?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
If you generate the data in code and then pass it to sql server over the network via a query, then you are simply generating more traffic for your network (albeit just a few extra bytes). If you use the built-in GETDATE() function you are saving yourself from having to pass that data over the network. I usually use GETDATE() myself.
This depends on what you're doing. If you need the current time, it would be best to get the current time from whereever you need the time. (I know a guy who only needed the current time in his app, but created a call to the database's GetDate function, which was on another machine. That's not very accurate for the current application time.)
This may have been a big issue if your DB and app are on two or more machines, and the Server OS was older and it didn't have the ability to sync the time. I would say that the performance of the two functions would be really really close.
If you're worried about network bandwidth (if connecting with a sql server instance on another server), then having the database do the insert would be less of a load. If the column always displays the current time, then you could set the default for the column to GetDate(), eliminating the need to manually call the function or insert a value.
The biggest thing to thing about would be how important is this to you? There is the issue of what is deemed the "current time" -- Is the current time when the data was entered by the user at the application level, or is it current when the data is inserted into the table (use app's Now for the former, Sql's GetDate for the latter). For instance, there are cases where there may be a delay between a record being built and actually inserted into the DB. Every decision should be made based on frame of reference.
Again, my general suggestion is to use the function from the local source, unless you use the default value suggestion for your table's column.
|||If the data is going to be stored in the database, I almost always use GetUTCDate() inside the database.
For web applications, this solves a number of problems. If you need to scale out to a web farm, you don't have to worry about keeping all the web farm machines (and possibly the SQL Server) clocks in sync with each other (Single time source). You don't have to worry about what timezone each of your webservers/users are in (Since the time is in UTC). That means I can take the easy way out and display the result in UTC or a fixed/hardcoded timezone to the user to start, and add user timezone support (user set, or autodetected) as the application matures, and I don't have to convert the data in the database when I do.
For desktop applications accessing the database, obviously keeping all the clocks synced is much more difficult, so not relying on the application layer to provide the current time greatly simplifies things.
In the past quite often a project starts as either a web application and then as it matures, we build a webservice for it or have desktop applications that need to access/create/import/export the data as well. Or the reverse, it's a desktop application and then use a web application for other parts/seconday interface into the data or migrate to a web application completely.
|||
Motley:
If the data is going to be stored in the database, I almost always use GetUTCDate() inside the database.
For web applications, this solves a number of problems. If you need to scale out to a web farm, you don't have to worry about keeping all the web farm machines (and possibly the SQL Server) clocks in sync with each other (Single time source). You don't have to worry about what timezone each of your webservers/users are in (Since the time is in UTC). That means I can take the easy way out and display the result in UTC or a fixed/hardcoded timezone to the user to start, and add user timezone support (user set, or autodetected) as the application matures, and I don't have to convert the data in the database when I do.
For desktop applications accessing the database, obviously keeping all the clocks synced is much more difficult, so not relying on the application layer to provide the current time greatly simplifies things.
In the past quite often a project starts as either a web application and then as it matures, we build a webservice for it or have desktop applications that need to access/create/import/export the data as well. Or the reverse, it's a desktop application and then use a web application for other parts/seconday interface into the data or migrate to a web application completely.
Thanks Motley. I've never had to figure out hardware allocations for a website before and I'm new to all of this. I understand where you may want 2 or 3 web servers simply due to heavy traffic and just keep 1 database server. But would there ever be a situation where 1 web server is enough and you may need more than 1 database server? Or maybe one might need more than 1 of each kind? Are there rules of thumb to use when deciding how to scale up?
|||Yes, using multiple web servers that access a single database server is common.
Scaling out to multiple database servers is a LOT more complicated, so it's usually avoided if possible. That usually means build a bigger database server, and/or improve the web application code as much as possible first. This is a lot less common.
Scaling out a database server and only requiring a single web server is probably the rarest of them all. Now there may be reasons for a single web server and multiple database servers, like instancing, or web front-ending multiple database driven applications, but scaling out isn't usually one of them.
|||
Motley:
Yes, using multiple web servers that access a single database server is common.
Scaling out to multiple database servers is a LOT more complicated, so it's usually avoided if possible. That usually means build a bigger database server, and/or improve the web application code as much as possible first. This is a lot less common.
Scaling out a database server and only requiring a single web server is probably the rarest of them all. Now there may be reasons for a single web server and multiple database servers, like instancing, or web front-ending multiple database driven applications, but scaling out isn't usually one of them.
Thanks for the clarification, Motley. I have a question regarding backing up of data. Arguably, the contents of the database are the meat and potatoes of the website. Lose the database information and you'll lose everything, correct? So what do most people running 1 database server do to protect their lifeblood? Is the RAID 1 setup sufficient? If one of the drives go down, I'm ok right? I've heard of the databases having their own protection like failover (not even sure what this is), etc, but I'd imagine a RAID setup would make those superfluous features unnecessary.
Motley:
If the data is going to be stored in the database, I almost always use GetUTCDate() inside the database.
For web applications, this solves a number of problems. If you need to scale out to a web farm, you don't have to worry about keeping all the web farm machines (and possibly the SQL Server) clocks in sync with each other (Single time source). You don't have to worry about what timezone each of your webservers/users are in (Since the time is in UTC). That means I can take the easy way out and display the result in UTC or a fixed/hardcoded timezone to the user to start, and add user timezone support (user set, or autodetected) as the application matures, and I don't have to convert the data in the database when I do.
For desktop applications accessing the database, obviously keeping all the clocks synced is much more difficult, so not relying on the application layer to provide the current time greatly simplifies things.
In the past quite often a project starts as either a web application and then as it matures, we build a webservice for it or have desktop applications that need to access/create/import/export the data as well. Or the reverse, it's a desktop application and then use a web application for other parts/seconday interface into the data or migrate to a web application completely.
I think I'm going to go with this solution. I can add another column to my user table called something like "timezone" and have them input their GMT offsets when they register their account. Then, on authenticated pages, I would add/subtract the offsets everytime I obtain datetime values. Does this sound like an ok route?
I just tested this on my localmachine. I'm in NY so my offset is GMT -5, however, when I looked in the database, the time stored was 4 hours ahead, not 5. What did I do wrong here?
Thanks.
|||
S2kDriver:
I think I'm going to go with this solution. I can add another column to my user table called something like "timezone" and have them input their GMT offsets when they register their account. Then, on authenticated pages, I would add/subtract the offsets everytime I obtain datetime values. Does this sound like an ok route?
I just tested this on my localmachine. I'm in NY so my offset is GMT -5, however, when I looked in the database, the time stored was 4 hours ahead, not 5. What did I do wrong here?
Thanks.
Don't let your app decide whether to add or subtract. Simply add the offset to the GMT. You may be having a mathematical error, such as GMT - -5, which would yield GMT + 5. If you simply add the offset, you should be fine, i.e.: GMT + -5 is the same as GMT - 5.
You may also be using GetDate() instead of GetUtcDate() in SQL Server; the former would return your local time instead of GMT/UTC.
|||I did not do the math yet. All I did was run getUTCDate() on the local machine I'm using to test. I'm in NY (GMT -5), so the inputted data in the database should have been 5 hours ahead, but it was only 4. Sorry for the add/subtract confusion, yea I was just speaking affirmatively.
I'm sure I'm not using getDate(), if I were, the inputted data in the database would have been identical to the time I was seeing on the computer.
I've thought about the DST (daylight savings) factor, but I don't see why that would have any effect since the database just looks at the computer's settings (which time zone it is set to) and just compensates to arrive at GMT.
Did anyone else have this similar problem?
Thanks.
Monday, March 19, 2012
dmvs and mirroring
All,
My current understanding is that most, if not all dmv's are dynamic in that if a server is rebooted than the information gathered from the dmv's will be reset. What happens if the mirror instance fails over (manually or because of a hardward/software issue)? Does the failover of the principal to the mirror node cause the a resetting of the dmvs?
Thanks,
Ian
Hi Ian,
The DMVs are dynamic are always changing as the internal/external structures of the database/server change.
Jag
Sunday, March 11, 2012
Division Problem
Hi there,
I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.
Any help is appreciated
I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.
If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:
1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1
The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:
1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression
The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.
I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,
Instead of using
<current value> / <Previous value> * 100 >= 10
to get the percentage.
I can use
(<current value> - <Previous value>) >= <current value> * 0.1
However the previous value may be bigger than the current so i also have to check
(<current value> - <Previous value>) <= (<current value> * 0.1) * -1
Therefor there is no division.
Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||
first. . . why two columns. . . this is not normalized. . .
you should have
Table: MonthlyTaskData:
TaskID|MonthID | MonthValue
any other schema is plain wrong!!!
[rant over]
given @.CurrMonthID and @.PrevMonthID
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.
I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.
Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
Division Problem
Hi there,
I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.
Any help is appreciated
I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.
If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:
1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1
The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:
1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression
The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.
I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,
Instead of using
<current value> / <Previous value> * 100 >= 10
to get the percentage.
I can use
(<current value> - <Previous value>) >= <current value> * 0.1
However the previous value may be bigger than the current so i also have to check
(<current value> - <Previous value>) <= (<current value> * 0.1) * -1
Therefor there is no division.
Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||
first. . . why two columns. . . this is not normalized. . .
you should have
Table: MonthlyTaskData:
TaskID|MonthID | MonthValue
any other schema is plain wrong!!!
[rant over]
given @.CurrMonthID and @.PrevMonthID
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.
I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.
Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
Friday, March 9, 2012
Divide by number of days in the year.
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advanceHere's how to get the number of days in the current year:
select
case
when year (getdate()) / 100 % 4 = 0 then 365
when year (getdate()) % 4 = 0 then 366
else 365
end
However, in a UDF, you cannot have a non-deterministic function within it.
Thus, you cannot use getdate() directly. However, you could feed a date to
the function.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:19165261-4C92-45C9-97B5-00AC910B17FE@.microsoft.com...
How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advance|||For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:
>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>|||scuba
Here is a stright but bit complicated one. This is useful evenif calender
changes(yuck!) provided years starts from jan1 and ends with 31 dec(kidding)
SELECT DATEDIFF(DAY, CAST('01-01-' + cast(YEAR(GETDATE()) as varchar(4)) AS
DATETIME),CAST('12-31-' + cast(YEAR(GETDATE()) as varchar(4)) AS DATETIME))+
1
Regards
R.D
"scuba79" wrote:
> How can I create a function that will divide a parameter passed into the
> stored procedure by the number of days in the current year? I know that I
> can not just use 365 since it will not take into account leap years.
> Thanks in advance|||:-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e%23m2LE3rFHA.1032@.TK2MSFTNGP12.phx.gbl...
For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:
>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>