|
Hi,
I am working in an application, where only select statement can be used. This is to create a report.
I need one column, where the data should be calculated according to a formula.
Base salary * no of months.
If the salary is same throughout the year it is base salary * 12.
If it changes in b/w, say 4 months, then it is (base salary * 4) + (new base salary * 8)
Another report has the details of the salary of all the months for that person. I can pull that column in this report. But the final report should have only one row for one person
Please help me with this.
Thanks a lot in advance... 
|
|
|
|
|
Try creating a function to return the value. The performance won't be great, but it will do the job.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You can do it as a sub select. Trying to do it the way you describe is the most difficult methos and wuld require the UDF.
(Select Sum(Salary) from Table where Year = 2007) Salary
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am using virtual tables a lot (declare @my_table ...). Sometimes i have columns that accept null so inner join don´t work because doesn´t appear all values that are suposse to appear, and sometimes not either isnull() cleans that, if you know what i mean.
So, other day i got a +200 store procedure by use of virtual table. But it worked fine.
Now i have to copy a part of it to use in a bigger one. 
Can anyone help me here 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
You want someone to do your coding? Or do you need instruction on copy paste?
I don't understand the 200+ reference (lines?)
Why don't you try breaking the proc down to sub procs. The bit you need to copy, cut it out of the original and make it produce the same results as the current code calling it as a sub proc. Then just include it in the "bigger" proc, you can also do this as a UDF that returns a table used by multiple procs.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You want someone to do your coding?
That's the OP's usual M.O. 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
I did not understand the main problem but try using left join or right join to show all values.
|
|
|
|
|
that not what i asked. the sp work. just wanted a comment to know if it happens to other people as well
nelsonpaixao@yahoo.com.br
|
|
|
|
|
nelsonpaixao wrote: to know if it happens to other people as well
All the bloody time - extracting a part of a process from a large proc so it can be reused is a fundemental part of the development cycle. Identifying the bits that can be reused is where the art of development come into play.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
In c# i do this like everbody
routineA(string mystring, int myint);
routineB();
...
There is any equivalent to this in sql server?
I try to trigger some store procedures inside others but i can´t call variables inside.
create procedure C
as
begin
exec procedure B here i create a @table and fill it
...
...here i can´t do select´s or anything else
...
end
go
thanks
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Are you trying to call stored procedure from within another stored procedure?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
YES YES
I can call it but i can get acess to the values!!!!
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Hello there
I've just finished implementing an automatic DB upgrade framework in C#.
It supports finding the optimal DB upgrade path and includes hooks for adding specialized behavior if necessary (most of the time it can run on auto-pilot).
Currently it doesn't support DB schemas that contain views, triggers or fancy DB constraints (like foreign key constraints that are not actually supported in SQLite anyway) since my DB doesn't contain them and I did not have the time to implement a full SQLite DDL syntax parser.
I wondered if it will be worth the effort to publish it on codeproject.
If you are interested I'd like to hear your opinion!
Liron
|
|
|
|
|
Does anyone else get as frustrated as me trying to use Parameters with queries? How much time do you spend trying to fix "Datatype mismatch in criteria expression" or other such errors? I find these errors nearly impossible to debug and fix. There's no way I can find out which parameter is not correct.
Using Parameters is supposed to make creating complex queries neat and easy, but using Parameters has been nothing but a frustration for me.
|
|
|
|
|
TheMandolinMan wrote: How much time do you spend trying to fix "Datatype mismatch in criteria expression
None. I used stored procs and look what the data type is. 
Really its just a case of getting to know your system. As a long time contractor, when I go to a new site I tend to keep an Excel doc of the main columns and their types for a quick reference - unless the site has a good current data disctionar - and believe me, not many do
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I agree with Bob, I spend 0 time working out data type, a trode procedure tells you right there at the top of the proc. I then have a code generator (internally built) that writes the stub for use in the business layer.
Use stored procs....
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have to sort strings which contain integer values and those values are dot separated just like IP addresses are. But number of dots may vary in values, there may be one dot, there may be three dots or there may not be any dot. There may be any number of dots. please write query for sql2000
For example
1
2
3
3.1
3.2
3.2.1
10.1
10.23.43
10.23.43.22.34.32.2
10.23.43.22.34.2
etc
how can we sort above values using sql.
Can you please give me query for this? I shall be very thankful to you.
if you send the solution to my email address below aswell then i shall really appreciate.
habib
habib.rehman@hotmail.com
|
|
|
|
|
I believe nobody is going to do your work for you, but you can get guidance from CodeProject forums.
However asking to send you a solution isn't the right way to ask for help. Show that you have already put effort into this for example by posting code and asking for certain problems in that code.
To get you to start, write a stored procedure where you split the string and sort rows manually. When this is done, return correct results.
|
|
|
|
|
ok, i am using following queries, all are running fine but only one is not working in sql2000(however it is working in 2005).
if object_id('dbo.t1') is not null drop table dbo.t1;
create table dbo.t1
(
id int not null identity primary key,
val varchar(500) not null
);
go
-- Create and populate an auxiliary table of numbers
IF OBJECT_ID('dbo.Nums') IS NOT NULL DROP TABLE dbo.Nums
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
SET NOCOUNT ON;
DECLARE
@max AS INT,
@rc AS INT;
SET @max = 1000000;
SET @rc = 1;
BEGIN TRAN;
INSERT INTO dbo.Nums(n) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums(n)
SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums(n)
SELECT n + @rc FROM dbo.Nums
WHERE n + @rc <= @max;
COMMIT TRAN;
-- Function that splits array elements
CREATE FUNCTION dbo.fn_split(@arr AS VARCHAR(8000), @sep AS CHAR(1))
RETURNS TABLE
AS
RETURN
SELECT
(n-1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, '')) + 1 AS pos,
SUBSTRING(@arr, n, CHARINDEX(@sep, @arr+@sep, n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@arr) + 1
AND SUBSTRING(@sep+@arr, n, 1) = @sep;
GO
--And here’s the query code:
select *
from dbo.t1
order by
(select right('000000000' + element, 10) as [text()]
from dbo.fn_split(val, '.')
order by pos
for xml path(''));
This last query is not working in sql2000, however it is running in 2005.
can someone write this query for sql2000.
select *
from dbo.t1
order by
(select right('000000000' + element, 10) as [text()]
from dbo.fn_split(val, '.')
order by pos
for xml path(''));
modified on Sunday, August 31, 2008 6:10 AM
|
|
|
|
|
If I remember correctly, support for XML was introduced in SQL Server 2005 so this functionality cannot be used in version 2000.
So I suggest that you use different approach as I mentioned in previous post. Use temporary tables for splitting and sorting and return results based on temporary table.
|
|
|
|
|
Mika, can you please write complete code or an article so that everyone can benefit from it. you can write complete stuff by replying to this question or you can write a complete article. but i suggest that you do both of the jobs
thanks,
habib
modified on Sunday, August 31, 2008 6:44 AM
|
|
|
|
|
I'll take the article into consideration, but I don't currently have the time to write it nor the full implementation for your question. So I advice you to google for similar problems and/or ideas and to experiment on different approaches.
I'm sure that when you put your mind into this, it'll be done in no time.
Mika
|
|
|
|
|
How rude, you not only want himn to do your work for you, but to format it (an article) to your liking.
Thats rich.
|
|
|
|
|
Hello heyy, this is a great community!!
i've had this question all my life since i was learning databases lol,
once that i had my project finished, my desktop software and my database schema and all is finished .. and i want to make an installation ..
what i need to do .. to have my database installed and configured when the installation of my software finishes? ... i mean ..once that i have installed my software in another comp ... i need to install mysql and run the script to have my database created and in some cases go to control panel odbc ... and sign my database in there ... thats supposed to be part of my installation ..
a end user client ... doesnt know how to do that ...
i hope someone could help with that doubt
|
|
|
|
|
That would be a custom installer action.
MS Sql Express has a merge module that can be added to your install, and allows you to attach your programs database file on the fly.
For MySQL it would get a little more complex - you can probably unzip the server somewhere and then unzip your blank database into the right folder. Not sure about how the programmatic administration stacks up for MySQL.
You might also want to check your licensing for MySQL. IIRC the client libraries are GPL - so if you are distributing MySQL and have linked against the client libraries you'll probably either need a paid license, or GPL your program.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
