SQL Server 2014 :: Selecting Records Based On Date
May 6, 2014
So 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 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 ***
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers). I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr). I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table. I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds). A where clause seems to be needed but I don’t know the syntax or don’t find the right function I use the following code to insert the row :
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())? WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging). Today’s date is in the database but isn’t found by my tsql code I think.
I have a database full of different types of leads some for company A some for company B and so on, each doing a different service. However the leads from B can be used for A and leads from A can be used for B, so I want to merge the data.
Example:
Phone Number Name Home Owner Credit Insurance 727-555-1234 Dave Thomas Yes B 727-555-1234 Dave Thomas Gieco
I would like the end result to be one record:
Phone Number Name Home Owner Credit Insurance 727-555-1234 Dave Thomas Yes B Gieco
Since these were imported into SQL they all have a unique ID, here are the current labels
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1) and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0 IF (@_Stat_Count >0) BEGIN SELECT @Status = status_cd from status-table where status_id = 10 END
Hi! I have a table Tbl1 has to columns: A B _________ Ibm Me Sony Me Me Bob Me Frank I'd like to select all rows where B=ME and A=Me Thanks for the help
I need to list all the records in Table2 which don't have matching field values in Table1.
This the the exact opposite of what I need: SELECT DISTINCT Field1, Field2, Field3, Field4, Field5 FROM [Table1] WHERE EXISTS( SELECT DISTINCT FieldA, FieldB, FieldC, FieldD, FieldE FROM [Table2] )
The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.
What I need, however, is the exact opposite.
I tried the above using NOT EXISTS but I get no records at all.
SELECT RN_TEST_ID AS 'Test ID', MAX(RN_EXECUTION_DATE) AS 'Last Execution Date', MAX(RN_EXECUTION_TIME) AS 'Execution Time', RN_DURATION AS 'Run Duration' FROM RUN
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
SELECT * FROM bookkeep RIGHT OUTER JOIN acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.
I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.
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
I am having a table where i have the following columns where the date format is dd/mm/yyyy Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008 My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure? Please provide me with full stored procedure Thanx in advance
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:
@StartDate = '01/01/2008' @EndDate = '01/8/2008'
Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:
I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
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
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate FROM tblMachines LEFT JOIN (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo ) ServicedMachines ON tblMachines.MachineID=ServicedMachines.MachineID WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate field that i use in the subtraction for calculating the number of months.
Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate
SET NOCOUNT ON GO declare @num_of_times int declare @count int declare @frequency varchar(10) declare @num_of_times1 int
I am trying to create a job that would backup a DB dynamically to a different folder based on the week day. So if it's Saturday, backup to folder Saturday.
I have this
DECLARE @BackupLoc nvarchar (100) DECLARE @DayOfWeek nvarchar (100) set @BackupLoc = N'D:Backup' set @DayOfWeek = (SELECT DATENAME(dw,GETDATE())) set @BackupLoc = @backuploc + @DayOfWeek
[Code] ....
But it's giving me permission errors, although the AGENT and MSSQL service accounts are members of the local admins group.
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
