How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I need assistance with SQL coding that involves Lead, Lag,

Customer Question

I need assistance with SQL coding that involves Lead, Lag, Partition and related coding. It involves numbering a list of titles and values from 1 to x, x being when a title changes. Then I want to calculate a running total when the count gets to 12 and then a rolling 12-month total starting in the 12th row. Financially, when the count gets to 24, I want it to compute the R12 value at 24 divided by the value at row 12. Does that make sense and can you help me?
JA: What programs and versions are you using?
Customer: SQL 2016
JA: What is your ideal outcome? How can we solve this for you?
Customer: Replicate a sequence numbering, rolling 12 month value and percent change that I am now doing in Excel.
JA: Anything else you want the programmer to know before I connect you?
Customer: I don't think so.
Submitted: 9 months ago.
Category: Programming
Expert:  Bhavik Joshi replied 9 months ago.

Hi, I'm Bhavik. Welcome to JustAnswer. I'm reviewing your question now, and will reply back ASAP.

Expert:  Bhavik Joshi replied 9 months ago.

I think I got your question, I do not have any doubt as of now in requirement.

Please share your existing code and sample database or table structure if any so that I can pick it up from there.

Customer: replied 9 months ago.
I am sending you the file that illustrates what I need. I need Columns F-H computed in SQL from a Table that will have A-E populated.
Customer: replied 9 months ago.
I am resending the file. The first row has to have one inserted.
Customer: replied 9 months ago.
Did you receive?
Expert:  Bhavik Joshi replied 9 months ago.

Yes, I got it.

Customer: replied 9 months ago.
Do you have a time estimate on when the SQL code will be ready?
Expert:  Bhavik Joshi replied 9 months ago.

I was about to ask you the same question.? Is this is urgent ? If so then I can give more time to it.

Expert:  Bhavik Joshi replied 9 months ago.

Also it would be great if you could give me the expected results for some of the data so that I can see if I am getting correct results or not.

Customer: replied 9 months ago.
The Excel spreadsheet shows the expected results. In a SQL table with Columns A-D, I want to be able to have the SQL code that produces the remaining columns in the spreadsheet. Does that make sense?
Customer: replied 9 months ago.
The results would be a SQL Table that looks just like the Excel spreadsheet I sent you.
Expert:  Bhavik Joshi replied 9 months ago.

Yes, I can see column G is the running total. What about column H ?

Customer: replied 9 months ago.
G is actually not a running total. If the seq number gets to 12, then it is a rolling 12-month total. Then Column H is the % change. It does not start unless there is a seq of 24 or higher. When it reaches 24, the Column H is Column G, row 24/ row 12 -1. Row 25 would be row 25/row 13 -1. See the formulas in each cell?
Expert:  Bhavik Joshi replied 9 months ago.

Yes, I can see the formula's now, It is giving reference error in some initial rows of column G.

I will give you query to achieve this in sql as soon as possible.

Customer: replied 9 months ago.
Did you get the second spreadsheet I sent that had the errors removed? The first cell has to be initiated with a 1.
Expert:  Bhavik Joshi replied 9 months ago.

Yes, I got both the sheets.

Customer: replied 9 months ago.
Do you think you will be able to have the SQL code to me today?
Expert:  Bhavik Joshi replied 9 months ago.

I try to give you working code within 24 hrs from now.

Customer: replied 9 months ago.
Okay. I have a project stalled until I get it, so please deliver as soon as possible. Are you having to work on other things?
Expert:  Bhavik Joshi replied 9 months ago.

Yes, I have couple of things for today to look for. But I try to complete your work as soon as I can.

Customer: replied 9 months ago.
Okay, I was hoping you would be able to stay on it once you started. I assumed that would be the case. I may need to seek another solution. I'm not meaning to be critical. I just misunderstood.
Customer: replied 9 months ago.
How many more hours are you working today? And are you confident that you can create the SQL code to replicate the columns in the Excel spreadsheet?
Expert:  Bhavik Joshi replied 9 months ago.

Yes, I think, I will be able to do it. I have done this before ( around 2 to 3 years ago ). I am in India so it is currently 23:40 here so it's almost end of my day today.

Customer: replied 9 months ago.
Okay, I will pay $100 if you can have it by 11:00 your time (about 11 hours from now) and if it works correctly.
Expert:  Bhavik Joshi replied 9 months ago.

That's Great !!

Expert:  Bhavik Joshi replied 9 months ago.

I am sorry, I could not do that. I am opting out so that another expert can look into it.

Customer: replied 9 months ago.
When will I hear from the other expert?
Expert:  Bhavik Joshi replied 9 months ago.

I used this to get running total but could not do anything further that ,Not sure how to take care of values which we are getting at row 13 onwards

Below will give you running total.

SELECT a.id, a.name,a.amount, (SELECT SUM(b.amount)
FROM NAMES b
WHERE b.id <= a.id GROUP BY b.name)
FROM NAMES a
ORDER BY a.id,a.name;

Expert:  Bhavik Joshi replied 9 months ago.

