I see where you are going...

I've got a reasonably efficient query that gives me a count of the top 20 daily values in my database. Now I'd like to figure out the daily total of top 20 values. Then analyze this information to work out the average, min, max and standard deviation of the daily total of top 20 values.

My best effort is horribly slow - does anyone have a better idea how to do this?

Thanks!

The schema of the database it accesses:

create view eventView (timeStr, msec, host, process, dbName, point,
description, rtu, groupName, message, type,
sevInt, time)
as
select dateadd(second,time+60*offset,'01/01/70'), msec, host, process,
dbName, ptName, description, rtuName, groupName,
message, type, sevInt, time
from summary

CREATE TABLE [summary] (
[msrepl_tran_version] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_Summary_GUID] DEFAULT (newid()),
[time] [GMTtime] NOT NULL ,
[msec] [int] NULL ,
[offset] [GMToffset] NOT NULL ,
[type] [nameType] NULL ,
[host] [nameType] NULL ,
[process] [nameType] NULL ,
[dbName] [nameType] NULL ,
[ptName] [nameType] NULL ,
[description] [descType] NULL ,
[rtuName] [nameType] NULL ,
[groupName] [nameType] NULL ,
[message] [msgType] NOT NULL ,
[fgInt] [tinyint] NULL ,
[sevInt] [tinyint] NULL ,
[key1] [int] NULL ,
[key2] [int] NULL ,
[spooler] [tinyint] NULL
) ON [PRIMARY]
GO

My Top 20 query:

SELECT TOP 20 count (*) as "Number of Alarms", [point], [description]

FROM [event].[dbo].[eventView]
WHERE ([timestr] < left(getdate(),11) and [timestr] >= left(getdate() - 1,11))
GROUP BY point, description HAVING count(*) > 1
ORDER BY "Number of Alarms" desc

And the messy, slow meta query:

declare @.myDay datetime
declare @.begDay datetime

declare @.myTable
table(Alarms int, Point varchar(250), Description varchar(250), Before datetime, After datetime)

declare @.myDaily
table(Date datetime, Alarms int)

select @.myDay = left(getdate(),11)

select @.begDay = left (convert(datetime, '12/01/2006'), 11)

while @.begDay <= @.myDay
begin

insert into @.myTable
SELECT TOP 20 count (*) as "Number of Alarms", [point], [description],@.begDay
FROM [event].[dbo].[eventView]
where ([timestr] < @.begDay and [timestr] >= dateadd(day,-1,@.begDay))
group by point, description
having count(*) > 1
order by "Number of Alarms" desc

select @.begDay = dateadd(day,1,@.begDay)

end

--

insert into @.myDaily
select After as "Date", sum(Alarms) as "Alarms"
from @.myTable
group by After
--

select count(Alarms) as "Count", avg(Alarms) as "Average", max(Alarms) as "Maximum", min(Alarms) as "Minimum", stdev (Alarms) as "Standard Deviation"
from @.myDaily

As I know you can improve performance if you make some change in these places.
1. avoid using function in your where clause.
2. create index in summary table.
3. I am not sure why you have to use dateadd(second,time+60*offset,'01/01/70'), in eventView? You can create another column which store time as your local timezone.


|||

I'm still learning SQL, so I don't understand all of your suggestions:

1. Avoid using function in where clause

Are you referring to the dateadd? How else can I limit the data to daily information?

2. create index in summary table.

Sorry, can't do that. I don't have control over the summary table - it's provided to my company by the owner of the software.

3. why you have to use dateadd(second,time+60*offset,'01/01/70'), in eventView

Because the software mfg stores data in the summary table in the format of "UTC seconds ". Once again, I cannot change the summary table.

|||If I were you, I won't use dateadd(....) in eventView. That is we still use UTC timestamp in eventView.

you can create @.begDay_UTC and @.PreviousDay_UTC and use them in this part of code in your where clause.
([timestr] < @.begDay_UTC and [timestr] >= @.PreviousDay_UTC)

