Using Min Or Group By To Return Specific Records
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
ADVERTISEMENT
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
View Related
Mar 25, 2008
Hi!
I'am new to this forum and would apreciate any feedback on my problem.
I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299.
The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.
The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".
CODE:
SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall
FROM
(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week,
ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average
FROM SERGEL_PREPAID INNER JOIN
TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE
WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD')
GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP)
HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1
GROUP BY uke, all aar
NB: Using SQL Server 2005. Any tip or solution will be a big help
Best regards Gard S
View 2 Replies
View Related
Apr 17, 2008
Hi Newbie here,
i am trying to make a highscore table and i would like when the user enters a score for them to be able to see what position they are in the database without having to take out all the data. i have looked at a few methods and was wondering which one is the best solution and how i would go about implementing it
count()
rank()
Indexes
Any response would be greatly appreciated
Puzzled
View 3 Replies
View Related
Aug 9, 2007
Hello Reporting specialists ... (GregSQL, Jarret, Lisa .....)
How can i get the max value of a single specific table column ??
Is this possible ? Or am i asking the impossible ??
Hopelessly, desperatelly remaining ...
Vinnie
see also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1
View 1 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
Oct 31, 2006
Hello, I was wondering if someone could point me in the right direction on how to do this....I have a table that is a many to many relationship between userID's and courseID's. for instance (example data for each row - dont let the spaces between rows throw you off I am just adding them to seperate the different users)userID = 1, courseID = 1userID = 1, courseID = 2userID = 1, courseID = 3userID = 2, courseID = 1userID = 2, courseID = 3userID = 3, courseID = 1userID = 3, courseID = 2userID = 3, courseID = 3I want to be able to check for a number so in this case "2" which I would like to return all users who do not have a courseID = 2. In the example above the query would return the user with the userID of 2.Any help in how I should go about doing this would be greatly appreciated. Is there a function I can use? I apologize I am extremely new to SQL
View 2 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
Oct 5, 2007
I have a large table with a datetime column and rows in 30 second intervals
everyday for about 3 months. What I want to do is
create a select statement that will return only the
datetime between 07:00:00.000 and 15:00:00.000 for
each day. I imagine this is a common issue. How is this done with tSQL?
(I cannot find any similiar posts.)
View 1 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
Jul 20, 2005
Hello,Our QA team have running a lot of test scripts (for automated regressiontesting), they run them on the different databases (Oracle/MS SQL).Several of those tests are dependent on the current date/time. In order tobeable to use them efficiently, we changed the current date/time on the QAdatabase server to a specific date/time before starting the scripts, so weare sure the test scripts always run in the same environment.Resetting the date/time of the database server gives us more and moreproblems (OS problems, backup/ virusscan, ...).It is possible to fix the problem with SYSDATE function on Oracle by settingFIXED_DATE init parameter.Is it possible to 'change' the current date/time on 'database' level,instead of on OS level for MSSQL2000?Do you know other means to do such things?Thanks in advance,Konstantin
View 2 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
Jan 30, 2004
I'm having problems constructing a query. I need to get a count of emails in my database, but only the emails that appear 2 or more times.
Can anyone help?
Thanks!
Justine
View 4 Replies
View Related
Jul 23, 2004
Scenerio:
Its 3pm and a user comes to me and says, she's deleted an invoice with many associated items. I know the affected tables (foreign keys) and I have last nights backup of the db. However I don't want restore the entire db back from last night just the deleted invoice record/s. What is the best practice procedure for accomplishing this?
View 2 Replies
View Related
Jan 24, 2007
I have a column session_date that holds the date in the format: m/d/yyyy or m/dd/yyyy
how do I write my select statement to grab all records by a specific year?
SELECT *
FROM mytable
WHERE session_date =
Thanks for any help.
View 2 Replies
View Related
Dec 26, 2006
I am not sure where i should post this question since it falls both in Report Server and T-Sql but here goes...
I currently need to run a Report that has only specified records that the client/user wants by clicking the check in the check box next to the record they want. They can pick as many or a few of the records that want then run a report only with the records they indicated they wanted... i am thinking they will need some kind of t-sql statement either a function or temp table but i am not sure if even that...
if anyone has any ideas please reply...
Thanks,
WoFe
EXAMPLE: Instead of running a report on records 1, 2, 3, 4, 5, 6, 7, 8, 9
they would run the report on records: 2, 5, 6, 9
View 1 Replies
View Related
May 27, 2015
I have a request for being able to show ordertotals for those orders that contains a specific productgroup.
Case
Order 1 ProductGroup Value
A 20
B 40
C 40
Total 100
Order 2 ProductGroup Value
A 20
D 40
Total 60
So if i slice OrderTotal by ProductGroup the result will be
A 160
B 100
C 100
D 60
ALL 160
how to solve this.
View 9 Replies
View Related
Feb 6, 2008
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
View 6 Replies
View Related
Sep 6, 2007
This is out of my league. I’m hoping to get some good advice from someone experienced in the area. My inquiry is how to best handle large amounts of records, say 500,000 records or so. I am web programming and can’t send all this info from server to client.
Part of the problem is the manner in which the data gets stored. I cannot calculate what records I need to get for a distant page (i.e. if 10 items per page then where is my data getting page #512).
These are the very first five (5) records. First row is the primary key.
14
451
0
V5
2 vials
1
V5
8/10/2007
3
20
451
0
V10
2 vials
2
V5
8/10/2007
3
25
451
0
V5
2 vials
1
V5
8/15/2007
3
26
451
0
V10
2 vials
2
V5
8/15/2007
3
27
451
0
V40
2 vials
8
V5
8/15/2007
3Because records 1 through thirteen had been deleted, the primary keys for the first (5) are no longer 1, 2, 3, 4, 5. Had this been the case, a person could easily retrieve page 512, by mathematical calculation.
Page 1 would have been Records 1..10
# 2 = > 11..20
# 512=> 5111..5120.
I already have a program that loads the entirety into an arraylist; then picks out the page of data from the arraylist location. I could rewrite things so that a temporary SQL Table is created – but I don’t know is that a good idea?
All advice welcome - TIA
View 4 Replies
View Related
Mar 5, 2008
Hi,
How to display specific number of records? That means I want to display records starting from 3th row to 5th row. Please send your suggestions or links.
Table
-----
Name Age
-----------------
Raja 23
Kumar 26
Suresh 30
Rani 22
Subha 32
Ganesh 25
The result will be
Name Age
-----------------
Suresh 30
Rani 22
Subha 32
Thanx.
GANESAN MURUGESAN
View 2 Replies
View Related
Jul 20, 2005
Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
View 3 Replies
View Related
May 25, 2015
I have a requirement to display the total of a Group after subtracting a specific value from the same Group.
Example: Say the below data is grouped on a particular column
Group Values Month
Jan-15 Feb-15 Mar-15
A 10 20 30
B 5 10 25
C 1 2 3
D 5 10 15
Total 11 22 33
Formula is : Sum(A+C+D)- Sum(B)
What is the best way to Group the above scenario from SSRS level and display the result as shown above. I am able to display all the values except the last total row where am displaying the complete total i.e. 21 42 73.
How do I dynamically subtract the values for row B which is one of the group values.
View 4 Replies
View Related
Jun 20, 2007
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
Thanks,
View 4 Replies
View Related
Apr 21, 2008
hello, i have 100,000 records in my table. Out of that what means to me is only 500 records that I want to pass thru and send it to destination.
The data I am trying to transport has a column ID with Not Null values in it.
Please let me know how can we do it,
Can we use a look up for ID from the other tables to just pass thru when it finds an ID match,
Am i correct.
View 3 Replies
View Related
Jun 19, 2007
I need some assistance creating a query. I have users table, groups table, and UsersGroups table. Basically a single user could belong to one or more groups. I need to be able to return the users that are not in a specified groups. Table def are below.
USERS:
--------
ID (Primary Key)
Username
Password
GROUPS:
------------
ID (Primary Key)
GroupName
USERSGROUPS:
-----------------
ID (Primary Key)
GroupID
UserID
Any help is greatly appreciated!
Thanks!
View 4 Replies
View Related
Jan 19, 2008
I have a MyNames table which contains over 100 columns. The Name column may have the same name in many rows. The data looks like this.
Name..........Factor1..........Factor2...
John..........X................Y
John..........X................X
Mary..........T................Y
Mary..........V................Y
I need a result table containing all 100 columns from MyNames but only the unique names in the name column. I don’t care which name row is used.
Name..........Factor1..........Factor2...
John..........X................Y
Mary..........V................Y
I have tried DISTINCT and GROUP BY.
DISTINCT will unique on every column listed so I will end up with the name repetred in the Name column.
SELECT DISIINCT Name, Factor1, Factor2 => I do not want the Factor columns involved in the distinct. This will cause the same name to appear in more than one row.
Group by will requires that I place the remaininf 99 columns in a function in order to have the columns listed.
SELECT Name, MAX(Address)... => adding 99 fields.
Is there a way get only the unique Names with the 99 other columns with out having to manually put the other 99 columns in functions?
View 13 Replies
View Related
Mar 18, 2015
I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
[code]....
View 6 Replies
View Related
Jul 20, 2005
Hi,I have an Access application with linked tables via ODBC to MSSQLserver 2000.Having a weird problem, probably something i've done while not beingaware of (kinda newbie).the last 20 records (and growing)of a specific table are locked - cantchange them - ("another user is editing these records ... ").I know for a fact that no one is editing records and yet no user canedit these last records in the MDB - including the administrator -while able to add new records.Administrator able to edit records in the ADP (mssql server) where thetables are stored.Please help, the application is renedred inert .Thanks for reading,Oren.
View 3 Replies
View Related
Oct 24, 2013
I have a table with multiple products from different suppliers. there will duplicate products in my db as Supplier A may stock the same product as Supplier B. I have a business rule that only one unique product can be online at one time and as such I want the cheapest product from the 2 Suppliers to be displayed where there is a match.
I have been trying a Group By clause and also a partition by but my problem is returning the primary key for the winning row (minimum price).
here is an example
CREATE TABLE #TempTable(
productcode nvarchar(50),
productname varchar(50),
productmanufacturer varchar(50),
saleprice money,
tyrewidth varchar(5),
[Code] ....
How can I return the productcode in the above query for the row with the cheapest price? If I try MIN(productcode) or MAX(productcode) it will not always return the product code associated to the min price.
View 5 Replies
View Related
Jun 10, 2015
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
View 3 Replies
View Related
Apr 12, 2015
I have a table with records like that.
Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0
I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.
View 1 Replies
View Related
