Query with 5 tables, grouping by year

How would I group results from 4 tables, each with a year field, so the
results are one row for each year, all based on one clientid from the client
table
For instance, my tables:
Client table: fields ID, ClientName
Tables 1 through 4 all have the same fields, in addition to others: Fields
ID, ClientID (fk to Client table), ValidYear, Data ...
I want my results to be one row per year (we'll be querying only one
client), for example
Year Table1Data Table2Data Table3Data Table4Data
2001 1,000 3,300 15,000 445
2002 1,212 etc.
I've started with:
Select Table1.Data, Table1.ValidYear,
Table2.Data, Table2.ValidYear,
Table3.Data, Table3.ValidYear,
Table4.Data, Table4.ValidYear,
tblClient.ID, tblClient.ClientName
from Table1
inner join tblClient as a on a.ID = Table1.ClientID
inner join tblClient as b on b.ID = Table1.ClientID
inner join tblClient as c on c.ID = Table1.ClientID
inner join tblClient as d on d.ID = Table1.ClientID
and that's as far as I got, as soon as I enter criteria for ValidYear, I get
either too many rows of data or none at all. Not all data tables have data
for all years, by the way.
Thanks very much for your help.Hi,
would you like to check out he usage of DATEPART?
In your SELECT statement, you actually can put DatePart(year,
Table2.ValidYear) instead of Table2.ValidYear.
In the end of your SELECT statement, you just need to put GROUP BY
Table1.Data, Table2.Data etc to get a distinct value.
I hope this is what you are looking for.
Leo Leong
"et" wrote:

> How would I group results from 4 tables, each with a year field, so the
> results are one row for each year, all based on one clientid from the clie
nt
> table
> For instance, my tables:
> Client table: fields ID, ClientName
> Tables 1 through 4 all have the same fields, in addition to others: Field
s
> ID, ClientID (fk to Client table), ValidYear, Data ...
> I want my results to be one row per year (we'll be querying only one
> client), for example
> Year Table1Data Table2Data Table3Data Table4Data
> 2001 1,000 3,300 15,000 445
> 2002 1,212 etc.
> I've started with:
> Select Table1.Data, Table1.ValidYear,
> Table2.Data, Table2.ValidYear,
> Table3.Data, Table3.ValidYear,
> Table4.Data, Table4.ValidYear,
> tblClient.ID, tblClient.ClientName
> from Table1
> inner join tblClient as a on a.ID = Table1.ClientID
> inner join tblClient as b on b.ID = Table1.ClientID
> inner join tblClient as c on c.ID = Table1.ClientID
> inner join tblClient as d on d.ID = Table1.ClientID
> and that's as far as I got, as soon as I enter criteria for ValidYear, I g
et
> either too many rows of data or none at all. Not all data tables have dat
a
> for all years, by the way.
> Thanks very much for your help.
>
>

Query Tuning

I want to take the execution plan of some transact sql queries, I took the execution plan as text based one, that having

index scan, index seek

Remote scan, remote Update

sort order by cluases

in the above clauses what is the high performance, and how will i change to the high performance clauses by changin the query to improve the execution speed of the query

Please guide me

the execution plan is

SELECT [Inventory_Profile].[InventoryID] ,[Inventory_Profile].[Alias] ,[Inventory_Profile].[InventoryStatusID] ,[Inventory_Profile].[InventorySubTypeID] ,[Inventory_Profile].[InventoryTypeID] ,[Inventory_Profile].[AcquisitionDate] ,[Inventory_Profile].[UnitNumber] ,[Inventory_Profile].[YearOfManufacture] ,[Inventory_Profile].[Manufacturer] ,[Inventory_Profile].[Make] ,[Inventory_Profile].[Model] ,[Inventory_Profile].[SerialNumber] ,[Inventory_Profile].[UsageConditionID] ,[Inventory_Profile].[Description1] ,[Inventory_Profile].[Description2] ,[Inventory_Profile].[LocationEffectiveFromDate] ,[Inventory_Profile].[IsFlaggedForSale] ,[Inventory_Profile].[RentalPurchaseOrderNumber] ,[Inventory_Profile].[AquisitionPurchaseOrderNumber] ,[Inventory_Profile].[SortOrder] ,[Inventory_Profile].[IsSaleLeaseBack] ,[Inventory_Profile].[InterimRentReceivableUpfrontTaxModeID] ,[Inventory_Profile].[LeaseRentalReceivableUpfrontTaxModeID] ,[Inventory_Profile].[OverTermReceivableUpfrontTaxModeID] ,[TaxDepreciation_Inventory].[IsTaxDepreciationRequired] ,[TaxDepreciation_Inventory].[IsComputationPending] ,[TaxDepreciation_Inventory].[TaxDepreciationTemplateID] ,[TaxDepreciation_Inventory].[InventoryCostBasisAmount] ,[TaxDepreciation_Inventory].[DepreciationBeginDate] ,[TaxDepreciation_Inventory].[DepreciationEndDate] ,[TaxDepreciation_Inventory].[IsTaxDepreciationTerminated] ,[TaxDepreciation_Inventory].[IsStraightLineMethodUsed] ,[TaxDepreciation_Inventory].[IsLeaseTermUsedForStraightLineMethod] ,[Inventory_PTMS].[Division] ,[Inventory_PTMS].[Branch] ,[Inventory_PTMS].[SalesTaxPercent] ,[Inventory_PTMS].[SalesTaxAmount] ,[Inventory_PTMS].[IsSalesTaxIncluded] ,[Inventory_PTMS].[GLExpenseAccount] ,[Inventory_PTMS].[GLAssetAccount] ,[Inventory_PTMS].[SoftwareExclusionAmount] ,[Inventory_PTMS].[AssetCategoryCodeID] ,[Inventory_PTMS].[OwnershipCodeID] ,[Inventory_PTMS].[ManufacturingCodeID] ,[Inventory_PTMS].[ReimburseCodeID] ,[Inventory_PTMS].[BillingStatusID] ,[Inventory_PTMS].[PropertyTaxExemptionCodeID] ,[Inventory_PTMS].[UserDefinedField1] ,[Inventory_PTMS].[UserDefinedField2] ,[Inventory_PTMS].[Notes] FROM [Inventory_Profile] INNER JOIN [TaxDepreciation_Inventory] ON [Inventory_Profile].[InventoryID]=[TaxDepreciation_Inventory].[InventoryID] INNER JOIN [Inventory_PTMS] ON [Inventory_Profile].[InventoryID]=[Inventory_PTMS].[InventoryID] INNER JOIN [Inventory_Status_CnfgLocale] ON [Inventory_Profile].[InventoryStatusID] in (SELECT InventoryStatusID FROM Inventory_Status_CnfgLocale WHERE InventoryStatusName <> 'Donated' and InventoryStatusName <> 'Scrap' and InventoryStatusName <>'Write Off' and InventoryStatusName <> 'Sold')

|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | | | |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_PTMS].[PK_IInvPTMS]))
| | | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Profile].[PK_IInventory_InvID]), SEEK:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]), SEEK:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusID]=[LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]), WHERE:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Donated' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Scrap' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Sold' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Write Off') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[UK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[PK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]=[LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]) LOOKUP ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]))

Hello,

could you please provide the full execution plan as output by Query Analyzer, as well as the query you are running?

|||

Hi,

--index scan good, index seek better :-)

