query for sorting startdatetime and enddatetime
-
How to create a query for my problem?
example :
ID stardatetime enddatetime
135 10:40:25 10:40:25
135 10:40:27 10:40:27
135 10:40:27 10:41:05
I want this result:
ID stardatetime enddatetime
135 10:40:25 10:41:05
-
This works for your listed set of data, but I'm not sure it's what you really want...
SELECT ID, MIN(startdatetime) as MinStartDateTime, MAX(enddatetime) as EndDateTimeFROM SomeTable
Group By ID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
well this is just 3 column of my table... and what you post is correct...but i want all the other column to be shown with the adequate query..
can you help me?
-
If I have to guess at what you want and what your table really looks like, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ok sorry i'll post a entire prob...
I have this select which sort missed calls from table InteractionSegment...
SELECT
iseg.*
FROM
tblInteractionSegment iseg
WHERE iseg.InteractionSummaryId NOT in (SELECT InteractionSummaryId FROM tblInteractionSegment WHERE SegmentType in (2))
and iseg.SegmentType = 1
and LEN(callingdevice)>5
and SUBSTRING(ISNULL(AffectedDevice,''), 0, 1)<> 'T'
the result is a table with ID's,startdatetime,enddatetime and other data column...
so i want another condition inserted into this query that would give me for a single ID a min stardatetime and a max enddatetime..
-
Are you going to tell me what columns are in the table, or should I guess?
Read this to see the best way to post this to get quick responses. http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
SELECT TOP 1000 [InteractionSummaryId]
,[InteractionSegmentId]
,[SegmentType]
,[DeviceId]
,[StartDateTime]
,[EndDateTime]
,[AgentDeviceId]
,[SplitSkillDeviceId]
,[EnteredCodeData]
,[InfoLength]
,[InfoValue]
,[AffectedDevice]
,[CallingDevice]
,[CalledDevice]
,[LastRedirectionDevice]
,[UCID]
,[OriginalCallInfoUCID]
FROM [tblInteractionSegment]
-
You didn't read the article...
One more thing, taking that earlier set of data you posted
ID stardatetime enddatetime
135 10:40:25 10:40:25
135 10:40:27 10:40:27
135 10:40:27 10:41:05
Now, what do you want to see for the rest of the columns? Assuming the rest of the columns have different values for those three rows, do you want to see the values from the row with the lowest start date, the values from the row with the largest end date or something else
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
hm...that would be a tricky part....
i want in some way if could be possible.. to show in single row e.g. for ID 135 the lowest start date and largest end date..
-
The query I gave you earlier does that.
What about the rest of the columns? Which values do you want to show with that row?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
all the rest of columns..
-
I'm not really in the mood for playing 20 questions here.
Again I will ask:
What values do you want for the other columns? If you want in one row the min of the start date and the max of the end date, where do I get the rest of the column values from? Do they come from the row that had the minimum start date in? Do they come from the row that had the maximum end date in? Do they come from another row (and if so how do I identify that row)
If you cannot tell me that I cannot write you a query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
it's difficult to explain for me this problem because I'm not expert in this field..but I'll try.
we have one table with all values mentioned on the previous page..this table contain the ID's of every interaction which has been taken. So we have e.g. InteractionSummaryId 65,66,68,100...etc. but this ID's has multiple rows with interaction times (Starttime and Endtime )..so.. i would like to generate a query who will give to me a view of every single interaction with start and end time with the others data...
maybe the solution of that is to create a trigger who will fire on the main table after the data is inserted and update a new table which i will create, with values that i want...
this is my best to explain the prob...
-
californication2704 (8/21/2011)
so.. i would like to generate a query who will give to me a view of every single interaction with start and end time with the others data...Yes, I have got that much. What you haven't explained enough is the 'others data'
Ok, let's say we have this (example data that I'm guessing may exist)
InteractionSummaryId InteractionSegmentId SegmentType DeviceId StartDateTime EndDateTime AgentDeviceId SplitSkillDeviceId135 21 A 42 2011/07/21 8:25:00 2011/07/21 8:27:00 5 4
135 22 S 42 2011/07/21 8:29:05 2011/07/21 8:32:21 5 6
135 23 K 42 2011/07/21 8:33:00 2011/07/21 8:47:00 5 9
So you want, as a final resultset
InteractionSummaryId StartDateTime EndDateTime135 2011/07/21 8:25:00 2011/07/21 8:47:00
Now, looking at the example set of data, what values do you want the rest of the columns to show? They're different for all three rows, so I get the min of the startdate, the max of the end and then do what to get the rest of the column values (first row, last row, something else?)?
p.s. Please, please go and read that article I referenced. If you'd given me what I needed I could have got you a query yesterday already
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ok i have this table...
create table InteractionSegment (
InteractionSegmentId PRIMARY KEY int NOT NULL,
SegmentType smallint NOT NULL,
DeviceId varchar(64) NOT NULL,
StartDateTime datetime NOT NULL,
EndDateTime datetime NULL,
AgentDeviceId varchar(64) NULL,
SplitSkillDeviceId varchar(64) NULL,
EnteredCodeData varchar(64) NULL,
InfoValue nvarchar(256) NULL,
FOREIGN KEY (InteractionSummaryId) REFERENCES InteractionSummary (InteractionSummaryId)
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
--- ===the data inserted looks like this
---===select which pull out data for missed calls
SELECT iseg.*
FROM tblInteractionSegment iseg
WHERE InteractionSummaryId NOT in (SELECT InteractionSummaryId FROM tblInteractionSegment WHERE SegmentType in (2))
and SegmentType = 1
and LEN(callingdevice)>5
and SUBSTRING(ISNULL(AffectedDevice,''), 0, 1)<> 'T'
so for every interaction i would have a single row just like you mentioned in the post before..and other columns will have a value of that last ID and his max EndDateTime
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply