asked
Grouping Dilemma
declare @YourTable table ([Part Type] varchar(50), [make] varchar(50), [model] varchar(50), [YEAR] varchar(50), [REMARKS] varchar(50), [Number] varchar(50)) -- SET 1 insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN1234' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN1234' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN4567' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN4567' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN8910' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN8910' --SET 2 insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN5555' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN5555' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'ALL WHEEL DRIVE', 'MCN9999' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN9999' insert into @YourTable select 'FLRLNR', 'Cadillac', 'CTS', '2014', 'REAR WHEEL DRIVE', 'MCN7777' /* For any Year, make, model / Part Type There will be n number of parts have x number of remarks. if n number of parts in a group of Year, make and model and part type contains 2 remarks then it sshould be in output only when those 2 remarks present with all n number. SET 1 Should appear in output because 2014 Cadillac CTS has 2 drive types available and all 3 numbers (MCN1234,MCN4567,MCN8910)for FLRLNR. SET 2 Should not appear in output because 'MCN7777' is not available with ALL WHEEL DRIVE, so entire group discarded. */
It might help to explain your question if you gave some example data and expected output - I'm not sure from the question which rows you want, which you don't and why?
This feels like a relational division type problem. The best sources for this are: - Joe Celko :
https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ - Peter Larsson :
http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx Using those as a guide, I've come up with this solution: declare @YourTable table (PT varchar(50), MK varchar(50), MO varchar(50), YR varchar(50), DVR varchar(50), CC varchar(50), SK varchar(50)) insert into @yourtable select 'Brake Pad','Acura','RDX','2010','ALL Wheel Drive','RAY','RAYATD1089C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','All Wheel Drive','RAY','RAYATD536C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','ALL Wheel Drive','RAY','RAYEHT1089H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYATD1089C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYEHT1086H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYEHT1089H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','All Wheel Drive','RAY','RAYATD536C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','ALL Wheel Drive','RAY','JAYEHT1089H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYATD1089C'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','RAYEHT1086H'; insert into @yourtable select 'Brake Pad','Acura','RDX','2010','Front Wheel Drive','RAY','JAYEHT1089H'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383194'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383195'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383382'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','RAY','RAYBH383382'; insert into @yourtable select 'JOOMLA','Cadillac','CTS','2008','All Wheel Drive','MOO','MOOK750325'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','Rear Wheel Drive','MOO','MOOK750325'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','All Wheel Drive','MOO','MOOK750326'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','Rear Wheel Drive','MOO','MOOK750326'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','All Wheel Drive','MOO','MOOK750190'; insert into @yourtable select 'Stabilizer','Cadillac','CTS','2008','Rear Wheel Drive','MOO','MOOK750190'; select YourTable2.* from @YourTable Yourtable join ( select PT, MK, MO, YR, count(*) i from ( select distinct PT, MK, MO, YR, dvr from @YourTable ) a group by PT, MK, MO, YR )b on
b.PT =
Yourtable.PT and
b.MK =
Yourtable.MK and
b.MO =
Yourtable.MO and b.YR = Yourtable.YR join ( select SK, count(*) i from ( select distinct SK, dvr from @YourTable ) a group by SK )c on
c.SK =
Yourtable.SK and b.i > c.i right join @YourTable Yourtable2 on
b.PT =
Yourtable2.PT and
b.MK =
Yourtable2.MK and
b.MO =
Yourtable2.MO and b.YR = Yourtable2.YR where
Yourtable.SK is null I'd suggest digesting those links to understand what's going on here
Can you add that example data to the question, along with the expected output - it makes it easier to understand then
@Binod : I'm confused. Is this an addition to the original question or a change in the requirements. At first you wanted to exclude the entire PT if any SK within it didn't have all the DVRs, but now you only want to exclude the SK?
Please can you update the example output above - I am still struggling to understand what you require
Binod: If you post the sql statement to populate the same data and tabular o/p you want it might be easier for us to understand the requirement.. ~Kiran
16 People are following this question.