Soon someone will pick it up.

Expert:  Bruce Wilner replied 9 months ago.

Hello. I built the world's first multi-level secure RDBMS, Trusted RUBIX, including its SQL parser and ESQL/C precompiler.

I also wrote the proposed Multi-level Security Extensions to ANSI SQL Level II.

Perhaps I can assist you with your problems?

Expert:  Bruce Wilner replied 9 months ago.

Your first problem right off the bat is that you can't use GROUP BY or HAVING in a subquery, only at the top level. If some product supported it--which one doubts severely--it is entirely non-standard and unreliable. ALWAYS stick to the standard; do not become dependent upon vendor-specific niceties.

Also, in your sample query, "Row Thirteen" doesn't help me. I can't see your data, and I don't see that the query per se incorporates any row number semantics.

Customer: replied 9 months ago.
Do you understand my need or do I have to explain it all over again? Do you have my sample Excel spreadsheet I need to have replicated in a SQL Table? I am seeking the SQL code to compute three columns based on five given columns of data.
Expert:  Bruce Wilner replied 9 months ago.

Not sure why you would need such a tortured formulation, anyway, for what seems very straightforward. Have you posted the SCHEMA (that is, the CREATE TABLE statements) so that I can see what tables have what columns and what keys?

Expert:  Bruce Wilner replied 9 months ago.

I will look at your spreadsheet and see what I can figure out. Stand by. Thanks.

Expert:  Bruce Wilner replied 9 months ago.

You are computing F, G, and H based upon A through E, I take it?

Expert:  Bruce Wilner replied 9 months ago.

Interesting how EVERY row features the value 'DART' in column A. This renders it moot for inclusion in the table. Simply zap that column--UNLESS this is part of a BIGGER spreadsheet that includes values OTHER THAN 'DART'. Otherwise, we are needlessly toting around excess information.

Humor me and tell me how those three columns are calculated. I need a description like the following:

For every set of rows with the same value of FOO, count how many unique values of BAR occur.

For every set of rows with the same value of FOO, determine the average value of AWK within each set of rows that have the same ZIZ; then determine the average of all those averages.

Customer: replied 9 months ago.
Bruce, I'm not sure we are going to get along if you characterize my need as a tortured formulation.Yes, to your question. F is a simple sequencing that starts over any time there is a change in Column D. Column G is a Rolling 12-month calculation. Column H is a calculation that begins if the sequence number is ***** or greater. It is R24/R12-1, then R25/R13-1. and so on.
Customer: replied 9 months ago.
No sir, I am the client. You humor me!
Customer: replied 9 months ago.
My formula is in each Excel cell.
Expert:  Bruce Wilner replied 9 months ago.

Guess what: in SQL, there is no such thing as "a simple sequencing." SQL does not support the notion of sequences in that manner; tables are, by definition, unordered unless you IMPOSE an order via "ORDER BY."

Wait--you'll say "what about serial keys!" That's different: those are automatically assigned upon INSERT. Different beast altogether.

LOL I am happy to humor you, however we must meet halfway. Adding a "simple sequencing" won't serve any purpose but to require tortured mechanisms and to obfuscate anything else that depends upon it. There is guaranteed to be a simpler way. Never mind the "sequencing" and the "rolling": can you please provide the type of description for which I asked above?

"For every set of rows with the same value of FOO, . . ."

Expert:  Bruce Wilner replied 9 months ago.

Formulas--presumably copy/pasted from here and there--capture row-number-based specifics that have nothing to do with the STRUCTURE of the data. I require a formulation that makes semantic sense. SQL can calculate, say, the average COST for every DEPT that doesn't have three or more unique values of BLOOB; it CANNOT calculate the average cost for "rows 2411-2569," as there's no such thing in its world.

Let's try one more time: what specific problem are you trying to solve by including columns F, G, and H? What summary statistics, for example, do you want to derive?

Expert:  Bruce Wilner replied 9 months ago.

I see, right away, that one column is calculated based upon whether this value is greater than the value in the predecessor row.

SQL has no notion of predecessors--NONE AT ALL.

An SQL table is not a spreadsheet. The concepts don't map one-to-one.

SQL requires a different sort of reasoning: "set-at-a-time." But that doesn't mean you derive some bizarre column, F, that makes no sense WITHIN THE SQL WORLD, and then derive G based upon that, and then derive H based upon that.

What PROBLEM are you trying to solve with these three added columns?

Customer: replied 9 months ago.
Are you saying you can't replicate my spreadsheet in SQL? I want column F to be number 1 through the last number until Column D changes. Then based on the Column F, create a Rolling 12 month total. It will just be a cumulative value until Column F reaches 12. If Column F is less than 12, there will not be a R12, just a cumulative value. If Column F reaches 24, then compute a % change in Column H, starting with R24/R12-1.
Customer: replied 9 months ago.
You can't do this with Lead and Lag? I'm wanting to have a Rolling 12 months in G and a % Change in Column H. That's all.
Expert:  Bruce Wilner replied 9 months ago.

That's what I'm saying. You have to solve the problem appropriately.

You talk of twelve-month rolling totals. Funny, I don't see any months or dates anywhere. How is SQL supposed to determine what transaction relates to what month?

If "that's all," then kindly provide actual dates in some column somewhere.

What exactly are 12 and 24? Let me take a wild guess: things just happen to "roll over" every twelve months--even though there is no association with calendar years.

If you want things to be based upon dates, SQL need dates, not some seat-of-the-pants representation.

Expert:  Bruce Wilner replied 9 months ago.

Also, "% Change in Column H is not a "that's all," either. % change of what with relation to what?

Customer: replied 9 months ago.
You are insulting. I want to terminate this discussion, report you to a supervisor and have my money refunded.
Expert:  Bruce Wilner replied 9 months ago.

No, actually, I'm just trying to obtain a clear description of the problem.

Any SQL expert will proceed in precisely the same manner. SQL needs actual dates. It does not know from "neighboring" rows. It does not understand things that roll per twelves or twenty-fours. If you provide ACTUAL DATES, the problem can be solved VERY READILY.

Customer: replied 9 months ago.
The dates are in Column B.
Expert:  Bruce Wilner replied 9 months ago.

Now we are getting somewhere. I suspected those were Julian. They can readily be converted to human-readable.

Now, please re-describe F, G, and H in terms of dates and years, not in terms of "the previous row" (no such thing in SQL) or "whenever column D changes" (again, no such thing in SQL, because changes means 'changes from previous row,' which there's no such thing in SQL).

Expert:  Bruce Wilner replied 9 months ago.

Did that throw you for a loop? "There's no such thing as the previous row in SQL"? It happens to be true. Orders are only imposed by human readers at the last minute. Relations operate strictly upon independent tuples (rows of data, or pieces of rows). No such animal as "previous."

Column F will vanish, I should think. It seems to be a stopgap that somehow enables you to calculate G and H. When G and H are properly calculated, there will be no need for F.

Customer: replied 9 months ago.
Each Column D has a varying number of months. I want to compute a Rolling 12 month value of Column E in Column G.
Expert:  Bruce Wilner replied 9 months ago.

I may be missing something, as I don't see any months at all in D, just text strings.

By ROLLING, you refer to the twelve months IMMEDIATELY PRECEDING THIS ROW?

In the next row, the ROLLING total (column G) will have dropped off the oldest record from the last calculation, and picked up a fresh one?

In column H, you want the year-to-year variation. You want this recalculated in rolling fashion, every twelve months? You wait for 24 because there's no such thing as "one year compared to the previous year" until you've collected two years?

Customer: replied 9 months ago.
The months are in B. Note that when the value in D changes, the months change to a new set of months.
Customer: replied 9 months ago.
the Rolling 12 months means that the 12th month is the sum of the previous 12. The 13th month is also the sum of the previous 12. The 13th month is added and the 1st month is dropped off.
Customer: replied 9 months ago.
Starting in the 24th month, the the change in the Rolling 12 value from one year to the next is computed.
Expert:  Bruce Wilner replied 9 months ago.

O.K., and (assuming arguendo that there are twelve monthly records per year JUST FOR ILLUSTRATIVE PURPOSES) the rolling total reflects the last twelve records, and the % change reflects this record and the twelve-records-ago record?

That is readily done.

Customer: replied 9 months ago.
everything starts over with there is a change in Column D.
Expert:  Bruce Wilner replied 9 months ago.

Interesting how your spreadsheet wasn't usefully sorted by ALLOCMON.

By the way, ALLOCMON isn't the best name for a column that contains a month/day/year--unless your only interest is the month and the year. Please advise.

Expert:  Bruce Wilner replied 9 months ago.

And now we are totally lost by "everything starts when there is a change in column D."

PLEASE don't consider "changes" (I thought I said several times that there is no such thing as a sequence or a predecessor). Please consider GROUPINGS.

You want a DIFFERENT rolling total PER month, PER subcategory? I see that I had to torture this requirement out of you!

Customer: replied 9 months ago.
It is intentionally sorted by Client, Category, Subcategory and then AllocMonth.
Customer: replied 9 months ago.
Stop, stop stop! We're done. Have someone at Just Answer to contact me immediately.
Expert:  Bruce Wilner replied 9 months ago.

Well, it doesn't appear that way to me. It appears to be sorted by Client, Allcmonth, Category, Subcat.

HUMBLE SUGGESTION: stick to your spreadsheet, since you have a crystal-clear idea of what you want, and each question I ask about what you claim to want causes the Hydra to sprout two more heads.

Expert:  Bruce Wilner replied 9 months ago.

Since we are obviously unable to fulfill your goal--including the SQL internals guru who knows every last subtle semantic of the language and, what's more, has implemented the ENGINE to effectuate it--I will provide you a refund.

Your spreadsheet works. It supports the notions with which you are accustomed to working, such as "predecessor" and "sortings" that change from the beginning of the sentence to the end of the sentence. I WILL GET YOU A FULL REFUND.