|
That wouldn't be in the database, would it?
|
|
|
|
|
That wouldn't matter, would it?
Some people generally check every option, others trust in a form of exception-handling.
Bastard Programmer from Hell
|
|
|
|
|
|
Why does it matter to you? Do you make a difference between deleting a file from disk and a file from a database, from an exception-handling viewpoint?
It's a personal preference; lots of experienced programmers tend to check specific whether a file (or record) exists and whether they've got the appropriate rights to delete it (from disk or database). I prefer to rely on exception-handling, has always worked quite well for me.
Do you try-except, or do you return a customized boolean that consists of Yes , No and EFileNotFound ?
Bastard Programmer from Hell
|
|
|
|
|
I'm pretty sure none of that has anything to do with the original question.
Eddy Vluggen wrote: a difference between deleting a file from disk and a file from a database
Yes. The database will likely return "0 records affected". The file system may simply say "ok, it's gone" even if it didn't exist. Either way, I don't care, just aslong as it's gone.
Eddy Vluggen wrote: whether they've got the appropriate rights to delete it
That's a case where looking first may give inacurate information and you could wind up having to handle an Exception anyway, so I prefer to just try it and see.
|
|
|
|
|
PIEBALDconsult wrote: I'm pretty sure none of that has anything to do with the original question.
True.
Bastard Programmer from Hell
|
|
|
|
|
Hello SayamiSuchi,
This problem can be solved in many ways.
Method 1: As has already been answered by UNCRushFan
IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
END
Method 2: Another traditional way [ Count(ColumnName) approach ]
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
Method 3: Merge statement of Sql Server 2008
Consider the below statement first
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
I have a source table and a destination table. I am inserting the record in the destination table by checking whether the Key of the source table exists in the destination or not. In this case no key will be found in the destination and hence the output will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
Now let us insert a record (with an already existing key) in the source table as
insert into @tblSource Select 'Joy','Joy2'
At this time the records in the source table will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
3 Joy Joy2
Now if we run the above Merge statement query
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
The output in the destination table will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
because the key is already present in the destination table and hence it has been ignored.
But if we have the source table as
insert into @tblSource Select 'NewName','NewKey'
i.e.
ID Name Key
1 Sam Sam1
2 Joy Joy2
3 NewName NewKey
and then execute the above query, the output will be
ID Name Key
1 Sam Sam1
2 Joy Joy2
3 NewName NewKey
Because this time we are inserting a new key altogether.
So the query will be
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]);
N.B.~ This will work in Sql Server 2008 and above
Hope this helps
Thanks
Niladri Biswas
|
|
|
|
|
I'm building a site that will be out on the web that takes job applications. Within the admin portal I am using SSRS 2008 to display the information from the database to the HR users. How do I need to set up SSRS so that I can point to the url that displays the report (I am rendering the report in a PDF format through the url)?
|
|
|
|
|
You are going to have to set up an SSRS server if you want to use the URL method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
so, here is what I am doing so far: we have an SSRS server that is already up and running, so I put my report on that server so people in HR can get to the applications while at work. Now, we're dealing with multiple HR groups on different domains, so I want to place it on the web server so everyone can hit it but I am lost on how to configure SSRS to run on the run on the server. I can get it installed and up, but when I change the urls/virtual directories for SSRS it all goes to pot. What am I doing wrong?
|
|
|
|
|
Hello CP,
I've got a package in Sql Server 2005 (Integrated Services) and had a question about the useage of parameters.
I perform a right click on my package and pick 'Run Package'.
I go to the Set Values tab and put in my parameters like this:
Property path | Value
\package.variables[Dir].Value | C:\DBExports\
\package.variables[Month].Value | 4
Here's my problem:
I want to use a single package to export the current and past month.
Is there a datetime function or macro I can call in the Value field?
The first rule of CListCtrl is you do not talk about CListCtrl - kornman
|
|
|
|
|
Please can anyone assist me with the following questions?
1. Given the following table (the data could be hundreds of rows):
CompanyID CompanyName Town Country CompanySize
1 CompanyA Maryland USA 10
2 CompanyB Maryland USA 19
3 CompanyC Maryland USA 20
4 CompanyD Texas USA 13
5 CompanyE Texas USA 40
6 CompanyE Florida USA 4
For those towns which have more than one company in them, calculate (in one query):
· The total number of employees in the town.
· The average number of employees in a company (per town).
· The average number of employees in a company (per town) only for companies which
have less than 20 employees.
The results should exclude Florida and be sorted in descending order of the total number of
employees in the town.
I will be very grateful for your professional response.
Regards,
Current
Addendum
I forgot to mention that CompanySize is the number of employees
Thanks for your observation.
modified on Saturday, June 25, 2011 7:20 AM
|
|
|
|
|
current1999 wrote: · The total number of employees in the town.
Based on this point, I can't see any employee information, I guess exists a Employee table which is related with your sample,right?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Hi Blue_Boy,
Did you see my addendum?CompanySize is the number of employees.
Cheers
|
|
|
|
|
Look into using Group By Town and Sum(CompanySize) these will allow you to get the result you need. You may have yo do some filtering after the sum in which case look into Having sum() > #n
You weren't expecting someone to actually write the query for you were you!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks.
Your propositions have been tested without success.
Since the sample data is given, if can supply full query, there is no sin!
Cheers
|
|
|
|
|
If you need the code written for you then trundle across to rentacoder, we support people who want to learn how to write the code, NOT people who want us to do their work for them.
current1999 wrote: Your propositions have been tested without success.
Then show us what you have tried and we may be able to help you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
May be you allow others to contribute if you can't soften your words a bit,You footnote speaks for you!
cheers
|
|
|
|
|
Take note of the vaote applied to the messages in this thread, others are contributing. Also note that you are not getting a response that supplies you with the codz, this should also tell you that you are asking the wrong question or are on the wrong site.
We are here to help developers, not supply free services. Either do your work or be prepared to pay someone to do it for you. Oh and if you are doing this for your own entertainment then thank you, I find it entertaining education you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Agreed. The question screamed "homework" when I looked at it. Don't know (but might guess) whi univoted, but have a reasonably heavy 5.
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Looking at the balance I'd say the sultana just worked out that he can vote, thanks!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The question has "homework" written all over it. I notice he did eventually find a sucker willing to do it for him for free.
|
|
|
|
|
try this
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
This will give you result as
<br />
Town TotalNrOfEmployees AverageEmployeesPerTown AverageEmployeesPerTownLessThen20<br />
Texas 53 8 13<br />
Maryland 49 8 12
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thanks a million Blue_Boy.
It is good there is an exceptional person like you. Some have no clues, only to be throwing DIATRIBES.
Thanks a million Blue_Boy for sharing your knowledge.
Regards
modified on Monday, July 18, 2011 4:08 AM
|
|
|
|
|
Hope this helps
Input:
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
Query:
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
Output:
Town Total Employees In the Town Avg No of employees(per town) AVG Less than 20
Maryland 102 16 14
Texas 102 26 13
Thanks
Niladri Biswas
|
|
|
|
|