How do I sort this set of records ( SQL HELP please )
-
Take a look at the attached screen shot for you you to understand what I need....
1. ) I need you to modify the SQL stmt below but covering the requirements listed below.
1.2) I need to sort by Claims.FormNbr
1.3) Then if there is a value in the Claims.CrossoverOrigFormNbr field then those records should come immediately below the first record set
So as shown in the picture...'15878194' records then followed by '15728890'Ideally it would be best of the sorting can be done by
1. ) SUBSTRING(Claims.claimid,2,6) , 2.) Claims.FormNbr (ASC or DESC ) 3.) if there is a value in the Claims.CrossoverOrigFormNbr field then those come immediately under the record set in Claims.FormNbr
( The attached pic shows the perfect order... The Select stmt will span a wide range of formnbrs.Thanks in advance
Select LEFT(Claims.ClaimID,1) ,
Claims.plancode,
Claims.CrossoverFlag,
Claims.CrossoverOrigFormNbr,
CrossoverNewFormNbr,
Claims.FormNbr,
ClaimsDetail.PCDCDKEy, *
FROM mhpdw2.edw.claim.dclaim AS claims WITH (nolock)
INNER JOIN mhpdw2.edw.claim.dclaimserviceline
AS ClaimsDetail
WITH (nolock)
ON ( claims.claimtid = ClaimsDetail.claimtid )
-
First, can we get some DDL so we can test things easier on our end for writing and testing the code?
Next part in my mind is a tad more tricky to do. I'm thinking that you'll need to do a self join or a CTE or if you don't care about performance, a cursor.
But just reading the SQL statement and having no data is making it more difficult for me to mentally envision how to solve this.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
The data is shown in the picture... I am not able to give you data since it comes from tables that have millions of rows. So please use your imagination.
I have a feeling there is a guru out there who can resolve this just like that ....... -
Once again, you haven't fully described your problem, as we have no idea how the additional field relates back to the preceding records that it having a value is supposed to get it included immediately following, so anything I suggest would be a non-scientific wild-a$$ guess. You've been around this forum more than long enough to know that describing a problem is something you need to be relatively ANAL about and go into sufficient detail. As you've provided no sample data, nor any expected results, we've got literally nothing to work with. I know you can do better...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - mw112009 - Monday, April 17, 2017 2:22 PM
First, sample data is exactly that--sample data. No one expects (or wants) you to type in the entire table. We just need enough to work with.
Second, you already have the data. It would be very simple to script out the data that you have in your graphic whereas all of the rest of us would have to type out the set of data in your graphic. If you don't care enough about the solution to take this step to make it easier for others, why should others care enough to help you?
Finally, I worked out a solution that I think would work, but with your attitude, I'm inclined to tell you to go pound sand.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - mw112009 - Monday, April 17, 2017 2:22 PM
Using my imagination to guess the data says to me that the query you have in the screenshot is giving you the expected results, which is also what you suggested, and your screenshot shows the query. So I am not sure what needs to be done then since that query gives you the expected results.
Now stepping past that, we need some sample data. We don't need millions of rows, just enough to have a good sample set to work with or that can meet your requirements.
breaking it down to the simplest query based on your requirements, I'm thinking something like:DECLARE #temp TABLE(COL1 int, COL2 int)
INSERT INTO #TEMP VALUES (null, 1),(null, 1),(1, 3),(null,2)
and you want the results to be:
COL1 COL2
null 1
null 1
1 3
null 2Is that roughly correct? Is there more edge cases that could come up? such as col2 having a 1 but col1 not being null? or col2 being 3 but col1 not being 1? or is it even possible to have col1 null more than once?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Thank you ... but I managed to resolve it.....
ORDER BY
(
CASE WHEN Claims.CrossoverOrigFormNbr = '' THEN Claims.FormNbr + Claims.CrossoverNewFormNbr
ELSE
Claims.CrossoverOrigFormNbr + Claims.FormNbr
END
)That did it....
- mw112009 - Monday, April 17, 2017 2:46 PM
Now I am a little confsued how that works... using the above order by, and using the 2 examples you provided, are you not doing:
ORDER BY 31607084
31607084 = 15728890+15878194 which is the 2 numbers you provided in your example.
or are those not ints? If they are not ints, you probably see again why DDL is so critical to getting help, no?but couldn't you remvoe the CASE statement since either the ORIG column has a value or the NEW has a value? so you could do
ORDER BY Claims.CrossoverOrigFormNbr + Claims.FormNbr + Claims.CrossoverNewFormNBR
Mind you, I personally do not see how that works, but I am fairly confident my logic in the line above is identical to that in your case, is it not?The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Slightly off topic but you should consider not splattering that NOLOCK hint all over the place. I am guessing that you care about accuracy of your queries. When you use NOLOCK you will get mostly accurate data most of the time. But you can and will randomly get missing and/or duplicate rows. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ - Sean Lange - Monday, April 17, 2017 3:35 PM
Given the database name I'm going to go with no or very little DML activity during querying, so no potential for bad results.
I will note that almost every healthcare data Enterprise Data Warehouse I have ever worked with (and I have been doing healthcare data processing for a wide variety of clients for ~20 years) were day-based. Load at night and report during the day. And in such cases the entire database should be made read only for a variety of reasons, not the least of which is no need for NOLOCKs. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - TheSQLGuru - Monday, April 17, 2017 3:45 PM
That is certainly likely given that is may be a DW but is still worth mentioning as so few people understand the baggage that hint brings and so many use it liberally.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply