|
I m facing a problem in this query
select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate,<br />
convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp <br />
as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid <br />
where(a.eventtype='Single Month' or(a.eventtype='All Month' and <br />
(select substring(convert(nvarchar,edate,102),9,4) from event where eventtype='All Month')>( select substring(convert(nvarchar,getdate(),102),9,4)) ))order by a.eventdateselect * from event<br />
<br />
In event table there are some fields like eventid,eventdate,eventtype n etc
In user_info there are userid, name n etc fields
i wanna get all record when
1> eventtype is Single Month and the eventdate is in current month n eventdate is heigher than currentdate
2> eventype is All Month and eventdate is heigher than current date
eventid -----eventdate ------- eventtype
1 -----2008-04-15 00:00:00 ----- All Month
2 -----2008-04-20 00:00:00 ----- Single Month
3 -----2008-04-16 00:00:00 ----- Single Month
4 -----2008-04-16 00:00:00 ----- All Month
The answer shd be eventid 2,3,4
The Great Pleasure In Doing That Things That Other People Say U Can't By Doing This U Can Shut Their Mouth
|
|
|
|
|
Try this
select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate,<br />
convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp <br />
as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid <br />
WHERE a.eventdate > getdate()<br />
AND (<br />
(a.eventtype = 'Single Month' and left(convert(varchar,getdate(),102),7) = left(convert(varchar,edate,102),7))<br />
OR (a.eventtype = 'All Month')<br />
)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi All,
I need to query the DB2 database tables and populate the Excel work sheets (one sheet for single table) using VB Macro.
Also, I need the validation in the Excel sheet itself. (DB2 database credetials)
Can you pls help me to get start?
Thanks,
Jeyakumar 
Jey
modified on Wednesday, April 16, 2008 6:11 AM
|
|
|
|
|
Jay_se wrote: I need to query the DB2 database tables and populate the Excel work sheets (one sheet for single table) using VB Macro.
Also, I need the validation in the Excel sheet itself. (DB2 database credetials)
I believe Excel VBA does ADO, so you should be able to connect to the DB2 database and do your stuff. For connection information, look up www.connectionstrings.com[^].
|
|
|
|
|
Thanks ChandraRam, It is helpful.
Jey
|
|
|
|
|
Hi ,
im using two tables , tbl1,tbl2 . im multiplying tb1.column1 ,tbl2.column and displaying as result . i want to display sum(column1.column2) as total (another column).
can any body tell me the query for this ,
Thank u.
C.S.n
|
|
|
|
|
I feel I have the wrong handle on your problem but...
If you want the additional of col1 + col2 then it is the same method as the multiply
If you are after the sum of all the records preceding the current record then you need to look into running totals (depends on your DB)
if you want the sum of the 2 cols included in the result set you need to do the query 2 times, 1 to get the total into a var and the seconed to include it in your result set
You really need to define your problem a little clearer. Remember we have not gone through the process of getting where you are stuck and do not have the code in front of us.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi ,
i will explain where i stuck ..
i'm having two tables transaction and pricing . in transaction table i'm
having number of seats column and price/seat in pricing . here i want a
column which displays (no. of seats * price/ seat ) and sum of all these
rows as next row or separate column.i'm getting(no. of seats * price/seat)
using join but im not getting how to do sum of all these rows .
can u tell me this pls ..
thanks
csn
|
|
|
|
|
Depending on whether you are trying to just add the two columns together, or add the sum of the columns together, you could either do:
select tbl1.column1 + tbl2.column as summation or
select sum(tbl.column1 + tbl2.column) as total
|
|
|
|
|
mrsuman wrote: im using two tables , tbl1,tbl2 . im multiplying tb1.column1 ,tbl2.column and displaying as result . i want to display sum(column1.column2) as total (another column).
can any body tell me the query for this ,
Is this what you are looking for:
select tbl1.column1,tbl2.column2,tbl1.column1*tbl2.column2 as Result, sum(tb1.column1*tbl2.column2) as Total
This query will have the Total column as the same value for all the rows.
|
|
|
|
|
Hi,
Sorry for late reply .
here im having price of seat in one table and no. of seats in another table.
i want to get result(Price * No. Of Seats ) as amount to pay as one cloumn and total of these values as another column.
i think now u can understand my problem ..
can u tell me the query how can i do this .
thanks .
|
|
|
|
|
i will explain where i stuck ..
i'm having two tables transaction and pricing . in transaction table i'm
having number of seats column and price/seat in pricing . here i want a
column which displays (no. of seats * price/ seat ) and sum of all these
rows as next row or separate column.i'm getting(no. of seats * price/seat)
using join but im not getting how to do sum of all these rows .
can u tell me this pls ..
thanks
csn
|
|
|
|
|
Can you give an example output you want from this query?
|
|
|
|
|
Thanks for u r reply ..
ex :
result
------
200
350
500
400
250
in next row or separate column i want sum of these rows .
|
|
|
|
|
If having it in the same row is Ok, then my earlier query should work for you (providing of course that you specify the correct join condition).
|
|
|
|
|
Hi...
I'm trying to get programmatically a backup file of my database, here is the code:
<br />
string BakFilePath = System.IO.Path.GetTempFileName();<br />
<br />
SQLDMO.Backup bak = new SQLDMO.BackupClass();<br />
bak.Devices = bak.Files;<br />
bak.Files = "[" + BakFilePath + "]";<br />
bak.Database = "MyDB";<br />
bak.BackupSetDescription = "Something";<br />
<br />
bak.SQLBackup(srv);<br />
Under VS2005 c# SQL Server 2005, in debug mode there are no problem.
When installing on a win xp with SQL Express 2005, I have the following error:
<br />
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\Documents and Settings\JOZ\Local Settings\Temp\tmp19.tmp'. Operating system error 5(Access denied).<br />
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.<br />
I like sqldmo backup because is really quick... but this issue is driving me crazy... Can somebody help me please? 
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
Maybe your SQL Server 2005 service is running under an account which has permissions to the backup folder, which the account used by SQL Express does not.
Try giving write permissions to the account under which SQL Express runs.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi all,
I m new in VC++, i m working on MFC-SDI document type application,
in my application i want to use MS-Acess 2007 Database,
but i dont have any knowledge about this.
please anyone can tell me at starting how an i use Access databse in my application.
thanks in advance.
|
|
|
|
|
|
i have 2 table in my database:one table for insert records,another table for backup of first table.i want write a trigger that execute when insert one record in first table(execute backup process)and calling a web service on this trigger.please help me as soon as soon
elham.a
|
|
|
|
|
I have a program written in VC++6 that connects to a database through SQL-ADO. Occasionally the DB server will disconnect or reconnect or fail in some way. Right now I have no indicator when this happens on my program side, so users who are currently logged don't know about the error and when they try to save their work an SQL error comes up saying...
Error(0)80004005:Connection Failure
I'm looking for some kind of Disconnect or Reconnect event or indicator of some sort in SQL-ADO. SQL-DMO has the ConnectionBroken Event which seems to be about what I want. Is there anything similar in ADO? If not, what is the best way to detect a broken connection? I'd rather not resort to polling the server if possible.
|
|
|
|
|
If you're using System.Data.SQLClient.SQLConnection, you have a property called State (SqlConnection.State) which will return the current state of the connection.
Reference
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Thanks. I can use the State property, but it isn't updating correctly. When the error occurs, state is still set to Open, even though the connection has been broken. Is there any kind of message or event that happens when there is a disconnect?
|
|
|
|
|
Hi
here is what I have
1- I have 2 different tables in 2 different databases
2- I need to write a stored procedure to start a transaction to add data in both tables so if any error occured then rollback
so I want to access the table in the other database to add data to it
any help
thanks
Shaimmaa
|
|
|
|
|
Get the data from one database table to anothter databse
select * from databasename.dbo.tablename
smile 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
