SQL Server 2008 :: Left Joins And Query Plan Compile Times

Mar 8, 2015

We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.

So rather then the following exemplary fragment

select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id

Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.

I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.

View 9 Replies


ADVERTISEMENT

SQL Server 2008 :: Graphical (Include Execution Plan) Left On By Default?

Jun 25, 2015

Is there a way to leave the graphical 'Include Execution Plan' on by default in SSMS? I don't know how many times I run a long-running query, say to myself, "wow, that took a while; I wonder what the execution plan looks like?" only to realize that I left it turned off. Now I have to turn it on, and wait for the query to run again. I'm guessing there's a setting in the options somewhere to always leave it on, but I'm not sure where

View 2 Replies View Related

SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache

Mar 14, 2015

Is only one plan is kept for one query in plan cache?

i heard generally hash is created for a query and plan is search with this hash.

View 2 Replies View Related

Multiple Left Joins (2 Left Joins 1 Big Headache)

Sep 1, 2005

Hi All,

Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.


My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx

I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.

View 2 Replies View Related

SQL Server 2008 :: Iterate Query Using A Loop As Many As 5 Times Max?

Mar 20, 2015

If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End

I want to iterate this query using a loop as many as 5 times max.

View 3 Replies View Related

Query With A Lot Of Left Joins - Rewrite?

Sep 21, 2007

Hello,

I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?

How would I rewrite left joins? Any examples?


Thanks.

View 5 Replies View Related

Query Plan SQL Server 2008

Apr 30, 2008

I have a person table with 1 billion rows on it, partitioned equally at 10 million rows per partition. The primary key constraint is a composite of an identity column and ssn( char(11) ) with the partitioning column built on the SSN.



This is built on my home grown workstation:

Microsoft 2008 Server 64 bit, Microsoft SQL server 2008 64 bit, Intel 2.66 quad core, 8 gb ram, Os/ raid 1, data on 6 drives hardware/software raid 50, transaction logs on 4 drive raid 10, all drives sata II/ 3gb burst.



I have updated statistics on the table and I have 2 queries that give clustered index seek , one never comes back before I get impatient, the other comes back instantly, and the showplan looks the same for both queries.



SELECT *

FROM Person

WHERE PersonKey > -1 and SSN = '219-09-3987'



AND



SELECT TOP 100 PERCENT *

FROM Person

WHERE PersonKey > -1 and SSN = '219-09-3987'



Incidentally the query with the top 100 percent is the one that returns instantly.



I am puzzled

1) Why the estimated plan looks the same

2) Why a top 100 Percent query is faster than one without it



Timothy A. Vanover

View 2 Replies View Related

Multi Table Query Using Two LEFT JOINs

Jul 24, 2012

I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.

SELECT
quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
FROM quote
LEFT JOIN machining_operations
ON machining_operations.quote_num = quote.quote_id
LEFT JOIN parts
ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
GROUP BY quote.quote_id

View 10 Replies View Related

Query Optimizer Problems With Many LEFT/RIGHT OUTER JOINs

Mar 14, 2008

I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).

I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.


Basically I have a query:

SELECT ....
FROM TABLEA

INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'


The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.

If I simply change the query to:

SELECT ....
FROM TABLEB

INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'

The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.

The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.

Any ideas how I can fix this?

View 1 Replies View Related

SQL Server 2008 :: Query Plan Output List (IsBaseRow)

May 6, 2015

Looking at an execution plan today I noticed something I've not seen before. The plan includes a non clustered index seek, followed by a RID lookup on the heap. The output list for the index seek contains the expected "Bmk" column (in this case "Bmk1473"), but also includes "IsBaseRow1475". This isn't a column from the table.

View 3 Replies View Related

SQL Server 2008 :: Query Execution Plan Of Stored Procedure

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 8 Replies View Related

SQL Server 2012 :: Date / Time Calculations For Work-plan Times?

May 31, 2015

I have a table that stores working hrs, such as

RecID,StaffID,StartDate,EndDate
17,969,2015-05-18 00:00:00.000,2015-05-18 06:00:00.000
18,969,2015-05-18 18:00:00.000,2015-05-19 06:00:00.000
19,969,2015-05-19 18:00:00.000,2015-05-20 06:00:00.000
20,969,2015-05-20 18:00:00.000,2015-05-21 06:00:00.000
21,969,2015-05-21 18:00:00.000,2015-05-22 06:00:00.000
22,969,2015-05-22 18:00:00.000,2015-05-23 06:00:00.000
23,969,2015-05-23 14:00:00.000,2015-05-24 08:00:00.000
24,969,2015-05-24 22:00:00.000,2015-05-25 00:00:00.000

