Why do people need to do this?

Question

Why do people need to do this?

Viewing 5 posts - 91 through 94 (of 94 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Bruce, you've described to a tee what I had in mind when I said "failure to think" .... nailed it, scored a 10, etc., etc., ... Sounds like those jokers couldn't think their way out of a wet paper bag that already has giant holes in it large enough to drive an 18-wheeler through... and, no less, because they'd have to "get wet"...

Thanks for the good laugh on that, it's appreciated (not at you, but with you...) :D:D:D

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

Bruce W Cassidy (2/15/2009)
[font="Verdana"]Okay, not exactly sure this is related. I await correction with trepidation. However...
In a company that shall not be named for which I had the misfortune to work for a little over a year ago as their "data architect" (before I realise that this was a political position and they actually wanted someone to pat them on the back and say "yes, everything is fine, even though your data is crap and your performance sucks"), I made the following suggestion as an architecture. But first, some backfill.
The company receives product orders from all sorts of places. Orders contain items with quantities against them. Some details are held against the order (due date, who made the order, the shipment address, etc) and some against the items (quantity, discounts, price, etc). But the list of items have no inherent sequence, other than that they appear on the same order.
The existing software would create an order entry. It would then post the order lines row by agonising row (where have I heard that before?) Business logic fired by trigger on the order, and on every order lines, recalculating totals for the order each time. At the end of posting, depending on some criteria (such as stock availability), the entire order could then be rejected and rolled back.
I proposed that we pass the entire order to the database as a whole within an XML string. The procedure could then insert one order, all of the order lines (as a set), and then do the necessary business logic. All of that could be done in one transaction so a rollback could still be generated if need be, but I worked out that the order could be rejected before posting.
Now, within the XML string, the order lines still had no sequence. However, when I posted them to the database, I needed them to have an order line number starting from one. Easy.
Except, someone suggested that for our bulk orders, we could pass the entire set of orders as one XML string. So now I had to post multiple orders, containing order lines, that needed to be given a sequence starting from one for each order.
Then the entire thing got thrown out of the window by the "enterprise architect" because (and I quote, no joke) "it is a change."
So in retrospect, you're right... there is no business case for this. 😛
[/font]

Man, that's real close and, of course, that has a pretty easy solution. The proplem that I've been solving for folks requires a sequence on things that repeat... not unique items like an invoice number or anything.

Kinda like this...

[font="Courier New"]SampleDateItemSequence

2009-02-14 21:27:33.433A1

2009-02-14 21:27:35.970A2

2009-02-14 21:27:45.247B1

2009-02-14 21:27:50.077A1

2009-02-14 21:28:12.177A2

2009-02-14 21:28:13.937B1

2009-02-14 21:28:26.360B2

2009-02-14 21:28:26.753B3

2009-02-14 21:28:27.673B4

2009-02-14 21:28:39.193B5

2009-02-14 21:28:42.510A1

2009-02-14 21:28:51.493B1

2009-02-14 21:28:55.037B2

2009-02-14 21:29:04.493A1

2009-02-14 21:29:16.020B1

2009-02-14 21:29:20.267B2

2009-02-14 21:29:36.410B3

2009-02-14 21:29:53.947B4

2009-02-14 21:30:00.377A1

2009-02-14 21:30:04.150A2

2009-02-14 21:30:05.013A3

2009-02-14 21:30:10.857B1

2009-02-14 21:30:16.770B2

2009-02-14 21:30:17.687A1

2009-02-14 21:30:18.110B1

2009-02-14 21:30:18.347B2

2009-02-14 21:30:22.533B3

2009-02-14 21:30:38.897A1

2009-02-14 21:30:44.870A2

2009-02-14 21:31:13.933B1[/font]

Why in the heck would anyone need to do such a thing?

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

... and, no.... again, I don't need to know how to solve the problem... I've done it at least a half dozen times with the "running total update" and once using a chunk of someone else's brilliance (see below)... I want to know why someone would need to do this...

--===== Create a test table

CREATE TABLE #Sample

(

SampleDate DATETIME,

Item CHAR(1)

)

--===== Populate the test table with sample data

INSERT INTO #Sample

(SampleDate,Item)

SELECT '2009-02-14 21:27:33.433','A' UNION ALL

SELECT '2009-02-14 21:27:35.970','A' UNION ALL

SELECT '2009-02-14 21:27:45.247','B' UNION ALL

SELECT '2009-02-14 21:27:50.077','A' UNION ALL

SELECT '2009-02-14 21:28:12.177','A' UNION ALL

SELECT '2009-02-14 21:28:13.937','B' UNION ALL

SELECT '2009-02-14 21:28:26.360','B' UNION ALL

SELECT '2009-02-14 21:28:26.753','B' UNION ALL

SELECT '2009-02-14 21:28:27.673','B' UNION ALL

SELECT '2009-02-14 21:28:39.193','B' UNION ALL

SELECT '2009-02-14 21:28:42.510','A' UNION ALL

SELECT '2009-02-14 21:28:51.493','B' UNION ALL

SELECT '2009-02-14 21:28:55.037','B' UNION ALL

SELECT '2009-02-14 21:29:04.493','A' UNION ALL

SELECT '2009-02-14 21:29:16.020','B' UNION ALL

SELECT '2009-02-14 21:29:20.267','B' UNION ALL

SELECT '2009-02-14 21:29:36.410','B' UNION ALL

SELECT '2009-02-14 21:29:53.947','B' UNION ALL

SELECT '2009-02-14 21:30:00.377','A' UNION ALL

SELECT '2009-02-14 21:30:04.150','A' UNION ALL

SELECT '2009-02-14 21:30:05.013','A' UNION ALL

SELECT '2009-02-14 21:30:10.857','B' UNION ALL

SELECT '2009-02-14 21:30:16.770','B' UNION ALL

SELECT '2009-02-14 21:30:17.687','A' UNION ALL

SELECT '2009-02-14 21:30:18.110','B' UNION ALL

SELECT '2009-02-14 21:30:18.347','B' UNION ALL

SELECT '2009-02-14 21:30:22.533','B' UNION ALL

SELECT '2009-02-14 21:30:38.897','A' UNION ALL

SELECT '2009-02-14 21:30:44.870','A' UNION ALL

SELECT '2009-02-14 21:31:13.933','B'

--===== Calculate the Groups in date order.;WITH

cteRowNumItem AS

(

SELECT SampleDate,

Item,

RowNum = ROW_NUMBER() OVER(PARTITION BY Item ORDER BY SampleDate)

FROM #Sample

)

,

cteGroup AS

(

SELECT SampleDate,

Item,

GroupNum = ROW_NUMBER() OVER(ORDER BY SampleDate)-RowNum,

ROW_NUMBER() OVER(ORDER BY SampleDate) AS RowNumSampleDate,

RowNum AS RowNumItem

FROM cteRowNumItem

)

SELECT SampleDate, Item,

ROW_NUMBER() OVER (PARTITION BY Item,GroupNum ORDER BY RowNumSampleDate) AS Sequence

FROM cteGroup

ORDER BY SampleDate

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

smunson (2/15/2009)
Bruce, you've described to a tee what I had in mind when I said "failure to think" .... nailed it, scored a 10, etc., etc., ... Sounds like those jokers couldn't think their way out of a wet paper bag that already has giant holes in it large enough to drive an 18-wheeler through... and, no less, because they'd have to "get wet"...
Thanks for the good laugh on that, it's appreciated (not at you, but with you...) :D:D:D
Steve
(aka smunson)
:):):)

[font="Verdana"]Oh, feel free to laugh at me. Classic mistake on my behalf. I was so in awe of the fact that this company recognised the importance of architecture and that they had an enterprise architect and a software architect and were looking at hiring a database architect, that I forgot to ask the basic question:

"Why is a company that designs and manufactures clothing... writing software?"

[/font]

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

Heh... simple... the software was the very fabric of their business {visibly and audibly gagging at the terrible pun} 😛

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