Full join problem...

Hi,
My scenario is that i do a full join for check after duplicates name with
the difference function.
The problem is that i want to set a group value for each probably
duplicates.
After that i can set a distinct or something.
Help.
Here are the result i want to get help with (group value column):
name,id,name,id,diff_value,group_value
Smyth 2 Smythe 1 4 ?
Smith 3 Smythe 1 4 ?
Smythe 1 Smyth 2 4 ?
Smith 3 Smyth 2 4 ?
Smythe 1 Smith 3 4 ?
Smyth 2 Smith 3 4 ?
Anderson 5 Andersson 4 4 ?
Anderzon 6 Andersson 4 4 ?
Andersson 4 Anderson 5 4 ?
Anderzon 6 Anderson 5 4 ?
Andersson 4 Anderzon 6 4 ?
Anderson 5 Anderzon 6 4 ?
For all Smith spelling a want to set a group_value 1,
for anderson group_value 2.
And so on.
Here are the ddl example.
Create Table table1
(
ID Integer Identity(1,1),
name varchar(50)
)
Insert Into table1 (name)
Values ('Smythe')
Insert Into table1 (name)
Values ('Smyth')
Insert Into table1 (name)
Values ('Smith')
Insert Into table1 (name)
Values ('Andersson')
Insert Into table1 (name)
Values ('Anderson')
Insert Into table1 (name)
Values ('Anderzon')
And the query:
select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as diff_value,'?'
as group_value from
table1 as a,table1 as b
where DIFFERENCE(a.name,b.name)>2 and a.id<>b.id
Thanx
// twHi
I am not sure why you are want all (a,b) and (b,a) tuples as they are
effectively duplicates?
If not then maybe you can use a.id as the grouping value
e.g.
select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as diff_value,a.id
as group_value
from table1 as a
left join table1 as b ON a.id<b.id
WHERE DIFFERENCE(a.name,b.name)>2
John
"tw" <tw@.tactics.se> wrote in message
news:OMkWVRSlFHA.3828@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My scenario is that i do a full join for check after duplicates name with
> the difference function.
> The problem is that i want to set a group value for each probably
> duplicates.
> After that i can set a distinct or something.
> Help.
> Here are the result i want to get help with (group value column):
> name,id,name,id,diff_value,group_value
> Smyth 2 Smythe 1 4 ?
> Smith 3 Smythe 1 4 ?
> Smythe 1 Smyth 2 4 ?
> Smith 3 Smyth 2 4 ?
> Smythe 1 Smith 3 4 ?
> Smyth 2 Smith 3 4 ?
> Anderson 5 Andersson 4 4 ?
> Anderzon 6 Andersson 4 4 ?
> Andersson 4 Anderson 5 4 ?
> Anderzon 6 Anderson 5 4 ?
> Andersson 4 Anderzon 6 4 ?
> Anderson 5 Anderzon 6 4 ?
> For all Smith spelling a want to set a group_value 1,
> for anderson group_value 2.
> And so on.
> Here are the ddl example.
> Create Table table1
> (
> ID Integer Identity(1,1),
> name varchar(50)
> )
> Insert Into table1 (name)
> Values ('Smythe')
> Insert Into table1 (name)
> Values ('Smyth')
> Insert Into table1 (name)
> Values ('Smith')
> Insert Into table1 (name)
> Values ('Andersson')
> Insert Into table1 (name)
> Values ('Anderson')
> Insert Into table1 (name)
> Values ('Anderzon')
>
> And the query:
> select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as diff_value,'?'
> as group_value from
> table1 as a,table1 as b
> where DIFFERENCE(a.name,b.name)>2 and a.id<>b.id
> Thanx
> // tw
>|||Thanx for the help.
But i want all "Smythe" spellings in the same group,
and all "Andersson" in the same.
Is that possible?
Now is the group value 1 and 2 for "Smith" and 4 and 5 for "Andersson".
// tw
"John Bell" <jbellnewsposts@.hotmail.com> skrev i meddelandet
news:OOVutGTlFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi
> I am not sure why you are want all (a,b) and (b,a) tuples as they are
> effectively duplicates?
> If not then maybe you can use a.id as the grouping value
> e.g.
> select a.name,a.id,b.name,b.id,DIFFERENCE(a.name,b.name) as
> diff_value,a.id as group_value
> from table1 as a
> left join table1 as b ON a.id<b.id
> WHERE DIFFERENCE(a.name,b.name)>2
> John
> "tw" <tw@.tactics.se> wrote in message
> news:OMkWVRSlFHA.3828@.TK2MSFTNGP12.phx.gbl...
>|||Hi
This may have already be posted but I can't be certain!
The group value for Smythe is 1
The group value for Smyth is 2
The group value for Andersson is 4
The group value for Anderson is 5
If you wish to remove the child group values then you could do
something like:
SELECT P.ParentName, P.parent_id, P.Childname, P.Child_id,
P.diff_value, P.GroupValue
FROM
( SELECT a.name as ParentName,a.id as parent_id,b.name as Childname,
b.id as Child_id,DIFFERENCE(a.name,b.name) as diff_value, A.id AS
GroupValue
from table1 as a
left join table1 as b ON a.id<b.id
WHERE DIFFERENCE(a.name,b.name)>2 ) P
WHERE P.parent_id NOT IN ( SELECT b.id FROM table1 as a
left join table1 as b ON a.id<b.id
WHERE DIFFERENCE(a.name,b.name)>2 )
But the ParentName will depend on the order of the ids.
John

Full Database backup size.....

When I take the full backup of the database the size of the backup file
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
AllenThe size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free
,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:

> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>

Full Database backup size.....

When I take the full backup of the database the size of the backup file
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
AllenThe size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>

Full Database backup size.....

When I take the full backup of the database the size of the backup file
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
Allen
The size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:

> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>
|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi
|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>

Full backup without change archive bit

Here's the scenario:
I set a maintenance plan for sqlserver:
monday morning : full backup
in 6 days rest : differential backup
Sometimes I need to make a full backup of some databases and send to client.
I've got a problem, when I have to make a full backup for example in friday
for someone , because all next differential backups from maintenance plan
are wrong.
(include only differences from friday - not from monday)
So I have a question. How to make a full backup without reset an archive
bit?.
Any idea ?
Thanks to all in advance for your worthy assistance,
Radoslaw PutekRadek
Can you perfom full BACKUP DATABASE on friday within another job ,I mean not
by maintenance plan?
"Radek" <SPAM_putti@.poczta.onet.pl> wrote in message
news:bnql59$77a$1@.atlantis.news.tpi.pl...
> Here's the scenario:
> I set a maintenance plan for sqlserver:
> monday morning : full backup
> in 6 days rest : differential backup
> Sometimes I need to make a full backup of some databases and send to
client.
> I've got a problem, when I have to make a full backup for example in
friday
> for someone , because all next differential backups from maintenance plan
> are wrong.
> (include only differences from friday - not from monday)
> So I have a question. How to make a full backup without reset an archive
> bit?.
> Any idea ?
> Thanks to all in advance for your worthy assistance,
> Radoslaw Putek
>|||> > Here's the scenario:
> > I set a maintenance plan for sqlserver:
> > monday morning : full backup
> > in 6 days rest : differential backup
> >
> > Sometimes I need to make a full backup of some databases and send to
> client.
> > I've got a problem, when I have to make a full backup for example in
> friday
> > for someone , because all next differential backups from maintenance
plan
> > are wrong.
> > (include only differences from friday - not from monday)
> >
> > So I have a question. How to make a full backup without reset an archive
> > bit?.
> > Any idea ?
> >
> > Thanks to all in advance for your worthy assistance,
> >
> > Radoslaw Putek
--
> Radek
> Can you perfom full BACKUP DATABASE on friday within another job ,I mean
not
> by maintenance plan?
>
--
I used my own backup procedures in jobs.
I have over 500 databases and these procedures make me :
SQL_20031029_FULL catalog
database1_full.bac
database2_full.bac
...
SQL_20031030_DIFF catalog
database1_diff.bac
database2_diff.bac
...
.....
And when i make a full backup of some databases in friday(for example) my
restore plan doesn't work.
Radoslaw Putek|||Radek
I'd go with writing stored procedure that accep parameter as database id.
If you were asked to backup particular database/or databases you just put
their id as parameter, that's all.
"Radek" <SPAM_putti@.poczta.onet.pl> wrote in message
news:bnqn9o$ht2$1@.atlantis.news.tpi.pl...
> > > Here's the scenario:
> > > I set a maintenance plan for sqlserver:
> > > monday morning : full backup
> > > in 6 days rest : differential backup
> > >
> > > Sometimes I need to make a full backup of some databases and send to
> > client.
> > > I've got a problem, when I have to make a full backup for example in
> > friday
> > > for someone , because all next differential backups from maintenance
> plan
> > > are wrong.
> > > (include only differences from friday - not from monday)
> > >
> > > So I have a question. How to make a full backup without reset an
archive
> > > bit?.
> > > Any idea ?
> > >
> > > Thanks to all in advance for your worthy assistance,
> > >
> > > Radoslaw Putek
> --
> > Radek
> > Can you perfom full BACKUP DATABASE on friday within another job ,I mean
> not
> > by maintenance plan?
> >
> --
> I used my own backup procedures in jobs.
> I have over 500 databases and these procedures make me :
> SQL_20031029_FULL catalog
> database1_full.bac
> database2_full.bac
> ...
> SQL_20031030_DIFF catalog
> database1_diff.bac
> database2_diff.bac
> ...
> .....
> And when i make a full backup of some databases in friday(for example) my
> restore plan doesn't work.
> Radoslaw Putek
>
>|||Radek,
As I understand it, your problem is that your diff backups will be based on that "extra" database
backup, something you don't want them to be. I'm fairly certain that you cannot do that, I'm afraid.
Detaching might be an option (but that is not fully online, of course).
This seems like a valid request for sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Radek" <SPAM_putti@.poczta.onet.pl> wrote in message news:bnql59$77a$1@.atlantis.news.tpi.pl...
> Here's the scenario:
> I set a maintenance plan for sqlserver:
> monday morning : full backup
> in 6 days rest : differential backup
> Sometimes I need to make a full backup of some databases and send to client.
> I've got a problem, when I have to make a full backup for example in friday
> for someone , because all next differential backups from maintenance plan
> are wrong.
> (include only differences from friday - not from monday)
> So I have a question. How to make a full backup without reset an archive
> bit?.
> Any idea ?
> Thanks to all in advance for your worthy assistance,
> Radoslaw Putek
>|||Ok
Now I see what you mean?
Perhaps detach a database will be an option but I really don't know another
solution
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OdR673snDHA.2272@.tk2msftngp13.phx.gbl...
> Radek
> I'd go with writing stored procedure that accep parameter as database id.
> If you were asked to backup particular database/or databases you just put
> their id as parameter, that's all.
>
> "Radek" <SPAM_putti@.poczta.onet.pl> wrote in message
> news:bnqn9o$ht2$1@.atlantis.news.tpi.pl...
> > > > Here's the scenario:
> > > > I set a maintenance plan for sqlserver:
> > > > monday morning : full backup
> > > > in 6 days rest : differential backup
> > > >
> > > > Sometimes I need to make a full backup of some databases and send to
> > > client.
> > > > I've got a problem, when I have to make a full backup for example in
> > > friday
> > > > for someone , because all next differential backups from
maintenance
> > plan
> > > > are wrong.
> > > > (include only differences from friday - not from monday)
> > > >
> > > > So I have a question. How to make a full backup without reset an
> archive
> > > > bit?.
> > > > Any idea ?
> > > >
> > > > Thanks to all in advance for your worthy assistance,
> > > >
> > > > Radoslaw Putek
> > --
> > > Radek
> > > Can you perfom full BACKUP DATABASE on friday within another job ,I
mean
> > not
> > > by maintenance plan?
> > >
> > --
> > I used my own backup procedures in jobs.
> > I have over 500 databases and these procedures make me :
> > SQL_20031029_FULL catalog
> > database1_full.bac
> > database2_full.bac
> > ...
> > SQL_20031030_DIFF catalog
> > database1_diff.bac
> > database2_diff.bac
> > ...
> > .....
> > And when i make a full backup of some databases in friday(for example)
my
> > restore plan doesn't work.
> >
> > Radoslaw Putek
> >
> >
> >
> >
>