So working times can go over midnight, there can be more than one working period in a day etc.

For this staff member the summary of the weeks work will be

18/05/2015 - 12 hrs
19/05/2015 - 12 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 12 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs

Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.

So for example

If this person

had a days holiday on the 22nd, shown in the HOLIDAY table as

StaffID,DateFrom, DateTo
969, 22/05/2015 00:00:00.000,22/05/2015 23:59:59.000

A Leave of Absence on the 20th, shown in the LEAVE table as

StaffID,DateFrom, DateTo
969,20/05/2015 12:00:00.000,20/05/2015 16:00:00.000

And was off sick on the morning of the 19th, shown in the SICKNESS Table as

StaffID,DateFrom, DateTo
969, 19/05/2015 00:00:00.000,19/05/2015 11:59:59.000

Now the Summary table should now show

18/05/2015 - 12 hrs
19/05/2015 - 6 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 0 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs

The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?

I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.

View 1 Replies View Related

SQL Server 2008 :: Same Database With Two Different Machines At Different Times

Jun 19, 2015

For security reasons customer wants to put a SQL database on an encrypted thumb drive (IronKey). Here's the rub though. He wants to be able to work with the data on a workstation. Then, if he takes his laptop out of the office he wants to be able to simply plug that thumb drive into the laptop and fire up SQL on the laptop and use that same database. Procedurally this would work in that the database can be created so that the location is the same from both machine viewpoints, however will the two different SQL instances allow moving the database back and forth like this?

View 9 Replies View Related

SQL Server 2008 :: Putting Rows Together To Get Total Times

Jan 30, 2015

I am building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistencies with this, and i'm hoping to exclude them.. Here is a sample table:

Function | Employee | DateTime
--------------------------------------------
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM

Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2

Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type.

View 8 Replies View Related

SQL Server 2008 :: Partitioning - Left Or Right?

Jul 1, 2015

Normally it is recommended to leave an empty partition on both the front and back ends of a table to avoid data movement when merging/splitting. But I have some questions based on my scenario, which is a table partitioned by a load date, so all records in a partition contain the same date, not a range of dates.

If I use range left, once I switch out the first partition it would become empty, so would there be data movement when I merge it into the next partition? The real issue though is that we will not just be removing the first partition, but "random" partitions throughout the table. Will this work?

If I use range right, when I split the last partition to create a new one it doesn't seem there would be any data movement there either. Am I missing something?

Basically I'm wondering if I should use range left or right. Most recommend using right, but then the boundary value is not the value in the partition. This could potentially result in someone deleting the wrong data if they are not careful. So is there any reason not to use left in my scenario?

I did a test of removing a partition in the middle and it worked just fine; this was using range right. I have about 6 million rows per partition. I also tested splitting at the end and it worked fine. I'll rebuild it with range left and test.

View 0 Replies View Related

Left Joins And Where

Apr 25, 1999

To the wise,

This may be a very simple problem but it's been racking may brains for a while and I just can't seem to think it through clearly.

I'm trying to return a query which uses a left join and where. I'm hoping to get a result set which shows - let us says all the departments in a company. I would like to see all the department but only the names of department heads that earn 20.000+.

In MS Access I used a subquery. My subquery returned only departments with department heads that earned 20.000+ - I then left joined the departments table to that query - no problem.

With MSSQL I've tried IN, ANY, ALL but my result sets only returns the departments that earn 20.000+ and the employees for those particular departments.

I'm thinking there must be some way of doing this without having to use a union clause.

Thanks for taking the time to read this message through.

Sincerely,


Arthur Lambiris

View 6 Replies View Related

Left Joins

Nov 2, 2006

My understanding of relevant topics as well as SQL Books Online definition of left outer joins is that each record in the left table will be retrieved and where no associated right record exists then null values will be displayed for records in the right hand table but I've obviously misunderstood and would be grateful if someone could show me how to produce the required effect.

My scenario is pretty simple: 2 tables DiningTables and Reservations with columns as follows:

DiningTables: TBL_ID and TBL_Location - TBL_ID is the primary key

Reservationss::RES_TBL_ID and RES_Diner_Name - RES_TBL_ID is the primary key

There are 8 records in DiningTables and 4 records in Reservations and the objective is obtain the following output:

TBL_ID RES_Diner_Name

1 Jones

2 Smith

3 Bloggs

4 Mack

5 null

6 null

7 null

8 null

The SQL query I used is

SELECT Reservations.RES_Diner_Surname, DiningTables.TBL_ID
FROM DiningTables LEFT OUTER JOIN
Reservations ON DiningTables.TBL_ID = Reservations.RES_TBL_ID



That query generates 11 rows as follows:

TBL_ID RES_Diner_Name

1 Jones

1 Smith

1 Bloggs

1 Mack

2 null

3 null

4 null

5 null

6 null

7 null

8 null

I'm clealry missing something incredibly obvious and I kinda feel like the village idiot and would be extremely grateful for a clue!!

View 5 Replies View Related

Left Or Right Joins

Oct 25, 2007

Hi All,

This may be a stupid question. But I just want to make sure i'm going in right direction.
I wrote the following query. My purpose is to retrieve all the members who submitted loan applications.
I just want to make sure my query is right? Can anyone veryfy this query?
Do I have to use loanApplication table first instead of members. Also Do I need to use right outer joins instead of left outer joins?





Code Block
SELECT Member.CUMemberId, LoanApplication.SubmittedOn, Member.LastName, Member.FirstName, Member.MiddleName, LoanApplication.Amount,
LoanApplication.Decision, LoanApplication.Term, Rate.InterestRate, LoanApplication.Status, Member.CuStatus
FROM Member INNER JOIN
MemberLogon ON Member.Id = MemberLogon.MemberFK INNER JOIN
LoanApplication ON Member.LastLoanApplicationFK = LoanApplication.Id AND Member.Id = LoanApplication.MemberFK LEFT OUTER JOIN
Account ON Member.Id = Account.MemberFK AND LoanApplication.LoanFK = Account.Id LEFT OUTER JOIN
Rate ON LoanApplication.RateFK = Rate.Id
WHERE (LoanApplication.Status = 'Submitted')





Thanks

View 3 Replies View Related

SQL Server 2008 :: Left Function And CHARINDEX

Mar 30, 2015

SELECT LEFT(InstanceName,CHARINDEX(' ',InstanceName)-1)
FROM ServerDB

Returns the following error.

Invalid length parameter passed to the SUBSTRING function.

View 2 Replies View Related

SQL Server 2008 :: Move Row Values To The Left

Oct 20, 2015

I have imported a txtfile to access and have some problem making the data appear in my wanted way. I want to shift the cells in the first row one step to the left, i.e to overwrite the zeros with the left value. Then I want to assign the first row values as field names.

Current data

Field1 Field2 Field3 Field4 Field5
0 Name Major Sector Hi
karl
per
anna

Wanted outcome

Name Major Sector Hi
karl
per
anna

View 8 Replies View Related

Rewriting Left Joins

Sep 21, 2007

Hello,
I am working on a query that has 11 left join statements, some are hitting against reference data that has a small amount of records, whereas others not so small.  From a performance standpoint, should I look at rewriting this query, and how would I do so?  What is an alternative to left joins; any examples anyone has?
Thanks.

View 2 Replies View Related

OUTER LEFT JOINs On SQL CE

Feb 17, 2008

Hi,

First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?

I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:

SELECT A.*
, F.Descricao AS FamiliasDescricao
, M.Descricao AS MarcasDescricao
, I.Descricao AS IVADescricao
FROM Artigos AS A
LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID
LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID
LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID
INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;

The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error [7]", Native error 25607, an the stack trace ends with:

em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s()
em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()

If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.

Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?

Any other sugestions?

Thank you,
Hugo Lopes

View 6 Replies View Related

Multiple Left Joins Help?

Apr 27, 2004

Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.



These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this



emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID)

emailRecipients table (emailID_PK, RecipientID, SentToEmail)

luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)



Thanks

View 2 Replies View Related

Nested Joins - Joining One Table To Another Multiple Times

Jan 9, 2006

Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks

View 1 Replies View Related

Right/left Outer Joins && Performance

Nov 13, 2003

Other than being much less readable, is there a downside to combining left and right outer joins in the same SELECT? I'm reviewing some generally poor code done by a contractor and it's peppered with queries with both left and right joins. I've always thought it was just a semantic difference, but I was just wondering if, other than readability, there were any performance issues.

Thanks,
Pete

View 1 Replies View Related

Left Joins And Inline Views

Jan 12, 2005

Hi,

I am working a DTS package and I need to Join to completely differnet tables in such a way that I need to do an inline view and an Outer Join. In this current form, it drops all columns for a day if one of the inline views returns null.



SELECT 01 as WHSE_ID
, A.On_Time
, B.Early
, C.Late
, (D.AVG_Duration / (A.On_Time + B.Early + C.Late))AS AVG_Duration
, E.DelDate
, F.*
FROM

(SELECT COUNT(SDD_Status) AS On_Time
, SDD_Date as On_Time_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'On Time'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) a,

