Write A Query To Group Records Based On Speed (specific Value Of Zero)?
Jun 28, 2012
I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:
Table - Vehicle_Event
Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3
[code].....
OUTPUT_Required:
Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0
[Code] .....
I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.
View 6 Replies
ADVERTISEMENT
Mar 12, 2008
I am fairly new to transact SQL and I am having difficulty retrieving the set of records I require given the data shown below. I want to be able to filter the records just to return the records that have the minimum securityorder for each unique secsyscode. I suspect I need to use min or group by to achieve the desired affect but cannot seem to get it right
any help would be appreciated
eg in the following
secsyscode, securitytypecode and securityorder are integers and securityCode is a char(16).
secsyscode
securityCode
securitytypecode
securityorder
1
Special
1
2
2
Total Fund
999
17
3
PerfInd
995
14
3
PerformanceIndex
999
17
4556
93152
1
2
4556
10815-0
4
1
4557
558372
1
2
4557
12137-0
4
1
4558
656113
1
2
4558
13154-0
4
1
4559
53673
1
2
4559
13672-0
4
1
I only want the following records to be returned.
secsyscode
securityCode
securitytypecode
securityorder
1
Special
1
2
2
Total Fund
999
17
3
PerfInd
995
14
4556
10815-0
4
1
4557
12137-0
4
1
4558
13154-0
4
1
4559
13672-0
4
1
View 5 Replies
View Related
Nov 16, 2006
i have a table such sa below:
Name1, Name2, Name3, Nam4, C1, C2,.., C100
and in this table, i have found index for Name1-Nam4,
i don't why sql below is very slow?
select
Name1, sum(C1), ...., Sum(C100)
from
(
select
Name1, Name2, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, Name4, C1, ...., C100
from
My_Table
group by Name1, Name2, Name3, Name4
) as T
group by Name1, Name2, Nam3
) as T
group by Name1, Name2
) as T
group by Name1
Does 'Group By' affect the speed of query?
View 1 Replies
View Related
Jul 9, 2015
I have just been running a query which I was planning on improving by removing a redundant GROUP BY (there are about 20 columns, and one of the columns returned is atomic, so will mean that the "group by" will never manage to group any of the data) but when I modified the query to remove the grouping, this actually seems to slow the query, and I can't see why this would be the case.
Both queries return the same number of rows (69000), as I expected, and looking at the query plan, then they look nearly identical, other than at the start, there is a "stream aggregate" and "sort" being performed. The estimated data size is 64MB for the non-grouped query (runs in 6 min 41 secs), vs 53MB for the aggregated query (runs in 5 min 31 secs), and the estimated row size is smaller when aggregated.
Can rationalise this? In my mind, the data that is being pulled is identical, plus there is extra computation for doing an unnecessary aggregation, so the aggregated query should be unquestionably slower, but the database engine has other ideas; it seems to be able to work more quickly when it needs to do unnecessary work :) Perhaps something to do with an inefficient query plan for the non-aggregated query? I would have thought looking at the actual execution plan might have made this apparent, but both plans look very similar.
Edit: More information, the "group by" query had two aggregations on it, a count of one of the columns, and an average of another one. I changed this so that it was just "1" instead of the count, and for the average, I changed it to be the expression within the average aggregate, since the aggregation effectively does not do anything.
View 2 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Oct 27, 2000
The simple 'select' from ISQL/W is taking more than 2 mins. for getting 20000 records. Is it normal?
The time that is shown at bottom of window is the correct measure of response time or is there any other way?
Thanks
View 1 Replies
View Related
Jun 6, 2004
Use Pubs
SELECT pub_id, type, SUM(price) as Total_price
FROM titles
GROUP BY pub_id, type
The above query returns the following resultset:
0736business 2.9900
1389business 51.9300
0877mod_cook 22.9800
1389popular_comp42.9500
0736psychology 45.9300
0877psychology 21.5900
0877trad_cook 47.8900
0877UNDECIDED NULL
Now I want to add another "Group By" on type, so I tried:
Select type, sum(Total_Price) from
(SELECT pub_id, type, SUM(price) as Total_Price
FROM titles
GROUP BY pub_id, type)
Group By type
But I got error: Incorrect syntax near the keyword 'Group'. How can I write such a nested group by query.
Thanks in advance for any help.
View 5 Replies
View Related
Jun 21, 2008
Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**
if no employee is absent in the department it has to display Zero
View 2 Replies
View Related
Oct 26, 2006
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies
View Related
Aug 21, 2014
select
DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC),
a.order,a.line,a.datedel,a.recpt,b.status,
b.item,b.t_sup
from historytbl a
inner join order b
on a.order = b.order
and a.line = b.line
and a.status =4
group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup
The query is returned the results below.
Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
3ccc302014-04-08rc3zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
5fff90 2014-02-11rc8yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
8iii102014-04-10rc5rrr123555
However, I want to have the query only show most recent two records for each group of item and sup, please see the results I want below.
Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
View 4 Replies
View Related
Mar 14, 2008
My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.
View 3 Replies
View Related
Feb 26, 2014
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
View 3 Replies
View Related
Jul 31, 2014
I have a query to insert records into a table based on a request but the query can only read one record at a time. How do i change the query such that it is able to read multiple records. In the below query i was able to input only 1 request which is 149906.
Query
declare @num_of_times int
declare @Count INT
DECLARE @newrequestid varchar(50)
DECLARE @Frequency VARCHAR(25), @RequestId INT, @x INT, @Max INT, @RptDesc INT
SET @RequestId = 149906
SET @x = 1
[Code] ....
View 1 Replies
View Related
Oct 12, 2015
I've been able to get this select query to work, but I'm not sure how to modify it to turn it into a DELETE query:
USE QSCTestENG
select p.[testid], COUNT(c.[testid])
FROM [dbo].[tblTestHeader] p
left outer join [dbo].[tblTestMeasurements] c ON p.[testid]=c.[testid]
where p.[model] = 'XPPowerCLC125US12'
group by p.[testid]
having COUNT(c.[testid]) <>48;
View 2 Replies
View Related
Jun 7, 2014
I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match)
Last name (exact match)
Address line 1 (exact match)
Postal code/zip (exact match)
First Initial (exact match)
Last name (exact match)
DOB exact match
Postal code/zip (exact match)
View 1 Replies
View Related
Aug 27, 2014
I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.
View 1 Replies
View Related
Aug 28, 2015
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300
What I would like to see is a result like:
St. Louis 1000
Other Missouri City 2450
View 5 Replies
View Related
May 28, 2007
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
View 4 Replies
View Related
Feb 5, 2008
Hi everyone, I'm working with SSRS 2000 and I have my report grouped by =Fields!ID.Value , but each ID has just the ID#, or an A or OT at the end. EX. ID# 12345, is divided and shows me like if there are 3 different Employees: 12345, 12345-A, and 12345-OT. I want 12345 and 12345-A, to be grouped in one, and 12345-OT, be displayed seperately. Is this possible? Do I have to write a special command??? all help is useful so please help.
Thanks,
Abner
View 6 Replies
View Related
Jan 2, 2007
I am trying to accomplish the following.
There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.
The first and second table have one field in common - the Account number.(ACCT_NUM)
the exclusion is based in steps..
1) if it has all the fields in the second table for that account number, then compare all of them..
2) if only 4 of them are present,compare and check for null for the rest
3) if only 3 of them are present,compare and check for null for the rest
4) if only 2 of them are present,compare and check for null for the rest.
how do i do this... pls suggest
Thanks
View 1 Replies
View Related
Jan 11, 2008
Do triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
View 3 Replies
View Related
Nov 9, 2005
Guys
Here's the scenario
create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-12-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '30-11-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-10-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('98765' , '31-12-2005' , 2)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 1)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 0)
select * from data1
I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way
Any ideas
View 3 Replies
View Related
Apr 8, 2008
Hi
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId
Returns
TeamMemberId MonthId
1 14
6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId, PositionName
Returns
TeamMemberId MonthId PositionName
1 2 ***. d. Geschäftsführung
6 20 ***. d. Geschäftsführung
1 14 CEO
6 16 CEO
The red rows are the ones I wanna get.
How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns?
Any help is much appreciated.
Regards,
Stefan
View 6 Replies
View Related
Jul 20, 2007
Hi,
DONT KNOW IF IT IS POSSIBLE.. PLEASE SUGGEST.
Currently working on a upload module where in the data from excel file is imported to the destination tables. Data in the excel sheet comes in phases. All excel sheet columns data don't come at first shot. The excel sheet's data is dumped into temporary tables which inturn is looped using cursor's and gets finally updated to the actual tables.
Now, the problem I am facing is how do I update columns of the actual table with the data (i.e NON NULL values) available in the temporary table without tampering the data allready present in actual table.
Ideally what required is, update the actual table column values with the corresponding columns of temporary table ONLY for Non NUll column values of temporary table.
Temporary and Destination tables have 85 columns each. I don't want to write 85 update queries.
The scenario which I am facing is given below with 2 columns as an example.
1. Table 1 :- tbl_source (Temporary Table) has two columns src_Col1 & src_Col2
2. Table 2 :- tbl_destination (Actual Table) has two columns dest_Col1 & des_Col2
Scenario -1
---------------
tbl_Source Sample Data (after excel import to the temporary table)
------------------------
src_Col1 src_Col2
------------------
50 NULL
tbl_Destination Sample Data
------------------------
dest_Col1 dest_Col2
------------------
50 NULL
Scenario -2
---------------
tbl_Source Sample Data
------------------------
src_Col1 src_Col2
------------------
NULL 100
tbl_Destination Sample Data
------------------------
dest_Col1 dest_Col2
------------------
50 100
One update query which handles both scenarios.
Thanking you in anticipation.
Regards
View 4 Replies
View Related
Nov 8, 2005
One of my table called as 'customertable' contains following fields
customername, emailid, subscriptionendperiod
Example records are:
david, david@john.com, 12/20/2005(mm/dd/yyyy format).
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.
Regards
View 3 Replies
View Related
Aug 22, 2002
How can we use the SQL INSERT statement in the stored procedure to create a table in a user specified filegroup? Table1 will be created by using the following SQL script and will be placed in a default filegroup. The question is can this table been created in a specified filegroup other than default filegroup.
INSERT INTO Table1(col1,col2)
SELECT col1,col2
FROM Table2
Example, userA table assign to userA filegroup. userB table assign to userB filegroup. Usually by using CREATE statement you can put the table on a specific filegroup as long as a predefined filegroup has been hard coded in the script. I appreciate any inputs.
View 1 Replies
View Related
Jan 19, 2006
Hi,
I am trying to add "Remote Desktop Users" as a login group to SQL Server 2005 (i.e. so they can log into management studio). I get a "Not Found Error" (see below). But it is of course found, because I selected it :-) What is going on here? I see the "Administrators" group and various SQL Server groupls added automatically there, so there must be a way, right?
I know I can add each user in the group individually, but that seems overkill when I can just use the group in which they are contained.
Here is the error I get:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Login 'TESTSERVERRemote Desktop Users'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Windows NT user or group 'TESTSERVERRemote Desktop Users' not found. Check the name again. (Microsoft SQL Server, Error: 15401)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
View 6 Replies
View Related
May 26, 2007
I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in.
Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]).
Any suggestions/comments would be appreciated. Thank you in advance.
View 1 Replies
View Related
Jul 3, 2007
Hi. I have a table called "Maxes" with three fields: Exercise_ID, weight, and date. This is for journaling my weightlifting progress. What I want my query to do is this:
Return just one record for each Exercise_ID, and only the one with the most recent date.
I tried this:
Code:
SELECT DISTINCT Maxes.Exercise_ID, Maxes.Date_Maxed, Maxes.[Max Weight]
FROM Maxes ORDER BY Maxes.Date_Maxed DESC;
but it doesn't quite work. Can someone show me how to do this?
View 4 Replies
View Related
Mar 11, 2002
I am kinda new with SQL and am trying to get a count of on the number of updates and or inserts to any given or group of tables and cannot get the syntax correct...can anyone help with this?
Thank you in advance.
Colin P.
View 1 Replies
View Related
Jun 15, 2015
Here's what I need to do..
1. I have a dataset with userID's
2. I want to only show the records where a userID is a member of a specific AD group,
I'm not allowed to use a linked server. I was able to make a datasource in SSDT that connects to AD, but I don't know where to begin with the dataset expression in the SSRS report.
View 7 Replies
View Related
Jun 22, 2015
I have some duplicate values for my query results, about 200 duplicates out of 30000 rows. Of these 200 duplicates I want to keep the ones that have a higher value for... 'UpdatedBatchID'.
SELECT
IR.Id as 'ID'
, CAST(IR.Priority as varchar) as 'Priority'
, IRSupportGroupDN.DisplayName as 'Support Group'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) as 'Created Date'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.ResolvedDate) as 'Resolved Date'
, SLOConfig.DisplayName as 'SLO'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),SLOFact.TargetEndDate) as 'SLO Target'
, SLOStatusDN.DisplayName as 'SLO Status'
, SLOMetric.DisplayName as 'SLO Metric'
, SLOFact.UpdatedBatchId as 'UpdatedBatchID'
View 11 Replies
View Related
Jun 8, 2015
I have got question because time is running but I still don't know how to do it.
So I have 1 group with 3 rows and I would like to put for each row not only group individual number like 1,2,3,4,5 ... etc until end of report. Generally using fuction RowNumber I got 1,2,3 and then 1,2,3 again.
View 9 Replies
View Related
