New Query aggregate, count but w/ date calculation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New Query aggregate, count but w/ date calculation

Author

Topic

Oatmeal
Starting Member

3 Posts

Posted - 2012-02-19 : 14:25:22

Hi. Below is a list of the 3 tables.

Table1-Location = LocationID
Table2-CableBoxes = CableBoxID, LocationID, StartDate, EndDate
Table3-Events = CableBoxID, EventID

Table1 is a location table for all locationID. Each location can have only ONE active cablebox but can have a history of other cables boxes that have been used and replaced.. Table2 is a table that contains all information about CableBoxes, which can be current or previous cablebox that is/was tied to the LocationID. It is a 2nd level dimension table which includes a history of any previous cables boxes that tie to a locationID. There is a StartDate and EndDate for all cablebox. Enddate = '12-31-9999' means it is the current cablebox. Other cable boxes with different enddate means it was replaced because of fault.

I am trying to find how many CableBoxID have been in each location (lcoationID). I also want to include how many days the previous non-active cables boxes stayed at each location. I want my results to be anything that is greater than 1 for cable boxes in a locaiton. Basically how many cablebox have been send to the location in total but also group by duration(time/days) the results to how long the 'previous' cable boxes stayed at the location. Essentially I want the data so I can analyize for patterns.

My in progress statement:

SELECT L.LocationID, COUNT(C.CableBoxID)
FROM Location L INNER JOIN
CableBoxes C
ON L.locationID = C.locationID
INNER JOIN Events E
ON C.CableBoxID = E.CableboxID
WHERE C.CableBoxID > 1
GROUP BY L.LocationID

THe issue now is how can I calculated the number of days the previous cable boxes stayed at each location and group them by the each cablebox. I am not sure where to start with adding this criteria. This is where I need help.

thx

Oatmeal
Starting Member

3 Posts

Posted - 2012-02-19 : 16:28:46

LocationTable

PK (Surrogate Key), LocationID
12345, 0000001
12312, 0000002
12329, 0000003

** LocationID could technically be the PK but it is not.

CableBoxTable
ABC1 (Suggogate Key), CableBoxID, LocationID, StartDate, EndDate
1z234, 123A2, 0000001, 01-01-2007, 06-01-2007
1sw23, 132G1, 0000001, 06-02-2007, 10-01-2007
21aq1, 1awh1, 0000001, 10-02-2007, 12-31-9999
3123a, 123fr, 0000002, 01-01-2007, 03-01-2008
321as, 321j3, 0000002, 03-02-2008, 12-31-9999
18812, 1ss23, 0000003, 02-15-2007, 12-31-9999

** As you can see Location 0000001 have a history with a total 3 cable boxes or prior history of 2 defective boxes, 0000002 has 1 defective box and 0000003 have zero. All 3 currently have a working Cablebox hence EndDate of 12-31-9999

EVentTable

EVentLogID (Surrogate Key), EventID, Event Name, CableboxID

ASDF, 001, Overheat, 123A2
REWQ, 002, NoConnection, 132G1
VCXZ, 010, VoltageFlux, 123fg

** The Event table is huge as it has every event ever recorded. Some events does not mean the cablebox does not work but just totality of the events being sent from the cablebox.

What I require on top of the statement is a way to calculate the uptime (days) of the cablebox before they become inoperable. In the data provided above. Defective Cablebox: 123A2 had an uptime of 150 days or 5 (months) x 30 days(days in month) (I am rounding off here). Defective CableBox: 132G1 has 119 days OR 4 months - 1 day and both were in location 0000001.

I am not sure how I would write the statement that would use math function to figure out the 'uptime' of old boxes.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-02-19 : 21:42:49

With that data, what is the desired output?

Oatmeal
Starting Member

3 Posts

Posted - 2012-02-20 : 22:06:52

Output something like this:

LocationID, CableBoxID, uptime_days
0000001, 132G1, 119
0000001, 123A2, 150
0000002, 123fr, 425

Basically, LocationID grouped with associated cablebox and the # of days on previous cables boxes that were at the same location. Ignoring the current cable box. In essence showing the history of replaced cable boxes and uptime(days) grouped to locationID.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources