Hi I need to create query which would calculate weekly change of some values. There is table with values for every day. At the end of week I need to calculate % of change. It is something like this:
SELECT ((LastFridayValue - PreviousFridayValue) / PreviousFridayValue) * 100 from myTable.
or it could be something like this:
(LastValue - FirstValue) / FirstValue * 100 from top 5 values from my table order by ID DESC.
Please help me translate this into real sql query :)
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
Now, I need to check T1 and if T1.CheckBoxCol=1 then set T2.CheckBox1=1 else if T1.CheckBoxCol=2 then set T2.CheckBox2=1 else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
Hello, I have MyTable with ID, IsYesNo fields ID is duplicated so I need perform select on MyTable with the following conditions: 1. Select all the ID distinct where IsYesNo=’Yes’ first 2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the first selection Combine these two and return the result
I'm sure this is really basic. I've created a simple form that on submittal, I would like the text boxes to be submitted to the database. I have successfully created the Datasource and it connects ok, but what code would i use to submit one text box for email address and a simple submit button. Is there an easy way to do this with VWDE? Thanks
Hi,I have a hotel reservation system.I need to implement Check availability (Room checking) function for the project.But I dont understand how do I start and write SQL for this.Here is table structureTblRooms------------RoomsIDRoomNameNoteunitPriceSeasonalOffersTblReservation----------------ReservationIDArrivalDateDepartureDateArrivalFromFlightNoPurposeOfVisitTblRoomsInventory-------------------RoomsInventoryIDTotalRoomsBookedRooms Should I add more fields or table to implement this or this is enough .please any body can help me
How to write this SQLSelectCommand="SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending')"How to get latest order id from this.I need to combine it with above sql.I mean i want to select above records but based on max orderid record.such as select latest records from ....above SQL where max orderid
A file with source data will be made available in comma delimitted format to supply a new weekending value, size and demand.
What I will need is first the dts to bring that in. then I am assuming a stored procedure to be run (this is why I am here) to add the data from the comma del. file into the table. If the data EXISTS I would want it to UPDATE the values that are in the dest table but run a calculation first which would be the planutil minus demand then the result to be updated intot the record. if the record from the tab del. file does not exisst in the dest table then insert it.
another words the logic i have in mind read the data from the temp tample (where the file gets imported into) see if the record exists in the live table is it does update it with the calculation of planutil minus demand if not create the new record in the live table. I need it to compare..
Someone help me with some code i thank you kindly in advance
p.s. And good books dedicated to stored procedures??
Many thanks in advance for anyone that can help me write this qry:
To summarise - we have a database that links components to services. Components can have a 1 to many relationship with services. The components are held in a table:
We also have a services table which hold all our various services we own:
dbo.services
svcID svcName 1052 svc1 1053 svc2 1054 svc3
We then have a tbl that shows the links between components and services
dbo.compUses
svcID compID 1052 310 1052 400 1053 122 1256 134
I would like to find out through the qry what components are currently NOT linked to a service. This will allow me to find out what components have no relationships.
This is in SQL2005.
Have I explained this well enough? Any help would be much appreciated!
Hey peeps, fishkake's back, and he's more clueless than ever!
OK, after a few days of wrestling with books and experimenting, I now know all about reading SQL. Well, what that means is I understand Select From Where etc etc.
How do I write data? I have a reference guide, if somebody could give me literally a few high-level commands that are to do with writing data in a similar way to reading it with the SELECT statement, it would be very helpful...
Hi, I have to implement a search functionality. In the various filters for the search, Store Number is one such filter. The user should be able to enter range values for store numbers. Like 1500-1600. So this should filter for all the stores between 1500 and 1600. Similarly, all these also should be valid. 1550,1600 1550 1550 - 1580,6000,8000 etc. I have function which identifies the commas or dashes and seperates out the store number and returns a string like Stores.Storenumber in(1555,1600) Store.StoreNumber between 1555 and 1600 etc... i generate a sql at run time and append this piece and then execute the sql. I have one of the query below.
declare @strQuery varchar(max) declare @strConcat varchar(10) declare @strAppend varchar(max) set @strAppend='' set @StrConcat ='And '
if @IsAdmin is null-- Not a Admin set @StoreId =(select StoreNumber from Stores where Store_Id = @StoreId)
set @strQuery=' Select (Select StoreNumber from Stores where Store_id=d.DestinationId) as StoreNumber, CartonNumber, ActualReceiptDate as [Scan Date], isnull(Sum(QtyShipped),0) as [Total Units], b.BatchNumber from Carton c left outer join CartonDetail Cd on Cd.Carton_Id = c.Carton_ID inner join Batch b on b.General_Id = c.Carton_Id and b.BatchType=''Warehouse'' and b.TranTable=''Carton'' inner join Document d on d.Document_ID = c.document_Id inner join Stores st on st.Store_ID = d.SourceID and st.StoreType =5 inner join Stores on Stores.Store_ID = d.DestinationID inner join Codelist cl on cl.Codelist_Id = c.CartonStatus_ID inner join Codes on Codes.Code_ID = cl.Code_id and Codes.CodeType=''Cartons Status Code'' where not c.cartonNumber is null '
if not (@StoreId) is null begin set @strAppend = @strConcat + '(' + dbo.DecodeStoreNo(@StoreId) + ')' End
if not (@DateFrom) is null and not (@DateTo) is null begin set @strAppend = @strAppend + @strConcat + '(convert(varchar(50),c.ActualReceiptDate,101) between ''' + @DateFrom + ''' and ''' + @DateTo + ''')' End
if not (@CartonNumber) is null Begin set @strAppend = @strAppend + @strConcat + '(c.CartonNumber = ''' + cast(@CartonNumber as varchar) + ''')' End
if not (@Status) is null Begin set @strAppend = @strAppend + @strConcat + '(cl.Codevalue = ''' + @Status + ''')' End
set @strAppend = @strAppend + ' group by d.DestinationId,CartonNumber , ActualReceiptDate , b.batchnumber order by ActualReceiptDate'
set @strQuery = @strQuery +@strAppend execute(@strQuery) This query takes time, if there a little over 1000 records. I wanted to know, if there is any way to optimize this query? or any other way in which the above can be accomplished.
I hope i was able to explain my query fairly. Please let me know otherwise.
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
Now, I need to check T1 and if T1.CheckBoxCol=1 then set T2.CheckBox1=1 else if T1.CheckBoxCol=2 then set T2.CheckBox2=1 else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
I have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields. Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If this is a success, I need to set T1.Status=’Done’ for the updated records. So this should only be updated records. T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=’Done’, any example would be greatly appreciated.
hi,i am working on asp.net development server. i have created a web page on which i have put some option to enter the any user id. as soon as user id is entered on button click i want to search the first name, last name, age in my database where i have inserted the table with entries. then i wan to display it on the page.Please tell me how can i write the C# code on the clicking event so that i get results. i have connected the database to my virtual server.
I have a table named "products", it has a column called "category", I have data such as "CategoryA", "CategoryB" and "CategoryC" etc.... now I want to do a select statement and choose "CategoryA" and "CategoryB" but I want to rename them as "A" and "B". how do I write such a statement... thanks!
Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it. @UserName nvarchar(128), @Email nvarchar(50), @FirstName nvarchar(25), @LastName nvarchar(50), @Teacher nvarchar(25), @GradYr int DECLARE @UserID uniqueidentifier SELECT @UserID = NULL SELECT @UserID = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserNameINSERT INTO [table name](UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr)
I wanted to select few columns from the result of calling this store procedure and sort by a column. {CALL FIQR.Portfolio.Loans_In_Portfolio('2007-09-07', 'FISYND', '', '%')} How should I write it?(without changing the store procedure)
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
hi,all,I have a table called "docs", it has a collum named "dirname"the table data like this docs table id dirname1 c14/sharedoc/forums2 c14/sharedoc/forums3 c15/sharedoc/forums... now I need get dirname equal 'c14/sharedoc/forums' data,so I write sql like this select * from docs where dirname='c14/sharedoc/forums' but,this return nothing. I guest "/" was been filter,so can someone write correct sql?
I have two table named tbl_Scale and tbl_NGTrDAMaster tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key and we get CityTypeID from xml databinder....... In my form thr is two drop down list one for scale name and another for city type id this is the data form tbl_NGTrDAMaster 17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412 but i am expecting only one combination of set..... like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that.... please help me.... i am in big trouble......Thanx in advance If my qes is not clear for everyone... plz tell me.... i try my lebel best for understand my prob to u.....
Hi all, i want to write a stored procedure to get the user details from userprofile table based on the time schedule from another table called reminders. the structure of reminder table is called jobtype - type of job that is sending mails to user or sending mails to employee like that frequency - how frequenctly sending mails that is daily or weekly or monthly weekday - if the above option is weekly then we have to take which day on weekly to send mail dayofmonth - if the frequency option is monthly then we have to take this field for which day on month jobtime - At what time to send mails it may be daily or weekly or monthly so i want to write the stored procedue based on above values to execute some task( that is getting userdetails and sending mails). please help me. Thanks!
