CODEWITHSUNDEEP
mysqlsql
enchance
enchance

Reputation: 30421

Write SQL code in common english for non-programmers to understand

Is there a known standard for writing SQL logic to common English for non-programmers to understand? The Monthly Reports I'm generating need to be verified by the client and I don't think I have the balls to shove SQL down his richie rich throat. :)

For example, this:

SELECT cust.*
FROM sl_customers cust
LEFT JOIN sl_orders orders ON cust.id = orders.customer_id
WHERE 
    DATE_FORMAT(orders.delivery_date, '%Y-%m-%d') BETWEEN '2014-08-01' AND '2014-08-31'
    AND DATE_FORMAT(cust.created, '%Y-%m-%d') BETWEEN '2014-08-01' AND '2014-08-31'
    AND orders.status != 'cancelled'
    AND cust.entry = 'New User'
    AND cust.infosrc IN ('Ads - Newspaper', 'Ads - TV', 'Ads - Radio', 'Internet - Social Network')

Upvotes: 1

Views: 102

Answers (3)

user149341
user149341

Reputation:

Here's a mostly line-by-line translation:

SELECT cust.*

Get the customer information…

FROM sl_customers cust
LEFT JOIN sl_orders orders ON cust.id = orders.customer_id

…for every order (and the customer associated with the order's customer ID),…

WHERE 
    DATE_FORMAT(orders.delivery_date, '%Y-%m-%d') BETWEEN '2014-08-01' AND '2014-08-31'

…where the order's delivery date is between August 1st and 31st…

(Aside: This part, and the following one, could be written more simply in SQL as orders.delivery_date BETWEEN '2014-08-01' AND '2014-08-31'. You don't need the DATE_FORMAT() here.)

    AND DATE_FORMAT(cust.created, '%Y-%m-%d') BETWEEN '2014-08-01' AND '2014-08-31'

…and the customer's created date is between August 1st and 31st…

    AND orders.status != 'cancelled'

…and the order's status is not "cancelled"…

    AND cust.entry = 'New User'

…and the customer's entry is "New User" (whatever that is?)…

    AND cust.infosrc IN ('Ads - Newspaper', 'Ads - TV', 'Ads - Radio', 'Internet - Social Network')

…and the customer's infosrc is "Ads - Newspaper", "Ads - TV", "Ads - Radio", or "Internet - Social Network".

Upvotes: 1

Leo
Leo

Reputation: 14820

There's no such thing as "SQL logic to common English". Basically, SQL is for developers to understand, not for executives who have no development skills. The closest you can get (professionally) is designing a diagram, if you have some BA skills such as a Data Flow Model Diagram, a Data Flow Diagram or a Database Diagram. If you don't, then simply put it in plain English...

Gets "new" customers who came from "Ads, Social Network, etc" that have an "active order" to be delivered in August

As a aside note, consider using an INNER JOIN instead

Upvotes: 0

Carl Manaster
Carl Manaster

Reputation: 40336

I don't think there's a standard, but if I were trying to explain this to a non-SQL speaker, I'd say something like:

This gives me all the new customers created during August who have active (non-cancelled) orders delivered during August and who came from newspaper, TV, or radio ads, or through social media

Upvotes: 0

Related Questions