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

How to handle this Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 7:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:55 AM
Points: 18, Visits: 170
I need a way how to handle described situation bellow, which will work very fast for lot of rows.

-- Test Data

CREATE TABLE #Table
(
ID [INT] IDENTITY(1, 1)
NOT NULL ,
[col1ID] [INT] NOT NULL ,
[Col2ID] [INT] NOT NULL
)

GO
INSERT INTO #Table
( col1ID, Col2ID )
VALUES ( 1, 1 ),
( 1, 2 ),
( 1, 3 ),
( 2, 1 ),
( 2, 1 ),
( 2, 1 ),
( 3, 2 ),
( 3, 2 ),
( 3, 2 )
GO

Output should look like this:

1. First situation without parameter should give output like this:

Col1|Bit
1 0
2 1
3 1


2. Second situation when you supply parameter for Col2ID, example @parameter = 2 output should look like this:

Col1|Bit
1 0
2 0
3 1


What are best possible ways to handle this two situations ?

Thanks
Post #1562634
Posted Thursday, April 17, 2014 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 268, Visits: 598
What have you tried so far? How is the column 'Bit' computed?
Post #1562639
Posted Thursday, April 17, 2014 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 7,118, Visits: 13,486
What are the business rules?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1562640
Posted Thursday, April 17, 2014 7:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:55 AM
Points: 18, Visits: 170
Bit column will be bit data type only need to return 0 or 1.


when you query the temp table you will notice that:

 col1ID|col2ID
1 1
1 2
1 3

this should give output only one row.
values in col1ID are grouped for same number here col1ID value is 1,
values in col2ID are different for same col1ID and this makes Bit column to be 0

here is the output.

col1ID|Bit
1 0


col1ID|col2ID
2 5
2 5
2 5

values in col1ID are grouped for same number here col1ID is 2,
values in col2ID are same for col1ID and this makes Bit column to be 1

this should give output only one row

col1ID|Bit
2 1


So results are grouped by col1ID, when you have same value in col1ID and different value in col2 (for same value in col1ID ) then you have 0 in bit column, otherwise 1.
Post #1562663
Posted Thursday, April 17, 2014 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
I am sure that your desired output makes perfect sense to you. Unfortunately we don't know your business rules and what you posted does not help clarify what you want. Remember we can't see your screen, we don't know anything about your project and we have no idea what you want to do other than what you have posted. If you can explain the logic we can help with this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1562680
Posted Thursday, April 17, 2014 2:16 PM This worked for the OP Answer marked as solution
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 1,959, Visits: 2,893

DECLARE @parameter int
SET @parameter = 2 --NULL=ALL; spec. value = that value

SELECT
col1ID,
CASE WHEN COUNT(DISTINCT Col2ID) = 1 AND
(@parameter IS NULL OR MIN(Col2ID) = @parameter)
THEN 1 ELSE 0 END AS bit
FROM #Table
GROUP BY col1ID
ORDER BY col1ID




SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1562849
Posted Friday, April 18, 2014 6:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:55 AM
Points: 18, Visits: 170
ScottPletcher (4/17/2014)

DECLARE @parameter int
SET @parameter = 2 --NULL=ALL; spec. value = that value

SELECT
col1ID,
CASE WHEN COUNT(DISTINCT Col2ID) = 1 AND
(@parameter IS NULL OR MIN(Col2ID) = @parameter)
THEN 1 ELSE 0 END AS bit
FROM #Table
GROUP BY col1ID
ORDER BY col1ID






Awesome solution, exactly i was looking for, many thanks
Post #1562995
Posted Friday, April 18, 2014 6:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:08 AM
Points: 10, Visits: 212
I believe this will handle your first case:
SELECT [col1ID]
, CAST(CASE WHEN MIN([Col2ID]) = MAX([Col2ID]) THEN 1 ELSE 0 END AS BIT) AS [Bit]
FROM [#Table]
GROUP BY [col1ID]
ORDER BY [col1ID];

I do not understand what your business rules are for the second case.
Post #1563009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse