April 19, 2011 at 2:32 pm
I am working on implementing a business logic, I need help figuring out the logic
Logic:
Each customer has 4 account fileds namely Acc1, Acc2, Acc3 and Acc4 in dbo.Accounts table. It is equally possible for all these accounts to be populated and being NULL, they can be either. Depening on the availability I need to select top 2 accounts as per priority
Order of Priority is
Acc1
Acc3
Acc2
Acc4.
If No account is populated NULL can be selected, If only 1 acc is populated that acc can be slected
Which means if Acc1 and Acc3 are NOT NULL , we need to SELECT Acc1+'/'+Acc3 FROM dbo.Accounts
But IF (Acc1 is NULL) , then SELECT Acc3+'/'+Acc2 FROM dbo.Accounts
And so on . so we will end up in the follwoing possible combinations
Acc1+Acc3
Acc1+Acc2
Acc1+Acc4
Acc2+Acc3
Acc2+Acc4
Acc3+Acc4
Acc1
Acc2
Acc3 and
Acc4
Can some one help me with this logic, I can share mine, buts its so Dumb 🙁 . I eneded up in Nested If loops.
As Im writting this out i have a alternate way of expressing the logic
IF (All 4 are NULL) --> SELECT NULL
IF (Only 1 is populated) --> SELECT that 1
IF (Only 2 are Populated) --> SELECT those 2
IF (3 or all(4) are populated) --> Apply the priority logic .. Hmmm
Thnks
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
April 19, 2011 at 3:14 pm
MV,
If you're willing to help us help you, I think we can come up with something.
What I need is for you to read the first article in my signature, then come back and post some table definitions and sample data (in the form of CREATE TABLE / INSERT statements). Then, based upon the sample data provided, show us what your expected results would be. This will allow us to test out different things that will work, and then to post a tested, working solution.
Off the top of my head, this is looking like:
1. UNPIVOT.
2. Sort by priority.
3. Get the top (2) NOT NULL.
4. Depending on the expected results, PIVOTing this information back.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 4:20 pm
I appolozize for the unethical post.
I am atatching three scripts:
1. TO create the table
2. TO insert sampel Data
3. Expected results for about 13 records out of teh selected TOP 100.
The business logic condition still holds good.
For the econvinience , the SQL code is being embeded here too
CREATE TABLE
/****** Object: Table [dbo].[MasterAccountTest] Script Date: 04/19/2011 16:37:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MasterAccountTest](
[Acc1] [numeric](11, 0) NULL,
[Acc2] [numeric](11, 0) NULL,
[Acc3] [numeric](11, 0) NULL,
[Acc4] [numeric](11, 0) NULL)
GO
INSERT STATEMENTS
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 2088643245 , 2084297481 , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7507644027 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1670852599 , NULL , 7805021468 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7805026425 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1463229507 , NULL , 7976845259 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7908124714 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1495244131 , NULL , 7771636083 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 2082390898 , 2086875576 , 7960940816 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1792846016 , NULL , 7988507727 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7910815634 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7863212059 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7756071546 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 2380486739 , 2380204745 , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1376503929 , 1376513902 , 7972599274 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1376517366 , 1634255774 , 7824995652 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1946590573 , NULL , 7926416923 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7887597851 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1423877110 , NULL , 7939552884 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1628487832 , NULL , 7852520348 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1249658471 , 1249758425 , 7912205360 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7707093619 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7884004456 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1926772985 , 2476887688 , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1618275378 , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 2086713569 , 2075820111 , 7958749150 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1293401366 , 1293562903 , 7985525286 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1932841155 , 2086435252 , 7956996739 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 2089695507 , 2088852118 , 7910439110 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 1914150804 , 1914914700 , NULL , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7789738640 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7931307726 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , 7909745768 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( 2087651038 , NULL , 7506728549 , NULL );
INSERT INTO [dbo].[MasterAccountTest] VALUES ( NULL , NULL , NULL , NULL );
Expected Results
-- SELECT TOP(100)* FROM dbo.MasterAccountTest
Acc1 Acc2 Acc3 Acc4 Expected Result
------------------------------------------------------- --------------------------------------- --------------------------
2088643245 2084297481 NULL NULL 2088643245/2084297481
NULL NULL 7507644027 NULL 7507644027
1670852599 NULL 7805021468 NULL 1670852599/7805021468
NULL NULL 7805026425 NULL 7805026425
1463229507 NULL 7976845259 NULL 1463229507/7976845259
NULL NULL NULL NULL NULL
2088643245 2084297481 NULL NULL 2088643245/2084297481
NULL NULL 7507644027 NULL 7507644027
1670852599 NULL 7805021468 NULL 1670852599/7805021468
NULL NULL 7805026425 NULL 7805026425
1463229507 NULL 7976845259 NULL
NULL NULL NULL NULL
NULL NULL 7908124714 NULL
1495244131 NULL 7771636083 NULL
2082390898 2086875576 7960940816 NULL
1792846016 NULL 7988507727 NULL
NULL NULL 7910815634 NULL
NULL NULL 7863212059 NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL 7756071546 NULL
2380486739 2380204745 NULL NULL
1376503929 1376513902 7972599274 NULL
1376517366 1634255774 7824995652 NULL
1946590573 NULL 7926416923 NULL
NULL NULL NULL NULL
NULL NULL 7887597851 NULL
1423877110 NULL 7939552884 NULL
1628487832 NULL 7852520348 NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
1249658471 1249758425 7912205360 NULL
NULL NULL 7707093619 NULL
NULL NULL 7884004456 NULL
1926772985 2476887688 NULL NULL
NULL NULL NULL NULL
1618275378 NULL NULL NULL
2086713569 2075820111 7958749150 NULL
1293401366 1293562903 7985525286 NULL
1932841155 2086435252 7956996739 NULL
2089695507 2088852118 7910439110 NULL
NULL NULL NULL NULL
1914150804 1914914700 NULL NULL
NULL NULL 7789738640 NULL
NULL NULL 7931307726 NULL
NULL NULL 7909745768 NULL
2087651038 NULL 7506728549 NULL
NULL NULL NULL NULL
1334466649 NULL NULL NULL
1225445233 NULL 7717084234 NULL
NULL NULL 7932813594 NULL
NULL NULL 7852465154 NULL
NULL NULL NULL NULL
NULL NULL 7939935894 NULL
1903828500 NULL 7790401441 NULL
1179020101 NULL NULL NULL
1709878878 NULL 7903154402 NULL
1403282818 NULL 7871567184 NULL
1233642626 NULL 7936485417 NULL
2075247884 NULL 7788663030 NULL
1912857539 NULL 7869223823 NULL
1912902349 8005515551 7925861087 NULL
2082861357 NULL 7807088039 NULL
1386792831 1386793623 7812187883 NULL
2089547740 2084274343 7718987992 NULL
NULL NULL 7918778991 NULL
NULL NULL NULL NULL
NULL NULL 7872955436 NULL
NULL NULL 7506853424 NULL
NULL NULL NULL NULL
NULL NULL 7974574168 NULL
1256752000 NULL 7980977186 NULL
2392343696 NULL NULL NULL
NULL NULL 7772448207 NULL
NULL NULL NULL NULL
1379388287 1379288211 7775913919 NULL
NULL NULL NULL NULL
1293573166 NULL 7504246418 NULL
NULL NULL NULL NULL
1932771084 NULL 7985740526 NULL
2838334444 NULL 7926026914 NULL
NULL NULL 7912606935 NULL
1614280413 NULL 7923194641 NULL
2089081551 2089038586 7853357661 NULL
1773786212 NULL 7854773634 NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
1384867510 NULL NULL NULL
2079369067 2072085612 NULL NULL
NULL NULL 7901577217 NULL
1788514404 NULL 7826431232 NULL
NULL NULL 7723646997 NULL
NULL NULL 7923837363 NULL
1465821414 1456821511 7788897602 NULL
1536509162 NULL 7842881248 NULL
1249446842 NULL 7934765817 NULL
NULL NULL 7933687659 NULL
NULL NULL 7951268733 NULL
(100 row(s) affected)
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
April 19, 2011 at 6:36 pm
Minnesota - Viking (4/19/2011)
I appolozize for the unethical post.I am atatching three scripts:
1. TO create the table
2. TO insert sampel Data
3. Expected results for about 13 records out of teh selected TOP 100.
The business logic condition still holds good.
For the econvinience , the SQL code is being embeded here too
Because of the nature of the formating script on this forum, posting such a huge amount of code really screws things up for the rest of us trying to help. Please go back and edit your post to only contain the first 10 lines of INSERT code. Thanks.
Here's the code that will solve your problem.
SELECT STUFF(
ISNULL('\'+CAST(Acc1 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc2 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc3 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc4 AS VARCHAR(10)),'')
,1,1,'')
FROM dbo.MasterAccountTest
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2011 at 3:08 am
Nice solution Jeff - except the priority is supposed to be 1,3,2,4 not 1,2,3,4 according to the orig post, so simple mod required.
--Edit, I was wrong. In the case where 3 or more accounts are populated, only the first two are required, so the solution requires more work ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 20, 2011 at 8:08 am
;
WITH cteAccountOrder (ColumnName, ColumnOrder) AS
(
-- the preferred sorting order when > 2 accounts in a row
SELECT 'Acc1', 1 UNION ALL
SELECT 'Acc3', 2 UNION ALL
SELECT 'Acc2', 3 UNION ALL
SELECT 'Acc4', 4
), cteRN AS
(
-- add a row number so we can keep track of each row
SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
Acc1,
Acc2,
Acc3,
Acc4
FROM MasterAccountTest
), cteUnPivot AS
(
-- unpivot the acc data into columns of the column name and it's value
SELECT RN,
ColumnName,
ColumnValue
FROM (SELECT RN, Acc1, Acc2, Acc3, Acc4
FROM cteRN) p
UNPIVOT
(ColumnValue FOR ColumnName IN (Acc1, Acc2, Acc3, Acc4)) AS unpvt
), cteSort AS
(
-- sort each rows accounts by the preferred sorting order
SELECT t1.RN,
t1.ColumnName,
t1.ColumnValue,
RNSort = ROW_NUMBER() OVER (PARTITION BY t1.rn ORDER BY t2.ColumnOrder)
FROM cteUnPivot t1
JOIN cteAccountOrder t2
ON t1.ColumnName = t2.ColumnName
), cteDistinct AS
(
-- get a distinct list of rows
SELECT DISTINCT RN
FROM cteSort
WHERE RNSort < 3
)
SELECT c1.RN,
c2.Accounts
FROM cteDistinct c1
-- for each row, get the first (top) two accounts in the proper sort order.
-- put into a '/' delimited string.
-- remove the leading '/'
CROSS APPLY (SELECT Accounts = STUFF((SELECT TOP (2)
'/' + CONVERT(VARCHAR(11), ColumnValue)
FROM cteSort c2
WHERE c1.RN = c2.RN
ORDER BY c2.RNSort
FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,'')) c2
ORDER BY c1.RN;
Edit: Check out Creating a comma-separated list (SQL Spackle)[/url] for more information on how the FOR XML/STUFF stuff is all working.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2011 at 4:26 pm
Phil Parkin (4/20/2011)
Nice solution Jeff - except the priority is supposed to be 1,3,2,4 not 1,2,3,4 according to the orig post, so simple mod required.--Edit, I was wrong. In the case where 3 or more accounts are populated, only the first two are required, so the solution requires more work ...
Dang... I didn't even look at the rest of the requirements. Thanks for the catch, Phil.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2011 at 5:41 pm
@Minnesota - Viking,
What do you want to do about dupes across the 4 columns?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2011 at 6:10 pm
This looked like a fun problem (after Phil told me the rules... thanks again for the catch, Phil) so I decided to give it a whirl in my own fashion, anyway, even after Wayne had sussed the problem. If you consider de-duplification of data a bonus (there's 8 duplicated rows in the data not including the ones with all nulls), I think you may enjoy the performance of the following...
SELECT --==== Put it all back together from below with a Cross Tab (think pivot)
Acc1, Acc2, Acc3, Acc4,
Priority2 =
MAX(CASE WHEN ca.FinalSort = 1 THEN ca.Accx ELSE NULL END)
+ MAX(CASE WHEN ca.FinalSort = 2 THEN '/' + ca.Accx ELSE '' END)
FROM dbo.MasterAccountTest
CROSS APPLY
( --=== Get the top 2 non-null entries according to the 1,3,2,4 priority
SELECT TOP 2
MySort = ROW_NUMBER() OVER (ORDER BY d.MySort),
Accx = CAST(d.Accx AS VARCHAR(30))
FROM ( --=== Stack'em (think unpivot)
SELECT 1, Acc1 UNION ALL
SELECT 2, Acc3 UNION ALL
SELECT 3, Acc2 UNION ALL
SELECT 4, Acc4
) d (MySort,AccX)
WHERE d.Accx IS NOT NULL
) ca (FinalSort,Accx)
GROUP BY Acc1, Acc2, Acc3, Acc4
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2011 at 6:16 pm
Jeff's solutions works if you know the length. **a few assumptions here**
SELECT
LEFT(
STUFF(
ISNULL('\'+CAST(Acc1 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc3 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc2 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc4 AS VARCHAR(10)),'')
,1,1,'')
,21)
FROM dbo.MasterAccountTest
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 20, 2011 at 6:40 pm
Jason Selburg (4/20/2011)
Jeff's solutions works if you know the length. **a few assumptions here**
SELECT
LEFT(
STUFF(
ISNULL('\'+CAST(Acc1 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc3 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc2 AS VARCHAR(10)),'')
+ ISNULL('\'+CAST(Acc4 AS VARCHAR(10)),'')
,1,1,'')
,21)
FROM dbo.MasterAccountTest
Jason, you're a bloody genius! :blush: Just one tweak on your wonderful idea and we're there!
If we don't know the length, then we can force the max length for positive BIGINTs and simply remove all spaces afterwards.
SELECT REPLACE(
LEFT(
STUFF(
ISNULL('\'+CAST(Acc1 AS CHAR(19)),'')
+ ISNULL('\'+CAST(Acc3 AS CHAR(19)),'')
+ ISNULL('\'+CAST(Acc2 AS CHAR(19)),'')
+ ISNULL('\'+CAST(Acc4 AS CHAR(19)),'')
,1,1,'')
,39)
,' ','')
FROM dbo.MasterAccountTest
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2011 at 2:27 am
Neat.
And there was me trying to find the second occurrence of "\" and SELECT LEFT to that position, dealing with NULLs etc along the way. Not pretty.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 21, 2011 at 3:20 am
Jeff Moden (4/20/2011)
If we don't know the length, then we can force the max length for positive BIGINTs and simply remove all spaces afterwards.
Also if you append the separator to the cast instead of prefixing, you don't need the stuff 😛
Far away is close at hand in the images of elsewhere.
Anon.
April 21, 2011 at 3:24 am
David Burrows (4/21/2011)
Jeff Moden (4/20/2011)
If we don't know the length, then we can force the max length for positive BIGINTs and simply remove all spaces afterwards.Also if you append the separator to the cast instead of prefixing, you don't need the stuff 😛
Evolution in practice - don't you just love it 😀
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 21, 2011 at 3:31 am
ChrisM@home (4/21/2011)
Evolution in practice - don't you just love it 😀
It is darn near impossible to improve on Jeff's solutions, so must make the most of it while I can 😀
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply