SQL 2012 :: Select Record With Most Recent Order Date?

Jun 16, 2014

I am working on a query that needs to return the record order number with the most recent requested delivery date.

It seems to work most of the time, but I have found some glitches for some of the items.

example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014

I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014

This query is what I have so far:

SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'

[code]....

WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?

View 9 Replies


ADVERTISEMENT

Select Most Recent Record

Dec 21, 2006

Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.

Normally 1 record is added to the db.
00:00:00
01:00:00
02:00:00
...
21:00:00
22:00:00
23:00:00

All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.

I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01

We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o

This is what we have know. It used to work fine untill we had 2 record being added instead of one.
SELECT TimeOfCapture, Value1, Value2, Value3
FROM FactoryTable
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))

View 1 Replies View Related

Select Most Recent Record??

Apr 27, 2004

Hi,

I want to construct a query that would give the most recent record. Table data looks like -

F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/6/2004 9:31:54

All columns are strings as the data is obtained from text file. Column F3 can have same or different dates. In the above data select 1st record as it is the most recent.
If data is like -
F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/7/2004 19:31:54

Select the 2nd record.

How do I construct the SQL query?


Let me know.

View 1 Replies View Related

Select Most Recent Record By Product + Supplier

Aug 27, 2005

I am trying to pickup the most recent record by Product and Supplier.
eg.

Rec Prod Supp Date
1 1000 WES01 Jan 1/2005
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
The Result I want is as follows:

Rec Prod Supp Date
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
I have tried various methods with the TOP 1 and DISTINCT statements, but have not been able to get the results I am looking for. Help would be appreciated.

View 4 Replies View Related

Select Most Recent Customer Record From Table Only?

Nov 6, 2007

If I have a table structure similar to the following, how might I query it to obtain the Transaction ID, Transaction Date, and Customer Name for the most recent transaction per customer only:


TransactionTable:
TransactionID TransactionDate TransactionType CustomerName
1 10/1/07 1 Bob
2 8/30/07 2 Janet
3 9/17/07 1 Mike
4 9/25/07 1 Bob


The following query will return all records in the table other than Janets...not what I want:
SELECT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC


The following query will return all records in the table other than Janets again, because DISTINCT will use the combo of TransactionID, TransactionDate, and Customer name for uniqueness...not what I want:
SELECT DISTINCT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC

The results set I'm looking for would be the following, where only the most recent entry per customer with TransactionType 1 is returned (i.e. one record for Bob):


TransactionTable:
TransactionID TransactionDate CustomerName
1 10/1/07 Bob
3 9/17/07 Mike


I believe I need an appropriate subquery to yield the results I desire, but can't sort out what it is? I do not want to execute multiple queries but subqueries are fine. Unfortunately there's probably an easy answer that my brain is not currently generating. Any help would be appreciated.


Note, this is not a real table, but a sample to illustrate the concept for the query I need.

Thanks.

View 6 Replies View Related

Find Most Recent Record In Table According To Date Field

Sep 19, 2015

The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.

Code:
SELECT
tblinmate.statusid,
tblinmate.activedate,
Last(tblclassificationhistory.classificationid) AS LastOfclassificationID,
Last(tblsquadhistory.squadid) AS LastOfsquadID,
tblperson.firstname,
tblperson.middlename,
tblperson.lastname,

[Code] ....

The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function

Code:
SELECT a.inmateID,
a.classificationID,
b.max_date
FROM (
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: Find Most Recent Record Of Consecutive Dates

Feb 23, 2015

I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.

I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:

FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123

From this table, I would like a query that would give me this result:

01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123

There will likely be more than just 2 consecutive records per contract number.

View 4 Replies View Related

SQL Server 2012 :: Query Design - Find Most Recent Datetime Record Each Day For A Customer

Apr 2, 2015

So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:

SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)

[Code] ....

View 4 Replies View Related

Select Only Most Recent Date?

Mar 8, 2012

I need this query to pull just the last scan date, i.e. if a scan ran on 3/7/2012 I need just the records (there may be few or many) that pertain to that date and no other dates. When I run this query I am still getting all of the results (i.e. scan dates of 3/7/2012, 3/5/2012, 3/1/2012, etc)

I just need the most recent date, even if there are many records for that date (i.e. 10 IP addresses that were all scanned on 3/5/2012)

Code:
SELECT CPA.ScanName,
CPA.pspplMSSeverity,
CPA.smachIPAddress,

[Code]....

View 3 Replies View Related

How Tot Select The Most Recent Date

Jan 9, 2007

I have 2 tables:
1) Item table (No_, Description)
2) Sales Price HAG table (Item No_, Sales Price HAG, Starting Date)

I want to have a report within from every item the most recent sales price. But, it is possible that a unique item has more than 1 sales price in the 'Sales Price table' (if it has more than one Starting Date). I want to report the sales price with the most recent date.

I've made this statement:
SELECT [DatabaseName$Item].No_, [DatabaseName$Item].Description,
[DatabaseName$Sales Price HAG].[Unit Price]
FROM [DatabaseName$Item] INNER JOIN
[DatabaseName$Sales Price HAG] ON
[DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_]
WHERE ([DatabaseName$Sales Price HAG].[Starting Date] IN
(SELECT MAX([Starting Date]) AS EXPR1
FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1]
GROUP BY [Item No_]))



After running I still get from some items more than 1 price...... What went wrong?

Thx for help!

View 10 Replies View Related

Power Pivot :: Difference In Cost Between Most Recent Date And Second Most Recent Date

Apr 15, 2015

Have a table that list item#, date the standard cost went into effect and the standard cost.  How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.

Item#      EffectiveDate    StdCost

1152        01/01/2009      5.50
1152        09/29/2014      5.81
1152        04/04/2011      5.56

[code]....

View 2 Replies View Related

Select Most Recent By Date Plus Other Criteria

Feb 1, 2015

I have the following query that should return the most recent FormNote entry for a work order where the note begins with "KPI". However if someone decides to a more recent note, it selects that one, even if it doesn't begin with "KPI".

I would like it to return the most recent record that ALSO begins with "KPI". How can I correct this?

Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join

(select ID, WorksOrder,[CreationDate], TextEntry
from
(
select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,*
from FormNotes
)orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where TextEntry like 'KPI%'

Sample results below, see line 5 - this record should not have been selected as there is a record beginning with "KPI" for that work order, but it is dated before this one.

worknumber date_created itemcode Notes
-------------------- ----------------------- -------------------- -----------------------------------------------------------------------------------
HU-DN-004385 2014-07-21 16:15:00 4261 KPI Hyd oil leak repaired
HU-DN-004707 2014-08-06 11:39:00 8005 KPI Valve replaced on day 2.
HU-DN-004889 2014-08-19 15:44:00 9275A KPI Repaired in 2 days - m/c working
HU-DN-004923 2014-08-22 14:23:00 4261 KPI New tracks fitted
HU-DN-005162 2014-09-12 15:04:00 9360A Mechlock key delivered to site - m/c working
HU-DN-005170 2014-09-15 12:07:00 2130A KPI 28.10.14 Metlock fitted

View 5 Replies View Related

SQL Server 2012 :: Joining Table To Itself To Get Most Recent Date As Well As Additional Fields

Mar 11, 2014

I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:

(A = Active, R = Repeat, L = liquidation......)

Here is a sample of the data I am trying to report off of:

CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL
) ON [PRIMARY]

[code]...

I have tried join to the same table - but I am still unable to get it to work.

View 1 Replies View Related

Most Recent Date With Most Recent Table ID

Jul 15, 2014

I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic

SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records
FROM table1
GROUP BY tCustID

View 2 Replies View Related

Joining Record With The Most Recent Record On Second Table

Apr 23, 2008

Could anybody help me with the following scenario:

Table 1 Table2

ID,Date1 ID, Date2

I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.

Example:

Table1 data Table2 Data

ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008


The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1

Any help would be greatly appreciated.
Thanks

View 4 Replies View Related

Getting Most Recent Order

Jan 15, 2007

I have a Customers table and an Orders table. The Customers table has fields... CustomerId, and CustomerName. The Orders table has Fields CustomerId, OrderDate, and ProductId.

I wish to execute a query that will return a single Customer and Order record for a given customer ID that reflects the most recent order. So, a typical join like...


SELECT c.customerId, c.customerName, o.orderdate,o.productId

