|
hello guys... I have this stored proc which gets some values from table but in addition, it calculates some values like %age of result. The question is: how can I send this value <b>WITH</b> a column name like <i>Percantage</i>. Here is the query for stored proc but I dont know what to add
<pre lang="SQL">
CREATE PROCEDURE [dbo].[GetStudents] AS
SELECT
s.StudentId, s.FirstName,s.SurName,
( ((s.ObtMarks) / (s.TotalMarks)) * 100) --this is the value I want to send with column name
FROM Students s
GROUP BY
s.StudentId, s.FirstName,s.SurName
</pre>
-- modified 28-Dec-11 7:30am.
|
|
|
|
|
Hi,
try giving your calculate column value an alias.
Then you can reference it by name in your c# datatable.
I've modified (but no tested or ran) your procedure.
Hope it helps,
CREATE PROCEDURE [dbo].[GetStudents] AS
SELECT
s.StudentId, s.FirstName,s.SurName,
( ((s.ObtMarks) / (s.TotalMarks)) * 100) AS PercentageValue
FROM Students s
GROUP BY
s.StudentId, s.FirstName,s.SurName
|
|
|
|
|
just put
colName = Calculated Value
and you are good to go 
|
|
|
|
|
just change the query like this..
SELECT
s.StudentId, s.FirstName,s.SurName,
( 'Percentage - ' + ((s.ObtMarks) / (s.TotalMarks)) * 100) as 'Percentage'
hope it works..
with regards
Karthik Harve
|
|
|
|
|
can be a useful keyword.
|
|
|
|
|
Provided you don't misspell it by adding an extra 's'
|
|
|
|
|
I have a select query that returns a some rows . i want to send
each of the rows in returned result to a function but i do not know how ?
can you help me
"Note that i do not want to use Cursor "
select PersonelCode , ...
from PersonnelTable
-------------------------------------
and the function Need the PersonelCode.For each row this function should be executed
Create Function MyFunction ( @PersonelCode )
begin
body of the function
end
|
|
|
|
|
Hi,
Check the following Script,
It will be use full for ur Query..........
SET NOCOUNT ON
DECLARE @Employee TABLE(ID INT , EmpName VARCHAR(40))
DECLARE @I INT, @Count INT, @Temp VARCHAR(50)
SET @I = 1
INSERT INTO @Employee(ID,EmpName)
SELECT 123,'Prabu'
UNION ALL SELECT 234, 'Raja'
UNION ALL SELECT 236, 'Kartik'
UNION ALL SELECT 1234, 'Venkat'
SELECT @Count=COUNT(*) FROM @Employee
PRINT '~~~~~~~~~~~~~~~~~'
PRINT 'Employee Details'
PRINT '~~~~~~~~~~~~~~~~~'
WHILE @I <= @Count
BEGIN
WITH Emp AS
(
SELECT row_number() OVER ( ORDER BY ID ) 'RowNum', ID, EmpName
FROM @Employee
)
SELECT @Temp =CAST(ID AS VARCHAR) +' - '+EmpName
FROM Emp
WHERE RowNum =@I
PRINT @Temp
SET @I=@I+1
END
PRINT '~~~~~~~~~~~~~~~~~'
SET NOCOUNT OFF
|
|
|
|
|
And the following doesn't do it?
select PersonelCode , MyFunction(PersonelCode), ...
from PersonnelTable
|
|
|
|
|
I have a select query that returns a some rows . i want to send
each of the rows in returned result to a function but i do not know how ?
can you help me
select PersonelCode , ...
from PersonnelTable
-------------------------------------
and the function Need the PersonelCode.For each row this function should be executed
Create Function MyFunction ( @PersonelCode )
begin
body of the function
end
|
|
|
|
|
|
How about
SELECT PersonelCode ,MyFunction(PersonelCode), ... FROM PersonnelTable
|
|
|
|
|
i'm trying to filter by date using query but error.
select s.IdStaff, s.NamaStaff,
sum(pd.Qty *
case s.posisi
when 'K.Teknisi' then kd.komisiteknisi
when 'K.Controller' then kd.komisitelemarketing
when 'K.Manager' then kd.komisicontroller
when 'K.TeleMarketing' then kd.komisimanager
when 'K.Cadangan' then kd.komisicadangan
end) Insentif
from staff s inner join penjualan p on s.idstaff = p.idstaff
inner join penjualandetail pd on p.idpenjualan = pd.idpenjualan
inner join komisidetail kd on pd.idbarang = kd.idbarang
where p.tglpenjualan between 12/1/2011 and 12/30/2011
group by s.idstaff, s.namastaff
error message : Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int
|
|
|
|
|
Use quotes around the dates and check that the format of the dates is valid.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
perfect !!
wondering why i always forgot that even after my teacher told me twice.
how about the date format?
is it YYYY/MM/DD or MM/DD/YYYY or else?
thanks before.. 
|
|
|
|
|
|
vkstarry wrote: YYYY/MM/DD
always works.
vkstarry wrote: MM/DD/YYYY
might work depending on your date settings.
|
|
|
|
|
Hi . I have written an assembly in which it has two user defined functions . In sql when i create the assemblies , it does not make any errors even when i create my first function again i do not have any errors but when i create the second function , the server computer has this error , the most intresting thing for me is that i have installed all of them in another computer and it worked but in my server it has this error
can you help me ?
the error is :
" Failed to open malformed assembly 'mscorlib' with HRESULT 0x80070008. "
|
|
|
|
|
googling for Failed to open malformed assembly with HRESULT 0x80070008 turned up this[^]. Check your disk!
|
|
|
|
|
Hi
I need help in writing a SQL query that gives the difference in values for two consecutive dates.
My table structure is as follows :
Collapse | Copy Code
Symbol Name Dates Outstanding values
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0.7995
VAN Equity 12/5/2011 0.7000
VAN Equity 12/8/2011 0.7000
I want the output in the following form :
Collapse | Copy Code
Symbol Name Dates Difference
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0
VAN Equity 12/5/2011 -0.0995
VAN Equity 12/8/2011 0
I came up with the below query.
Collapse | Copy Code
WITH LHP AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Dates ) AS rn
FROM test_table as LI
)
SELECT mc.[Outstanding Values] - mp.[Outstanding Values],
mc.[Outstanding Values] , mp.[Outstanding Values]
FROM LHP mc
inner JOIN LHP mp
ON mc.rn = mp.rn - 1
The above query works fine if I have just one set of Symbol Names.
However, if I have my data in the below format :
Collapse | Copy Code
Symbol Name Dates Outstanding Values
VAN Equity 2011-12-03 00:00:00.000 0.7995
VAN Equity 2011-12-04 00:00:00.000 0.7995
VAN Equity 2011-12-05 00:00:00.000 0.7
VAN Equity 2011-12-08 00:00:00.000 0.7
VAN Equity 2011-12-09 00:00:00.000 0.6
VIN Equity 2011-12-03 00:00:00.000 0.1
VIN Equity 2011-12-04 00:00:00.000 0.2
VIN Equity 2011-12-05 00:00:00.000 0.7
VIN Equity 2011-12-08 00:00:00.000 0.7
VIN Equity 2011-12-09 00:00:00.000 0.6
VAT Equity 2011-12-03 00:00:00.000 0.1
VAT Equity 2011-12-04 00:00:00.000 0.2
VAT Equity 2011-12-05 00:00:00.000 0.7
VAT Equity 2011-12-08 00:00:00.000 0.7
VAT Equity 2011-12-09 00:00:00.000 0.6
i.e. multiple sets of Symbol Names distributed across the same set of dates my query gives me the results as shown below : which is not as expected.
Collapse | Copy Code
No Outstanding Outstanding
Name Values Values
0.6995 0.7995 0.1
-0.1 0.1 0.2
-0.5995 0.2 0.7995
0.0995 0.7995 0.7
0 0.7 0.7
0.1 0.7 0.6
Any help would be greatly appreciated.
|
|
|
|
|
Export
Symbol Name Dates Outstanding values
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0.7995
VAN Equity 12/5/2011 0.7000
VAN Equity 12/8/2011 0.7000
To Excel, Sort by date, insert a column for difference, insert a formula in one cell then drag down the formular for all the other cells.
|
|
|
|
|
do you mean as below?(see last column):
VAN Equity 2011-12-03 00:00:00.000 0.7995 0.7995
VAN Equity 2011-12-04 00:00:00.000 0.7995 0
VAN Equity 2011-12-05 00:00:00.000 0.7 -0.0995
VAN Equity 2011-12-08 00:00:00.000 0.7 0
VAN Equity 2011-12-09 00:00:00.000 0.6 -0.1
VIN Equity 2011-12-03 00:00:00.000 0.1 0.1
VIN Equity 2011-12-04 00:00:00.000 0.2 0.1
VIN Equity 2011-12-05 00:00:00.000 0.7 0.5
VIN Equity 2011-12-08 00:00:00.000 0.7 0
VIN Equity 2011-12-09 00:00:00.000 0.6 -0.1
VAT Equity 2011-12-03 00:00:00.000 0.1 0.1
VAT Equity 2011-12-04 00:00:00.000 0.2 0.1
VAT Equity 2011-12-05 00:00:00.000 0.7 0.5
VAT Equity 2011-12-08 00:00:00.000 0.7 0
VAT Equity 2011-12-09 00:00:00.000 0.6 -0.1
Maybe I have a nice way
|
|
|
|
|
esProc can solve this problem easily. It is just like Excel+SQL, a free tool, see: A Query Language Over-perform SQL
[^].
code as below:
A1: =sqlsvr.query("select Name,Dates,[Outstanding Values] from test_table order by Name,Dates")
A2: =A1.derive(:Difference)
A3: =A2.group(Name)
A4: =A3.(~.run(Difference='Outstanding Values'-'Outstanding Values'[-1]))
I can't post a image file,so here are some explains:
A1 cell:query some data from database.
A2 cell:add a column to A1, named "Difference", just no data.
A3 cell:group the data by field "Name" in A2 cell. Here are 3 groups,
A4 cell:within every group(i.e. "~"), modify the field "Difference". The algorithm is: "this row" subtract "last row"(i.e. 'Outstanding Values'[-1])
I think esProc is more simple for mass data computation
BTW. how to post a image file?
|
|
|
|
|
Hi
How to write a where condition in the OPENQUERY to get data from ms access using linked server.
select * from openquery(AccessDB,'select * from TblEmp where EmpPK=1'). This query show the following error message
" OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AccessDB" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "select * from TblEmp where EmpPK=1" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AccessDB". "
With out where condition its working fine
Thankyou
YPKI
|
|
|
|
|
Had you added server to sql server
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