If you can find out index Summary table used, that will help us find out how to improve performance.

Also, the estimated executions in SQL Server Management Studio will help us find out which part of code is the most expensive.|||

You can replace that while loop with a single query that will greatly improve the performance, here it is (I think, haven't tested but should be very close)

insert into @.myTable
SELECT TOP 20 count (*) as "Number of Alarms", [point], [description], [timestr], dateadd(day, 1, [timestr])
FROM [event].[dbo].[eventView]
where [message] not like '%NORMAL state%' and
[message] not like '%restored - normal%' and
[message] not like '%communication%restored%' and
[message] not like '%PLM - NORMAL%' and
[type] = 'alarm' and
[timestr] between @.begDay and @.myDay
group by [timestr], dateadd(day, 1, [timestr]), point, description, dbName
having count(*) > 1
order by "Number of Alarms" desc

|||

Agree this makes more sense and is easier to read. I've tested this and it does not make much difference in the performance.

I'm going to use your suggestion - much cleaner and easier to understand! Thanks...

|||

Think you are trying to grab the daily information using the GROUP BY.

This doesn't work for me because I'm trying to get a set of n days ( = 265 days on my system) daily top 20 values; this query only returns 20 values. I want 265 x 20 values.

|||

Which version of SQL Server are you using? You can simplify the WHILE loop in SQL Server 2005 using the APPLY operator. In SQL Server 2000, there is no easy way to write a single query - you have to do some sort of procedural loop which might be the fastest way. See below for an example in SQL Server 2005:

-- Top 2 order details based on quantity for each order:

select *
from Orders as o
cross apply (
select top 2 *
from "Order Details" as od
where od.OrderID = o.OrderID
order by od.Quantity
) as o2

|||

Our system runs MS SQL Server 2000. And since its the back end providing data archiving for our turnkey system, we will be using this for years to come.

When you say "procedural loop" that makes me think the BEGIN loop is the only way to do this job. Too bad 8-(

I need your migration stories!

Greetings:
I'm a lowly applications programmer (one of three IT employees total) in a
company that's about 40 personnel strong in management and 50 personnel
strong in production.
We have a shared drive that has about 81 Access databases in it (total of
1.25 Gigs of information) that have been made since Access 2.0 was available
all chucked full of custom software mostly using DAO for data access. All of
them are in Access 97 format. It's just a huge blob of interconnected
databases and my opinion is it's time for them to go!
So, I'm looking for migration stories. I know there are others out there who
have been in this situation and I'm wondering if you can comment a little
about your particular scenario. Was it cost effective in the end, was it
worth the hassle, tips & tricks, and how you finally managed to win your
management over to the idea of shilling out the dollars for a real DBMS.
Thanks!
CaseyYou may want to check out Russell Sinclair's book:
http://www.apress.com/book/bookDisplay.html?bID=74
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Casey" <clengacher@.smcky.com> wrote in message
news:e5i2W352DHA.1736@.TK2MSFTNGP09.phx.gbl...
Greetings:
I'm a lowly applications programmer (one of three IT employees total) in a
company that's about 40 personnel strong in management and 50 personnel
strong in production.
We have a shared drive that has about 81 Access databases in it (total of
1.25 Gigs of information) that have been made since Access 2.0 was available
all chucked full of custom software mostly using DAO for data access. All of
them are in Access 97 format. It's just a huge blob of interconnected
databases and my opinion is it's time for them to go!
So, I'm looking for migration stories. I know there are others out there who
have been in this situation and I'm wondering if you can comment a little
about your particular scenario. Was it cost effective in the end, was it
worth the hassle, tips & tricks, and how you finally managed to win your
management over to the idea of shilling out the dollars for a real DBMS.
Thanks!
Casey|||Management only hears the sound of money. Any changes you suggest must have
some impact on general productivity of the company or IT management costs.
Simplistically, if you can demonstrate that spending $20,000 on hardware and
$30,000 on development will increase productivity of 90 employees by 5%,
then there is a direct connection in the management's mind to the bottom
line and your suggested changes. If all you do is make snarky comments about
the in-efficiency of the system, then nobody will hear you. So you really
need to create a solid, justifiable business case for what you think is
obvious.
One approach is to break the problem into small enough chunks that
management is able to tolerate the risk. If you tell them you want to
consolidate the entire 81 databases into an integrated, seamless
productivity solution, it might scare the hell out of them and they'll shut
you down with little deliberation. But if you suggest a smaller prototype
project that takes a few of these databases and makes them more effecient,
then you have a proof-of-concept that can be applied to the whole mess. Once
you demonstrate some success, then the rest is easier to sell.
As an aside, I can't imagine any logical reason for 81 different databases
to manage an organization that size.And in Access, no less.
Oy!
Good luck, lowly applications programmer
Bob Castleman
SuccessWare Software
"Casey" <clengacher@.smcky.com> wrote in message
news:e5i2W352DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Greetings:
> I'm a lowly applications programmer (one of three IT employees total) in a
> company that's about 40 personnel strong in management and 50 personnel
> strong in production.
> We have a shared drive that has about 81 Access databases in it (total of
> 1.25 Gigs of information) that have been made since Access 2.0 was

---

available

quote:

---

> all chucked full of custom software mostly using DAO for data access. All

---

of

quote:

---

> them are in Access 97 format. It's just a huge blob of interconnected
> databases and my opinion is it's time for them to go!
> So, I'm looking for migration stories. I know there are others out there

---

who

quote:

---

> have been in this situation and I'm wondering if you can comment a little
> about your particular scenario. Was it cost effective in the end, was it
> worth the hassle, tips & tricks, and how you finally managed to win your
> management over to the idea of shilling out the dollars for a real DBMS.
> Thanks!
> Casey
>

---

I need your migration stories!

Greetings:
I'm a lowly applications programmer (one of three IT employees total) in a
company that's about 40 personnel strong in management and 50 personnel
strong in production.
We have a shared drive that has about 81 Access databases in it (total of
1.25 Gigs of information) that have been made since Access 2.0 was available
all chucked full of custom software mostly using DAO for data access. All of
them are in Access 97 format. It's just a huge blob of interconnected
databases and my opinion is it's time for them to go!
So, I'm looking for migration stories. I know there are others out there who
have been in this situation and I'm wondering if you can comment a little
about your particular scenario. Was it cost effective in the end, was it
worth the hassle, tips & tricks, and how you finally managed to win your
management over to the idea of shilling out the dollars for a real DBMS.
Thanks!
CaseyThis is a multi-part message in MIME format.
--=_NextPart_000_036F_01C3DB74.FFF6CEC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
You may want to check out Russell Sinclair's book:
http://www.apress.com/book/bookDisplay.html?bID=74
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Casey" <clengacher@.smcky.com> wrote in message
news:e5i2W352DHA.1736@.TK2MSFTNGP09.phx.gbl...
Greetings:
I'm a lowly applications programmer (one of three IT employees total) in a
company that's about 40 personnel strong in management and 50 personnel
strong in production.
We have a shared drive that has about 81 Access databases in it (total of
1.25 Gigs of information) that have been made since Access 2.0 was available
all chucked full of custom software mostly using DAO for data access. All of
them are in Access 97 format. It's just a huge blob of interconnected
databases and my opinion is it's time for them to go!
So, I'm looking for migration stories. I know there are others out there who
have been in this situation and I'm wondering if you can comment a little
about your particular scenario. Was it cost effective in the end, was it
worth the hassle, tips & tricks, and how you finally managed to win your
management over to the idea of shilling out the dollars for a real DBMS.
Thanks!
Casey
--=_NextPart_000_036F_01C3DB74.FFF6CEC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You may want to check out Russell =Sinclair's book:
http://www.apress.com/book/bookDisplay.html?bID=3D74">http://www.=apress.com/book/bookDisplay.html?bID=3D74
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Casey" wrote =in message news:e5i2W352DHA.1736=@.TK2MSFTNGP09.phx.gbl...Greetings:I'm a lowly applications programmer (one of three IT employees total) in acompany that's about 40 personnel strong in management and 50 personnelstrong in production.We have a shared drive that =has about 81 Access databases in it (total of1.25 Gigs of information) that =have been made since Access 2.0 was availableall chucked full of custom =software mostly using DAO for data access. All ofthem are in Access 97 =format. It's just a huge blob of interconnecteddatabases and my opinion is it's =time for them to go!So, I'm looking for migration stories. I know there =are others out there whohave been in this situation and I'm wondering if =you can comment a littleabout your particular scenario. Was it cost =effective in the end, was itworth the hassle, tips & tricks, and how you finally =managed to win yourmanagement over to the idea of shilling out the dollars =for a real DBMS.Thanks!Casey

---

--=_NextPart_000_036F_01C3DB74.FFF6CEC0--|||Management only hears the sound of money. Any changes you suggest must have
some impact on general productivity of the company or IT management costs.
Simplistically, if you can demonstrate that spending $20,000 on hardware and
$30,000 on development will increase productivity of 90 employees by 5%,
then there is a direct connection in the management's mind to the bottom
line and your suggested changes. If all you do is make snarky comments about
the in-efficiency of the system, then nobody will hear you. So you really
need to create a solid, justifiable business case for what you think is
obvious.
One approach is to break the problem into small enough chunks that
management is able to tolerate the risk. If you tell them you want to
consolidate the entire 81 databases into an integrated, seamless
productivity solution, it might scare the hell out of them and they'll shut
you down with little deliberation. But if you suggest a smaller prototype
project that takes a few of these databases and makes them more effecient,
then you have a proof-of-concept that can be applied to the whole mess. Once
you demonstrate some success, then the rest is easier to sell.
As an aside, I can't imagine any logical reason for 81 different databases
to manage an organization that size.And in Access, no less.
Oy!
Good luck, lowly applications programmer :)
Bob Castleman
SuccessWare Software
"Casey" <clengacher@.smcky.com> wrote in message
news:e5i2W352DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Greetings:
> I'm a lowly applications programmer (one of three IT employees total) in a
> company that's about 40 personnel strong in management and 50 personnel
> strong in production.
> We have a shared drive that has about 81 Access databases in it (total of
> 1.25 Gigs of information) that have been made since Access 2.0 was
available
> all chucked full of custom software mostly using DAO for data access. All
of
> them are in Access 97 format. It's just a huge blob of interconnected
> databases and my opinion is it's time for them to go!
> So, I'm looking for migration stories. I know there are others out there
who
> have been in this situation and I'm wondering if you can comment a little
> about your particular scenario. Was it cost effective in the end, was it
> worth the hassle, tips & tricks, and how you finally managed to win your
> management over to the idea of shilling out the dollars for a real DBMS.
> Thanks!
> Casey
>

I need to find total for rows with a certain value: how?

I have a table that contains a column that collects discreet data (1 for on
and 0 for off, just those two values) and a date column which is a timestamp
containing a date and time. There are other columns in this table but they
are unimportant in this discussion.
A process inserts rows into this table about every second or so, depending
on the state of an object.
It has been requested of me that I prepare a report. Among other items,
this report must show the total time an object is turned on. In other
words, for every row that is set to 1, I need to add up the time and let the
users know how long that item was set to on for a given period of time, say
24 hours i.e. how long was that object in an on state during the last 24
hours.
I consider myself to be pretty handy at SQL but I must confess I'm at a loss
to come up with a query for this.
Any ideas and examples would be greatly appreciated?
Thanks in advance,
Chris SmithOh I neglected one important piece...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||jeez, typo... And this produces total Millliseconds, Change the ms inside
the datediff function to whatever you want it to produce if milliseconds is
not what you want:
sec, minute, hour, day, etc...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTimeMs
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LogDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||I assume that the table also contains an ObjecID - to identofy WHICH Object
is being Tuened On Or OFF...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of JOIN to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the immediate NEXT
time it entered the OFF state. Likewise, if it is the end time that is
stored, I need to find the immediate PREVIOUS time it left the OFF
state.
Another thing you will have to consider, though this has to do with
data integrity, how do u ensure no overlaps occur in the records (such
that your data says at a given point in time, the object was both ON
and OFF)? Do you allow time gaps in which you can't tell whether the
object was ON or OFF? if you don't how do u ensure there are no gaps?
If you do, how do you interpret the time where there are no records?
Please clarify...|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of join to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the NEXT time it
entered the OFF state. Likewise, if it is the end time that is stored,
I need to find the PREVIOUS time it left the OFF state.
Another thing you will have to consider, though this has to do with
integrity, how do u ensure no overlaps occur in the records? Do you
allow time gaps in which you can't tell whether the object was ON or
OFF? if you don't how do u ensure there are no gaps? If you do, how do
you interpret the time where there are no records?
Please clarify...|||Hi Sienko,
I'll try to answer your questions as best as I can.
First of all, it should be noted this isn't a typical database. This is
actually a database accessed through MS SQL 2000 called InSQL. InSQL allows
you to store real time data more efficiently than SQL Server while allowing
for more transactions per second and storing the data in a manner that
allows for smaller files on the harddrive than what SQL Server itself would
allow. InSQL itself is actually made up of extension tables to SQL Server
2000. Along with this, InSQL provides other things such as providing a
discreete data type which allows you to store values that tells you if a
device is on or off where 1=on/open and 0=off/close. This probably isn't
important for you to know but it might be. For the most part, T-SQL is
still valid so any solution you can help me find should still work.
A typical row will include a column for a tagname, a discreete value column
(contains a value of 1 or 0, that is all as far as I know of), and a
timestamp which is just a datetime column (this column is equal to the time
the discreete value was retrieved from a device, not the time the row was
created). There are other columns but they aren't important to this
discussion as far as I know of. An example row might look like:
tagname_here -- discreete value -- datetime
There are rows inserted for each tag about every second or so. We'll say a
row is inserted each second to keep this simple. The rows will be the same,
just the fact the discreete value is different along with the time. For
example, a valve maybe open at the time a sensor takes a reading so this
data is retrieved (the state of the valve along with the datetime the
reading was taken) and inserted into a table (discreetehistory is the name
of the table I think, we'll use that for the sake of this conversation). As
long as the valve is open, a row will be inserted into this table where that
row will have a 1 in the discreete column and the datetime that value was
taken. When the valve is closed, rows will be inserted into the table where
the discreete value is now 0 and it has a datetime with it. Again, as long
as the valve is closed, rows will be inserted into the table with a
discreete value of 0 along with it's datetime. In other words, we have a
process that is looking at equipment 24/7/365. This process determines the
state of devices and inserts rows into a table every second or so.
Everytime a row is written, that row represents the state of the device and
a datetime is stored in that row to let us know when the sensor reading was
taken.
I don't think the table will allow null values nor do I believe a null value
will ever occur but am not certain. There is a transition period between
opening and closing valves. After all, a valve doesn't instantly open or
close. Depending on the size of size valve, it might take 1 to 5 seconds to
switch states. But, I don't believe this transition state is being
recorded. I wish I was at work answering this. I could tell you for sure
then.
Again, the problem is I need a query where I can report how long a valve was
left open in the last two hours. In other words, for a time from 12:00PM to
2:00 PM, the valve might be open at first but close at 12:30. Then, the
valve might open at 12:40. Then, the valve might be close at 1:00PM and
then open again at 1:10 PM. In other words, the valve was opened and
closeded multiple times during this two hour block. In total, the valve was
open 1hr and 40 minutes during this two hour block with the valve being
closed for 20 minutes. I need a query that will read this table and
everytime the valve is open which is represented with a discreete value of 1
(where 1 = open), then it will sum up all the times and report this to me.
I thought I was pretty good with T-SQL but I've never done anything like
this before when it comes to time. I must admit I don't have the slightest
idea how to proceed with this one.
Any thoughts would be greatly appreciated. If you need more information,
please let me know.
Thanks for trying to help me out. I apologize for taking a while to respond
to you but I was busy and simply forgot about this.
Thanks again!
Chris Smith
"sienko" <sienko@.gmail.com> wrote in message
news:1112174062.024672.131620@.g14g2000cwa.googlegroups.com...
> It might be helpful for you to provide sample data, especially about
> this timestamp field. When a row is inserted with the on/off flag set
> to ON, what value is in the timestamp column? The way I see it, there
> are 3 options: -
> 1. The time the object entered the ON state only
> 2. The time the object left the ON state only
> 3. Both the time the objected entered the ON state + the time it left
> or the duration it was in this state.
> The 3rd option is easy because all you need to do is sum the duration
> (or calculate it from the Left - Entered Time and sum) for each state
> in a given time period.
> The 1st and 2nd options are a bit tough since you have to form a sort
> of JOIN to find the related state from which the object toggled from or
> to. e.g. if I store the time the object entered the ON state, to find
> out how long it stayed in this state, I need to find the immediate NEXT
> time it entered the OFF state. Likewise, if it is the end time that is
> stored, I need to find the immediate PREVIOUS time it left the OFF
> state.
> Another thing you will have to consider, though this has to do with
> data integrity, how do u ensure no overlaps occur in the records (such
> that your data says at a given point in time, the object was both ON
> and OFF)? Do you allow time gaps in which you can't tell whether the
> object was ON or OFF? if you don't how do u ensure there are no gaps?
> If you do, how do you interpret the time where there are no records?
> Please clarify...
>|||You usually model time as durations, so you would have
CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
. );|||On Sat, 2 Apr 2005 12:49:01 -0500, someone wrote:
-- Crosspost to non-existant group removed --
(snip description)
>I thought I was pretty good with T-SQL but I've never done anything like
>this before when it comes to time. I must admit I don't have the slightest
>idea how to proceed with this one.
>Any thoughts would be greatly appreciated. If you need more information,
>please let me know.
Hi Chris,
I think the best way to approach this problem is to start with a view to
reduce the load of imported data to just the relevant things: periods
without status change, with beginning and ending datetime.
CREATE VIEW Periods
AS
SELECT start.Status,
start.TheDatetime AS StartDT,
MAX(sameperiod.TheDatetime) AS EndDT
FROM RawData AS start
INNER JOIN RawData AS sameperiod
ON sameperiod.Status = start.Status
AND sameperiod.TheDatetime >= start.TheDatetime
AND NOT EXISTS
(SELECT *
FROM RawDate AS beetween -- deliberate misspelling: reserved word
WHERE beetween.TheDatetime > start.TheDatetime
AND beetween.TheDatetime < sameperiod.TheDatetime
AND beetween.Status <> start.Status)
WHERE NOT EXISTS
(SELECT *
FROM RawDate AS previous
WHERE previous.TheDatetime =
(SELECT MAX(TheDatetime)
FROM RawData
WHERE TheDatetime < start.TheDatetime)
AND previous.Status = start.Status)
GROUP BY start.Status, start.TheDatetime
With this query, your report becomes easy:
DECLARE @.StartReport smalldatetime
DECLARE @.EndReport smalldatetime
SET @.StartReport = '2005-04-02T12:00:00'
SET @.EndReport = '2005-04-02T14:00:00'
SELECT SUM(DATEDIFF(minute,
CASE WHEN StartDT < @.StartReport
THEN @.StartReport ELSE StartDT END,
CASE WHEN EndDT < @.EndReport
THEN @.EndReport ELSE EndDT END))
FROM Periods
WHERE StartDT < @.EndReport
END EndDT > @.BeginReport
I was not able to test the query and view above, since you have not
posted the CREATE TABLE and INSERT statements needed to create a test
database on my server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The thing is I'm not creating a table with multiple columns for dates. A
table is already present with one datecolumn only. I need to find all rows
within a timespan that have a value equal to 1 in the discreete value
column. Once I find all the rows, I need to add them all somehow such that
it will tell me the total time all the rows were set to a 1 in this column.
Any ideas on that?
Thanks!
Chris Smith
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112464694.412138.243320@.o13g2000cwo.googlegroups.com...
> You usually model time as durations, so you would have
> CREATE TABLE Events
> (event_id CHAR(10) NOT NULL,
> start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_time DATETIME, -- null means current
> .. );
>

I need some information regarding Windows Mobile5.0

i am using windows mobile 5.0 .Now iam downloading the total database from remote system using synchronization.but it is a huge database.so i want to download only some part of the databse.i.e in employee table somany employees will be there.from there i want to download only one employees data from that database.

Can we do this using synchronization concept.Here i want to use XML schema.That XML file will be downloaded into the PDA and will be converted to SQLCE.And while uploading the XML file will be uploaded and the data will be stored into the SQLSERVER of the remote system.

Can anyone give me guidance in this issue sothat iam very greatful to them.

If you are using merge replication and you are pulling the entire publication to the mobile device, you might want to simply put some filters in place. you can establish horizontal or vertical filters on your publication so that only certain rows or columns in the publication propagate to the SQL CE or SQL Mobile database.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replperf_5kx1.asp

If you want to use XML instead, you might review the 4 new Northwind sample apps such as

http://msdn.microsoft.com/library/en-us/dnppcgen/html/nw_pocket_analyze_decision_support_wm2k3_sp.asp?frame=true&_r=1

many of which use XML as a mechanism of data synchronization with SQL CE on device.

-Darren

I need help top N report

I have created a report this is the Sql statement I need to be able to do a Top N 20 by payer.Pay_Company for total Invoice_ar amount can someone help me with this?

SELECT COUNT(JOB.JOBID) AS 'transcount', COUNT(DISTINCT JOB.PATIENTID) AS 'patient count', SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost', AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc', SUM(JOB.TRANSPORTATION_TCOST)
+ SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID)
AS 'PatAvgT', SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE AS Expr1, INVOICE_AR.AMOUNT_DUE
FROM JOB INNER JOIN
INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN
PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN
STATES ON JOB.JURISDICTION = STATES.INITIALS
WHERE (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate AND @.enddate)
GROUP BY JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE
ORDER BY 'tcost' DESC

So whats the question?|||The question is I need to know how to take that t sql script and add what ever I need to be able to give me the top 20 payers by largest dollars within the defined startdate and enddate|||

SELECT TOP 20COUNT(JOB.JOBID)AS'transcount',COUNT(DISTINCT JOB.PATIENTID)AS'patient count',SUM(JOB.TRANSPORTATION_TCOST)AS'tcost',SUM(JOB.TRANSPORTATION_DISC_COST)AS'dtcost',AVG(JOB.TRANSPORTATION_DISC)AS'avgTDisc',SUM(JOB.TRANSPORTATION_TCOST) +SUM(JOB.TRANSPORTATION_DISC_COST)AS'TGrossAmtBilled',SUM(JOB.TRANSPORTATION_TCOST) /COUNT(DISTINCT JOB.PATIENTID)AS'PatAvgT',SUM(JOB.TRANSPORTATION_DISC)AS'avgPercentDiscT',SUM(JOB.TRANSPORTATION_TCOST) /COUNT(JOB.JOBID)AS'RefAvgT', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATEAS Expr1, INVOICE_AR.AMOUNT_DUEFROM JOBINNERJOIN INVOICE_ARON JOB.JOBID = INVOICE_AR.JOBIDLEFTOUTER JOIN PAYERON PAYER.PAYERID = JOB.PAYERIDLEFTOUTER JOIN STATESON JOB.JURISDICTION = STATES.INITIALSWHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE_AR.INVOICE_DATEBETWEEN @.startdateAND @.enddate)GROUP BY JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUEORDER BY'tcost'DESC