Finding Duplicates

I have a company table and I would like to write a query that will return to
me any duplicate companies. However, it is a little more complicated then
just matching on exact company names. I would like it to give me duplicates
where x number of letters at the beginning of the company name match AND x
number of letters of the address match AND x number of letters of the city
match. I will be doing this in batches based on the first letter of the
company name. So for example I will first process all companies that start
with the letter "A".

So for all "A" companies I want to find companies where the first 5 letters
in the company name match and the first 5 characters of the address field
match and the first 5 characters of the city match. THANKS!!!Can you post simplified DDLs, some sample data & expected results? For
detail refer to : www.aspfaq.com/5006

--
- Anith
( Please reply to newsgroups only )|||"Erich" <erich93063@.hotmail.com> wrote in message news:<102gbomj7gc84f7@.corp.supernews.com>...
> I have a company table and I would like to write a query that will return to
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me duplicates
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
> So for all "A" companies I want to find companies where the first 5 letters
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!!

Something like this may work:

select t.*
from dbo.MyTable t
join
(
select
left(CompanyName, 5) as 'CompName',
left(Address, 5) as 'Addr',
left(City, 5) as 'City',
count(*) as 'Dupes'
from
dbo.MyTable
where
left(CompanyName, 1) = 'A'
group by
left(CompanyName, 5),
left(Address, 5),
left(City, 5)
having
count(*) > 1
) dt
on dt.CompName = left(t.CompanyName, 5)
and dt.Addr = left(t.Address, 5)
and dt.City = left(t.City, 5)

If this doesn't work as you expect, then please consider posting your
table DDL, as well as some sample data.

Simon|||This may work as well, assuming a parameter is passed into the stored proc
or function for the first letter:

SELECT C1.CompanyID, C2.CompanyID,
C1.CompanyName, C2.CompanyName,
C1.Address, C2.Address, C1.City, C2.City
FROM Company C1 JOIN Company C2 ON
LEFT(C1.CompanyName, 5) = LEFT(C2.CompanyName, 5) AND
LEFT(C1.Address, 5) = LEFT(C2.Address, 5) AND
LEFT(C1.City, 5) = LEFT(C2.City, 5) AND
C1.CompanyID != C2.CompanyID
WHERE LEFT(C1.CompanyName, 1) = @.FirstLetter

You could also use a variable parameter instead of hard-coding "5" to allow
for more specific or more general matches.
ie, ... LEFT(C1.Company, @.MatchLength) = LEFT(C2.CompanyName, @.MatchLength)

"Erich" <erich93063@.hotmail.com> wrote in message
news:102gbomj7gc84f7@.corp.supernews.com...
> I have a company table and I would like to write a query that will return
to
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me
duplicates
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
> So for all "A" companies I want to find companies where the first 5
letters
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!!

Finding duplicate entries in a "smart" way - by comparing first two words

What is the best way to compare two entries in a single table where
the two fields are "almost" the same?

For example, I would like to write a query that would compare the
first two words in a "company" field. If they are the same, I would
like to output them.

For example, "20th Century" and "20th Century Fox" in the company
field would be the same.

How do I do this? Do I need to use a cursor? Is it as simple as using
"Like?"Here's an example:

CREATE TABLE Sometable (company VARCHAR(20) PRIMARY KEY)

INSERT INTO Sometable VALUES ('20th Century')
INSERT INTO Sometable VALUES ('20th Century Fox')
INSERT INTO Sometable VALUES ('Something Else')

SELECT company
FROM Sometable AS S
JOIN
(SELECT first_two_words
FROM
(SELECT company,
LEFT(company,
CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
AS first_two_words
FROM Sometable) AS X
GROUP BY first_two_words
HAVING COUNT(*)>1) AS T
ON T.first_two_words =
LEFT(company,CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))

--
David Portas
----
Please reply only to the newsgroup
--|||>> a query that would compare the first two words in a "company"
field [sic]. If they are the same, I would like to output them. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY,
company CHAR(35) NOT NULL,
...);

