Query problems - Group By and Latest date

Hi all,

hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.

There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:

SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber

What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:

SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03

What i really need is to only list the last part that is installed, hence the one with the latest timestamp:

SerialNumber Param1 TimeStamp

0 Part1 15/03/07

0 Part2 16/03/07

0 Part3 15/03/03

Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.

Many thanks in advance,

Grant

This should do the trick:

SELECT [SerialNumber]
,Param1
,MAX([TimeStamp])
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
ORDER BY SerialNumber

You only need to take the max of your timestamp field (and remove it from the group by). The group by all fields is a bit extreme in the previous query (you could use the distinct keyword instead if you had apparent duplicate rows (i.e. replaced the part 3 times in a day).

|||Thats sorted it.
I wasn't as far of the mark in the first place as i'd thought. Thank's very much for the assistance, its much appreciated.

Cheers,

Grant|||Hi, apologies but i need one more piece of advice on this subject.

If i want to include a column with a serial number of the part that has been replaced, how would i do that. As soon as i add it, it needs to be part of an aggregate function or the group by clause. When it becomes part of the group by clause it then duplicates the part again.

Any ideas?

Thanks,

Grant|||

Is the serial number of the part in the same table - if so presumably it is different for each time that part is replaced. You can use a nested query to get that - however it will run into a problem if there are multiple records with the same date. As it stands at the moment you could not distinuish between them.

If you had records:

SerialNumber Param1 TimeStamp Part_SN
0 Part1 15/03/07 1234
0 Part2 15/03/07 1235
0 Part2 16/03/07 1236
0 Part2 16/03/07 1237
0 Part3 15/03/03 1238

How would you know which of the two Part2 items fitted on 16 Mar to give the serial number of? If there are additional fields to determine this then we need to use them

If this does not arise then the query below should serve (substitute correct fieldname for Part_SN):

SELECT B.[SerialNumber]
,B.Param1
,B.[TimeStamp]
,B.Part_SN
FROM (
SELECT [SerialNumber]
,Param1
,MAX([TimeStamp]) AS TimeStamp
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
) A
INNER JOIN [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL] B
ON (A.[SerialNumber] = B.[SerialNumber]) AND
(A.Param1 = B.Param1) AND
(A.[TimeStamp] = B.[TimeStamp]) AND
(B.Test = 'T120')
ORDER BY B.[SerialNumber]

If this is a problem then you will get multiple records in that case - one for each serial number. If the TimeStamp is a datetime which includes the time of the replacement then this will not be an issue (as long as the required serial number is the last record.

|||Thanks,

That is exactly what i wanted to do. Works like a charm.

Grantsql

Query Performance and Index

Dear Forum,
Please help me modify this query for optimum performance and suggest indexes
to create. Thanks. James
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAM
E,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinitionHi James
I added parenthesis to your query:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,
MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND
BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
(BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
)
GROUP BY
BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
Please let me know if this is correct.
If it is correct, then there is no need of changing the query, your query
looks perfect.
There might be Primary Keys declared on the tables that are used here.
Else create Index on the columns that are involved in the where clause.
please let me know your comments
thanks and regards
Chandra
"James Juno" wrote:

> Dear Forum,
> Please help me modify this query for optimum performance and suggest index
es
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate
,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes
,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
> BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_N
AME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
>|||Adding to my previous post, what i feel is, the first OR should be replace
with AND:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode AND
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAM
E,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
"Chandra" wrote:
[vbcol=seagreen]
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CO
DE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndD
ate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefiniti
on
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
>|||Chandra,
This is great. Thank you so much. I haven't tried it yet - but it looks goo
d.
James.
"Chandra" wrote:
[vbcol=seagreen]
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CO
DE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndD
ate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefiniti
on
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
>|||To avoid confusion, use ansi join instead old style. Also use alias to make
the statement more readable.
SELECT
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition,
c.StateFips,
MIN(substring(c.JudgmentDate, 1,6)),
MAX(substring(c.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCases as c
inner join
BBNew.dbo.fffCourtCodes as cc
on c.CourtCode = cc.MST_COURT_CODE
inner join
BBNew.dbo.fffCourtTypes as ct
on c.CourtType = ct.CTcode
inner join
BBNew.dbo.fffRecordTypes as rt
on c.FilingType = rt.RTcode
WHERE
c.StateFips LIKE :Param_State
OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY
c.StateFips,
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition;
Be sure to have an index by:
- BBNew.dbo.fffCases.PostedDate <-- clustered one
- BBNew.dbo.fffCases.StateFips
- BBNew.dbo.fffCases.CourtCode
- BBNew.dbo.fffCases.CourtType
- BBNew.dbo.fffCases.FilingType
- BBNew.dbo.fffCourtCodes.MST_COURT_CODE
- BBNew.dbo.fffCourtTypes.CTcode
- BBNew.dbo.fffRecordTypes.rt.RTcode
AMB
"James Juno" wrote:
[vbcol=seagreen]
> Chandra,
> This is great. Thank you so much. I haven't tried it yet - but it looks g
ood.
> James.
> "Chandra" wrote:
>|||Alejandro,
Great. Thank you.
James
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> To avoid confusion, use ansi join instead old style. Also use alias to mak
e
> the statement more readable.
> SELECT
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition,
> c.StateFips,
> MIN(substring(c.JudgmentDate, 1,6)),
> MAX(substring(c.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCases as c
> inner join
> BBNew.dbo.fffCourtCodes as cc
> on c.CourtCode = cc.MST_COURT_CODE
> inner join
> BBNew.dbo.fffCourtTypes as ct
> on c.CourtType = ct.CTcode
> inner join
> BBNew.dbo.fffRecordTypes as rt
> on c.FilingType = rt.RTcode
> WHERE
> c.StateFips LIKE :Param_State
> OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY
> c.StateFips,
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition;
> Be sure to have an index by:
> - BBNew.dbo.fffCases.PostedDate <-- clustered one
> - BBNew.dbo.fffCases.StateFips
> - BBNew.dbo.fffCases.CourtCode
> - BBNew.dbo.fffCases.CourtType
> - BBNew.dbo.fffCases.FilingType
> - BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> - BBNew.dbo.fffCourtTypes.CTcode
> - BBNew.dbo.fffRecordTypes.rt.RTcode
>
> AMB
>
> "James Juno" wrote:
>|||In general, it is a good practice to create a Primary Key constraint on
all tables (which will automatically create a unique index), and to
create indexes on foreign key constraints.
You did not post any DDL, so the keys and indexes cannot be reviewed.
But the query would benefit if all join columns were indexed.
Gert-Jan
James Juno wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest index
es
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate
,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes
,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
> BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_N
AME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition|||Gert-Jan,
I got the script that works. Thanks for your contribution.
James
"Gert-Jan Strik" wrote:

> In general, it is a good practice to create a Primary Key constraint on
> all tables (which will automatically create a unique index), and to
> create indexes on foreign key constraints.
> You did not post any DDL, so the keys and indexes cannot be reviewed.
> But the query would benefit if all join columns were indexed.
> Gert-Jan
>
> James Juno wrote:
>

Query Performance and Index

Dear Forum,
Please help me modify this query for optimum performance and suggest indexes
to create. Thanks. James
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinitionHi James
I added parenthesis to your query:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,
MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND
BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
(BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
GROUP BY
BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
Please let me know if this is correct.
If it is correct, then there is no need of changing the query, your query
looks perfect.
There might be Primary Keys declared on the tables that are used here.
Else create Index on the columns that are involved in the where clause.
please let me know your comments
thanks and regards
Chandra
"James Juno" wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
>|||Adding to my previous post, what i feel is, the first OR should be replace
with AND:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode AND
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
"Chandra" wrote:
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
> >|||Chandra,
This is great. Thank you so much. I haven't tried it yet - but it looks good.
James.
"Chandra" wrote:
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
> >|||To avoid confusion, use ansi join instead old style. Also use alias to make
the statement more readable.
SELECT
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition,
c.StateFips,
MIN(substring(c.JudgmentDate, 1,6)),
MAX(substring(c.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCases as c
inner join
BBNew.dbo.fffCourtCodes as cc
on c.CourtCode = cc.MST_COURT_CODE
inner join
BBNew.dbo.fffCourtTypes as ct
on c.CourtType = ct.CTcode
inner join
BBNew.dbo.fffRecordTypes as rt
on c.FilingType = rt.RTcode
WHERE
c.StateFips LIKE :Param_State
OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY
c.StateFips,
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition;
Be sure to have an index by:
- BBNew.dbo.fffCases.PostedDate <-- clustered one
- BBNew.dbo.fffCases.StateFips
- BBNew.dbo.fffCases.CourtCode
- BBNew.dbo.fffCases.CourtType
- BBNew.dbo.fffCases.FilingType
- BBNew.dbo.fffCourtCodes.MST_COURT_CODE
- BBNew.dbo.fffCourtTypes.CTcode
- BBNew.dbo.fffRecordTypes.rt.RTcode
AMB
"James Juno" wrote:
> Chandra,
> This is great. Thank you so much. I haven't tried it yet - but it looks good.
> James.
> "Chandra" wrote:
> > Hi James
> > I added parenthesis to your query:
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,
> > MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> > MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> >
> > FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> > BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> >
> > WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND
> > BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> >
> > (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> > OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> > OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> >
> > GROUP BY
> > BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> >
> > Please let me know if this is correct.
> >
> > If it is correct, then there is no need of changing the query, your query
> > looks perfect.
> > There might be Primary Keys declared on the tables that are used here.
> >
> > Else create Index on the columns that are involved in the where clause.
> >
> > please let me know your comments
> >
> > thanks and regards
> > Chandra
> >
> > "James Juno" wrote:
> >
> > > Dear Forum,
> > >
> > > Please help me modify this query for optimum performance and suggest indexes
> > > to create. Thanks. James
> > >
> > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition
> > >|||Alejandro,
Great. Thank you.
James
"Alejandro Mesa" wrote:
> To avoid confusion, use ansi join instead old style. Also use alias to make
> the statement more readable.
> SELECT
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition,
> c.StateFips,
> MIN(substring(c.JudgmentDate, 1,6)),
> MAX(substring(c.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCases as c
> inner join
> BBNew.dbo.fffCourtCodes as cc
> on c.CourtCode = cc.MST_COURT_CODE
> inner join
> BBNew.dbo.fffCourtTypes as ct
> on c.CourtType = ct.CTcode
> inner join
> BBNew.dbo.fffRecordTypes as rt
> on c.FilingType = rt.RTcode
> WHERE
> c.StateFips LIKE :Param_State
> OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY
> c.StateFips,
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition;
> Be sure to have an index by:
> - BBNew.dbo.fffCases.PostedDate <-- clustered one
> - BBNew.dbo.fffCases.StateFips
> - BBNew.dbo.fffCases.CourtCode
> - BBNew.dbo.fffCases.CourtType
> - BBNew.dbo.fffCases.FilingType
> - BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> - BBNew.dbo.fffCourtTypes.CTcode
> - BBNew.dbo.fffRecordTypes.rt.RTcode
>
> AMB
>
> "James Juno" wrote:
> > Chandra,
> >
> > This is great. Thank you so much. I haven't tried it yet - but it looks good.
> >
> > James.
> >
> > "Chandra" wrote:
> >
> > > Hi James
> > > I added parenthesis to your query:
> > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > BBNew.dbo.fffCases.StateFips,
> > > MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> > > MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> > >
> > > FROM
> > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> > > BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> > >
> > > WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > AND
> > > BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > >
> > > (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> > > OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> > > OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> > >
> > > GROUP BY
> > > BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> > >
> > > Please let me know if this is correct.
> > >
> > > If it is correct, then there is no need of changing the query, your query
> > > looks perfect.
> > > There might be Primary Keys declared on the tables that are used here.
> > >
> > > Else create Index on the columns that are involved in the where clause.
> > >
> > > please let me know your comments
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "James Juno" wrote:
> > >
> > > > Dear Forum,
> > > >
> > > > Please help me modify this query for optimum performance and suggest indexes
> > > > to create. Thanks. James
> > > >
> > > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > > > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > > > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > > > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > > > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > > > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > > > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > > > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > > BBNew.dbo.fffRecordTypes.RTdefinition
> > > >|||In general, it is a good practice to create a Primary Key constraint on
all tables (which will automatically create a unique index), and to
create indexes on foreign key constraints.
You did not post any DDL, so the keys and indexes cannot be reviewed.
But the query would benefit if all join columns were indexed.
Gert-Jan
James Juno wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition|||Gert-Jan,
I got the script that works. Thanks for your contribution.
James
"Gert-Jan Strik" wrote:
> In general, it is a good practice to create a Primary Key constraint on
> all tables (which will automatically create a unique index), and to
> create indexes on foreign key constraints.
> You did not post any DDL, so the keys and indexes cannot be reviewed.
> But the query would benefit if all join columns were indexed.
> Gert-Jan
>
> James Juno wrote:
> >
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
>

Query Performance and Index

Dear Forum,
Please help me modify this query for optimum performance and suggest indexes
to create. Thanks. James
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
Hi James
I added parenthesis to your query:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,
MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6) ),
MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND
BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
(BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
GROUP BY
BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
Please let me know if this is correct.
If it is correct, then there is no need of changing the query, your query
looks perfect.
There might be Primary Keys declared on the tables that are used here.
Else create Index on the columns that are involved in the where clause.
please let me know your comments
thanks and regards
Chandra
"James Juno" wrote:

> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
> BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
>
|||Adding to my previous post, what i feel is, the first OR should be replace
with AND:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode AND
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
"Chandra" wrote:
[vbcol=seagreen]
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6) ),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
|||Chandra,
This is great. Thank you so much. I haven't tried it yet - but it looks good.
James.
"Chandra" wrote:
[vbcol=seagreen]
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6) ),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
|||To avoid confusion, use ansi join instead old style. Also use alias to make
the statement more readable.
SELECT
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition,
c.StateFips,
MIN(substring(c.JudgmentDate, 1,6)),
MAX(substring(c.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCases as c
inner join
BBNew.dbo.fffCourtCodes as cc
on c.CourtCode = cc.MST_COURT_CODE
inner join
BBNew.dbo.fffCourtTypes as ct
on c.CourtType = ct.CTcode
inner join
BBNew.dbo.fffRecordTypes as rt
on c.FilingType = rt.RTcode
WHERE
c.StateFips LIKE :Param_State
OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY
c.StateFips,
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition;
Be sure to have an index by:
- BBNew.dbo.fffCases.PostedDate <-- clustered one
- BBNew.dbo.fffCases.StateFips
- BBNew.dbo.fffCases.CourtCode
- BBNew.dbo.fffCases.CourtType
- BBNew.dbo.fffCases.FilingType
- BBNew.dbo.fffCourtCodes.MST_COURT_CODE
- BBNew.dbo.fffCourtTypes.CTcode
- BBNew.dbo.fffRecordTypes.rt.RTcode
AMB
"James Juno" wrote:
[vbcol=seagreen]
> Chandra,
> This is great. Thank you so much. I haven't tried it yet - but it looks good.
> James.
> "Chandra" wrote:
|||Alejandro,
Great. Thank you.
James
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> To avoid confusion, use ansi join instead old style. Also use alias to make
> the statement more readable.
> SELECT
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition,
> c.StateFips,
> MIN(substring(c.JudgmentDate, 1,6)),
> MAX(substring(c.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCases as c
> inner join
> BBNew.dbo.fffCourtCodes as cc
> on c.CourtCode = cc.MST_COURT_CODE
> inner join
> BBNew.dbo.fffCourtTypes as ct
> on c.CourtType = ct.CTcode
> inner join
> BBNew.dbo.fffRecordTypes as rt
> on c.FilingType = rt.RTcode
> WHERE
> c.StateFips LIKE :Param_State
> OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY
> c.StateFips,
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition;
> Be sure to have an index by:
> - BBNew.dbo.fffCases.PostedDate <-- clustered one
> - BBNew.dbo.fffCases.StateFips
> - BBNew.dbo.fffCases.CourtCode
> - BBNew.dbo.fffCases.CourtType
> - BBNew.dbo.fffCases.FilingType
> - BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> - BBNew.dbo.fffCourtTypes.CTcode
> - BBNew.dbo.fffRecordTypes.rt.RTcode
>
> AMB
>
> "James Juno" wrote:
|||In general, it is a good practice to create a Primary Key constraint on
all tables (which will automatically create a unique index), and to
create indexes on foreign key constraints.
You did not post any DDL, so the keys and indexes cannot be reviewed.
But the query would benefit if all join columns were indexed.
Gert-Jan
James Juno wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
> BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
|||Gert-Jan,
I got the script that works. Thanks for your contribution.
James
"Gert-Jan Strik" wrote:

> In general, it is a good practice to create a Primary Key constraint on
> all tables (which will automatically create a unique index), and to
> create indexes on foreign key constraints.
> You did not post any DDL, so the keys and indexes cannot be reviewed.
> But the query would benefit if all join columns were indexed.
> Gert-Jan
>
> James Juno wrote:
>