SQL Query 1

[vza]

Question:

I am creating an Java Application which takes in a text file with Wireless Devices and Locations these Devices are supposed to be in, and compare it to device history data stored within within a SQL database.

A feature of the Application is to compute the total percentage of time the Device was located within the proper location ((which is # of times device is in proper location/total # of times device is in a location) * 100) over a certain time duration which is inserted by the user.

One table has a macaddress field and a ID field.
The other table has a historyID field (foreign key to first table) a starttime field, endtime field, and a location field.

How can I query the Database to get an accurate count of how many times the device appears in the proper Location WITHIN the user-inserted time duration? I need to account for the starttime and endtime fields, which are in the DATETIME format and are exclusive to each history entry and can be used as boundaries for the user-inserted time. Any help would be appreciated.

Thanks
-vza

 

[amrita418]

Try something like this:

Declare     @inputtime datetime
set         @inputtime = '07/25/2004'
select      a.id, 
            (sum(case when b.macaddress = a.location then 1 else 0) / (count(*) * 1.0) * 100)
from        HistoryID a
            inner join OtherTable B
            on (a.id = b.id)
where       @inputdate between a.starttime and a.endtime
group by    a.id

Regards,
AA

 

[vza]

I will try this out...
So if I had the user enter a start time and and end time (through the Application) in two variables
how could i incorporate that into the query so it count all entries within the user entered time frame (comparing with the two datetime fields?) as opposed to just one date?

Here is what I have so far:
Select lh.macaddress, (Sum(Case When ll.locationName = 'Locale1' Then 1 Else 0 End)/ (count(*) * 1.0) * 100) As Percent
From loglocations ll
Inner Join loghistories lh
On (lh.id = ll.historyId)
where ?? (date info would go here)
Group By lh.macaddress

-vza

 

[amrita418]

I would not know how to pass the values from the application to query but the query to be run should be something like

where lh.starttime >= 'start value passed' and lh.endtime <= 'end value passed'

Regards,
AA

 

[vza]

amrita,

I appreciate all your assistance with this issue!

This query works perfectly for record counts....
but I ran into another issue:

I can't really compute total percentage times for a device without taking into account the amount of time a Device is actually within a location...

is there any way I could alter the above query to account for the TOTAL TIME a device has spent in a Location divided by the TOTAL TIME the device has been in ANY location?

Any responses would be greatly appreciated.

Thanks
-vza

 

[amrita418]

Did you mean that you want to count only if the location name is some location?? i.e. do not want a count(*) in the denominator. Do you have pattern for location names?

If yes,
Try replacing the count(*) with the case stmt (using locationname like '%pattern%') below

sum(case when ll.locationname like 'loc%' then 1 else 0 end)

Regards,
AA

 

[vza]

amrita,
Maybe I should rephrase:

Here is an entry within the histories table (some fields have been excluded which are unnecessary):

Fieldname: Sample Entry:
macaddress 00:02:2D:A5:F1:E6
locationName Services
starttime 2005-07-21 11:04:34
endtime 2005-07-21 13:34:45

My application takes in a macaddress of a device and its known location. The user also inputs a start date/end date as start and end time interval to collect information.

I need a query which can find out the total amount of time this device has been in its proper location (based on the starttime, endtime and locationName fields) over the time interval inserted by the end-user.

I figured a simple count would have worked before but it doesnt account for time, it just counts the entry as a whole.

-vza

 

[amrita418]

I am not 100% clear on your requirement but try the code below. It adds up all the time where the device was in a given location vs the time diff entered by the user.

I have added day as the parameter for the datediff fucntion but you can change it to hour or min etc which ever matches your criteria.

Sum(Case When ll.locationName = 'Locale1' Then datediff(day, lh.starttime, lh.endtime) Else 0 End) / (datediff(day, startdate, enddate) * 1.0) * 100) As Percent

I do not have sample data to test the query so let me know it that helps.

Regards,
AA

 

[vza]

is there any way this line could be re-written using the unix_timestamp function?

-vza

 

[amrita418]

AFAIK, sql server does not have the unix_timestamp function.

Is your database MS Sql server? If not then try the MYSql forum.

Regards,
AA