Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

query to get the name of a product with only incremental values in the quantity column Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 11:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 36,712, Visits: 31,163
Nisean (12/12/2012)
i need a query to get the names of the product which has incremental or equal values every year 2009-2012(in its QTY column)
Product year QTY
Computer 2009 100
Computer 2010 200
Computer 2011 300
Computer 2012 400
printer 2009 100
printer 2010 200
printer 2011 250
printer 2012 250
flash drive 2009 400
flash drive 2010 500
flash drive 2011 700
flash drive 2012 900
monitor 2009 200
monitor 2010 300
monitor 2011 250
monitor 2012 400
keyboard 2009 100
keyboard 2010 150
keyboard 2011 200
keyboard 2012 150

-------------output----------
Computer
printer
flashdrive


What do you want to do if there's a year missing from any given product?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1398175
Posted Wednesday, December 19, 2012 8:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 18, Visits: 178
CELKO (12/18/2012)
I think you meant monotonic increasing and not increment . Let's use ISO-11179 names and the MySQL year format:

CREATE TABLE Inventory
(product_name VARCHAR(32) NOT NULL,
inventory_year CHAR(10) NOT NULL,
PRIMARY KEY(product_name, inventory_year),
onhand_qty INTEGER NOT NULL
CHECK(onhand_qty >= 0));

INSERT INTO Inventory
VALUES
('Computer', '2009-00-00' 100),
('Computer', '2010-00-00' 200),
('Computer', '2011-00-00' 300),
('Computer', '2012-00-00' 400),
('printer', '2009-00-00' 100),
('printer', '2010-00-00' 200),
('printer', '2011-00-00' 250),
('printer', '2012-00-00' 250),
('flash Drive', '2009-00-00' 400),
('flash Drive', '2010-00-00' 500),
('flash Drive', '2011-00-00' 700),
('flash Drive', '2012-00-00' 900),
('monitor', '2009-00-00' 200),
('monitor', '2010-00-00' 300),
('monitor', '2011-00-00' 250),
('monitor', '2012-00-00' 400),
('keyboard', '2009-00-00' 100),
('keyboard', '2010-00-00' 150),
('keyboard', '2011-00-00' 200),
('keyboard', '2012-00-00' 150));

SELECT X.product_name
FROM (SELECT product_name,
(ROW_NUMBER() OVER(PARTITION BY product_name
ORDER BY inventory_year) -
ROW_NUMBER() OVER(PARTITION BY product_name
ORDER BY onhand_qty))
AS compare_seq_delta
FROM Inventory) AS X
GROUP BY X.product_name
HAVING MIN(compare_seq_delta) = 0
AND MAX(compare_seq_delta) = 0;

Untested.


thank you CELKO!!! it worked ..learned something new abt ROW_NUMBER function. and thanks for all the info abt the forum
Post #1398433
Posted Wednesday, December 19, 2012 8:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 18, Visits: 178
Jeff Moden (12/18/2012)
Nisean (12/18/2012)
thank you for the response. FYI this is not a homework or an interview question. i know how to create a table and insert data on it! i need a query which will give me the required result.and yes i even couldnt figure out how to start on this!!
anybody!!???


The purpose is that a lot of people like to test their solutions before they post them. If you post readily consumable data, it makes it easier for them which means you get better answers quicker. Please see the first link after my signature line below for a more detailed explanation.


thank you for your response JEFF. the query provided by CELKO worked. and i will definately go through the links.
Post #1398440
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse