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?
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.
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
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.
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!!
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?
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!!
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.
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.
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?
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
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)
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?
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' ...
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??
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.
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
"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.
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.
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:
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?
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?"
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.
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.
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?
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
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
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.