SELECT F1.foo_key, F2.foo_key
FROM Foobar AS F1, Foobar AS F2
WHERE CHARINDEX(F1.company, F2.company) = 1
AND F1.foo_key < F2.foo_key;

finding average earning over period of employment

It has been a while since I have had to write a really advanced query and I was wondering if I could get a little bit of help from someone

find the average monthly earning while you have been an affiliate

affiliate_id, transaction_dt, earnings are the only fields that you have to worry about. I can obviously find the average earnings for a period of a year but, I would like something like

spavgmonthlyearning affiliate_id

any help would be greatly appreciate even a point in the right direction would be fantastic.so this will do it.
is this pure sql or will it only run on sqlserver
thank you for your help

DECLARE @.maxdate as datetime
DECLARE @.mindate as datetime
set @.maxdate = (select max(TRANS_DT) from dbo.affiliate_log)
set @.mindate = (select min(trans_dt) from dbo.affiliate_log)
select sum(earning)/datediff(M,@.mindate,@.maxdate)
from dbo.affiliate_log where affiliate_id = 1|||select DATEPART(m,date_field), avg(sales)
from table
group by DATEPART(m, date_field)|||It depends on what you mean by "average".

The simple average monthly earning would just be the total earnings divided by the total months.

select affiliate_id,
sum(earnings)/datediff(month, min(transaction_dt), max(transaction_dt))
from yourtable
group by affiliate_id

Or if they skip some months:

select affiliate_id,
sum(earnings)/count(distinct convert(varchar(7), transaction_dt, 120))
from yourtable
group by affiliate_id

Find primary key columns

I'm trying to find out some information about the schema of a database so that I can write a smarter table browser for my application's admin interface. I need to get the primary keys of a table, but I can't seem to get them in a simple manner. I've tried using ASP.NET functionality (like GetSchemaTable() for instance) but it doesn't get all the information I want. I tried INFORMATION_SCHEMA.xxx but those views don't give me everything I need either. Here's what I need:

Table Name, Column Name, Column Position, Data Type, Domain Name (user defined data type name), Length (in characters for nvarchar/nchar please), Is it nullable, Is it computed, Referenced Table Name, Referenced Column Name, Is it part of the primary key

I enforce that the schema allows a column to reference at most one column in one other table, so the "referenced" columns will either be null or contain at most one value. I've constructed this so far:

select o.name TableName
, c.name ColumnName
, fko.name ReferenceTableName
, fkc.name ReferenceColumnName
, COALESCE(t.name, t.name) ColumnType
, c.prec ColumnLength
, c.isnullable IsNullable
, c.iscomputed IsComputed
, c.colid ColumnOrder
from syscolumns c
inner join sysobjects o on c.id=o.id
inner join systypes t on c.xusertype=t.xusertype
left outer join sysforeignkeys fk on c.id=fk.fkeyid and c.colid=fk.fkey
left outer join sysobjects fko on fk.rkeyid=fko.id
left outer join syscolumns fkc on fk.rkeyid=fkc.id and fk.rkey=fkc.colid
where o.xtype='U'

Where can I find out if a column is a primary key?I don't see a way for you to get all of the information you are looking for out of the Information_Schema views, either.

I normally get the "is this a primary key?" information out of the Information_Schema.Key_Column_Usage view joined on the Information_Schema.Table_Constraints view. I am not certain of the underlying sys tables (althought perusal of those views will obviously tell you). Below is some incomplete code showing how I'd join the KCU and TC views:

SELECT
 Tables.Table_Name AS [Table Name],
 Columns.Column_Name AS [Column Name],
 Columns.Ordinal_Position AS [Column Position],
 CASE WHEN TC.Constraint_Name IS NULL Then 'No' Else 'Yes' END AS [Part of the Primary Key]
FROM
 information_schema.tables AS Tables
LEFT OUTER JOIN
 information_schema.columns AS Columns ON Tables.Table_Catalog = Columns.Table_Catalog AND Tables.Table_Schema = Columns.Table_Schema AND Tables.Table_Name = Columns.Table_name
