|
Dear Friends,
i want to declare an float variable and stored data in that variable the scenario is as given below
suppose i have table name Table_1 having three column BranchID, Payment and Date
BranchID, Payment, Date
1, 2000, 10/05/2008
1, 1000, 11/05/2008
1, 3000, 12/05/2008
2, 2000, 10/05/2008
i want to create an procedure which give output as 'Branch ID 1 made payment of 6000 Rs' passing the parameter BranchID and Date
Parameter value BranchID = 1 and Date BETWEEN CONVERT(DATETIME, '2008-05-10 00:00:00', 102) AND CONVERT(DATETIME, '2008-05-12 00:00:00', 102)
how can i do this please help me thanks in advanced
Sasmi
|
|
|
|
|
Do you want to get all values of BranchID by sum of Payment is 6000 and between two dates?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I Want outut if my inpout is Branch ID = 1 and Date BETWEEN CONVERT(DATETIME,
'2008-05-06 00:00:00', 102) AND CONVERT(DATETIME, '2008-05-13 00:00:00', 102)
So it should be 6000 Rs which is sum of all record whose criteria satisfy the branch ID and Date as provided by user
Sasmi
|
|
|
|
|
currently i m using SQL server 2000.
i need a query to insert "NULL" inside my table wherever the field lenght=0.
help me! - KARAN
modified on Monday, May 12, 2008 4:44 AM
|
|
|
|
|
Use a case statement to check the length -
case when datalength(value) = 0 then null else value end
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob......
But the thing is how to proceed.
I m having nearly 50 columns in my database and above 5000 records.......... how to do this using "CASE"?
i want to implement all the 50 columns one by one?
|
|
|
|
|
There is no easy way in sql. If you are calling the insert from an application then perhaps you could do it within the code there, otherwise its a case of string the case statments together..
insert into table1(cola, colb,.....)<br />
select case when datalength(valuea) = 0 then null else valuea end, case when datalength(valueb) = 0 then null else valueb end .....
Personally I would write some sql to generate the insert statement.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanks yaar! could it handle using TRIGGERS? that is, after user update or insert, it has to check the inserted values and if column length is zero, insert null??? this can be done? - KARAN
|
|
|
|
|
Hi
I want to calculate sum after the count of the column.
e.g my query is :
Select Name,(Count(Value) as cnt from NameTable1 group by Name
result : Name cnt
Riya 2
Jiya 5
reema 4
I want to calcluate sum of count of value field that is :11
Plz let me query for this.
Thnx
|
|
|
|
|
Hi
I have caluculated sum 11 from
Select Sum(cnt)as cnt1 from
(Select Name,(Count(Count))as cnt from Nametable1 group by Name)a query
But i want output in one resultset as:
Name cnt cnt1
Riya 2 11
Jiya 5 11
reema 4 11
Plz help as soon as possible
thnx
|
|
|
|
|
Not sure if the following will do what you want:
SELECT Name, cnt, sum(cnt)
FROM (
SELECT Name, Count(Count)) as cnt FROM NameTable1 Group By Name
) AS InnerQuery
GROUP BY cnt, Name
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
You don't need the group by in the inner query
Alsvha wrote: SELECT Name, Count(Count)) as cnt FROM NameTable1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ahh you're right. Just took the inner query from the poster
|
|
|
|
|
This will work but it is not efficiant as it sums the count for the table for each row.
<br />
create table #b1(cola int)<br />
<br />
declare @a int<br />
<br />
set @a = 0<br />
<br />
while @a < 10<br />
begin<br />
insert into #b1 select @a<br />
select @a = @a +1<br />
end<br />
<br />
set @a = 4<br />
while @a < 10<br />
begin<br />
insert into #b1 select @a<br />
select @a = @a +1<br />
end<br />
<br />
<br />
select cola,count(*),(select count(*) from #b1) <br />
from #b1<br />
group by cola<br />
<br />
<br />
drop table #b1
You would be better having this code:
declare @total int<br />
<br />
select @total = count(*) from #b1<br />
<br />
select cola,count(*),@total<br />
from #b1<br />
group by cola
Bob
Ashfield Consultants Ltd
|
|
|
|
|
try this
SELECT Name,<br />
(SELECT SUM(t1.Value) <br />
FROM myTable AS t1) AS sumofcnt,<br />
(SELECT COUNT(t1.Value) <br />
FROM myTable AS t1<br />
WHERE (t1.Name = myTable.Name)) AS cnt<br />
FROM myTable<br />
GROUP BY Name
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
modified on Monday, May 12, 2008 6:09 AM
|
|
|
|
|
hi
how can i do select query that have match case in access
example:
car - is not - CAR
thanks
|
|
|
|
|
If you are using the design view - in the "Field" write: UCase(FieldName)
and in the Criteria write CAR (or whatever you want it to be in upper case)
|
|
|
|
|
That will result in a case-insensitive match, not a case sensitive one, which is what the poster is asking for.
|
|
|
|
|
A quick google search yields this.[^]
|
|
|
|
|
Hi all,
Im having problem with Simple style combobox.
actualy i have a form window with a combobox and other one is Textbox.
combobox style is Simple(CBS_SIMPLE). there is so meny text. when user type some text and hit enter key it will be select current item.
but its nothong happen. user have to press down arrow.
my question is this.
i don't wanna use down arrow key, i want to use enter key to sellect current item.
what its posible to change Keys event?
please give me any idia with small source.
thank you
bir
|
|
|
|
|
This is a database forum and is not the right place to post this question. Please post it to vc++ forum.
|
|
|
|
|
Hi,
i want to Create a trigger [for intert/update] dynamically for a table which has been created dynamically.
Can anyone help me?
thanks in advance
sri
|
|
|
|
|
|
Hi, I am having a strange issue when using the RSClientPrint component to print reports from Reporting Services. I am printing tickets, and everything worked just fine until I tried to print a 2 pages report. The first page is printed ok, but the 2 page can't be printed.
By clicking the preview button of the component, I can see the first page of the report, but the second page shows an error.
Any suggestions? What am I missing? Is there a special attribute I have to define for my component? I am using the margins attributes, height and width, and cultures.
Thank you very much in advance.
|
|
|
|
|
Some code would be nice or at least pseudo just so we know what you have done. Anyhow, are you calculating when first page is ended so printing can continue to 2nd page? Just paste some code and then we can help.
|
|
|
|