A SQL Procedure To STOP Repeated Info
Feb 4, 2007
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
View 8 Replies
ADVERTISEMENT
Feb 4, 2007
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
View 5 Replies
View Related
Sep 4, 2007
I have problem, i wanted a query which will search the duplicate and then give suggestionmost repeated word
Table containing the records like below
ID
Movie Name
New Name
1
Spider Man
Spider Man
2
Spider Man 2
Spider Man
3
Spider Man 3
Spider Man
4
Spider Man UK
Spider Man
5
Spider Man USA
Spider Man
6
New Spider Man
Spider Man
7
Spider Man Black
Spider Man
8
Spider Man Part 1
Spider Man
9
Spider Man Part 2
Spider Man
10
Spider Man I
Spider Man
11
Spider Man III
Spider Man
12
Spider Man Part II
Spider Man
My manufacturer send me the data in this format and i have to allocate there new name
to do some comparison
I wanted to make this process automatic.
what i mean is i need a query which will give me a repeated records along with suggestion
as new name.
I am fully confident that you guys will help me out from this problem.
Looking forward
View 9 Replies
View Related
Jan 10, 2007
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View 2 Replies
View Related
Jul 16, 2004
Hi there :)
I was hoping someone could help me figure out why this doesn't work....
I have a class called ProductsDB with a method called AddProduct that looks like this:
public String AddProduct(int categoryID, int makeID, string name, double price, double saleprice, double offerprice, int homepage, string thumbpath, string imagepath, string description)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ProductsAdd", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);
SqlParameter parameterMakeID = new SqlParameter("@MakeID", SqlDbType.Int, 4);
parameterMakeID.Value = makeID;
myCommand.Parameters.Add(parameterMakeID);
SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Value = name;
myCommand.Parameters.Add(parameterName);
SqlParameter parameterPrice = new SqlParameter("@Price", SqlDbType.Money, 8);
parameterPrice.Value = price;
myCommand.Parameters.Add(parameterPrice);
SqlParameter parameterSalePrice = new SqlParameter("@SalePrice", SqlDbType.Money, 8);
parameterSalePrice.Value = saleprice;
myCommand.Parameters.Add(parameterSalePrice);
SqlParameter parameterOfferPrice = new SqlParameter("@OfferPrice", SqlDbType.Money, 8);
parameterOfferPrice.Value = offerprice;
myCommand.Parameters.Add(parameterOfferPrice);
SqlParameter parameterHomepage = new SqlParameter("@Homepage", SqlDbType.Bit, 1);
parameterHomepage.Value = homepage;
myCommand.Parameters.Add(parameterHomepage);
SqlParameter parameterThumbnail = new SqlParameter("@Thumbnail", SqlDbType.NVarChar, 50);
parameterThumbnail.Value = thumbpath;
myCommand.Parameters.Add(parameterThumbnail);
SqlParameter parameterImage = new SqlParameter("@Image", SqlDbType.NVarChar, 50);
parameterImage.Value = imagepath;
myCommand.Parameters.Add(parameterImage);
SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
parameterProductID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProductID);
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
// Calculate the ProductID using Output Param from SPROC
int productId = (int)parameterProductID.Value;
return productId.ToString();
}
catch
{
return String.Empty;
}
}
And then in another cs file for my aspx page I have an event handler for an add button in a webform, which looks like this:
// Add New Product to ProductDB database
bikescene.Components.ProductsDB accountSystem = new bikescene.Components.ProductsDB();
String productId = accountSystem.AddProduct(Int32.Parse(DDLproductcategory.SelectedValue), Int32.Parse(DDLproductmanufacturer.SelectedValue), TBproductname.Text, Double.Parse(TBproductprice.Text), Double.Parse(TBproductsaleprice.Text), Double.Parse(TBproductofferprice.Text), Int32.Parse(DDLproducthomepage.SelectedValue), thumbnailpath, imagepath, TBproductdescription.Text);
And finally, here is my stored procedure:
CREATE Procedure ProductsAdd
(
@ProductID int,
@CategoryID int,
@MakeID int,
@Name nvarchar(50),
@Price money,
@SalePrice money,
@OfferPrice money,
@Homepage int,
@Thumbnail nvarchar(50),
@Image nvarchar(50),
@Description nvarchar(500)
)
AS
INSERT INTO Products
(
productID,
categoryID,
makeID,
name,
price,
saleprice,
offerprice,
homepage,
thumbpath,
imagepath,
description
)
VALUES
(
@ProductID,
@CategoryID,
@MakeID,
@Name,
@Price,
@SalePrice,
@OfferPrice,
@Homepage,
@Thumbnail,
@Image,
@Description
)
SELECT
@ProductID = @@Identity
GO
Basically, my images are uploaded to the server just fine but nothing is being added to the database + I don't get any error messages.
Any ideas?
Many thanks indeed :)
View 1 Replies
View Related
Feb 16, 2006
How does one get the names and data types of the parameters of a SQL 2005 stored procedure? There does not seem to be a refresh for a command parameters object, and the syntax for the get() in I don't seem to have right in c++.
View 1 Replies
View Related
Mar 3, 2008
Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.
Here is a simple example of what I have:
Code Snippet
begin try
if not exists (select * from sys.database_principals where [name] = 'flea')
raiserror('flea not found', 16, 1)
end try
begin catch
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select
@ErrorMessage = error_message(),
@ErrorSeverity = error_severity();
raiserror(@ErrorMessage, @ErrorSeverity, 1);
end catch
go
begin
print 'hello world'
end
At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!
Thanks,
Flea
View 5 Replies
View Related
Sep 28, 2007
i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.
could you give a little code sample?
thanx
View 4 Replies
View Related
Jul 20, 2005
Dear GroupI have found that table A had SELECT permissions for 'Public' but not tableB.Giving 'Public' SELECT permissions on table B did the trick.HOWEVER, I don't want anyone to be able to do a direct SELECT on table A orB but only give them access to the data by using the stored procedures. Isthere any way this can be set up?Thanks for your efforts!Have a nice day!Martin"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message news:...[color=blue]> Dear Group>> I'm having two stored procedures, sp_a and sp_b>> Content of stored procedure A:> CREATE PROCEDURE dbo.sp_a> SELECT * FROM a> GO>> Content of stored procedure B:> CREATE PROCEDURE dbo.sp_b> SELECT * FROM b> GO>> I have created a user that has execute permissions for both procedures.> When I run procedure A, all works fine but when running procedure B I'm> getting an error saying that the user must have SELECT permissions on> table B.>> Both tables are owned by dbo, and the security role for the user doesn't> has any SELECT permission on table a and b.> I'd be grateful if anyone could point me in a direction why this error> might come up for procedure B but not for A,> with a possible solution without giving the user SELECT permissions.>> Thanks very much for your help!>> Martin>[/color]
View 6 Replies
View Related
Jul 31, 2015
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
View 3 Replies
View Related
May 7, 2007
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
View 3 Replies
View Related
Feb 21, 2007
Why this SQL procedure gives contiguous repeated records ( 3 or 4 times ) ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE GetProductsOnPromotInDep
(@DepartmentID INT)
AS
SELECT Product.ProductID, Title
FROM Product INNER JOIN
(ProductCategory INNER JOIN
(Category INNER JOIN Department ON Department.DepartmentID = Category.DepartmentID)
ON ProductCategory.CategoryID = Category.CategoryID)
ON Product.ProductID = ProductCategory.ProductID
WHERE Category.DepartmentID = @DepartmentID
AND ProductCategory.CategoryID = Category.CategoryID
AND Product.ProductID = ProductCategory.ProductID
AND Product.OnPromotion = 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
View 2 Replies
View Related
Dec 21, 2004
If I have a student table with 3 columns ID, FirstName and LastName
ID- FirstName -LastName
-------------------------
1 Tom Hanks
2 Jerry Thomas
and I have a subject table with two columns StudentID and Subject. StudentID is a foreign key to the student table.
StudentID - Subject
------------------------
1 History
1 Biology
2 History
If my query is
select distinct student.id, student.fname, student.lname, student.subject from student
left outer join subject on student.id = subject.studentid
then I get
1 Tom Hanks History
1 Tom Hanks Biology.
Is there a way I could get
1 Tom Hanks History
null null null Biology
--> meaning I don't want to repeat data??
thanks
Teena
View 1 Replies
View Related
Jun 19, 2006
How can find out the repeated character in each row value of spacific column
View 1 Replies
View Related
Dec 17, 2013
For the below mentioned query their is repetition of rows with the same data.
SELECT srs.prod_area as PA,
srs.art as ArtNo,
b.adsc_unicode as ArtName,
cast(case when a.unit <> '2' then c.avsx/10 else c.avsx/1000 end as integer(2)) as AwsMHS,
cast(srs.estimate/10 as integer(1)) as AwsSRSThisWeek,
[Code] ....
View 4 Replies
View Related
Mar 5, 2014
finding the solution for the below query?It displays repeated records.
select distinct ku.username,rro.role_name,rp.resource_type_code,kr.region_name,kc.currency_name,
fcr.cost_rule_id RULE,fcr.rate current_rate,pp.project_name,kou.org_unit_name ORG_UNIT
from
[code]....
View 2 Replies
View Related
Feb 5, 2008
Hi!
I am beginner on using SQL.
How can I select the repeated rows (five or more times) on a table which have the same ID's but different updated date. I do not need to group all the rows with the same ID,s but rows which are repeated many times according to the required reports needed.
Below are some information regarding tables and views:
VIEW name= dbo.tblCCInvoicesH
COLUMN ID= ConsumerID
COLUMN DATE= ReadingDate
Thank you in advance...
meti
View 1 Replies
View Related
Mar 10, 2008
Hi
I have created a Percent Log Used Alert with a threshold of 85% and am getting e-mailed with Database Mail, the problem is that I continue to get e-mailed repeatedly with the same e-mail until I disable the Alert. Is there any way to have it just e-mail it once?
Thanks
Pam
View 3 Replies
View Related
Feb 26, 2007
This is a problem I usually solve with procedural code, but I am wondering how/if it could be done with SQL queries.
A simple one to many query like:
Select inv.invnbr, inv.freight, invline.quantity, invline.partnbr, invline.cost from inv inner join invline on inv.id = invline.InvID
Returns something like:
invnbr freight quantity partnbr cost
100 50 3 abc 50
100 50 6 def 65
100 50 10 ghi 70
Is there way I can rewrite the query, or add a subquery such that the result set would be:
invnbr freight quantity partnbr cost
100 50 3 abc 50
100 0 6 def 65
100 0 10 ghi 70
Eg, the freight value, which comes from the one/header table, would show up in only one of the lines for invnbr 100?
Many thanks
Mike Thomas
View 9 Replies
View Related
May 21, 2007
The scenario is as follows:
I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.
SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z
The problem is that each contract can have multiple actions associated with it...
There ideal output would be:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80
Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..
Any help would be greatly appreciated.
Thanks,
Neil
View 4 Replies
View Related
Apr 15, 2004
Hi there :)
I am developing a system for my uni course and I am stuck a little problem...
Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.
I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.
How can I stop the repeats?
Heres my sql select code in my cs file:
string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id ";
SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection);
sqlConnection.Open();
lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection);
lecturersDG.DataBind();
And here is a pic of my Data Model:
Data Model Screenshot
Any ideas? Many thanks :) !
View 1 Replies
View Related
Sep 7, 2005
I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?
Ex.
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310
So i would like this data to come up as:
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310
View 7 Replies
View Related
Jul 20, 2005
I'm using the following query to look in a log file and show somestatistics. It counts the total number of views and the total numberof unique users who have viewed a particular item (the "id" and"title" fields are associated with the item).SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) ASNumberUniqueUsers, Type, idFROM ActivityLogWHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 200512:00AM'GROUP BY Title, Type, hdnIdORDER BY TopViewed descThis works fine on SQL Server 2000 (our development machine), but onSQL Server 7 (our production machine), the title column has the samevalue for every row. The other columns show the correct values.If I remove the "ORDER BY" clause, then it works fine. If I removethe "COUNT(DISTINCT UID)" column, it works fine. If I totally removethe WHERE clause, it works fine.Any ideas? It seems like a bug since it works fine on sql2k. I'vetried adding OPTION (MAXDOP 1) to the end of the query, but thatdidn't help.We're using SQL Server 7.0 sp1, and my boss doesn't want to riskupgrading to sp4 because it might screw up all of our otherapplications. I looked through all of the sp2 through sp4 bug fixes,and I didn't see anything specifically mentioning this.Thanks.
View 2 Replies
View Related
Sep 23, 2007
Hi Guys,
I am using the query below to retrieve these results:
You can see that the results are repeated, once for DATIF = 1 and then again for DATIF = 2.
In this case does not matter if the results appear close to DATIF 1 or DATIF 2.
Take in care that I can not know how may extradates or Extrasums are attached to each Account.
Is there any way to avoid these repeated rows?
Thanks in advance,
Aldo.
ACCOUNTKEY DATFID DATFNAME DATF SUFID SUFNAME SUF
--------------- ----------- -------------------------------------------------- ----------------------- ----------- -------------------------------------------------- ----------------------
123456 1 ExtraDates01 2005-01-01 00:00:00.000 1 ExtraSum01 4
123456 1 ExtraDates01 2005-01-01 00:00:00.000 2 ExtraSum02 3
123456 1 ExtraDates01 2005-01-01 00:00:00.000 3 ExtraSum03 1
123456 1 ExtraDates01 2005-01-01 00:00:00.000 4 ExtraSum04 2
123456 2 ExtraDates02 2004-01-01 00:00:00.000 1 ExtraSum01 4
123456 2 ExtraDates02 2004-01-01 00:00:00.000 2 ExtraSum02 3
123456 2 ExtraDates02 2004-01-01 00:00:00.000 3 ExtraSum03 1
123456 2 ExtraDates02 2004-01-01 00:00:00.000 4 ExtraSum04 2
Code Snippet
SELECT DISTINCT
Accounts.ACCOUNTKEY,
ExtraDates.DATFID,
ExtraDateNames.DATFNAME ,
ExtraDates.DATF ,
ExtraSums.SUFID ,
ExtraSumNames.SUFNAME ,
ExtraSums.SUF
FROM
EXTRADATES AS ExtraDates
LEFT OUTER JOIN EXTRADATENAMES AS ExtraDateNames ON ExtraDates.DATFID = ExtraDateNames.DATFID
RIGHT OUTER JOIN ACCOUNTS AS Accounts ON ExtraDates.KEF = Accounts.ACCOUNTKEY
LEFT OUTER JOIN EXTRASUMS AS ExtraSums
LEFT OUTER JOIN EXTRASUMNAMES AS ExtraSumNames ON ExtraSums.SUFID = ExtraSumNames.SUFID ON Accounts.ACCOUNTKEY = ExtraSums.KEF
LEFT OUTER JOIN EXTRANOTENAMES
RIGHT OUTER JOIN EXTRANOTES ON EXTRANOTENAMES.NOTEID = EXTRANOTES.NOTEID ON Accounts.ACCOUNTKEY = EXTRANOTES.KEF
WHERE
Accounts.SORTGROUP BETWEEN 0 AND 999999999
AND Accounts.ACCOUNTKEY BETWEEN '123456' AND '123456'
View 2 Replies
View Related
Mar 19, 2007
I have hundreds of these errors saying 'Login failed for user 'Reporting' The user is not associated with a trusted SQL Server connection [CLIENT: ip address]
The ip address is that of the server that sql server is installed on.
Looking in my log file, all looks good until I get to Service Broker manager has started, then I get Error: 18452, Severity: 14 State: 1 then these two lines repeat about every minute, for the last 3 days!
I think I must have just missed a tick box somewhere, but where?
I have been into one of the databases, and input and checked data, both via an application I wrote and SQL Server Management Studio.
I am also having trouble connecting using my application to connect to the database, I can only connect if I use a Windows administrator account (this SQL Server 2005 running on a Windows 2003 Server, with the app on PC running Windows 2000)
View 4 Replies
View Related
Jan 12, 2007
Hi,
i have a matrix with some fixed columns. when the fixed columns have repeated data, the repeated data is hidden.
how do you show the data all the time??
eg. i have:
Customer - Month - bikes - balls - toys - Total
Sam - January - 0 - 4 - 10 - 14
- February - 3 - 2 - 8 - 13
- March - 2 - 0 - 9 - 11
Tom - January - 2 - 5 - 2 - 9
- February - 5 - 2 - 8 - 15
- March - 1 - 6 - 3 - 10
but i want it to show like this:
Customer - Month - bikes - balls - toys - Total
Sam - January - 0 - 4 - 10 - 14
Sam - February - 3 - 2 - 8 - 13
Sam - March - 2 - 0 - 9 - 11
Tom - January - 2 - 5 - 2 - 9
Tom - February - 5 - 2 - 8 - 15
Tom - March - 1 - 6 - 3 - 10
Thanks.
View 3 Replies
View Related
Mar 7, 2007
Hi guys,
I have a matrix report with one row group and one dynamic column group. My issue is that I want to see the column group to be appeared only on the first page of the report not on every next page, because I am going to finally export the report to Excel spreedsheet so I don't want the column to be repeated in the middle of the records.
Let me know if anybody has some idea.
Thanks!
--Amde
View 1 Replies
View Related
Sep 14, 2007
Hi:
I am fairly new to SQL Server 2005 and before now, I have only had to restore databases, and connect to tables via ODBC connection in a reference (read only) setup. Today, I have a very small project to set up using the server.
I have a userlogon.csv file that the network stores on a file server in a hidden share logon$. It has 4 columns, UserID, Computer, Date, Time.
I was able to create a database called UserLogon and import the file as it was today. I want to create a scheduled update so the server would go to this file perhaps 4 times a day (or more) and grab any new logins that have appended itself to this CSV file.
So, as a newbie with a 1,900 page SQL Server 2005 unleashed manual at my side, could someone outline what the steps are in general I should follow to set this up?
I have the process laid out in my mind, but I don't know how to translate in into a scheduled task of the SQL Server :
1. Create DB and import the table (done)
2. create a stored procedure that connects to the CSV file and evaluates date and time stamps then appends any new records into the SQL db table. (appending records would be achieved by using the INSERT and WHERE statements?)
3. Schedule a job to perform this task on a routine basis.
It appears that the file connection portion of this set up is defined outside the evaluation and append record procedure? (not in the same stored procedure). Perhaps I tie the whole process together using the Job Manager, selecting the file settings, and then the stored procedure to be performed on the file.?
I hope I have been descriptive enough to ask if someone could outline the modules/features/processes involved so I can read up on them and figure them out using the book.
Thank you in advance.
David
View 1 Replies
View Related
Nov 10, 2006
i have telephone numbers in my table and i want to select based on numbe of repeated numbers.. for example..
39657777
39111132
36399991
The first has foue 7s, second has four 1s and the thrid has four 9s.
View 5 Replies
View Related
Feb 18, 2008
If I have this table
Mytable
IdnCol ColumnA ColumnB ColumnC ColumnD
1 CC DD EE FF
2 DD GG HH JJ
3 HH JJ KK HH
How I can check for the repeated value in the column.
for example in the 3rd record, in columnA and ColumnD the value HH is repeated
so is there a way to find if there are values repeated in any of the columnA ColumnB ColumnC ColumnD.
Thanks
View 4 Replies
View Related
Jun 14, 2006
Hi
Ihave a table With A Culomn That Is NOT Unique And Repeat Many Tims BUT I want To Have a Select Command That Filter Repeated Value And Show Only One of rows Whith Same Data
Thank u
View 1 Replies
View Related
Aug 26, 2014
I'm trying to come up with a query for this data:
CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
[Code] ....
Here are the expected results:
OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010
[Code] ....
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 1
ELSE 0
END AS isFirstVisit,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 0
ELSE 1
END AS isRepeatVisit
FROM#Visits
View 3 Replies
View Related
Sep 24, 2015
I have one table having three columns.This table contains lot of repeated records. I want to delete this records .
In this below example i want to delete all the records which columns id and no columns contains same values.
id no sequence
------------------------------------
35 35432 1
35 35432 2
35 35432 3
36 35432 1
35 45623 1
First three records the columns id and no contains same value. I want to delete this three records.
But in last record for id =35 and no column =45623.it is not repeated so it should not be deleted.
View 8 Replies
View Related
