Easy aggregation question

Given this table
CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10), Operator
char(2))
which has multiple rows for each ExpSetNo, I want to find each ExpSetNo that
has only one TableAlias among all of the same ExpSetNo rows and which has
'CC' as the Operator in all those rows.
I would only want to return ExpSetNo 1 from this data:
INSERT SearchFieldDetail
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 2, 'COMCF', 'CC'
UNION
SELECT 2, 'COC', '='
UNION
SELECT 3, 'COC', 'CC'
UNION
SELECT 3, 'COMCF', 'CC'
And here's what I'm doing.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
END) = 0
DROP TABLE SearchFieldDetail
This works, but are there any suggestions for improvement?> I want to find each ExpSetNo that
> has only one TableAlias among all of the same ExpSetNo rows
In this case, you need to add DISTINCT to your COUNT function like the
example below. Also, I believe your sample data insert query needs to
specify UNION ALL rather than UNION.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0
ELSE 1
END) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:xqadnVdqk_RebmvcRVn-sg@.sti.net...
> Given this table
> CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10),
> Operator
> char(2))
> which has multiple rows for each ExpSetNo, I want to find each ExpSetNo
> that
> has only one TableAlias among all of the same ExpSetNo rows and which has
> 'CC' as the Operator in all those rows.
> I would only want to return ExpSetNo 1 from this data:
> INSERT SearchFieldDetail
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COC', '='
> UNION
> SELECT 3, 'COC', 'CC'
> UNION
> SELECT 3, 'COMCF', 'CC'
> And here's what I'm doing.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
> END) = 0
> DROP TABLE SearchFieldDetail
> This works, but are there any suggestions for improvement?
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eCvRsJ1AFHA.2104@.TK2MSFTNGP14.phx.gbl...
> In this case, you need to add DISTINCT to your COUNT function like the
> example below. Also, I believe your sample data insert query needs to
> specify UNION ALL rather than UNION.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN
0
> ELSE 1
> END) = 0
Excellent, Dan - thanks. A good example of bad test data (created without
UNION ALL) giving misleading results.

dynamically delete data

Hi All,
I have the following situation.
Every month, I populate data from a source table.
This table has a field called process_date (char data type) and the
format is mmyy. So, 0406 means data for the month of April of 2006.
This source table always overlaps with old data. For example, for this
month it may have data for January, February or March of 2006, which I
already have processed.
What I do presently is I manually run a delete command and then insert
in the target table.
Such as:
delete Table1 where Process_Date<>'0406'
I want to make this automated so that I will not have to manually run
the above code.
I was wondering how could I achieve that?
I will highly appreciate your help.
Thanks a million in advance.
Best regards,
MamunHello Mamun,
You could create a SQL Server Agent job to run every month. This job
can execute the T-SQL statements you require to insert/delete the
required data and won't require any intervention by you (although you
should be checking that whenever the job executes it executes
successfully).
If you're new to creating SQL Server Agent jobs then SQL Server Books
Online should be able to run you through the process.
HTH,
Nate.
mamun wrote:
> Hi All,
> I have the following situation.
> Every month, I populate data from a source table.
> This table has a field called process_date (char data type) and the
> format is mmyy. So, 0406 means data for the month of April of 2006.
> This source table always overlaps with old data. For example, for this
> month it may have data for January, February or March of 2006, which I
> already have processed.
> What I do presently is I manually run a delete command and then insert
> in the target table.
> Such as:
> delete Table1 where Process_Date<>'0406'
> I want to make this automated so that I will not have to manually run
> the above code.
> I was wondering how could I achieve that?
> I will highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...
Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = GETDATE(); #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Try this ...
DECLARE @.tname VARCHAR(30), @.sql VARCHAR(2000)
SET @.tname = CONVERT(CHAR(8),GETDATE(),112)-- remove the spaces
SELECT @.sql = 'CREATE TABLE dbo.##' + @.tname + ' (mycol CHAR(10))' -- needs
to be global or permenant
SELECT @.sql
EXECUTE (@.sql)
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = GETDATE(); #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>|||Why would you need dynamically named and/or dynamically created objects? How
are you planning on using them?
This is possible, but hardly recommended. There are better options and if
you post more information we can help you design a much more efficient
solution.
But if you insist on creating SQL objects dynamically you should read this
great article on dynamic SQL by Erland Sommarskog:
http://www.sommarskog.se/dynamic_sql.html
ML
http://milambda.blogspot.com/

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:
> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamic sql with char(39)

hi,
What's the pros and cons for the following two methods
when you define charactor strings in a dynamic sql?
1.
SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
+ '000000' + char(39) ...
2.
SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
they both work, I personally prefer second method, what do
you think?
many thanks!!
JJ
I use the second method most of the time. But occassionally when I have
some complex and requires many single qoute, and I am having problems with
the quoting I will consider using the char(39).
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
> hi,
> What's the pros and cons for the following two methods
> when you define charactor strings in a dynamic sql?
> 1.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
> + '000000' + char(39) ...
> 2.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
> they both work, I personally prefer second method, what do
> you think?
> many thanks!!
> JJ
>
|||thanks Gregory! so there is no performance or reliability
difference between the two?
JJ
>--Original Message--
>I use the second method most of the time. But
occassionally when I have
>some complex and requires many single qoute, and I am
having problems with
>the quoting I will consider using the char(39).
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
+ ''000000'' ...[vbcol=seagreen]
do
>
>.
>
|||Or you can use this #3.
SELECT @.EXPORT_VIEW_SQL = 'SELECT ' + quotename('000000',char(39))
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
> hi,
> What's the pros and cons for the following two methods
> when you define charactor strings in a dynamic sql?
> 1.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
> + '000000' + char(39) ...
> 2.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
> they both work, I personally prefer second method, what do
> you think?
> many thanks!!
> JJ
>