Flag Values Based on Columns

Question

Flag Values Based on Columns

Viewing 9 posts - 31 through 38 (of 38 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Jeff Moden (1/26/2016)
I'd take some heart in what was said as mean as it sounded. We have quick posts that don't survive the future and people saying they solved what was given at the time instead of asking the right questions before posting. Are you sure you'd want a future employer to find this post that started off by ignoring the fact that there was nothing in the original post to reliably sort on? Think about it a little more. 😉

Don't take me wrong on this Jeff, I recognise that I should have done this better in the first place and I really do appreciate when I'm corrected! But it is kind of disappointing when the structure of the solution is there and no one pitches in the final bit.

😎

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Eirikur Eiriksson (1/27/2016)
But it is kind of disappointing when the structure of the solution is there and no one pitches in the final bit.
😎

Only thing which was there was a fake.

The core of the solution was faked using a pre-sorted array of test data.

If you know you've come short of a solution due to time constraints or some other reason - you must point it out and indicate where is a bit missing .

If you respect yourself as a professional, of course.

But you know, Eirikur, it's not you who's done the worst part in this thread.

_____________
Code for TallyGenerator

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Sergiy (2/1/2016)
Eirikur Eiriksson (1/27/2016)
But it is kind of disappointing when the structure of the solution is there and no one pitches in the final bit.
😎
Only thing which was there was a fake.
The core of the solution was faked using a pre-sorted array of test data.
If you know you've come short of a solution due to time constraints or some other reason - you must point it out and indicate where is a bit missing .
If you respect yourself as a professional, of course.
But you know, Eirikur, it's not you who's done the worst part in this thread.

Point taken;-)

😎

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Eirikur Eiriksson (1/27/2016)
Jeff Moden (1/26/2016)
I'd take some heart in what was said as mean as it sounded. We have quick posts that don't survive the future and people saying they solved what was given at the time instead of asking the right questions before posting. Are you sure you'd want a future employer to find this post that started off by ignoring the fact that there was nothing in the original post to reliably sort on? Think about it a little more. 😉
Don't take me wrong on this Jeff, I recognise that I should have done this better in the first place and I really do appreciate when I'm corrected! But it is kind of disappointing when the structure of the solution is there and no one pitches in the final bit.
😎

Gosh, I hope you're not serious. The original problem had no solution and Sergiy proved that. There was no "structure of the solution" because there could not be a solution with the original given data. I clearly laid out (and others asked for clarification, as well) to the OP what was needed to be guaranteed in the data for it to work and got no response from the OP. By the time the OP responded, others had jumped in. How is any of that disappointing?

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

Jeff Moden (2/1/2016)
Eirikur Eiriksson (1/27/2016)
Jeff Moden (1/26/2016)
I'd take some heart in what was said as mean as it sounded. We have quick posts that don't survive the future and people saying they solved what was given at the time instead of asking the right questions before posting. Are you sure you'd want a future employer to find this post that started off by ignoring the fact that there was nothing in the original post to reliably sort on? Think about it a little more. 😉
Don't take me wrong on this Jeff, I recognise that I should have done this better in the first place and I really do appreciate when I'm corrected! But it is kind of disappointing when the structure of the solution is there and no one pitches in the final bit.
😎
Gosh, I hope you're not serious. The original problem had no solution and Sergiy proved that. There was no "structure of the solution" because there could not be a solution with the original given data. I clearly laid out (and others asked for clarification, as well) to the OP what was needed to be guaranteed in the data for it to work and got no response from the OP. By the time the OP responded, others had jumped in. How is any of that disappointing?

This data could be pre-sorted elsewhere (like in Excel) imported, and the user is only interested in the columns presented to us (not the sort column). In the case where order is guaranteed the post Eirikur provided helps. Clearly, guaranteeing sort results is of utmost relevancy. As I had mentioned , this is an unknown. Others did point out this important piece. So we did go about it the right way jumping in , stating this will only work "if" .... . I think that is what Eirikur means to say.

----------------------------------------------------

Sergiy SSC Guru Points: 110208 More actions · Answer 6

MMartin1 (2/2/2016)
This data could be pre-sorted elsewhere (like in Excel) imported, and the user is only interested in the columns presented to us (not the sort column). In the case where order is guaranteed

Editor: Comment removed

Don't you really know that pre-soring anything in Excel or any other external application does not guarantee that the set will be imported into a SQL Server table in the same order?

Don't you really know that even if the data physically placed in a table in a desired order (which is probably even enforced with a clustered primary key) there is no guarantee it will be selected by a query in the same order, unless you enforce the order of the records in the query itself?

Editor: Comment removed

_____________
Code for TallyGenerator

nm.rajesh SSC-Addicted Points: 421 More actions · Answer 7

Hi Erikiur,

Your Solution was indeed a great and quick way to achieve , however there is an issue with the code . Please find the Details Below :

Sample Table And Code Below (Please look at Row Number 305 Even though we do not have 8 Consecutive Values the flag Value is 1

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.SampleTable') IS NOT NULL DROP TABLE dbo.SampleTable;

CREATE TABLE [dbo].[SampleTable](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Measure1] [float] NULL,

[Measure2] [float] NULL,

)

