Query To Get Latest 2 Records For Each Group
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
ADVERTISEMENT
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
Mar 21, 2007
Hi all,
hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.
There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:
SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber
What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:
SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03
What i really need is to only list the last part that is installed, hence the one with the latest timestamp:
SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03
Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.
Many thanks in advance,
Grant
View 8 Replies
View Related
Aug 25, 2015
I have a scenario as below for one ID -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+
I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+
But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.
CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)
INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')
View 8 Replies
View Related
Mar 17, 2006
I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
View 24 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Aug 8, 2013
Here's what I'm trying to do
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
2501102$1012013-04-23
3501102$1182013-02-04
4501102$982013-05-19
5501103$532013-05-21
6501103$392013-07-09
I'm trying to get the latest buy price for each fund a member bought.
So in the above example, I'm trying to formulate a query to retrieve all the latest bought price for memberid 105:
Desire Query Result
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
4501102$982013-05-19
6501103$392013-07-09
So far, I've tried using Group By clause on FundID and BuyPrice and MAX() function for the date, but I can' get the TransID.
View 3 Replies
View Related
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
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
Sep 21, 2007
Hello!
I have a table, where one of the columns is the date/timestamp of when each row was inserted. I want to be able to extract the most recently inserted rows.
With Sybase (a not so distant cousin of MS SQL) the following works:
select * from TABLE having date = max(date)
With MS SQL, however, the same query does not work:
Column 'TABLE.date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
What's the solution? Thanks!
View 14 Replies
View Related
Dec 3, 2007
Let's say I have a data entry from a pool of employees:
table is as follow:
EmpNo Branch Date Amount
1 A101 11/30/2007 $0.90
1 A101 11/30/2007 $1.20
2 A101 11/30/2007 $0.90
3 A101 11/30/2007 $0.80
How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?
Thanks! :D
View 7 Replies
View Related
Feb 13, 2008
Hi,
I hava a table with the following information
CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10));
INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2');
INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2');
INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2');
INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3');
INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2');
INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3');
INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4');
INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');
Here based on latest revision and refid I should get the fieldnam and value.
Expected output:
REFID FIELDNAM VALUE REVISION
1001 A A4 1
1001 E E4 1
1002 B B2 0
1001 C C2 0
View 7 Replies
View Related
Dec 16, 2007
Hi all,
I have a question regarding SQL Server Performance and would be grateful for a tip. Let's say I have a DB with 50.000 records. These records belong to 1.000 different datasets, so there is 1 actual and 49 historical data records. For example a company with 1000 employees has a database where each year a new record is created for each employee so after 50 years they have 50.000 records (50 years x 1000 employees). 1 record is actual, and 49 are historical. What is the best way to store this? Main target is performance for the enduser, so when an employee clicks "See all my records" it should be fast. But on the other hand the application mainly works only with the current year. Additionally it should also be fast for the boss of business unit who wants to see the latest records of his e.g. 100 employees. I have some ideas and would like to get your opinion:
1. Retrieve by latest date
Just store the records. To get the current year just select the record with the latest year. Disadvantage might be with larger databases: If the company switches to store the requests per month, each user would have 600 records (12 months x 50 years). Each time the latest record should be retrieved, 600 recards have to be compared regarding the latest date (or sorted by date descending using Top1, but this might be a problem for the boss then? Or could this be combined for a group if the boss wants to see all the latest records of his employees?).
2. Add a 'IsCurrent'-Flag
Each time a new record is stored it should be compared to the latest record. If it is newer, the 'IsCurrent'-Flag should be removed and then checked on the new record. This should be fast processed (because on saving a new record it only needs to be checked against the currently 'IsCurrent'-flagged record), and for retrieving the current record no further comparison is necessary. But how could I do this? I need to update the "AddRecord"-SP with this comparison, but I don't know how to do this.
Currently number 2 is my favorite, I just don't know how to do it ;-) What is your opinion about it, and could you include an example?
Thanks
View 20 Replies
View Related
Mar 17, 2015
I have a table where i am inserting into temp table, I mean selecting the records from existing table. From this how can i get latest records.
create table studentmarks
(
id int,
name varchar(20),
marks int
)
Insert into dbo.studentmarks values(1,'sha',20);
[Code] ....
How to write a sql query to get the below output
studentname totalmarks
sha 90
hu 120
View 1 Replies
View Related
Mar 26, 2012
I have 2 tables:
TransactionsImport (which is the destination table)
TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600
View 2 Replies
View Related
Jun 15, 2006
Itemlookup tableField names : index_id (primary key), itemno, description.It has a child table, which is ItemPriceHistory tableThe relationship to the child table is one (parent table)-to-many(child table). - It is possible to have no child record for some rowsin the parent table.ItemPriceHistory tableField names: index_id (primary key), itemlookupID (foreign key of theItemlookup table), date begin, priceIt is a child table of the itemlookup table.How can I get all records for both tables with the latest begin date ifexists?I also need to show the records in the parent table if there is norelated record in the child table.Please help
View 4 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
Feb 22, 2007
Hi all,I have the following tableName Date Wish ValidName is person's name, date defaults to getdate() and is neverassigned directly (datetime field), Wish is some message, and Valid isbit, 1 indicates if the wish is the latest, and therefore valid. Allprevious wishes are kept in database, and are "invalidated" by settingthe Valid to 0.So, a typical data set looks like:Name Date Wish ValidJoe 02/01/2007 Ice Cream 0Joe 02/04/2007 Bicycle 0Joe 02/06/2007 PS3 0Joe 02/22/2007 XBox 360 1Mary 02/02/2007 Barbie 0Mary 02/04/2007 Cindy 0Mary 02/06/2007 Barbie house 0Mary 02/20/2007 Get married 1My users want to see the initial wish at some point and another onesome time later (they provide dates). So, if someone wanted to seechanges in wishes between 02/03 and till 02/15, they would get thatJoe's initial wish was Bicycle and the latest that he wanted was PS3.As for Mary, she started wanting Cindy and ended up thinking about theBarbie house.I can do UNION, but is there another way to do that?Thank you.
View 7 Replies
View Related
Jan 6, 2005
Hi All,
I have thsi list of record
Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004
sp i want to list out cars that status solid has been sold
so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks
Regards,
Shaffiq
View 6 Replies
View Related
Jun 11, 2006
I've been baffled over how to do this without using a script... I would like to get the info I need with a single query.
Here's my scenario, the table looks like this (simplified):
ID, TIME
101, 5am
101, 6am
104, 5am
260, 5am
104, 6am
260, 6am
101, 7am
260, 9am
104, 7am
101, 8am
So basically I have a column of identifications and a column of times. They won't necessarily be in order. I would like a query that gives me this:
101, 8am
104, 7am
260, 9am
It would order the IDs ascending, only showing the newest time assigned to that ID in the table.
Thanks in advance for any help :)
View 14 Replies
View Related
Jun 29, 2012
Table has details like below:
NAME UPATE-TIMESTAMP
==== ===============
mary time1
mary time2
mary time3
tom time1
tom time2
tom time3
tom time4
richard time1
richard time2
Output Expected:
NAME UPATE-TIMESTAMP
==== ===============
mary time3
tom time4
richard time2
In summary, the requirement is to fetch the latest upated record for each unique NAME.
View 1 Replies
View Related
May 29, 2008
Hi,
I have this specific problem, that has been causing me alot of headaches for the last couple of days, and I can't seem to fiure it out on my own.
Basically i am working on a many to one currency conversion setup, where I have a fact table with exchange rates for periods and for all input currencies. The reporting currency is USD. Here is a demonstration of the data in it:
Valid Date Source Currency Rate
2008-01-01 GBP 2.0563
2008-01-01 EUR 1.4634
2008-04-01 GBP 1.9968
2008-04-01 EUR 1.5197
Normal period by period conversion is easy enough, but I want to convert the measures at the latest known rate, based on the period selected. Lets say I run an analysis without slicing on time, then it want it to convert at the last entered rate of all time, and if the user wants to run the analysis as of March 08, then it should convert all data to the rate as of January, since this is the latest one known.
I have tried to set the measure to a semi-additive behaviour, with "lastnonempty", but the problem with that is the conversion over time. Here is an example:
I want to show a measure summed "all to date" as of June 08. What I want is it to convert everything at the rates entered in April 08, but what I get is that it convert everything up until Jan 08 and the rates entered in January, and thereafter it starts to use the rated entered in April.
Period Amount LCY Currency Code Exchange Rate
Dec 07 100 GBP 2.0563 (Wrong, should be 1.9968)
Dec 07 100 EUR 1.4634 (Wrong, should be 1.5197)
Jan 08 100 GBP 2.0563 (Wrong, should be 1.9968)
Jan 08 100 EUR 1.4634 (Wrong, should be 1.5197)
Feb 08 100 GBP 1.9968
Feb 08 100 EUR 1.5197
Mar 08 100 GBP 1.9968
Mar 08 100 EUR 1.5197
Apr 08 100 GBP 1.9968
Apr 08 100 EUR 1.5197
May 08 100 GBP 1.9968
May 08 100 EUR 1.5197
Jun 08 100 GBP 1.9968
Jun 08 100 EUR 1.5197
How do I create a measure containing the latest known rates based on time selection?
Thanks in advance
View 2 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Oct 30, 2007
I have the following table called BADSANTA:
varchar(30) datetime
NAME WHENBAD
OJ Simpson 2007-1-12 xx:xx:xx:xxx
OJ Simpson 2007-4-2 xx:xx:xx:xxx
Monica Lewinsky 2006-7-4 xx:xx:xx:xxx
Monica Lewinsky 2006-10-31 xx:xx:xx:xxx
Bill Clinton 2006-7-4 xx:xx:xx:xxx
Bill Clinton 2006-10-31 xx:xx:xx:xxx
Bart Simpson 2006-11-2 xx:xx:xx:xxx
Bart Simpson 2006-2-25 xx:xx:xx:xxx
Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
Thanks in advanced.
View 4 Replies
View Related
Jul 20, 2005
The following SQL query :-SELECT CardHolder.RecordID, History.GenTime, History.Link1FROM History FULL OUTER JOINCard ON History.Param3 =LTRIM(RTRIM(Card.CardNumber)) FULL OUTER JOINCardHolder ON Card.CardHolderID =CardHolder.RecordIDWHERE (Card.Deleted = 0) AND (History.GenTime IS NOT NULL)ORDER BY CardHolder.RecordID, History.GenTime DESCreturns :-RecordID GenTime Link12 04/06/2004 15:30:00 1232 01/06/2004 16:00:00 1232 01/06/2004 08:00:00 1101155 02/06/2004 11:30:00 1231155 02/06/2004 08:00:00 1103925 03/06/2004 09:00:00 1233925 03/06/2004 08:00:00 1104511 06/06/2004 11:30:00 1234511 06/06/2004 10:30:00 110Is there a way of modifying this query to just return the lastestgenTime for each RecordID ??? ie return just rows 1,4,6 & 8.I assume it is something to do with MAX, but I can't seem to get myhead around it.Any help, or pointers would be appreciated.Oh, running query on Microsoft SQL Server 2000.RegardsDave
View 3 Replies
View Related
Aug 29, 2014
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
View 7 Replies
View Related
Feb 11, 2012
I have a group of records like this;
NAME| RUN START| RUN END| LK1| LK2| DESC
Graeme Brown|2012-02-23 07:20:00|2012-02-23 07:50:00|2213|2244|AK1/4/PI2
Graeme Brown|2012-02-23 08:00:00|2012-02-23 09:25:00|2244|2052|AK1/4/PI2
Graeme Brown|2012-02-23 09:30:00|2012-02-23 11:05:00|2052|917|AK1/4/PI2
Graeme Brown|2012-02-23 12:15:00|2012-02-23 13:55:00|917|2052|AK1/4/PI2
Graeme Brown|2012-02-23 14:05:00|2012-02-23 15:40:00|2052|1111|AK1/4/PI2
They are grouped on the last column [DESC].
I want to get;
NAME| RUN START| RUN END| LK1| LK2| DESC
Graeme Brown| 2012-02-23 07:20:00| 2012-02-23 15:40:00| 2213| 1111| AK1/4/PI2
So what it needs to do is combine the ;
earliest RUN START and corresponding LK1 with
latest RUN END and corresponding LK2.
I've tried creating temp tables with the mins and maxs - but then I can't combine them with the LK1 and Lk2 fields...
View 3 Replies
View Related
Jun 21, 2007
sample query results:
cid cno cvalue
--- --- -------
835201add edit
835201add edit
836202with VAT
836202with VAT
how can i filter this more into this:
835201add edit
836202with VAT
View 3 Replies
View Related
Nov 7, 2007
Hi,
can anyone help me to group by Top 2 records by ID and Date?
My table contains following data
create table #test (id int, code varchar(10),TestDate datetime)
insert into #test (id,code,TestDate) values (12,'ABC','01/11/2007')
insert into #test (id,code,TestDate) values (12,'ABC','01/11/2007')
insert into #test (id,code,TestDate) values (12,'BC','02/18/2007')
insert into #test (id,code,TestDate) values (12,'BC','02/18/2007')
insert into #test (id,code,TestDate) values (12,'BC12','10/01/2007')
insert into #test (id,code,TestDate) values (12,'BC11','10/11/2007')
insert into #test (id,code,TestDate) values (12,'BC11','01/25/2007')
insert into #test (id,code,TestDate) values (12,'ABC','01/11/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/11/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/12/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/12/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/11/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/11/2007')
select * from #test order by TestDate
--Result Set
id code TestDate
12 ABC 2007-01-11 00:00:00.000
12 ABC 2007-01-11 00:00:00.000
12 ABC 2007-01-11 00:00:00.000
12 BC11 2007-01-25 00:00:00.000
14 YZ123 2007-02-11 00:00:00.000
14 YZ123 2007-02-11 00:00:00.000
14 YZ123 2007-02-11 00:00:00.000
14 YZ123 2007-02-12 00:00:00.000
14 YZ123 2007-02-12 00:00:00.000
12 BC 2007-02-18 00:00:00.000
12 BC 2007-02-18 00:00:00.000
12 BC12 2007-10-01 00:00:00.000
12 BC11 2007-10-11 00:00:00.000
--I want to get only duplicate records, so SQL should eliminate uniq recod.Total will --get total number of records per ID and date, but want to display only two reacords by --Id and Testdate.
--my result set should contain following data
--Total of first byID and by Date
----id code TestDate Total
----12 ABC 2007-01-11 00:00:00.000 3
----12 ABC 2007-01-11 00:00:00.000
----14 YZ123 2007-02-11 00:00:00.000 3
----14 YZ123 2007-02-11 00:00:00.000
----14 YZ123 2007-02-12 00:00:00.000 2
----14 YZ123 2007-02-12 00:00:00.000
----12 BC 2007-02-18 00:00:00.000 2
----12 BC 2007-02-18 00:00:00.000
If anyone have any idea,, plz help me out..
Thanks....
View 2 Replies
View Related
Dec 20, 2013
1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result
======================================================================
CREATE TABLE and Insert Data
======================================================================
use master
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
[Code] .....
View 14 Replies
View Related
Feb 11, 2015
Here is my table data:
CREATE TABLE
#TestTable (
Pk INT,
GroupID INT,
Enabled BIT
[code]..
I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?
View 2 Replies
View Related
Aug 14, 2007
Hi all, I am relatively new to sql and have an issue I am trying to solve. I have a table with several records with the same id:
id amount date
-- ------ ------
1 100 01/01/2006
1 2000 06/01/2005
2 200 01/01/2005
2 500 06/01/2007
how would I get the data for the record with the newest date?
So I would return:
id amount date
-- ------ ----------
1 100 01/01/2006
2 500 06/01/2007
Thanks in advance for the help.
View 8 Replies
View Related
Apr 21, 2006
Hi,
I've a problem with the following stored procedure Select . It does compile and run, but doesn't return the sorted result I wanted, which was to have the records from tblPieces (alias Pcs) sorted by (in order) Pcs.fkBatchId, Pcs.fkProfileCode, Pcs.Colour. What happens instead, I think, is that the correct records are selected, but in the record creation order. CREATE PROCEDURE dbo.LoadOneBatch
(
@BatchId int, -- the pkBatchId of the batch required
@OnlyNew bit -- If true, only consider batches that haven't ever been loaded.
)
AS
SET NOCOUNT OFF
SELECT Bat.pkBatchId,
Pcs.*
FROM tblBatches AS Bat
JOIN (
SELECT Bat1.pkBatchId,
Pcs1.fkProfileCode,
Pcs1.Colour
FROM tblBatches AS Bat1
JOIN tblPieces AS Pcs1 ON Pcs1.fkBatchId = Bat1.pkBatchId
WHERE Bat1.pkBatchId = @BatchId
GROUP BY Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour
) SubQ ON SubQ.pkBatchId = Bat.pkBatchId
JOIN tblPieces AS Pcs ON Pcs.fkBatchId = Bat.pkBatchId
WHERE (@OnlyNew = 1 And Bat.IsLoaded = 0 And
Bat.IsCompleted = 0 And Bat.pkBatchId = @BatchId) Or
(@OnlyNew = 0 And Bat.pkBatchId = @BatchId)
GO
EXEC LoadOneBatch @BatchId = 1, @OnlyNew = 0
The DDL for the two tables is:CREATE TABLE [tblBatches](
[Stamp] timestamp NOT NULL,
[pkBatchId] int IDENTITY(1,1) NOT NULL,
[BatchNo] varchar(50) NULL,
[SubmitTime] [datetime] NULL,
[FinishTime] [datetime] NULL,
[IsLoaded] bit NULL,
[IsCompleted] bit NULL,
PRIMARY KEY ( [pkBatchId] ASC )
)
CREATE TABLE [tblPieces](
[Stamp] timestamp NOT NULL,
[pkPieceId] int IDENTITY(1,1) NOT NULL,
[fkBarId] int NULL,
[fkBatchId] int NULL,
[PieceNo] varchar(12) NOT NULL,
[Descrip] varchar(48) NULL,
[Position] real NULL,
[LeadPrep] char(1) NOT NULL,
[TailPrep] char(1) NOT NULL,
[Length] real NOT NULL,
[fkProfileCode] varchar(10) NOT NULL,
[Colour] varchar(5) NOT NULL,
PRIMARY KEY ( [pkPieceId] ASC )
)
The data records output are roughly:pkBatchId Stamp pkPieceIdfkBarIdfkBatchIdPieceNo Descrip PositionLeadPrepTailPrepLengthfkProfileCodeColour
1 0x00000000000036B21 NULL1 00000000000118960 /003/F>1 N2NULL / 913 6000 wht
1 0x00000000000036B32 NULL1 00000000000218960 /003/F<1 N2NULL / 913 6000 wht
View 5 Replies
View Related
