Sql Comparing two tables: multiple

Showing posts with label multiple. Show all posts

Thursday, March 29, 2012

Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissql sql

Concatenate multiple reports into one pdf

Is there any way to do the above? I need to supply a number of "management accounts" reports individually during the month, and then the accountant needs to run them all into a single pdf at the end of the month for the board report.

Thanks in advance

Combining reports into one report directly is not a current feature. One option you could try would be to create a master report that uses the Sub Report feature of RS. A simplistic view would be to create the master report that looks like Subreport\Pagebreak\Subreport\Pagebreak\etc...

|||Thanks - I had a feeling that was the case. I have lots of info in the header, so I will have to strip that out.

Concatenate Multiple Records Into One Field

Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

TABLE
ColumnA ColumnB
1......A
2......B
3......C
4......A
5......A
6......B
7......C
8......D
9......C
10.....E

EXPECTED OUTPUT
ColumnA ColumnB
1......4,5
2......6
3......7
4......1,5
5......1, 4
6......2
7......3
8......
9......3,7
10.....create function ConcatFld (@.RowId int, @.RowVal char(1))
returns varchar(100) AS
begin
declare @.Ret varchar(100)
set @.Ret=''
select @.Ret= @.Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@.RowVal and ColA<>@.RowId
if len(@.Ret) > 0
set @.Ret = left(@.Ret,len(@.Ret)-1)
return @.Ret
end
--------

select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1|||Thanks Upalsen, this is exactly what I need!!!

concatenate multiple fields

I don't have any troubles concatenating two fields together but so far, I
haven't been able to concatenate more than two fields.
(Order.FirstName+' '+order.LastName) as Name
will work
(Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
will not work.
Any suggestions?I can't think of any reason that shouldn't work.
What is the exact error?
Is this a problem you see in Query Analyzer or
via your "client" code such as ASP or ASP.NET?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"jeff fisher" <jeff@.fisher.com> wrote in message
news:eWY6$uvlFHA.3120@.TK2MSFTNGP09.phx.gbl...
>I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?|||If by "not work" you mean the seconds example is NULL, then it's because the
middle name field is NULL -- concatenating any char field with NULL yeilds
NULL; mathmatical, bitwise, and other operations have similar behavior.
Anyways this is a display issue and would be better handled by your client
code, but for some solutions, in BOL look up:
- ISNULL
- COALESCE
- SET CONCAT NULL YIELDS NULL
"jeff fisher" wrote:

> I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
What the heck does "will not work" mean' Would you like to go to a
doctor that tells you something like that? Why did you use a reserved
word for a table name'
My guess -- based on absolutely nothing you told us -- is that you
have NULL-able columns and do not know that NULLs propagate, one of the
most basic priniciples in SQL.

Concatenate Columm Values from multiple Rows into a single col

Yes, the order is not guaranteed.
ML
http://milambda.blogspot.com/ML (ML@.discussions.microsoft.com) writes:
> Yes, the order is not guaranteed.
Not even that. You are not even guaranteed to get all rows. For 1, 2, 3, 4
you could get '1,2,3,4' or you could get only '4'.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That would make the function completely useless - could you give an example,
please? I've tested it with a few typical set-ups and have always found it t
o
return expected results.
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> That would make the function completely useless - could you give an
> example, please? I've tested it with a few typical set-ups and have
> always found it to return expected results.
Check out http://support.microsoft.com/default.aspx?scid=287515, and pay
particular attention to the first sentence under CAUSE.
Nevermind that the article then bend over backwards, to specify things that
may work. For me the conclusion is clear: don't rely on this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree using functions in the ORDER BY clause in this case is a disaster
waiting to happen, but since my function does not use them at all, were you
able to reproduce the problem anyway?
If you're too busy to play with this, I absolutely understand. I'm just
trying to learn new things every day. I promise I'll stop a few days after
I'm dead. ;)
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> I agree using functions in the ORDER BY clause in this case is a
> disaster waiting to happen, but since my function does not use them at
> all, were you able to reproduce the problem anyway?
My point is not that I can reproduce it here and now. My point is that
what works today, could break tomorrow.
For instance, there are people out there who have defined views in
SQL 2000 which goes:
SELECT TOP 100 PERCENT
..
ORDER BY
and they are happy because when they say:
SELECT * FROM view1
the see the data in order.
Then they move to SQL 2005 and get hit, because the optimizer is now
less likely to return the data in order. The truth was all the time
that without an ORDER BY, the order of the data is undefined.
See also
http://lab.msdn.microsoft.com/produ...b9-3dd863ae6b1c
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This bug report clears up the matter greatly. Thank you very much. I intend
to include this example in my blog as a warning ASAP.
I've searched the web for this issue, but found no usable references. Thanks
again.
ML
http://milambda.blogspot.com/

Tuesday, March 27, 2012

Concat tables into one row in view

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.

What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail

I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.

table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

thx
M@.M@. (mattcushing@.gmail.com) writes:

Quote:

Originally Posted by

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.
>
What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail
>
I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.
>
table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

SQL Server MVP Anith Sen has a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql sql

Sunday, March 25, 2012

Computed Fields/Multiple Datasources

I am getting started with Reporting Services.
I am having trouble with computed fields. I have two issues:
1. I have a special dataset that I use to read parameters from a
database table. This table has only one row. I would like to add a
computed field that divides one of the columns in this row by another.
I created a computed field called ProRatedMultiplier with the following
definition:
=Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
The problem is that when I insert this computed field into my report,
it wants to sum the output:
=Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
If I remove the SUM() function, I get compile errors.
I need to use this computed value in many places on the report and thus
would like to have the formula defined only once (instead of repeating
it in each field).
It seems SRS sees it as returning one or more rows and thus wants to
aggregate. Am I going about this the wrong way? Can I make any changes
to make this work?
2. Assuming I get the above to work, I will have this new computed
field on my dataset. I would like to create a computed field on another
dataset that uses this first computed field value.
This new, second computed field would multiply the first computed field
value (which is a pro-rata multiplier) by the sum() aggregate of a
column in the second dataset.
The definition would like something like this:
=SUM( Fields!INVOICEAMOUNT.Value) *
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
This also will not compile.
Am I going about this the wrong way?For #1:
Use the First aggregate function:
=First(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
So even if your InputDataDS dataset has more than one rows (for whatever
reasons), you will still get the expected result. Since you use the
calculated field value in another dataset, you cannot omit the aggregate
function (otherwise the fields collection would be scoped to the wrong
dataset).
For #2:
You could write an expression in a textbox like this:
=SUM( Fields!INVOICEAMOUNT.Value) * First(
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
However, you cannot define a calculated field which uses aggregate
functions.
Probably a better solution for #1 & #2:
Since you have only 1 row - did you look into hidden textboxes, which
calculate the formulas instead of calculated fields? You could then
reference the value of the formula textbox in other textboxes by using an
expression like
=ReportItems!FormulaTextbox.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message
news:chqjea$pq0@.odak26.prod.google.com...
> I am getting started with Reporting Services.
> I am having trouble with computed fields. I have two issues:
> 1. I have a special dataset that I use to read parameters from a
> database table. This table has only one row. I would like to add a
> computed field that divides one of the columns in this row by another.
> I created a computed field called ProRatedMultiplier with the following
> definition:
> =Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
> The problem is that when I insert this computed field into my report,
> it wants to sum the output:
> =Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
> If I remove the SUM() function, I get compile errors.
> I need to use this computed value in many places on the report and thus
> would like to have the formula defined only once (instead of repeating
> it in each field).
> It seems SRS sees it as returning one or more rows and thus wants to
> aggregate. Am I going about this the wrong way? Can I make any changes
> to make this work?
> 2. Assuming I get the above to work, I will have this new computed
> field on my dataset. I would like to create a computed field on another
> dataset that uses this first computed field value.
> This new, second computed field would multiply the first computed field
> value (which is a pro-rata multiplier) by the sum() aggregate of a
> column in the second dataset.
> The definition would like something like this:
> =SUM( Fields!INVOICEAMOUNT.Value) *
> Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
> This also will not compile.
> Am I going about this the wrong way?
>

Monday, March 19, 2012

Composite Index Question

I have a rather large table that is queried in many different ways but
only has 6 fields. My question is: Is it better to have multiple
composite indexes that are tailered specifically to each of the queries
that are run? or is it better to have a few single column indexes?
I am using SQL SErver 2000.
Thanks in advance.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!As always, it depends. I'd suggest putting together a test script of all of
the queries your table is going to get and run that script through the Index
Tuning Wizard. That will be a good first step toward getting your indexing
down. After that, I'd revisit your queries, to see if any rewrites will
coax a bit more speed.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Debbie" <anonymous@.email.com> wrote in message
news:%23QCUGHWWEHA.4048@.TK2MSFTNGP12.phx.gbl...
I have a rather large table that is queried in many different ways but
only has 6 fields. My question is: Is it better to have multiple
composite indexes that are tailered specifically to each of the queries
that are run? or is it better to have a few single column indexes?
I am using SQL SErver 2000.
Thanks in advance.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Composite index design question

I have converted a database to support multiple clients by adding a ClientID
(int) to most of the 200 tables. This is to enable an application to use a
single database for multiple clients, separating the data via the ClientID
Where the primary key was OrderID, or ProductID, it is now ( ClientID,
OrderID) and (ClientID, ProductID) respectively.
The majority of clustered indexes are composite and have ClientID as the
first column. This of course means most of the foreign key indexes have
ClientID as the first column in the index. I even added ClientID to most
other nonclustered indexes since I know that ClientID will be in every join
and specified in the WHERE clause of every query.
It occurred to me recently that this database may have hundreds of thousands
of Orders and Products, but will likely only have 30-50 unique values of
ClientID.
This being the case, I would assume I will get better performance if the
indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
ClientID) ? The database tables are not sufficiently large at this point to
make a difference, but with 30 clients they will hit the 100K mark in a shor
t
period of time.Hi
When you create a composite index on columns SQL Server keeps statistics on
only one (first) column. So make sure that this column is selective enough.
http://www.sql-server-performance.c...ite_indexes.asp
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>|||Consider seperate non-composite indexes for ClientID and ProductID, since
this will provide more flexibility when generating an execution plan. Also,
consider not clustering on ClientID. With 50 unique clients using the
database, clustering will result in additional I/O and page fragmentation.
I generally avoid using composite columns except on clustered indexes, and I
generally avoid clustered indexes except on data warehouse tables where
there is a large amount of fairly static data that is frequently grouped or
sorted in mass for reporting purposes. There is little advantage to
clustering indexes in an OLTP database, unless it is on a bookmarked id
column like Product.ProductID.
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>

Sunday, March 11, 2012

Complicated SQL Select help needed

Hi,

My users table contains a field called researchInterestId which looks like this: 1, 5, 10

This is because users where allows to select multiple options when choosing their research interests.

I have another table which contains the names of those research interests, which looks like this:

researchInterestId researchInterestName

1 Biology

2 Cancer

My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10)

Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called?

Thanks

Sam

You can create a function as shown here:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx in the comments and use it to join with the researchinterests table to get the description.

|||

It worked perfectly, thanks so much! Smile

Thursday, March 8, 2012

complexity of nested self left joins?

Hi,

I'm curious about the computational complexity of a query I have. The
query contains multiple nested self left joins, starting with a simple
select, then doing a self left join with the results, then doing a self
left join with those results, etc. What puzzles me is that the time
required for the query seems to grow exponentially as I add additional
left joins, which I didn't expect. I expected the inner select to
return about 25 rows (it does), then I expected the self join to result
in about 25 rows (it does), etc. Each join just adds another column; it
doesn't add more rows. So the left part of the join is staying the same
size, and so is the right part of the join, since I'm always joining
with the same table.

So I would think the time for this query should be (time to join 25
rows against the source table) * (num joins), but it seems to be
something like (num rows) ^ (num joins). Any ideas? I'm just trying to
understand the system a little better. (But if you have any ideas about
improving the query, I'm always open to those, too.)

The execution plan is what you'd expect: an index seek loop-joined with
another index seek, the results of which are merge-joined with another
index seek, the results of which are merge-joined with another index
seek, ad nauseum, until a final "compute scalar cost (39%)" and "select
(0%)"

For the brave and curious, I've pasted the query below.

Thanks

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as
[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as
[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as
[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as
[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as
[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((select
distinct cp_yyyymm from cp_deal_history where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymm
between 200304 and 200504) as x left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') as
table0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1
on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2
on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3
on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4
on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5
on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6
on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7
on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8
on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9
on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') as
table10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymmFor those of you who's cranial parser requires liberal use of indents
and line breaks, I spent about an hour cleaning this up.

BTW, did you write this query to experiment with running times or are
you actually trying to accomplish something useful?

left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFCM9704') as table0
on x.cp_yyyymm=table0.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFC9104') as table1
on x.cp_yyyymm=table1.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT01C') as table2
on x.cp_yyyymm=table2.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT02B') as table3
on x.cp_yyyymm=table3.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03D') as table4
on x.cp_yyyymm=table4.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03E') as table5
on x.cp_yyyymm=table5.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03F') as table6
on x.cp_yyyymm=table6.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04A') as table7
on x.cp_yyyymm=table7.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04C') as table8
on x.cp_yyyymm=table8.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04D') as table9
on x.cp_yyyymm=table9.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT99C') as table10
on x.cp_yyyymm=table10.cp_yyyymm
order by x.cp_yyyymm|||Still not entirely sure what you're trying to accomplish, but -- if
you're doing what I think you are -- why not try something like this:

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C],
from (select distinct cp_yyyymm
from cp_deal_history
where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C',
'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C',
'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as x
left outer join cp_deal_history y
on x.cp_yyyymm = y.cp_yyyymm
order by x.cp_yyyymm|||That is not great surprise. LEFT OUTER JOINs are not well optimized
and thigns get flaky about five self joins in most queries. The real
proglem is that your code is awful.

Why would anyone, except an 1950's COBOL programmer, store temporal
data in strings! Why did you name a column for its format and not its
contents? Time is always stored as durations in a temporal data type.

Why would you divide by integer 1 when the column looks like a count of
loans? Tell me that you did not store that data as strings too and
have to force type conversions!! Try this:

SELECT deal_date,
MAX(CASE WHEN deal_id = 'AFCM9704'
THEN loan_cnt ELSE NULL END) AS AFCM9704,
..
MAX(CASE WHEN deal_id = 'BFAT99C'
THEN loans_cnt ELSE NULL END) AS BFAT99C
FROM DealHistory
WHERE deal_date BETWEEN '2003-04-01' AND '2005-04-30'
GROUP BY deal_date;|||Sweet Jesus, ZeldorBlat, if I'd thought anyone was actually going to
look at that I'd have pretty-printed it myself. So I apologize, and
thank you for your effort. The query constructs the data for a chart,
with cp_yyyymm being the independent variable, and each row needing the
value of cp_num_loans for each month and each particular deal id. So
the result set needs exactly one row per month. Your suggested query is
definitely moving in the right direction, but it just pulls all the
data - it doesn't "filter" it and arrange it as required. After playing
around a little bit, the query I eventually got working was basically
what CELKO suggested:

select right(z.cp_yyyymm, 2)+'-'+left(z.cp_yyyymm, 4) as [Month],
max(AFCM9704_unfiltered) as [AFCM9704],
max(AFC9104_unfiltered) as [AFC9104],
max(BFAT01C_unfiltered) as [BFAT01C],
max(BFAT02B_unfiltered) as [BFAT02B],
max(BFAT03D_unfiltered) as [BFAT03D],
max(BFAT03E_unfiltered) as [BFAT03E],
max(BFAT03F_unfiltered) as [BFAT03F],
max(BFAT04A_unfiltered) as [BFAT04A],
max(BFAT04C_unfiltered) as [BFAT04C],
max(BFAT04D_unfiltered) as [BFAT04D],
max(BFAT99C_unfiltered) as [BFAT99C]
from
(select cp_yyyymm, cp_deal_id,
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704_unfiltered],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104_unfiltered],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C_unfiltered],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B_unfiltered],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D_unfiltered],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E_unfiltered],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F_unfiltered],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A_unfiltered],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C_unfiltered],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D_unfiltered],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C_unfiltered]
from cp_deal_history as y
where cp_deal_id in ('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B',
'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as z
group by z.cp_yyyymm
order by z.cp_yyyymm

Note the lack of a single join. Sigh - I don't know what I was
thinking. Thanks very much to both of you for your help. (I'm still
curious as to why the joins weren't working the way I expected, though.)

complex tables - want to join to get one row of results from multiple rows

Hi there

This is a hard problem that I have - I have only been using sql for a couple of weeks and have gone past my ability level quickly! The real tables are complex but I will post a simple and a real version with the hope someone can help me.

Any help would be much appreciated - I would also be happy to pay someone to actually do it if it takes time to work out as I know that its hard when all your help is free :)
================================================
SIMPLE VERSION
Table 1 Dog breeds
dogbreedID, dogBreedName, colour
1,labrador,golden
2,beagle, tricolour
3,great dane, marle

Table 2 - maps criteria to dog breeds
dogbreedID, criteriaID, value, location
3,2,easy to train, c:/filepath2
1,1,good with children, c:/filepath
1,2,easy to train, c:/filepath2
2,1,good with children, c:/filepath
3,3,stranger friendly, c:/filepath3

So that leads to table 3 sitting behind the scenes not used in this query:
criteriaID, value, location
1,good with children, c:/filepath
2,easy to train, c:/filepath2
3,stranger friendly, c:/filepath3

I want a view that has the following:
dogbreedID, dogBreedName, colour, criteriaID1, value1, location1, criteriaID2, value2, location2, criteriaID3, value3, location3,criteriaID4, value4, location4

1,labrador,golden,1,good with children, c:/filepath,2,easy to train, c:/filepath2,NULL,NULL,NULL,NULL, NULL, NULL

2,beagle, tricolour,1,good with children,NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL

3,great dane, marle,NULL, NULL, NULL,2,easy to train, c:/filepath2,3,stranger friendly, c:/filepath3

================================================== =====
more complicated view - you can see each table is actually a combination of table values but I dont think that matters to this problem - the above example is fine but I am not very good with this so may have left somehting out that you can derive from the example below:
Table 1:
SELECT distinct dbo.BREED_dogBreeds.breedId, dbo.BREED_dogBreeds.breedName, dbo.BREED_dogBreeds.alternativeName, dbo.BREED_dogBreeds.shortDesc,
dbo.BREED_dogBreeds.katShortDesc, dbo.BREED_dogBreeds.longDesc, dbo.BREED_dogBreeds.katLongDesc,
dbo.BREED_dogBreeds.thingsToConsider, dbo.BREED_dogBreeds.temperament, dbo.BREED_dogBreeds.history, dbo.BREED_dogBreeds.feeding,
dbo.BREED_tblCountry.Country_Name, dbo.BREED_dogBreeds.colour, dbo.BREED_dogBreeds.breedProfileLink, dbo.BREED_Grooming.groomText,
dbo.BREED_GroomFrequencyValues.value, dbo.BREED_Training.intelligence, dbo.BREED_Training.trainingNotes, dbo.BREED_Training.exerciseText,
dbo.BREED_Training.exerciseTime, dbo.BREED_Training.timePerDay, dbo.BREED_Suitability.idealOwner, dbo.BREED_Size.size,
dbo.BREED_sizes.bheightmin, dbo.BREED_sizes.bheightmax, dbo.BREED_sizes.bweightmin, dbo.BREED_sizes.bweightmax,
dbo.BREED_sizes.dheightmin, dbo.BREED_sizes.dheightmax, dbo.BREED_sizes.dweightmin, dbo.BREED_sizes.dweightmax,
dbo.BREED_Sociability.compatibility FROM dbo.BREED_Sociability RIGHT OUTER JOIN
dbo.BREED_dogBreeds ON dbo.BREED_Sociability.sociabilityID = dbo.BREED_dogBreeds.sociabilityID LEFT OUTER JOIN
dbo.BREED_Size RIGHT OUTER JOIN
dbo.BREED_sizes ON dbo.BREED_Size.id = dbo.BREED_sizes.sizeid ON
dbo.BREED_dogBreeds.sizeId = dbo.BREED_sizes.breedSizeId LEFT OUTER JOIN
dbo.BREED_Suitability ON dbo.BREED_dogBreeds.suitabilityID = dbo.BREED_Suitability.suitabilityID LEFT OUTER JOIN
dbo.BREED_Training ON dbo.BREED_dogBreeds.trainID = dbo.BREED_Training.trainID LEFT OUTER JOIN
dbo.BREED_GroomFrequencyValues RIGHT OUTER JOIN
dbo.BREED_Grooming ON dbo.BREED_GroomFrequencyValues.gfvID = dbo.BREED_Grooming.gfvID ON
dbo.BREED_dogBreeds.groomID = dbo.BREED_Grooming.groomID LEFT OUTER JOIN
dbo.BREED_tblCountry ON dbo.BREED_dogBreeds.country = dbo.BREED_tblCountry.Country_ID

Table 2:
SELECT [breedId]
,[breedCriteriaID]
,[value]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[vbreedCriterias]
where levelID>=3
order by breedId, breedCriteriaID asc

TABLE 3
SELECT [breedCriteriaID]
,[criteriaValue]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[BREED_Criteria]

Table1
186Afghan HoundTazi, Baluchi HoundA strikingly beautiful dog with dignified poise.Afghans are kept primarily as show dogs and can also be used for lure coursing. They are extremely loving and loyal to their owners and are gentle souled and good with children. Afghans can make companion dogs but their considerable needs means that only devoted owners keep them. Aloof with strangers but affectionate and loyal to their owners. Can be very clown like at play and are very people oriented. Love children and being included in family life. Can become introverted if excluded from social situations whilst pups.An ancient breed, the afghan looks as classy as its pedigree. Afghans were used in Afghanistan to protect the flocks and would hunt and kill panthers, leopards and other large predators. The first dog to be shown was in the UK in 1907 having previously been banned from export. Afghans can be fussy with their food and it is better to instill good eating habits when they are pups and ideally treats should be avoided. AfghanistanAfghans are a grooming salons dream come true - they demand regular grooming and can quickly become knotted and tangled without it. DailyAll dogs are bright but Afghans may not quite earn the MENSA of the dog world.Afghans can be hard to train with lots of perseverance needed. Highly strung, stubborn and sensitive natured dog making them difficult to train. Training cannot be rushed and as they are sensitive souls and it is very important not treat them harshly. Sometimes difficult to housebreak. As puppies, Afghans often appear awkward, with uneven growth, gawkiness and loose limbs, and for this reason, exercise must be carefully monitored to avoid injury to their growing bones.00Suitable for the experienced, comitted dog owner with time on their hands and a great love for the breed.large63cm69cm23kg25kg68cm74cm25kg (55lb)28kg (62lb)They are hunting stock and love to chase anything that moves so perhaps not the ideal dog to share a home with cats and small animals!
187Bluetick CoonhoundNULLNot found in Australia, the Bluetick Coonhound is a friendly hound that makes an excellent tracking or scent dog.NULLThis breed originated in the states and has a smooth, dense, tri colour coat. The base colour is white with heavy ticking of black and tan markings over their chest, eyes, muzzle, lower legs and feet. This breed is recognised as a competitive, fearless, dedicated hunter. The Bluetick has a typical hound bawl and so is not the quietest of dogs.NULLThis dog needs a lot of exercise. They love to do jobs, and keep busy. They need to be exercised vigorously or they run the danger of becoming destructive and will howl excessivly.The Coonhound is deeply devoted, fearless, attentive and loyal. They make very good guardians and family companions. They are reserved with strangers, but are not aggressive towards them. They are not the best with other animals, and get along better with older, considerate children.The Bluetick originated in Louisiana at the beginning of the 20th century. They were developed from crosses between the English Coonhound, the Foxhound and the french Grand Bleu de Gascogne. Their tricoloured, blue-speckled coat sets them apart from other Coonhound breeds. Originally they were registered as a variant of the English Coonhound, but in 1946 they were given separate recognition as a distinct breed in their own right.Today the English Coonhound is sometimes referred to as the Redtick. The original, old-fashioned Bluetick dog, which was larger and slower than the modern type, began to loose ground because they did less well in the increasingly popular field competitions and night trials. The smaller, faster version began to eclipse them and became one of the most popular and numerous of all coonhound breeds. This upset the traditionalists, who preferred the old-style Big Blue , and some of them reacted by switching their allegiance to other large-bodied breeds, such as the Blue Gascon, and the Majestic.This breed is not fussy when it comes to their diet, but they have quite a healthy appetite.United States of AmericaNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
188Australian Cattle DogCattledog, Queensland Heeler, Heeler, Blue Heeler, Red Heeler, BlueyA strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.A strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog has a strong herding instinct and when playing may nip the heels of children.The Australian Cattle Dog needs a job, companionship and activity for the mind and body, all day, every day. Easily bored, he can become noisy and/or destructive. Renowned for his protectiveness and loyalty to master and property, he is very selective as to who is friend or foe. In 1840, Thomas Hall, a landowner in New South Wales, imported two smooth-haired blue merle Scotch Collies and crossed their progeny with the Dingo. The resulting litters became known as Hall's Heelers. The progeny were generally of Dingo type with the colour being either red or blue merle and were valued for their ability to handle wild cattle, stamina to travel great distances over all types of terrain, and their endurance in extremes of temperature. Later, Hall's Heelers were crossed with a Dalmatian, which changed the merle colour to red or blue speckle and instilled in the dogs a love of horses and protectiveness toward master and property. A further cross was made to the Kelpie to produce highly intelligent, controllable workers resembling thickset Dingoes and with peculiar markings known to no other dog. In 1903 a standard for the breed was drawn up and from these beginnings the Australian Cattle Dog has developed into one of the most popular breeds of dog in Australia today. AustraliaMinimal grooming although weekly brushing is required to remove dead hair. Does shed seasonally.Weekly - at homeHe is loving, playful, and eager to please his owner. Very quick to learn.Unless you can give him extensive exercise do not even consider owning an Australian Cattle Dog. He was bred to work all day in hard conditions and will become bored if not given sufficient exercise.602Single guy or gal, athletic, able to have the dog with them most of the time. Not a first time dog owner. Families with older children.medium43cm (17")48cm (19")16kg (35lb)20kg (44lb)46cm (18")51cm (20")16kg (35lb)20kg (44lbs)Not good with dogs of the same sex. OK with cats if raised with the cat since puppyhood. Not good with other small mammals.

TABLE 2
1861suits older children/Images/icons/older_children.gif
1862suits younger children/Images/icons/suits_young_children.gif
1863suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1866easy to transport/Images/icons/transport.gif
1868grooming needs/Images/icons/grooming_needs.gif
18610distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18612Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18615Energy Level/Images/icons/energy_levels.gif
18616Affection with family/Images/icons/affectionate.gif
18622may bite intruder/Images/icons/bite_intruder.gif
18623watchdog skills/Images/icons/watchdog_skills.gif
1871suits older children/Images/icons/older_children.gif
1876easy to transport/Images/icons/transport.gif
18710distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18712Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18713stranger friendly/Images/icons/stranger_friendly.gif
18715Energy Level/Images/icons/energy_levels.gif
18716Affection with family/Images/icons/affectionate.gif
18722may bite intruder/Images/icons/bite_intruder.gif
18723watchdog skills/Images/icons/watchdog_skills.gif
1881suits older children/Images/icons/older_children.gif
1883suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1884cattle friendly/Images/icons/cattle_friendly.gif
1886easy to transport/Images/icons/transport.gif
18810distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18811trainability/Images/icons/trainable.gif
18812Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18813stranger friendly/Images/icons/stranger_friendly.gif
18814How often this breed is found in the pound/Images/icons/found_in_pount.gif
18815Energy Level/Images/icons/energy_levels.gif
18816Affection with family/Images/icons/affectionate.gif
18819Availability/Images/icons/availability.gif
18820cat friendly/Images/icons/cat_friendly.gif
18822may bite intruder/Images/icons/bite_intruder.gif
18823watchdog skills/Images/icons/watchdog_skills.gif
1891suits older children/Images/icons/older_children.gif
1892suits younger children/Images/icons/suits_young_children.gif
1898grooming needs/Images/icons/grooming_needs.gif
18910distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18912Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18913stranger friendly/Images/icons/stranger_friendly.gif
18914How often this breed is found in the pound/Images/icons/found_in_pount.gif
18915Energy Level/Images/icons/energy_levels.gif
18916Affection with family/Images/icons/affectionate.gif
18919Availability/Images/icons/availability.gif
18923watchdog skills/Images/icons/watchdog_skills.gif
1901suits older children/Images/icons/older_children.gif
1903suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1906easy to transport/Images/icons/transport.gif
19010distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19011trainability/Images/icons/trainable.gif
19013stranger friendly/Images/icons/stranger_friendly.gif
19014How often this breed is found in the pound/Images/icons/found_in_pount.gif
19015Energy Level/Images/icons/energy_levels.gif
19016Affection with family/Images/icons/affectionate.gif
19019Availability/Images/icons/availability.gif
19022may bite intruder/Images/icons/bite_intruder.gif
19023watchdog skills/Images/icons/watchdog_skills.gif
19024Gay Icon/Images/icons/gay_icon.gif
1921suits older children/Images/icons/older_children.gif
1922suits younger children/Images/icons/suits_young_children.gif
1923suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1924cattle friendly/Images/icons/cattle_friendly.gif
1925bunny/guinea pig friendly/Images/icons/bunny_friendly.gif
1926easy to transport/Images/icons/transport.gif
19210distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19211trainability/Images/icons/trainable.gif
19212Shedding / Hair loss/Images/icons/shedding_hairloss.gif
19213stranger friendly/Images/icons/stranger_friendly.gif
19215Energy Level/Images/icons/energy_levels.gif
19216Affection with family/Images/icons/affectionate.gif
19217dog friendly/Images/icons/dog_friendly.gif
19219Availability/Images/icons/availability.gif
19220cat friendly/Images/icons/cat_friendly.gif
19223watchdog skills/Images/icons/watchdog_skills.gif

etcHi there

I got a response on another board. Basically I just used
SELECT dogBreedID,
crit1value = MIN(CASE criteriaID WHEN 1 THEN value END),
crit1location = MIN(CASE criteriaID WHEN 1 THEN location END),
crit2value = MIN(CASE criteriaID WHEN 2 THEN value END),
crit2location = MIN(CASE criteriaID WHEN 2 THEN location END),
...
FROM tbl
GROUP BY dogBreedID

and joined that to the first table.

Fantastic!

complex sql statement

I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(select(CommissionRebate* Quantity)

from

Account A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Insert

into Transaction

(

Payee

Deposit

AccountNumber

)

Values

(

'Account Credit'

@.NetCommission

@.AccountNumber

)

Insert

intoTransaction

(

Payee

........

Transaction is the key word,you have to choose another table name.

|||Or always embed such object names in [] or "" to avoid "syntax error":

Insert
into [Transaction]

(

Payee...|||

Even with the table name changed I am still getting a subquery error

Subquery returned more than 1 value.

All I really want to do is run a calculation on ech row in the database...any ideas?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(

select

(CommissionRebate* Quantity)

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Insert

into TradeTransaction

(

Payee

Deposit

AccountNumber

)

Values

(

'Account Credit'

@.NetCommission

@.AccountNumber

)

|||Obviously the error indicates?that?some?subquery?returns?more?than?1?value?while?you're?trying?to?assign?the?returned?values?to?a?single?variable.?This?is?a?bad?logic?as?there?is?no?array?in?T-SQL.?So?my?suggestion?would?be?to?use?some?aggregation?funciton?or?TOP?keyword?to?restrict?the?subquery?to?return?only?1?value, for example:

Set @.NetCommission =
(select top 1 (CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)

Or:

Set @.NetCommission =
(select max(CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)|||

This gets me the two values I need, but I then need to insert these two values into a different table... how would I go about doing this?

Select

sum(A.CommissionRebate* T.Quantity)As NetCommission, T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

|||

Ok this one works but it only inserts the first row and runs successfully, but does not go on to insert the other rows...

Declare

@.NetCommissiondecimal

Declare

@.AccountNumberVarchar(50)

Select

@.NetCommission=Sum(A.CommissionRebate* T.Quantity), @.AccountNumber= T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

Insert

into appTransaction

(

Payee

Payment

AccountNumber

)

Values

(

'Client Credit'

@.NetCommission

@.AccountNumber

)

|||If you want to insert multiple rows you need to use Cursor to skip through the rows to be inserted, or store the rows in a temple table so that you can?fetch rows from it?into?some?table?in?a?single?insert?statement.|||

I did this with the cursor idea, worked for a second and now I am getting this message: Error converting data type varchar to decimal.

DECLARE

appCursorCursor

For

Select

T.AccountNumber,sum(CommissionRebate)/count(*)*sum(Quantity)as NetCommission

from

cmsTrades T

Join

cmsCalcs con c.AccountNumber= T.AccountNumber

where

T.TradeDate=CAST(YEAR(getdate()) as varchar) + RIGHT('00'+CAST(MONTH(getdate()) as varchar), 2) + RIGHT('00'+CAST(DAY(getdate())-1 as varchar), 2)

and

.Month= 11

GROUP

BY T.AccountNumber

Open

appCursor

Declare

@.NetCommissiondecimal, @.AccountNumberVarchar(50)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

While

(@.@.Fetch_Status<>-1)

Begin

(@.@.Fetch_Status<>-2)

Insert

into appTransaction

(

Payee

Payment

AccountNumber

)

Values

(

'Client Credit'

@.NetCommission

@.AccountNumber

)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

End

appCursor

DEALLOCATE

appCursor

|||I found this:

DECLARE
appCursor Cursor For
Select
T.AccountNumber, sum(CommissionRebate) / count(*) * sum(Quantity) as NetCommission
from
cmsTrades T
.....
Fetch Next from appCursor Into @.NetCommission, @.AccountNumber

Shouldn't this be?Fetch Next from appCursor Into @.AccountNumber, @.NetCommission? The same order as the returned fields in the select query?|||

Forget the cursor. You want to stay away from those as much as possible. Keep with set-based logic; it's the SQL way! :-)

Use this sort of approach instead:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
CommissionRebate * Quantity,
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber

|||that works great, would there be an easy way to sum up the inserts by AccountNumber so instead of 36 inserts for one accountnumber I would have just 1 for each accountnumber for that day?|||

Yes, you might try this:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
SUM(CommissionRebate * Quantity),
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber
GROUP BY
A.AccountNumber

|||That did the trick perfectly...thank you!

Wednesday, March 7, 2012

Complex Searches

I have a search form that searches multiple fields in a db.
Currently, if you enter "nike putter" it only returns records that
have "nike putter" as a connected phrase. How do I get the db to
return records that have "nike" or "putter" in say the Title field and/
or "nike" or "putter" in the Description field?
So, if a record has a Title with "putter" and a description with the
word "Nike", what needs to be done either with the SQL code or the SQL
server configuration to return the record if a search enters "Nike
Putter"?
Thanks.
BrettHi
try some thing like this
select * from table where Title like '%nike%putter' or Description like
'%nike%putter'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Brett
Perhaps you need to consider ful-text search to enable on the database.
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Thanks for the quick reply but I have no way of knowing how the
searcher will enter the phrase. They may enter "putter nike titleist"
as a search phrase.
So maybe my question should be how to search the db for each
individual word used in the search term.
Brett
On Jun 5, 8:46 am, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> try some thing like this
> select * from table where Title like '%nike%putter' or Description like
> '%nike%putter'
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"Brett_A" <brettat...@.gmail.com> wrote in message
> news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
> >I have a search form that searches multiple fields in a db.
> > Currently, if you enter "nike putter" it only returns records that
> > have "nike putter" as a connected phrase. How do I get the db to
> > return records that have "nike" or "putter" in say the Title field and/
> > or "nike" or "putter" in the Description field?
> > So, if a record has a Title with "putter" and a description with the
> > word "Nike", what needs to be done either with the SQL code or the SQL
> > server configuration to return the record if a search enters "Nike
> > Putter"?
> > Thanks.
> > Brett

Complex Searches

I have a search form that searches multiple fields in a db.
Currently, if you enter "nike putter" it only returns records that
have "nike putter" as a connected phrase. How do I get the db to
return records that have "nike" or "putter" in say the Title field and/
or "nike" or "putter" in the Description field?
So, if a record has a Title with "putter" and a description with the
word "Nike", what needs to be done either with the SQL code or the SQL
server configuration to return the record if a search enters "Nike
Putter"?
Thanks.
BrettHi
try some thing like this
select * from table where Title like '%nike%putter' or Description like
'%nike%putter'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Brett
Perhaps you need to consider ful-text search to enable on the database.
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Thanks for the quick reply but I have no way of knowing how the
searcher will enter the phrase. They may enter "putter nike titleist"
as a search phrase.
So maybe my question should be how to search the db for each
individual word used in the search term.
Brett
On Jun 5, 8:46 am, "vt" <vinu.t.1...@.gmail.com> wrote:[vbcol=seagreen]
> Hi
> try some thing like this
> select * from table where Title like '%nike%putter' or Description like
> '%nike%putter'
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"Brett_A"
<brettat...@.gmail.com> wrote in message
> news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>
>
>
>

Complex query involving multiple tables

I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:

[tblEmployee]

empID

empName

deptID

jobtID

[tblDept]

deptID

deptNum

deptName

[tblJobTitle]

jobtID

jobtNam

[tblTrainng]

trnID

trnName

[tblTrnRev]

trnrevID

trnID

trnrevRev

trnrevDate

[tblEduJob]

ejID

jobtID

trnID

[tblEducation]

eduD

empID

trnrvID

eduDate

The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.

What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.

i'm somewhat at a loss of where to begin.

If you can put some actual SQL structure including foreign keys (and data) to the talbes it might be easier to see. Your column names can be daunting to try to traverse.

It is an interesting query, but it will take a bit of working out, and some data would make it much easier.

|||

i'm attaching a script to create a database, the tables, and some sample data.

create database "HRMS"
ON PRIMARY
( NAME = HRMS, FILENAME = 'C:\DB\HRMS.MDF', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
LOG ON
( NAME = HRMS_log, FILENAME = 'C:\DB\HRMS_log.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )

use "HRMS"

create table "tblEduJob" (
"ejID" uniqueidentifier not null,
"jobtID" uniqueidentifier null,
"trnID" uniqueidentifier null)

alter table "tblEduJob"
add constraint "tblEduJob_PK" primary key ("ejID")

create table "tblTrnRev" (
"trnrevID" uniqueidentifier not null,
"trnID" uniqueidentifier not null,
"trnrevRev" int not null,
"trnrevDate" datetime null)

alter table "tblTrnRev"
add constraint "tblTrnRev_PK" primary key ("trnrevID")

create table "tblEducation" (
"eduID" uniqueidentifier not null,
"empID" uniqueidentifier not null,
"trnrevID" uniqueidentifier not null,
"eduDate" smalldatetime null)

alter table "tblEducation"
add constraint "tblEducation_PK" primary key ("eduID")

create table "tblJobTitle" (
"jobtID" uniqueidentifier not null,
"jobtName" varchar(25) not null)

alter table "tblJobTitle"
add constraint "tblJobTitle_PK" primary key ("jobtID")

create table "tblTraining" (
"trnID" uniqueidentifier not null,
"trnNbr" varchar(8) null,
"trnName" varchar(50) not null,
"trnDesc" text null,
"trnSOP" bit null)

alter table "tblTraining"
add constraint "tblTraining_PK" primary key ("trnID")

create table "tblDept" (
"deptID" uniqueidentifier not null,
"deptNbr" varchar(4) null,
"deptName" varchar(25) null)

alter table "tblDept"
add constraint "tblDept_PK" primary key ("deptID")

create table "tblEmployee" (
"empID" uniqueidentifier not null,
"empNbr" varchar(10) not null,
"empLName" varchar(30) null,
"empFName" varchar(20) null,
"deptID" uniqueidentifier null,
"jobtID" uniqueidentifier null)

alter table "tblEmployee"
add constraint "tblEmployee_PK" primary key ("empID")

alter table "tblEduJob"
add constraint "tblTraining_tblEduJob_FK1" foreign key (
"trnID")
references "tblTraining" (
"trnID")

alter table "tblEduJob"
add constraint "tblJobTitle_tblEduJob_FK1" foreign key (
"jobtID")
references "tblJobTitle" (
"jobtID")

alter table "tblTrnRev"
add constraint "tblTraining_tblTrnRev_FK1" foreign key (
"trnID")
references "tblTraining" (
"trnID")

alter table "tblEducation"
add constraint "tblEmployee_tblEducation_FK1" foreign key (
"empID")
references "tblEmployee" (
"empID")

alter table "tblEducation"
add constraint "tblTrnRev_tblEducation_FK1" foreign key (
"trnrevID")
references "tblTrnRev" (
"trnrevID")

alter table "tblEmployee"
add constraint "tblDept_tblEmployee_FK1" foreign key (
"deptID")
references "tblDept" (
"deptID")

alter table "tblEmployee"
add constraint "tblJobTitle_tblEmployee_FK1" foreign key (
"jobtID")
references "tblJobTitle" (
"jobtID")

INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('F8ED13F7-8CC4-4F4A-9D05-0D7A931D375F','3000','PRODUCTION')
INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('EBC02D85-3518-4982-827C-359645343A5F','1000','ACCOUNTING')
INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('5AB270BD-3127-43CD-839E-56B3D5A30B85','4000','ADMIN')
INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('7F787D86-1C3A-4118-9521-9853D49C4708','2000','SALES')

INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('EF49546A-7A5E-43BE-B3B1-04B72A498301','ACCOUNTANT')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('720ECD1A-7A2B-498F-A43B-073C689D4CC4','SALESMAN')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('4CAF209A-4151-49AE-B103-5D677B4D829C','CEO')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('490EC42E-3E33-410D-B489-9A4CE6F7AAE0','CFO')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('5A7DD66C-F5C9-44E1-8716-A666C215359E','MACHINE TENDER')

INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('DFDF932E-5C09-4B72-A1D4-1A39BC821ACF','1001','SMITH','JOHN',1,'EBC02D85-3518-4982-827C-359645343A5F','490EC42E-3E33-410D-B489-9A4CE6F7AAE0')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('9F71DE9E-A6C1-4FA7-A822-620893717403','1005','CROCKETT','DAVIE',NULL,'F8ED13F7-8CC4-4F4A-9D05-0D7A931D375F','5A7DD66C-F5C9-44E1-8716-A666C215359E')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('E0DE6887-08AD-4911-95FC-A4A3D91358DF','1004','DOE','JACK',NULL,'7F787D86-1C3A-4118-9521-9853D49C4708','720ECD1A-7A2B-498F-A43B-073C689D4CC4')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('99DEF2F2-4CE7-4862-BB6C-B3EC3390532F','1002','JONES','BOB',NULL,'EBC02D85-3518-4982-827C-359645343A5F','EF49546A-7A5E-43BE-B3B1-04B72A498301')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('D7C4CE72-700E-4F21-A90B-C5E413EAAABE','1003','SMITH','JANE',NULL,'5AB270BD-3127-43CD-839E-56B3D5A30B85','4CAF209A-4151-49AE-B103-5D677B4D829C')

INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('0FBB1202-1A96-4FD0-88F5-5501EC848307','2','CLASS 2',0)
INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('C89FE8A1-B993-4CE0-B092-6D6982AD30B3','1','CLASS 1',0)
INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4','4','CLASS 4',0)
INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('FD3769D6-EF93-4170-BF23-E29CDEE5934D','3','CLASS 3',0)

INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('40B4ADAF-C4E8-48D8-835D-1337E5325BA8','C89FE8A1-B993-4CE0-B092-6D6982AD30B3',0,'Jan 1 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('CFE261A1-8A62-4523-BF10-1A4B70DC0A89','FD3769D6-EF93-4170-BF23-E29CDEE5934D',0,'Jan 1 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('BD3B2F29-F76D-425D-A15A-2AB442F607EB','0FBB1202-1A96-4FD0-88F5-5501EC848307',0,'Apr 3 2006 4:20:21:000PM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','C89FE8A1-B993-4CE0-B092-6D6982AD30B3',2,'Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('22546AB5-F39A-409F-AB99-632AD129618D','C89FE8A1-B993-4CE0-B092-6D6982AD30B3',1,'Jan 2 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('50FB73E1-7B96-4FD6-BFF9-DC282576176F','FD3769D6-EF93-4170-BF23-E29CDEE5934D',1,'Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('F537B949-35CB-491F-94D9-E1B8CD392921','3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4',0,'Apr 3 2006 4:20:34:000PM')

INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('31BF04D5-1502-4DE8-9804-066BD20F3A84','490EC42E-3E33-410D-B489-9A4CE6F7AAE0','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('50907488-2272-4084-9FAC-07452AD8A21F','720ECD1A-7A2B-498F-A43B-073C689D4CC4','3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('9579F27B-FD0D-4CBF-8C8D-1D4327FBC86B','EF49546A-7A5E-43BE-B3B1-04B72A498301','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('5DB85977-4E34-4106-BD92-292F69480569','5A7DD66C-F5C9-44E1-8716-A666C215359E','FD3769D6-EF93-4170-BF23-E29CDEE5934D')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('863A24F8-4085-4B0B-A840-571D32038E9A','720ECD1A-7A2B-498F-A43B-073C689D4CC4','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('2DE5211B-12FE-40A5-8286-660B0364DDD1','EF49546A-7A5E-43BE-B3B1-04B72A498301','FD3769D6-EF93-4170-BF23-E29CDEE5934D')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('04E83A51-CD45-48C9-9ACD-690B1A24CB93','5A7DD66C-F5C9-44E1-8716-A666C215359E','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('ED3C34DA-E48E-49EE-8A2D-70009720CE31','490EC42E-3E33-410D-B489-9A4CE6F7AAE0','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('7F71E446-05CB-47F8-9071-7CDBF404FCBB','720ECD1A-7A2B-498F-A43B-073C689D4CC4','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('F09BBA4D-F834-41C5-ACF3-A5862226A9C9','EF49546A-7A5E-43BE-B3B1-04B72A498301','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('027A62E5-A9D9-4666-9AB4-BC2CDC995807','4CAF209A-4151-49AE-B103-5D677B4D829C','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('BC8DD185-DAAA-4E90-A483-D48D73E75291','4CAF209A-4151-49AE-B103-5D677B4D829C','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('F392F54B-0F71-4FAC-A5B8-F822B0ED7597','EF49546A-7A5E-43BE-B3B1-04B72A498301','3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4')

INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('DD2B3BF8-5548-4F6F-A664-0372333A1C59','D7C4CE72-700E-4F21-A90B-C5E413EAAABE','54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('65AB167B-2785-4966-8266-299B57B92BAA','D7C4CE72-700E-4F21-A90B-C5E413EAAABE','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('86E5FFDE-A732-4B7E-9254-57ABAD89BE7F','DFDF932E-5C09-4B72-A1D4-1A39BC821ACF','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('7572DF39-0274-461C-9824-5C524DDD2DEF','E0DE6887-08AD-4911-95FC-A4A3D91358DF','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('C195DC05-B577-4A13-A51E-86694B8C8C36','99DEF2F2-4CE7-4862-BB6C-B3EC3390532F','54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('3B3D46CF-76C1-42DF-9AA0-D026CF7D9CB1','9F71DE9E-A6C1-4FA7-A822-620893717403','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('8805BC51-850D-4A38-85D3-D7AF7ECC90B5','99DEF2F2-4CE7-4862-BB6C-B3EC3390532F','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('49C849AF-1362-4889-87F7-EA83F26F327E','E0DE6887-08AD-4911-95FC-A4A3D91358DF','54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','Apr 3 2006 12:00:00:000AM')

|||

Good script. Because you did that, I wrote the following query. I like the table design. Did you design this?

Hopefully you can follow this. I broke it up into two views and a query to hopefully make it clearer... If you don't understand, just ask :)

--first, the employee and what is needed
create view employeeNeeds
as
select e.empId, jt.jobtID, ej.ejID, t.trnId
from tblEmployee as e
join tblJobTitle as jt
on e.jobtID = jt.jobtId
join tblEduJob as ej
on ej.jobtId = jt.jobtid
join tblTraining as t
on t.trnId = ej.trnId
go

--next, get the employee's training history
create view employeeTraining
as
select empId, trnId, max(latest) as currentTraining
from ( select e.empId, tr.trnId, case when tr.trnRevRev = (select max(inn.trnRevRev) as trnRevRev
from tblTrnRev as inn
where inn.trnId = tr.trnId) then 1 else 0 end as latest
from tblEmployee as e
join tblEducation as ed
on e.empId = ed.empId
join tblTrnRev as tr
on ed.trnRevId = tr.trnRevId ) as history
group by empId, trnId

go
select e.empLName, e.empFName, t.trnName, case when currentTraining is null then 'Not Taken'
when currentTraining = 1 then 'Current'
when currentTraining = 0 then 'Taken, Not Current' end
from employeeNeeds as en
join tblEmployee as e
on e.empId = en.empId
join tblTraining as t
on t.trnId = en.trnId
left outer join employeeTraining as et
on en.empId = et.empId
and en.trnId = et.trnId
order by e.empLName, e.empFName, t.trnName

|||

Yes i did put that database together actually. It was my first 'production' database. i've tinkered for years on a personal level but have created my first ASP.NET/C# app with the SQL database on the backend for a program at work to keep track of employee training.

Thanks for the script. It worked like a charm. I had done some research yesterday and had determined that I probably needed a case statement but wasn't sure how to construct. I was trying to make it one complex statement. Breaking it down into the two views and then creating the select hadn't crossed my mind. Thanks again!

|||

Cool. Yeah, I was impressed that it was really very normalized. I would loved to have changed your table and column names from tblEmployee and empId to employee and employeeId, respectively, but the structure was way more important.

It gave me something to do while watching the really boring bball game tonight (I wanted Florida to win since I am a SEC fan, but wow. what a snore..)

Saturday, February 25, 2012

complex queries and performance

i have a question on the above mention... say if i need to run a batch
procedure with very complex and multiple set base CRUD functionalites, does
it make any difference if i break them down into 1 store procedure per query
or multiple query per procedure. Presumbly everything is set to run with no
lock, does it make any difference?It usually depends. Consider recompilations: what needs to be
recompiled if you refresh statistics on one table? What needs to be
recompiled when you add an index on one table? How much time does
recompilation take?

Sunday, February 19, 2012

compiling objects to multiple databases

Anyone have any suggestions they could share on compiling objects to multipl
e
databases at the same time on the same server and also to multiple servers?
Example:
Stored procedure needs to be compiled on 60 databases on the same server.
Thanks for any help!Assuming the name of the SP on all 60 databases on the server are the same:
sp_MSforeachdb 'USE ? IF EXISTS (SELECT * FROM sysobjects WHERE name =
''sp_name'' AND xtype = ''U'') EXEC sp_recompile ''sp_name'''
"BL" wrote:

> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||Jack - how would I go about getting them compiled in the database when they
currently exist in a .txt file?
"BL" wrote:

> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||I would write an application using ADO.NET which reads the text files and cr
eate the procedures on
each server/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BL" <BL@.discussions.microsoft.com> wrote in message
news:87375008-C1A7-4009-835E-095F70427CE2@.microsoft.com...
> Jack - how would I go about getting them compiled in the database when the
y
> currently exist in a .txt file?
> "BL" wrote:
>

Sunday, February 12, 2012

Comparison with multiple tables

HELP!!!!
I am using SQL in Access and need to pull all of the records that don't
match in the key field. The key fields are the same name in both tables and
I
have built a relationship on a different field. Both tables have some
matching records and some non matching. I want all of the records from both
tables that do not match!SELECT a.<column list>, b.<column list>
FROM a
FULL OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables
> and I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from
> both
> tables that do not match!|||Try,
select *
from t1 full outer join t2
on t1.pk_col = t2.pk_col
where t1.pk_col is null or t2.pk_col is null
AMB
"Tess9126" wrote:

> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables an
d I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from bot
h
> tables that do not match!|||Access did not like the word outer. I am at work with absolutely no referenc
e
material. I am familure with SQL but I am really stuck. I want to thank you
for trying to help me!
"Jacco Schalkwijk" wrote:

> SELECT a.<column list>, b.<column list>
> FROM a
> FULL OUTER JOIN b
> ON a.<primary key> = b.<primary key>
> WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
> news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
>
>|||Ah, Access. My Access SQL is rather rusty, so maybe the following will work:
SELECT a.<column list>, b.<column list>
FROM a
LEFT OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE b.<primary key> IS NULL
UNION
SELECT a.<column list>, b.<column list>
FROM b
LEFT OUTER JOIN a
ON b.<primary key> = a.<primary key>
WHERE a.<primary key> IS NULL
If it doesn't work, the best thing to do is post your question on an Access
newsgroup.
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:43F5BF5D-9B8C-401C-B1F7-136381102C7C@.microsoft.com...
> Access did not like the word outer. I am at work with absolutely no
> reference
> material. I am familure with SQL but I am really stuck. I want to thank
> you
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>|||Then you will need a union all of two outer joins.
select t1.c1, ..., t1.cn
from t1 left join t2 on t1.pk_col = t2.pk_col
where t2.pk_col is null
union all
select t2.c1, ..., t2.cn
from t1 right join t2 on t1.pk_col = t2.pk_col
where t1.pk_col is null
AMB
"Tess9126" wrote:
> Access did not like the word outer. I am at work with absolutely no refere
nce
> material. I am familure with SQL but I am really stuck. I want to thank yo
u
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>

Thursday, March 29, 2012

Concatenate Multiple Rows?

Concatenate multiple reports into one pdf

Concatenate Multiple Records Into One Field

concatenate multiple fields

Concatenate Columm Values from multiple Rows into a single col

Tuesday, March 27, 2012

Concat tables into one row in view

Sunday, March 25, 2012

Computed Fields/Multiple Datasources

Monday, March 19, 2012

Composite Index Question

Composite index design question

Sunday, March 11, 2012

Complicated SQL Select help needed

Thursday, March 8, 2012

complexity of nested self left joins?

complex tables - want to join to get one row of results from multiple rows

complex sql statement

Wednesday, March 7, 2012

Complex Searches

Complex Searches

Complex Searches

Complex query involving multiple tables

Saturday, February 25, 2012

complex queries and performance

Sunday, February 19, 2012

compiling objects to multiple databases

Sunday, February 12, 2012

Comparison with multiple tables

Sql Comparing two tables

Blog Archive

About Me