We are developing a database in SQL and we are trialing some of our typical analysis undertaken on out dataset.
I have a problem with a update function. ID direction Holiday Lat Long Speed obstime - Datestamp LicenseID - varchar(7) status - int (0 or 1) O-Unoccipied, 1-occupied Pickup - Boolean Dropoff - Boolean
I am trying to update the 'Pickup' or 'Dropoff' when the status changes from 0 to 1 or from 1 to 0 if the difference in the datestamp is less than 2 minutes. Pickup is when the status goes from 0 to 1 Drop off is when the status goes from 1 to 0
Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record. CREATE PROCEDURE UpdateCartItem(@cartItemID Int,@newQuantity numeric(9))ASUPDATE CartItems Set quantity = @newQuantityWHERE cartItemID = @cartItemIDGO
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
I hope you can help me. I posted this in the microsoft sql server newsgroupa few days ago and got no response so I thought I'd try here. If I canprovide any clarification I'll be glad to do so.I'm trying to calculate a column based on the value of the previous record.I'm not very experienced with SQL-Server.I'm using the following table:CREATE TABLE tblPayment([PaymentID] [int] IDENTITY (1, 1) NOT NULL ,[LoanID] [int] NULL ,[PaymentPeriod] [int] NULL ,[PaymentRecDate] [datetime] NULL ,[PaymentAMT] [money] NULL)I have a view based on this table. That view has the following calculatedcolumnsBeginningBalance: For the first record, this is equal to the loan amountfrom the loan table. For each additional record this is equal to the endingbalance from the previous payment record.Interest: BeginningBalance * the monthly interest rate from the loantablePrincipal: PaymentAMT - InterestEndingBalance: BeginningBalance - PrincipalIt might seem I could use a subquery to calculate the Beginning Balance asin:SELECT LoanID, PaymentPeriod, PaymentAMT,(SELECT SUM(PaymentAMT) FROM tblPayment AS tbl1WHERE tbl1.LoanID = tblPayment.LoanID AND tbl1.PaymentPeriod <tblPayment.PaymentPeriod) AS BeginBalanceFROM tblPaymentWHERE (LoanID = @LoanID)But this will not work, because the interest is calculated on the previousmonth's balance. I need to find a way to loop through the recordset. Isthis possible?Thank you,--Derek CooperDatabase9www.database9.com
I am trying to create package something like that..
1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
We are currently developing a crm that has the following main tables: client, sales, actions, contacts and employees with up to 4 million records. The db contains several other tables with extra information but these aren't relevant for this problem. Clients have sales, contacts and actions. sales and actions can have seperate contacts as well, and contacts can be related to employees in a way.
Offcourse a security model was implemented but this only provides the option to hide or show all the records of these main tables. Now people noticed that it is a must to let people see for example only companies they created or do sales with. In other words access must be given at a record level.
The first thing that pops in my head is to create a client_security, sales_security, ... table that contains the employee_id and client_id or sales_id, or ... And add a join with one of these security tables for each select that is executed.
I wonder (because of the huge amount of data) if there isn't a better way to get this done?
Another possible issue that came to mind was, that when some data doesn't need protection or some people have the rigth to see everything, we would have to create the records in the security table anyways, resulting in probably hundreds of thousands of records.
Feedback on our solution (if we can call it that allready), or even keywords to find more about this issue via google (record based security really isn't the best description I guess) are most appreciated.
is this possible to retireve data from view where i need only userid with rolekey1.? tried with a function but its taking more time? any options in doing it in the view itself?
Hi all - this one has me stumped... PLEASE HELP!!!
I have a back/forward navigation link that passes a URL.startrow number (lets call it n) - based on n - I only want to select the record that is the n'th record based on a sort order (gall_order) - (SQL SERVER).
<cfquery name="gallHomePic1st" datasource="id" maxrows="1"> SELECT id FROM gall_home ORDER BY gall_order asc </cfquery>
For e.g. - I want the 7th (14th - 21st etc) record based on gall_order asc.
Technology Type Size XYZ A 200 XYZ1 A 200 XYZ2 A 300 XYZ3 A 300 ABC1 X 238 ABC2 X 238 PQR B 320 MNO C 330
I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.
Now I need to concatenate the Technology based on Type and size.
As you can see in Type A we have two sizes 200 and 300.
We need to group the Technology of type A with same size together.
So the output of the procedure should be
XYZ + XYZ1 XYZ2+ XYZ3 ABC1 + ABC2 etc.
We need to concatenate the Technology string with the next technology if they have the same type and size.
Hi People, hope someone can help me out here with a little problem. Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58" Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control.
Below is how i get the string from the listbox control Dim except_time As DateTime except_time = DropDownList1.SelectedValue The expect_time is then passed to store procedure along with some other vars, which looks like this -- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData] -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- Insert statements for procedure here UPDATE exceptions SET validated = @validated, update_time = @update_time WHERE (except_time = @except_time)END So validated and update_time should be updated when except_time is equal to @except_time
My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match. can anyone see any silly mistakes i'm doing here ????
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D') ORDER BY tblAppKitOffers.OfferAmount Desc
I have a query which finds duplicate spec_items linked to a work order. What I want to do it remove the duplicates (and in some cases there will be more than one) leaving only the record with the highest [sr.id]
select sr.id, sr.linked_to_worknumber, sr.spec_checklist_id from spec_checklist_remind sr inner join spec_checklist_remind sc on sc.linked_to_worknumber = sr.linked_to_worknumber group by sr.id,sr.linked_to_worknumber, sr.spec_checklist_id Having sr.spec_checklist_id = 30 and count(*)>1 order by sr.linked_to_worknumber
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours], wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
This is a simple one, and I know that it has to be fairly common, but I just can't figure out an elegant way to do it. I have a table with the following fields: OrderID (FK, not unique) InstallationDate (Datetime) CreateDtTm (Datetime)
There is no PK or Unique ID on this table, though an combo of OrderID and CreateDtTm would ostensibly be a unique identifier.
For each OrderID, I need to pull the InstallationDate that was created most recently (based on CreateDtTm). Here's what I've got so far, and it works, but man is it ugly:
SELECT a.OrderID, InstallationDate
FROM ScheduleDateLog a
INNER JOIN
(SELECT OrderID, max(convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)) as TopRecord
FROM ScheduleDateLog GROUP BY OrderID) as b
ON convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)=b.TopRecord
May i know what is the SQL query to create another intermediate column which is used to store the sum of two columns of each and every record. Thank you.
I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.
Basically my SELECT statement looks something like this, then is followed by an If... Else statement
SELECT * FROM TableName WHERE LastName = varLastName AND FirstName = varFirstName AND Address = varAddress
If RecordSet.EOF = True Then 'Item Not Found, add new record 'code to add new record...... Else 'Item Found, do nothing End If
RecordSet.Update RecordSet.Close
Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??
Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated..
I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together.
I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one..
Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first..
Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part..
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
How to do some procedural updates based on a record string. What I wish to do.
If the value (of type string) in my column contains the ‘/’ character I wish to ‘do something’ If the value (of my string) in my column contains the ‘-‘ character I wish to ‘do something else’.
Assume my column contains both ‘/’ and ‘-‘ in the same table.column, but not within the same record.
The data in question is in
tbl_Quotes.tLastDateValue
I’ve found I can use something like this to search my string.
SELECT Count(CHARINDEX('/',tbl_Quotes.tLastDateValue)) FROM tbl_Quotes WHERE CHARINDEX('/',tbl_Quotes.tLastDateValue)>0
And similar for ‘-‘ records.
I’m thinking something like this could be used as the test.
How do I put this into play, I’m thinking of an IF or CASE but not sure how to best test and which program flow to use in SQL.
I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with. I have a client that basically wants web-based-update access to their sql server database. Specifically, for a group of users to be able to access a page where they select a record for editing. the caveat is that no two users should be able to pull up the same record at the same time. Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything.. so this is my current approach: The page they use starts-out with basically a blank form. there are custom-built paging controls at the bottom of the screen. they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process". when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately. The entire page is encapsulated within an ajax.net updatepanel. The entire page has caching disabled. This works well in conjunction with the first page being blank. if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page. A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close. in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later. In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen). The concept seemed to be working well until I started multi-user testing. Now it seems as if two users time it perfectly - they are actually able to both select the same record. it happens pretty rarely, but it does seem to happen. I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure). I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations.. but that's more annoying/confusing from a logging standpoint than anything.. I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it.. but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time. I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now. anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this.. or if you see any obvious problems/fixes with my current approach.. I would greatly appreciate any info you could provide- thanks-
I am having trouble returning the correct record with my stored procedure. my problem is that i don't know how to structure the sql statement to do the following: given a set of records that have the same loankey, i need to 1. find the record that has most recent date (lockExprDt) 2. for all records with that date, find the highest Lock Number (LockNo) 3. for the all the records with that date and that LockNo, find the highest extension number (Ext) currently my sql statement returns a record that has the most recent date. i don't now how to write the sql to further define my query to return the record that has the most recent date with highest lock number, and finally the highest extension number. any suggestions as to what i am doing wrong. below is my slq statement. please note that i need to add the sql that will query for the max LockNo, and max Ext. Any help is greatly appreciated. thx! select a.loankey, a.lockrate, a.investor, a.price, a.ext, a.cost, a.lockno, a.lockstatus , CASE WHEN CONVERT(CHAR(8),a.lockdate,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),a.lockdate,10) END as 'LockDate' , CASE WHEN CONVERT(CHAR(8),b.lockExprDt,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),b.lockExprDt,10) END as 'LockExprDt' , Case WHEN CONVERT(CHAR(8),b.lockExprDt,10)>= CONVERT(CHAR(8),GETDATE(),10) THEN datediff(day, CONVERT(CHAR(8),GETDATE(),10), CONVERT(CHAR(8),b.lockExprDt,10)) ELSE NULL END as 'Days' from cfcdb..locktable ainner join (select loankey, max(lockExprDt) as lockExprDtfrom cfcdb..locktablegroup by loankey) bON a.loankey = b.loankey AND a.lockExprDt = b.lockExprDt where a.loankey = @LoanKey
Hi, Let's say I have 1000 registered users in database table and each of them has numeric ranking value. How can I get the position of each user in comparison to other users ranking value?
I need to call the stored procedure below. Basically what I need to know is if the query returns a record? Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query. GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
Hellow Folks. Here is the Original Data in my single SQL 2005 Table: Department: Sells: 1 Meat 1 Rice 1 Orange 2 Orange 2 Apple 3 Pears The Data I would like read separated by Semi-colon: Department: Sells: 1 Meat;Rice;Orange 2 Orange;Apple 3 Pears I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