--Remote scan und update is always involved if your doing something on a remote basis, like a linked server. If you want to use the remote indexes you will have to create a logic / stored procedureon the remote side on execute that via a linked server command.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Index scan sometimes means bad news :)
Say, when a clustered index is defined on a table, the query plan will never show a "table scan" operation, it will always show "index scan", even if it is scanning the table.
Index seek means that your index is actually been used, this is generally a good news. But nothing general can be said, sometimes index seek is worse than index scan, and vice vers. For example, if you perform a range query and have a clustered index on appropriate columns, this results in an index scan, which is the fastest plan in most cases. Meanwhile, if you have a non-clustered index on those columns, you will get an index seek(or table scan, depending on statistics). This is almost always worse that index seek. If you do not have clustered index on the columns in where clause, but you have some other clustered index, you will get an index scan which is bad, it is a table scan in fact. So in the first case scan > seek and in the second case seek > scan.

Post the query, and table schema, we will be able to be more definite in that case.

Query to select data based on alphanumeric (surname) information.

Hello,

I am trying to write a query that will be able to select different segments of data based on spelling of the last name.

For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'

then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...

I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?

Thanks

It isn't perfectly straightforward, particularly at the end of the ranges, but this will work...

create table person

(

lastName varchar(20)

)

insert into person

select 'AAA Dude'

union all

select 'Branson'

union all

select 'EJJ Dude'

union all

select 'Flighter'

union all

select 'Jaenor'

union all

select 'Zoinks'

--first

select *

from person

where lastName >= 'a' --Just one letter needed here because we want all

and lastName < 'EJK' --Added one letter to the range you want

lastName

--

AAA Dude

Branson

EJJ Dude

select *

from person

where lastName >= 'EJK'

and lastName < 'JAF' --again, one character more than the range

lastName

--

Flighter

Jaenor

select *

from person

where lastName >= 'JAF'

and lastName <= replicate('Z',20) --All zzz's would be the end of the range

--replicated to the max length of the column

lastName

--

Zoinks

|||

The following querry may fit for you,

Code Snippet

Create Table #person (

[lastName] Varchar(100)

);

Insert Into #person Values('AAA Dude');

Insert Into #person Values('Branson');

Insert Into #person Values('EJJ Dude');

Insert Into #person Values('Flighter');

Insert Into #person Values('Jaenor');

Insert Into #person Values('Zoinks');

Insert Into #person Values('EJK');

Insert Into #person Values('EJ');

Insert Into #person Values('EJJ');

Insert Into #person Values('EJJ ZZZZZZ');

Insert Into #person Values('JA');

Insert Into #person Values('JAE');

Select * from #person

Where

[lastName] >= 'AAA'

And [lastName] <= 'EJJ' + Replicate(Char(255),100) -- change the length with your datatype

Select * from #person

Where

[lastName] >= 'EJK'

And [lastName] <= 'JAE' + Replicate(Char(255),100) -- change the length with your datatype

|||

The reason I went with:

where lastName >= 'a' --Just one letter needed here because we want all

Was that I wanted to make sure that all alpha numeric values got in, even if the value 'A boinger' was in the table. If there are no spaces in the code value, it wouldn't matter. For example:

select case when 'a boinger' >= 'AAA' then 'yes' else 'no' end

Would be 'no' since 'a b' < 'AAA'

|||

Having worked with large criminal justice applications/databases, I've noticed that often when arrested, folks may have 'odd' street names, or the arresting officer is dealing with someone unconscious or intoxicated, so the name in the database may be '10 minute Fred', '2 Drink Limit', '1perp', '2ndDrunk', etc.

So, I would not limit names to starting with alpha...

('10 minute Fred' was never in a house/business for more than 10 minutes when he was burglurizing them.)

query to parse out values from one column into different columns

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr

What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.Look up CROSSTAB queries in Books Online.select userid,
max(case recordtag when 1 then record end) as firstname,
max(case recordtag when 2 then record end) as middleinitial,
max(case recordtag when 3 then record end) as suffix
from [YourTable]
group by userid|||Thanks for the info. I'll let you know how I do.|||I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!

Query to display a field based on a parameter

