Help me write this query in SQL

Question

I'm trying to get help writing a query from scratch using Structured Query Language, or SQL.

What information do you need from me to get my boss to stop screaming? I've asked all my co-workers for help, but they just keep sending me to some site called lmgtfy.com - it doesn't seem to provide any help either.

Thanks,

-Anonymous User

Answer 1

Howdy© and Welcome aboard®!

We're glad you chose dba.stackexchange.com for assistance with your homework a production emergency writing your non-trivial query.

When you need help writing a Query From Scratch©, the first thing to do is make sure the query you're writing doesn't already have a lot of common answers.

Some of the most popular query writing questions around here have to do with:

• Turning columns to rows: (Pivoting)
• Turning rows to columns: (Unpivoting)
• Returning N Rows Per Group
• Calculating rolling sums
• Creating CSV columns
• Splitting strings

If none of those are what you're after, this is the stuff we'll need to get you a Good Answer, Fast®.

The first rule here, before any and all help topics, is DON'T POST SCREENSHOTS.

Folks can't copy and paste a screenshot into a code editor to work with. Aside from illustrating problems with results, they're really not helpful here.

The next steps are pretty easy-to-follow guidelines for Asking A Good Question® so you can Get A Good Answer®.

1. A clear explanation of the problem. For example:

   I have two date columns in my table. I need to write a query that returns the min and max of them combined.

   Or

   I have a column of composite order information (000-AAAA), and I need to split them out.

   See How to create a Minimal, Complete, and Verifiable example for thoughts on this, and related subjects.

   The more relevant information you provide, the better.

   What sort of stuff is relevant?

   • RDBMS Version and Edition
   • The problem you're trying to solve
   • Limitations (no temp tables, etc.)
   • Context (stored proc, view, app code)

2. Sample data as CREATE TABLE and INSERT statements.

   This part can be annoying until you get the hang of it, but it really does help people Jump Right In© and get started helping you. It doesn't have to be a million billion rows like your actual table, but it should provide enough data to demonstrate:

   • Normal arrangement of data
   • Any observable 'bad' data that the query needs to defend against
   • Any observable edge cases that the query needs to defend against

   
   

   Going back to a previous examples:

   I have two date columns in my table. I need to write a query that returns the min and max of them combined. One issue is that one column is a DATE, and the other is DATETIME, and I want them treated equally for the date portion.

   Or

   I have a column of composite order information (000-AAAA), and I need to split them out. Some rows have two hyphens, and some rows have no hyphens.

   For some help with generating data:

   • Generating Test Data: Part 1 - Generating Random Integers and Floats
   • Generating Test Data: Part 2 - Generating Sequential and Random Dates
   • http://www.generatedata.com/
   • SQL and PL/SQL FAQ

3. Expected results -- but don't just tell us what you want. Some people like word problems, but other people are terrified by the ambiguity of Words On The Internet®©.

   This helps anyone trying to find a solution to your code validate their results. There are usually multiple solutions to a single problem.

   Going back to a previous examples:

   I have two date columns in my table. I need to write a query that returns the min and max of them combined. One issue is that one column is a DATE, and the other is DATETIME, and I want them treated equally for the date portion. I'd really like to to have the data ordered descending.

   Or

   I have a column of composite order information (000-AAAA), and I need to spit them out. Some rows have two hyphens, and some rows have no hyphens. I'd really like each portion (before and after the hypen(s)) in separate columns.

4. Show us what you got -- no, no, put your pants back on.

   I mean show us any code you've tried. Explain why it's not working.

   If you're getting an error message, POST THE ERROR MESSAGE, as text, not a screenshot.

   If you've already researched your problem (you did research your problem first, right?), post any material you've already checked.

   This helps us in two ways: We don't duplicate work, and we don't try to send you to a resource you've already visited. Perhaps the most depressing thing on the internet is a link already clicked.

5. Expect clarification questions

   There's almost always going to be a quirk. For example, when someone asks a punch-in-punch-out question, what to do if punch-in and punch-out don't occur on the same day usually comes up, because it's a common dilemma.

   When you respond to comments, UPDATE YOUR QUESTION.

   Don't try to respond at length in comments. It just makes more work for moderators to clean up later.

   Use comments to notify people that your question has been updated.

   Doing this also gives you the opportunity to format error messages, code, and output in ways that comments just aren't meant for.

6. Testing code online for free can make this easier. There are a number of sites that offer sandboxed environments to run SQL code in on various platforms.

   • dbfiddle.uk
   • sqlfiddle.com
   • db-fiddle.com
   • rextester.com

Following these steps should lead to you getting The Best Answer Ever® to your question.

These suggestions are here for Your Success® as much as ours.

We want to help. We're here to help. For free. Taking a bit to ask a good, well-formatted question lets everyone know that you respect their time as much as they respect yours.

For related thoughts: How To Ask Questions The Smart Way

We also have a list of what we consider canonical answers to a variety of problems that are commonly asked here.

Thanks, and Happy Querying®!