Sorting Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sorting Problems

Author

Topic

matrixrep
Starting Member

30 Posts

Posted - 2013-11-28 : 16:09:10

Here is my problem

Table 1

Year---Plant---TRUCK---ID
2013---ABC-----1JA-----001
2013---ABC-----1JB-----002
2013---ABC-----1JB-----003
2013---ABC-----1HC-----745
2013---ABC-----1HD-----745
2013---ABC-----1HE-----345

TABLE 2

Year---Plant---TRUCK
2013---ABC-----1JA
2013---ABC-----1HC
2013---ABC-----1HE

The result should be:

TABLE 3

Year---Plant---TRUCK--Number of ID
2013---ABC-----1JA---- 3
2013---ABC-----1HC---- 1
2013---ABC-----1HE---- 1

For 1JA, i have 3 ID since 1JB is not in table 2 and ID are all different
For 1HC, i have 1 ID since 1HE is not in table 2 and ID is the same

Any help is deeply appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-28 : 19:39:42

quote:
For 1JA, i have 3 ID since 1JB is not in table 2 and ID are all different

So you are counting 1JB under 1JA ? how to determine 1JB should be counted under 1JA ?

quote:
For 1HC, i have 1 ID since 1HE is not in table 2 and ID is the same

How about 1HD ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 23:16:52

Didnt understand your full requirement. however based on your sample data and output i think this is what you're after


;With temp
AS
(
SELECT t1.*,t2.Plant
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.Year = t1.Year 
AND t2.Plant = t1.Plant 
AND t2.TRUCK = t1.TRUCK 
)
SELECT t.Year,t.Plant,t.TRUCK,Cnt
FROM Temp t
OUTER APPLY (SELECT TOP 1 ID
             FROM Temp
             WHERE Year = t.Year 
             AND Plant = t.Plant 
             AND TRUCK = t.TRUCK 
             AND ID > t.ID
             AND Plant IS NOT NULL
             )t1
CROSS APPLY (SELECT COUNT(ID) AS Cnt
             FROM Temp
             WHERE ID >= t.ID AND( ID < t1.ID OR t1.ID IS NULL)
             )t2
WHERE t.Plant IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

matrixrep
Starting Member

30 Posts

Posted - 2013-11-29 : 08:37:56

I had a mistake in my first post

Table 1

Year---Plant---TRUCK---ID
2013---ABC-----1JA-----001
2013---ABC-----1JB-----002
2013---ABC-----1JB-----003
2013---ABC-----1HC-----745
2013---ABC-----1HD-----745
2013---ABC-----1HE-----345

TABLE 2

Year---Plant---TRUCK
2013---ABC-----1JA
2013---ABC-----1HD
2013---ABC-----1HE

The result should be:

TABLE 3

Year---Plant---TRUCK--Number of ID
2013---ABC-----1JA---- 3
2013---ABC-----1HD---- 1
2013---ABC-----1HE---- 1

For 1JA, i have 3 ID since 1JB is not in table 2 and ID are all different
For 1HD, i have 1 ID since 1HC is not in table 2 and ID is the same

It is not always the smallest number of truck that is regrouping under. Like 1JB regroup under 1JA and 1HC regroup under 1HD.
In fact, all trucks that is not listed in table 2 will regroup like all 1J% truck regroup under 1JA and all 1H% under 1HD.

matrixrep
Starting Member

30 Posts

Posted - 2013-11-29 : 13:57:38

Thank you again visakh16

After this part of the script, it helped me configure the rest of what i wanted.

;With temp
AS
(
SELECT t1.*,t2.Plant
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.Year = t1.Year
AND t2.Plant = t1.Plant
AND t2.TRUCK = t1.TRUCK
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 01:40:26

quote:
Originally posted by matrixrep

Thank you again visakh16

After this part of the script, it helped me configure the rest of what i wanted.

;With temp
AS
(
SELECT t1.*,t2.Plant
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.Year = t1.Year
AND t2.Plant = t1.Plant
AND t2.TRUCK = t1.TRUCK
)

you're welcome
Glad that I could help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources