January 6, 2015 at 12:05 am
I have a query like :
SELECT DISTINCT C1.containerCode ,C1.containerName,
(SELECT C1.containerCode as containerCode,SE.createdOn
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0 AND C1.isDeleted =0
inner join
( select C1.containerCode, MAX(SE.createdOn) as maxdate
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0
WHERE C1.isDeleted = 0
group by C1.containerCode) CC
on CC.containerCode = C1.containerCode
and CC.maxdate = SE.createdOn) AS NEWLOCATION
----------------
For the column, I need to show only the field C1.containerCode as NEWLOCATION and SE.createdOn is not required. But it is used there, to fetch the record with max createdOn date. How can I fetch the fetch the C1.containerCode as NEWLOCATION and show it
January 6, 2015 at 12:12 am
Please provide table DDL, some sample data and desired output.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2015 at 1:51 am
Junglee_George (1/6/2015)
I have a query like :SELECT DISTINCT C1.containerCode ,C1.containerName,
(SELECT C1.containerCode as containerCode,SE.createdOn
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0 AND C1.isDeleted =0
inner join
( select C1.containerCode, MAX(SE.createdOn) as maxdate
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0
WHERE C1.isDeleted = 0
group by C1.containerCode) CC
on CC.containerCode = C1.containerCode
and CC.maxdate = SE.createdOn) AS NEWLOCATION
----------------
For the column, I need to show only the field C1.containerCode as NEWLOCATION and SE.createdOn is not required. But it is used there, to fetch the record with max createdOn date. How can I fetch the fetch the C1.containerCode as NEWLOCATION and show it
This isn't a properly-formed query. It looks like you are attempting to retrieve the last-known position of a container from table StoringsEvents, is this correct?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2015 at 1:53 am
More readable:
SELECT DISTINCT
C1.containerCode,
C1.containerName,
(SELECT C1.containerCode AS containerCode,
SE.createdOn
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0
AND C1.isDeleted = 0
INNER JOIN (SELECT C1.containerCode,
MAX(SE.createdOn) AS maxdate
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0
WHERE C1.isDeleted = 0
GROUP BY C1.containerCode
) CC ON CC.containerCode = C1.containerCode
AND CC.maxdate = SE.createdOn
) AS NEWLOCATION
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 2:43 am
Yes Chris, You are correct. I want to get last position from StoringEvents Table.
January 6, 2015 at 2:57 am
Junglee_George (1/6/2015)
Yes Chris, You are correct. I want to get last position from StoringEvents Table.
Thanks. Can you follow Koen's advice and post scripts please? Also, can you provide us with a definition of the following columns:
StoringsEvents.ITEMINCID
StoringsEvents.ITEMSQLID
StoringsEvents.CONTAINERINCID
StoringsEvents.CONTAINERSQLID
Containers.CONTAINERINCID
Containers.CONTAINERSQLID
Cheers.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2015 at 12:09 pm
If the query otherwise works the way you want it to, just remove the unneeded column from the subquery:
...,
(SELECT C1.containerCode as containerCode --,SE.createdOn
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0 AND C1.isDeleted =0
inner join
( select C1.containerCode, MAX(SE.createdOn) as maxdate
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0
WHERE C1.isDeleted = 0
group by C1.containerCode) CC
...
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply