If then Else Statements (sum as well)

Question

If then Else Statements (sum as well)

mgarner-946171

Grasshopper

Points: 13
More actions
March 17, 2009 at 2:23 pm

#201106

Ok. this is my first time posting to this site. I am just learning SOL, and really need the help, as my Boss wants this project finished.
Problem: I have data from two sources. One is called emission data and the other is pump data. I have start dates and end dates for the pump times. The emission data is recorded every second. What I need is a formula that will show if a pump was on or off during any point in time. For instance if I scroll to a point in time on the table, I can tell if a pump was on or off. I have an excel spreadsheet that does this, but I need to learn how do do it in SQL, since the data will be growing at a very fast rate over the next few months. I can email the spreadsheet to anyone who will look at it for some ideas how to make the query.
Thanks in advance-M

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mgarner-946171 Grasshopper Points: 13 More actions · Answer 2

Thanks for the quick response.

the table looks something like this 10,000+rows and 83 colums.

I will break down the columns I think I need.

StartTime=pump start time

EndTime=pump end time

EmissionDate=counts every second , giving emission data not needed for this exersize.

The BETWEEN statement did not work for me, although I may have not used it right. The excel sheet that does the formula uses a less than..greater than.

I can get one data point from one date, such as select StartTime,EndTime from EmissionTable

WHERE StartTime>'2009-01-19 16:20:00.000' AND EndTime< '2009-01-19 19:20:00.000'

But I need to be able to get all the times the pump was on, or off.

Something that would compare the start and end times of the pumps for any given time. I know thats pretty vauge, but as I said, I dont know sql speak yet. I can email the spreadsheet if need be. Thanks again.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Please read the article I linked.

I don't want to guess as to your requirements and I would like to be able to test it before posting. Hence the request for a table definition, sample data and expected results.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mgarner-946171 Grasshopper Points: 13 More actions · Answer 4

SELECT [StartTime]

,[EndTime]

,[Active Time]

,[Pump Type]

,[emissiondate]

FROM [test2]

GO

SAMPLE DATA

Start Time EndTime Active time Pump Type Emissiondate

2009-02-25 10:02:00.0002009-02-25 10:25:00.000 52630 WD 2009-02-19 14:35:00.000

Expected results

Start Time EndTime Active time Pump Type Emissiondate

2009-02-25 10:02:00.0002009-02-25 10:25:00.000 52630 WD 2009-02-19 14:35:00.000

*the sample data continues like above for around 10000 lines..most taken up be

NEW COLUMN= on/off

in my scenario, the new column would tell me if the Pump was "on" or "off"

since the sample data is so large, I import it using the wizard. I am not trying to be difficult, I just don't know the sql language enough to write the query, and am having problems getting my results.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

In the article which Gail referred you to earlier, this is described as "The Wrong Way to Post Data". Please read the article, it will only take three or four minutes of your time. Follow the simple steps to give us scripts for creating your tables and populating them with your data. With tables and data ready to go, your problem will be solved in no time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden