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)
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?
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.
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
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?
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
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
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
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
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!
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.
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 ''
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
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
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)
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!'
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
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.
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])))
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()))
I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:
SiteDate Data A01/01/2013 ... B02/01/2013 ... C03/01/2013 ... A04/01/2013 ... B05/01/2013 ... C06/01/2013 ... And so on...
In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.
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
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
Say I have 3 .bak files named:jamesB.bak, jamesG.bak, jamesW.bakIs there a clever way to find out which is the most recent of thesebackup files?? Using sql query analyzer preferably...
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,
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.
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.
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
