Finding Max Of 3 Max Date Columns?
May 13, 2014
how I would work out the max value of 3 max date values. Note the max date values may be null.
Here is my code :
SELECT TD.sro_num, SRO.description, SRO.cust_num, custad.name, SRO.sro_type, TD.whse, TD.CostCode, TD.stat_code, TD.[No of days in Status],
SRO.total_cost_lbr, SRO.total_cost_matl, SRO.total_cost_misc, ((SRO.total_cost_lbr) + (SRO.total_cost_matl) + (SRO.total_cost_misc)) AS total,
convert(varchar,MAX(mt.trans_date), 103)as max_matl_transdate, convert(varchar,MAX(lbr.trans_date), 103)as max_lbr_transdate, convert(varchar,MAX(misc.trans_date), 103) as max_misc_transdate
[code]...
View 2 Replies
ADVERTISEMENT
Apr 14, 2008
I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx
View 2 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
Oct 21, 2013
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
[code]....
View 11 Replies
View Related
Oct 25, 2007
Hi;
We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.
I have a CommisionAudit table that tracks changes in commission rate with the following values.
ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------
with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.
View 11 Replies
View Related
Oct 29, 2013
I have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..
View 1 Replies
View Related
Jun 2, 2011
How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:
Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A
I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.
View 9 Replies
View Related
Nov 21, 2013
I have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are
1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'
View 5 Replies
View Related
Apr 24, 2006
Hi Friends,
me again. I am trying to find out if all of my intended foreign keys are actually set as foreign keys programmatically.
Ive stuffed something up, please find my error
USE RIQDB1
SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE ((column_name Like '%fk%') AND (FROM Information_schema.type = F))
go
thanks
Cm
View 9 Replies
View Related
Aug 11, 2006
I have a tutorial in which there is a question to find the person who will be celebrating their Birthday within a week.
The table contains : name, dob, sex
Thanks
Knowledge grows when shared...
View 3 Replies
View Related
Jul 26, 2006
I have a table containing several date fields in which certain events happened, and I need to be able to find the expiration date for renewal. The expiration dates are at the end of the 6th, 12th, or 24th month after the event occurred, depending on the event.
Is there a way to return in a query the last day of month x following a date field? For instance, if the date '3/12/2006' is stored in the field for a 12-month expiration, I need to return '3/31/2007'.
Any ideas?
View 1 Replies
View Related
Nov 26, 2013
I want to find out the earliest [First_Post_Date] for any parentdid
My query (See below)
Produces the following results
SELECT
ParentID
,[First_Post_Date]
,[FDMSAccountNo]
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878595212886'
Order by ParentID desc
[code]....
View 1 Replies
View Related
Aug 1, 2014
I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below: URL....I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.
So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.
This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in.
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refDate datetime,
@Weeks INT,
@lastdate datetime
]code]....
But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.
View 2 Replies
View Related
Sep 18, 2006
Hi,Can anyone help please?select notefield, modifiedonFROM Table1WHERE id = '100426' and(statusfield like '%criteria1%' ORstatusfield like '%criteria2%')Produces a list of records based upon the criteria. I would like to beable to only show the newest dated record, from the modifiedon field.I've tried max(modified) on, but as I am using an aggregate function inthe query I have to use GROUP BY, which notefield does not like as thisis a ntext field.(I get:Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.)Any ideas please?
View 1 Replies
View Related
Oct 10, 2001
Is there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?
We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.
View 1 Replies
View Related
Jan 7, 2014
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Current results
FdmsaccountnoParentidLast_Post_Date
87802012188487823334288920140105
87802012788187823334288920140103
87802012888987823334288920131231
87870180988087823334288920131217
87802012088687823334288920131204
87823334288987823334288920131201
87870180888287823334288920131130
87802011588687823334288920131120
87875705088487823334288920131011
Desired results
FdmsaccountnoParentidLast_Post_DateParent Last Post Date
8780201218848782333428892014010520140105
8780201278818782333428892014010320140105
8780201288898782333428892013123120140105
8787018098808782333428892013121720140105
8780201208868782333428892013120420140105
8782333428898782333428892013120120140105
8787018088828782333428892013113020140105
8780201158868782333428892013112020140105
8787570508848782333428892013101120140105
My query is
select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc
View 3 Replies
View Related
Jul 23, 2005
Hi all,I have two tablesCREATE TABLE [JEMP] ([EMPID] [int] NOT NULL ,[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE[DOB] [smalldatetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [JPRO] ([PromoID] [int] IDENTITY (1, 1) NOT NULL ,[EmpID] [int] NOT NULL ,[EffectiveDate] [smalldatetime] NOT NULL ,[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION) ON [PRIMARY]GOINSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2002-15-11 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2003-03-01 00:00:00',8,7)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2002-01-04 00:00:00',20,22)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2005-05-01 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(5,'2001-10-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(6,'2001-08-01 00:00:00',55,NULL)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(7,'2003-10-01 00:00:00',11,8)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(8,'2001-09-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(9,'2002-01-05 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2002-11-01 00:00:00',24,25)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2003-11-15 00:00:00',23,24)--I wish to find the designation of employee on given date by usingpromotion and master table . I am using the following query to get theresultselect isnull( ( select top 1 newdesigid from JPRO whereempid=1 and effectivedate<'anygivendate' order by effectivedate desc ), (select desigid from empmast where empid=1) )It did give the result but looking for better method to solve this.With regardsJatinder
View 2 Replies
View Related
Oct 2, 2007
Hi,
I am using SQL Server 2000 and have the following questions:
1. How do I know the last updated (data) date using system objects or any other method?
2. How do I know the last modified date of a table using system objects or any other method?
3. How do I know when a table is last accessed
Any help is appreciated
Thanks for your time and help in advance
Kumar
View 3 Replies
View Related
Sep 16, 2006
Hi,
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
Any help would be appreciated.
Thanks,
-Niraj
View 1 Replies
View Related
Apr 19, 1999
I know I am missing something basic, here.
I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in.
At various times, I need to do a query to find all the rows that have no dates entered.
What do I use in my where clause? SQL server does not like 'where date = null.'
Thanks,
Judith
View 1 Replies
View Related
Sep 13, 2013
I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.
Example dataset SQL below:
CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'
[Code] ....
I would expect a dataset to be returned consisting of:
1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
View 9 Replies
View Related
Jul 18, 2014
In my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.
I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.
View 8 Replies
View Related
Jan 14, 2014
I have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
999119/2/20119/9/2012
999119/10/20129/10/2012
999119/11/20129/11/2012
999119/12/20126/2/2013
999116/3/20136/30/2013
[Code] ....
In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.
Here's another example where there is a gap in the ranges.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
333112011-09-022012-08-31
333112012-09-012012-09-10
333112012-09-112012-09-11
333112012-09-122013-01-06
333112013-09-01NULL
There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.
The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.
View 9 Replies
View Related
Jan 3, 2014
I'm trying to find if any part of a date range in my table between orig_start_rent and stop_rent falls within a period I specify in two variables: startPeriod and endPeriod
For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. My code here:
declare @startPeriod as smalldatetime
declare @endPeriod as smalldatetime
set @startPeriod = '2013-11-01'
set @endPeriod = '2013-11-30';
select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem
View 5 Replies
View Related
May 30, 2015
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
View 4 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
Jul 6, 2015
getting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
View 4 Replies
View Related
May 17, 2015
As I am have query, I want to find sqlserver 2012 user/login last login date, when user last time date logon to server.Â
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>
View 6 Replies
View Related
Mar 13, 2013
What are some good querys to run against a sample table of dates and items where the results can be used for charts?
Here is the sample table.
Code:
CREATE TABLE [dbo].[TestDates](
[ItemId] [int] IDENTITY(1,1) NOT NULL,
[AssignmentName] [nvarchar](max) NULL,
[AssignedDate] [date] NULL,
[DueDate] [date] NULL,
[Code] .....
View 1 Replies
View Related
May 28, 2008
Hello.
I have three INT columns in a table that record the users birth year, month, and day.
BDAY_DAY (INT)
BDAY_YEAR (INT)
BDAY_MONTH (INT)
I'd like to include a function in my query that will return their Age in years based on these three columns.
I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.
Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int
AS
Begin
Declare @Age As Int
Set @Age = Year(@Today) - Year(@DOB)
If Month(@Today) < Month(@DOB)
Set @Age = @Age -1
If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB)
Set @Age = @Age - 1
Return @AGE
End
Usage (how do i pass the three columns into this function??)
SELECT Last_Name, First_Name, ssn, dob
FROM Employee_Data e (nolock)
WHERE Cust_Id = 'Customer1'
and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21
View 3 Replies
View Related
Jan 26, 2007
First of all I wants to "Thank You" all of you has been trying to helps me solve some of the problems.
Now, I come across trying to get data records from 3 columns but two columns "SeasonalStartsuppressiondate" & "SeasonalEndSuppressionDate" both contain no year last four digits. How would I select a query have some records that customer is not fall in between vacation time given a particular months Mar/02/ and Nov/04/? I only want record with “Y� fields not in those months. Notice don't have "YEAR" at the end and so, I can't determine it year of 2005 or 2006.
Here's my table and query statement:
Table - Column fields
SeasonalFlag, SeasonalStartSuppDate, SeasonalEndSuppDate
Y ,04/02,11/04
N ,....,....
Query Statement
SELECT SeasonalFlag, SeasonalStartSuppDate, SeasonalEndSuppDate
FROM advodb ----<<<<my sample query>>>>>-----
WHERE (seasonalflag='y'
AND
convert(datetime,seasonalstartsuppdate + '/2007')< getdate()
AND
convert(datetime,seasonalendsuppdate + '/2007') > getdate())
thank you
RV
View 1 Replies
View Related
Aug 22, 2007
Hi
SQL Server 2000
I have four columns of date in a table say Date1, Date2, Date3, Date4 in table Tab_A.
I want to get average date of four columns for each row.
Please advise how?
Thanks
J a w a d
View 2 Replies
View Related
Aug 28, 2007
adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39
I would like to combine adate with atime to get adatetime
View 5 Replies
View Related
