SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sorting Problems
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

matrixrep
Starting Member

29 Posts

Posted - 11/28/2013 :  16:09:10  Show Profile  Reply with Quote
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)

Singapore
17584 Posts

Posted - 11/28/2013 :  19:39:42  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/28/2013 :  23:16:52  Show Profile  Reply with Quote
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
Go to Top of Page

matrixrep
Starting Member

29 Posts

Posted - 11/29/2013 :  08:37:56  Show Profile  Reply with Quote
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.


Edited by - matrixrep on 11/29/2013 09:02:03
Go to Top of Page

matrixrep
Starting Member

29 Posts

Posted - 11/29/2013 :  13:57:38  Show Profile  Reply with Quote
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
)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/30/2013 :  01:40:26  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000