FROM Customers C LEFT OUTER JOIN Orders o ON (o.customerId=c.customerId)

WHERE c.customerId=SOMEVALUE

would return all the orders for a given customer but I only want the most recent order. Can anyone help?

Thanks.

View 9 Replies View Related

SQL 2012 :: Load Files In Date Order In SSIS

Apr 10, 2014

I have files which has date in file name and I want to load all files in sequential order.

Here I am giving you an example of my file name.

"NetworkActivity_869_403722_01-01-2014.log"
"NetworkActivity_869_403722_01-02-2014.log"
"NetworkActivity_869_403722_01-03-2014.log"
"NetworkActivity_869_403722_01-04-2014.log"
"NetworkActivity_869_403722_01-05-2014.log"

These are my files in that I want to load files sequentially means jan 1st, 2nd like this way.

View 5 Replies View Related

Select Date Record

Feb 18, 2006

    how to select record from the table where the data between a  range. example  between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks

View 2 Replies View Related

Select Record Based On Max Date

Sep 12, 2012

I have the following table:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind

I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

So i would want the following from above:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind

I'm having trouble with the syntax need to accomplish this.

View 2 Replies View Related

Select Record With Oldest Date

Mar 4, 2008

Hi,

I have a base query that will return the ID, StartDate and Code for all IDs. I SELECT only for Codes 5 and 9. For most of the IDs I return a record for both a Code 5 and Code 9. They will have different dates however. How could I select from this base query one record for each ID with the oldest date? The items in yellow are the ones that I would want to return to a report in SSRS. Is there a way to put this data in a temp table and read through it to compare IDs and grab the one with the older date?










ID
StartDate
Code

100
1/2/2000
5

100
4/6/2004
9

205
3/13/2002
5

205
9/10/2002
9

300
10/10/1999
9

407
2/12/2005
5

407
7/17/2007
9


Thanks,

rb

View 10 Replies View Related

Getting Most Recent Record

Sep 4, 2007

I am trying to think of the easiest solution to do this.

What it is, is that I am trying to be alerted, if an account has not had its daily reports ran for x days (for example, 3 days).

I have two tables.

One is a table, with a list of AccountIDs that are to have the report done.

Another is a Log Table, which a record is inserted whenever a report has run. So, if a report has not run, it will not have a record in that table.

Here is the table setup with sample data. I am only going into basics, as the tables are used for a couple other things which are not needed for this.


Code:


Table: Reports_AccountID
ReportID | AccountID
--------------------------
5 | 1234
5 | 987
4 | 1234
3 | 1234
5 | 5677

Log: Reports_Log
LogID | ReportID | AccountID | ReportRunDate
----------------------------------------
9876 | 5 | 1234 | 9/4/2007 10:35:43 AM
9875 | 5 | 987 | 9/4/2007 10:35:43 AM
9874 | 4 | 1234 | 9/4/2007 9:05:43 AM
9873 | 3 | 1234 | 9/4/2007 9:30:17 AM
9872 | 5 | 1234 | 9/3/2007 10:35:43 AM
9871 | 5 | 987 | 9/3/2007 10:35:43 AM
9870 | 4 | 1234 | 9/3/2007 9:05:43 AM
9869 | 3 | 1234 | 9/3/2007 9:30:17 AM
9868 | 5 | 1234 | 9/2/2007 10:35:43 AM
9867 | 5 | 987 | 9/2/2007 10:35:43 AM
9866 | 5 | 5677 | 9/2/2007 10:35:43 AM
9865 | 4 | 1234 | 9/2/2007 9:05:43 AM
9864 | 3 | 1234 | 9/2/2007 9:30:17 AM



so if i wanted to know what report for reportID 5 hasn't run in the past 2 days, it would be accountID of 5677 (last ran on 9/2/2007 10:35:43 AM)

I am not sure where to even start. I am thinking I can grab the latest report ran for all Accounts in the Reports_AccountID table (create temp table and insert most recent log record into that table), and do a date difference between the date and current datetime and select based on datedifference > 2 ?

or would the best way is to do an inner join between the 2 tables for all reports ran in the past 2 days, and then whatever account is not listed in that query, is a report that has not run (do a subquery?)

