January 17, 2016 at 3:09 pm
Hi All,
I Have a Table with the values as shown below :
IDcode recoIDPostmtsa_m DesiredFlag
19990104220791236917.2 1
20000104220791236917.2 1
34330104220791237017.2 1
45430104220791237017.2 1
16560104220791237322.6 0
26610104220791237322.6 0
36610104220791237422.6 0
1661010422079127050.4 0
2661010422079127060.4 0
199901042207912708-0.9 0
I want to create a flag as 1 if the ID column have values in sequential order from 1 - 4 , if it having values are from 1- values less than 4 (3,2,1) then flag as zero , How can we achieve this in T-SQL.
Resultant set should have an additional column where it populates 1 for first 4 Rows and zero for all other records based on the above logic.
January 17, 2016 at 3:25 pm
Quick suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(ID,code,DesiredFlag) AS
(SELECT * FROM (VALUES
(1,'999010',1)
,(2,'000010',1)
,(3,'433010',1)
,(4,'543010',1)
,(1,'656010',0)
,(2,'661010',0)
,(3,'661010',0)
,(1,'661010',0)
,(2,'661010',0)
,(1,'999010',0)
) AS X(ID,code,DesiredFlag)
)
,BASE_DATA AS
(
SELECT
SD.ID
,SD.code
,SD.DesiredFlag
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) - SD.ID AS SD_GRP
FROM SAMPLE_DATA SD
)
SELECT
BD.ID
,BD.code
,BD.DesiredFlag
,CASE
WHEN COUNT(*) OVER
(
PARTITION BY BD.SD_GRP
) = 4 THEN 1
ELSE 0
END AS CALC_FLAG
FROM BASE_DATA BD;
Results
ID code DesiredFlag CALC_FLAG
----------- ------ ----------- -----------
1 999010 1 1
2 000010 1 1
3 433010 1 1
4 543010 1 1
1 656010 0 0
2 661010 0 0
3 661010 0 0
1 661010 0 0
2 661010 0 0
1 999010 0 0
January 17, 2016 at 4:15 pm
Thanks a lot 🙂
January 17, 2016 at 4:29 pm
nm.rajesh (1/17/2016)
Thanks a lot 🙂
You are very welcome.
😎
January 20, 2016 at 2:20 pm
Great solution Eirikur , the
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) - SD.ID AS SD_GRP
does the trick.
So going back to the OP, will you know what to do if you desire rows that have instead five in the group? Remember you will be maintaining this 😉
----------------------------------------------------
January 20, 2016 at 8:29 pm
MMartin1 (1/20/2016)
Great solution Eirikur , the
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) - SD.ID AS SD_GRP
does the trick.
It does exactly that - a trick.
But does not deliver an actual solution, unfortunately.
This "solution" works only while you have the data in an ordered array controlled by VALUES expression.
As soon as you try to apply it on a real table occupying more than 8kB of space, probably indexed, having inserts, updates and deletes happening all the time - it will fail.
Here is the proof:
USE tempdb;
DROP TABLE #SampleData
GO
CREATE TABLE #SampleData (
ID INT,
Code VARCHAR(50),
DesiredFlag bit
)
GO
INSERT INTO #SampleData
VALUES
(1,'999010',1)
,(2,'000010',1)
,(3,'433010',1)
,(4,'543010',1)
,(1,'656010',0)
,(2,'661010',0)
,(3,'661010',0)
,(1,'661010',0)
,(2,'661010',0)
,(1,'999010',0)
GO 30
EXEC sp_spaceused '#SampleData'
GO
-- Now we delete and re-populate some randomly selected records
DELETE FROM #SampleData
WHERE code = '000010'
GO
INSERT INTO #SampleData
VALUES
(2,'000010',1)
GO 30
-- Exactly the same values went in replacing the ones which have been deleted
SET NOCOUNT ON;
;WITH SAMPLE_DATA(ID,code,DesiredFlag) AS
(SELECT * FROM #SampleData
)
,BASE_DATA AS (
SELECT
SD.ID
,SD.code
,SD.DesiredFlag
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) - SD.ID AS SD_GRP
FROM SAMPLE_DATA SD
)
SELECT
BD.ID
,BD.code
,BD.DesiredFlag
,CASE
WHEN COUNT(*) OVER
(
PARTITION BY BD.SD_GRP
) = 4 THEN 1
ELSE 0
END AS CALC_FLAG
FROM BASE_DATA BD;
Still works.
sp_spaceused shows [data] = 8 KB - we still are within 1 page.
Now, let's try to increase the volume a bit:
USE tempdb;
DROP TABLE #SampleData
GO
CREATE TABLE #SampleData (
ID INT,
Code VARCHAR(50),
DesiredFlag bit
)
GO
INSERT INTO #SampleData
VALUES
(1,'999010',1)
,(2,'000010',1)
,(3,'433010',1)
,(4,'543010',1)
,(1,'656010',0)
,(2,'661010',0)
,(3,'661010',0)
,(1,'661010',0)
,(2,'661010',0)
,(1,'999010',0)
GO 40
EXEC sp_spaceused '#SampleData'
GO
DELETE FROM #SampleData
WHERE code = '000010'
GO
INSERT INTO #SampleData
VALUES
(2,'000010',1)
GO 40
SET NOCOUNT ON;
;WITH SAMPLE_DATA(ID,code,DesiredFlag) AS
(SELECT * FROM #SampleData
)
,BASE_DATA AS (
SELECT
SD.ID
,SD.code
,SD.DesiredFlag
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) - SD.ID AS SD_GRP
FROM SAMPLE_DATA SD
)
SELECT
BD.ID
,BD.code
,BD.DesiredFlag
,CASE
WHEN COUNT(*) OVER
(
PARTITION BY BD.SD_GRP
) = 4 THEN 1
ELSE 0
END AS CALC_FLAG
FROM BASE_DATA BD;
Now we've got [data] = 16 KB.
Table takes 2 pages for the data.
As a result - CALC_FLAG matches DesiredFlag on some random and inconsistent occasions.
ORDER BY (SELECT NULL) does not work anymore.
_____________
Code for TallyGenerator
January 21, 2016 at 11:10 am
That would make sense since this table is a heap. The ID column is not really an identifyer in this situation. 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).
----------------------------------------------------
January 21, 2016 at 1:13 pm
MMartin1 (1/21/2016)
That would make sense since this table is a heap. The ID column is not really an identifyer in this situation. 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).
Yeah, I see.
I would not recommend to hire you, guys, as consultants.
Because you're ok with providing solutions which works on data in its present state only and will quite possibly fail the very next week after you cashed the check for your valuable service.
😎
_____________
Code for TallyGenerator
January 21, 2016 at 1:26 pm
Sergiy (1/21/2016)
MMartin1 (1/21/2016)
That would make sense since this table is a heap. The ID column is not really an identifyer in this situation. 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).Yeah, I see.
I would not recommend to hire you, guys, as consultants.
Because you're ok with providing solutions which works on data in its present state only and will quite possibly fail the very next week after you cashed the check for your valuable service.
😎
No Problem, heh, Here what is provided is what is asked for. Simple. Your input also matters, never said it didnt. You assume a bit much though. The OP might have a situation where he needs to deliver a result now, and take care of database Normalisation after. We dont know.
----------------------------------------------------
January 21, 2016 at 1:56 pm
Sergiy (1/21/2016)
MMartin1 (1/21/2016)
That would make sense since this table is a heap. The ID column is not really an identifyer in this situation. 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).Yeah, I see.
I would not recommend to hire you, guys, as consultants.
Because you're ok with providing solutions which works on data in its present state only and will quite possibly fail the very next week after you cashed the check for your valuable service.
😎
He he he,
someone is mixing up a quick suggestion towards a solution and a consulting job, tells me that someone should not be in the business of the latter.
😎
January 21, 2016 at 3:42 pm
MMartin1 (1/21/2016)
You assume a bit much though. The OP might have a situation where he needs to deliver a result now, and take care of database Normalisation after. We dont know.
There is something we know for sure.
The solution which one of you guys provided and another one supported cannot possibly work on a real-life database populated with any reasonable amount of data.
That's the fact.
Continuing from here - I can see only 2 options when OP could be satisfied with the solution:
1. It's applied to a basic data set in DEV database OP uses to prove the concept.
In this case you provided OP with a perfect "time bomb" in the code - his development will pass the unit testing in DEV, but will unexpectedly fail once moved to a PROD system.
2. It was a question on an exam or any other kind of test.
In this case you helped OP to cheat without actually learning any professional skills.
Which option looks better on you?
You choose.
_____________
Code for TallyGenerator
January 21, 2016 at 3:49 pm
Sergiy (1/21/2016)
MMartin1 (1/21/2016)
You assume a bit much though. The OP might have a situation where he needs to deliver a result now, and take care of database Normalisation after. We dont know.There is something we know for sure.
The solution which one of you guys provided and another one supported cannot possibly work on a real-life database populated with any reasonable amount of data.
That's the fact.
Continuing from here - I can see only 2 options when OP could be satisfied with the solution:
1. It's applied to a basic data set in DEV database OP uses to prove the concept.
In this case you provided OP with a perfect "time bomb" in the code - his development will pass the unit testing in DEV, but will unexpectedly fail once moved to a PROD system.
2. It was a question on an exam or any other kind of test.
In this case you helped OP to cheat without actually learning any professional skills.
Which option looks better on you?
You choose.
Hey, here's another idea.
Instead of bashing people who at least attempted to help the OP, why don't you chime in with your solution to the problem? For me, that option looks better than any of the alternatives you have given.
January 21, 2016 at 3:54 pm
nm.rajesh (1/17/2016)
Hi All,I Have a Table with the values as shown below :
IDcode recoIDPostmtsa_m DesiredFlag
19990104220791236917.2 1
20000104220791236917.2 1
34330104220791237017.2 1
45430104220791237017.2 1
16560104220791237322.6 0
26610104220791237322.6 0
36610104220791237422.6 0
1661010422079127050.4 0
2661010422079127060.4 0
199901042207912708-0.9 0
I want to create a flag as 1 if the ID column have values in sequential order from 1 - 4 , if it having values are from 1- values less than 4 (3,2,1) then flag as zero , How can we achieve this in T-SQL.
Resultant set should have an additional column where it populates 1 for first 4 Rows and zero for all other records based on the above logic.
Rajesh, I am not sure if any of the solutions posted so far really give you what you need, but I am also quite unsure on what you need exactly.
You mention a sequential order, but I do not see anything that defines the order of the rows. Remember that a table in a relational database is by definition unsorted, and that the order you see rows returned is not guaranteed to be always the same, unless you explicitly define the order with an ORDER BY.
I am also not sure exactly on the requirement. Do you just want the first four rows in the returned data to have a flag set? Or do you need it set to any set of consecutive rows that have the values 1-4? What if there is a set of 0-4, or 1-5?
January 21, 2016 at 5:09 pm
Hugo Kornelis (1/21/2016)
Hey, here's another idea.Instead of bashing people who at least attempted to help the OP, why don't you chime in with your solution to the problem? For me, that option looks better than any of the alternatives you have given.
By the time I opened this thread it already had a solution posted, and it was already marked with "This worked for the OP".
Therefore I'm pretty sure OP would not come back here, so there is no point in posting any alternative solution.
Especially considering - there is no correct solution to the problem as it stands.
There must be questions asked, adjustments made, etc.
I see you did just that in your next post.
Will see if OP will show any interest in it.
I hope to be wrong.
For the fairness sake - I did exactly what you suggested in another thread ("Date Function") with similar quick solution which "worked for the OP" at the moment but failed couple of days later.
He was immediately provided with another "solution", as faulty as the first one.
It worked for the one particular day OP was asking about, but will fail again in a week or two, because it follows the faulty requirements.
After an attempt to clarify the requirements the OP apparently lost interest to the topic.
Probably until "the day" will come. 😉
_____________
Code for TallyGenerator
January 21, 2016 at 6:19 pm
Hugo Kornelis (1/21/2016)
Hey, here's another idea.Instead of bashing people who at least attempted to help the OP, why don't you chime in with your solution to the problem? For me, that option looks better than any of the alternatives you have given.
Because... sometimes there is no solution. That's the case with the given data. Unless the a_m column is guaranteed to define the grouping and is guaranteed to never repeat as a grouping for the life of the table (and such a "guarantee" must be enforced by some form of constraint that isn't present in this problem) and the combination of a_m and ID is guaranteed to be unique (also requiring a constraint that isn't present in this problem), this problem is doomed to failure.
Sometimes it's better to say so than to offer any code help.
To be honest, I don't see any bashing occurring except maybe from you ;-). I saw a bit of broken English that went straight for the heart of the problem that sometimes comes across as a bit short because the written word frequently doesn't carry the tone. Bashing would be something more like what Celko does or accusing someone of not making an effort when someone is actually trying to point out the larger problem enough for people to have the revelation themselves.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply