Using SQL Server 2005 Standard & SQL Server Reporting Services.
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.
AMB
|||OK, I'm a newbie at all of this so I will try and give you what you asked for:
In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.
Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.
Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes
Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)
Expected result Data for Query ran with Interval Parameter set to 30 Mins
Expected Result Set for Query Ran with Interval Parameter set to 60 Mins
Hope this helps explain it a little more, if not let me know how I can clarify.
Thanks for your time.
|||This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.
Code Snippet
CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime
)
GO
INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO
I will see if I can come up with a procedure to show you how to get what you want.
|||Try:
Code Snippet
createtable #t (
RecordID intnotnulluniqueclustered,
Conv1 smallintnotnull,
Conv2 smallintnotnull,
Conv3 smallintnotnull,
TimeStampVal datetimenotnull
)
go
setnocounton
insertinto #t values(1, 2, 2, 2,'2007-06-29 07:00:00.000')
insertinto #t values(2, 2, 2, 2,'2007-06-29 07:15:00.000')
insertinto #t values(3, 2, 2, 2,'2007-06-29 07:30:00.000')
insertinto #t values(4, 2, 2, 2,'2007-06-29 07:45:00.000')
insertinto #t values(5, 2, 2, 2,'2007-06-29 08:00:00.000')
insertinto #t values(6, 2, 2, 2,'2007-06-29 08:15:00.000')
insertinto #t values(7, 2, 2, 2,'2007-06-29 08:30:00.000')
insertinto #t values(8, 2, 2, 2,'2007-06-29 08:45:00.000')
insertinto #t values(9, 2, 2, 2,'2007-06-29 09:00:00.000')
insertinto #t values(10, 2, 2, 2,'2007-06-29 09:15:00.000')
insertinto #t values(11, 2, 2, 2,'2007-06-29 09:30:00.000')
insertinto #t values(12, 2, 2, 2,'2007-06-29 09:45:00.000')
setnocountoff
go
declare @.sd datetime
declare @.interval int
set @.sd = '2007-06-29 07:00:00.000'
set @.interval = 30
;with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal)/ @.interval as grp
from
#t
)
select
sum(Conv1)as Conv1,
sum(Conv2)as Conv2,
sum(Conv3)as Conv3,
min(TimeStampVal)as TimeStampVal
from
cte_1
group by
grp
order by
min(TimeStampVal)
go
droptable #t
go
AMB
|||Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).
I do have a quick question for clarification though if you would indulge me:
In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.
Code Snippet
with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal) / @.interval as grp
from
#t
)
I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.
Thanks again
Colin
|||Hi Colin,
Glad it helped.
> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view
> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.
select 1 / 2, 1 / 2.
go
I will suggest, if you want to learn more T-SQL, to get the books:
- Inside SQL server 2005: T-SQL Querying
- Inside SQL server 2005: T-SQL Programming
you will not regret having those books.
AMB
No comments:
Post a Comment