LEFT OUTER JOIN
 information_schema.key_column_usage AS KCU ON Tables.Table_Catalog = KCU.Table_Catalog AND Tables.Table_Schema = KCU.Table_Schema AND Columns.Column_Name = KCU.Column_Name
LEFT OUTER JOIN
 information_schema.table_constraints AS TC ON Tables.Table_Catalog = TC.Table_Catalog AND Tables.Table_Schema = TC.Table_Schema AND KCU.Constraint_Name = TC.Constraint_Name AND TC.constraint_type = 'PRIMARY KEY'

Terri|||Thanks for the idea to look at the INFORMATION_SCHEMA.KEY_COLUMN_USAGE directly! You can execute "sp_helptext [INFORMATION_SCHEMA.KEY_COLUMN_USAGE]" if you want to see how this view is defined. It uses an undocumented table master.dbo.spt_values, but it did lead me to the sysindexkeys and sysindexes table as I was looking for documentation of the spt_values table.

Here is my solution, for anyone who is interested. I use only explicitly documented columns of sysXXX tables, so that hopefully it might work in SQL Server versions other than SQL Server 2000.

select o.name TableName
, c.name ColumnName
, t.name ColumnType
, c.prec ColumnLength
, CONVERT(bit, COALESCE(pko.id, 0)) IsKey
, CONVERT(bit, c.isnullable) IsNullable
, CONVERT(bit, c.iscomputed) IsComputed
, c.colid ColumnOrder
, fko.name ReferenceTableName
, fkc.name ReferenceColumnName
from syscolumns c
inner join sysobjects o on c.id=o.id
inner join systypes t on c.xusertype=t.xusertype
left outer join sysforeignkeys fk on c.id=fk.fkeyid and c.colid=fk.fkey
left outer join sysobjects fko on fk.rkeyid=fko.id
left outer join syscolumns fkc on fk.rkeyid=fkc.id and fk.rkey=fkc.colid
left outer join sysindexkeys ik on c.id=ik.id and c.colid=ik.colid
left outer join sysindexes i on ik.id=i.id and ik.indid=i.indid
left outer join sysobjects pko on c.id=pko.parent_obj and i.name=pko.name
where o.xtype='U'

This assumes that each column references at most one other column. If you have a column that references more than one column, you'll get a row for each referenced column due to the left outer joins. It is ok for more than one column to be part of a primary key. ColumnType is either the user defined data type or the system defined data type if the column was not specified with a user defined data type. ColumnLength is the length in characters for nvarchar and nchar, NULL for text, ntext and image, and the length in bytes for all other types. The IsKey, IsNullable and IsComputed columns are converted to bit type so that ASP.NET can automatically treat them as Boolean values.

find out what tables contain a specific column

I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.

I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.

I would surely appreciate if someone else has already done this!

Thanks!

When I use the sysobjects/syscolumns to get info about a db I always do it in the DB I need info about, not in the master table.

use MyDatabase;
select * from sysobjects where name like 'someprefix%'

To get some info it works well but not recommended to use in production.

|||

SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b

ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_NAME LIKE 'TblName%'

AND b.COLUMN_NAME LIKE 'ColName%'

ORDER BY a.TABLE_NAME

|||Nope, this one yielded no results. I'll try the next one and let you all know. Thanks for trying!|||WONDERFUL! Just what I was looking for. Thank you so much!|||

Try this way. If you still don't get anything, then you have other "issues".

SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_NAME = b.TABLE_NAME
ORDER BY a.TABLE_NAME

Find object owner

Is there a query I can write that joins 2 system tables to return the owners of objects (e.g. tables) and the object's name?

Thanks,

Dave

IN SQL SERVER 200 the SCHEMA IN the information_VIEW represents the owner OF an objects e.g. FOR tables

SELECT TABLE_SCHEMA FROM [INFORMATION_SCHEMA].TABLES

IN SQL SERVER 2005, you will have TO determine the owner OF the SCHEMA instead using

SELECT Name FROM sys.Schemas

INNER JOIN sys.server_principals

ON [Schemas].principal_id = [server_principals].Principal_id

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

