February 19, 2012 at 12:48 pm
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
February 19, 2012 at 12:58 pm
Can you please provide some ready to use sample data and your expected result?
For a guideline how to do that please see the first link in my signature.
This will not only help us to better understand the scenario you're struggling with. It will usually result in a tested solution according to your specific situation.
February 19, 2012 at 2:22 pm
Apologies for not providing an example of the table. I will best describe it below.
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.
February 19, 2012 at 2:33 pm
Are you looking for a simple DATEDIFF() statement?
SELECT CableBoxID,DATEDIFF(dd,StartDate, EndDate)
FROM CableBoxTable
February 19, 2012 at 3:02 pm
I have not used that keyword before.
This is my statement so far. Essentially I need the calculation within the statement I have completed so far.
SELECT L.LocationID, COUNT(C.CableBoxID) AS CNT, C.EndDate-C.StartDate AS NUM_DAYS
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, C.EndDate-C.StartDate AS NUM_DAYS
Not sure if this is the right direction.
February 19, 2012 at 3:12 pm
February 19, 2012 at 3:20 pm
Data type is DATE
My error.. apologies. DB is oracle and I am using Oracle SQL Developer. I am not sure if DateDiff function works.
February 19, 2012 at 3:58 pm
OYeah (2/19/2012)
Data type is DATEMy error.. apologies. DB is oracle and I am using Oracle SQL Developer. I am not sure if DateDiff function works.
You will probably have better luck getting an answer on an Oracle forum since most of us around here don't use Oracle (this is a SQL Server forum). 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2012 at 4:03 pm
YEs I will thanks.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply