GroupHell - 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

General SQL Server Forums

New to SQL Server Programming

GroupHell

Author

Topic

Vivan
Starting Member

9 Posts

Posted - 2015-04-21 : 14:09:23

Hello All,

May i get your expertise suggestion on below scenario if possible.

3 rules to get the output.

1st: group by on column PT,MK,MO AND YR

some number of Parts and some number of Remarks and some numbers of remarks3 we will get after grouping PT,MK,MO AND YR.

2nd: if only one distinct value in remarks 1 in a group, need to exclude from the output.

if all the part not present with all the remarks then entire group
should get exclude.

3rd: Only those records should be in output when all the part in a group present with all the available remarks,
and all the value of remarks 3 of the group should also present with all the remarks

DECLARE @myTable TABLE
(

PT VARCHAR (50),
MK VARCHAR (50),
MO VARCHAR (50),
PART VARCHAR (50),
REMARKS VARCHAR (50),
[REMARKS3] VARCHAR (50),
YR VARCHAR (50)
)

insert @myTable

--SET 1 – there’s 1 number (BK80) that doesn’t have the four wheel drive drive type so this group Excluded

SELECT 'ABS', 'MAZDA','Protégé','1990', 'four wheel drive','Front', 'AK75' UNION ALL
SELECT 'ABS', 'MAZDA','Protégé','1990', 'rear wheel drive','Front', 'AK75' UNION ALL
SELECT 'ABS', 'MAZDA','Protégé','1990', 'rear wheel drive','Front', 'BK80' UNION ALL

--SET 2 – this would be in output because for a group of mk,mo,yr and PT all parts available with all remarks 1
-- and all remarks 2 available with all Remarks 1 having same part

SELECT 'LBS','SAZDA','TATA','1999', 'four wheel drive','Front','DHRUV50' UNION ALL
SELECT 'LBS','SAZDA','TATA','1999', 'four wheel drive','REAR','DHRUV50' UNION ALL
SELECT 'LBS','SAZDA','TATA','1999', 'Rear wheel drive','Front','DHRUV50' UNION ALL
SELECT 'LBS','SAZDA','TATA','1999', 'Rear wheel drive','REAR','DHRUV50' UNION ALL
SELECT 'LBS','SAZDA','TATA','1999','four wheel drive', 'Front','AGNIV' UNION ALL
SELECT 'LBS','SAZDA','TATA','1999','rear wheel drive', 'Front','AGNIV' UNION ALL
SELECT 'LBS','SAZDA','TATA','1999','four wheel drive', 'Rear','AGNIV' UNION ALL
SELECT 'LBS','SAZDA','TATA','1999','rear wheel drive', 'Rear','AGNIV' UNION ALL

--SET 3 – there is only one value in remarks 1 four wheel drive, so this group Excluded

SELECT 'check','ford','ferrari','2005', 'four wheel drive','Front','AXNK3678' UNION ALL
SELECT 'check','ford','ferrari','2005', 'four wheel drive','rear', 'BIP3985L' UNION ALL
SELECT 'check','ford','ferrari','2005', 'four wheel drive','Front','AXNK3678'

so output will be set 2 records

Thanks a lot.

viva

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-21 : 14:23:10

I Can do the first part because I understand what you are asking

SELECT * FROM @mytable C1
WHERE EXISTS
( SELECT 1 FROM @mytable C2
WHERE C1.MAKE = C2.MAKE
AND C1.MODEL = C2.MODEL
AND C1.LINE = C2.Line
AND C1.YEAR = C2.YEAR
GROUP BY C2.MAKE,C2.MODEL,C2.LINE,C2.YEAR
HAVING Count(DISTINCT Part) > 1
)

The rest of the stuff with R1 R2 etc. makes no sense to me as I do not see that in the data. What is RX1, etc. is that c commination occurrence of parts and remarks???

Vivan
Starting Member

9 Posts

Posted - 2015-04-21 : 14:32:21

Hello Michael
I am sorry if its confusing.

i tried to say

X1,X2,X3 (it stand for any part in the group)

similarly

r1,r2,and r3 (Any Remarks in remarks 1 feild)

and
RX1,RX2 (Any remarks in Remarks 2 feild)

viva

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-21 : 14:37:07

still not following - those value are not part of the data?? could you give an example

Vivan
Starting Member

9 Posts

Posted - 2015-04-22 : 10:27:10

