#sql #powerbuilder #sqlanywhere
#sql #powerbuilder #sqlanywhere
Вопрос:
У меня есть таблица, содержащая pipes которые установлены в определенном месте. Установленные даты всегда заполняются, удаленные даты могут быть нулевыми
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
Теперь мне нужен обзор общей длины для каждого типа, который был установлен за данный месяц, поэтому результат должен быть, например, от 01/02/2011 до 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
РЕДАКТИРОВАТЬ: Разъяснение по этому ожидаемому результату.
В конце это будет использовано для просмотра общей длины канала в любой данный момент в течение запрошенного месяца. Итак, если вы посмотрите на таблицу выше, в течение 2 февраля были установлены PT2 каналы. Они были установлены в январе, но все еще присутствуют, поэтому в течение всего месяца общая длина составляет 4000.
То же самое для PT1 : С начала февраля установлено 2 канала PT1, то есть 2400. Однако 15-го числа устанавливаются дополнительные 2 канала PT1 длиной 1000, так что с 15-го по 25-е общая длина каналов PT1 составляет 4400.
Эти 2 канала удаляются 25-го, так что с 25 по конец месяца снова 2400.
Я надеюсь, что теперь это имеет немного больше смысла.
Я пытаюсь понять, как это сделать в SQL, это для отчета, и обычно в любой данный месяц устанавливаются сотни таких каналов.
Это для использования в приложении Powerbuilder, поэтому, если вы знаете какую-либо хитрость datawindow, которая может помочь, не стесняйтесь поделиться.
Комментарии:
1. Вы имеете в виду, что в феврале (ваши даты приведены в формате DDMMYYY) длина для PT1 должна быть отрицательным числом, так что, если бы вы запускали отчет за первый квартал года, 2000 футов, удаленные в феврале, были бы вычтены из 2400 футов PT1, установленных в январе, для чистой установленной длины в 400 футов для PT1 за квартал?
2. Извините, что не могу удалить это — StackOverflow не дает вам много времени на обдумывание при вводе текста или времени на то, чтобы быть прерванным телефонным звонком, прежде чем он закроет возможность изменять / удалять комментарии.
Ответ №1:
Слишком много удовольствия от этого! Я позволил себе несколько вольностей, например, предположил порядок сортировки и изменил критерии вашего запроса на диапазон дат вместо объявления месяца.
Для начала я изменил набор данных с диапазонов дат на даты изменений и количество изменений (положительные значения на дату установки, отрицательные значения на дату удаления).
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
И, да, эти двоеточия перед аргументами (я переключил вас на даты начала и окончания… для меня проще, и теперь вы можете создавать отчеты за шесть месяцев) означает, что я использую DataWindow. (Держу пари, кто-нибудь может создать чисто SQL-подход с вышеупомянутым изменением концепции, но я придерживаюсь того, что знаю.)
Загрузите SQL в DataWindow (я использовал произвольную форму) и, при необходимости, установите сортировку на стороне клиента по типу и date_of_change (ремень и подтяжки). Набор данных включает в себя нулевые удаленные даты в качестве изменения на следующий день после окончания диапазона вашего запроса, поэтому создайте фильтр для исключения этих нулей:
date_of_change <= date_end
Создайте группу на основе типа и поместите тип в заголовок группы.
В группе сведений (куда отправляются все последующие элементы управления) создайте вычисление с именем date_from со следующим выражением:
if (date_of_change < date_start, date_start, date_of_change)
Создайте вычисление с именем date_to со следующим выражением:
if (type = type[1] and getrow() < rowcount() and date_of_change[1] <= date_end,
RelativeDate(date_of_change[1], -1), date_end )
Создайте вычисление с именем installed_length со следующим выражением:
cumulativesum ( change_of_length FOR GROUP 1)
Выберите все элементы управления в вашей группе сведений и придайте им видимое выражение:
if (date_of_change = date_of_change [1] and type = type[1], 0, 1)
что сделает их невидимыми, если следующая строка имеет тот же date_of_change и тип; вы хотите, чтобы отображалась только последняя строка с суммой всех сегодняшних действий.
Перетащите полосу сведений на нулевую высоту и автоматически измените высоту полосы сведений.
Это даст вам версию того, что вам нужно. Черт возьми, я пытался выполнить сортировку отчета по дате (не ясно, это то, что вы хотели, или сначала отсортировать по типу), но это нарушает функциональность CumulativeSum (). Может быть, кто-то другой сможет это выяснить.
Удачи,
Терри.
P.S. Если ЭТО ТАК, позвольте мне добавить вот это, вот экспорт моего прототипа. Это может быть вам полезно, а может и не быть.
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()
Комментарии:
1. Вау, спасибо за подробный ответ! Я также переключился на начальные и конечные даты, используя хранимую процедуру. Я обязательно проверю это.
Ответ №2:
Вы имеете в виду, что в феврале (ваши даты приведены в формате DDMMYYY) длина для PT1 должна быть отрицательным числом, так что, если бы вы запускали отчет за первый квартал года, 2000 футов, удаленные в феврале, были бы вычтены из 2400 футов PT1, установленных в январе, для чистой установленной длины в 400 футов для PT1 за квартал? Если это так, вы могли бы сделать это как пересечение двух встроенных или постоянных представлений, одно для установок, другое для удалений, тип и (извлеченный) период времени являются столбцами объединения, вычитая удаленную длину для каждого типа за период из установленной длины для каждого типа за период, чтобы получить чистую установленную длину для каждого типа за период. Левое соединение между установками и удалениями.
Если вам также необходимо отслеживать удаление канала, который не обязательно был установлен вашей компанией — например, есть запись об удалении PT77, но нет записи об установке PT77, вы могли бы обратиться к этому как к ОБЪЕДИНЕНИЮ двух встроенных или постоянных представлений, одного для установок, другого для удалений, причем удаления преобразуются в отрицательные длины; затем вы бы преобразовали этот запрос UNION ALL во встроенное представление, где вы бы группировали по типу по периоду и суммировали длину.
Комментарии:
1. Спасибо за ответ, я немного прояснил свой ожидаемый результат. Я не хочу отслеживать, сколько было установлено за месяц, я хочу знать общее количество каналов, присутствующих в течение этого месяца. Я должен был сделать это более понятным.
Ответ №3:
Ваш пример результата, похоже, не имеет смысла. Это
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
не показывает, сколько каналов PT1 было установлено ни за январь, ни за февраль.
Следующий запрос показывает общую длину канала, который устанавливался каждый месяц, по одной строке для каждого типа.
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
ВОЗВРАТ
PT1 2011-1 2400
PT2 2011-1 4000
PT1 2011-2 2000
Позже . . .
Для общей длины канала на любую заданную дату я бы, вероятно, использовал что-то вроде этого.
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
Этот запрос возвращает
type effective_date installed removed
PT1 2011-02-28 4400 2000
PT2 2011-02-28 4000
Комментарии:
1. Спасибо за ваш ответ, я немного прояснил ожидаемый результат. Хотя некоторые каналы установлены в январе, они все еще присутствуют в феврале. Поэтому их необходимо учитывать.