|
Thanks ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
I was born intelligent
Education ruined me!.
|
|
|
|
|
I want to create a Stored procedure that looks up data based off the last full month and also one for year to date data. These SP will fire as part as a script so there can be no user input regarding the dates. Is there a way to do this?
Chris
|
|
|
|
|
Sure.
Just put a condition like below for data from the last month
WHERE MONTH(dtmTransactionDate) = MONTH(DATEADD(m, -1, GETDATE()))<br />
AND YEAR(dtmTransactionDate) = YEAR(DATEADD(m, -1, GETDATE()))
If you need to get specific date (e.g. starting from 15 of Jan to 15 of Feb) just add DAY condition to it.
For year to date, just subtract 1 year from the current date using the DATEADD function.
Edbert P.
Sydney, Australia.
|
|
|
|
|
If you are interested in the last full month and need the last day - you run into problems with February.
Try:
declare @month char(10),@lastmonth datetime
set @month = Convert(Char(6),getdate(),112)+'01'
Print @month
select @lastmonth = dateadd(m,-1,cast(@month as datetime))
print @lastmonth --First day of prior month
select @lastmonth = dateadd(d,-1,cast(@month as datetime))
print @lastmonth --Last day of prior month
Barbara
|
|
|
|
|
create table TestLoan(
loanid int identity(1,1),
custid int,
loanvalue varchar(50)
)
GO
INSERT INTO TestLoan values(2,'First Loan')
INSERT INTO TestLoan values(2,'Second Loan')
INSERT INTO TestLoan values(2,'Third Loan')
INSERT INTO TestLoan values(2,'Fourth Loan')
INSERT INTO TestLoan values(2,'Fifth Loan')
GO
Does anyone know a way I could return the 3 most recent loans in the same row? The trick would be making it always be the 3 most recent loans, no matter what else was added.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Here is one that you might be able to use:
DECLARE @TotalLoanValue varchar(150)<br />
<br />
SELECT TOP 3 @TotalLoanValue = COALESCE(@TotalLoanValue + ', ', '') + loanvalue<br />
FROM TestLoan<br />
ORDER BY loanid DESC<br />
<br />
SELECT @TotalLoanValue
This will work with SQL 2K. I haven't tested it with any other DBs.
It will return something like this:
Fifth Loan, Fourth Loan, Third Loan
I hope this helps 
Edbert P.
Sydney, Australia.
|
|
|
|
|
Wow Edbert - I'm not even sure how this works. Isn't COALESCE meant to return the first non null value? Can you explain the mechanics a bit?
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Sorry, it was 9AM in the morning and I haven't had my coffee 
Yes, COALESCE is meant to return the first non-null value from the values passed.
Have a look at this code:
SELECT TOP 3 @TotalLoanValue = COALESCE(@TotalLoanValue + ', ', '') + loanvalue
The first time the @TotalLoanValue variable is processed, it will return NULL as the value. I use COALESCE to return empty string ('') instead, so when I concatenate the next rows, they will not result in null.
You can also use ISNULL(@TotalLoanValue + ', ', '') instead of COALESCE(@TotalLoanValue + ', ', '') as they're basically doing the same thing, except that ISNULL only accepts two values.
What the whole SQL statement does is just to concatenate the result of the query into one comma-delimited string. Just change the delimiter if you need to.
Good luck! 
PS: Use WHERE loanvalue IS NOT NULL if you want to always return 3 records.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Self joins! There are a beautiful thing when some Neanderthal wants a denormalized report from you nice orderly normalized data structure.
Try this (works on sql server. Access is ???):
select top 1 *
from
testloan t1,
testloan t2,
testloan t3
where
t1.loanid<>t2.loanid and
t2.loanid<>t3.loanid and
t1.loanid<>t3.loanid
order by
t1.loanid desc,
t2.loanid desc,
t3.loanid desc
Bill
|
|
|
|
|
I'm a little confused. How do I know what's being returned to me from a stored procdure when I call it from my C++ app?
For instance I want to write a program that returns back all users accessing a database. I found the stored procedure called "sp_who". I think I understand how to call it but I'm unsure how I find out what is returned to me. Is there not a way to do this on the fly in the code?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
SELECT loginame, hostname FROM Master.dbo.sysprocesses
WHERE loginame != 'sa'
this should give you all the users loged on the server
Will
|
|
|
|
|
I am preparing to migrate a legacy system using foxpro tables (.dbfs) to VB.NET. During the transition stage, some of the new system needs to access data inside foxpro tables. I am now coding the data access component to handle these tasks. My initial code testing revealed, that the time to transfer records from foxpro tables to a ADO.NET's dataset table took a lot of time.
I am connected with a pre-need company (similar to insurance) so we basically keep track of planholders and their premium payments (montly, quarterly, semi-annual, annual. payment term is up to 10 years)
The code is the basic dataset creation and fill. The SQL statement is "SELECT * FROM premiumpayments WHERE account = planholderaccountnumber". My test data reads 84 records representing 7 years of monthly preimiun. It took 3-5 seconds to complete from my local drive.
I searched the archive but did not found any related postings. Is there a way to speed up this process? I would really appreciate any help. Thanks!
|
|
|
|
|
I learn the follow commands to backup and restore of SQL Server:
backup database northwind to disk = 'c:\northwind.bak'
restore database northwind from disk = 'c:\northwind.bak'
Ok, but my database is in server of my ISP... How I do to perform backups and restores? I need contact them? 
And how I do to download the backups???
Thanks for all by the help.
|
|
|
|
|
I suggest contacting them. Remember that the path you specify in the BACKUP DATABASE command is relative to the database server machine, i.e. the above command will backup to the root of the server's c: drive.
If you don't specify a path, the backups will go to the server's default backup directory.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi :
I am a starter in .NET technology and just trying to save this soap formatted XML serialized from Account class, mentioned below.
========================================================
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:Account id="ref-1" xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Accounts/Accounts%2C%20Version%3D0.0.0.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3Dnull">
<Name id="ref-3">Atanu</Name>
<Balance>500</Balance>
</a1:Account>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
==========================================================
Account table in SQLServer2K, has 2 fields: Name & Balance and currently has no data. When trying to insert using usual method (DataAdapter, CommandBuilder, DataSet), it is inserting NULL in the Name col and 500 in the Balance column.
Then I removed the (id="ref-3") from (<Name id="ref-3">Atanu</Name>) of the above generated XML and now the value "Atanu" is correctly inserted in the Name col? But then I should not have done this.
Could you please tell me how to insert the above XML into Account table. Also could you please tell meaning of the phrase "<Name id="ref-3">Atanu</Name>" found in the above XML, generated using serialize method and soap-formatter - I mean meaning of "id=ref-3" attrb. in the name element and is there any way to get rid of the attrb. while the XML was generated.
Thanks very much.
Atanu
|
|
|
|
|
What is your code? You don't need to insert the whole XML, SQLServer 2000 can do it for you and return results from a table in a XML format. For start point see sp_xml_preparedocument in SQLServer Online Book to see how to insert XML file ito your database.
Mazy
"Man is different from animals in that he speculates, a high risk activity." - Edward Hoagland
|
|
|
|
|
Hello
this is sam, and I have a big problem with ASP !!!!
I want to retrieve data from a database and then send this data to a url as a query string.
ex: http://77.125.192.120:1428/Recieve.asp?tel=20123335357&Msg=Misssshhhhh%20this%20is%20this?&CC=20&user=SMS&pass=Ed3kWKad&enc=1
enc:
Encoding: if message is ucs2 =0;
if message is plain Text =1
and if the Msg is in arabic so it is encoded and then sent.
Please help me with this. how can i do it in VBScript, ADO ?
I want the application to be run without stopping, to send data to the url without stopping. any new data comes, is sent to the url directly.
yours
sam.
Sam2004
|
|
|
|
|
I have a datagrid bound to a sqlserver table. It has 2 columns. how can i write a statement that will evaluate when i change data in a cell of column 1 and trigger the data in column 2 to change base off of the column 1 change?
|
|
|
|
|
hello,
i am in need of ODBC(C++) code that performs join (natural or inner) between 2 tables based on some condition.But i dont know where to look for. Can someone please give me some pointers or source.
(this is not my assignment!! i need to it for someother purpose and unfortunately i dont have time to learn ODBC c++ interfaces).
thanks a lot
-Ganesh.M.Ramaswamy
|
|
|
|
|
I'm not sure the ODBC library has this feature. Normally you join tables using SQL, in the statement that you pass to SQLExecDirect or SQLPrepare, or within a stored procedure (which you call using a suitable {call=procname} syntax with either of the above functions).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi
Someone knows if it's possible replicate data from a SQL Server 7 to a SQL Server 2k. I try but it's no possible. There is not a third part software to make this??.
Thanks
----
hxxbin
|
|
|
|
|
you are sorta trying to make water run uphill, why not upgrade the SQL7 server to 2K?
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
Er, maybe because of the horrendous pricing model of SQL Server 2000? Most companies that use 2000 (like mine) have a mixture, and that won't change any time soon.
Regards,
Jeff Varszegi
|
|
|
|
|
Hey
I couldn't find out the exact procedure to use sql server ce in embedded visual c++. If u have any tutorials or weblink, Plz help me.
Thanks
JP
ALL THINGS ARE POSSIBLE UNTIL THEY ARE PROVED IMPOSSIBLE-AND EVEN THE IMPOSSIBLE MAY ONLY BE SO,AS OF NOW
|
|
|
|
|
Hi, gurus
I have a web method, getting DataSet with locale property set to "en-GB". My local settings are different. DataSet has columns of datetime type and I was suprised that when I get data using
DataSet myDS = goToWebMethod(...);
and
Inserting data into local DB from myDS as is
I've got permanent 6 hr difference in time !
Any ideas?
JackDotNet
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
