|
Use parameters then you don't need to worry about date formats and it reduces your worries about a SQL Injection attack. Also, if you are going to put a date into a SQL string it is better to use the ISO format which is yyyy-mm-dd becuase it is locale independent.
|
|
|
|
|
You have too many items in your GROUP BY clause. The GROUP BY clause should contain only the non-aggregate items that are also in the SELECT clause. If your GROUP BY clause contains more items then it will appear to be producing duplicates (although it isn't, it is just throwing away the information the distinguishes one row from the other).
|
|
|
|
|
Don't you think it is increadibly rude when someone asks a question, you then answer it and a little while later they delete the original question. That means there is now an orphaned answer to a question no one now knows. This really is NOT helpful to anyone searching the forum for the answer to a similar problem.
I really have to dispair sometimes - I really do. 
|
|
|
|
|
Colin Angus Mackay wrote: I really have to dispair sometimes - I really do.
Keep your chin up Colin...you do a fine job around here (IMO).
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Colin Angus Mackay wrote: you then answer it and a little while later they delete the original question.
That is why I make the attempt to put the question into my reply. Then they can delete it all they want but their question still sits there in my reply :->
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
|
PaulC1972 wrote: That is why I make the attempt to put the question into my reply. Then they can delete it all they want but their question still sits there in my reply
That's a good idea. Especially since it looks like Colin is either arguing with or insulting himself right now. 
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Chris Meech wrote: Especially since it looks like Colin is either arguing with or insulting himself right now.
The problem comes when I argue with myself to such an extent that it becomes violent. It's not easy to hit yourself in the face with a chair - I always manage to duck out of the way in time.
|
|
|
|
|
LOL.
Note to self. Not a good idea to be standing behind Colin when he's arguing with himself. Be aware of flying chairs.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Is it possible to use a table in a foreign database via ODBC in a MS SQL Server 2005 SELECT query. I have data in a legacy database that I would like to use with data within SQL Server 2005.
For example, with MS Access you can "Get External Data" and link to tables in other database sources. I would like to do something similar in MS SQL Server
Thanks
Steve Jowett
|
|
|
|
|
You could always link your other database in as a Linked Server.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi there,
I've got a Messages table which looks roughly like this:
SenderID (int)
RecipientID (int)
TimeStamp (datetime)
Body (varchar(512)
Now I'd like to do a select which gets me (RecipientID = 1) the first message (depending on the timestamp) of each sender. Example: Sender ID 5 has sent two messages, the recordset should contain the oldest message of this fella. Sender ID 6 has sent five messages, the recordset should contain the oldest message of this guy as well.
I thought this could be done by tying a DISTINCT to a column. I hoped I could do it somehow like this:
SELECT DISTINCT(SenderID), TimeStamp, Body FROM Messages WHERE RecipientID = 1 ORDER BY TimeStamp DESC
I was just guessing into the wild. Is there an easy way to do that?
Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Try
SELECT M.SenderId, M.TimeStamp, M.Body FROM Messages AS M
WHERE M.TimeStamp = (SELECT MIN(M1.TimeStamp) FROM Messages AS M1 WHERE M1.SenderId = M.SenderId)
|
|
|
|
|
Distinct won't do this because unless the timestamp was the same for both records, then you have two distinct records. What you could do is to select based on the MIN timestamp (there are may ways to do this).
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest.
This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
I did this blind, without sql around, so forgive me if it is a little off. It should be pretty close to what you are looking for if I understood what you were trying to do.
SELECT T1.SenderID, MIN(TimeStamp) AS MINStamp, Body AS Body
FROM @Messages T1
INNER JOIN (
SELECT
GROUP BY SenderID
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off.
This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
|
|
|
|
|
I have impersonated a user for my project (C# ASP.NET) lets call him Mr:X.
For security reasons I want to connect to a database with another user Mr:Y.
I still want to use windows authentication and Integrated Security=SSPI.
Is this possible?
|
|
|
|
|
hi folks,
Iam working sql server 2005
Iam unable to change the already existing identity value.
Pls think a while for this,,,,,,i already tried
alter table "ALTER TABLE <table_name> ALTER COLUMN <column_name> IDENTITY (x,y)"
its urgent
thanks in advance
Rupa
|
|
|
|
|
DBCC CHECKIDENT (jobs, RESEED, 30)
I hope this will help to reset the identity value
|
|
|
|
|
Hi genius,
Nice to meet you here!
When developing BI programs with SQL Server 2005, I got this problem:
Users want the report should be exported in office excel 2003 format, and stored in PIVOT TABLE.
We have decided to use OLAP tech in SSAS(SQL Server2005 Analysis Services) to generate cubes.
If we just use excel as a client tool, and create a pivot table which connects to the cube database, it's ok.
But, the users want to download the excel file from a web page, further more , they want to filter the data through the web page.
How can I do?
Wait inline.
Thank you!
|
|
|
|
|
Hi,
Myself imran, I am quite new to dotnet. i am developing web application using asp.net and C#(Visual Studio 2005). i have defined connection string in web-config like <connectionstrings>
<add name="connstr" providername="System.Data.SqlClient" connectionstring="server=local;database=HFSERVICES;uid=sa;pwd=;">
Can any one help me to know How to Access database using this connetion string.
thanks
regards
imran khan
|
|
|
|
|
Welcome to The Code Project.
Please don't post the same question all over the place. Pick the forum that best fits your question.
---
b { font-weight: normal; }
|
|
|
|
|
Thank you for first Lesson.
regards
imran khan
|
|
|
|
|
keyValue = System.Configuration.ConfigurationManager.AppSettings("key")
gets the connection string from web.config
Steve Jowett
|
|
|
|
|
Hello,
I'd appreciate if someone could kindly help me with the following database design issue. I've already solved it, but my solution is not much elegant. I'm working on this SQL database for one of my friends. It will make her work more efficient since she's currently dealing with a tangled up set of excel tables.
The database will hold various data for several thousand employees. This would be a piece of cake, but we also need to track the changes of those data values in time. For example, we need to know that John Smith was in Quality Department in October, but since November, he's been in Services Department, etc.
Having a separate "historical data states" table for each employee (or, alternatively, for each data type) is not a good idea, so I designed a simple table named "TimeTableItem" that stores:
- a link to the table that holds table.column names (for instance "19" for dbo.Department.DepartmentID table) to which the current row relates ("TimeTableDataTypeID" column below)
- and an ID of the related value itself (for example, "7" for Services Department within the Department table - "TimeTableDataValueID" column below)
The actual table design:
- TimeTableItemID (PK, int, not null)
- PersonID (FK, int, not null)
- TimeTableDataTypeID (int, not null)
- TimeTableDataValueID (int, not null)
- StartTime (dateteime, null) (null is treated as "from the beginning of time" 
- EndTime (datetime, null) (null is treated as "forever"
- CreatedOn (datetime, null)
- ModifiedOn (datetime, null)
Obviously, the PersonID is a link to the Person table. By the same token, StartTime and EndTime define in which time span the data for the particular person is in effect, CreatedOn and ModifiedOn denotes these self-explanatory timestamps.
The trouble with this kind of design is that we don't know beforehand to WHAT employee data (Department? Position? Salary band?) will the next row be related, so we can't set a relation between the tables here (we dont' know what will the other table be). This means that there's no way to set a foreign key constraint on a column that holds the VALUE of the related data element. This is not much consistent and might lead to a situation when my friend deletes a department from the departments table that is used somewhere else. This will result in orphaned rows.
Is there any way to solve this efficiently?
Thank you for any input,
Michal
-- modified at 21:14 Monday 4th December, 2006
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