I have a client table, with 8 fields, all of which contain a phone number.
Bad design, I know, but let's not get into that here. My problem now is
that in addition to those fields, we have a "Preferred" field, which simply
names the field of the preferred phone number. So we have fields like
HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
How can I create a query so that the record displays only that field that is
preferred? I hope that makes sense.
For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
Preferred "Home Phone", I want to the query to only display the name, and
home phone.
For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
"Business Phone", I want to the query to only display the name, and business
phone.
All of these records are displayed in a datagrid.
Thanks for your help.SELECT CASE Preferred
WHEN 'Home Phone' THEN HomePhone
WHEN 'Work Phone' THEN WorkPhone
..
WHEN 'Yet Another Phone' THEN YetAnotherPhone
End as PreferredPhone
FROM LotsOfPhones
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:29:47 -0600, "KatMagic" <SSKatMagic@.yahoo.com>
wrote:

>I have a client table, with 8 fields, all of which contain a phone number.
>Bad design, I know, but let's not get into that here. My problem now is
>that in addition to those fields, we have a "Preferred" field, which simply
>names the field of the preferred phone number. So we have fields like
>HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
>How can I create a query so that the record displays only that field that i
s
>preferred? I hope that makes sense.
>For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
>Preferred "Home Phone", I want to the query to only display the name, and
>home phone.
>For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
>"Business Phone", I want to the query to only display the name, and busines
s
>phone.
>All of these records are displayed in a datagrid.
>Thanks for your help.
>

Query table based on multiple keys

Hey,

I am having some confusion about how to formulate this particular
query.
I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with the
columns a1,a2,a4 forming the primary key. Table B again has 3 columns
with b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.
All columns are of the same datatype in both tables. Now I want to get
rows from table A which are not present in table B. Whats the best way
of doing this?

Thanks

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict235166.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725I'm no expert, so this probably isn't the most efficient way to do
this, but I think this will work:

select A.* from A, B
where A.a1 *= B.b1
and A.a2 *= B.b2
and A.a4 *= B.b4
and B.b1 is null

--Richard|||SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a3 = B.b3
AND A.a4*= B.b4 );|||
--CELKO-- wrote:
> SELECT a1, a2, a3, a4
> FROM A
> WHERE NOT EXISTS
> (SELECT *
> FROM B
> WHERE A.a1 = B.b1
> AND A.a2 = B.b2
> AND A.a3 = B.b3
> AND A.a4*= B.b4 );

Yeah, that works better than my version. I just tested a little more
and realized that mine doesn't actually do what I expected, but I can't
figure out why not.

--Richard|||--CELKO-- (jcelko212@.earthlink.net) writes:
> SELECT a1, a2, a3, a4
> FROM A
> WHERE NOT EXISTS
> (SELECT *
> FROM B
> WHERE A.a1 = B.b1
> AND A.a2 = B.b2
> AND A.a3 = B.b3
> AND A.a4*= B.b4 );

What is that *= doing on the last row?

The requirements were somewhat ambiguous, but one of these should do:

SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a4 = B.b4 );

(Rows identified by keys, the value in the non-key column a3/b3 may
be different.)

SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a4 = B.b4
AND A.a3 = B.b3 );

(Rows may be in both tables, but may have a difference in a3/b3.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> I'm no expert, so this probably isn't the most efficient way to do
> this, but I think this will work:
> select A.* from A, B
> where A.a1 *= B.b1
> and A.a2 *= B.b2
> and A.a4 *= B.b4
> and B.b1 is null

*= is a older form of outer join which has all sorts of funny
quirkes with it. I am not going to find why this does not work.

Use the new ANSI syntax instead:

select A.*
from A
left join B ON A.a1 = B.b1
and A.a2 = B.b2
and A.a4 = B.b4
where and B.b1 is null

But I much prefer NOT EXISTS for this type of query, as it much better
expresses what you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> What is that *= doing on the last row? <<

Arrrgh! Cut & paste error!