Count based on date intervals

Question

Post reply

Count based on date intervals

Dave-3000

SSCertifiable

Points: 6186
More actions
June 3, 2004 at 11:56 am

#82976

I have a problem that I can't quite get started on solving.
I have a table of asset statuses. Each time the asset status changes, a new row is inserted into the table.
CREATE TABLE dbo.Tbl_EMStatusHistory (
EMStatusID int IDENTITY (1, 1) NOT NULL ,
EMSessionID int NULL ,
AssetID int NOT NULL ,
AssetStatus int NOT NULL ,
StatusComment varchar (250) NULL ,
StatusDate smalldatetime NOT NULL ,
InsertUser sysname NOT NULL ,
InsertDate smalldatetime NULL ,
CONSTRAINT PK_EMStatusHistory PRIMARY KEY NONCLUSTERED
(
EMStatusID
&nbsp
)
Here is some sample data:
EMStatusID AssetID AssetStatus StatusDate DeviceType
47726 22624 OUT 2003-10-05 Monitor
38100 22624 IN 2003-10-16 Monitor
47261 22624 OUT 2003-10-25 Monitor
38193 22624 IN 2003-11-02 Monitor
39171 22624 RV 2004-05-02 Monitor
For asset 22624, the current (most recent) status = RV. Before that, it last
changed status on 2003-11-02, when it was IN.
I need to produce a report that counts the devices that were IN on a given report date,
like so:
ReportDate Monitors Transmitters
05/01/2004 34 26
05/02/2004 37 30
05/03/2004 39 32
05/04/2004 35 31
The end user supplies the date range.
I am unsure how to approach this. I came up with one Einsteinian-Newtonian method that involved three temp tables, a garden hose, and a duck, and it was getting uglier by the minute. Has anyone had experience with a requirement like this? TIA, Dave
There is no "i" in team, but idiot has two.

Viewing 10 posts - 1 through 9 (of 9 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Why not create something like the following:

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SELECT DateLastTouched, Device, COUNT(Device)

FROM tblListOfStuff

WHERE DateLastTouched BETWEEN @StartDate AND @EndDate

GROUP BY DateLastTouched, Device

ORDER BY DateLastTouched, Device

I know that this will produce the list something like:

05/10/2004 Monitors 50

05/10/2004 Transmitters 100

However, This should be a start and get you the information you are looking for

Good Luck,

PS. the problem was with the Duck for some reason they don't interact well with garden hoses...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 2

First thing to notice is that the sample data could not be inserted in the table structure that you provided. For testing, I used the following:

CREATE TABLE dbo.Tbl_EMStatusHistory (
 EMStatusID int IDENTITY (1, 1) NOT NULL ,
 EMSessionID int NULL ,
 AssetID int NOT NULL ,
 AssetStatus int NOT NULL ,
 StatusComment varchar (250) NULL ,
 StatusDate smalldatetime NOT NULL ,
 InsertUser sysname NOT NULL ,
 InsertDate smalldatetime NULL ,
 CONSTRAINT PK_EMStatusHistory PRIMARY KEY  NONCLUSTERED 
 (
  EMStatusID
&nbsp
)

insert into Tbl_EMStatusHistory (AssetID, AssetStatus, StatusDate, InsertUser) 
SELECT 22, 1, '20031105', 'Me'
UNION ALL
SELECT 22, 2, '20031116', 'Me'
UNION ALL
SELECT 22, 1, '20031125', 'Me'

union all

SELECT 22624, 1, '20031005', 'Me'
UNION ALL
SELECT 22624, 2, '20031016', 'Me'
UNION ALL
SELECT 22624, 1, '20031025', 'Me'
UNION ALL
SELECT 22624, 2, '20031102', 'Me'
UNION ALL
SELECT 22624, 3, '20040502', 'Me'

Another thing is that you need a unique constraint:

ALTER TABLE Tbl_EMStatusHistory ADD UNIQUE (AssetID, StatusDate)

If you would have two status changes in the same day, you would not know which one is the last one (unless you use the time portion of StatusDate or the identity column).

In this scenario, the following query will give the status of each assed at the given date:

DECLARE @ReportDate smalldatetime
SET @ReportDate='20031117'

SELECT AssetID, AssetStatus FROM Tbl_EMStatusHistory a
WHERE StatusDate=(
 SELECT MAX(StatusDate) FROM Tbl_EMStatusHistory b
 WHERE a.AssetID=b.AssetID AND b.StatusDate <= @ReportDate

The following query will give the status of each asset in each working day in the given period:

DECLARE @StartDate smalldatetime, @EndDate smalldatetime
SET @StartDate='20031101'
SET @EndDate='20031130'

SELECT ReportDate, AssetID, AssetStatus 
FROM Tbl_EMStatusHistory a CROSS JOIN (
 SELECT DISTINCT StatusDate as ReportDate FROM Tbl_EMStatusHistory
 WHERE StatusDate BETWEEN @StartDate AND @EndDate
) r WHERE StatusDate=(
 SELECT MAX(StatusDate) FROM Tbl_EMStatusHistory b
 WHERE a.AssetID=b.AssetID AND b.StatusDate<=ReportDate
)

Razvan

David Burrows SSC Guru Points: 65138 More actions · Answer 3

DECLARE @SDate smalldatetime, @EDate smalldatetime

SET @SDate = '2004-06-01'

SET @EDate = '2004-06-05'

SELECT d.ReportDate,

SUM(CASE WHEN a.DeviceType = 'Monitor'

THEN 1 ELSE 0 END) as [Monitors],

SUM(CASE WHEN a.DeviceType = 'Transmitter'

THEN 1 ELSE 0 END) as [Transmitters]

FROM

(SELECT @SDate+number as [ReportDate]

FROM master.dbo.spt_values

WHERE type = 'P'

AND (@SDate+number) <= @EDate) d

LEFT OUTER JOIN

(SELECT h.DeviceType,h.StatusDate,

(SELECT MIN(m.StatusDate)

FROM #Tbl_EMStatusHistory m

WHERE m.StatusDate > h.StatusDate) as [NextStatusDate]

FROM #Tbl_EMStatusHistory h

WHERE h.AssetStatus = 'IN') a

ON d.ReportDate >= a.StatusDate

AND d.ReportDate <= a.NextStatusDate

GROUP BY d.ReportDate

ORDER BY d.ReportDate

Far away is close at hand in the images of elsewhere.
Anon.

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 4

Please let me know if this works

Select distinct A.StatusDate,

(select Count(Distinct B.AssetID) from dbo.Tbl_EMStatusHistory B where B.DeviceType = 'Monitor' and B.StatusDate = A.StatusDate and B.AssetStatus = 'IN') Monitors,

(select Count(Distinct B.AssetID) from dbo.Tbl_EMStatusHistory B where B.DeviceType = 'Transmitter' and B.StatusDate = A.StatusDate and B.AssetStatus = 'IN') Transmitters

from dbo.Tbl_EMStatusHistory A where A.StatusDate >=StartDate and A.dbo.Tbl_EMStatusHistory<=EndDate

You can also use left outer join to solve this out. I am trying to get it.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 5

Hi, All: Thanks for all the replies. I think I didn't explain the situation very well because most gave the solution I thought I could use, but it turned out didn't give me the result I need. Tbl_EMHistory.StatusDate is the date the Asset's status changed. I need to find the assets whose status changed within the time frame indicated, and not counts of assets whose status changed on the given date. I had posted the same question to another web site, and I got back a good solution, but it involves adding another column, StatusDateEnd. We just went into production yesterday a.m., and we always have a "cooling off" period before we can make major changes like schema changes. This is only for one report, so I have some time to figure out if it is better to change the schema, or figure out a temp table solution to implement it.

Thanks for your help. Dave

There is no "i" in team, but idiot has two.

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Ok lets break down the facts to make sure I understand.

1) These are the columns we have the most interest in.

AssestID is the item

AssestStatus is its status

StatusDate is the date that status went into effect

DeviceType is the type of item we are talking about.

2) We are only concerned for with the count for the time the AssetsStatus is "IN"

3) We have a given range we need to count for. ANd it needs to be a daily breakdown.

Let''s start with making sure the timeframe an item is "IN" begining to end??

I am assuming you cannot enter another status for the same day. If so may need to do a little tweaking.

SELECT

C1.AssetID,

C1.AssestStatus,

C1.StatusDate StatusDate1,

C2.StatusDate StatusDate2,

C1.DeviceType

FROM

dbo.Tbl_EMStatusHistory C1

LEFT JOIN

dbo.Tbl_EMStatusHistory C2

ON

C1.AssetID = C2.AssetID AND

C1.StatusDate < C2.StatusDate

Now ther wil be a problem above in that there can very well be multiple values above C1.StatusDate for the C2.StatusDate.

We need the Minimum value > than C1.StatusDate, so do this.

Change to ON to

ON

C1.AssetID = C2.AssetID AND

C2.StatusDate = (SELECT MIN(C3.StatusDate) FROM dbo.Tbl_EMStatusHistory C3 WHERE C3.AssetID = C1.AssetID AND C3.StatusDate > C1.StatusDate)

So now we can identify the begining and ending of the time "IN" you may also want to add a WHERE clause you narrow down the end frame and make this a view for reuse, if you do then you can drop the C1.AssetStatus value being output after you test.

For the example we will say we have a view created named "vw_DevicesIn".

Now I personnally would create a table with every day in it as the primary key for valid ranges just to save some memory overhead but some folks prefer to build a temp table to conserve data space.

For my example thou we''ll go with the temp table.

Generate a temp tablewith a single column DateVal that is non-null and the primary key. Then cycle thru and insert all the dates for the range needed. This table for the example is named #tbl_DateRange

Now

SELECT

D1.DateVal ReportDate,

SUM(CASE V1.DeviceType WHEN 'Monitor' THEN 1 ELSE 0 END) Monitors

SUM(CASE V1.DeviceType WHEN 'Transmitter' THEN 1 ELSE 0 END) Transmitters

FROM

#tbl_DateRange D1

INNER JOIN

dbo.vw_DevicesIn V1

ON

D1.DateVal BETWEEN V1.StatusDate1 AND DATEADD(d,-1,ISNULL(V1.StatusDate2,'99991231'))

GROUP BY

D1.DateVal

That should do it for you or like I said above may need a bit of tweaking to finish it.

David Burrows SSC Guru Points: 65138 More actions · Answer 7

Antares' solution is very similar to mine which was based on your first post and was only interested in assets that were 'IN'. Your example report was split by date.

I need to find the assets whose status changed within the time frame indicated

This statement together with your first post is ambiguous at best. You first indicated that you were only interested in assets that were in, now is it any status change?

What is the 'time frame' you mention, is it a single day, date range?

You need to clarify what 'status changed within the time frame' means. If an asset changes status 5 times in the period, how many times is it counted?

Can you post more data and exactly what the output should be based on that data.

I think I may know what you're after but need clarification to make sure.

Far away is close at hand in the images of elsewhere.
Anon.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 8

Thanks very much for your help. David, you were right the first time, I am only interested in counting devices with a status of IN on each day in the date range. That first code example you gave me was pretty much right on. I ended up using both it and Antares' example as a guide (love the narrative, Antares). The thing I was not seeing was how to get the NextStatusDate, which both of you gave me a good method to get it. I wrapped the code in a stored proc, and it works like a champ. So thanks for getting me out of the sand trap.

The next thing I have to figure out with it is that the customer told us this morning, that, by the way, a device CAN change status twice in one day. Sigh.

Thanks, Dave

There is no "i" in team, but idiot has two.

David Burrows SSC Guru Points: 65138 More actions · Answer 9

The next thing I have to figure out with it is that the customer told us this morning, that, by the way, a device CAN change status twice in one day.

Thought that would be the case. Users always do that , you get the report just right and then they say, by the way.....

What you will have to do is alter the query to DISTINCT the date/asset values before date grouping and summing.

If you post your final query maybe we can help (no promises though )

Far away is close at hand in the images of elsewhere.
Anon.