May 10, 2012 at 12:51 pm
Example, I need to get all the records weekly start from last 2 Sunday 00:00:00 AM to last Saturday 23:59:59 include any records start before last 2 Sundays but end after last Sunday. Then i sum them and get percentage.
CREATE TABLE #Testing
(
Location varchar(255),
OS varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL
SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL
SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL
SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL
SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL
SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL
SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213 UNION ALL
SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL
SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL
SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL
SELECT 'carolina','win7','2012-04-28 00:00:00.000','2012-05-06 03:59:59.000'
select * from #Testing
drop table #Testing
Here are my code
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)))
DECLARE @t TABLE([Location] CHAR(20), OS CHAR(10),ID int, [Active User Start] DATETIME, [End Time] DATETIME, [Total Using Time] DECIMAL)
INSERT INTO @t
SELECT [Table_Control].location, [Table_Control_History].os,
CASE
WHEN [Table_Control_History].start_time < @2SundaysAgo THEN (@2SundaysAgo)
ELSE ([Table_Control_History].start_time)
END AS '[Table_Control_History].start_time'
, CASE
WHEN [Table_Control_History].change_datetime > @lastSaturday
then @lastSaturday
ELSE [Table_Control_History].change_datetime
END AS '[Table_Control_History].change_datetime'
, CASE
WHEN [Table_Control_History].start_time < @2SundaysAgo
THEN DATEDIFF (SECOND, @2SundaysAgo,MAX([Table_Control_History].change_datetime))
WHEN ([Table_Control_History].start_time > @2SundaysAgo
AND
[Table_Control_History].change_datetime > @lastSaturday )
THEN DATEDIFF(SECOND, [Table_Control_History].start_time, @lastSaturday)
ELSE DATEDIFF (SECOND,[Table_Control_History].start_time,([Table_Control_History].change_datetime))
END AS 'Total Using Time'
FROM [Table_Control] join [Table_Control_History] on [Table_Control].id = [Table_Control_History].parent_id
WHERE [Table_Control_History].change_datetime BETWEEN @2SundaysAgo AND @lastSaturday
OR [Table_Control_History].start_time between @2SundaysAgo and @lastSaturday
OR ([Table_Control_History].start_time < @2SundaysAgo AND [Table_Control_History].change_datetime > @lastSaturday)
GROUP BY [Table_Control].location,[Table_Control_History].os,[Table_Control_History].parent_id,[Table_Control_History].start_time,[Table_Control_History].change_datetime
ORDER BY [Table_Control].place
SELECT [Location],OS
,DATEDIFF (SECOND,[Active User Start],MAX([End Time])) As 'Total Time'
FROM @t
WHERE OS IS NOT NULL
AND DATEDIFF(ss,[Active User Start],([End Time])) >= 300
GROUP BY [Location],OS,[Active User Start]
ORDER BY [Location]
I got these
Location OS Total Time
tampawin72575
tampawin7157619
tampawin73352
dallasvista604799
greenbaywinxp14400
greenbaywin7 518400
san josewinxp228121
san josewinxp70853
san josewinxp193514
san josewinxp176290
san josewinxp110999
san josewinxp110940
new yorkwin7 136290
carolinawin7 604799
How do update the code to get the result like this?
which i want to get percentage , how do i get
Place OS Total Percent
tampa win7 (2575+157619+3352) (2575+157619+3352)/ (7 *24 * 3600) * 100
tampa unknown ((7 *24 * 3600) - (2575+157619+3352)) ((7 *24 * 3600) - (2575+157619+3352)) / (7 *24 * 3600) * 100
dallas vista 604799 (604799)/(7 *24 * 3600) * 100
dallas unknown ((7 *24 * 3600) - 604799) ((7 *24 * 3600)- 604799)) / (7 *24 * 3600) * 100
greenbay win7 518400 (518400)/(7 *24 * 3600) * 100
greenbay XP 14400 (14400)/(7 *24 * 3600) * 100
greebbay unknown ((7 *24 * 3600) - (518400+14400)) ((7 *24 * 3600) - (518400+14400)) / (7 *24 * 3600) * 100
....
Thanks for teaching me.
May 10, 2012 at 1:22 pm
Do you see anything wrong with the following? Have you tried running this in a sandbox database?
CREATE TABLE #Testing
(
Location varchar(255),
OS varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL
SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL
SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL
SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL
SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL
SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL
SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213 UNION ALL
SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL
SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL
SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL
May 10, 2012 at 1:26 pm
Actually Lynn that part is fairly easy to correct. The real problem is the OP wants help with the original query and did not provide any details about the original tables. Then the desired output is also a bit oddball.
In your example output did you want the formula or did you want the result and you used the formula to help clarify how it is to be calculated. If it is the latter I applaud you because most people would just put the total and expect us to figure out how to get there.
_______________________________________________________________
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/
May 10, 2012 at 1:28 pm
Here is the sample ddl all cleaned up and ready to roll.
CREATE TABLE #Testing
(
Location varchar(255),
OS varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing (Location, OS, Starttime, Endtime)
SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL
SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL
SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL
SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL
SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL
SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL
SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213' UNION ALL
SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL
SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL
SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL
SELECT 'carolina','win7','2012-04-28 00:00:00.000','2012-05-06 03:59:59.000'
select * from #Testing
drop table #Testing
_______________________________________________________________
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/
May 10, 2012 at 1:29 pm
Sean Lange (5/10/2012)
Actually Lynn that part is fairly easy to correct. The real problem is the OP wants help with the original query and did not provide any details about the original tables. Then the desired output is also a bit oddball.In your example output did you want the formula or did you want the result and you used the formula to help clarify how it is to be calculated. If it is the latter I applaud you because most people would just put the total and expect us to figure out how to get there.
I know, I just think the OP should be sure any code he posts will actually work when someone does a cut and paste. Nothing more irritating than having to fix code that is fairly simple to write and test.
May 10, 2012 at 1:31 pm
sabercats (5/10/2012)
Example, I need to get all the records weekly start from last 2 Sunday 00:00:00 AM to last Saturday 23:59:59 include any records start before last 2 Sundays but end after last Sunday. Then i sum them and get percentage.
CREATE TABLE #Testing
(
Location varchar(255),
OS varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL
SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL
SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL
SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL
SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL
SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL
SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213 UNION ALL
SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL
SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL
SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL
SELECT 'carolina','win7','2012-04-28 00:00:00.000','2012-05-06 03:59:59.000'
select * from #Testing
drop table #Testing
Here are my code
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)))
DECLARE @t TABLE([Location] CHAR(20), OS CHAR(10),ID int, [Active User Start] DATETIME, [End Time] DATETIME, [Total Using Time] DECIMAL)
INSERT INTO @t
SELECT [Table_Control].location, [Table_Control_History].os,
CASE
WHEN [Table_Control_History].start_time < @2SundaysAgo THEN (@2SundaysAgo)
ELSE ([Table_Control_History].start_time)
END AS '[Table_Control_History].start_time'
, CASE
WHEN [Table_Control_History].change_datetime > @lastSaturday
then @lastSaturday
ELSE [Table_Control_History].change_datetime
END AS '[Table_Control_History].change_datetime'
, CASE
WHEN [Table_Control_History].start_time < @2SundaysAgo
THEN DATEDIFF (SECOND, @2SundaysAgo,MAX([Table_Control_History].change_datetime))
WHEN ([Table_Control_History].start_time > @2SundaysAgo
AND
[Table_Control_History].change_datetime > @lastSaturday )
THEN DATEDIFF(SECOND, [Table_Control_History].start_time, @lastSaturday)
ELSE DATEDIFF (SECOND,[Table_Control_History].start_time,([Table_Control_History].change_datetime))
END AS 'Total Using Time'
FROM [Table_Control] join [Table_Control_History] on [Table_Control].id = [Table_Control_History].parent_id
WHERE [Table_Control_History].change_datetime BETWEEN @2SundaysAgo AND @lastSaturday
OR [Table_Control_History].start_time between @2SundaysAgo and @lastSaturday
OR ([Table_Control_History].start_time < @2SundaysAgo AND [Table_Control_History].change_datetime > @lastSaturday)
GROUP BY [Table_Control].location,[Table_Control_History].os,[Table_Control_History].parent_id,[Table_Control_History].start_time,[Table_Control_History].change_datetime
ORDER BY [Table_Control].place
SELECT [Location],OS
,DATEDIFF (SECOND,[Active User Start],MAX([End Time])) As 'Total Time'
FROM @t
WHERE OS IS NOT NULL
AND DATEDIFF(ss,[Active User Start],([End Time])) >= 300
GROUP BY [Location],OS,[Active User Start]
ORDER BY [Location]
I got these
Location OS Total Time
tampawin72575
tampawin7157619
tampawin73352
dallasvista604799
greenbaywinxp14400
greenbaywin7 518400
san josewinxp228121
san josewinxp70853
san josewinxp193514
san josewinxp176290
san josewinxp110999
san josewinxp110940
new yorkwin7 136290
carolinawin7 604799
How do update the code to get the result like this?
which i want to get percentage , how do i get
Place OS Total Percent
tampa win7 (2575+157619+3352) (2575+157619+3352)/ (7 *24 * 3600) * 100
tampa unknown ((7 *24 * 3600) - (2575+157619+3352)) ((7 *24 * 3600) - (2575+157619+3352)) / (7 *24 * 3600) * 100
dallas vista 604799 (604799)/(7 *24 * 3600) * 100
dallas unknown ((7 *24 * 3600) - 604799) ((7 *24 * 3600)- 604799)) / (7 *24 * 3600) * 100
greenbay win7 518400 (518400)/(7 *24 * 3600) * 100
greenbay XP 14400 (14400)/(7 *24 * 3600) * 100
greebbay unknown ((7 *24 * 3600) - (518400+14400)) ((7 *24 * 3600) - (518400+14400)) / (7 *24 * 3600) * 100
....
Thanks for teaching me.
By the way, you forgot to put double quotes (") around the word sql in the IFCode tags.
May 10, 2012 at 1:32 pm
This one i made up per your team request. I did not test with sandbox ?
CREATE TABLE #Testing
(
Location varchar(255),
OS varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL
SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL
SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL
SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL
SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL
SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL
SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213 UNION ALL
SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL
SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL
SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL
I only write the code to get from SSMS 2008 and pull data, from the code i can get Location, OS, Duration but stuck with SUM, percent and add Unknown in there 🙁
I hope someone can write code and i will learn it from there. I cannot believe sql query so powerful. I only did this in perl but not live update in SharePoint. Thanks
May 10, 2012 at 1:42 pm
sabercats (5/10/2012)
This one i made up per your team request. I did not test with sandbox ?
CREATE TABLE #Testing
(
Location varchar(255),
OS varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL
SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL
SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL
SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL
SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL
SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL
SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213 UNION ALL
SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL
SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL
SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL
I only write the code to get from SSMS 2008 and pull data, from the code i can get Location, OS, Duration but stuck with SUM, percent and add Unknown in there 🙁
I hope someone can write code and i will learn it from there. I cannot believe sql query so powerful. I only did this in perl but not live update in SharePoint. Thanks
Well there seems to be a bit of a language barrier here. No problem, we can work with that. What Lynn was saying is this code will not run as you posted it.
Back to the topic at hand. I don't get this "unknown" thing at all. In all of your sample data the location is known, there are no rows with no location. My guess is this in the original data but we would have no way of knowing that.
Getting the sum is pretty simple.
select Location, OS, SUM(datediff(n, starttime, endtime)) as MySum,
(SUM(datediff(n, starttime, endtime)) / 100.0) / 7 *24 * 3600 as MyPercentage
from #Testing
group by Location, OS
Not totally sure about the percentage column because I am not 100% sure what you want here. But this example should show you how it works.
_______________________________________________________________
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/
May 10, 2012 at 1:42 pm
sabercats (5/10/2012)
This one i made up per your team request. I did not test with sandbox ?
CREATE TABLE #Testing
(
Location varchar(255),
OS varchar(255),
Starttime DATETIME,
Endtime DATETIME
)
INSERT INTO #Testing
(Location, Starttime, Endtime)
SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL
SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL
SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL
SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL
SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL
SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL
SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213 UNION ALL
SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL
SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL
SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL
SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL
I only write the code to get from SSMS 2008 and pull data, from the code i can get Location, OS, Duration but stuck with SUM, percent and add Unknown in there 🙁
I hope someone can write code and i will learn it from there. I cannot believe sql query so powerful. I only did this in perl but not live update in SharePoint. Thanks
Look at the table then look at the insert statement. You should be able to see what is wrong without even running the code.
May 10, 2012 at 1:50 pm
Oh I missed OS . Sorry . Do you have a clue how do i get sum and percent?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply