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

COMPARE QUERY Expand / Collapse
Author
Message
Posted Thursday, October 21, 2010 6:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 7:20 AM
Points: 49, Visits: 81
is there a way to compare the next record in the query to the previous record
i would like to see if there is a different unit cost on a sku as long as it is the same season, style and color

SELECT
SCDIVN AS COMPANY,
SCSEAS AS SEASON,
SCSTYL AS STYLE,
SCCOLR AS COLOR,
SCSKU# AS SKU,
SCQTY AS QTY,
SCCOST AS INV_COST,
CASE WHEN scqty = 0 THEN 0
WHEN sccost = 0 THEN 0
ELSE (SCCOST / SCQTY) END AS UNIT_COST

FROM dbo.SHIPSKU#
WHERE (SCDIVN = 'aaa')
GROUP BY SCDIVN, SCSEAS, SCSTYL, SCCOLR, SCSKU#, SCQTY, SCCOST
ORDER BY COMPANY, SEASON, STYLE, COLOR
Post #1008383
Posted Thursday, October 21, 2010 6:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
How do you define the sequential order of rows in this table?
Is there a suitable datetime column or an incrementing integer column?
Post #1008424
Posted Thursday, October 21, 2010 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 7:20 AM
Points: 49, Visits: 81
Not sure if i understand 100% what you are asking but, i am grouping my query

Post #1008443
Posted Thursday, October 21, 2010 8:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
You have a GROUP BY clause but since you have no aggregate functions its only effect is to eliminate duplicate rows.

It would help if you could describe what you trying to do in some more detail, including providing the DDL scripts for the table, and INSERT statements to insert some test data into that table.
Post #1008522
Posted Thursday, October 21, 2010 12:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 7:20 AM
Points: 49, Visits: 81
You have a GROUP BY clause but since you have no aggregate functions its only effect is to eliminate duplicate rows.

i am dividing in the case statement.

It would help if you could describe what you trying to do in some more detail,
i am trying to compare records in the query.
if record 1 says 14.50 i want it to search the next row to see if it has the same value


including providing the DDL scripts for the table, and INSERT statements to insert some test data into that table.

there are no scripts to the table, no insert statements, this is querying a table for data not inserting into the table



Post #1008695
Posted Thursday, October 21, 2010 1:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 1,945, Visits: 2,864
This makes no sense. Rows are not records; there is no ordering unless you EXPLICITLY show it as values in a column.


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. Please learn to 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.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1008750
Posted Friday, October 22, 2010 7:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 7:20 AM
Points: 49, Visits: 81
never mind ill figure it out.
this was just a quick query that i posted to see if someone had any suggestions on a creating a compare query. didnt need code debugged or anything of that nature.

thanks anyway
Post #1009182
Posted Friday, October 22, 2010 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 9:51 AM
Points: 48, Visits: 366
Sharon,

You are asking two separate questions here:

" is there a way to compare the next record in the query to the previous record"

and

"i would like to see if there is a different unit cost on a sku as long as it is the same season, style and color "

Andrew and Celko have responded to your first question by trying to make it a teachable moment about set-based thinking. SQL does not guarantee any order of records unless you tell it an order. Your order by clause is less than the fields driving your groups, so within a group, "next record" cannot be predicted. In the provided query, "next record" is an ambiguous concept unless you can articulate what it means to be the next record.

Now, your initial idea of using "next record" as what you think you need to solve the business question can eventually get the answer with further elaboration that would allow the data in one record to determine what the "next record" would be, but really it doesn't need it. An answer with this approach is subject to misinterpretation and a more complicated than necessary query.

"i would like to see if there is a different unit cost on a sku as long as it is the same season, style and color "

Does this answer the business question:

SELECT
SCDIVN AS COMPANY,
SCSEAS AS SEASON,
SCSTYL AS STYLE,
SCCOLR AS COLOR,
SCSKU# AS SKU,
Min(sccost) as lowestcost,
Max(sccost) as highestcost
FROM dbo.SHIPSKU#
WHERE (SCDIVN = 'aaa')
GROUP BY SCDIVN, SCSEAS, SCSTYL, SCCOLR, SCSKU#
Having Min(sccost) <> Max(sccost)

Post #1009290
Posted Friday, October 22, 2010 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 7:20 AM
Points: 49, Visits: 81
possibly thanks ill have to see
Post #1009300
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse