|
Hello everyone,
I have a rather large query that returns some fields, including a date field. Some dates are in the future, some are in the past, and some of the results are null. I need to find a way to arrange them so the nearest dates to today are displayed, then the ones further away, then the older ones, then the nulls.
Example: given that getDate() = Jan 23 2006 2:41PM
------------
1 Jan 30 2006 3:00PM
2 Jan 20 2006 1:14PM
3 NULL
4 Dec 31 2006 11:00AM
5 Jan 24 2006 7:00PM
6 NULL
I want the result set to be arranged like so:
-----------
1 Jan 24 2006 7:00PM
2 Jan 30 2006 3:00PM
3 Jan 20 2006 1:14PM
4 Dec 31 2006 11:00AM
5 NULL
6 NULL
As perviously mentioned the query is huge so I'd like to avoid using a union if at all possible, just to reduce clutter. But if it needs to be done, it needs to be done.
Any ideas? Thanks in advance!
-------------------
abort, retry, fail?
|
|
|
|
|
jszpila wrote: As perviously mentioned the query is huge so I'd like to avoid using a union if at all possible, just to reduce clutter
 Yes, it can be done without UNIONs
SELECT dateColumn,
ABS(DATEDIFF(minute, dateColumn, @distanceFromThisTime)) AS TimeDifference,
CASE WHEN dateColumn IS NULL THEN 0 ELSE 1 END AS Nothing
FROM myTable
ORDER BY CASE WHEN dateColumn IS NULL THEN 0 ELSE 1 END DESC,
ABS(DATEDIFF(minute, dateColumn, @distanceFromThisTime)) ASC
In the above code:
dateColumn is the column that contains the date information.
@distanceFromThisTime is a datetime variable that contains the time that the query is centred on, probably getdate() from your description.
The ORDER BY clause first of all sinks the NULLs to the bottom, then it orders the remaining stuff in ascending order.
Finally, you can obviously ditch the TimeDifference and Nothing columns, I put them in to illustrate what values might be returned if you wanted to try this code in query analyzer first.
Does this help?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-- modified at 17:48 Monday 23rd January, 2006
|
|
|
|
|
Thanks! It's definitely a step in the right direction, but it's kinda goofing the order of the dates. It has successfully pushed all the nulls to the bottom, though.
My results along these lines (I'm doing some formatting in the SQL - I'm doing the formatting independently with an alias so I don't think it would be messing it up):
01/26/06
01/18/06
01/31/06
01/13/06
02/10/06
12/31/05
Seems kinda random. I've tried tweaking and nothing really affects the results coming out that way.
Any other ideas? Thanks again!
-------------------
abort, retry, fail?
-- modified at 18:25 Monday 23rd January, 2006
|
|
|
|
|
jszpila wrote: My results along these lines (I'm doing some formatting in the SQL - I'm doing the formatting independently with an alias so I don't think it would be messing it up):
01/26/06
01/18/06
01/31/06
01/13/06
02/10/06
12/31/05
Assuming you are doing a differential from today 23-Jan-2006
01/26/06 - 3 days from today
01/18/06 - 5 days
01/31/06 - 8 days
01/13/06 - 10 days
02/10/06 - 18 days
12/31/05 - 23 days
Seems reasonable to me. That's why I put the extra columns in the demonstration code, because it does seem a bit random.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Ah, alright, that makes sense now. I didn't pick up on that right away.
Thanks a ton!
-------------------
abort, retry, fail?
|
|
|
|
|
I have a stored procedure that updates data in one table and saves who updated it and the update date in another table. For some reason, the user ID is updating, but the date isn't. I'm using the GetDate() function, so it should work fine, as far as I know. It's like the update statement doesn't even recognize the second column.
Before you ask, I know the SQL is correct. In fact, if I execute the stored procedure from QA, everything works fine. Are there any issues with using the GetDate() function in a stored procedure? I wouldn't think there are.
Here's my SQL:
UPDATE UserDefinedRows
SET UpdatedByUserId = @UserID,
UpdateDate = GETDATE()
WHERE UserDefinedRowID = @UserDefinedRowID
Michael Flanakin
Web Log
|
|
|
|
|
Oh, and when the SP gets run from the application, it gets run by a DBO user.
Michael Flanakin
Web Log
|
|
|
|
|
I just logged into QA as the user the application is running as and the SP executed fine. I'm still having problems when executing it from the app, tho. This is pretty ridiculous.
Michael Flanakin
Web Log
|
|
|
|
|
UpdateDate = GETDATE()
please past the error message
hint,
if UpdateDate is datatime then it will work properly.
Qaisar Mehmood Mughal
...............................................................................
May Allah gave u success in all fields of life. (Aameen)
...............................................................................
Software Engineer
ISF - Islamabad Software Factory
3rd Floor, Software Technology Park
Constitution Avenue, Islamabad 44000, Pakistan
www.isf.com.pk
qaisar@isf.com.pk , qmmughal@gmail.com
Phone: +92 (51) 2825387 : 0321-5226623
|
|
|
|
|
how to delete a record from the databaseb using ado
yamuna
|
|
|
|
|
yamunasenthilvel wrote: how to delete a record from the databaseb using ado
The simplest way to delete values from a database is.
CString sql = "DELETE * FROM tbl_Name WHERE col =’value’"
pConnection.Execute(sql.AllocSysString(), NULL, adExecuteNoRecords);
|
|
|
|
|
hi..
i have written code for inserting the records into the table but with it itself i need to do deleting and update of records.. how to include those things with in same code.. please help me..
code is
#include "stdafx.h"
#include <stdio.h>
#include <conio.h>
#include <windows.h>
#include "impfile.h"
int main(int argc, char* argv[])
{
HRESULT hr = S_OK;
try
{
CoInitialize(NULL);
// Define string variables.
_bstr_t strCnn("Provider=sqloledb;Data Source=(local);Initial Catalog=pubs;User ID=test;Password=test;");
//_bstr_t strCnn("Server=(local);Database=pubs;Trusted_Connection=Yes");
_RecordsetPtr pRstAuthors = NULL;
// Call Create instance to instantiate the Record set
hr = pRstAuthors.CreateInstance(__uuidof (Recordset));
if(FAILED(hr))
{
printf("Failed creating record set instance\n");
return 0;
}
printf("Created RecordSEt");
//Open the Record set for getting records from Author table
pRstAuthors->Open("SELECT au_lname,au_fname,city FROM Authors",strCnn, adOpenStatic,adLockReadOnly,adCmdText);
printf("Recordset Opened");
//Declare a variable of type _bstr_t
_bstr_t valField1;
_bstr_t valField2;
_bstr_t valField3;
pRstAuthors->MoveFirst();
//Loop through the Record set
if (!pRstAuthors->EndOfFile)
{
while(!pRstAuthors->EndOfFile)
{
valField1 = pRstAuthors->Fields->GetItem("au_lname")->Value;
valField2 = pRstAuthors->Fields->GetItem("au_fname")->Value;
valField3 = pRstAuthors->Fields->GetItem("city")->Value;
printf("%s \t - %s \t - %s \t\n",(LPCSTR) valField2, (LPCSTR) valField1,(LPCSTR)valField3 );
pRstAuthors->MoveNext();
}
}
pRstAuthors->Close();
getch();
}
catch(_com_error & ce)
{
printf("Error:%s\n",ce.Description);
}
CoUninitialize();
return 0;
}
yamuna
|
|
|
|
|
hi...
how to pass muliple option from a drop down to the stored procedure as string and then pass the values to the conditional clause of the procedure.
sathy
|
|
|
|
|
imsathy wrote: muliple option from a drop down
A drop down only permits one option to be selected at a time.
imsathy wrote: to the stored procedure as string
string value = MyDropDownList.SelectedValue;
SqlCommand cmd = new SqlCommand(myConnection, "StoredProc");
cmd.CommandType = CommandType.StoredProcdure;
cmd.Parameters.Add("@paramName", value);
SqlDataReader reader = cmd.ExecuteReader();
imsathy wrote: then pass the values to the conditional clause of the procedure
For example:
CREATE PROCEDURE dbo.StoredProc
@paramName varchar(100)
AS
SELECT * FROM SomeTable
WHERE someColumn = @paramName
GO
Does this help?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Select Products
im filling this dropdown dynamically thro xml and js
i want the multiple selected values from thus drop to be a comma separated string and pass it as a parameter to a stored procedure..
and is there a way to pass these multiple values to the where clause of the sp
here how to give the multiple values of the serial nos from the string of the sp to this query
SELECT
...
...
...
WHERE serial_number =@serialNo
sathy
|
|
|
|
|
Are you required to use a combobox dropdown? Why I ask is because listboxes can do multiple selections.
PJC
|
|
|
|
|
thanks guru but my requirement is for tht only ....
i have sorted tht out ..
sathy
|
|
|
|
|
Glad you sorted that out.
Have a good day,
Paul
|
|
|
|
|
Hi guys
I have a problem:
I need to execute a command text in a stored procedure
begin
.
.
.
declare @sql nvarchar(1000)
set @sql = '
decalre @v1 int
declare @v2 int
...
'
exec(@sql)
end
i need to get value of @v1 and @v2 after exec(@sql)
without using temporary table.
|
|
|
|
|
WDI wrote: without using temporary table.
@v1 and @v2 exist only within the scope of the EXEC statement, in order to get them out you need to SELECT them and have the EXEC be the source of data for an INSERT statement (normally into a temporary table - although it could be a permanent table).
Why are you not wanting to use a temp table?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
cos I need to execute stored procedure more than 1000 time and i am worry about cpu and memory usage!
|
|
|
|
|
WDI wrote: I need to execute stored procedure more than 1000 time and i am worry about cpu and memory usage!
If you are worried about performance then why are you using dynamic statements in the first place?
Here is a tip: Develop the software first in a way that works and is maintanable - then seek out the bottlenecks (the areas where poor performance is a problem) and fix them. Putting performance up front will lead to poorly designed applications that will be difficult to maintain.
That said, another solution might be for your dynamic SQL to generate a stored procedure (assuming it has the rights to do that) with @v1 and @v2 as OUTPUT parameters. The dynamic SQL creates the stored procedure, and your non-dynamic SQL will execute it, retreiving the values and then DROP the stored procedure. However, the stored procedure created would need a unique name so calling it may be a problem. And, I don't know the performance impact of creating and dropping stored procedures 1000 times - it would probably be worst that the temp table solution.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Firstly, I apologise if this is the wrong group to post in, my problem involves technologies covered in three different groups. C# is the language, .NET 2.0 is the platform, SQL Server is the DBMS.
I'm developing on my local machine and have a named instance of SQL Server running. I'm trying to connect to the instance of SQL Server using SQLDMO (because I want the connection to return a list of all the databases available on that server, and SQLDMO seems the best way of doing it)
My user account is set up as a trusted connection via Enterprise Manager, but I'm unable to log onto the server. The app returns an error message of:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'foo'
The code in question looks like:
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();<br />
srv.Connect(this.cmbServer.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
I have also tried connecting without providing username or password details, but have had the same result. Any ideas?
|
|
|
|
|
usernamed wrote: srv.Connect(this.cmbServer.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
If you are using Windows Authentication why are you passing a user name and password? With Windows Authentication your credentials are passed automatically, this is sending a specific username and password which SQL Server will interpret as being for a SQL Server Account (not a windows account).
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi Colin,
Thanks for responding. I should have written in my initial post that I'd tried not passing a username or password, but was getting the following error:
No overload for method 'Connect' takes '1' arguments(CS1501)
As such, I thought that the Connect Method forced me to put in a username and password, even if they shouldn't be required.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
