Table 1:
AB C
114.7
214.7
314.7
Table 2:
ADE
11a1.2
11b1.8
22a2.1
22b2.3
33a0.6
I would like to filter the second table taking one row per ID of Field A (first table) and selecting the rows whose sum of E is equal to the value in field C; in this example the resulting table should be:
Table 3:
ADE
11b1.8
22b2.3
33a0.6
Total field E = value in field C = 4.7
Hi there,
I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more). In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping. However, I need to do a calculation in a textbox above the table using these row counts. The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way. Is there anyway to get access to these row counts on the groups? Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible? Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).
Any help is much appreciated,
Thanks.
I have a temporary table that contain 1000 rows. I want to insert records from 900 to 1000 into another temporary table. Is ther any query that will do this in SQL server. With cursors I can do that but I need one shot query. Please give me some suggestions.
I using this query but it is not returning any rows.
select * into #t2 from #t1 t1 where not exists( select top 900 * from #t1 t2)
hi!I have two tables with same structure. I need to run a select query thatwill return only the rowsfrom one table that are not in the other.In MySQL it would beselect * from table1 MINUS select * from table2.but in MsSql I can not find the apropriate way to do this.Any Help?Zvonko
View 3 Replies View RelatedHy!
I'm trying to write a query which returns only some rows in my table...
For example :
I want all the rows included between 30 and 50 from table XYZ
(30 and 50 are not keys but really the numbers of records)
Is there someone out there who has a hint for me???
Thank you for your help and sorry for my english!
RadiFluide
I need to select the last 6 rows from a table... I was doing it like this:
select * from table where id > (select max(id)-6 as id from table)
But if one of the records is deleted it will only show 5.... Is there any easy way to make it show the bottom 6 records?
much thanks sql gods...
Hi,
These aren't my tables, but lets dumb it down for my sake!
I have 2 tables:
CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount
There are several records in TransactionInfo for each record in CompanyInfo.
I want to:
SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)
How can I do this? Can I?
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
I have a report that I created and the report was working until I added some fields to a group footer row in a table.
My table has 5 group levels. I had information displaying in the 5th level header group and detail. It was working fine. Then I added some fields to the 4th level group footer. Now it displays only the Page header, Table header, and the 4th level group footer data.
What happened to the rest of the data?
All the cells and rows I want to display have the Visibility Hidden set to false. I tried removing the objects I added (to the 4th level group footer) and it still does not work. Is this a bug or did I set something that is hiding the data.
Thanks,
Fred
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
I want to write a stored procedure to add all of the flying time on a given airplane for each flight until now and then reset the total when it gets to a new airplane. I want to be able to pull from the table all flying time from a given date for a given airplane.
For example:
AircraftNo Date Flight_Hrs Total
38 3/1/06 1 4.5
38 5/10/06 1.5 3.5
38 7/10/06 2 2
100 8/19/03 4 8.5
100 10/04/04 3 4.5
100 9/17/06 1.5 1.5
I pulled the following code from the internet but I'm getting errors.
select AircraftNo
,Actual_departing_date,Flight_Hours
,sum(Flight_hrs) over partition by AircraftNo
order by Date
rows between current row and unbounded following) total
from (Select Distinct
F.AircraftNo
,F.Date
,C.Flight_Hrs
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No
Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.
I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:
EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234
Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.
We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)
Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1
1 0 0 2
0 0 0 2
0 0 1 2
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
Can I do this in SQL ?...some kind of grouping ?
how can I select 10 random rows from a table?
Please help
Hello,
Does anyone know how to select rows by their position in a table. I need to be able to e.g return the 2,000th - 2,200th rows as a set. I will have many concurrent clients connecting to this table so I do not want to use cursors. The table is 800,000 in length.
Any ideas people?
Thanks,
Alan
Hi,
I've a table the following table:
ActDate datetime
CustomerID int
data on the table:
6/27/2007 10:23:22 AM
113
6/27/2007 10:25:20 AM
113
6/27/2007 11:38:34 AM
76
6/27/2007 5:23:22 PM
212
6/28/2007 11:58:58 AM
110
6/28/2007 12:24:17 PM
110
6/28/2007 12:27:15 PM
110
7/3/2007 5:16:44 PM
63
7/3/2007 5:16:56 PM
63
7/12/2007 3:35:50 PM
98
7/13/2007 3:30:05 PM
110
7/13/2007 3:32:49 PM
110
7/13/2007 3:38:01 PM
110
7/13/2007 3:46:00 PM
110
7/14/2007 10:25:55 AM
82
7/15/2007 9:19:11 AM
18
7/21/2007 3:53:16 PM
34
7/23/2007 11:53:43 AM
84
7/23/2007 11:55:24 AM
84
7/23/2007 11:55:48 AM
84
7/24/2007 7:41:31 PM
116
7/27/2007 9:39:52 AM
110
7/27/2007 12:41:49 PM
18
7/28/2007 11:00:55 AM
113
7/31/2007 10:11:31 AM
82
8/1/2007 5:56:23 PM
98
8/2/2007 7:22:16 PM
77
8/2/2007 8:28:11 PM
77
I need to filter the table in order to have only a record by CustomerID with the higher date.
Thanks in Advance
JP
Hi,
I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's.
can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
Hello all.
Im trying to write a stored procedure which i can stipulate the top number of rows it returns. I know i use the following syntax:
SELECT TOP 5
And i know i need to pass the number of rows i want as a variable to the stored proceudre like:
@Top as Integer
But when i try and do the following. SQL Server complains about the syntax:
SELECT TOP @Top
Can anyone advise me how i should be doing this?
I have two tables a stock table and a price table and I want to select the correct price for the Stock Date.
Problem is sometimes there is a promotion date in the price table between the live dates so the rows double up. Below is my sql but I get over 17,000 rows when it should be about 16,964.
I will post up the table and data.
SELECT a.[Company]
,a.[ProductID]
,a.[ColourSize]
,a.[StockDate]
,a.[Quantity]
,b.[Ticket Price]
FROM[Stock_Ledger] a
LEFT OUTER JOIN [Product_Prices] b
ON a.[Company] = b.[Company]
AND a.[ProductID] = b.[ProductID]
AND a.[ColourSize] = b.[ColourSize]
AND a.[StockDate] BETWEEN b.[StartDate] AND b.[EndDate]
ORDER BY a.[StockDate],a.[ProductID],a.[ColourSize]
Mike writes "Hi,
I am a beginner with TSQL and I hope this is not a silly question :-)
Lets say I have a table with 2 columns, 1 a primary key identity field with increment 1 and the other a char.
EG:
ID ANIMAL
---------
1 CAT
2 DOG
3 PIG
4 RAT
5 PIG
6 DOG
7 DOG
.
.
And so on with many entries
How do I return a selection of rows that have the contents of the ANIMAL field matching 1 or more times
EG:
From above table I want to return rows 2,6 & 7 and 3 & 5 ONLY and not 1(CAT) and 4(RAT) because they only occur once.
In my real life situation I have unknown numeric data in field 2 but the principal is the same.
How do I do this?
Thanks in Advance
Mike"
I have a table stock, on the table I have company_name, stock_value, sector.
Now I want to get top five rows from this table depending on the five highest value of stock_value and for each sector. The query has to run on both oracle DB and Microsoft SQL Server with top priority MSSQL.
I will appreciate if you help me on this
jideofor
I know I can use "First" to specify a number of rows to return from a query but is it possible for the number of rows returned to be based on a parameter, something like this:
SELECT FIRST @someNumber name, age
FROM friends
WHERE age > @ageInput
Hi experts,I have been trying to limit the table rows in the following situation,any suggestions will be appreciated.we have table called tempTb has columns id, c_id, c_name, rating, datecolumns.id is an identity column.date is a datetime column, the rest are varchar datatype.Here is the table structure with sample data,idc_idc_nameratingdate1aoamer onli11/1/20022aoamer onli13/1/20023aoamer onli16/1/20024aoamer onli39/1/20025aoamer onli312/1/20026aoamer onli33/1/20037aoamer onli36/1/20038aoamer onli39/1/20039aoamer onli212/1/200310aoamer onli16/1/200411aoamer onli112/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200214xyxabs yasd26/1/200215xyxabs yasd29/1/200216xyxabs yasd112/1/200217xyxabs yasd13/1/200318xyxabs yasd36/1/200319xyxabs yasd39/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/200422xyxabs yasd112/1/2004[color=blue]>From this table I need to select the rows with rating changes only,[/color]i.e if two or three consecutive rows have same rating only the firstrow should be selected.the selection should look like...idc_idc_nameratingdate1aoamer onli11/1/20024aoamer onli39/1/20029aoamer onli212/1/200310aoamer onli16/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200216xyxabs yasd112/1/200218xyxabs yasd36/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/2004I was trying to do this by self-joining the table like....select t1.* from tempTb t1, tempTb t2where t1.id!=t2.id,t1.c_id=t2.c_id,t1.c_name=t2.c_name,t1.rating!=t2.rating.But this is generating cartesian products,I have tried some other combinations after where clause with date colmnwtc,but none seems to give the required result.so if anybody can guide me in the right direction I would appreciateit.Thanks alot,Remote
View 4 Replies View RelatedHello All,I have this table:CREATE TABLE [dbo].[tbl_TESTING] ([ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,[FNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[MOVEDINYR] [int] NULL ,[MOVEDOUTYR] [int] NULL) ON [PRIMARY]GOwith these records:INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1995', '2000')INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1994', '2005')What I would like to do is be able to select FIRSTNAME, LASTNAME,MIN(MOVEDINYR), MAX(MOVEDOUTYR) for JAMES TAYLOR e.g.FIRSTNAME=JAMESLASTNAME=TAYLORMOVEDINYR=1994MOVEDOUTYR=2005Some sql syntax help appreciated,thanks in advance!
View 2 Replies View RelatedIs it posible on a query ordered by employee name, for exemple, that MSSQL gives me a position of which employee is contained on the recordset?
that's my query (simplified)
SELECT name, payment, category FROM employee_payments ORDER BY name
John, 1000, sallary
Peter, 1500, sallary
Peter, 500, other
Zeus, 1000, sallary
I want to add another field giving me the position of the employee on the recordset:
John, 1000, sallary, 1
Peter, 1500, sallary, 2
Peter, 500, other, 2
Zeus, 1000, sallary, 3
Is it posible?
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies View RelatedI got the following query:SELECT TOP (8) ext.extID, ext.Quote, ext.sourceTitle, ext.extRating, gf_game.gameID, gf_game.catID, gf_game.URL, gf_game.TitleFROM gf_game_ext AS ext INNER JOIN gf_game ON gf_game.gameID = ext.gameIDWHERE (ext.Approved = 1)ORDER BY ext.extID DESC which is e.g. producing this output: 6000 -some text- Title 90 1960 2 tom-cl tom cl5999 -some text- title 90 1960 2 tom-clcl asdf5998 -some text- title 90 1959 2 tom-cl-cl asdfWhat I'd like to do now is to filter out the duplicate GameIDs (= 1960) so that just one unique row with the gameid 1960 is remaining. If I put in a SELECT DINSTINCT TOP(8) it just counts for the table ext, but I need it to count for gf_game.gameID - is that possible?Thanks a lot!
View 9 Replies View RelatedHi all -
is there a way to process a file x records at a time?
We have a table that I need to append to an existing table. The date columns are currently in char but must be converted to datetime for the existing table. The problem is I have bad data. There are 3 million rows where the date field isn't valid for SQL's datetime format. Since this is the data I have, I have to work with it. I would like for SQL to just insert a null if it comes upon a bad date. Currently when it encounters a field that isn't valid, it stops the process with an error.
I have tried to go around it below, but there is still something "hanging" I would like to be able just to insert one million rows at a time and if it errors, then I can look at the next million, find the error, fix it and continue on.
Any suggestions? Or if you have a better idea all together I would love to see it.
SQL Server 7.0, SP2
,CASE
when
(substring(check_date,1,4) not between '1997' and '2000' or
substring(check_date,5,2) not between '01' and '12' or
substring(check_date,7,2) not between '01' and '31') THEN null
ELSE cast(check_date as datetime)
END AS check_date
Thanks,
Michelle
I have to select rows based on if the transaction date = todays date.The column is defined as numeric 8 with 0 decimal.how to code for todays date with such a column?
View 8 Replies View RelatedHere's my current SQL:
SELECT
RN_TEST_ID AS 'Test ID',
MAX(RN_EXECUTION_DATE) AS 'Last Execution Date',
MAX(RN_EXECUTION_TIME) AS 'Execution Time',
RN_DURATION AS 'Run Duration'
FROM RUN
[code]....
Here's a sample of data returned:
Test IDLast Execution DateExecution TimeRun Duration
86722/9/2015 0:0012:08:16180
86822/9/2015 0:0011:29:06181
86842/9/2015 0:0008:29:17119
105252/3/2015 0:0019:03:4089
105252/3/2015 0:0019:10:13305
106682/3/2015 0:0018:55:43103
106682/6/2015 0:0018:10:50123
114572/3/2015 0:0011:40:0726
What I need are two things:
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
As a beginner i am having trouble with this.
i have two different tables , both have a name column, nvarchar datatype.
I would like to select from table B all the rows which contain a name which is not in table A.
Then insert these rows, into table A
tried a few different ways & just keep getting strange errors that refer to courier font ??
SQL Team Your my Hero !
Here's my tables:
Students
-----------------------
StudentID | StudentName
-----------------------
StudentClasses
-----------------------
StudentID | ClassName
-----------------------
I'm trying to put data into a gridview in ASP.NET 2.0 like this:
Student | Classes
-----------------
Name1 | ClassName1 ClassName2 ClassName3
Name2 | ClassName1
Name3 | ClassName1 ClassName2
I just need to retrieve names of each class that a student is taking, then print them out one after another.