Using MS SQL 2005, how can I SELECT only the records whos date field is equal to the most recent date held in that field?
For example:
date item colour
---- ---- ------
12/15/2007 shirt red
12/13/2007 shirt black
12/15/2007 shirt blue
result(s) expected:
12/15/2007 shirt red
12/15/2007 shirt blue
I need to select the most recent price for an item from this table where certain criteria are met:
flexing_stock_transactions
--------------------------
item date price status week firm_or_commission
---- -------- ----- ----- ---- ------------------
CH 10/05/07 200 2 35 F
AL 10/05/07 195 1 35 C
CH 10/05/07 209 1 35 F
CH 11/05/07 210 1 35 C
I am currently using:
SELECT
price, date
FROM flexing_stock_transactions
WHERE (
[date] = (SELECT MAX([date]) FROM flexing_stock_transactions)
AND item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
)
zero rows are returned wheras I was expecting:
209, 10/05/07
I'm using MS Server 2005
I want to display some horse racing results I have on a page but I only want to show the results for the last 7 days.
I have fields for date, time of race, horses name and result.
How can I just get records within the last 7 days to display?
I'm new to this SQL malarky so please be gentle!
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]....
Good Morning,I have a view that contains rate information, contractIDs, and effectivedates.I need to select the rate info based on contractID and date.I can provide a date and contractID, and I need to select the rate info forthat contract where the effective date is <= dateprovided.I need the 1 record that is closest to that date.I am thinking something with max() perhaps. Any ideas? The <= effectivedate will return several rows, I just need the one closest to the date Iprovide.Thanks for any advice,CK
View 2 Replies View RelatedI have a table that has a DateTime column which uses a DataTimedatatype. How do I retrieve a range of records based on the month andyear using ms sql?Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedSo let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate). I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.
I can't just do something like:
SELECT *
FROM Orders
WHERE ReceiptDate >= add_months(date,-6)
because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.
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
Hi guys,
I am having a problem in outputting the last two dates that a bill has been sent out.
Background:
A customers recieves a bill 4 times a year. The dates for issuing these bills are not fixed and I dont know the date that bills are usually sent out.
The database that I am using stores all of the dates that a bill would have been sent out.
Issue:
A customer recently requested to see the dates of last 2 bills along with their value.
I know that you can view a customer last date by using the following statement.
Select max(issuedate)LastBill
from statements
but i don't know how i could output the last 2 bills that were issued
Any ideas....??
Velvet.
I know that in order to select the
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 RelatedI know how to select the most recent row from a database:
SELECT TOP (1) Location, Date FROM Images ORDER BY Date DESC
But how do I select the second to most recent? or the third most recent? or the 4th, ect, ect, ect.
There must be some method to it, anyone have any suggestions?
I have a DB with the fields: Id, Date, Name, Status.
Now I want to select all distinct names with the most recent corresponding status.
With the SELECT DISTINCT Name, I get only one record per different name which is ok.
Now I want to add next to each different name, the most recent added status.
Can I combine those 2 in 1 query? I want to show them using PHP on a website.
Now I get a table with a row for each different name, but I can't add a second column where the most recent Status is showed for that name.
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).
actions table (columns: action_id, request_id, action_type, action_time, action_reason)
I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.
My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria
SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time
So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)
Hopefully this has made sense so far.
So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
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.
i have a datetime field in the post tables.
I would like to get the records within the latest 7 days.
Are there any functions for doing something like this?
my current query is something like
select * from post where creation_time ....
Thank you
Ok, I was able to find the most recent Effective date and it works well. The only problem I am having is with the CarrierName. See there are different Carriers with so then it makes us have Mult effective dates. I need to pull the most recent effective date with carrier. So, I though to max the pcsiNumber2, but that didn’t work. Do you have any other ideas? Let me know.
Select Distinct
--PCSNumber,
PCSINumber1,
max(PCSINumber2) as mostrecentPCS,
LastName,
FirstName,
State,
--InsuranceCarrier,
CarrierName,
MAX(CoverageEffectiveDate)as MostRecentEff,
MAX(TerminationDate) as MostRecentTerm,
AmtPerClaim,
Aggreg
FROM VW_Medical_Malpractice_Project
Where
State = 'PA'
Group By PCSINumber1, LastName,FirstName,State,CarrierName,AmtPerClaim,Aggreg
Order By PCSINumber1
Example
000043010003MUSSER WILLIAM American Professional Agency2002-06-01
000043010002MUSSERWILLIAM Columbia Casualty2000-02-20
000043010001MUSSERWILLIAMMCIC Vermont Inc.1998-07-01
000043010004MUSSERWILLIAMProMutual Insurance Co.2005-05-16
000043010005MUSSERWILLIAMTri-Century Insurance Company2007-01-01
So, what I need is the most recent effective date which would be 2007-01-01, which I have but what is not letting me do this is the names, the 5ith column. I was trying to use the 2nd column to use the max , but it is not working. What am I doing wrong. I need the whole last row, but that number 0005 can change because there may only be 2 dup records.
If I have tables like Customer (Id, First, Last, Address, etc.), CustomerEvent (EventCode, EventDate, CustomerId), EventTypes(Code, Description) how would I get all of the CustomerIds where the latest CustomerEvent record is a cancel and it happened within the last 60 days?
If I just do a select max(EventDate) and put EventTypes.Code='cancel' in the where clause it picks up records where the customer has done other things since the cancel.
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
[code]....
Hey guys, I have a table full of data that has duplicate records except for two date columns (date1 and date2). What I would like to do is remove the duplicates while retaining the most recent record, how can I do this?
So record 1 looks like this:
Code:
John | Smith | 08/08/2000 | 10/10/2000
Record 2 looks like this:
Code:
John | Smith | 08/10/2005 | 10/10/2005
I'd like to remove the first instance and keep the second (most recent one).
Ideas?
Thanks!
Sorry if I am posting in the wrong place here. I am not a programmer by profession but just muddle through building some reports for my job. The report I am working on at the moment is the most complicated one I have worked on so far. I am working with MS Query (SQL) and trying to limit the rows returned with the most recent date. I have searched online for the answer to this but just cannot seem to get it to work.
There can be more than one Production Name per Manuscript Number. Each Production Name has a Production Task Assignment Start Date. I want to display one row per record with the latest (most recent) Production Task Assignment Start Date. I have been trying to use the MAX functionality (which I have seen listed online) but just cannot get it work. Can anyone help? It would be much appreciated!
The code looks like this:
SELECT SCHEDULE_GROUPS.`Schedule Group Description` AS 'AE', ROLEAUTH_DOC_PEOPLE_ADDR.`Article Type`, ROLEAUTH_DOC_PEOPLE_ADDR.`Manuscript Number` AS 'MS #', DOCUMENT.`Production Notes` AS 'Short title', ROLEAUTH_DOC_PEOPLE_ADDR.`First Author Last Name` AS 'First Author', PRODUCTION_TASKS.`Production Task Name` AS 'Name', "Production Task Assignment Start Date"-"ROLEAUTH_DOC_PEOPLE_ADDR.Final Decision Date" AS 'Days Since', ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Last Name of the Editor who made First Decision`, ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Decision Term`, ROLEPROD.`Production Task Assignment Start Date`
FROM DOCUMENT.tab DOCUMENT, PRODUCTION_TASKS.tab PRODUCTION_TASKS, ROLEAUTH_DOC_PEOPLE_ADDR.tab ROLEAUTH_DOC_PEOPLE_ADDR, ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.tab ROLEEDIT_DOC_PEOPLE_ADDR_AUTH, ROLEPROD.tab ROLEPROD, SCHEDULE_GROUPS.tab SCHEDULE_GROUPS, SCHEDULE_GROUPS_TOC.tab SCHEDULE_GROUPS_TOC
WHERE PRODUCTION_TASKS.`Unique Idenitifer` = ROLEPROD.`Production Task ID` AND ROLEPROD.`Document ID` = ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = SCHEDULE_GROUPS_TOC.`Document ID` AND SCHEDULE_GROUPS_TOC.`Schedule Group ID` = SCHEDULE_GROUPS.`Unique Identifier` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = DOCUMENT.`Unique Document ID` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Unique Document ID` AND ((SCHEDULE_GROUPS.`Schedule Group Description` Not Like '%2008' And SCHEDULE_GROUPS.`Schedule Group Description` Not In ('Accepted (With SC for assigning)') And SCHEDULE_GROUPS.`Schedule Group Description` Not In ('Comments')) AND (ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Decision Term`='Accept'))ORDER BY ROLEAUTH_DOC_PEOPLE_ADDR.`Manuscript Number`
Once the records are retrived I am returning the data to Excel.
Thanks for your time.
Natalie
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]....
There are several day_timestamp for each index_id.Anyone can help me to write a sql to generate the most recentday_timestamp of index_ids which has not accessed into the system in90 days from today's date.So, I need to get the most recent date and time for each index_id in90 days from today's date.Sample data:Index_idday_timestamp2 2001-04-11 21-29-312 2002-05-21 21-29-312 2003-06-11 21-29-3122004-11-21 21-29-312 2004-09-21 21-29-315 2000-04-21 21-29-315 2003-05-21 21-29-315 2003-06-21 21-29-3152004-09-11 21-29-318 2000-08-11 21-29-3182004-04-01 21-29-3182004-09-21 21-29-3182004-09-23 21-29-31102001-04-11 21-29-31102002-04-21 21-29-31102003-08-11 21-29-31102004-10-21 21-29-31102004-09-21 21-29-31The output will be as below:2 2004-11-21 21-29-3152004-09-11 21-29-3182004-09-23 21-29-31102004-10-21 21-29-31
View 6 Replies View RelatedI 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!
I've got a table like so
date | number___________|_____________
2007-01-01 | 432007-01-02 | 652007-01-03 | 232007-01-04 | 652007-01-05 | 232007-01-06 | 11
2007-01-07 | 52
2007-01-08 | 83
2007-01-09 | 44
and I want to get the most recent date in the past which I must go back where the sum of the numbers of the number column if greater than a certain number.
EG. From the above data, the date which I must go back to get a sum of 195 is 2007-01-05
as 44+83+52+11+23 is the least amount that is greater than 195.
Can anyone help, is this even possible? I'm flummoxed!
The database consists of the following tables:
create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
Possible result: [URL] ......
Hi,
I have the following table:
Table name Employee
===============
emp_id,
emp_name,
emp_city
Table name EmpStatusReport
===================
emp_id,
action
date
I need to write a sql query to get the emp_name, emp_city and the recent date when the user has sent status report over last 30 days. The user has sent a status report if action field in the empStatusReport is set to 'reported'. This table gets filled everytime user sends report.
I tried to the do the following:
select e.emp_name, e.emp_city, esr.date from employee e, EmpStatusReport esr where e.emp_id and esr.emp_id and
/* esr.date = max(esr.date) and esr.date > currentDate - 30 and esr <= currentDate */
I am not able to write a correct login for date part. Any help in this will be highly appreciated.
Thanks!
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
Hi,
Not sure if you could help or not, but I need to pull the most recent effective date for this report I am trying to run, but I am getting know where. If someone can take a look at this, it would be great.
Example….
pcs number 00004344 effective dates 5/1/2006 and 5/1/2007. I need it to be the most recent effective date which would be, 5/1/2007 date.
Can someone help me?
USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES] Script Date: 03/10/2008 09:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES]
AS
BEGIN
SET NOCOUNT ON
DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME
SET @STARTTIME = GetDate()
PRINT 'SP started on: ' + CAST(@StartTIME as varchar)
PRINT ''
DECLARE @PKey varchar(16), @pcsi_id1 varchar(8), @pcsi_id2 varchar(4) /**@pcsi_id3 varchar(4),@Lpcsi_id3 varchar(4)**/
DECLARE @LplID varchar(12), @LTrm Datetime, @Eff Datetime, @Trm Datetime, @Gap int, @Corrected bit
DECLARE @CTrm DATETIME, @i varchar(8), @LastID varchar(8), @LEff Datetime, @FinalEff DATETIME
SET @i = 0
IF OBJECT_ID('tempdb..#pcsiData') IS NOT NULL
DROP TABLE #pcsiData
IF OBJECT_ID('tempdb..#HoldKey') IS NOT NULL
DROP TABLE #HoldKey
IF OBJECT_ID('tempdb..#HoldKey2') IS NOT NULL
DROP TABLE #HoldKey2
SET DATEFORMAT mdy;
SELECT pcsi_id1 + pcsi_id2 AS pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO #pcsiData FROM pcsi p
WHERE (SELECT COUNT(pcsi_id1 + pcsi_id2) FROM pcsi WHERE pcsi_id1 = p.pcsi_id1) > 1 --AND p.pcsi_prd = 'dgh'
ORDER BY p.pcsi_id1 + p.pcsi_id2 ASC, p.pcsi_eff1 ASC
--SET TRM DATES TO NULL WHERE DATE IS 1977-03-23 00:00:00.000
--(IMPACT XSQL process uses that date in place of null!)
UPDATE #pcsiData
SET pcsi_trm1 = null
WHERE pcsi_trm1 = '1977-03-23 00:00:00.000'
SELECT pcsi_id1 + pcsi_id2 as Pkey, (COUNT(pcsi_id1 + pcsi_id2)) AS DupCount --pcsi_eff1, pcsi_trm1, COUNT(pcsi_id1 + pcsi_id2) AS DupCount
INTO #holdkey
FROM #pcsiData
GROUP BY pcsi_id1 + pcsi_id2
HAVING count(*) = 1
IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[NonDuppcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE NonDuppcsiDataForMalPracticeReport
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO NonDuppcsiDataForMalPracticeReport
FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN(SELECT pkey from #Holdkey)
DELETE FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN (SELECT pkey from #HoldKey)
DROP TABLE #HoldKey
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1, count(*) as NoofDup
INTO #HoldKey2
FROM #pcsiData
GROUP BY pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
HAVING count(*) > 1
SET NOCOUNT OFF
DELETE #pcsiData
FROM #pcsiData, #holdkey2
WHERE #pcsiData.pcsi_id1 = #holdkey2.pcsi_id1
AND #pcsiData.pcsi_id2 = #holdkey2.pcsi_id2
drop table #holdkey2
SET NOCOUNT ON
IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[pcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE pcsiDataForMalPracticeReport
--CREATE TABLE pcsiDataForMalPracticeReport (pcsi_pkey varchar(16) PRIMARY KEY, pcsi_id1 varchar(8), pcsi_id2 varchar(4), pcsi_id3 varchar(4), pcsi_eff1 varchar(8), pcsi_trm1 varchar(8), Corrected bit)
SELECT pcsi_id1 + pcsi_id2 as pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO pcsiDataForMalPracticeReport
FROM #pcsidata p
WHERE pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM #pcsidata WHERE pcsi_id1 = p.pcsi_id1 AND pcsi_id2 = p.pcsi_id2)
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM #pcsiData
--group by pcsi_pkey
Order By pcsi_id1 + pcsi_id2, pcsi_eff1 ASC
OPEN Cur
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = 2
DELETE FROM tmppcsiDatesWithGaps --Clear table used for debugging
WHILE @@FETCH_STATUS = 0
BEGIN --Begin While Loop
IF @pcsi_id1 + @pcsi_id2 = @lplID
BEGIN --If current record is for the same provider location as the last then...
SET @Gap = DATEDIFF(day, @Ltrm, @Eff)
IF @Gap > 2
BEGIN --If there is a gap greater than 1 day...
PRINT ''
PRINT 'GAP between fetch ' + str(@i - 1) + ' and ' + str(@i) + ' (' + @pcsi_id1 + ' ' + @pcsi_id2 + ' ' + '): ' + str(@gap) + ' days! '
PRINT 'Last Trm: ' + CAST(@LTrm AS varchar) + ' Eff: ' + CAST(@eff AS Varchar)
PRINT ''
--IF EXISTS (SELECT * FROM pcsiDataForMalPracticeReport WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2)
--IF @pcsi_id1 + @pcsi_id2 NOT IN (SELECT pcsi_id1 + pcsi_id2 FROM tmppcsiDatesWithGaps)
--BEGIN --Begin if effective date was not already updated
--IF @Leff > @Eff
UPDATE pcsiDataForMalPracticeReport
SET pcsi_eff1 = @Eff-- pcsi_Ltrm = @LTrm
WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_id3 = @lpcsi_id3, pcsi_eff1 = @LEff-- pcsi_Ltrm = @LTrm
--WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--INSERT INTO pcsiDataForMalPracticeReport (pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_id3, pcsi_eff1, pcsi_trm1)
--VALUES (@pcsi_id1 + @pcsi_id2 + @pcsi_id3, @pcsi_id1, @pcsi_id2, @lpcsi_id3, @LEff, @Ltrm)
INSERT INTO tmppcsiDatesWithGaps (pcsi_id1, pcsi_id2, lpcsiid, EffectiveDate) VALUES (@pcsi_id1, @pcsi_id2, @lplid, @Eff)
--END --End if effective date was not already updated
END --End if there is a gap greater than 1 day
END --End if the provider location is different than the last row
--Set current rows data in last rows variables...
SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm
--Get next row of data
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = @i + 1 --increment i
PRINT 'Iteration #' + str(@i) + ' -- ' + @pkey + ' Eff: ' + Cast(@Eff as varchar) + ' ' + ' Trm: ' + cast(@trm as varchar)
END --End While Loop
INSERT INTO pcsiDataForMalPracticeReport
SELECT distinct pcsi_id1 + pcsi_id2, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM NonDuppcsiDataForMalPracticeReport
/***
UPDATE #pcsidata
SET pcsi_trm1 = '20470101'
WHERE pcsi_trm1 is null OR pcsi_trm1 = ''
/**
SELECT p.pcsi_id1, p.pcsi_id2, MAX(p.pcsi_trm1)
INTO #HoldKey2
FROM #pcsidata p
group by p.pcsi_id1, p.pcsi_id2
ORDER BY p.pcsi_id1
UPDATE R SET pcsi_trm1 = I.pcsi_trm1 FROM pcsiDataForMalPracticeReport R
INNER JOIN #HoldKey I
ON r.pcsi_id1 = I.pcsi_id1
AND r.pcsi_id2 = I.pcsi_id2
**/
Print ''
Print ''
Print ''
Print 'SETTING MAX TERM VALUES NOW....(This may take a while)'
Print ''
Print ''
UPDATE pcsiDataForMalPracticeReport
set pcsi_trm1 = jp.MaxTrm
FROM pcsi p JOIN (SELECT pcsi_id1, pcsi_id2, MAX(pcsi_trm1) as maxtrm FROM pcsi p2
--WHERE p2.pcsi_id1 + p2.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2
GROUP BY p2.pcsi_id1, p2.pcsi_id2) jp ON (jp.pcsi_id1 + jp.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2)
DECLARE @NotTermed int
SET @NotTermed = (SELECT COUNT(*) FROM pcsiDataForMalPracticeReport WHERE pcsi_trm1 = '20470101')
PRINT''
PRINT 'Total non-duplicate records not termed: ' + str(@NotTermed)
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_trm1 = NULL
--WHERE pcsi_trm1 = '20470101'
**/
UPDATE pcsiDataForMalPracticeReport
SET pcsi_trm1 = NULL
PRINT ''
PRINT 'STEP TWO.......................'
PRINT 'Preparing the table names...tmpMalPracticeEffectiveDates'
PRINT''
--This step updates tmpMalPracticeEffectiveDates with the desired effective date and most recent termination date
--if there are no current records with a termination date = NULL
TRUNCATE TABLE tmpMalPracticeEffectiveDates
SET NOCOUNT OFF
PRINT 'Inserting new data into tmpMalPracticeEffectiveDates'
INSERT INTO tmpMalPracticeEffectiveDates
(pcsi_id1, pcsi_id2, pcsi_eff1)
SELECT DISTINCT pcsi_id1, pcsi_id2, pcsi_eff1
FROM pcsi p
WHERE p.pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM pcsi p2
WHERE p2.pcsi_id1 = p.pcsi_id1 AND p2.pcsi_id2 = p.pcsi_id2)
ORDER BY pcsi_id1, pcsi_id2
------------------------------
--Set temp bogus date to distinguis which records are current in
--subsequent statement
PRINT 'Setting bogus date to distinguish pcsi records that are not termed'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = '12/21/2049'
WHERE '03/23/1977' IN (SELECT pcsi_trm1 FROM pcsi p WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1 AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
-------------------------------
PRINT 'Setting most recent term date for pcsi records that are not currently active'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = (SELECT MAX(pcsi_trm1) FROM pcsi p
WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1
AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
WHERE tmpMalPracticeEffectiveDates.pcsi_trm1 is NULL
-------------------------------
PRINT 'Setting bogus dates back to NULL'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = NULL
WHERE pcsi_trm1 = '12/21/2049'
-------------------------------
--CORRECT EFFECTIVE DATES WITH GAPS...
PRINT 'Correcting Effective Dates for those records with gaps in credentialing records'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_eff1 = t.EffectiveDate
FROM tmppcsiDatesWithGaps t
WHERE tmpMalPracticeEffectiveDates.pcsi_id1 = t.pcsi_id1
AND tmpMalPracticeEffectiveDates.pcsi_id2 = t.pcsi_id2
----END OF SP---
DECLARE @Diff decimal
SET @ENDTIME = getdate()
PRINT ''
PRINT ''
DECLARE @GapCount int
SET @GapCount = (SELECT COUNT(*) FROM tmppcsiDatesWithGaps)
PRINT 'Total number of non-distinct provider locations: ' + Str(@i) + '.'
PRINT 'Total number of gaps found: ' + Str(@GapCount) + '.'
PRINT 'FINISHED ON: ' + cast(@ENDTIME as varchar)
SET @Diff = CAST(DATEDIFF(second, @StartTime, @EndTIME) AS varchar)
PRINT ''
PRINT 'Time elapsed: ' + str(@Diff) + ' seconds.'
PRINT ' = ' + str(@Diff/60) + ' Minutes!'
END
Hi I have a issue here where i have to store a record from a flat file into Sql Server Table.
The criteria is that there are records in flatfile having same business key but different transaction dates.
I have to place only htose records into the table which are having recent or greater transaction date .
example
name age date
xyx 34 00000000
xyx 34 20071103
xyx 34 20071031
key age and Name
ihave to pick only one record with greater o recent date from 3.
What approach should i take.
Some of the Business keys conssit of 2 or 3 columns .
I have to insert or store only the recent Date consisting records. how do i acheive this please help me.
I'm trying to make a parameter in SSRS that defaults to most recent date with sales. Below gives me all dates with sales. How can I get the results to give me just the most recent? I've tried MAX and LastChild in a couple different ways but no luck.
WITH
MEMBER [Measures].[ParameterCaption] AS [Time].[Date].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Time].[Date].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Time].[Date].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
[Code] ....
Hello,
I've a table which contains the nest following data:
Date Sales Price
01-01-07 5,00
31-03-07 6,00
16-04-07 5.75
26-04-07 6.25
For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.
How can I get it?
Thx!
This is my query, from my nonono table I need to return the most recent date from attempted (column name) How could I modify this to do such
Select *
FROM [nonono] td
WHERE (NOT EXISTS
(SELECT [First], [second]
FROM [yesyesyes] AS d
WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
This is my table:
ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/12/14 451
1 Nestle NS Foods Products 01/17/14 495
2 Oracle OR Software Info 01/24/14 279
2 Oracle OR Soft & IT Info 01/26/14 310
2 Oracle ORL Software Info 01/25/14 219
2 Oracle ORL Soft IT 01/28/14 600
MonthlyAmount Funded AppCategory Research
37.5623 Yes NE NA
41.2365 No N NA
23.2568 Yes OR InProgress
25.8333 Yes ORL NA
18.2189 Yes SOF Approved
50.0000 No IT RejectedExpected Output:
ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/17/14 946
2 Oracle OR Soft & IT Info 01/26/14 589
2 Oracle ORL Soft IT 01/28/14 819
MonthlyAmount Funded AppCategory Research
78.7988 No N NA
49.0901 Yes ORL NA
68.2189 No IT Rejected
I want to pick the recent modified date for DepCode and sum Yearly and Monthly Amount. I have tried this query and not able to get the output. This is the single table.
select B1.[ID], B1.[AppName], B1.[DepCode], B1.[DepName], B1.[Group],
B2.ModifiedDate, B2.YearlyAmount, B2.MonthlyAmount,
B1.[FuBded], B1.[AppCategory], B1.[Research]
FROM Business B1
INNER JOIN
(select [ID], MAX(ModifiedDate) as ModifiedDate, SUM(YearlyAmount) as YearlyAmount,
SUM(MonthlyAmount) as MonthlyAmount
from Business
Group by ID) B2
ON B1.ID = B2.ID AND B1.ModifiedDate = B2.ModifiedDate