Calculation of fuel using given value

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

I have a table contains two fields named ME and STE with data type decimal.I want to find

(ME used for last 90 Days / Total STE for last 90 days) * 24

and show this in another field.
I need to solve this using sql. How can I write query for solving this. Please help me to solve this.

Posted 9-May-13 0:50am

CSR89

Add a Solution

Comments

gvprabu 9-May-13 6:57am

give some values for actual table data and required output.

CSR89 9-May-13 7:04am

I have tried this one. SELECT ME FROM table_name WHERE CreatedDate >= dateadd(d,-90,getdate())/(SELECT STE FROM table_name WHERE CreatedDate >= dateadd(d,-90,getdate()))*24. But showing error as "Implicit conversion from data type datetime to decimal is not allowed. Use the CONVERT function to run this query."

gvprabu 9-May-13 7:04am

how we will find the last 90 days, In that table any date time column is there. then why u told 2 columns like ME and STE?

gvprabu 9-May-13 7:05am

first update ur Question with table structure and sample data.

gvprabu 9-May-13 7:06am

what ever u tried that is not correct way... send the table structure and required output

CSR89 9-May-13 7:17am

two more fields are there name and date, also 10 entries are there. How can I write query to find my solution that is (ME used for last 90 Days / Total STE for last 90 days) * 24 ) individually for each 10 entry.

2 solutions

Solution 2

I'll do it like this:

SQL

SELECT SUM(ME)/SUM(STE)*24 FROM myTable WHERE DateNew>=DATEADD(YourDay,-90,GetDate())

Posted 9-May-13 1:56am

Radoslav Dimitrov

Comments

gvprabu 9-May-13 8:34am

what is the difference between ur solution and Solution 1.? Don't post same answers like existing one.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-05-09T01:05:00

Solution 1

Try:

SQL

SELECT (SUM(ME) / SUM(STE)) * 24 FROM myTable WHERE DateInserted >= DATEADD(day, -90, GetDate())

Posted 9-May-13 1:05am

OriginalGriff

Comments

CSR89 9-May-13 7:11am

Hi OriginalGriff, I have one more doubt. In above question I have one more field named NAME and I have 10 entries. How can i use the query(your solution) to find my solution that is (ME used for last 90 Days / Total STE for last 90 days) * 24 ) individually for each 10 entry.

OriginalGriff 9-May-13 7:17am

Add
GROUP BY Name
to the end of the query.

CSR89 9-May-13 7:21am

If same name is repeating with different entry, then what to do? At that time also I need solution for all entry induvidually

OriginalGriff 9-May-13 7:25am

Then you need to look at what makes them "different entries" - I can't say because I have no idea of your data other than what you have told me above!

CSR89 9-May-13 7:29am

ok

Maciej Los 9-May-13 15:15pm

+5