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)
I have the following SQL statement that works great for my purpose:
SELECT DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0)) AS TimePeriod, AVG(ZNT) AS Expr1, AVG(SAT) AS Expr2, AVG(RAT) AS Expr3, AVG(RH) AS Expr4 FROM HVACValues GROUP BY DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0)) ORDER BY TimePeriod
I'd like to do everything above but add to display only those records from 1/1/07 to 1/7/07...so a week's worth.
I'm no SQL expert but I assume that I'd add 'WHERE TimePeriod = ....' Any help is much appreciated!
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())
hi all how to find the continuous date from the given date range in sqlserver 2005 e.g. 2007-01-27 and 2007-02-02 and output should be 2007-01-27 2007-01-282007-01-292007-01-302007-01-312007-02-012007-02-02any suggestion?
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
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
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!!!
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
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?
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
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...
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.
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.
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!!
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.
I'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:
DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#) Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date: UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#) WHERE (((table1 .ID) Between 1 And 5456));
This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.
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
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.
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.
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?
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.
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?
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
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)
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?
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
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?
Hullo!Doubt it matters too much, but I'll just start off by saying that I'm using C# ASP.NET 2.0 in Visual Studio 2005.I currently have a UID field entitled "ID", and I'd like to make it autonumbering so that users can input stuff into it through a simple web interface and not have to worry about the user's ID.Anyone know how I could go about doing this? I can't seem to find a way through VS2005's GUI, and I can't seem to find anything on the internet about doing it through an SQL statement.Thanks!
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.
I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).
...
From date To Date
...
From age To Age
...
From Salary To Salary
@salary is the last one.
when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DimTimesheetTimesheetNumber'. (rsErrorExecutingCommand)
Range operator ( : ) operands have different levels; they must be the same.
BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.