just trying to think of the best way to do this. any tips/advice would be appreciated

View 6 Replies View Related

Most Recent Record

Nov 13, 2007

Please concider the line of code below. it doesnt quite work how i want it to. i need it to only pull records where there isnt any activity completed (actualend) after a given period. and only show the most recent activity per regardingobjecid(sales person) however it returns all the records before that period and shows me the most recent one up to that time. Please help.

here is the code


SELECT new_ratingname, regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename, count(*) AS Total
FROM (SELECT filteredcontact.new_ratingname, filteredactivitypointer.regardingobjectidname, filteredactivitypointer.actualend,
filteredactivitypointer.Owneridname, filteredactivitypointer.createdbyname, filteredactivitypointer.activitytypecodename, row_number()
OVER (Partition BY regardingobjectid
ORDER BY actualend DESC) AS recid
FROM FilteredActivityPointer INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid
WHERE new_ratingname NOT IN ('dead', 'archive', 'continous updates') AND filteredactivitypointer.statecodename = 'completed' AND
filteredactivitypointer.owneridname IN (@user) AND filteredactivitypointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper ') AND
filteredactivitypointer.activitytypecodename IN ('phone call', 'e-mail', 'fax') AND filteredactivitypointer.actualend <= dateadd(d,
- CAST(@NeglectedDays AS INT), GetUTCDate())) AS d
WHERE recid = 1
GROUP BY d .actualend, d .regardingobjectidname, d .owneridname, d .createdbyname, d .activitytypecodename, new_ratingname
ORDER BY d .actualend



Compnetsyslc

View 4 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

Can I Easily SELECT A Date As October 12, 2012 (instead Of Oct 12 2012)?

Jan 29, 2008

 Presently using CONVERT(VARCHAR(11), [ExpiryDate], 100) to get close to the correct format.Only stumbled across this by accident so am assuming there might be better hidden 'treasures' .Formatting dates seems to be my biggest time-consuming activity and I just don't seem to get better at it! 

View 4 Replies View Related

SQL 2012 :: Select Top 100 - A Record Is Not Returning From Database

Aug 24, 2015

I have a strange situation with an select. I've noticed that when I select top 100, a record is not returning from the database, but when doing top 101 the record appears on position 41.

The query is like this:

select top 100 GroupId, count(HouseId)
from House h
group by h.GroupId
order by max([DateCreated]) desc

From all discussions about top 100 vs top 101 I've noticed that everybody is saying that top 101 is using another algorithm and we can have a speed problem, but my problem is not about this. With top 100 I'm missing a record that should appear at index 41.

View 9 Replies View Related

Select Null Value Date Fields Record

Sep 28, 1999

Hi:

I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.

-Nicole-

View 2 Replies View Related

Transact SQL :: Select First And Last Record For Certain Date Range

Jun 16, 2015

I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:

DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '05-1-2015'
Set @EndDate = '05-31-2015'
SELECT
a.AgentName,
R.BALANCEBEFORE,

[Code] ....

AGENTNAME          BALANCE BEFORE  BALANCE AFTER          DATE
DOUGLAS              9738.75                9782.75                     2015-05-11
DOUGLAS              9782.75                9804.75                     2015-05-12
DOUGLAS              9804.75                9837.75                     2015-05-13

In the sample data above, ideally I would like my query to return data as follow:

AGENTNAME          BALANCE BEFORE                  BALANCE AFTER
DOUGLAS              9738.75 (from first activity)    9837.75 (from last activity)

Not sure how I can write sql query to accomplish this.

View 7 Replies View Related

How Do I Select The Maximum Date For Each Record Having Duplicate ID

Dec 19, 2007



Hi All,
Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.

Here is My source table Name: Source_table,


CREATE TABLE dbo.Source_table

(

AccountID INT PRIMARY KEY,

Price int,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

Flag_Status Int Default 1

)

GO

Here is the Fact_table


CREATE TABLE dbo.Fact_table

(

Fact_table_Key Int Identity (1, 1) NOT NULL,

AccountID INT,

Price INT,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

editor VARCHAR(64),

Flag_Status Int Default 1,

Inserted_Date DATETIME DEFAULT GETDATE(),

Updated_Date DATETIME DEFAULT GETDATE()

)

