Get total length of pipe per date interval

Question

I have a table containing pipes that are installed at a location. Installed dates are always filled in, removed dates can be NULL

Type   Installed     Removed     Length
PT2    01/01/2011    NULL        2000
PT2    01/01/2011    NULL        2000
PT1    01/01/2011    NULL        1200
PT1    01/01/2011    NULL        1200
PT1    15/02/2011    25/02/2011  1000
PT1    15/02/2011    25/02/2011  1000

Now I need an overview of the total length per type that was installed for a given month, so the result should be for example from 01/02/2011 to 28/02/2011:

 Type    From         To            Length
 PT2     01/02/2011   28/02/2011    4000
 PT1     01/02/2011   14/02/2011    2400 
 PT1     15/02/2011   24/02/2011    4400 Edit: (starts on 15 not 14)
 PT1     25/02/2011   28/02/2011    2400

EDIT: Clarification on this expected result. In the end this will be used to see the total length of pipe at any given moment during the requested month. So if you look at the table above, during February 2 PT2 pipes were installed. They were installed in January, but are still present, so during the entire month there is a total length of 4000.
Same for PT1: From the start of February 2 PT1 pipes are installed, so 2400. However on the 15th an extra 2 PT1 pipes of length 1000 are installed so during the 15th to the 25th the total length of PT1 pipes is 4400.
These 2 pipes are removed on the 25th so 25 to end of month it's 2400 again.
I hope this makes a little more sense now.

I'm struggling with how to do this in SQL, It's for a report, and generally there are hundreds of these pipes installed in any given month.

It's for use in a Powerbuilder application, so if you know any datawindow trickery that might help feel free to share.

Do you mean that in February (your dates are in DDMMYYY format) the length for PT1 should be a negative number, so that if you were to run the report for the first quarter of the year, the 2000 feet removed in February would be subtracted from the 2400 feet of PT1 installed in January, for a net installed length of 400 feet for PT1 over the quarter? — Tim, Apr 12 '11 at 11:24
Sorry about not being able to remove this -- StackOverflow does not give you much time to think as you type, or time to get interrupted by a phone call, before it closes off the ability to change/remove comments. — Tim, Apr 12 '11 at 11:39

score 1 · Accepted Answer · answered Apr 13 '11 at 03:44

Having too much fun with this! I've taken a few liberties, like assuming a sort order, and changing your query criteria to a date range instead of declaring a month.

To start with, I changed the data set from date ranges to dates of changes and quantity of changes (positive values on installation date, negative values on removal date).

SELECT type,
    installed as date_of_change,
    length change_of_length 
FROM pipes 
WHERE (installed BETWEEN :date_start AND
         :date_end) OR
     ((installed < :date_start) AND
         (isnull (removed, :date_end) >= :date_start)) 
UNION  ALL 
SELECT type,
    isnull (removed, dateadd (day, 1, :date_end)),
    (length * -1) 
FROM pipes 
WHERE (installed BETWEEN :date_start AND
         :date_end) OR
     ((installed < :date_start) AND
         (isnull (removed, :date_end) >= :date_start)) 
ORDER BY type,
    date_of_change

And, yes, those colons in front of the arguments (I've switched you to start and end dates... easier for me and now you can do six month reports) means I'm leveraging a DataWindow. (I'm betting someone can create a pure SQL approach with the above concept change, but I'm going with what I know.)

Load the SQL into a DataWindow (I used freeform), and optionally set a client-side sort by type and date_of_change (belt and suspenders). The data set includes null Removed dates as a change the day after the end of your query range, so create a filter to exclude those nulls:

 date_of_change <=  date_end

Create a group based on type, and put type into the group header.

In the detail band (where all subsequent controls are going), create a compute called date_from with the following expression:

if (date_of_change < date_start, date_start, date_of_change)

Create a compute called date_to with the following expression:

if (type = type[1] and getrow() < rowcount() and date_of_change[1] <= date_end, 
RelativeDate(date_of_change[1], -1),  date_end )

Create a compute called installed_length with the following expression:

cumulativesum ( change_of_length FOR GROUP 1)

Select all the controls in your detail band and give them a Visible expression of:

if (date_of_change =  date_of_change [1] and  type = type[1], 0, 1)

which will make them invisible if the next row has the same date_of_change and type; you only want the last row to show with the sum of all of today's activities.

Drag the detail band to height of zero, and make the detail band autosize height.

That will give you a version of what you're after. FWIW, I tried to make the report sort on date (not clear if that's what you wanted, or sorted first on type), but it breaks the CumulativeSum() functionality. Maybe someone else can figure it out.

