April 18, 2012 at 12:15 pm
I have a database and try to get duration to make percentage.
When i get records, some of them have same Start Time,
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT Location
,Starttime
,Endtime
(DATEDIFF (SECOND, Starttime,Endtime) as Duration
FROM Testing
WHERE Endtime BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY Location
and get this result
Location Starttime Endtime Duration
Dallas 2012-04-08 0:00:00.0002012-04-10 13:00:12.0001304357
San Jose2012-04-08 0:00:00.0002012-04-10 12:59:39.370418234
San Jose2012-04-08 0:00:00.0002012-04-11 09:51:17.613493332
San Jose2012-04-08 0:00:00.0002012-04-11 09:54:48.483493543
San Jose2012-04-08 0:00:00.0002012-04-11 09:55:12.120493567
Tampa2012-04-08 1:00:00.0002012-04-09 14:23:33.000336867
Tampa2012-04-08 1:00:00.0002012-04-09 14:24:39.000336933
Tampa2012-04-08 1:00:00.0002012-04-13 16:53:44.000691479
....
What will you write a query to get record if it has the same Location and same Starttime, then show record with the longest Duration?
Dallas 2012-04-08 0:00:00.0002012-04-10 13:00:12.0001304357
San Jose2012-04-08 00:00:00.0002012-04-11 09:55:12.120493567
Tampa2012-04-08 1:00:00.0002012-04-13 16:53:44.000691479
Thanks for teaching me.
April 18, 2012 at 12:18 pm
Can you post ddl and sample data for your table? Then a clear definition of what you want for results. Take a look at the first link in my signature about how to post questions to get you the best response.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 1:11 pm
Sorry, i am newbie and just learned sql from google then use SSMS Express to get data. Is this what i should do per your request?
--===== Create the test table with
CREATE TABLE Testing
(
Location varchar(255)
Starttime DATETIME,
Endtime DATETIME
PRIMARY KEY (Location),
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT yyyy-mm-dd h:mm:ss
--===== Insert the test data into the test table
INSERT INTO Testing
(Location, Starttime, Endtime)
SELECT 'Dallas','2012-04-08 0:00:00.000',2012-04-10 13:00:12.000' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000',2012-04-10 12:59:39.370 UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000',2012-04-11 09:51:17.613 UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000',2012-04-11 09:54:48.483 UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000',2012-04-11 09:55:12.120 UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000',2012-04-09 14:23:33.000 UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000',2012-04-09 14:24:39.000 UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000',2012-04-13 16:53:44.000
Thanks.
April 18, 2012 at 1:27 pm
Few syntax error but pretty close.
CREATE TABLE #Testing
(
Location varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'Dallas','2012-04-08 0:00:00.000','2012-04-10 13:00:12.000' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-10 12:59:39.370' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:51:17.613' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:54:48.483' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:55:12.120' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-09 14:23:33.000' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-09 14:24:39.000' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-13 16:53:44.000'
select * from #Testing
drop table #Testing
I changed it to a temp table instead of a persistent table but that is not a big deal at all.
OK so now we have a table with some data. What do you want out of it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 1:40 pm
Now i would like to have the data like this
Location Starttime Endtime Duration
Dallas 2012-04-08 0:00:00.0002012-04-10 13:00:12.0001304357
San Jose2012-04-08 00:00:00.0002012-04-11 09:55:12.120493567
Tampa2012-04-08 1:00:00.0002012-04-13 16:53:44.000691479
If it has same location, same starttime then keep record with duration longest in seconds (Endtime-Starttime)
Thanks
April 18, 2012 at 1:48 pm
Like this?
select Location, MIN(Starttime), MAX(Endtime)
from #Testing
group by Location
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 2:13 pm
What if i have San Jose SELECT 'San Jose','2012-04-10 0:00:00.000','2012-04-15 09:55:12.120'
then it will get only 'San Jose','2012-04-08 0:00:00.000 ??
CREATE TABLE #Testing
(
Location varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'Dallas','2012-04-08 0:00:00.000','2012-04-10 13:00:12.000' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-10 12:59:39.370' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:51:17.613' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:54:48.483' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:55:12.120' UNION ALL
SELECT 'San Jose','2012-04-10 0:00:00.000','2012-04-15 09:55:12.120' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-09 14:23:33.000' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-09 14:24:39.000' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-13 16:53:44.000'
select * from #Testing
drop table #Testing
April 18, 2012 at 2:30 pm
Well which San Jose row would you want in that case?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 2:31 pm
Or are you saying you want to get two rows when there are different starttimes??
select Location, Starttime, MAX(Endtime)
from #Testing
group by Location, starttime
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 2:40 pm
Then i can get something like this
Location Starttime Endtime Duration
Dallas 2012-04-08 0:00:00.0002012-04-10 13:00:12.0001304357
San Jose2012-04-08 00:00:00.0002012-04-11 09:55:12.120493567
San Jose 2012-04-10 0:00:00.000 2012-04-15 09:55:12.120 232323
Tampa2012-04-08 1:00:00.0002012-04-13 16:53:44.000691479
You can have a lot of San Jose list or Tampa ... as long not the same start time, if it has same start time then keep the record which has the longest duration time.
Sorry for the way i explain not perfectly
Thanks,
April 18, 2012 at 2:44 pm
sabercats (4/18/2012)
Then i can get something like this
Location Starttime Endtime Duration
Dallas 2012-04-08 0:00:00.0002012-04-10 13:00:12.0001304357
San Jose2012-04-08 00:00:00.0002012-04-11 09:55:12.120493567
San Jose 2012-04-10 0:00:00.000 2012-04-15 09:55:12.120 232323
Tampa2012-04-08 1:00:00.0002012-04-13 16:53:44.000691479
You can have a lot of San Jose list or Tampa ... as long not the same start time, if it has same start time then keep the record which has the longest duration time.
Sorry for the way i explain not perfectly
Thanks,
Does my post just above yours work?
Don't worry about the explanation. It seems that English is probably not your first language? You are doing just fine on your explanations. I am just trying to make sure I am answering your question correctly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 2:50 pm
Perhaps something like this?
CREATE TABLE #Testing
(
Location varchar(255),
Starttime DATETIME,
Endtime DATETIME
);
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'Dallas','2012-04-08 0:00:00.000','2012-04-10 13:00:12.000' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-10 12:59:39.370' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:51:17.613' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:54:48.483' UNION ALL
SELECT 'San Jose','2012-04-08 0:00:00.000','2012-04-11 09:55:12.120' UNION ALL
SELECT 'San Jose','2012-04-10 0:00:00.000','2012-04-15 09:55:12.120' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-09 14:23:33.000' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-09 14:24:39.000' UNION ALL
SELECT 'Tampa','2012-04-08 1:00:00.000','2012-04-13 16:53:44.000';
select * from #Testing;
WITH BaseData AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY Location, Starttime ORDER BY DATEDIFF(ms, Starttime, Endtime) DESC) AS rownum,
Location,
Starttime,
Endtime
FROM
#Testing
)
SELECT
Location,
Starttime,
Endtime
FROM
BaseData
WHERE
rownum = 1;
drop table #Testing
April 18, 2012 at 4:56 pm
Sean, this one works
select Location, Starttime, MAX(Endtime)
from #Testing
group by Location, starttime
Lynn, thanks i did not check with yours yet because Sean's
select Location, Starttime, MAX(Endtime)
from #Testing
group by Location, starttime
Worked great.
Thank you all.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply