SQL Server 2012 :: Query Pulling Latest Info Data From Table

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


ADVERTISEMENT

Query The Latest Info

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

SQL Server 2012 :: Retrieving Dataset That Only Includes Latest Versions Of Data

Mar 28, 2014

In our Microsoft Dynamics Nav instance we have a Sales Header Archive table - into which copies of the Sales Header are placed, with 3 items forming the compound key:

Document Number
Version
Occurrence number

so if doc 1 is archived, then the records would be

Doc# | Version | Occurrence #
1 | 1 | 1

When a second copy is archived a new record is added:

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2

and then when maybe a 3rd version is archived a 3rd entry added

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
1 | 2 | 2

This is for EACH document and I now need to retrieve the dataset which is the latest version of each document... but I'm drawing a blank!

If I

select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#]

then I get the distinct list of docs, but I now need to use this list to select the records which match this criteria, from this table.

How do I select just these?

I thought (wrongly) that I could simply say:

Select * from Invoice Table where
Invoice.[Doc#], Invoice.[V], Invoice.[Occurrence #] in
(select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#])

View 2 Replies View Related

Pulling Info From The Current Row Of One Dataset Into Another

Sep 6, 2007

This is day 1 of me ever using RS so forgive ignorance of the topic.

Here's the basic scenario:

Input parameter from the user is an Account name.

For that account name I want to list Addresses of locations under the account, and then for each of those, I want to list received shipments from the address.

So Ideally the result is:



Account Name

Address 1


Shipment 1
Shipment 2
Shipment 3

Address 2


Shipment 1
Shipment 2

Address 3


Shipment 1

So, I am able to list the addresses just fine, but the next step is getting the stuff from the shipments table based on the currently running address? I'm probably saying that poorly. What I need to know is how do I structure the dataset/report to accomplish this. I suppose I could combine the query for the shipments with the query for the addresses, but that seems suboptimal to me.

TIA for the help.

View 1 Replies View Related

SQL 2012 :: Query That Gives Info Twice?

Aug 13, 2015

I would like to do a query that would give me the info twice, with just a small difference.

Let's say I have a table with elements A, B and C. Whatever it is, it does not matter.

I would like to get all the data so I just do that:

SELECT A, B, C

Then I would like to add a bit of text, so I'll add:

SELECT A, B, C, 'Text1' as D

The thing is that I would like every single elements (A,B,C) to be repeated twice: Once with Text1, once with Text2.

Let's say my data looks like that:

1 2 3
2 1 3
4 1 2

Then my query would give me this:

1 2 3 Text1
2 1 3 Text1
4 1 2 Text1
1 2 3 Text2
2 1 3 Text2
4 1 2 Text2

(in no particular order)

Is there a way I can do this in just one query?

View 9 Replies View Related

Pulling Data From Xls Thru' Query Analyzer!

May 21, 2004

Hi MAK,

I got this piece of code from one of your articles:

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')

Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?

Thank you!

View 2 Replies View Related

T-SQL (SS2K8) :: Selecting Latest Record With Info

Aug 27, 2014

I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:

-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes

create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)

[code]...

What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.

View 2 Replies View Related

Help With Pulling Data Related To All Items In A Table

Jan 11, 2006

Hey guys,
I have created an asp.net page where users can select multiple items and then submit the form.  I would like to return related items back.  The catch is, I want to only return items that are related to all of the selected items.
I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId)
I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable.
Any help or suggestions would be great.
Thanks,
Matt

View 1 Replies View Related

Pulling Data From Table Which Has Date Field

Dec 18, 2013

I am pulling data from table which has date field to it. I am using below query to get the date

select ArrivalDate As[ARRIVE DATE, , Date, 0], ArrivalTime As[ARRIVE TIME, 4, String, 0] from ArrivalInfo

but i am getting date as 20130925 in format but i want date to be in 09/25/2013 (mm/dd/yyyy)..

View 2 Replies View Related

Pulling Data From Flat File To A Table

Nov 2, 2007

I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file.
Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?

Cheers

View 10 Replies View Related

SQL Server 2012 :: Get Latest Occurrence Of X Month?

Jan 17, 2014

I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.

At the moment I'm using this:

DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')

View 8 Replies View Related

SQL Server 2012 :: Select Row Having Latest Backup Date

Nov 29, 2014

I have a select query that results all the dbs and their several backup dates×. Howover I only need the latest backup dates for each of the db.How can I write that in tsql?

View 5 Replies View Related

SQL Server 2008 :: Query Pulling Too Many Records

Feb 11, 2015

My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.

Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,

[Code] .....

View 9 Replies View Related

Amending Query To Get Info From New Table

Oct 19, 2013

I used to use this code to extract coupon usage from two tables orders and coupons

select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber

[code]....

Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are

ID in Promotions

and

PromotionID in PromotionUsage

Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?

View 5 Replies View Related

Join Two Tables And Only Return The Latest Data For The Child Table

Sep 24, 2007

I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:
select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?
I tried to create a function to get the latest sentence for a chargeID like the following:
select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?
thanks,

View 4 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

SSIS Error While Pulling Data From Oracle Server

Oct 16, 2007



Hi,

I have SSIS package which is created to pull data from oracle server. The package is running fine when executing from BI studio. But failing when i execute as a job. I am getting teh below error message.

Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2007-10-15 16:04:15.60 Code: 0xC020801C Source: Data Flow Task O... The package execution fa... The step failed.


Appreciate if somebody can provide some inputs on this .

View 12 Replies View Related

Pulling Data From MySQL DBMS In France To Sql Server 2005 Here In US

Jan 22, 2008



HI All,
I want to pull data from mysql dbms to sql servr 2005, i have wrote the following codes but it takes more than an hour and half which is not visible. Thus is there any consideration to consider and reduced time it takes. For your information i am going to use SSIS packags, there is no any tranformation, it is direct dump.
Here is the code i am using,


SELECT *
FROM
OPENQUERY (Server_1,'


SELECT
t3.Column11 as Column1,
Column12 as Column2,
Column13 as Column3,
Column14 as Column4,
Column15 as Column5,
Column16 as Column6,
Column17 as Column7,
Column18/1000 as Column8
FROM
table1 t1
INNER JOIN
table2 t2
ON t1.ColumnId = t2.columnID
INNER JOIN
Table3 t3
ON t2.columnId = t3.columnID
WHERE t1.Column4 > Sometime ')


View 11 Replies View Related

SQL Server 2012 :: Update Table Using CTE Or Using Inner Query On Same Table

Jul 29, 2015

To avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario...

UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId

[Code] ...

What is the better approach and should be followed to avoid locks and gain performance or best approach.

View 7 Replies View Related

Is There A System Table With Timestamp Info Or DTS Job Info?

May 7, 2007

I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.

ddave

View 3 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

SQL Server 2012 :: Data Transfer From Staging Table Into Main Table

Jan 14, 2014

What is the best way to transfer data from the staging table into the main table.

Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)

Note: Staging table may have some data same as the main table.

Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)

The problem with the above method is that, it takes a lot of time and log file size grows very big.

View 9 Replies View Related

SQL Server 2008 :: Query To Group And Find Latest

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

Help With An MS SQL Server Query To Return The Latest Dates Against Each RecordID.

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

SQL Server 2008 :: How To Get Latest Records From Table

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

SQL Server 2012 :: Better Way To Query A Hierarchy Table?

Jan 21, 2015

I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.

My question: is there a better way to query that hierarchy table instead of using recursive?

** The current user-defined function that is written using recursive:

CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)

[code].....

** And definition of table 'DepartmentItem' :

DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),

[Code] .....

View 2 Replies View Related

Getting The Latest Data From Different Fields In One SQL - SQL Server 2000

Aug 9, 2006

Hi,

I'm trying to write some SQL which will give me the following result from the table below..

Currently I can do each of the PRICEx fields in a seperate query then join the results together. I do this by getting the MAX(Date) WHERE PRICEx IS NOT NULL then using that date to get PRICEx, and then repeat until I've done all the PRICE fields.

This is acceptable for a small selection, but for a large selection this can get quite slow. I was just wondering if there is there a way to do this in one single SQL without having to join the table to itself 3 times (for all 3 PRICE fields)







Source Table:





ProductID
MarketID
Date
PRICE1
PRICE2
PRICE3

1
2
1/01/2006
2.45
3.4
2.97

1
2
2/01/2006
2.51
3.5
NULL

1
2
3/01/2006
NULL
3.6
NULL

1
2
4/01/2006
NULL
NULL
NULL

Result:



ProductID
MarketID
PRICE1
PRICE2
PRICE3

1
2
2.51
3.6
2.97







Thanks,

Victor.

View 14 Replies View Related

SQL Server 2012 :: Extracting Data From XML Query

Sep 28, 2015

I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.

create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">

[Code] ....

View 6 Replies View Related

SQL Server 2012 :: Update Table Using Variable From Another Query?

Apr 3, 2014

I want to update one table with the value from variable using a set based approach.

so the line

>> select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))

works fine but I want to take @list and use it to update my customers table. here is the full code.

create table customers
(custid int, fee_history varchar(max))
insert into customers
(custid
, fee_history

[code]....

View 5 Replies View Related

SQL Server 2012 :: Dynamic Query On DB Table Names

Mar 9, 2015

I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.

declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: Table Variable In Dynamic Query?

Jul 2, 2015

I have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.

For Eg- @TableVariable

SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '

BUt it gives an error that @TableVariable must be declared

View 8 Replies View Related

SQL Server 2012 :: Use Select Within A Table Field In 2nd Query

Aug 27, 2015

I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)

Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'

What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.

View 6 Replies View Related

SQL Server 2012 :: How To Combine Data From Different Tables Using A Query

Dec 17, 2013

I have a set of tables that look like what I have shown below. How I can achieve the desired output ?

CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);

INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);

[Code] ....

I have a query currently that looks like this . @Month and @Year are supplied as parameters

SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]

[Code] ...

Right now I see an output like this : for a particular value of @Month and @Year

SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :

[Customer],SalesofProductA, SalesofProductB, SalesofProductC

How it can be done ?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved