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
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.
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
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,
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)
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
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?
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.
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
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])))
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...
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?
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
I'm trying to create a stored procedure from a join of two tables. One table holds a list of containers, and the other table holds the history of the contents of those containers. All I want is to retrive the most recent history for each container. For example, the containers table has the container number and name, and the history table has the volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.
Normally, I would just create a cursor that holds a list of the containers and some blank fields, and then loop through it, retrieving the most recent record one by one, but I don't know how to do that in Transact-SQL. Also, I thought maybe some SQL wizard out there might know of a way to do it with a simple select statement.
Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm The result of the query should only return 3 rows/records,not 5. Thank you. Greg
Code Snippet select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age ,pv.WeightPounds, pv.HeightInches from Patients as p inner join PatientVisits as pv ON p.ID = PV.PatientID order by pv.VisitDate desc
INSERT INTO Patients (ID, FirstName,LastName,DOB) select '1234-12', 'Joe','Smith','3/1/1960' union select '5432-30','Bob','Jones','3/1/1960' union select '3232-22','Paul','White','5/12/1982' INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches) select '1234-12', '10/11/2001','180','68.5' union select '1234-12', '2/1/2003','185','68.7' union select '5432-30','11/6/2000','155','63.0' union select '5432-30','5/12/2001','165','63.0' union select '5432-30','4/5/2000','164','63.5' union select '3232-22','1/17/2002','220','75.0'
CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)
INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22') INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20') INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')
[Code] ....
I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result
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.
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
insert into Channels values (1, 'Channel 1'); insert into Channels values (2, 'Channel 2'); insert into Channels values (3, 'Channel 3'); insert into Channels values (4, 'Channel 4');
insert into Blogs values ('1', 'blah'); insert into Blogs values ('3', 'blah'); insert into Blogs values ('4', 'blah'); insert into Blogs values ('12', 'blah'); insert into Blogs values ('34', 'blah'); insert into Blogs values ('35', 'blah'); insert into Blogs values ('67', 'blah');
insert into Episodes values (1, 3, '', '', 'Episode 1, blog 3'); insert into Episodes values (2, 3, '', '', 'Episode 2, blog 3'); insert into Episodes values (3, 3, '', '', 'Episode 3, blog 3'); insert into Episodes values (4, 3, '', '', 'Episode 4, blog 3'); insert into Episodes values (5, 1, '', '', 'Episode 5, blog 1'); insert into Episodes values (6, 1, '', '', 'Episode 6, blog 1'); insert into Episodes values (7, 1, '', '', 'Episode 7, blog 1'); insert into Episodes values (8, 4, '', '', 'Episode 8, blog 4'); insert into Episodes values (9, 4, '', '', 'Episode 9, blog 4'); insert into Episodes values (10, 4, '', '', 'Episode 10, blog 4'); insert into Episodes values (11, 4, '', '', 'Episode 11, blog 4'); insert into Episodes values (12, 4, '', '', 'Episode 12, blog 4'); insert into Episodes values (13, 12, '', '', 'Episode 13, blog 12'); insert into Episodes values (14, 12, '', '', 'Episode 14, blog 12'); insert into Episodes values (15, 12, '', '', 'Episode 15, blog 12'); insert into Episodes values (16, 12, '', '', 'Episode 16, blog 12'); insert into Episodes values (17, 12, '', '', 'Episode 17, blog 12'); insert into Episodes values (18, 34, '', '', 'Episode 18, blog 34'); insert into Episodes values (19, 34, '', '', 'Episode 19, blog 34'); insert into Episodes values (20, 34, '', '', 'Episode 20, blog 34'); insert into Episodes values (21, 34, '', '', 'Episode 21, blog 34'); insert into Episodes values (22, 35, '', '', 'Episode 22, blog 35'); insert into Episodes values (23, 35, '', '', 'Episode 23, blog 35'); insert into Episodes values (24, 35, '', '', 'Episode 24, blog 35'); insert into Episodes values (25, 35, '', '', 'Episode 25, blog 35'); insert into Episodes values (26, 67, '', '', 'Episode 26, blog 67'); insert into Episodes values (27, 67, '', '', 'Episode 27, blog 67'); insert into Episodes values (28, 67, '', '', 'Episode 28, blog 67'); insert into Episodes values (29, 67, '', '', 'Episode 29, blog 67'); insert into Episodes values (30, 67, '', '', 'Episode 30, blog 67');
insert into BlogAssociations values (3,'',1); insert into BlogAssociations values (12,'',1);
There is my above sql script, i'm currently using mysql at home to test things but will convert to work in mssql. I need to get associated BlogID for Certain ChannelID from the BlogAssociations table. Then using those BlogID's that it finds for let just say ChannelID "1" list off the 3 most recent EpisodeID's from the Episodes Table per each BlogID. So if ChannelID "1" has four BlogID's associated with it then in the Episodes table list off the recent 3 EpisodeIDs for BlogID "3", then the 3 most recent EpisodeIDs for BlogID "5" and so on and so on.
I have this which kind of does what I want:
Code:
select e.BlogID, e.EpisodeID, e.other FROM episodes e, BlogAssociations ba WHERE e.BlogID = ba.BlogID and ba.ChannelID = '1' ORDER BY e.EpisodeID DESC;
It returns:
Code:
+--------+-----------+---------------------+ | BlogID | EpisodeID | other | +--------+-----------+---------------------+ | 12 | 17 | Episode 17, blog 12 | | 12 | 16 | Episode 16, blog 12 | | 12 | 15 | Episode 15, blog 12 | | 12 | 14 | Episode 14, blog 12 | | 12 | 13 | Episode 13, blog 12 | | 3 | 4 | Episode 4, blog 3 | | 3 | 3 | Episode 3, blog 3 | | 3 | 2 | Episode 2, blog 3 | | 3 | 1 | Episode 1, blog 3 | +--------+-----------+---------------------+
But as you can see it lists off 5 of the Episodes for BlogID "12", I only want the most recent 3 as previously stated. It also lists off more than 3 Episodes for BlogID "3". How in the world do I go about doing this? I'm making this a stored procedure so I can't use php otherwise I wouldn't even be posting
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB, (select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation, (select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
