SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


COMPARE QUERY


COMPARE QUERY

Author
Message
sharonmtowler
sharonmtowler
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 136
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
andrewd.smith
andrewd.smith
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2618 Visits: 3232
How do you define the sequential order of rows in this table?
Is there a suitable datetime column or an incrementing integer column?
sharonmtowler
sharonmtowler
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 136
Not sure if i understand 100% what you are asking but, i am grouping my query
andrewd.smith
andrewd.smith
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2618 Visits: 3232
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.
sharonmtowler
sharonmtowler
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 136
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
sharonmtowler
sharonmtowler
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 136
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
keith.gerritsen
keith.gerritsen
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 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)
sharonmtowler
sharonmtowler
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 136
possibly thanks ill have to see
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search