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`
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.
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 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
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])))
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 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 have a design question that I was hoping you all can help with.
When I normally design a table I always make sure to include a status field. This way I can "delete" a record without actually having to delete it, just update the record to a status which signifies deleted. This ensures my users have an undelete capability as well as safesguarding against the "mad clickers" which click right through the "You are deleting this record..." alerts.
I have been recently adding tables which have temporary records and are meant to expire at a certain date. I have designed these tables to have an expiration date field AND a status field.
My question is this: What is the normal convention relating to these types of records/tables? Should queries using these types of tables query them using the status AND the expiration date? Should you create a job that updates the records status based on the expiration date being passed. Should you use a view that only displays current records based on expiration date?
I just want to make sure Im following standard practice as I design these types of tables.
Consider the following tableCustomercustId char(10)accountExpiryDate datetimeaccountStatus bitNow, I want to update the accountStatus to False as soon as thecurrent date becomes accountExpiryDate.I think it can be done using "SQL Agent" but my webhost doesnt provideme access to that. I have access only to the Query Analyzer.ThanksShane
I have a scenario to fetch records for each ID on 2 conditions. There are 2 types of Product type for each ID. PFE and PRI. I need only latest active PFE and at the same time all the latest PRI should be closed. (meaning, only PFE should be in Active status currently)
1.) Latest Product type PFE should be A (active) status for the particular ID 2.) At the same time ALL the latest PRI should be C(closed) status for the same ID
I have give example with 3 scenarios and desired output
1.) For ID 101, Latest PFE is active and all latest PRI is closed ----> Should come in result 2.) For ID 102, Latest PFE is Closed and all latest PRI is closed ---->Should NOT come in result
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
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
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
I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please
AS SELECT [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date], [dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date] FROM[dbo].[30 Day exclusion] INNER JOIN [dbo].[30 Day exclusion].[id] ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
WHERE (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate) AND (@Enter_LastName is null or [Last Name] = @Enter_LastName) ORDER BY [Last Name]
