Search Repeated Record And Give Suggestion As Most Repeated One

Sep 4, 2007

I have problem, i wanted a query which will search the duplicate and then give suggestion
most 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


ADVERTISEMENT

Testing For Repeated Value In The The Columns Record Sql

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

REPEATED RECORDS

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

Repeated Data

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

Repeated Character In String

Jun 19, 2006

How can find out the repeated character in each row value of spacific column

View 1 Replies View Related

Rows Repeated With Same Data

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

Display Repeated Records

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

SELECT The Repeated-same Rows

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

Repeated Alert E-mails

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

SQL Way To Suppress Repeated Values

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

Excluding Repeated Values In A Sum

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

Need To Avoid Repeated Data In A DataGrid

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

Removing Rows With Repeated Data

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

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 View Related

SQL 7: One Column's Value Is Repeated Throughout Entire Result Set

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

Tsql - Avoid Repeated Rows

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

Repeated Errors In SQL Server Log File

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

Matrix Show Repeated Data

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

Matrix Column Repeated On Every Page.

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

How To Setup A Repeated Update To A Table In SQL?

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

Select Based On Repeated Numbers

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

Select Command That Filter Repeated Value

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

A SQL Procedure To STOP Repeated Info Being Stored

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

SQL Server 2012 :: Finding First And Repeated Values

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

SQL 2012 :: Deleting Repeated Records In Table

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

Normalization - How To Get Repeated Data From Existing Table

Jul 8, 2014

What I need to know is how to get repeated data from a existing table, to normalized tables with out typing it...

Example:

---------------------
| ID |TEXT | TAGS |
---------------------
| 1 |text1|repeating|
| 2 |text2|repeating|
----------------------

How to normalization would be use 3 tables ,one for tags_id and tags, and another relating the tags_id to ID from the table above...but how does the code already know there is a tag called repeating?

View 3 Replies View Related

Power Pivot :: Calculate Repeated Values

Jun 15, 2015

I have one table

Customer ID; Sort of Transactions;Value
1234;Kit;1
1235;Kit;1
1236;Revenue;30
1235;Kit;2
1238;Revenue;10
1235 ;Kit;1

Looking for a formula for calculated column, which gives me sum of "Kit" for Customers

1234;Kit;1;1
1235;Kit;1;4
1236;Revenue;30;0
1235;Kit;2;4
1238;Revenue;10;0
1235 ;Kit;1;4

View 2 Replies View Related

DB Engine :: Repeated Warning - Columns With No Statistics

Nov 18, 2015

I am running on following environment - 

OS- Windows server 2012 
SQL - Windows SQL 2012 R2
Sharepoint 2010 SP2   

SQL has DB restored from earlier server. DB is quiet large in size because used with sharepoint.

Following steps have been followed on this restored DB - 

Maintenance Plan
Rebuild-Reorganize the indexes
Update Statistics

After above steps, query on Sharepoint table found performant. But after some delay/idol time(overnight) on server. Query takes much more(20X) time to execute. On running execution plans observed that some warnings are seen on columns which are primary keys. 

Columns with no statistics 'AllDocs.tp_DocID'

When Update statistics is executed again in SQL management studio above issue is again seen resolved, but came again after some delay.

Is there any SQL logs where can I find activities performed during overnight with SQL which make this issue to happen? This issue was not there on Win2k8 environment.

View 6 Replies View Related

SQL Server Managment Studio Repeated Logins

Jan 11, 2008



Presumably an easy question but every time I open a query file the SSMS requires me to login. Very frustrating. Cannot find a configuration option, properties attribute or other to turn this off. Can anyone adivse how I can set up SMSS to require a login at the beginning of the session and then not again for each saved query I open?

Thanks.

View 3 Replies View Related

HELP!!! Creating Nested Data In Repeated Region Problem

Jul 28, 2007

