|
this problem maybe cause format date
if you need sure that set in command this date
solve:
insert_cmd = string.Format("Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD',08/14/2007,30,'Medium')");
|
|
|
|
|
Said could you plz tell me how to do that as i dnt know.
i'm new to sql
Sailaja
|
|
|
|
|
Insert into CreateTask(AssignedTo,AssignedDate,EstimatedEfforts,Priority) values ('ABCD','2007-8-11',30,'Medium'
In VB u have format function. U can use this function to format the date.
i.e. YYYY-MM-DD format.
|
|
|
|
|
thx pande... the code worked correctly if I give the date in single quotes but am not getting the current system time in it..
its displayed as:
8/14/2007 12:00:00 AM
Sailaja
|
|
|
|
|
If you want to insert the current date and time, use the Getdate() function. It's a Sql Server function that returns the current date and time. For instance:
insert into myTable(CurrentDate) values (GetDate());
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
my problem has been solved.
thank u so much...
Sailaja
|
|
|
|
|
i have a table, i want to display the values in rows into columns in sql server 2005..
Ex:-
<br />
<br />
a 1 x ----> a b c<br />
b 2 y ----> 1 2 3<br />
c 3 z ----> x y z<br />
Can anyone help me on this??
Dream it to achieve it
|
|
|
|
|
Hi
I have about 2k records in a table which contains a numeric field. I would like to know if there is any way I can select a set of records where the sum of this numeric field is limited to a particular value.
The table in question is part of an SQL 7 database.
Thanks in advance
Chandra
|
|
|
|
|
Hi Chandra
Do you mean:
select * from Orders where cust_id in (
select cust_id from Orders
group by cust_id
having sum(order_total) > 500)
order by cust_id This would receive the orders for customers who have placed more than $500 worth of orders.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi Andy
Thanks for your answer... but what if my table contains all distinct cust_id?
Regards
Chandra
|
|
|
|
|
Hi Chandra
Sorry - I got a little confused because your original question stated "... where the sum of ...".
Does
select * from Orders where OrderValue > 500 do what you want?
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi Andy,
I'm afraid this will not help either... let me explain with some data:
Field1 Field2<br />
3 34<br />
4 58<br />
5 97<br />
6 234<br />
7 345<br />
8 540<br />
9 679<br />
10 768<br />
11 2312<br />
12 4324
Now, I want to be able to pick up a set of records where the sum of Field2 is less than 5000.
I hope I have explained my requirement clearly
Thanks
Chandra
Modified to include /code tags since the tabs were swallowed up.
|
|
|
|
|
Hi Chandra
Thats a bit clearer - looks like some-sort of stock allocation problem. The simplest query would be:
select * from MyTable
where Field1 in (
select a.Field1
from MyTable a
inner join MyTable b
on b.Field1 <= a.Field1
group by a.Field1
having sum(Field2) < 5000)
order by Field1 The key is the link between a.Field1 and b.Field1 . However, this checks rows sequentially. If you have a record with Field1=100 and Field2=1 then earlier rows take precedence.
If you want a more optimised allocation query then you would need to resort to a UDF.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Thanks Andy
No... its not a stock-allocation problem, just something I am trying to implement without having to write a messy SP.
I ran your query and got this result... not quite what I was looking for, but thanks anyway.
field1 field2<br />
10 768<br />
11 2312<br />
4 58<br />
5 97<br />
7 345
|
|
|
|
|
Got it ....
create table #MyTable (
Field1 int,
Field2 int
)
go
insert into #MyTable values (3, 34)
insert into #MyTable values (4, 58)
insert into #MyTable values (5, 97)
insert into #MyTable values (6, 234)
insert into #MyTable values (7, 345)
insert into #MyTable values (8, 540)
insert into #MyTable values (9, 679)
insert into #MyTable values (10, 768)
insert into #MyTable values (11, 2312)
insert into #MyTable values (12, 4324)
go
select * from #MyTable
where Field1 in (
select a.Field1
from #MyTable a
inner join #MyTable b
on b.Field1 <= a.Field1
group by a.Field1
having sum(b.Field2) < 5000)
order by Field1
go My previous solution had an ambiguous column.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi, thanks for keeping at it still...
The result of the query is now only rows 10 and 11
andyharman wrote: My previous solution had an ambiguous column.
I had noticed it when I tried to run the query, and fixed it... thanks.
|
|
|
|
|
select Field2,sum(Field2)as [Sum] from tablename where Field2< 5000<br />
group by Field2
I Love SQL
|
|
|
|
|
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
|
|
|
|