Hi Guys,
Need your help on this problem.
Let say table name call tbl_range and 2 field call No1 and No2
I have this set of record :-
No1 No2
1000 2000
2001 3000
5000 6000
My problem, i want to check if user insert another set no No1 : 1500 No2 : 2500. So, this means that range already clash with another range. If this happened it will return 2 record (1000 - 2000) and (2001 - 3000). Can it be done and how?
Regards,
Shaffiq
I am creating a table that will check the age range for an attribute called Age. I am experiencing trouble when I run the code. Am I writing this code correctly?
check (Customer_Age in (>= '15' and <='100')),
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way?
Many thanks.
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
I need query which will check user supplied date range is in between the existing table startdate and enddate.
if any of the date of user supplied date range is in between the tables start date and end date,it should retrun that record from table.
for example user supply date range is from 1 may 2012 to 5 may 2012.
then query must check that
1 may 2005
2 may 2005
3 may 2005
4 may 2005
5 may 2005
(all dates) is in between startdate and enddate of existing table.
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedHi,
I need help with dates for my weekly report. Week starts on Sunday and ends on Saturday. In my view I have list of dates, how can I make a filter where dates should be within the week range. Example if I have, April 6, April 11, April 14, April 15. Week 1 should have April 6 and April 11 since week date is from April 6 to 12. Then April 14 and April 15 is for the next week. Thanks
Hi,
I have a where statement that covers the date range May 4 - May 12. However, it moves or rolls out every week. Is there a way to make it moves on May 13.
Here's my statement :
WHERE (Reviewdate >= DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))) AND (Reviewdate <= DATEADD(dd, 13, DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))))
Hi Everyone,
I am new to reporting servies and need some help.
My dataset is doing a simple select Details,amount,paidindate, from table where paidindate>=startdate <=enddate.
I have done a matrix report I would like add YTD as well as amount in each month. i.e
| Month + Year | YTD
Details | =amountmonth |=Amount for 01/04/year (finacial
year) to displayed month
having problems doing the YTD as it need to pull a different paidindate range.
Thanks,
John
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 RelatedI want to know what the best datatype is for a situation like this e.g.if I have a field "age" and the data has a range i.e. 22-30, 31-50 etc, what is the best data type to use for this scenario.
Similarly if I have a field that whereby you use a range for example 1-2 in one record but in another you get an integer value of 0 for instance, what again would be the best datatype.
Many thanks
Hi,
I'm trying to group data by date range, but each row of data could have a different date range based on a variable.
I want to say "look at the date range the paste five orders were placed" for each row individually. As an example, think of the rows as keywords in a Search Marketing program. Keyword X had 5 orders placed in the last week, Keyword Y had 5 orders placed in the last 2 weeks. I want each keyword to display its average impressions over the course of its respective date range.
Is this possible?
Thanks in advance!
Hi,
When I set a tinyint field as identity, it automatically sets the identity seed as 1. I wonder whether the increment is set to go around in loop. Say, I have the range of 256 numbers for a tinybit. So, an identifier should go from 1 to 255 and then to 0 and then back to 1. Is that right?
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.
System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.
Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.
At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.
anyone having this problem before?? hopefully you guys can help.
Thanks,
Hi all,
I have the Following Query
It is working fine if the first month is 01 and Second month is 12
but i want like this if the user selects 200103 to 200203 i want the
value of composite should be added from 200103 to 200203 in one row
and other row from 200203 to 200303.
like this if the user selects from 200004 to 200304 i have to get 3 rows..
that is Yearly data..so how can i modify the following query or any new query.
select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and '200312'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)
Thanks
For some reason this is just not "clicking" with me and the longer I stare at it the more I overcomplicate things and then I just confuse myself further...
Provided this relation showing where "pkey" and "skey" met on "ServiceDate":
Code:
skey pkey ServiceDate
--------------------------------------
123 1 11/12/2005
124 1 10/12/2005 *
123 2 10/02/2005
124 2 10/12/2005
124 2 11/12/2005
123 2 11/02/2005
I need to select out a list of distinct pkeys and skeys only when there was no meeting between the two in November 2005. In other words, this query would produce only one record - the starred record - when run against this sample table. This is because even though, for example, skey 124 / pkey 2 has an entry outside the desired range, it also has one inside the desired range. The same goes for the record outside our range for 123 and 2 - it also has a record inside our range.
At this point, I've come to the conclusion that I can first select all the distinct pkeys and skeys where the servicedate is not in 11/2005 then join it to a selection of distinct pkeys and skeys where the servicedate IS in the desired date range. Does that seem like the most straightforward way through this?
I don't get the impression that this is that complicated a problem, but it's one of those deals where I goofed up somewhere along the line, and now I think I'm really overthinking the problem, so I'd be much obliged if someone could give me a hearty slap to clear the ol' noggin.
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!!!
I have a report based on a query which requires the user to input the date range. I would like this date range to be a label in the report.
I already keying tha formula fo the data range.. Audit Fees
Report is my form
Between [Forms]![Purchase Report]![From] And [Forms]![Purchase Report]![To]
I am trying to run a report for the period "December 1 2010 to March 31 2011" However, it came out a warning message box.
"The expression is typed incorrectly or it is too complex.... try simplifying the expression by assigning parts pf the expression to variables".
I having some issues getting openRowset to work when a excel workbook has space(s) in the sheet name/table name. For example, Sheet1$A1:B10 works, however 'Sheet 1$'A1:B10 does not. Does anyone know what I'm doing wrong.
Thanks
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
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
Hi,
I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month >= 6 AND R.Year = 2006)
Group By R.Year , R.Month )
Union
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month <=3 AND R.Year = 2008)
Group By R.Year , R.Month )
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 ...?
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...
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.
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
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,
Hi,
I want to display some stats using captured information. the x-axis is the date on which something occured. the y-axis is the number of occurance on that day.
The problem is that the x-axis need to be of continous range, so say from 1st Mar 2006 to 8th Mar 2006, I need to display every date within that range.
I've gathered the neccessary stats with a group statement but I was hoping I can fill in the gaps without having to loop through the returned data to identify missing dates, is there any built int SQL Server function which will allow me to do this within my SELECT statement?
Cheers
james
`
I need to display all the dates within a range even with no data
For example right now my query get the records with the range say...
The range is 7/1/15 thru 7/7/15
I Get...
Joe 7/1/15 xxx
Joe 7/3/15 ccc
Joe 7/5/15 xxx
I want...
Joe 7/1/15 xxx
Joe 7/2/15
Joe 7/3/15 ccc
Joe 7/4/15
Joe 7/5/15 xxx
Joe 7/6/15
Joe 7/7/15
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]....
I'm using Reporting Services 2008 R2, version 10.50.2550.0, and Visual Studio 2008, version 9.0.30729.4462 QFE. Not sure if the SQL version matters, since I haven't gotten the report out of the development environment.I've read lots of messages, including URL.... that show you can have multiple data entries per row in a Range Bar Chart, simply by turning DrawSideBySide to False.My data set basically contains four pieces of data, LineNumber, Equipment, StartTime and EndTime. I have data like:
1, 'CLM1', 08:00, 08:30
1, 'CLM1', 09:00, 09:15
2, 'SS1', 08:05, 08:35
2, 'SS1', 09:05, 09:35
(I actually have a date with my datetime entries, but that should give you the idea.) My chart is set up with Values of EndTime and StartTime. If I set my chart up with only Equipment in the Category Groups entry, my chart will show CLM1 from 08:00-08:30, and SS1 from 08:05 to 08:35. No sign of the other entries per equipment, although I added a tablix to retrieve the same data and it all shows up.Changing DrawSideBySide between True, False and Auto has no effect on the data displayed on my chart. If I add StartTime in the Category Groups, I do show all the data, but one row for each piece of data, which isn't what the customer wants.From what I can tell from various sites, I'm set up right, but it just isn't working.
Hi can anybody let me know the script to copy a range of data from one excel sheet to new excelsheet.
thanks,
Prash