Trying To Find Best Way To Filter On Date Range
I have a database were a client can have many addresses. Clients may be at one address or another at different time of the year. The table allows for entering a startdate and an enddate for each address. I'm trying to figure out the best way to filter on this to return only the current address. I have tried the Where clause below but I'm not sure this is what i should use. The year is not needed but the datatype is datetime. I think i need to use the startdate also, but I cannot seam to get how to filter this. If anyone has ideas I would like to hear them.
Thank you,
A.Selected=1 AND A.EndDate Is Null OR DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How Can I Filter A Date Range In Classic ASP?
I have a Classic ASP page that provides me a view on Orders posted by customer for a selected month and year from a SQL Server 2000 database. This ASP page has a Stored Procedure that returns the orders posted by month and year. However, my needs are to be able to display the view by month, day and year to month, day and year, For example January 15, 2008 to February 14, 2008. The current Classic ASP page has a dropdown to select the month and year from this dropdown only displays months and years for the months and years the customer had posted orders. What I’d like to know is how to add to the classic asp page the means to input a date range on the fly that would return the report by month, day, year to month, day, year.
View Replies !
View Related
Find Order By Date Range Or Order Id
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
View Replies !
View Related
Query Info Between Time Range & Date Range
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 Replies !
View Related
Filter A Dataset With The Range (inc.) Operator
It seems that when I use the Range(inc.) as the filter operator for a Dataset and then try to use the Last and First keywords to extract the results from the dataset in the layout the results are sometimes wrong and don't match the expected. Does anyone knows of any issues that would affect this filtering mechanism?
View Replies !
View Related
Find IP In Range
Hi there: This one's a bit tricky. I want to be able to take an IP address of a request on my website and find it in a table. Specifically, I need to be able to record IP address RANGES for search engines so that when they attempt to find a page that isn't there, I can programmatically send a 404 header instead of give them the human-friendly page (please don't make suggestions on this - I'm using PHP, etc., and can't send both a 404 header and a human-friendly page). Anyway. Let's say a search engine has the IP range 33.33.0.0 through 33.33.255.255 (I made that up). if I receive a request from IP 33.33.101.221, how can I store ONE record (as either a regular expression or maybe multiple fields or somehow else) so that I can match that IP? I'm normally pretty good at figuring stuff like this out, but this one has me stumped for the moment. All help is greatly appreciated. I'll be posting a similar request on the PHP forum - I hope that's not considered cross-posting. Jay
View Replies !
View Related
Query Help - Giving A Date Range Given The Start Date, Thanks!
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 Replies !
View Related
{RESOLVED} Date Logic - Calculating A Date Range
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 Replies !
View Related
Report Builder: Date Filter, Relative Date
I have a date filter, and I default it to first day this month and last day this month under relative date, when I run it it givis me error: The Value expression for the report parameter €˜FromDate€™ contains an error: [BC30456] 'Date' is not a member of 'Integer'. (rsCompilerErrorInExpression) Can anyone fix this problem? But it works for Today or (n)months ago. Thanks.
View Replies !
View Related
How Can I Find Values Outside Of The Smalldatetime Range?
I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort. I have tried to find the values which are out of the smalldatetime range, with the following query SELECT * FROM midmar WHERE bday BETWEEN '01/01/1900' AND '06/05/2079' Which doesn't quite work, and gives me values that are actually between those two values listed. I have also tried having the WHERE clause read: WHERE bday <'06/06/2079' AND bday>'01/01/1900' and that doesn't really work either. What's going on? Is there likely another problem besides the structuring of my queries?
View Replies !
View Related
Finding Where My Date Falls In Date Range
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 Replies !
View Related
Range In Date
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 Replies !
View Related
Date Range
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 Replies !
View Related
Sum By Date Range
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 Replies !
View Related
Date Out Of Range?!
Greetings! I have a data source that gets generated based on a variable with the following SQL : "select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @[User::Last_Run_Date] + "'" But when I run my package I get an error message saying: The conversion of CHAR to DATETIME resulted in a DATETIME value out of range The Last_Run_Date variable is set to '2006-06-25 14:35:05.450' When I run the code in a QA session it works, but in the package it complains! What am I doing wrong? Thanks for your help in advance.
View Replies !
View Related
Get Date Range
I have a function that takes in a week number and returns data based on that week number, for one client we have a table that tells what the weeks are since they are not on a normal calendar, but their fiscal calendar. We now have another client that want the same function, but for it to return the data based on the normal calendar week. So, how can I determine the date range of a week, based on the week number?
View Replies !
View Related
Date Range With MDX
I want to return data from a date range using MDX. The user will pick a month and a number for the number of previous months to display. So far, using SSRS to write my MDX code I have: SELECT NON EMPTY { [Measures].[Steel Margin], [Measures].[Coil Weight], [Measures].[Amount], [Measures].[Gross Margin], [Measures].[Gross Profit] } ON COLUMNS, NON EMPTY { ([Date Shipped].[Month].[Month].ALLMEMBERS * [Out Rep].[Rep].[Rep].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DateShippedFiscalYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DateShippedMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DateShippedYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@OutRepRep, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW]))))) WHERE ( IIF( STRTOSET(@DimSalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@DimSalesTypeDescription, CONSTRAINED), [Dim Sales Type].[Description].currentmember ), IIF( STRTOSET(@DateShippedYear, CONSTRAINED).Count = 1, STRTOSET(@DateShippedYear, CONSTRAINED), [Date Shipped].[Year].currentmember ), IIF( STRTOSET(@DateShippedFiscalYear, CONSTRAINED).Count = 1, STRTOSET(@DateShippedFiscalYear, CONSTRAINED), [Date Shipped].[Fiscal Year].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS I have two other parameters: StartMonth and RollBackNumber. RollBackNumber will be the number of months and StartMonth will be my computed start parameter. I can write some code if necessary to compute the StartMonth (something like [Date Shipped].[Month].&[2007]&[3]&) if I have to.
View Replies !
View Related
Date Range
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 Replies !
View Related
Date Range
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 Replies !
View Related
Filter Date
I got a field called Date#, datatype = datetime, default value = getdate(). So after i insert a record, the Date# will auto record the system date and the time. And my MS SQL Server show: 6/9/2004 12:24:35 PM However i am not able to filter my record by date. The statement i tried are: SELECT * from mytable WHERE Date# = '6/9/2004 12:24:35 PM' SELECT * from mytable WHERE Date# = "6/9/2004 12:24:35 PM" SELECT * from mytable WHERE Date# = 6/9/2004 12:24:35 PM SELECT * from mytable WHERE Date# = '6/9/2004' Pls help me, Thanks a million
View Replies !
View Related
Filter By Date From VB
I have been trying to write a simple database program using VB and SQL Express. In doing so, I have run into a problem. I am trying to filter by a Date value. I have the following SQL in the background. SELECT TransID, BIN, CarrierName, CheckDate, NumberOfClaims, BeginningDate, EndingDate, CheckAmount, Code, CheckNumber FROM Payments WHERE (CheckDate=@CheckDate) ORDER BY CheckDate DESC I am using a datetimepicker to select a date value and pass it in. Doesn't work. I get an error about "errors converting string to date" or something like that. I thought datetimepickers passed date values. Anyway, just executing the SQL query and typing in the parameter value gives the same results. Please help. What am I doing wrong. Thanks for the help If you need additional info, please let me know.
View Replies !
View Related
Date Filter
I have a report in Visual Studio that I want to limit to just the activity for today. Normally I would add a WHERE stmt to the sql script that says docdate=getdate(). However this does not return any data to me in VS. I have tried many different syntax. Does anyone have the correct syntax or filter value that works in Visual Studio? Thank you.
View Replies !
View Related
How Would I Filter By Date?
Hi I am trying to make a chart by using data from a database. What I am trying to do is this. The user will have 3 options Option 1. View last quiz Option 2. View Data by week(up to 4 weeks). Option 3. View by month Later once I get these down I will have view 7 days worth of data. So I am not sure how to do option 2 and 3. I have not tired to do Option 1 yet so can't say yet. Like say if I have a months worth of data and they choose Option 2 it should make 4 columns on my graph. However I don't know how to make my sql to filter like that. A user also can do 0 or more quiz's a day so first all the values(total right, total wrong, total assitance needed) would need to be added up. After that the filter would have to be taken place so that it filters. So how would i do something like this?
View Replies !
View Related
Date Filter
Hi I have two tables, both have date/timestamp of some event. The first table has records of the correct date/timestamp and the second table has records with same date but different timestamps of the same event. I need to write a SQL code that selects records where the date/timstamp in the second table is closest to the correct date/timestamp in the first table. For example, first table has it like this 1/20/2005 8:00 The second table has 1/20/2005 4:00 1/20/2005 2:00 1/20/2005 3:00 So, I want a SQL code where (date1 minus date 2) is MIN. Assuming I have linked the 2 tables by their keys and want to bring back on the record where the dates are closest and filter out the rest. I am thinking a where clause, a CASE clause can do it. Thanks
View Replies !
View Related
Date Filter
Hi guys can you give a query where I can get my data filtered by date.. (a.jetTransactionDate between 01 / 01 / 01 / 2007 AND CONVERT(DATETIME, '2007-01-30 00:00:00', 102)) but I can't get the data for January instead all transactions for the past years have been included thanks! :)
View Replies !
View Related
Sql Server Date Range
I have a table namely "Information" , it has one field namely " Lastupdate" when i insert or update data , lastupdate column takes takes current datetime value. and this data i am displaying in another page . Here i want to display the information upto 21 days from last updated dates. means if i save data today (12/04/2008) then the "lastupdate" value will be (12/04/2008) and when i display this data should display upto 21 days means upto (23/04/2008) when date 24/04/2008 will come, this data should not display. and i want this from sql server query , if anybody have idea please write query for that , its very urgent ..............
View Replies !
View Related
Date Range Problem
Hello all,I am trying to write a query that compares a member's enrollment periodwith the products that their group has had during that period (allmembers belong to a group and the products that the member has arebased on that group). I need to get the date range for all productsthat the member had during their enrollment.Here are a few rules:- In the source table there are some group products that have tworanges that are really contiguous. This is because another column thatwe don't care about may have changed between those two periods. If theend_date = DATEADD(dy, 1, start_date) then the two periods are actuallycontiguous. These should only appear as one row in the output.- If the gap is greater than one day then two rows should result- If the product changes, of course it should be two rows in the output- If a group has a product from before the start of the member'senrollment then the start_date for the row should be the member'sstart_date. If the product extends past the member leaving the groupthen the end_date should be that of the member.- In my sample data below I only have as many as two rows back to backfor the same product that are contiguous. In reality there could beeven more than that.I have SQL that will join the two tables based on either the start orthe end date of the group product falling in the member's enrollmentperiod, but I'm not sure of the best way to merge the contiguous dateranges into single rows. Any suggestions?Erland, despite it being late on a Friday afternoon, the SQL and sampleoutput are below. ;-)Thanks,-Tom.CREATE TABLE Members (group_id INT NOT NULL,member_id INT NOT NULL,start_date DATETIME NOT NULL,end_date DATETIME NOT NULL )GOALTER TABLE Membership ADD CONSTRAINT PRIMARY KEY PK_Members PRIMARYKEY (group_id, member_id)GOCREATE TABLE Group_Products (group_id INT NOT NULL,product_id INT NOT NULL,start_date DATETIME NOT NULL,end_date DATETIME NOT NULL )GOALTER TABLE Group_Products ADD CONSTRAINT PRIMARY KEY PK_Group_ProductsPRIMARY KEY (group_id, product_id, start_date)GOINSERT INTO Members VALUES (1, 1, '2002-01-01', '9999-12-31')INSERT INTO Members VALUES (1, 2, '2004-11-01', '9999-12-31')INSERT INTO Members VALUES (1, 3, '2000-10-01', '2004-12-31')INSERT INTO Members VALUES (2, 4, '2002-01-01', '2005-01-15')INSERT INTO Members VALUES (2, 5, '2004-10-01', '9999-12-31')GOINSERT INTO Group_Products VALUES (1, 1, '2001-01-01', '2003-12-31')INSERT INTO Group_Products VALUES (1, 1, '2004-01-01', '2004-11-15')INSERT INTO Group_Products VALUES (1, 2, '2004-11-16', '9999-12-31')INSERT INTO Group_Products VALUES (2, 1, '2002-01-01', '2004-11-01')INSERT INTO Group_Products VALUES (2, 1, '2004-11-15', '9999-12-31')GOExpected Output:group_id member_id product_id start_date end_date-------- --------- ---------- ---------- ----------1 1 1 2002-01-01 2004-11-151 1 2 2004-11-16 9999-12-311 2 1 2004-11-01 2004-11-151 2 2 2004-11-16 9999-12-311 3 1 2001-01-01 2004-11-151 3 2 2004-11-16 2004-12-312 4 1 2002-01-01 2004-11-012 4 1 2004-11-15 9999-12-312 5 1 2004-10-01 2004-11-012 5 1 2004-11-15 9999-12-31
View Replies !
View Related
Dates In A Date Range
Is there a way that I can get a resultset that contains unique dates ina given date range without the need to have a temporary table and acursor?perhaps something like:declare @start_date as datetimedeclare @end_date as datetimeset @start_date as '1/1/2005'set @end_date as '1/1/2006'select fn_getuniquedate(@start_date, @end_date)1/1/20051/2/20051/3/2005...12/31/2005
View Replies !
View Related
Problem With Using BETWEEN For Date Range...
Hi all -This problem has been driving me crazy, and I'm hoping the answer issomething stupid I am neglecting to see....The procedure posted below is part of an Access/SQL database I havedeveloped. Basically, the user would input a beginning and ending date,and the query goes and pulls records that meet the following criteria:1. TradeDate is between beginning date and ending date2. TradeTime is between beginning date and ending date3. Trade's Match ID is equal to match IDs returned by the second selectstatement - this is part of a ticket processing system, and tickets aregrouped using their match id. So, if one ticket has been updated andnow meets criteria #1 or #2 above, this is supposed to also return anyof the other tickets with the same match ID - so if one ticket in agroup changes, our acct. dept can look at the whole group on theirreports.Anyway, the query below seems to work, but I am not happy with it. Theproblem was I was using the BETWEEN function, and not converting allthe dates to varchar. This worked fine, unless the beginning date andending date were the same. For example, if I had a ticket with atradedate of 5/3/06 and I ran a beginning date of 5/3/06 and an endingdate of 5/3/06, the ticket should be returned. However, with theBETWEEN statement, it would return no rows.I changed the BETWEEN statements to statements like:tradedate >= beginning date and tradedate <= ending datebut this also returned no rows.It was only upon converting all the dates to varchar and using the <=and >= operators that I started getting the results I need.Can someone tell me why the heck BETWEEN wouldn't work? Tradedate andTradetime are both datetime values, and I was bringing in the beginningand ending date variables in datetime form...is there a problem usingBETWEEN when the first and second variables used are the same?Any light you can shed on this would be great, because having all theseconvert statements and such makes me nervous...I'd rather get betweento work, but I have not been able to in my testing...Thanks! -JimCREATE PROCEDURE dbo.spAcctExport(@begindate datetime,@enddate datetime)AS SELECT TOP 100 PERCENT dbo.tblTradeAccount.AccountingCode ASTradeAccount, dbo.tblOrders.TicketNum, dbo.tblOrders.TradeDate,dbo.tblOrders.SettleDate, NULL ASProductionMonth, dbo.tblOrders.RepID, dbo.tblOrders.AcctNum,dbo.tblAccounts.Shortname, dbo.tblOrders.Quantity,dbo.TBLCUSIP.Factor, dbo.tblOrders.BuySell,dbo.tblOrders.CUSIP, dbo.TBLCUSIP.Issuer, dbo.TBLCUSIP.PoolNum,dbo.TBLCUSIP.Coupon,dbo.tblOrders.FixAdj, dbo.tblOrders.Price,dbo.tblOrders.RepSC, '=(H:H*I:I*Q:Q)/100' AS Markup, 'PTMSA' ASProdType, dbo.tblOrders.DeskSC,dbo.tblOrders.RepCarry, '=(H:H*I:I*T:T)/100' ASDeskMarkup, dbo.tblOrders.MatchID,'=IF(K:K="B",((I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' ASTotalPrincipal, dbo.tblOrders.CancelCorrect,dbo.tblOrders.OriginalTrade,dbo.tblOrders.TradeTime, dbo.tblOrders.Rep2ID,dbo.tblOrders.Rep2SCFROM dbo.tblOrders INNER JOINdbo.TBLCUSIP ON dbo.tblOrders.CUSIP =dbo.TBLCUSIP.CUSIP INNER JOINdbo.tblAccounts ON dbo.tblOrders.AcctNum =dbo.tblAccounts.AcctNum INNER JOINdbo.tblTradeAccount ON dbo.tblOrders.TradeAccount= dbo.tblTradeAccount.TradeAccountWHERE (((dbo.tblOrders.MatchID IS NOT NULL) AND (dbo.tblOrders.MatchID IN(SELECT MatchID FROM dbo.tblOrders WHERE (CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=CONVERT(VARCHAR(10), @begindate,101) ANDCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=CONVERT(VARCHAR(10), @enddate,101))OR (CONVERT(VARCHAR(10),dbo.tblOrders.TradeTime,101) >=CONVERT(VARCHAR(10), @begindate,101) ANDCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=CONVERT(VARCHAR(10), @enddate,101)))))OR ((CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=CONVERT(VARCHAR(10), @begindate,101) andCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=CONVERT(VARCHAR(10), @enddate,101))OR (CONVERT(VARCHAR(10),DBO.TBLORDERS.TradeTime,101) >=CONVERT(VARCHAR(10), @begindate,101) ANDCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=CONVERT(VARCHAR(10), @enddate,101))))ORDER BY dbo.tblOrders.CancelCorrect,dbo.tblTradeAccount.AccountingCode, dbo.tblOrders.MatchID,dbo.tblOrders.BuySell, dbo.tblOrders.TicketNumGO
View Replies !
View Related
Run Same Query For Each Day Across A Date Range?
Hi,I have a query which works for one day:SELECT SOME_COL AS something, SOME_COL2 AS something2 FROM myTableWHERE DATE = '2007-05-11' AND SOME_STAT 1Returnssomething something 21 23 4How do I get this to work for a date range (e.g. DATE '2007-05-09')where I get:date something something22007-05-09 1 22007-05-09 3 42007-05-10 1 22007-05-10 3 42007-05-11 1 22007-05-11 3 4Thanks in advance!
View Replies !
View Related
Select From A Date Range
Hi guys,whilst working on a project which I thought was nearly complete I havecome across a problem which was some how over seen, which I am hopingone of you guys know how to resovle.Firstly here is the script to create the table and insert some sampledata:CREATE TABLE [dbo].[tbltemp999] ([Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EventDate] [datetime] NOT NULL ,[EventID] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6005)The EventID 6006 represents a system shut down, and the EventID 6005represents a sytem start. Therefore from the above data you cans seethat the machine is not rebooted very often.My problem occurs when you select a date BETWEEN something where thereis no data. ie BETWEEN 2004-10-01 AND 2004-10-15 my desired resultfrom this should return 15 days of uptime but as there is no data itis returning nothing:(Can anyone work this one out?ThanksSunny
View Replies !
View Related
SQL Date Range Query
Hi Folks Need a little help with a Date Range query; Im using SQL 2000 and the field is set to datetime I need to query from now to 30 days ago, its very basic I know... The field is called - ArtDate Any help appreciated ! Thanks
View Replies !
View Related
Date Range Logic
Good morning all, I've got a little headscratcher for you involving date ranges. We have a table for recording absences: Absence(unique_identifier, parent_identifier, date_from, date_to ... ) And an employee table Employees(unique_identifier, Surname, Firstname, birth_date ...) Where the relationship between the two is: Employees.unique_identifier = Absence.parent_identifier The problem lies when wanting to know whether an employee was off within a specified date range. Absence: u_idp_iddate_fromdate_to 112007-02-012007-02-06 222007-01-292007-02-06 322007-03-252007-03-25 432007-06-062007-06-08 542007-02-052007-02-06 Given the above sample results, how can I identify which employees were off during the first week of February (2007-02-01 to 2007-02-07)? Expected Results: u_idp_iddate_fromdate_to 112007-02-012007-02-06 222007-01-292007-02-06 542007-02-052007-02-06 Any advice you can give to help me get the answer I need is much appreciated :)
View Replies !
View Related
Absences Between A Date Range...
Ok guys and gals I have a logic-scratcher for you! Employees(employee_number, surname, forename, etc) Absences(employee_number, date_from, date_to, etc) I know, not the best design but it's what I've got to work with! So here comes the tricky part; given a user-defined date range I need to return who has been off sick! Here's some sample data +-----------------+-----------+ | employee_number | forename | +-----------------+-----------+ | 1 | Aaron | | 2 | Ben | | 3 | Charlotte | | 4 | Dawn | | 5 | Edward | | 6 | Frank | +-----------------+-----------+ +-----------------+-----------+----------+ | employee_number | date_from | date_to | +-----------------+-----------+----------+ | 1 | 01/01/08 | 03/01/08 | | 2 | 02/01/08 | NULL | | 3 | 01/01/08 | 15/01/08 | | 4 | 08/01/08 | 10/01/08 | | 6 | 05/01/08 | 07/01/08 | +-----------------+-----------+----------+ So, who was off sick between 04/01/2008 and 09/01/2008? red indicates the people we want to return in our resultset Aaron was not off sick during this date range. Ben is still of sick and his sickness started in this date range. Charlotte was off sick during the entirety of this date range. Dawn's sickness started between these dates. Edwards hasn't been off sick at all and finally; Frank was off sick between these two dates. Unfortunately I can't get my head round the logic needed here... Any suggestions? I hope my noddy example gives you enough insight to help, if not; let me know and I'll try answer any questions you fire at me! -George
View Replies !
View Related
Date Range Problem
Hello! I'm quite new to Analysis Services. I have a problem i can't solve. Scenario: A car rental company registers the history of each car. for each record there are two date fields, StartDate and ReturnDate. A record in the historical table is: Car Mercedes C200 Customer Company1 StartDate 12/12/2003 ReturnDate 01/12/2004 Quantity 3 This means that the customer rented 3 mercedes c200 from 12/12/2003 to 01/12/2004 Then, If I want to know at the end of Q4-2003 how many cars where rented in T-SQL I should write SELECT….. FROM …… WHERE startdate<=12/31/2003 and (returndate >=31/12/2003 or returndate is null) Now I built a cube based on that table with the following dimensions and measures: Dimension: CarModel Customer StartDate ReturnDate Measures Quantity The question is: if I use as point of my analysis Q4-2003 and I want to see at that time how many car I rented, how can I filter or select the subset of the cars rented before Q4-2003 and returned after Q4-2003 (or not returned yet)? Carmodel quantity start return Mer c200 2 may-2003 feb-2004 Mer c200 6 dec-2003 mar-2004 If in the cube browser I try to filter the startime selecting q4-2003 my result will be just 6 but I had other to cars out rented before (then it should be 8). I should be able to have the possibility to dynamically select the analysis point because the cube is browsed by crystal reports, so I have to insert something in the cube structure, like….calculated member? Thanks in advance.
View Replies !
View Related
Dynamic Date Range MDX
Hi, I currently have a cube with a Dynamic Date Dimension that contains calculated members such as YTD, LYTD, current month etc. I need to add a new members that displays the last 12,18,24 months. I have this "Current Year" code but cannot work out what it is doing or how to modify it. It successfully displays the months for the current year. iif( IsAncestor([Period].CurrentMember,strtomember("Period].[Year].&[" + CStr(Year(Now())) + "]").FirstChild), (strtomember("[Period].[Year].&[" + CStr(Year(Now())) + "]"),[Dynamic Dates].[All Dates]), iif( IsAncestor(strtomember("[Period].[Year].&[" + CStr(Year(Now())) + "]"), [Period].CurrentMember), [Dynamic Dates].[All Dates], null )) Can you help please.
View Replies !
View Related
Please Help Me Very Urgent To Get A Date Range
Dear All expertise please help me becuase I am very beginner I have a Holiday Table in which contain each employee's holiday from date and todate . I want to get an sql to get the record having holiday from the given date range example Employee A having holiday from 01-01-2008 to 06-01-2008 Employee B having holiday from 05-01-2008 to 10-01-2008 Employee C having holiday from 20-01-2008 to 30-01-2008 I given the criteria as follows to get list of employeed having holiday in given date range Holiday from 01-01-2008 to 06-01-2008 . So Employee A and Employee B should come in the list. Please give me an SQL
View Replies !
View Related
Date Range Issue
I've been trying to find a good way to do this and it seems every idea I come up with or find only accomplishes the desired result in one scenario. Here is my issue. I'm designing reports with SQL Reporting Services 2000 and many of these reports need to show values for a dynamic date range such as the last full month, last full week, etc. similar to the built-in functions in Crystal Reports. To accomplish this, my first effort was to filter my results by looking only at results where a specific date parameter matched the current week or month -1 or -2 or whatever was needed. The following example would look at records for the previous month only: SELECT * FROM tbExample WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE()) I understand this may not be the most efficient way of performing this operation, but this seemed to work at the time. If I were looking at the previous week, I would simply replace MONTH([EntryDate]) with DATEPART(week, [Entry Date]) and get the same result. The issue that I've run into is that at the very beginning of the year (first week and month particularly), this code fails. Since the functions I've used above result in an integer, then statically subtract from it, at the beginning of the year, I potentially end up with zero or negative numbers which causes unpredictable results or errors. To address my issue, I thought I would write an IF statement which would look at the result and if it were zero or a negative number, compensate accordingly. Following is an example for the previous month: IF (MONTH(GETDATE())-1 <= 0) BEGIN SELECT * FROM tbExample WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1+12 AND YEAR([Entry Date]) = YEAR(GETDATE())-1 END ELSE BEGIN SELECT * FROM tbExample WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE()) END RETURN The above example simply compensates by adding 12 months back to the result and subtracting 1 year instead. This is obviously limited, but seemed alright for my application. This example does not however work for the weekly ranges. The problem with using this on a weekly basis is that there are technically 53 weeks in a year, though the last week only has a few days. I am unsure if there is a way and if so, how to calculate this to add back 52 or 53 weeks respectively depending on where the current date falls. In researching another issue, I received a tip of a different way to pull date ranges. Although this addresses the issue of rolling back into the previous year, it would only work for the monthly reports, not the weekly. Here is the example: SELECT * FROM tbExample WHERE [Entry Date] >= DATEADD(month, -1, DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0)) AND [Entry Date] < DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0) As stated previously, this works perfectly by focusing on anything between the first day of the previous month and the first day of the current month. The problem is that the 1-day function does not allow for a weekly focus. I believe I have explained my situation to the best of my ability. I am completely open to suggestion, whether it be along the line of things I have already tried or fresh, new ideas. Thank You
View Replies !
View Related
Date Range Dilema
Hi, I have a report in which I need to filter data for a range from beginning of current month to end of current month , ie a data table field "date_occured" should be between the range from beginning of current month to end of current month this is a default Also this should be a parameter that the users can change ie two text box parameters on the top of report set to default of beginning of current month and end of current month which can be later varied by the users In my sql code I have set two parameters @begindate datetime @endDate datetime in where clause where date_occured between @begindate and @endDate but in the report rdl , I am unable to set the two text boxes to default to the dates 11/1/2007 and 11/30/2007 ---example for this month Please help Thank you
View Replies !
View Related
Date Range Criteria
Ehhm,great!! Could you tell me what is going wrong with the stored procedure below? I can't get any results! (the 'ap_dateIn' field in the sql table 'tbl_1' has "dd/mm/yy hh:mms" smalldatetime format) ( @date_min smalldatetime='1/1/1910', @date_max smalldatetime='1/1/2010' ) AS SET NOCOUNT ON; SELECT tbl_1.* FROM tbl_1 WHERE (ap_dateIn BETWEEN @date_min AND @date_max) thakns for your time,fellaz!
View Replies !
View Related
Query For Date Range
I use oledb (ACCESS database) in my application. i want to build a query to retrieve the number of Bookings from my Booking table where the appointment_date_time.timeOfADay is in range of 9am-12pm or 14pm- 7pm, that is (>= 9 and <12) or (>= 14 and < 17). Please help to build the query, I found some query sample like: select * from tblstudents where classID='1' and studentstartdate between ('2004-12-03') and ('2004-12-12') or WHERE DateField BETWEEN @StartDate AND @EndDate But I dont want to search year and month and day, i just want to search the actual hour of a day. i am stuck with the syntax, please help
View Replies !
View Related
Searching Date Range
i have a date field with which i'd like to search several date ranges. the date field is populated as month, day, year (2/23/2007). I'd like to select date using the following criteria: < 30 days (i.e. select * from date where today's date < 30) 30 - 60 days (i.e. select * from date where today's date >= 30 and <= 60) > 90 days (i.e. select * from date where today's date < 90) Is there a simple way of acheiving this? Thanks!
View Replies !
View Related
Grouping By Date Range
My company is unusual in that our accounting periods are not actual months. We have what we call "Red Fridays." These are spaced 3-5 weeks apart. So, my company doesn't care what happened in the month of April, but they care what happened between April 4 and May 2 because these are the Red Fridays. So, I have created a database with a table called "RedFridays" with the dates for this year. I want to combine this with various tables in our ERP database. I use a Left Outer Join between the Red Friday Dates and the corresponding date in the ERP database. I need to create a custom grouping formula which accomplishes the following: 1. Subtract a certain number of months from today's date to determine which Red Friday would be the correct starting date. 2. Group records by date between that Red Friday and the second one. This would be listed as something like "Month 1". 3. Continue grouping in this way to the present date and that Red Friday range. Can anyone point me in the right direction? Any help would be greatly appreciated.
View Replies !
View Related
|
TRACKER
