Retrieving All Users In Db With Specified AGE Range
Jul 29, 2007
Hello, I have a table called Member in my database that I use to store information about users including the date of birth for each person. I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages). Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.
So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application. And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).
How is the best way to go about this? There are many sites out there that return users with user specified age ranges. Is there a best way to do this that's the least taxing on the db?
TIA.
View 6 Replies
ADVERTISEMENT
Jan 5, 2014
I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.
I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)
On query run;
Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)
if none
Load the first record
View 6 Replies
View Related
Mar 5, 2007
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
View 14 Replies
View Related
Apr 28, 2015
I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.
Sample Table
ID DeviceID Temp InsertDate
1 10 55 04-28-2015 8:00 AM
2 65 74 04-28-2015 8:00 AM
3 44 23 04-28-2015 8:00 AM
4 10 87 04-28-2015 8:01 AM
5 65 65 04-28-2015 8:01 AM
View 3 Replies
View Related
Mar 11, 2015
I am using following queries in a stored procedure.This stored procedure is executed through a dot net application.
DECLARE @DEPTNBR BIGINT
SELECT @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS= 1
UPDATE DEPARTMENT_DETAILS SET STATUS= 0 WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC FROM DEPARTMENT_DETAILS WHERE DEPTNBR = @DEPTNBR
From my queries,I am providing a available department information.Each user needs to get unique available department information.But when more number of users using the application concurrently, multiple users getting same department information.How to solve my problem?I always wants to get unique department information even though multiple users using the application concurrently.
View 6 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
Jul 28, 2015
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
View 5 Replies
View Related
Mar 6, 2008
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies
View Related
Nov 26, 2014
I'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.
UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';
UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;
UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);
UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);
error message
(1000 row(s) affected)
Msg 245, Level 16, State 1, Line 181
Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.
View 5 Replies
View Related
Sep 28, 2015
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies
View Related
Apr 30, 2008
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
View 9 Replies
View Related
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
Nov 28, 2006
Hi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh
View 1 Replies
View Related
Jun 4, 2008
I want to be able to list all users connected/logged in a specific database and disconnect them all or a certain user.
can this be done in SQL and if so how?
View 1 Replies
View Related
Sep 26, 2007
We are using Navision with SQL server 2003.
What kind of methods is there out there to reduce hwo often this happens?
View 10 Replies
View Related
Jul 29, 2013
I have a need to select rows where values in a column are within a range AND ALSO the first row that is highest (MAX) but just UNDER the range. For example:
NAME VAL
Jim 4
John 5
Tom 6
Julie 7
Rich 8
Rob 9
RANGE TO SELECT is 6-8 (inclusive)
Rows with a value in the range are returned (6, 7, & 8) as well as the row with a highest value that is JUST BELOW the range (5)
NAME VAL
John 5
Tom 6
Julie 7
Rich 8
View 2 Replies
View Related
Nov 6, 2007
How can I efficiently retrieve the top x managers within the top y regions within the top z states within the top q countries?
The only way I have been able to do this is to first find the top q countries, and store the list in an IN clause. Then for each of those, find the top z states and create another IN clause that contains the country+state concatenated. Then for each of those, find the top y regions and concatenate country+state+region. Then finally find the top x managers within this IN clause.
This works fine for a few hundred records, but once it reaches the thousands, it takes much too long. The final IN clause contains thousands of entries.
Isn't there a simpler way to approach this, especially with SQL Server 2005?
View 3 Replies
View Related
Jan 19, 2005
i am using a stored procedure, lets say spTemp, that calls sp_executesql within it. in the sp_executesql procedure i am passing parameters from the stored procedure 'spTemp'. i want to retrieve the result of the sp_executesql statement so as to perform further calculations in spTemp. is there a way of doing this?
ex:
create procedure spTemp
(
@abc int
)
as
declare @temp int
exec sp_executesql 'select @temp = Id from Product where @abc > 10'
...
how to get the value @temp from this statement?
View 2 Replies
View Related
Sep 24, 2007
I am now retrieving the PDF from the database and I am getting an error:
Error 1 'GetImages.GetImage(int)': not all code paths return a value
I have:
int imageid = Convert.ToInt32(Request.QueryString["ACTUAL_IMAGE_PDF"]);
And...
private SqlDataReader GetImage(int imageid)
{
Sql Statement...
}
Could someone help please??
View 3 Replies
View Related
Jun 15, 2004
Hi
I've got a module that contains the following function.
Imports System.Data.SqlClient
Module SQL_Sprocs
Function ListUsers()
Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("DBConn"))
Dim cmd As SqlCommand = New SqlCommand("list_users", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
ListUsers = dr
dr.Close()
conn.Close()
End Function
End Module
I then want to call this function from my webform. So I'm using the following
Dim dr As SqlClient.SqlDataReader
dr = Timetracker.SQLProcs.ListUsers()
Do While dr.Read
Label1.Text &= dr("first_name") & " " & dr("last_name") & ", "
Loop
dr.Close()
But it's not working. I want to have a module that contains all my sprocs and be able to call them from the individual webpages.
What am I doing wrong?
Lbob
View 3 Replies
View Related
Jul 22, 2004
Hi, I have a Stored procedure doind an INSERT which then returns @@Identity.
I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..
Not sure where I am going wrong...
Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?
Many Thanks
View 3 Replies
View Related
Sep 16, 2004
I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.
Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.
View 1 Replies
View Related
Oct 11, 2005
i have a table in MS SQL Server,i need to show two results at a time,and on click of a button nxt two results have to b shown,how do i accomplish this (i mean retrieving the results two at a time)thanx in advance
View 1 Replies
View Related
Dec 21, 2006
Faculty member writes:
"I meant to delete just one assignment, doing which was giving me difficulty.
I deleted a whole category by accident which had most of my daily grades in it for a course with quite a few of them. I was negligent in backing them up, so I cannot restore them. I have an XLS file from the midterm, but am missing about 6 grades after that.
Is there any way to recover the grades I had in the deleted category. It has been a couple of days since I entered any new grades. HELP!"
My current backup strategy is to do a full backup 5 pm, differentials every 2 hours, logs every 30 min, all so they arenot simultaneous. Backing these all to same backup device via three different jobs.
Then the server is backed up to tape shortly after the 5 pm full backup.
Obviously, I can't restore from the backup for this situation because it would hurt other data.
But - is there a way I can take last night's backup from tape and restore it to a different database than our production database? (Such a database doesn't at this time exist. Would I detach, copy over the files, and then reattach on prod and attach on the devl side, and then restore from the backup device to the devl copy?)
I've never had to perform this kind of rescue and am wondering if my backup strategy is flawed since it isn't immediately evident how I can easily do this.
I needed a similar thing from our Oracle DBA yesterday, and he went to tape, copied out a table and put it on the Oracle database under a different owner, and I easily fixed the few records that I had botched.
That's exactly what I need to do now with MS SQL 2000.
As the faculty dude said, "HELP!"
And Thanks.
View 2 Replies
View Related
Oct 26, 2005
Hi,
I was hoping someone would be able to help me, I trying to go through several databases, 111 to be exact, and get the data out of a particular table which is present in all the databases on the same server.
I know how to get the names of the databases from the INFORMATION_SCHEMA, but how can use the name for it to cycle through all the databases and get data out of the one particular table.
View 5 Replies
View Related
Jan 5, 2006
Hi,
I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information.
For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name:
----- SQL CODE -----
SELECT DISTINCT
scContacts.ID,
scContacts.inheritCoAddr,
scContacts.fName,
scContacts.lName,
scContacts.contact,
scContacts.billToAddress1,
scContacts.billToAddress2,
scContacts.billToState,
scContacts.billToZip,
scContacts.billToCity,
scContacts.billToCounty,
scContacts.billToCountry,
scCompany.ID AS companyID,
scCompany.companyName,
scCompany.companyURL,
scCompany.billToAddress1 AS companyAddress1,
scCompany.billToAddress2 AS companyAddress2,
scCompany.billToCity AS companyCity,
scCompany.billToState AS companyState,
scCompany.billToZip AS companyZip,
scCompany.billToCounty AS companyCounty,
scCompany.billToCountry AS companyCountry,
scCompany.businessType,
scCompany.phoneExt,
scCompany.phoneNum,
scCompany.faxNum,
scCompany.minEmployees,
scCompany.maxEmployees,
scCompany.actionTypeMAX,
scCompany.actionDateMAX,
scCompany.actionTimeMAX,
scCompany.statusID,
scCompany.userID,
scCompany.repID
FROM
scCompany,
scContacts
INNER JOIN
scStatus ON
scStatus.ID = scCompany.statusID
WHERE
scgroupContacts.contactID = [insert cookie value]
AND
scgroups.ID = [insert cookie value]
AND
scCompany.ID = scContacts.companyID
AND
scCompany.groupID = scgroups.ID
----- END SQL CODE -----
As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table?
thanks,
mellamokb
View 3 Replies
View Related
Jun 5, 2006
bobby writes "I have a Table from which i get the ID of customers(Customers are people who have written an online exam).The date of completion of the exams are stored in the corresponding table with the name of the exam.There are more than one exams and the names of the exams are stored in an other table.Now my question is how to get the date of cpletion with the given Id from the first table?"
View 2 Replies
View Related
Apr 23, 2007
Hi, I need some help. I have this query where i need to pull up students that have more than or equal to 3 absences in a row. Now I am getting all those students that are absent but not in a row. But i was wondering if there is a way to tell if a student was absent three days in a row . There is a date field that is being used to identify the day of the class and a status field that identifies if the student was absent or present. Please help someone.
Birju
View 3 Replies
View Related
Feb 14, 2008
i lost a table with lots of data - there's around 10,000 records inserted daily.
my problem was i only realized this morning (and i lost it last night) and i have the backups backing up each day every hour - so the backup had already overwritten it seems the db that was missing the table)
my backup type is set to append - so i though i could retrieve by date time but it only let me retrieve the backup at 11pm(tune if last backup) and no earlier time. is there anyway of getting the data from the 7 pm backup -- again it backsup each hour to the same file - set to append so i thought that meant i could choose a time but it doesn't seem to be working.
please advise.
View 4 Replies
View Related
Feb 18, 2008
insert into newTABLE(newCOLUMN)
Select
Case When [Column 11] like '%DOB%' Then
SubString( [Column 11],
CharIndex('DOB',[Column 11],1)+3,
Case When CharIndex(';',[Column 11],1)= 0 Then
CharIndex('.',[Column 11],1) Else CharIndex(';',[Column 11],1)
End-(CharIndex('DOB',[Column 11],1)+3)) Else '' End,[column 11]
from table
Following is da example of return result
through the image, we can found that some of them got ddmmyy and some got only year.Currently, i want to insert those record to newtable newcolumn with only those got propal date(unwanted for only year).What should i do following?
View 6 Replies
View Related
Jul 23, 2005
Hi AllBit of a situation so hopefully someone can advise.I've a client who has a sql 2000 database that *unfortunately* hasn'thad a backup procedure in place. Its been running for just over a yearand on Monday it got, well, screwed. They have a 50 gig ldf file.Yes, 50 gig. Is it possible to put in place a database maintenanceplan that will allow me to roll back to, say, last friday just from theldf file?Thoughts? Any advice would be welcome.MTIAMark
View 3 Replies
View Related
Jul 23, 2005
Hi all,I have an application that creates a publication on the server, andhave multiple mobile devices creating annonymous subscriptions to thatpublications. I need to write a report that checks if each device havethe replication synchronized successfully. I can rundistribution.dbo.sp_MSenum_merge or look intodistribution.dbo.MSmerge_history to get at the data for _all_subscriptions to a given publication, but to look at a particularsubscription, I need to filter by either the subscriber_db or agent_idcolumn. The problem is, how do I get either one of these informationfrom the device? Or is there other way of retrieving the merge historyfor a particular device/annonymous subscription?Thanks in advance,Harold
View 1 Replies
View Related
Jul 29, 2005
Hi,Is it possible to get metadata (i.e. descriptions of tables etc.) insql-server? In Oracle you can retrieve this information with tables likeall_objects, user_tables, user_views etc. For example, this query selectsthe owner of the table 'ret_ods_test' (in Oracle!):select ownerfrom all_objectswhere object_name = 'ret_ods_test'What's the equivalent in sql server?Thanks a lot.
View 2 Replies
View Related