In SQL SERVER 2005, because of the ALTER AUTHORIZATION clause, the schema owner may not always be the object owner

See, http://msdn2.microsoft.com/en-us/library/ms187359.aspx

So, an alternative to the above query for SQL SERVER 2005 is

select user_name(objectproperty(object_id,'OwnerId')), name from sys.objects

This always returns the correct owner of the object and takes 'ALTER AUTHORIZATION' into account.

Find matching set of items

Without using a cursor or any kind of looping, I need to write a query that,
given one set of items, will tell me if there is another set that has the
same combination of items.
A good way to represent what I am trying to do would be, using Northwind,
and given a specific orderID, I want to know if any other orders have the
same combination of order items (did this customer buy the same combination
of products as any other customers).
I can't get my head around a way to do this relationally, given that it has
to match a set of items to other set items.
Any help is appreciated.
-DanHow would this other set be passed to the query?
And is this some sort of homework assignment?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:EB238306-7EC6-4525-BA84-86F970008B2D@.microsoft.com...
> Without using a cursor or any kind of looping, I need to write a query
that,
> given one set of items, will tell me if there is another set that has the
> same combination of items.
> A good way to represent what I am trying to do would be, using Northwind,
> and given a specific orderID, I want to know if any other orders have the
> same combination of order items (did this customer buy the same
combination
> of products as any other customers).
> I can't get my head around a way to do this relationally, given that it
has
> to match a set of items to other set items.
> Any help is appreciated.
> -Dan
>|||"Given a sepcific OrderID" is how the other set would be "passed" or rather
created. I have been working on ways to do it with joined derived tables or
corelated sub queries, but I haven't been able to come up with anything.
This isn't a homework assignment, I was just hoping someone else has had to
solve a similar problem relationally, and could at lease give me some advice
on how to approch the problem. Northiwind is just an easy way to represent
the problem since most people are familiar with the schema. It is not the
actual database that I'm working on.
"Adam Machanic" wrote:

> How would this other set be passed to the query?
> And is this some sort of homework assignment?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:EB238306-7EC6-4525-BA84-86F970008B2D@.microsoft.com...
> that,
> combination
> has
>
>|||This is a form of relational division, and as much as I hate to quote him, I
think Joe Celko's standard post on this topic is pretty good:
http://groups-beta.google.com/group...b1b2c
bb
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:49A4FBD9-9182-4FA4-AD02-B9DE62A0B948@.microsoft.com...
> "Given a sepcific OrderID" is how the other set would be "passed" or
rather
> created. I have been working on ways to do it with joined derived tables
or
> corelated sub queries, but I haven't been able to come up with anything.
> This isn't a homework assignment, I was just hoping someone else has had
to
> solve a similar problem relationally, and could at lease give me some
advice
> on how to approch the problem. Northiwind is just an easy way to represent
> the problem since most people are familiar with the schema. It is not the
> actual database that I'm working on.
>
>
> "Adam Machanic" wrote:
>
the
Northwind,
the
it

find and replace

how do i write a query that will go through all the rows of a text field and
replace the search word with a new word.
like the find and replace feature in MS Access
REPLACE "ABC" WITH "CBS" IN DBTABLE.DBTEXT
Thanks,
AaronHi,
Use Update statement with Replace function in SET clause. This will work out
if ur data type is
Varchar/Char/Nchar,Nvarchar.
Thanks
Hari
SQL Server MVP
"Aaron" <kuya789@.yahoo.com> wrote in message
news:uvdHyxNOFHA.3396@.TK2MSFTNGP10.phx.gbl...
> how do i write a query that will go through all the rows of a text field
> and replace the search word with a new word.
> like the find and replace feature in MS Access
> REPLACE "ABC" WITH "CBS" IN DBTABLE.DBTEXT
> Thanks,
> Aaron
>|||Could you give me an example?
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:Oormu$NOFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Use Update statement with Replace function in SET clause. This will work
> out if ur data type is
> Varchar/Char/Nchar,Nvarchar.
> Thanks
> Hari
> SQL Server MVP
> "Aaron" <kuya789@.yahoo.com> wrote in message
> news:uvdHyxNOFHA.3396@.TK2MSFTNGP10.phx.gbl...
>|||Aaron
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('FDABC')
UPDATE #Test SET col=REPLACE(col,'ABC','CBS')
--or if you know the start point and length of the string
UPDATE #Test SET col=STUFF(col,3,3,'CBS')
SELECT * FROM #Test
"Aaron" <kuya789@.yahoo.com> wrote in message
news:eSVOunOOFHA.2520@.tk2msftngp13.phx.gbl...
> Could you give me an example?
>
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:Oormu$NOFHA.904@.tk2msftngp13.phx.gbl...
field
>

Find a date that occurs every 2 days from a fixed date

Hi
I am trying to write a script that only sums the values in a column if the
date for the record occurs every 2 days after another date. The trouble is,
it is a recurring 2 day cycle. i.e Only sum the values if the record date is
every 2nd day from the first date.
eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
05/03, 07/04 etc. If the record does not occur every second day, it should
return 0.
I can use datediff to get the difference and I can cast it into a decimal
and divide by 2 but can't get it to validate in the statement.
Is there an easier way?Mark,
Post your DDL & T-SQL ?
Robert
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
> Hi
> I am trying to write a script that only sums the values in a column if the
> date for the record occurs every 2 days after another date. The trouble
> is,
> it is a recurring 2 day cycle. i.e Only sum the values if the record date
> is
> every 2nd day from the first date.
> eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
> 05/03, 07/04 etc. If the record does not occur every second day, it should
> return 0.
> I can use datediff to get the difference and I can cast it into a decimal
> and divide by 2 but can't get it to validate in the statement.
> Is there an easier way?
>|||Here is the short version of a complex query:
select guest.arrival, multirate.room_type, multirate.rate_date,
count (multirate.room_type)
from guest, multirate
where guest.property=multirate.property and guest.account=multirate.account
and
multirate.rate_date='xxxxx'
The problem is this:
Assume the guest.arrival='2006-03-01'
If the multirate.rate_date in the where statment is '2006-03-02', it must
ignore the record from the count because the datediff between 02/03 and 01/0
3
is not a multiple of 2.
If the multitrate.rate_date='2006-03-03', it must count the record into the
result because it occurs 2 days after the arrival.
The multirate.rate_date will be a variable passed to the statement, so the
query must be able to validate that the rate_date has a multiple of 2
difference between the rate_date and arrival.
Does this make sense?
"Robert Ellis" wrote:

> Mark,
> Post your DDL & T-SQL ?
> Robert
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
>
>|||Mark:
Does the following example help? There may be other ways to do it.
IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
CREATE TABLE blah
(
BlahGroup CHAR(2) NOT NULL,
FirstDate DATETIME NOT NULL,
SecondDate DATETIME NOT NULL
)
GO
SET DATEFORMAT YMD
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-02'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-06'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-07'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-06'
GO
SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
FROM blah b
WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2) =
(FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
GROUP BY b.BlahGroup
GO
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
> Here is the short version of a complex query:
> select guest.arrival, multirate.room_type, multirate.rate_date,
> count (multirate.room_type)
> from guest, multirate
> where guest.property=multirate.property and
> guest.account=multirate.account
> and
> multirate.rate_date='xxxxx'
> The problem is this:
> Assume the guest.arrival='2006-03-01'
> If the multirate.rate_date in the where statment is '2006-03-02', it must
> ignore the record from the count because the datediff between 02/03 and
> 01/03
> is not a multiple of 2.
> If the multitrate.rate_date='2006-03-03', it must count the record into
> the
> result because it occurs 2 days after the arrival.
> The multirate.rate_date will be a variable passed to the statement, so the
> query must be able to validate that the rate_date has a multiple of 2
> difference between the rate_date and arrival.
> Does this make sense?
> "Robert Ellis" wrote:
>|||Additionally:
obviously you would add something like:
AND (FirstDate = @.ParamDate)
to the 'WHERE' clause for your implementation...
cheers,
Robert
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>|||% modulus
argh
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>