query to get the name of a product with only incremental values in the quantity column

Question

query to get the name of a product with only incremental values in the quantity column

sql84

SSC Eights!

Points: 830
More actions
December 12, 2012 at 10:55 am

#286121

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
Computer2009100
Computer2010200
Computer2011300
Computer2012400
printer 2009100
printer 2010 200
printer 2011250
printer 2012250
flash drive2009400
flash drive2010500
flash drive2011700
flash drive2012900
monitor 2009200
monitor 2010300
monitor 2011250
monitor 2012400
keyboard 2009100
keyboard 2010150
keyboard 2011200
keyboard 2012150
-------------output----------
Computer
printer
flashdrive

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

This looks like homework or an interview question so ill post some usable data. Can you show what you have tried so far?

CREATE TABLE tmpProduct (

Product VARCHAR(32),

[Year] INT,

QTY INT,

CONSTRAINT PK_tmpProduct PRIMARY KEY (Product, [Year])

)

INSERT INTO tmpProduct (Product, [Year], QTY)

SELECT * FROM (VALUES

('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))X(Product, [Year], QTY)

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

sql84 SSC Eights! Points: 830 More actions · Answer 2

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!!???

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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!!???

Interesting....then how did this other post from today just happen to have not only the same requirements but the exact same data?

http://www.sqlservercentral.com/Forums/Topic1398007-392-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sql84 SSC Eights! Points: 830 More actions · Answer 4

yea i jus saw tht...he is my friend ...we both are new to this forum and we both trying to figure out the solution...we tryin to delete one post but jus couldnt figure out how...man i dont get it...why everybody are acting like teachers??!!! or a spy..if u dont want to help thn jus move on.

sql84 SSC Eights! Points: 830 More actions · Answer 5

CELKO (12/18/2012)
.why everybody are acting like teachers?
Some of us are teachers. And it is our duty to find students who cheat on their homework and get them expelled from their schools for it. Over the years I have gotten two students (New Zealand, guys with "do my homework" requests) and one instructor (reproducing copyrighted materials -- my books -- without permission from my publisher).
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.

thank you for the explanation CELKO. it does make sense... i dont want this forum to be cheaters way out either...i am not a DBA either a student, was jus trying to learn SQL by myself. well as i said i am new and havent gone through all those formats/rules (which i should). but at least if somebody can tell me wht functions can get me to my result i could start trying them out.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

CELKO (12/18/2012)
Let's use {snip} and the MySQL year format:

Since this is an SQL Server forum, let's not. Besides, that break a cardinal rule... though shalt not store dates as character based data.

Untested

Why not? You've got the data and the code. Press the {f5} key and run it!

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

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.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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
Computer2009100
Computer2010200
Computer2011300
Computer2012400
printer 2009100
printer 2010 200
printer 2011250
printer 2012250
flash drive2009400
flash drive2010500
flash drive2011700
flash drive2012900
monitor 2009200
monitor 2010300
monitor 2011250
monitor 2012400
keyboard 2009100
keyboard 2010150
keyboard 2011200
keyboard 2012150
-------------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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql84 SSC Eights! Points: 830 More actions · Answer 9

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:-)

sql84 SSC Eights! Points: 830 More actions · Answer 10

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.