group of MK,MO,YR,PT contain some number of Parts and Remarks
and it should only be in output when all part of the group available with
all remarks1 and Remarks 2 present in the group.

all other condition remain same.

if any pf the part not present with all available remarks 1 and remarks 2,
then entire group should be discard.

i edited the question.
Thanks

viva

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-22 : 11:48:10

Yeah - not making any sense to me.

How would SQL know the following:

-- As Rear is not available with four wheel drive , discard from output

you would need something to define the rules --

I think what you are saying is that you need to exclude records that are not viable based on part?
example
if there was a car that could not have power steering, such as the Pontiac Fiero, then having a part list for power steering would not be viable as that vehicle did not have power steering.

I know that, but from a coding perspective, the SQL would need to have some way to determine that.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-22 : 14:09:55

you need to come up with a better way of explaining what you need. what you are stating doesn't help determining what you want.

start here:

What about this data makes itself excluded from the output- use only this data to explain why this data should be excluded

SELECT 'MAZDA','Boulevard','1990', 'four wheel drive', 'FRONT','AK75', 'Audio' UNION ALL -- As Rear is not available with four wheel drive , discard from output
SELECT 'MAZDA','Boulevard','1990','front wheel drive', 'REAR','AK75', 'Audio'UNION ALL -- As Rear is not available with four wheel drive , discard from output
SELECT 'MAZDA','Boulevard','1990', 'front wheel drive','FRONT','AK75', 'Audio'UNION ALL -- As Rear is not available with four wheel drive , discard from output

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-22 : 14:10:04

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-23 : 11:32:07

you've changed the data - kind of hard to figure out the rule if you change the data and you can't explain what the rule is?

I am trying to figure out the rule based on the data you originally provided
start here:

What about this data makes itself excluded from the output- use only this data to explain why this data should be excluded

SELECT 'MAZDA','Boulevard','1990', 'four wheel drive', 'FRONT','AK75', 'Audio' UNION ALL -- As Rear is not available with four wheel drive , discard from output
SELECT 'MAZDA','Boulevard','1990','front wheel drive', 'REAR','AK75', 'Audio'UNION ALL -- As Rear is not available with four wheel drive , discard from output
SELECT 'MAZDA','Boulevard','1990', 'front wheel drive','FRONT','AK75', 'Audio'UNION ALL -- As Rear is not available with four wheel drive , discard from output

This is the data you originally provided
INSERT @mytable
-- MAKE, MODEL YEAR REMARKS 1 REMARKS 2 PART LINE
SELECT 'MAZDA','Boulevard','1990', 'four wheel drive', 'FRONT','AK75', 'Audio' UNION ALL -- As Rear is not available with four wheel drive , discard from output
SELECT 'MAZDA','Boulevard','1990','front wheel drive', 'REAR','AK75', 'Audio'UNION ALL -- As Rear is not available with four wheel drive , discard from output
SELECT 'MAZDA','Boulevard','1990', 'front wheel drive','FRONT','AK75', 'Audio'UNION ALL -- As Rear is not available with four wheel drive , discard from output
SELECT 'Aerio','Tesla', '2014', 'four wheel drive', 'rear', '19-1505', 'BAR'UNION ALL -- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2014', 'front wheel drive','rear', '19-1505', 'BAR'UNION ALL -- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2014', 'four wheel drive', 'rear', '65-1505', 'BAR'UNION ALL -- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2014', 'front wheel drive','rear', '65-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2014', 'four wheel drive', 'FRONT','19-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2014', 'front wheel drive','FRONT','19-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2014', 'four wheel drive', 'FRONT','65-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2014', 'front wheel drive','FRONT','65-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'four wheel drive', 'rear', '19-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'front wheel drive','rear', '19-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'four wheel drive', 'rear', '65-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'front wheel drive','rear', '65-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'four wheel drive', 'FRONT','19-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'front wheel drive','FRONT','19-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'four wheel drive', 'FRONT','65-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'Aerio','Tesla' ,'2015', 'front wheel drive','FRONT','65-1505', 'BAR'UNION ALL-- SHOULD BE IN OUTPUT
SELECT 'KingQuad','Horse','2006', 'front wheel drive','rear', '256-950', 'COLIN'UNION ALL-- 1 record for a group of make,model,year and part type Discard it from output
SELECT 'Arctic','Burgman','2005', 'four wheel drive', 'FRONT','ANKX18', 'JHUMKA'UNION ALL-- Discard , as both the part not contain both the remarks 1
SELECT 'Arctic','Burgman','2005', 'front wheel drive','FRONT','CTYK68', 'JHUMKA'-- Discard , as both the part not contain both the remarks 1

