I need to return a computed value and have no idea how to do it.
I have a table of dates:
Column DataType
RowID int
Person nvarchar(20)
Percent real
Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult
John D - 2001 - .58
John D - 2002 - .52
John D - 2003 - .50
Jane D - 2001 - .58
Jane D - 2002 - .52
Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
That's how it looks when you click on the results of a small subset of the query. Just what I want. Unfortunately when you try to right click and save it you get
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29 if we pass year =2015 and month=09 then we have to get 2015-09-29 if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
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.
