Maxdate In Table Data (date Range)
Feb 4, 2004
Hi all, First post on THIS forum...so be kind (I have my nomex suite on anyway, and am flameproof, but STILL!)
I have a table that consists of a bunch-o-gibberish, but essentially looks like this (for the sake of discussion):
PK RecID int
PK StartDate smalldatetime
PK EndDate smalldatetime
Gibberish_1 varchar(zillions and zillions)
Gibberish_2 int
OK, my query (get it? *LOL* I kill me) is this:
My PK date range is essentially a range indicating a "validity date range" or in other words - "this record applys to dates from StartDate to EndDate".
If I insert a new row, that I want to be "valid" from today on into the forseeable future. I'm thinking I would enter it with StartDate = today, and would like to figure out a good "MAXDATE" type value to put in the EndDate column so that:
1) I can find a record using a single date (i.e., the current date) and find the validation record (as defined above) - - - I'm not concerned about this part of the question, as I can build the simple query to do so).
2) NOT have to keep adjusting the EndDate to keep it ahead of the current date so that the query into the range will work.
Essentially, my question is this: What is a good value to put in the EndDate to mean "infinity" - - that will still work when I query using a single date designed to find the validation record with a range that includes it?
My boss suggested something like "January 1, 2040", but this bothers me, since my kids may be working here then :lol: and I don't want to have to force them into a Y2K-like issue where they now have to go adjust all the "maxdate" values. Anythoughts or conventions that I am unaware of here?
Thanks!
Paul
View 10 Replies
ADVERTISEMENT
Apr 6, 2015
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
View 8 Replies
View Related
Dec 2, 2015
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
Note: ROW is Partition By ColumnA,ColumnB
TableName: PromotionList
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Expected outcome after performing update on the table
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
View 4 Replies
View Related
Sep 15, 2004
Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??
My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!
View 14 Replies
View Related
Mar 23, 2004
Hi all
Suppliers send my client sales figures in different formats. Some send data for each sale, some summarise by week, month or quarter.
I need to report on this data, showing estimates as to how many sales per day, week, month or quarter. To give you an example of the data I receive, see a simplified script below.
CREATE TABLE dbo.sales_summary (
summary_id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
start_date datetime NOT NULL ,
end_date datetime NOT NULL ,
number_of_sales int not null
)
GO
insert into sales_summary VALUES ( '20030101', '20030131', 100)
insert into sales_summary VALUES ( '20030101', '20030120', 150)
insert into sales_summary VALUES ( '20030111', '20030131', 200)
insert into sales_summary VALUES ( '20030201', '20030228', 120)
insert into sales_summary VALUES ( '20030201', '20030207', 50)
go
As you can see, I essentially receive a date range and a number of sales in each row. The data in the real system is received from more than 100 suppliers and the sales_summary table has more than a million rows in it.
Can anyone suggest an efficient way of being able to create a report that lists sales
- for each day
- for each week
- for each month
etc.
An example of the daily report might look something like
Date Number of Sales
01-Jan-03 20
02-Jan-03 0
03-Jan -03 15
etc.
An example of the weekly report might look something like
Week Starting Number of Sales
01-Jan-03 100
08-Jan-03 135
15-Jan-03 54
etc.
This has been driving me nuts for a while so any help is appreceiated.
Matt
View 2 Replies
View Related
Feb 14, 2008
When im using the below query im getting the output, but when i change the starting date to 2006 I'm not getting the data for 2007 though it falls between the 2006 and 2008 range...
select * From dbname..tbl where date>= '03/jan/2007' and date <= '11/feb/2008' and Status= 'C' and ID is not null
AND (ACCOUNT = '25869' or ACCOUNT = '0' + '25869' )
Check and post your comments ASAP...
View 11 Replies
View Related
Jun 29, 2015
I've data like below:
Now, I've to get active data for a particular date range. Let me explain the active data definition as below:
StartDate : 01-Jul-2015
EndDate : 31-Dec-2015
It should return all the data which was active for that date range even if it was only for one day.If no data found for that date range, check the last record before start date and and if its active then it should be returned else not.
I though of creating a function and pass primary key with date range and return the final status but that doesn't seems like an optimized query.
View 4 Replies
View Related
Jan 15, 2012
If you have a table that has records which are applicable over a date range, is there a preferred design for dealing with the dates?
A simple example might be an employee table, which might have multiple employees, with each employee have multiple records, each record being applicable over a particular date range.
For instance:
Code:
EmpID EmployeeName StartDate EndDate AdditionalFieldsOfData
00001 Jones, Jerry 4/6/2011 8/10/2011
00001 Jones, Jerry 8/11/2011 1/7/2012
00001 Jones, Jerry 1/8/2011 12/31/3000
00002 Fredricks, Fred 8/3/2011 10/15/2011
00002 Fredricks, Fred 10/16/2011 12/31/3000
One could model this table with an implied EndDate (of 12/31/3000), as in:
Code:
EmpID EmployeeName StartDate AdditionalFieldsOfData
00001 Jones, Jerry 4/6/2011
00001 Jones, Jerry 8/11/2011
00001 Jones, Jerry 1/8/2011
00002 Fredricks, Fred 8/3/2011
00002 Fredricks, Fred 10/16/2011
Or, one could imply the beginning date and store the first record's beginning date elsewhere, in a date hired table, or in additional field on each record. As in:
Code:
EmpID EmployeeName EndDate HireDate AdditionalFieldsOfData
00001 Jones, Jerry 8/10/2011 4/6/2011
00001 Jones, Jerry 1/7/2012 4/6/2011
00001 Jones, Jerry 12/31/3000 4/6/2011
00002 Fredricks, Fred 10/15/2011 8/3/2011
00002 Fredricks, Fred 12/31/3000 8/3/2011
View 4 Replies
View Related
Mar 16, 2015
I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.
I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.
View 7 Replies
View Related
Aug 20, 2006
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View 7 Replies
View Related
Jan 24, 2008
I have a DB with the folloing fields... ArticleID, ArticleTitle, PublishDate(DateTime Field), EndPublishDate(DateTime Field).... What I want to do is show in a list view only the articles where the date is on or after the PublishDate and before or on the EndPubishDate.
I thought I could simply do something like this
SELECT ArticleTitle, PublishDate, PublishEndDate, PublishedFROM UserArticlesWHERE (Published = @Published) AND (PublishDate >= @PublishDate) AND (PublishEndDate <= @PublishEndDate)
Well I can't... can someone explain how I can do this please... I understand the the PublishDate and EndPublsihDate need a variable... The info is in the DB I just don't know how to do what I need to.
Thanks,
View 4 Replies
View Related
Jul 30, 2014
We have records in one table that are marked as accepted/rejected based on eligibility start and end dates in another table. We're loading new eligibility data into an ETL table and if the start or end date is going to change, I want to report any records that need to be reviewed to see if their status should change. The new dates could be before or after the existing dates, and the new or existing end date could also be NULL. Currently I'm using 4 > < statements and it seems to catch any scenario, but I'm wondering if there's a better way:
DECLARE @RECORDS TABLE(RecordDate date,ID varchar(8))
INSERT INTO @RECORDS(RecordDate, ID)VALUES('20100101','99'),('20110101','99'),('20120101','99'),('20130101','99'),('20140101','99')
DECLARE @ORIGINALDATES TABLE(StartDate date,EndDate date,ID varchar(8))
INSERT INTO @ORIGINALDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)
[code]....
View 1 Replies
View Related
Aug 16, 2006
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies
View Related
May 14, 2015
I am using Sql Server 2012.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
View 3 Replies
View Related
Apr 21, 2014
There is a simple SQL table ("mytable"). Say, it has 100 rows and 5 columns. One of the columns (say "time") contains timestamps across the whole day and the data in this column has the following format: hh:mm:ssAM/PM. So, the table looks like this:
time var1 var2 var3 var4
12:00:01PM value ...
12:00:05PM value
12:00:08PM value
12:00:20PM value
12:10:12PM value
...100 rows
How to create simple SQL request for extracting data between any 2 timestamps? For example, I need sub-table of the initial table containing all data values between 12:00:05PM and 12:00:20PM:
time var1 var2 var3 var4
12:00:05PM value
12:00:08PM value
12:00:20PM value
I consider something like this:
SELECT * FROM mytable WHERE time BETWEEN ...?
View 2 Replies
View Related
Dec 27, 2007
I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block
CREATE TABLE #gl_sizerange (
glsid int IDENTITY(1,1) NOT NULL,
lowsize int,
highsize int,
sizecat varchar(10),
milestone varchar(25),
days int
) ON [PRIMARY]
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block
CREATE TABLE #en_entity (
enid int NOT NULL,
orgsize int,
) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5)
INSERT into #en_entity VALUES(2, 18)
INSERT into #en_entity VALUES(3, 24)
INSERT into #en_entity VALUES(4, 25)
INSERT into #en_entity VALUES(5, 47)
INSERT into #en_entity VALUES(6, 101)
INSERT into #en_entity VALUES(7, 499)
INSERT into #en_entity VALUES(8, 500)
INSERT into #en_entity VALUES(9, 10000)
INSERT into #en_entity VALUES(10, 567890)
CREATE TABLE #op_opportunity (
opid int NOT NULL,
enid int NOT NULL,
milestone varchar(25),
daysopen int
) ON [PRIMARY]
INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5)
INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18)
INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24)
INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25)
INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7)
INSERT into #op_opportunity VALUES(6, 9, 'Close', 35)
INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
View 4 Replies
View Related
Feb 29, 2008
i m new in sql...and i have this procedure..which have cursor inside..
1. i want to get all distinct date into #tempt table.
2. In the loop for each distinct date fetch all the date into another #temp
3. get max date from that #temp and use that date to get the data from original table
but i m getting 0 rows else all 8000 rows..which is wrong..can anyone help me plz...
create procedure procdate1
(@name varchar(50))
as
begin
SET NOCOUNT ON
DECLARE @MaxDate datetime
DECLARE @Date datetime
select id, title, dated,
CONVERT(CHAR(10), dated,101) as date,
CONVERT(CHAR(8), dated,114) as time from general
where name = @name AND dated = @MaxDate
DECLARE CUR1 CURSOR FOR
SELECT @DATE FROM #tempt
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @Date
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT dated INTO #Date1 FROM general WHERE CONVERT(CHAR(10),dated,101) = @Date
SELECT @MaxDate = MAX(dated) FROM #date1
--DROP TABLE #Date1
FETCH NEXT FROM CUR1 INTO @Date
continue
CLOSE CUR1
DEALLOCATE CUR1
end
-- DROP TABLE #tempt
end
View 3 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Aug 27, 2015
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
View 3 Replies
View Related
Aug 11, 2013
Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
View 18 Replies
View Related
Apr 22, 2015
I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.
View 5 Replies
View Related
Sep 28, 2012
I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:
Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.
I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable
How could I get the temperature if I did a sub-query here?
View 5 Replies
View Related
Jan 23, 2007
I have a report that I need to run on 2 different date ranges.
Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE
The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)
So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007
My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007
Any help would be greatly appriciated!
View 2 Replies
View Related
Jul 23, 2005
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
View 2 Replies
View Related
Jul 26, 2007
Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.
Any ideas?
View 4 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
Aug 30, 2007
I have a order table which has a orderdate and despatchdate
i want to write a query in such a way that i want to get all the details from the table for a range specified date as the oderdate and despatchdate are user
interactable. I search the google to solve this problem but could not find a answer
View 10 Replies
View Related
Jan 14, 2005
What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @Begin Date AND @EndDate. I want to include all of the @EndDate.
View 6 Replies
View Related
Aug 9, 2007
I am working on a report for staff productivity, and have to get a summary figure for how much productivity was expected for a date range. The problem is that the amount expected from an employee can change if they move from full time to part time etc.
So I have a view that has the begin date, end date, expected daily production # by employee, and have to figure out how to get the multiplication to work correctly.
Example:
Employee 1 had a daily production # of 10 from 1/1/07-3/31/07 and daily production of 5 from 4/1/07 - now
If I run the production report for 1/1/07 - 6/30/07 what I want is one summary figure of for the entire range which would be 10*Datediff(d,1/1/07,3/31/07)+5*Datediff(4/1/07,6/30/07) or 890+450=1340.
Of course the actual date range for the report will be a variable, and the dates for the begin and end of a production date range will be all over the place.
Any quick and easy way to do this?
View 4 Replies
View Related
Apr 7, 2008
Hey guys..
pls help
what query should I do? Obtaining certain date only using SQL server2000?
Thnx...
View 4 Replies
View Related
Jun 18, 2006
Hi All,
I am very new to SQL and have a question, hopefully someone can answer.
I have a table of data, one of the fields is a date.
What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".
Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506
dates between 01/06/06 and 31/05/07 is fiscal year 0607
Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???
Any help is much appreciated.
Cheers
Rudi
View 8 Replies
View Related
Oct 2, 2006
Hi, 'am fairly very new to SQL SERVER 2000.
I have this particular problem....I need to develop a stored proc
where in a parameter checks the dates between JAN 1st and DEC 31st of a particular year. The parameter is declared for year..
I'll mention a small example below...
USE PUBS
declare @year as varchar
set @year = 1993
select * from employee
where hire_date >= '@year-01-01'
and hire_date<='@year-12-31'
This is just an example to show wat i want...
the year is prompted to the user...he/she can select any year
I get the following error message....
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Can anyone help me on this......
Thanks in advance
View 9 Replies
View Related
Mar 12, 2008
How to display start date and finish date together in the same column with a '-' in between?
2/24/2008 - 3/1/2008
View 3 Replies
View Related