GO

Source_table:

AccountID Price Address added edited Flag_Status
---------------- --------- ------------- ----------- ------------- ------------------
1 10 xyz 01-2006 01-2006 1
2 14 abc 02-2006 02-2006 1
3 13 mno 03-2006 03-2006 1

Here is the fact table, table name= Fact_table



Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date
-------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ -------------------
1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL
2 2 14 abc 02-2006 02-2006 1 05-2006 NULL
3 3 13 mno 03-2006 03-2006 1 05-2006 NULL
4 1 17 ght 01-2006 06-2006 1 NULL 08-2006
5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006
6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006
7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006
8 2 19 klm 02-2006 01-2007 1 NULL 02-2007
9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007

Here is what i am thinking: But it gives me Wrong Flag_Status.


UPDATE Fact_table

SET Flag_Status =


CASE


WHEN (SELECT Max(Updated_Date) From Fact_table

WHERE AccountID IN (SELECT AccountID FROM Fact_table

Group By AccountID Having Count(AccountID)>1)) >

(SELECT Max(edited) From Source_table

WHERE Flag_Status = 1)

THEN 1


WHEN (SELECT AccountID From Source_table

Group By AccountID

Having Count(AccountID) =1) =

(SELECT AccountID From Fact_table

WHERE Updated_Date IS NULL)

THEN 1

ELSE 0

END

View 12 Replies View Related

Query Getting Most Recent Record

Jun 16, 2015

Here's the scenario. Consider the following sample data.

CREATE TABLE #MyTest
(
CustomerNumber INT,
Division CHAR,
SalesRepType INT,
SalesRepNumber INT,
EnterDate DATE

[code]....

Essentially, what I’m attempting to do is for each Customer, Division, SalesRepType determine who the most recent assigned SalesRepNumber is and when (EnterDate) that person was assigned. So using the sample data, I would expect the following results.

CustomerNumberDivisionSalesRepTypeSalesRepNumberAssignedDate
10000A11002/1/2015
10000A23001/28/2015
10000B14002/1/2015
10000B26002/2/2015

I’ve tried various ways of using a CTE and ROW_NUMBER trying to get at this, but the area that is giving me the problem is in Division A, SalesRepType 1. Here is what gets me close, but I’m picking on SalesRepNumber 200 instead of 100 for Division A and

SalesRepType 1.
WITH
cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate)
AS
(
SELECT

[code]....

View 4 Replies View Related

How To Get Recent Record In Duplicates

Feb 12, 2014

by executing this qry i will get below result ,in that TId is duplicates that is from that i want recent record with same result.i want last one if TId comes duplicate record. any one ans pl

select sa.GrandTotal, sa.TId, sa.Id,sa.Date, pr.PName,
pr.PCode from Sales sa
Left Outer Join Product pr on sa.Pid = pr.Id where sa.GrandTotal is not null

GrandTotalTIdIdDate PNamePCode
200 122014-01-30 18:46:55.000RK100
560 252014-01-30 18:47:49.000RK100
420 262014-01-30 19:55:11.000RK100

View 1 Replies View Related

Selecting Most Recent Record

Jul 20, 2005

MSSQL2000I have a table that contains customer transactionsCustomerIDTransactionTransactionDate....I need to select the most recent record that matches a specific CustomerID.I am fairly new to SQL, could someone provide a sample select statement.TIATim Morrison-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com

View 3 Replies View Related

Reporting Services :: Expression To Return Date Value Based On Most Recent Date Only

Oct 7, 2015

How do you Write an expression to Return the date value based on the most recent date only?

Eg.  Date 10-7-2015,  action - done, notes - all items fixed.
       Date 4-5-2016, action - not yet done, notes - buying parts

All these dates are returned based on a search parameter based on a project number.  I only want the most current date and associated fields displayed.

View 4 Replies View Related

Use A Date To Select Out Previous 14 Days Record From The Table

Dec 10, 2007

May I know how to use a "date" to select out previous 14 days record from the table? and find the duplicated records?


-- sort out duplicate order from tblOrder

Select * FROM tblOrder

WHERE DDay > @prmDDay("day", -14, getDate())

Group by DDay



Many thanks~~~~~
Fr New Learner

View 3 Replies View Related







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