Good luck,

Terry.

P.S. If SO lets me put this much in, here's the export of my prototype. It may or may not be useful to you.

release 11.5;
datawindow(units=0 timer_interval=0 color=1073741824 brushmode=0 transparency=0 gradient.angle=0 gradient.color=8421504 gradient.focus=0 gradient.repetition.count=0 gradient.repetition.length=100 gradient.repetition.mode=0 gradient.scale=100 gradient.spread=100 gradient.transparency=0 picture.blur=0 picture.clip.bottom=0 picture.clip.left=0 picture.clip.right=0 picture.clip.top=0 picture.mode=0 picture.scale.x=100 picture.scale.y=100 picture.transparency=0 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes print.background=no print.preview.background=no print.preview.outline=yes hidegrayline=no showbackcoloronxp=no picture.file="" )
header(height=72 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )
summary(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )
footer(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )
detail(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" height.autosize=yes )
table(column=(type=char(4) updatewhereclause=yes name=type dbname="pipes.type" )
 column=(type=datetime updatewhereclause=yes name=date_of_change dbname="pipes.date_of_change" )
 column=(type=long updatewhereclause=yes name=change_of_length dbname="pipes.change_of_length" )
 retrieve="SELECT type, installed as date_of_change, length change_of_length
from pipes
where (installed BETWEEN :date_start AND :date_end) OR 
    ((installed < :date_start) AND (isnull (removed, :date_end) >= :date_start))
union all
select type, isnull (removed, dateadd (day, 1, :date_end)), (length * -1)
from pipes
where (installed BETWEEN :date_start AND :date_end) OR 
    ((installed < :date_start) AND (isnull (removed, :date_end) >= :date_start))
order by date_of_change, type" filter=" date_of_change <=  date_end "arguments=(("date_start", date),("date_end", date))  sort="type A date_of_change A " )
group(level=1 header.height=76 trailer.height=0 by=("type" ) header.color="536870912" header.transparency="0" header.gradient.color="8421504" header.gradient.transparency="0" header.gradient.angle="0" header.brushmode="0" header.gradient.repetition.mode="0" header.gradient.repetition.count="0" header.gradient.repetition.length="100" header.gradient.focus="0" header.gradient.scale="100" header.gradient.spread="100" trailer.color="536870912" trailer.transparency="0" trailer.gradient.color="8421504" trailer.gradient.transparency="0" trailer.gradient.angle="0" trailer.brushmode="0" trailer.gradient.repetition.mode="0" trailer.gradient.repetition.count="0" trailer.gradient.repetition.length="100" trailer.gradient.focus="0" trailer.gradient.scale="100" trailer.gradient.spread="100" )
text(band=header alignment="2" text="Type" border="0" color="33554432" x="5" y="4" height="64" width="224" html.valueishtml="0"  name=type_t visible="1"  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
text(band=header alignment="2" text="From" border="0" color="33554432" x="334" y="4" height="64" width="137" html.valueishtml="0"  name=t_1 visible="1"  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
text(band=header alignment="2" text="To" border="0" color="33554432" x="814" y="0" height="64" width="96" html.valueishtml="0"  name=t_2 visible="1"  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
column(band=header.1 id=1 alignment="0" tabsequence=32766 border="0" color="33554432" x="9" y="0" height="64" width="224" format="[general]" html.valueishtml="0"  name=type visible="1" edit.limit=4 edit.case=any edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
compute(band=detail alignment="0" expression="if (type = type[1] and getrow() < rowcount() and date_of_change[1] <= date_end, RelativeDate(date_of_change[1], -1),  date_end )"border="0" color="33554432" x="837" y="0" height="64" width="347" format="[SHORTDATE]" html.valueishtml="0"  name=date_to visible="1~tif (date_of_change =  date_of_change [1] and  type = type[1], 0, 1)"  resizeable=1  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0"  height.autosize=yes)
compute(band=detail alignment="0" expression="cumulativesum ( change_of_length FOR GROUP 1)"border="0" color="33554432" x="1417" y="0" height="64" width="215" format="[GENERAL]" html.valueishtml="0"  name=installed_length visible="1~tif (date_of_change =  date_of_change [1] and  type = type[1], 0, 1)"  resizeable=1  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0"  height.autosize=yes)
compute(band=detail alignment="0" expression="if (date_of_change < date_start, date_start, date_of_change)"border="0" color="33554432" x="320" y="8" height="64" width="311" format="[SHORTDATE]" html.valueishtml="0"  name=date_from visible="1~tif (date_of_change =  date_of_change [1] and  type = type[1], 0, 1)"  resizeable=1  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0"  height.autosize=yes)
column(band=detail id=2 alignment="0" tabsequence=32766 border="0" color="33554432" x="1915" y="8" height="64" width="352" format="[shortdate]" html.valueishtml="0"  name=date_of_change visible="0"  resizeable=1 height.autosize=yes edit.limit=0 edit.case=any edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
column(band=detail id=3 alignment="1" tabsequence=32766 border="0" color="33554432" x="2304" y="8" height="64" width="288" format="[General]" html.valueishtml="0"  name=change_of_length visible="0"  resizeable=1 height.autosize=yes edit.limit=0 edit.case=any edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" pagingmethod=0 generatedddwframes="1" )
xhtmlgen() cssgen(sessionspecific="0" )
xmlgen(inline="0" )
xsltgen()
jsgen()
export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 )
import.xml()
export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )
export.xhtml()

Wow, thanks for the extensive answer! I had switched myself to start and enddates too using a stored procedure. I'll be sure to check this out. — klennepette, Apr 13 '11 at 08:12

Mike Sherrill 'Cat Recall' · Answer 2 · 2011-04-12T12:32:57.100

Your sample result doesn't seem to make sense. This

Type    From         To            Length
 PT2     01/02/2011   28/02/2011    4000
 PT1     01/02/2011   14/02/2011    2400 
 PT1     14/02/2011   24/02/2011    4400 
 PT1     25/02/2011   28/02/2011    2400

doesn't show how much PT1 pipe was installed for either January or February.

The following query shows the total length of pipe that was installed each month, one row per type.

select type, 
       extract(year from installed) || '-' || extract(month from installed) as year_month, 
       sum(length) 
from pipes
group by type, year_month
order by year_month, type

returns

PT1   2011-1   2400
PT2   2011-1   4000
PT1   2011-2   2000

Later . . .

For the total length of pipe on any given date, I'd probably use something like this.

select p.type, 
       '2011-02-28' as effective_date, 
       (select sum(length)
         from pipes 
         where installed <= '2011-02-28'
           and type = p.type) as installed,
       (select sum(length)
        from pipes
        where removed <= '2011-02-28'
          and type = p.type) as removed
from pipes p
group by p.type, effective_date
order by type

That query returns

type  effective_date   installed  removed
PT1   2011-02-28       4400       2000
PT2   2011-02-28       4000

Thanks for your answer, I've clarified the expected result a bit. Though some pipes are installed in January, they are still present in February. So they need to be taken into account. — klennepette, Apr 12 '11 at 11:54

Tim · Answer 3 · 2011-04-12T11:48:22.640

Do you mean that in February (your dates are in DDMMYYY format) the length for PT1 should be a negative number, so that if you were to run the report for the first quarter of the year, the 2000 feet removed in February would be subtracted from the 2400 feet of PT1 installed in January, for a net installed length of 400 feet for PT1 over the quarter? If so, you could do this as the intersection of two inline or persistent views, one for the installs one for the removals, type and (extracted) time period being the join columns, subtracting removed length per type per period from installed length per type per period to get the net installed length per type per period. A left join between installs and removals.

If you also needed to track the removal of pipe that wasn't necessarily installed by your company -- e.g. there's a record of PT77 being removed but no record of PT77 being installed, you could address this as the UNION of two inline or persistent views, one for installs, one for removals, the removals being cast to negative lengths; then you would make that UNION ALL query itself into an inline view, where you'd group by type by period and sum the length.

Thanks for the answer, I've clarified my expected result a bit. I don't want to track how many were installed in a month, I want to know the total amount of pipes present during that month. I Should have made that more clear. — klennepette, Apr 12 '11 at 11:56

Get total length of pipe per date interval

3 Answers3