--Sample Data:

INSERT INTO [dbo].[SampleTable] (Measure1, Measure2)

VALUES

(0,0)

,(0,0)

,(0,1)

,(0,0)

,(-1,0)

,(0,0)

,(1,0)

,(1,1)

,(0,0)

,(0,1)

,(0,0)

,(0,-1)

,(0,0)

,(1,0)

,(0,0)

,(1,1)

,(0,1)

,(0,0)

,(-1,0)

,(0,0)

,(1,0)

,(0,0)

,(0,-1)

,(0,0)

,(0,1)

,(0,0)

,(0,-1)

,(0,0)

,(-1,0)

,(-1,-1)

,(-1,-2)

,(0,-1)

,(0,0)

,(0,1)

,(1,1)

,(1,2)

,(2,2)

,(3,3)

,(2,3)

,(2,2)

,(1,2)

,(0,1)

,(0,0)

,(0,-1)

,(-1,-1)

,(-1,-2)

,(-2,-3)

,(-2,-4)

,(-3,-4)

,(-2,-5)

,(-3,-5)

,(-3,-4)

,(-3,-3)

,(-3,-2)

,(-3,-1)

,(-2,0)

,(-1,0)

,(0,1)

,(0,2)

,(1,3)

,(2,3)

,(3,4)

,(4,4)

,(4,5)

,(5,6)

,(5,5)

,(5,4)

,(4,3)

,(2,2)

,(1,1)

,(0,0)

,(-1,-1)

,(-2,-1)

,(-2,-2)

,(-3,-2)

,(-2,-2)

,(-2,-3)

,(-3,-3)

,(-3,-2)

,(-2,-2)

,(-1,-2)

,(0,-1)

,(0,0)

,(1,1)

,(2,2)

,(3,2)

,(2,2)

,(1,2)

,(1,1)

,(1,1);

;WITH BASE_DATA AS

(

SELECT

ST.ID

,ST.Measure1

,ST.Measure2

,ROW_NUMBER() OVER

(

ORDER BY ST.ID

) - COUNT(*) OVER

(

PARTITION BY ST.Measure1

ORDER BY ST.ID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS M1_RCNT

,COUNT(*) OVER

(

PARTITION BY ST.Measure1

ORDER BY ST.ID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS M1_CNT

,ROW_NUMBER() OVER

(

ORDER BY ST.ID

) - COUNT(*) OVER

(

PARTITION BY ST.Measure2

ORDER BY ST.ID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS M2_RCNT

,COUNT(*) OVER

(

PARTITION BY ST.Measure2

ORDER BY ST.ID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS M2_CNT

FROM dbo.SampleTable ST

)

SELECT

BD.ID

,BD.Measure1

,BD.Measure2

,BD.M1_CNT

,BD.M2_CNT

,SIGN(CASE WHEN COUNT(*) OVER

(

PARTITION BY BD.M1_RCNT

) > 7 THEN 1 ELSE 0 END

+ CASE WHEN COUNT(*) OVER

(

PARTITION BY BD.M2_RCNT

) > 7 THEN 1 ELSE 0 END) AS FLAG

FROM BASE_DATA BD

ORDER BY BD.ID;

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

Sergiy (2/2/2016)
Editor: Comment removed
Don't you really know that pre-soring anything in Excel or any other external application does not guarantee that the set will be imported into a SQL Server table in the same order?
Don't you really know that even if the data physically placed in a table in a desired order (which is probably even enforced with a clustered primary key) there is no guarantee it will be selected by a query in the same order, unless you enforce the order of the records in the query itself?
Editor: Comment removed

Please do not post comments like this. Be professional and respectful in disagreements.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

I'm glad you have no comments on this statement:

MMartin1 (1/21/2016)
We are not recommending a practice of not maintaining some type of order in a table, just providing the solution given its present state (IE.. assuming no changes).

You must find it totally appropriate.

Imagine an apprentice on a building site who asks an experienced builder how to join these 2 studs he's holding.

And gets an answer - stick it together with PVA glue.

Why did he do it? Can't tell.

Probably did not have time for asking what is it for, or his brain was too occupied by another problem, or just been in a bad mood. Happens sometime.

Would his advice work? Sure.

While those studs are in hands of the apprentice.

It's exactly what MMartin1 said:

"just providing the solution given its present state (IE.. assuming no changes)"

Yes, PVA is strong enough to hold 2 wooden studs together.

Until they are placed into a building construction and a real load is applied.

Now, if you are a manager on that building site - what would you say to such an advisor?

And what would you say to another builder who pointed out the danger of advice given?

And what would you say about the third builder, seemingly professional one, who starts defending the PVA solution and making up reasons why that advice was totally appropriate?

"We do not know where those studs go, so using PVA is totally robust advice (assuming no changes in studs' position)".

Would you name him "professional"?

And what would you say about another guy who told him to "shut up and stop feeding the apprentice with BS" (in this kind of words)?

If this site would have any kind of code of professional conduct (not to confuse with political correctness) then insisting on a mock-up solution, especially after its methodical faults been exposed and proven, would mean an immediate ban.

If we respect our profession.

_____________
Code for TallyGenerator