Solved

make efficient code to locate a record in 2 years...

Posted on 2013-10-22
4
152 Views
Last Modified: 2013-10-22
can you suggest what can be done to improve this code.. this is basically looking for one person under two branches in consequtive years..

thanks
0
Comment
Question by:25112
  • 2
  • 2
4 Comments
 
LVL 5

Author Comment

by:25112
Comment Utility
select
"pogad00"."OFFICE_CODE" AS "TERR",
"pogad00"."OFFICE_NAME" AS "BORD_Name",  
"pogad00"."COUNTY" AS "County_Name",
"pogad01"."LOCATION_ID" AS "Location_Number",
"pogad01"."LOCATION_NAME" AS "Location_Name",
"pogad02"."EMP_ID" AS "EMP_ID",
"pogad02"."EMP_LAST_NM" AS "EMP_Last_Name",
"pogad02"."EMP_FIRST_NM" AS "EMP_First_Name",
"Further_Branch"."OFFICE_Code" AS "Other_TERR",
"Further_Branch"."OFFICE_Name" AS "Further_Branch_Name",
"Further_Branch"."County_Name" AS "Other_County_Name",
"Further_Branch"."Location_ID" AS "Other_Location_Number",
"Further_Branch"."Full_Location_Name" AS "Other_Location_Name",
"Further_Branch"."EMP_Last_Name" AS "Other_EMP_Last_Name",
"Further_Branch"."EMP_First_Name" AS "Other_EMP_First_Name"

from

"PDS"."OFFICE" "pogad00",
"PDS"."LOCATION" "pogad01",
"PDS"."EMPHIST" "pogad02",

(
select "Reporting_OFFICE"."Reporting_OFFICE_Code" AS "OFFICE_Code", "Reporting_OFFICE"."Reporting_OFFICE_Name" AS "OFFICE_Name", "Reporting_OFFICE"."Reporting_OFFICE_Type" AS "Reporting_OFFICE_Type", "Reporting_OFFICE"."County_Name" AS "County_Name", "pogad10"."LOCATION_ID" AS "Location_ID", "pogad10"."LOCATION_NAME" AS "Full_Location_Name", "pogad10"."GROW_TYPE_CODE" AS "GROWTH_Type_Code", "pogad11"."EMP_ID" AS "EMP_ID", "pogad11"."EMP_LAST_NM" AS "EMP_Last_Name", "pogad11"."EMP_FIRST_NM" AS "EMP_First_Name"

from

(
select distinct "REPORT_OFFICE"."REP_OFFICE_CODE" AS "Reporting_OFFICE_Code", "REPORT_OFFICE"."REP_OFFICE_NAME" AS "Reporting_OFFICE_Name", "REPORT_OFFICE"."REP_OFFICE_TYPE" AS "Reporting_OFFICE_Type", "REPORT_OFFICE"."OFFICE_KEY" AS "OFFICE_KEY", "REPORT_OFFICE"."COUNTY" AS "County_Name"
from "PDS"."REPORT_OFFICE" "REPORT_OFFICE"
where "REPORT_OFFICE"."REP_OFFICE_CODE" <> 'TE01' and "REPORT_OFFICE"."REP_OFFICE_TYPE" in ('DE', 'TO')) "Reporting_OFFICE",

"PDS"."LOCATION" "pogad10",
"PDS"."EMPHIST" "pogad11"

where

("Reporting_OFFICE"."Reporting_OFFICE_Type" in ('DE') or "pogad10"."GROW_TYPE_CODE" = '4T') and
"pogad11"."OFFICE_KEY" = "Reporting_OFFICE"."OFFICE_KEY" and
"pogad11"."Location_Date" =  '2012-01-01' and
year("pogad11"."EMPHIST_DATE") + 1 = year("pogad11"."Location_Date") and
month("pogad11"."EMPHIST_DATE") = 01 and
day("pogad11"."EMPHIST_DATE") in ('4', '3') and
"pogad10"."LOCATION_CODE" = "pogad11"."LOCATION_CODE"
) "Further_Branch"

where  
"pogad00"."OFFICE_CODE" = 'TE01' and
"pogad02"."Location_Date" =  '2012-01-01' and
year("pogad02"."EMPHIST_DATE") + 1 = year("pogad02"."Location_Date") and
month("pogad02"."EMPHIST_DATE") = 01 and
day("pogad02"."EMPHIST_DATE") in ('4', '3') and
("pogad00"."LEVEL_AGGR" in ('DE') or "pogad01"."GROW_TYPE_CODE" = '4T') and
"pogad00"."OFFICE_KEY" = "pogad02"."OFFICE_KEY" and
"pogad01"."LOCATION_CODE" = "pogad02"."LOCATION_CODE" and
"pogad02"."EMP_ID" = "Further_Branch"."EMP_ID"

order by 5 asc , 7 asc , 8 asc , 10 asc , 13 asc , 14 asc , 15 asc
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
Ug - lee.  For starters..

What's the purpose of the double quotes?  Delete them.
Use table alias hat have some inference to what the table is, i.e. o l and e instead of pogad00, pogad01, and pogad02.
I don't see anywhere in your code where the tables referenced in the FROM statement have a JOIN that defines the columns in which these tables are related.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
thanks for that review...

OK- the code is poor mainly because it is written by ad-hoc report generator.. i think it is worth writing a new query rather than making the joins on these..
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Make the joins.  Without them, SQL has no idea how to map the tables, so you'll have a CROSS JOIN where three tables with 100 rows each returns 100 x 100 x 100 = one million rows.

Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now