Vivan
Starting Member

9 Posts

Posted - 2015-04-24 : 15:08:48

Pardon Me Michael. Please allow me to explain from beginning with fresh set of data.

/*
The resultset is based on three criteria

1st: GROUP BY PART TYPE,MAKE,MODEL,YEAR
Based on above group whatever SKU we will get those all SKU should present with all available remarks
condition: if ONLY 1 Remarks 1 present in the group then it should exclude from the output.

2nd: Based on the group (PART TYPE,MAKE,MODEL,YEAR)
if All Remarks 3 should available with all Remarks 1 if not then exclude from the output.

3rd: even if any one SKU or remarks 3 of a group not fullfilling the crieteria then entire Group need to be exclude,
not only that line.
*/

DECLARE @MYTABLE TABLE
(
[PART TYPE] VARCHAR (50),
[MAKE] VARCHAR (50),
[MODEL] VARCHAR (50),
[YEAR] VARCHAR (50),
[SKU] VARCHAR (50),
[REMARKS] VARCHAR (50),
[REMARKS3] VARCHAR (50)
)

INSERT @MYTABLE

--[PART TYPE], [MAKE], [MODEL], [YEAR], [SKU], [REMARKS], [REMARKS3]
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Four Wheel Drive', 'Front' union all -- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Four Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Four Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Rear Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Rear Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Rear Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Four Wheel Drive', 'Rear' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Four Wheel Drive', 'Rear' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Four Wheel Drive', 'Rear' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF35G', 'Rear Wheel Drive', 'Rear' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'SKYF46D', 'Rear Wheel Drive', 'Rear' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'ADDALEAF', '54', '667', '2010', 'AIR75620', 'Rear Wheel Drive', 'Rear' UNION ALL-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS

SELECT 'CONV', '89', '668', '2014', 'TFC82112', 'Four Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'CONV', '89', '668', '2014', 'TFC82250', 'Four Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'CONV', '89', '668', '2014', 'TFC82350', 'Four Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'CONV', '89', '668', '2014', 'TFC82112', 'Rear Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'CONV', '89', '668', '2014', 'TFC82250', 'Rear Wheel Drive', 'Front' union all-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS
SELECT 'CONV', '89', '668', '2014', 'TFC82350', 'Rear Wheel Drive', 'Front' UNION ALL-- BE IN OUTPUT AS ALL PART IN A GROUP OF MAKE,MODEL,YEAR AND PART TYPE AVAILABLE WITH ALL REMARKS, AND ALL REMARKS 3 IS ALSO AVAILABLE WITH ALL REMARKS

SELECT 'AXLE', '894', '888','1984','MOOK8620', 'Four Wheel Drive', 'Front' union all --EXLUDE, ONLY 1 Remarks 1 present in the group

SELECT 'CLTPREPLAT', '72', '945', '2008', 'TCI498911', 'All Wheel Drive', 'FRONT' union all --Even All Part Present with All Remarks but All Remarks 3 not Present with all Remarks 1,- Full Group Exclude
SELECT 'CLTPREPLAT', '72', '945', '2008', 'TCI498911', 'FRONT Wheel Drive', 'REAR' union all --Even All Part Present with All Remarks but All Remarks 3 not Present with all Remarks 1,- Full Group Exclude
SELECT 'CLTPREPLAT', '72', '945', '2008', 'WLW140', 'All Wheel Drive', 'REAR' union all -- Even All Part Present with All Remarks but All Remarks 3 not Present with all Remarks 1,- Full Group Exclude
SELECT 'CLTPREPLAT', '72', '945', '2008', 'WLW140', 'FRONT Wheel Drive', 'FRONT' union all -- Even All Part Present with All Remarks but All Remarks 3 not Present with all Remarks 1,- Full Group Exclude

SELECT 'BALLJOINT', '96', '949', '2012', 'PRS599', 'Rear Wheel Drive', 'FRONT' union all --Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96', '949', '2012', 'PRS599', 'Front Wheel Drive','FRONT' union all -- Exclude from Resultset, as all part not with all remarks 1
SELECT 'BALLJOINT', '96', '949', '2012', 'OMEFK36', 'Rear Wheel Drive','FRONT' --Exclude from Resultset, as all part not with all remarks 1

SELECT * FROM @MYTABLE

viva

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources