|
The query I'm working on a query that selects all introduced leads. I have, of course, managed this part without a problem but the query needs to select only those that were introduced within the past month.
I have found the GETDATE keyword but how can i use this to find introduced leads. The leads have a date time stamp when they were created.
?
|
|
|
|
|
Use also DATEADD to work out when a month ago was.
e.g.
SELECT * FROM IntroductionLeads
WHERE CreatedDate >= DATEADD(month, -1, GETDATE())
|
|
|
|
|
Ok thanks for that. Works great. I take it there is no DATESUBTRACT then?
|
|
|
|
|
Sam Heller wrote: I take it there is no DATESUBTRACT then?
No, just supply a negative number to DATEADD.
|
|
|
|
|
SET @Stringer = '<br />
SELECT [ProductID],<br />
(SELECT Tagline FROM Products WHERE ProductID = P.ProductID) As Tagline,<br />
(SELECT U.[CompanyName] FROM Products K, Users U WHERE K.ProductID = P.ProductID AND K.UserID = U.UserID) As [Company Name],<br />
(SELECT Logo FROM Products WHERE ProductID = P.ProductID) As Logo,<br />
[Actual Speed],<br />
[Actual APR],<br />
[Actual Broker Fee],<br />
[Completion Ratio],<br />
CONVERT(decimal(18, 1),(([Speed] + [APR] + [BrokerFee] + [Completion Ratio] + [Commission] + [List Bid]) / 6)) AS HSScore <br />
FROM #temp P'
The following code does not seem to work. The error is produced from the line
(SELECT U.[CompanyName] FROM Products K, Users U WHERE K.ProductID = P.ProductID AND K.UserID = U.UserID) As [Company Name]
Is it not possible to put table aliases inside a string and then execute it?
|
|
|
|
|
I would have done them as joins rather than inner queries.
Sam Heller wrote: Is it not possible to put table aliases inside a string and then execute it?
Yes, you can use table aliases inside dynamic SQL.
|
|
|
|
|
Table aliases are fine within dynamic SQL.
What is the error message? Try adding PRINT @Stringer after this code and before the EXEC or sp_executesql that you are using to run it.
|
|
|
|
|
Stupid Stupid Stupid. Turns out I had set the varchar for the string to 500 and it query had just about reached that so the string was missing the FROM section of the query.
Thanks anyway though
|
|
|
|
|
You're not alone 
|
|
|
|
|
Hi ,
Please someone show me a code that updates records of a table one by one using a cursor . I counld'nt find one !.
|
|
|
|
|
|
Is there anybody who has a merge module or any other way of deploying SQL Server 2005? The InstallShield site says they still don't have it, and that the users should make their own custom setup procedures, and Microsoft still does not show the way of easily deploy SQL server 20005 Express with any installation. But they did ban the MSDE from Vista, so any installation that includes MSDE and is targeting Vista needs to be changed.
Thanks.
Sarajevo, Bosnia
|
|
|
|
|
Hello
I have an application writing Japanese characters to the database. When I use MS SQL 2000, it works fine - the complete string is stored, but when I use MSDE, the strings seems to be truncated 
Any ideas? Is there any setting in MSDE that I'm overlooking?
Thanks for any help
Chandra
|
|
|
|
|
Hi Guru's,
What's the trim command on SQL?
Thanks
Dom;)
|
|
|
|
|
Hi,
Using trim in SQL 2000 - ltrim() and rtrim() as given below: removes trailing and leading blanks. Hope this helps you. 
For Example:
------------
use pubs
go
select ltrim(rtrim(fname)) from
employee
go
- Harini
|
|
|
|
|
Thanks Harini
|
|
|
|
|
I have a table that stores two types of update records. At the moment I'm using a bit field to determine the update type, e.g. IsStatusUpdate. This value is 0 for comment updates and 1 for status updates. It has been bothering me recently that this could be made a char(1) field, with the more readable 'S' for status updates and 'C' for comment updates.
My only misgiving here though is the use of a literal value that is seemingly arbitrary to the user. With 'IsStatuUpdate bit, not null', I know I must store 1 for a status update. With 'UpdateType char(1), not null', I don't know that I must store 'S' for a status update. Any thoughts on this?
|
|
|
|
|
Bit fields will required less storage.
Also better when using with code (C# for example) because you'll need to deal with true,false. not magic values (S,C).
In the GUI, you can use a check box.
I think it's always better to separate how you display data from how you store it.
|
|
|
|
|
If I have a DataRow and a foreign key constraint (1:N), is there a way to get a DataTable with all of the childs of the relationship?
e.g. if I have
DepartmentDataTable dep = ...
EmployeeDataTable emp = ...
ForeignKeyConstraint fk = new ForeignKeyConstraint(dep.IDColumn, emp.DepartmentIDColum);
DepartmentRow hq = dep.FindByID(666);
EmployeeDataTable allHQEmployees = magic</>(hq, fk);
?
Developers, Developers, Developers, Developers, Developers, Developers, Velopers, Develprs, Developers!
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
Linkify!|Fold With Us!
|
|
|
|
|
Have you tried using the DataRow.GetChildRows() method? If you define a DataRelation instead of a ForeignKeyConstraint (which will be added along with the DataRelation) you can use DataRow.GetChildRows() to get a DataRow[] reference. Once you have DataRow[] you could instantiate a new EmployeeDataTable and copy DataRow[] into the new data table using the DataTable.ImportRow() method.
You could alternatively do something using a DataView, sort by DepartmentID and call DataView.FindRows(), then perform the same copy operation I mentioned above.
But other than creating a new table object and copying rows from one table to the next there is no way to return a new data table based on a "filter" of an existing table.
|
|
|
|
|
If I call GetData() on an System.Data.OleDb.OleDbAdapter, does that actually read all data from the database?
Or does it just open the rowset, and the data is read only when I access the actual rows?
Developers, Developers, Developers, Developers, Developers, Developers, Velopers, Develprs, Developers!
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
Linkify!|Fold With Us!
|
|
|
|
|
System.Data.OleDb.OleDbAdapter doesn't exist, but System.Data.OleDb.OleDbDataAdapter does. But it doesn't have a GetData() method, it has a fill method which loads a disconnected dataset or datatable will all the results of the query and then disconnects (if the connection was closed before Fill() was called.
|
|
|
|
|
Is it possible to have a constraint that depends on a column value? For instance, I would like my DocUpdate table to require NewDocStatus is not null when IsStatusUpdate = 1, but allow NewDocStatus to be null in other records.
|
|
|
|
|
Using a check constraint you can do the following:
<br />
CREATE TABLE myChkTable (IsStatusUpdate BIT, NewDocStatus VARCHAR(25))<br />
go<br />
<br />
ALTER TABLE myChkTable ADD CONSTRAINT chkTblConstraint CHECK (<br />
(IsStatusUpdate = 1 AND NewDocStatus IS NOT NULL) OR IsStatusUpdate = 0<br />
)<br />
go<br />
<br />
--the first three will succeed<br />
INSERT INTO myChkTable VALUES (0, null)<br />
INSERT INTO myChkTable VALUES (1, 'status')<br />
INSERT INTO myChkTable VALUES (0, 'not update')<br />
<br />
--this will fail<br />
INSERT INTO myChkTable VALUES (1, null)<br />
<br />
|
|
|
|
|
I don't mean to sound stupid.
But what does the term "MRD" stands for in SQL Server?
Does anybody know?
Thanks.
R
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