(SELECT COUNT(SDD_Status) AS Early
,SDD_DATE As Early_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Early'
And SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) b,

(SELECT COUNT(SDD_Status) AS Late
, SDD_Date As Late_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Late'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) c,

(SELECT SUM(CAST(SDD_Stay AS NUMERIC)) AS AVG_Duration
, SDD_Date As Stay_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) d,

(SELECT DISTINCT(SDD_Date) AS DelDate
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date)e,

(SELECT *
FROM WAREHOUSE_METRICS
WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
AND WHSE_ID = 2
GROUP BY MTRC_DTE
, MTRC_ID
, WHSE_ID
, INVN_LVL_CS_CNT
, INVN_LVL_DLLR
, INVN_LVL_PLLT_CNT
, RCV_CS_CNT
, SHP_CS_CNT
, SRVC_LVL_PCT
, SCRTCH_CNT
, DROP_CNT
, RPLNSH_CNT
, DMG_CNT
, RET_CNT
, PICK_CYC_CNT
, PICK_ERR_CNT
, RSV_CHK_CNT
, ERR_CNT
, DLY_CS_VRNC
, DLY_DLLR_VRNC
, CMB_THRUPUT
, DELAY_MINS_CNT
, DELAY_PCT
, UNLOAD_AVG
, LABOR_AVG
, SELECT_HR_CNT
, CRT_USERID
, CRT_DTE_TME
, UPD_USERID
, UPD_DTE_TME) f

WHERE a.On_Time_Date = E.DelDate
AND B.Early_Date = E.DelDate
AND C.Late_Date = E.DelDate
AND D.Stay_Date = E.DelDate
AND F.MTRC_DTE = E.DelDate

GROUP BY E.DelDate
, A.On_Time
, B.Early
, C.Late
, AVG_Duration
, A.On_Time_Date
, B.Early_Date
, C.Late_Date
, D.Stay_Date
, F.WHSE_ID
, F.INVN_LVL_CS_CNT
, F.INVN_LVL_DLLR
, F.INVN_LVL_PLLT_CNT
, F.RCV_CS_CNT
, F.SHP_CS_CNT
, F.SRVC_LVL_PCT
, F.SCRTCH_CNT
, F.DROP_CNT
, F.RPLNSH_CNT
, F.DMG_CNT
, F.RET_CNT
, F.PICK_CYC_CNT
, F.PICK_ERR_CNT
, F.RSV_CHK_CNT
, F.ERR_CNT
, F.DLY_CS_VRNC
, F.DLY_DLLR_VRNC
, F.CMB_THRUPUT
, F.DELAY_MINS_CNT
, F.DELAY_PCT
, F.UNLOAD_AVG
, F.LABOR_AVG
, F.SELECT_HR_CNT
, F.CRT_USERID
, F.CRT_DTE_TME
, F.UPD_USERID
, F.UPD_DTE_TME
, F.MTRC_ID
, F.MTRC_DTE
Order By E.DelDate


Please excuse the length of the code but one of the tables has a lot of columns.

Can anyone tell me if it's possble to do a join on an inline view?

Any help would be greatly appreciated.

Thanks in advance,

John

View 5 Replies View Related

Left Joins And Column Mapping

Jan 30, 2006

Hello,

I am trying to understand the concept of left joins. I have the following query and am not sure about the left joins.

I am familiar with joins but the left join below is a little confusing.Below it seems like a third table is involved. Is this because there is no column to map to in the from table? Also, since tables sl and sc are mapped based on the SecurityID column and sl and ex do not have any common columns, table sc is mapped to ex using the left join? Which table's data will be returned based on the left join?

I checked the column type for the Exchange column(ex.LSECode) and it appears varchar(3).



SELECT SecurityID = sl.SecurityID
, Security = RTRIM(sec.Name) + ' - ' + RTRIM(sec.Description)
, Ticker = ISNULL(ids.RIC, ids.Ticker)
, Sedol = ids.Sedol
, ISIN = ids.ISIN
, Exchange = ex.LSECode
, Country = cty.iso_code

FROM #SecList sl

JOIN SecurityClassification sc
ON sl.SecurityID = sc.securityId
AND sc.source = 99


LEFT JOIN exchange_table ex
ON sc.ExchangeID = ex.exchange

Thanks in advance!!!
novicesql123

View 2 Replies View Related

Complexity Of Nested Self Left Joins?

Jul 23, 2005

Hi,I'm curious about the computational complexity of a query I have. Thequery contains multiple nested self left joins, starting with a simpleselect, then doing a self left join with the results, then doing a selfleft join with those results, etc. What puzzles me is that the timerequired for the query seems to grow exponentially as I add additionalleft joins, which I didn't expect. I expected the inner select toreturn about 25 rows (it does), then I expected the self join to resultin about 25 rows (it does), etc. Each join just adds another column; itdoesn't add more rows. So the left part of the join is staying the samesize, and so is the right part of the join, since I'm always joiningwith the same table.So I would think the time for this query should be (time to join 25rows against the source table) * (num joins), but it seems to besomething like (num rows) ^ (num joins). Any ideas? I'm just trying tounderstand the system a little better. (But if you have any ideas aboutimproving the query, I'm always open to those, too.)The execution plan is what you'd expect: an index seek loop-joined withanother index seek, the results of which are merge-joined with anotherindex seek, the results of which are merge-joined with another indexseek, ad nauseum, until a final "compute scalar cost (39%)" and "select(0%)"For the brave and curious, I've pasted the query below.Thanksselect 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 (((((((((((selectdistinct cp_yyyymm from cp_deal_history where cp_deal_id in('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E','BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymmbetween 200304 and 200504) as x left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') astable0 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 table1on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') astable10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymm

View 4 Replies View Related

How Do You Do Multiple Left Outer Joins???

May 31, 2006

I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys.  I bolded the three IDs that could be NULL or have a foreign key for a value.  An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet.  Right now I only get profiles that have foreign key values and it misses the rest in the search.   So NULL MakeID or ModelID no result on that item my SQL statement below.  Using SQL Server 2005.  Pretty new this to SQL and databases but so far this has been the only trying part.

Thanks

String dbsql = "SELECT a.EquipmentID " +
                         "     , a.SerialNo " +
                         "     , b.Category " +
                         "     , c.Subcategory " +
                         "     , d.Make " +
                         "     , e.Model " +
                         "     , f.Status " +
                         "  FROM tblEquipInfo a " +
                         "     , tblEquipCat b " +
                         "     , tblEquipSubcat c " +
                         "     , tblEquipMake d " +
                         "     , tblEquipModel e " +
                         "     , tblStatus f " +
                         " WHERE b.Category = '" + val + "' " +
                         "   AND a.CategoryID = b.CategoryID " +
                         "   AND a.SubcategoryID = c.SubcategoryID " +
                         "   AND a.MakeID = d.MakeID " +
                         "   AND a.ModelID = e.ModelID " +
                         "   AND a.StatusID = f.StatusID";

 

 

View 3 Replies View Related

SQL Server 2008 :: Tell Number Of Times People Use SSMS To Change Client Data?

Mar 21, 2015

I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?

View 6 Replies View Related

In Outter Joins, What Makes A Table Either Right Or Left?

Dec 16, 2002

Until today, I was always under the impression that left vs. right was determined by which side of the comparison operator the table was located.

In other words:
LEFT JOIN LeftTable.ID = RightTable.ID

would pull all the records from LeftTable and those that matched from from RightTable and that:

RIGHT JOIN RightTable.ID = LeftTable.ID

would pull exactly the same result set but I was wrong. So, if it is not the table position in relation to the comparison operator, is it simply that the tables listed first in the FROM clause aren the ones "Left" of those subsequently entered?

View 2 Replies View Related

Getting Multiple Counts With Grouping And Left Joins

Mar 3, 2008

Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?

Thanks in advance.

drop table percentMinorities;
create view percentMinorities as
select s1.schoolid,
round(count(s2.studentid)/count(s1.studentid),2) as percentWhite,
round(count(s3.studentid)/count(s1.studentid),2) as percentBlack,
round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic,
round(count(s5.studentid)/count(s1.studentid),2) as percentAsian

from



students as s1 left join
(students as s2 left join
(students as s3 left join
(students as s4 left join students as s5
on s4.studentid is not null and s5.ethnicity = 'A')
on s3.studentid is not null and s4.ethnicity = 'H')
on s2.studentid is not null and s3.ethnicity = 'N')
on s1.studentid is not null and s1.ethnicity = 'O'

group by s1.schoolid

View 10 Replies View Related

Speed Up 6 Consecutive Left Outer Joins

Apr 28, 2008

Hi,

The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.

declare @keyWord varchar(40)
set @keyWord = 'merc'
set NOCOUNT on

SELECT distinct clr.clr_id
FROM CLR
LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
OR clr_nm.clr_nm like '%'+@keyWord+'%'
OR clr_use_yr.yr_num like '%'+@keyWord+'%'
OR paint_cd.paint_cd like '%'+@keyWord+'%'
OR modl.modl_nm like '%'+@keyWord+'%'

The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.

View 19 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved