|
Blue_Boy wrote: select Field2,sum(Field2)as [Sum] from tablename where Field2< 5000
group by Field2
Hi Blue_boy
I ran your query and got this result... not quite what I was looking for, but thanks anyway
Field2 Sum
34 34
58 58
97 97
234 234
345 345
540 540
679 679
768 768
2312 2312
4324 4324
Regards
Chandra
|
|
|
|
|
In Field2 doesn't exists any value great then 5000.
Or tell me which values shouldn't display (from those values which you posted) when you get sum of filed2 which are gerat then 5000.
I Love SQL
|
|
|
|
|
Hi
The result I am looking for is:
Field1 Field2
3 34
4 58
5 97
6 234
7 345
8 540
9 679
10 768
The records with 11 and 12 will not figure in this selection since adding any of these Field2 to the sum of the Field2 above will push the total above 5000.
Regards
Chandra
|
|
|
|
|
this should works
select * from tablename where Field2 in<br />
(select firstTable.Field2 from tablename firstTable inner join tablename secondTable <br />
on secondTable.Field2 <= firstTable.Field2 group by firstTable.Field2 having sum(secondTable.Field2) < 5000) order by Field2
I Love SQL
|
|
|
|
|
It worked! Thank you.
Regards
Chandra
|
|
|
|
|
You are welcome.
I Love SQL
|
|
|
|
|
Hi all
Actually am struglling with a problem ...here i got storedprocedure named Usp_Rpt_Proc1 which
accepts two arguments in datetime fomat ...when am executing it from queryanaliser it's working
fine
am executing it like
exec dbo.Usp_Rpt_Proc1 '1/1/2001','1/1/2008' it's returning some results as output
but the problem is i want to modify that perticular procedure
for that i copied and pasted the functionality (same code from SP) and inplace of
CREATE PROCEDURE dbo.Usp_Rpt_Proc1 (@StartDate DateTime,@EndDate DateTime)
AS
am declare 2 datetime variables..and passing values for them ...and finaly am executing it.this
time it's not returning any output...not showing errors aswelll
just showing the column names with out content....
Plz let me now the reson behind it.....(In the second case am taking the exact code ...that's for
sure).........Plz help me out
With regards
Yuva
|
|
|
|
|
if you need return output parameter from stored procedure you must to set word out beside parameter
for example
declare @Time1 datetime
declare @time2 datetime
set @Time1 = '1/1/2001'--if this output parameter set out beside it
set @Time2 = '1/1/2001'
==============================================
exec dbo.Usp_Rpt_Proc1 @time1 out ,@Time2 out
but this solve must used if the two output parameter
CREATE PROCEDURE dbo.Usp_Rpt_Proc1 (@StartDate DateTime output,@EndDate DateTime output)
===============================================
exec dbo.Usp_Rpt_Proc1 @time1 ,@Time2 out
but this solve must used if the one output parameter
CREATE PROCEDURE dbo.Usp_Rpt_Proc1 (@StartDate DateTime ,@EndDate DateTime output)
====================================================
exec dbo.Usp_Rpt_Proc1 @time1 ,@Time2 ,@output out
but this solve must used if the one output parameter
CREATE PROCEDURE dbo.Usp_Rpt_Proc1 (@StartDate DateTime ,@EndDate DateTime ,@output datatype output)
if you need any thing reply
with regards
Rami Abd Alhalim
|
|
|
|
|
Hi Guru's
I created a web application which is intended for multi users, I come up with an idea that every user who sign ups will have their own sets of tables instead of one row on the table with relations to other table, I'm just wondering whats the limit count of tables can a database (MSSQL2005) hold and is it a good advice to propagate table.
regards
Dom
|
|
|
|
|
dont know the limit but that sounds like a horrible idea! GOOD LUCK!
|
|
|
|
|
|
Hi,
In MS SQL 2005 the ID column of Sysobjects table is Int. It means that you can create Max of integer. The integer datatype can hold value up to 2^31.
|
|
|
|
|
Thanks for the nice feed from you guys, I guess I'm going to push it through though just to see how it goes when hits a thousand sign ups (day dream mode). I'll let you know when things start to mash up (couple of years I guess).
regards
Dom
|
|
|
|
|
I have created one table with DATETIME datafield, but its allowing me to
store date only in 'mm/dd/yyyy' format so, how do i change the format of this field to 'dd/mm/yyyy'
I hope you understood the problem....
Thanks. 
Prashant C
|
|
|
|
|
Just copy pasting online help ....
SET DATEFORMAT
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.
Syntax
SET DATEFORMAT { format | @format_var }
Arguments
format | @format_var
Is the order of the dateparts. Can be either Unicode or DBCS converted to Unicode. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.
Remarks
This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.
The setting of SET DATEFORMAT is set at execute or run time and not at parse time.
Permissions
SET DATEFORMAT permissions default to all users.
Examples
This example uses different date formats to handle date strings in different formats.
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
GO
SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '98/31/12'
SELECT @datevar
GO
SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '98/12/31'
SELECT @datevar
GO
Regards
KP
|
|
|
|
|
When I do the following SQL command to fill a GridView:
SELECT * FROM Owners WHERE OwnerID = 7 OR OwnerID = 3 OR OwnerID = 21 .....
I want the rows to be in the same order as in the WHERE section (7, 3, 21...)
Is this possible?
Jim
|
|
|
|
|
SELECT * FROM Owners WHERE OwnerID = 7
union
SELECT * FROM Owners WHERE OwnerID = 3
union
SELECT * FROM Owners WHERE OwnerID = 21
|
|
|
|
|
Thanks for the help. But I didn't get consistent results until I changed 'union' to 'union all'.
Jim
|
|
|
|
|
Hi all,
I have a datetime column on my table( ADEDATE type datetime)
Select id, ADEDATE from dbo.Tablename
I would like to get the day of the week for the result of the selecet statement.
the result of the select statement will be similar to ..
12,Monday
13,sunday
14,saturday
Which function shall i use for this task?
Thanks
|
|
|
|
|
SELECT DATENAME(dw, GETDATE())
Dave Evans
|
|
|
|
|
Thanks Dave.
It worked.
Keep the good work.
|
|
|
|
|
I need to access a billing webservice from SQL. I createde a new c# class project and made a web refrence to the web service "ProdBilling".
Here is the code of my assembly
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace PaymentProc
{
public class PaymentProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ChargeCard(int account, int amount)
{
string Response;
ProdBilling.Service serv = new ProdBilling.Service();
Response = serv.ChargeCard(account, amount);
SqlContext.Pipe.Send(Response);
}
}
}
I then ran WSDL
wsdl /o:PaymentProc.cs /n:PaymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx
Then compliled
csc /target:library PaymentProc.cs
and added the assembly
CREATE ASSEMBLY PaymentProc from 'D:\ProdCode\PaymentProc.dll' WITH
PERMISSION_SET = UNSAFE
I cannot figure out how to refrence the chargecard method
I have tried
CREATE PROCEDURE PaymentProc
@Account int,
@Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
It seems wsdl.exe put all this serialization code
namespace PaymentProc {
using System.Diagnostics;
using System.Web.Services;
using System.ComponentModel;
using System.Web.Services.Protocols;
using System;
using System.Xml.Serialization;
/// <remarks>
[System.CodeDom.Compiler.GeneratedCodeAttribute("wsdl", "2.0.50727.42")]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Web.Services.WebServiceBindingAttribute(Name="ServiceSoap", Namespace="http://ProdWeb1/PaymentProc")]
public partial class PaymentProc : System.Web.Services.Protocols.SoapHttpClientProtocol {
private System.Threading.SendOrPostCallback ChargeCardOperationCompleted;
/// <remarks>
public PaymentProc()
{
this.Url = "http://ProdWeb1/PaymentProc/PaymentProc.asmx";
}
/// <remarks>
public event ChargeCardCompletedEventHandler ChargeCardCompleted;
/// <remarks>
[System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ProdWeb1/PaymentProc/ChargeCard", RequestNamespace="http://ProdWeb1/PaymentProc", ResponseNamespace="http://ProdWeb1/PaymentProc", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
public string ChargeCard(int account, int amount) {
object[] results = this.Invoke("ChargeCard", new object[] {
account,
amount});
return ((string)(results[0]));
}
.................
When I run
CREATE PROCEDURE PaymentProc
@Account int,
@Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
Method, property or field 'ChargeCard' of class 'PaymentProc.PaymentProc' in assembly 'PaymentProc' is not static.
Any ideas?
Dave Evans
|
|
|
|
|
Hi friends
what is differnce b/w sqlserver2005 and sqlserver2000
what is the advantage of sqlserver2005
plz give goodtutorial link for sqlserver2005
regards
saravanan
-- modified at 8:04 Monday 13th August, 2007
|
|
|
|
|
|
Hi,
My code worked fine before i placed "SqlTransaction" command in my code. After placing "SqlTransaction" command in my code it is showing "Unexpected existing transaction." Please tell me where I am going wrong.
My Code:
protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString))
{
con.Open();
using (SqlConnection destinationConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString))
{
destinationConnection.Open();
using (SqlTransaction transaction = destinationConnection.BeginTransaction())
{
SqlCommand DelCmd = new SqlCommand("delete from mfund_data", con);
DelCmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = "mfund_data";
try
{
bulkCopy.WriteToServer(CreateDataTableFromFile());
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Response.Write(ex.Message);
}
}
}
destinationConnection.Close();
}
con.Close();
}
}
Regards,
Jagadeesh
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
