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 Steve Your Own Question

Steve
Steve, Consultant
Category: Programming
Satisfied Customers: 241
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Type Your Programming Question Here...
Steve is online now
A new question is answered every 9 seconds

I'm looking for some basic sql query help, SQL server 2014,

Customer Question

I'm looking for some basic sql query help
JA: What programs and versions are you using?
Customer: SQL server 2014
JA: What is your ideal outcome? How can we solve this for you?
Customer: I'm trying to create a basic query that will sum up revenue in two ways
JA: Anything else you want the programmer to know before I connect you?
Customer: I'm fairly new to SQL
Submitted: 2 months ago.
Category: Programming
Expert:  LogicPro replied 2 months ago.

Hi

Expert:  LogicPro replied 2 months ago.

Please provide complete requirements

Customer: replied 2 months ago.
Okay I am trying to create basic SQL query to use to determine recognized revenue for a single period but also YTD
Customer: replied 2 months ago.
I initially started by using SUM in my select statement but because I ultimately would like to have 2 separate columns I thought it may be easier to use a cross apply or other method of summing and then rejoining back into the main table
Expert:  LogicPro replied 2 months ago.

Provide me table structure

Expert:  LogicPro replied 2 months ago.

And sample output

Customer: replied 2 months ago.
It won't let me attach a file for some reason
Expert:  LogicPro replied 2 months ago.

If you have any files, zip and upload to www.wikisend.com and paste download link here.

Customer: replied 2 months ago.
Here is the query
Customer: replied 2 months ago.
Ideally PSM.Revenue 'Current Revenue' would be the amount recognized in a single period i.e. 201601, 201602, 201603 etc
Expert:  LogicPro replied 2 months ago.

Provide me table structure also with data

Customer: replied 2 months ago.
the second instance 'YTD Revenue' would be a running total of the amounts for every period up to the period I'm running it for
Expert:  LogicPro replied 2 months ago.

Provide me table name and column names.

Customer: replied 2 months ago.
I'm having issues with understanding the best way to sum the revenue cleanly for each project the period and YTD
Customer: replied 2 months ago.
for the period and YTD
Expert:  LogicPro replied 2 months ago.

When do you need it?

Customer: replied 2 months ago.
ASAP :)
Customer: replied 2 months ago.
If it's going to take a while don't worry about it, I just figured I would give this a shot wasn't relying on it as my only option
Expert:  LogicPro replied 2 months ago.

For the data provided, output for project(###) ###-####should be 3000 for project revenue and 5000 for ytd?

Customer: replied 2 months ago.
Right
Expert:  LogicPro replied 2 months ago.

please wait

Expert:  LogicPro replied 2 months ago.

My apologies, I am out of ideas and opting out for other Professionals to answer. Please wait. You will be notified by email as soon as any Professional joins you.

Thank you for your patience.

Expert:  Steve replied 2 months ago.

Hi there, my name is***** need a "GROUP BY" to get a subtotal. Try adding this after the last FROM statement and before the last WHERE statement:

GROUP BY PSN.Period

Customer: replied 2 months ago.
Right but I need two sums
Customer: replied 2 months ago.
One for the current period
Customer: replied 2 months ago.
one for ytd
Expert:  Steve replied 2 months ago.

OK, hold on one second

Expert:  Steve replied 2 months ago.

Do you have a separate Year field or is it just part of the PSN.Period?

Expert:  Steve replied 2 months ago.

You need to add another subquery to get the YTD, just a moment please.

Expert:  Steve replied 2 months ago.

, (SELECT SUM(PSN.Revenue) FROM PRSummaryMain PR2 WHERE PR2.Period <= PSN.Period AND PR2.WBS1 = PSM.WBS1 and PR2.WBS2 = PSM.WBS2 AND PR2.WBS3 = PSM.WBS3) AS YTD,

Expert:  Steve replied 2 months ago.

I think that will do it, do you understand the intent? I'm not certain of the related fields but if you understand the intent, you'll be able to change that if you need to.

Expert:  Steve replied 2 months ago.

Hey there, I was just checking back with you to see if this worked.

If this solved your issue, can you please accept my answer or I won't get paid anything for helping you. If not, please chat me back and I'll get you the right command for your query. Thanks!