Hi All, Would appreciate some assistance with the attached, I'm using some fairly complicated recordsets (ASP VBScript) for a property search website, I have a page where a user selects search by County this in turn takes them to a page which lists all the towns in the county and number of live properties in each, I'm using 4 recordsets for this - 3 of which list the towns (townsA2I, townsJ2R & townsS2Z) the 4th is a Property Count, examples of the recordsets are below (using townsAtoI and LiveProperties -

TownsA2I

<%
Dim TownA2I
Dim TownA2I_numRows

Set TownA2I = Server.CreateObject("ADODB.Recordset")
TownA2I.ActiveConnection = MM_recruta2_STRING
TownA2I.Source = "SELECT towncountyID, Town, County FROM dbo.easytolettowncounty WHERE Town LIKE 'A%' AND County = '" + Replace(TownA2I__MMColParam, "'", "''") + "' OR Town LIKE 'B%' AND County = '" + Replace(TownA2I__MMColParam1, "'", "''") + "' OR Town LIKE 'C%' AND County = '" + Replace(TownA2I__MMColParam2, "'", "''") + "' OR Town LIKE 'D%' AND County = '" + Replace(TownA2I__MMColParam3, "'", "''") + "' OR Town LIKE 'E%' AND County = '" + Replace(TownA2I__MMColParam4, "'", "''") + "' OR Town LIKE 'F%' AND County = '" + Replace(TownA2I__MMColParam5, "'", "''") + "' OR Town LIKE 'G%' AND County = '" + Replace(TownA2I__MMColParam6, "'", "''") + "' OR Town LIKE 'H%' AND County = '" + Replace(TownA2I__MMColParam7, "'", "''") + "' OR Town LIKE 'I%' AND County = '" + Replace(TownA2I__MMColParam8, "'", "''") + "' ORDER BY Town ASC"
TownA2I.CursorType = 0
TownA2I.CursorLocation = 2
TownA2I.LockType = 1
TownA2I.Open()

TownA2I_numRows = 0
%>

LiveProperties

<%
Dim LiveProperties
Dim LiveProperties_numRows

Sub sLivePropertyCount(vLocation)
vCount=0

Set LiveProperties = Server.CreateObject("ADODB.Recordset")
LiveProperties.ActiveConnection = MM_recruta2_STRING
LiveProperties.Source = "SELECT COUNT(PropertyID) As NumberofProperties, propertylive, propertylocation FROM dbo.easytoletproperty WHERE propertylive = 'y' AND propertylocation = " & vLocation & " GROUP BY propertylocation, propertylive"
LiveProperties.CursorType = 0
LiveProperties.CursorLocation = 2
LiveProperties.LockType = 1
LiveProperties.Open()

vCount=(LiveProperties("NumberofProperties"))
Response.write(vCount)

LiveProperties.Close()
Set LiveProperties = Nothing
end sub

LiveProperties_numRows = 0
%>

I've tried following the tutorial here http://www.webthang.co.uk/tuts/tuts_dmx/rob9/rob9.asp but when i review the page i get the following error -

"Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'Barnet'.

/PropertiesbyTown1.asp, line 338 "

Where line 338"

Where line 338 is the "LiveProperties.Open()" of the above recordset, when i try to view bindings on this recordset Dreamweaver gives me the following errors -

"Column 'dbo.easytoletproperty.propertylive' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Column 'dbo.easytoletproperty.propertylocation' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

I would appreciate any assistance -

View 1 Replies View Related

SQL 2012 :: CheckSum Agg Function Returns 0 For Even Number Of Repeated Values?

Aug 14, 2014

From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?

For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.

declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035

However, the function appears to returns 0 for an even number of repeated values.

declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0

It's not specific to -7077, for example:

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0

What's curious is that (3) repeated equal values will return a checksum > 0.

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777

But a set of (4) repeated equal values will return 0 again.

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0

Finally, a set of (2) uneuqal values repeated twice will return 0 again.

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0

View 0 Replies View Related

Table Column Headers Are Not Repeated On Each Page When A Row Spans Pages

Jul 5, 2007

If a single row of data spans a page then the column header is not repeated until the next row on the next page.

Is there a way to overcome this?

View 1 Replies View Related

Comparing Similar Tables - Removing Duplicate Or Repeated Data

Sep 30, 2007

It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.

I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.




Code Block
SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity
FROM BOOKED
LEFT JOIN
CURRENT
ON booked.item = current.item







No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.

As follows:







Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
A01
7680

12345
C01
20
A01
7680

54321
G10
1032
E15
1163

54321
G10
1032
F20
523

54321
G10
1032
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
Y15
3000

What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.

This is what I'm after:







Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
B01
0

12345
C01
20
C01
0

54321
G10
1032
E15
1163

54321
F20
0
F20
523

54321
H30
0
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
X10
0



Is this possible? If so, how?

I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.

-----------------------------------------------
Just thinking out loud here:
What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:




Code Block
SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER

FROM

(select item, bin, quantity
from BOOKED)q1
Left Join

(select item, bin, quantity
from CURRENT)q2
on q1.item = q2.item
Left Join

(select bin, item
from BOOKED
UNION
CURRENT)q3
on q1.item = q3.item

Order By q1.item





I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...


Any suggestions?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved