Database

Eddy Vluggen wrote:

a difference between deleting a file from disk and a file from a database

Eddy Vluggen wrote:

whether they've got the appropriate rights to delete it

PIEBALDconsult wrote:

I'm pretty sure none of that has anything to do with the original question.

Bastard Programmer from Hell

IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
  BEGIN 
    INSERT INTO.....
  END

Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'
--Select * from @tblSource

Declare @key as varchar(10)
set @key  = 'Joy2'

if(( Select count([KEY]) from @tblSource where [key] = @key) = 1)
begin
insert into @tblSource Select 'NewName', 'NewKey'
end 

Select * from @tblSource

Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2' 

Declare @tblDestination table([ID] int identity,[Name] varchar(20),[Key] varchar(10))

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);	

Select * from @tblDestination

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2

insert into @tblSource Select 'Joy','Joy2' 

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2
3	Joy	Joy2

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
Select * from @tblDestination

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2

insert into @tblSource Select 'NewName','NewKey' 

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2
3	NewName	NewKey

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2
3	NewName	NewKey

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);

current1999 wrote:

· The total number of employees in the town.

current1999 wrote:

Your propositions have been tested without success.

select  mt.town , sum(mt.CompanySize) as TotalNrOfEmployees,
sum(mt.CompanySize)/(select count(*) from mytable) as AverageEmployeesPerTown,
sum(mt.CompanySize)/(select count(*) from mytable where companysize<20) as AverageEmployeesPerTownLessThen20
		
from mytable mt
group by mt.town
having (sum(mt.CompanySize) > 20)

order by ( sum(mt.CompanySize)) desc

<br />
Town            TotalNrOfEmployees     AverageEmployeesPerTown    AverageEmployeesPerTownLessThen20<br />
Texas	        53	               8	                  13<br />
Maryland	49	               8	                  12

Declare @t table(CompanyID int identity, CompanyName varchar(20), Town varchar(20), Country varchar(20), CompanySize int)
insert into @t 
select 'CompanyA', 'Maryland', 'USA', 10 union all
select 'CompanyB' , 'Maryland', 'USA', 19 union all
select 'CompanyC', 'Maryland', 'USA', 20 union all
select 'CompanyD' , 'Texas', 'USA', 13 union all
select  'CompanyE', 'Texas', 'USA', 40 union all
select 'CompanyE', 'Florida', 'USA' ,4
Select * from @t

Select 
	t2.Town
	, t1.[Total Employees In the Town]
	, t2.[Avg No of employees(per town)] 
	, t2.[AVG Less than 20]
from

-- Query1 : The total number of employees in the town
	(
		Select 
			[Total Employees In the Town] =  SUM(a.CompanySize)
		from @t a 
		join (Select Town From @t Group by Town Having Count(Town) > 1 ) x 
		on a.Town = x.Town
	) t1
left join 
(
		Select 
			a.Town
			,a.[Avg No of employees(per town)]
			,b.[AVG Less than 20]
		from
		(
			-- Query 2: The average number of employees in a company (per town).
			Select	Town,[Avg No of employees(per town)] = SUM(CompanySize)/COUNT(Town) 
			From @t 
			Group by Town Having Count(Town) > 1 ) a

		join (

				-- Query 3 
				--			The average number of employees in a company (per town) only for companies which
				--			have less than 20 employees

				Select a.Town, [AVG Less than 20] =  SUM(CompanySize)/Count(a.Town) 
				from @t a 
				join (Select Town From @t Group by Town Having Count(Town) > 1 ) x on a.Town = x.Town
				where a.CompanySize < 20
				group by a.Town) b
				on a.Town = b.Town
) t2 on 1 = 1

Town	Total Employees In the Town	Avg No of employees(per town)	AVG Less than 20
Maryland	102	                      16	                    14
Texas	        102	                      26	                    13