Hot To Write This SP
May 9, 2002
Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.
Here is my table field setup with sample values all fields are numeric
except date
weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142
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??
View 3 Replies
ADVERTISEMENT
Jul 12, 2006
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
View 12 Replies
View Related
Jul 27, 2006
I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
How can I do this?
View 2 Replies
View Related
Jul 28, 2006
I have table T1 with fields T1.ID, T1.CheckBoxCol
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
for each T1.ID
How can I do this?
View 2 Replies
View Related
Jan 1, 2007
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
View 1 Replies
View Related
Apr 29, 2007
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
View 5 Replies
View Related
Aug 29, 2007
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
View 2 Replies
View Related
Sep 14, 2007
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
View 2 Replies
View Related
Jul 7, 2004
I need to compare two column in two different tables but I do not know how to write this query.
Example:
Table1
-------------
EmployeeSSN
EmployeePayRate
Table2
-------------
EmployeeSSN
EmployeePayRate
I need the result set to look like this
Result Set
-----------------------------------------------------
EmployeeSSN T1Rate T2Rate Same?
-----------------------------------------------------
111-11-1111 8.95 8.95 True
222-22-2222 9.95 8.95 False
333-33-3333 8.95 NoMatch False
444-44-4444 NoMatch 8.95 False
and so on....
How do I write this in Transact-SQL also using a function to return the "NoMatch" and True or False statements?
Thanks,
Roger
View 1 Replies
View Related
Dec 1, 2006
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:
dbo.components
compID compName compType
1 srv1 1
2 srv2 1
3 srv3 1
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!
View 1 Replies
View Related
Dec 9, 2005
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...
This site is just making me lazy now...
View 10 Replies
View Related
Apr 15, 2008
Good Afternoon all,
I was wondering how to write the TOP 5 records using Relational Algebra.
Dallr
View 6 Replies
View Related
Nov 29, 2007
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.
Thanks
Renu.
View 1 Replies
View Related
Mar 25, 2008
how to write an UDF and run it
View 2 Replies
View Related
Mar 5, 2007
Hi There,
I have been working in ssis and i have a task to write a log of the all events in daily basis.
I mean writing a log for the entire package for each tasks.
Can some one help me how to do it?
Thanks
View 1 Replies
View Related
Nov 15, 2007
Hello all!
I'm triyng to update a NULL value to another value. I know I can do this, just can't seem to write it out right.
Something like
UPDATE Part_Order
SET Ser = 'NA'
WHERE Ser isnull
I know thats not right, but I know I'm close.
Any hints?
TIA!
Rudy
View 5 Replies
View Related
Jul 28, 2006
I have table T1 with fields T1.ID, T1.CheckBoxCol
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
for each T1.ID
How can I do this?
View 4 Replies
View Related
Jul 27, 2006
I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
How can I do this?
View 4 Replies
View Related
Sep 13, 2006
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
View 1 Replies
View Related
Oct 28, 2006
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?
View 4 Replies
View Related
Nov 1, 2006
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.
View 9 Replies
View Related
Nov 7, 2006
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.
View 1 Replies
View Related
Dec 27, 2006
How can I write a function that accepts a data as a parameter and if the date is
Last Monday in May or
First Monday in September or
4th Tuesday in November
returns true otherwise returns false.
View 1 Replies
View Related
Feb 5, 2007
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 :)
View 3 Replies
View Related
Sep 11, 2007
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!
View 9 Replies
View Related
Dec 10, 2007
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)
View 8 Replies
View Related
Feb 8, 2008
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)
View 8 Replies
View Related
Feb 10, 2008
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.
View 8 Replies
View Related
Feb 11, 2008
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.
View 1 Replies
View Related
Apr 16, 2008
I need how we can write into a file result of query in SQL.
i need to write into a file the details we get after executing a query.
View 3 Replies
View Related
May 13, 2008
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?
View 3 Replies
View Related
May 21, 2008
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.....
View 2 Replies
View Related
Jun 18, 2008
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!
View 6 Replies
View Related
