Помогите написать SQL запрос ...

← →
DelphiN! © (2006-10-17 08:04) [0]

Чего-то я немогу сообразить как запрос написать :((

Есть таблица, в которой следующие поля

COMPUTER_ SUMM_ TIMES_ TIMEPO_ TIME_

В поле TIME_ содержится время в формате dd.mm.yyyy hh:nn:ss(когда на компьютере открыли время)
В поле TIMEPO_ время окончания сеанса
В поле TIMES_ время начала сеанса
В поле SUMM_ сумма денег, отданая за сеанс
В поле Computer_ имина компьютеров, например: K1,K2,K3 ...

Мне нужно написать запрос, который выводит следующую таблицу :

DATE_ Period Computer Summ Count Busy%
01.01.2006 00:00:00-08:00:00 1 200 2 75
01.01.2006 00:00:00-08:00:00 1 100 1 30
01.01.2006 00:00:00-08:00:00 2 0 0 0
01.01.2006 08:00:01-23:59:59 1 300 3 100
01.01.2006 08:00:01-23:59:59 1 0 0 0
01.01.2006 08:00:01-23:59:59 2 100 1 30
... ...

Где DATE_ - дата,
PERIOD_ - периоды, данных значений в таблице нет, я их должен вписать в запрос вручную и сделать выборку принадлежности к данным периодам времени значений из поля TIMES_ или TIMEPO_,
Computer_ - имина компьютеров, Count - колличество сеансов(записей) по данному компьютеру за данный период и данную дату,
Busy% - процент загрузки по данному компьютеру за данный период по данному компьютеру и данной дате

Как мне написать данный запрос? И вообще можно ли обойтись одним запросом?

← →
Sergey13 © (2006-10-17 08:19) [1]

Если сделать отдельную таблицу периодов, то станет проще.

← →
ЮЮ © (2006-10-17 08:21) [2]

> В поле TIME_ содержится время в формате dd.mm.yyyy hh:nn:
> ss

Лучше типы полей приведи, чтобы можно было поверить в это

01.01.2006 00:00:00-08:00:00 1 200 2 75
01.01.2006 00:00:00-08:00:00 1 100 1 30

Почему на одну дату, на один перииод и даже для одного и того же компьютера две записи?

← →
ЮЮ © (2006-10-17 08:22) [3]

Да и тип БД не мешало бы указать

← →
DelphiN! © (2006-10-17 08:29) [4]

> [1] Sergey13 © (17.10.06 08:19)

Да, это уже сделал

> [2] ЮЮ © (17.10.06 08:21)

Вот типы полей исходной таблицы :
COMPUTER_ - VARCHAR(10) SUMM_ - FLOAT TIMES_ - DATE TIMEPO_ - DATE TIME_ - DATE

Создал еще таблицу с периодами, там такие поля :

TimeS_ TimePo_
типа DATE

В полях значения вида 01.01.06 08:00:00 01.01.06 23:59:59
При этом при выборке значений по данным периодам не должна учитываться дата, тоесть значение вида 05.06.06 13:04:27 должно отнестись к периоду 01.01.06 08:00:00 - 01.01.06 23:59:59

Тип БД FireBird 1.5

← →
Sergey13 © (2006-10-17 08:35) [5]

> [4] DelphiN! © (17.10.06 08:29)

А как быть с сеансами не влезающими в один период?
Что есть
> Busy% - процент загрузки по данному компьютеру за данный
> период по данному компьютеру и данной дате
Это отношение рабочего времени сеансов к продолжительности периода?

← →
evvcom © (2006-10-17 08:55) [6]

> [0] DelphiN! © (17.10.06 08:04)
> когда на компьютере открыли время

веселая фраза :) Ты думаешь, время на компьютере открыли? Я думаю, гораздо раньше.

> В поле Computer_ имина компьютеров, например: K1,K2,K3 ...
> Computer_ - имина компьютеров

Запомни, пишется "имена"

> например: K1,K2,K3 ...

как это согласуется с

> Computer
> 1
> 1
> 2
> ...
? И Count 2, это ты хочешь из Computer_ "K1,K2" вытащить?

> Busy% - процент загрузки по данному компьютеру за данный
> период по данному компьютеру и данной дате

А это как считаешь?

> Как мне написать данный запрос?

СУБД - то какая? А то сейчас таких советов надаем, будешь потом говорить, что то, да это не понимается "твоим компьютером"!

← →
DelphiN! © (2006-10-17 09:54) [7]

> [5] Sergey13 © (17.10.06 08:35)
> > [4] DelphiN! © (17.10.06 08:29)
>
> А как быть с сеансами не влезающими в один период?

Если радиус сеанса не "влезает" в радиус периода, но его время задевает границы радиуса периода, то нужно отнести его к этому периоду

> Что есть
> > Busy% - процент загрузки по данному компьютеру за данный
>
> > период по данному компьютеру и данной дате
> Это отношение рабочего времени сеансов к продолжительности
> периода?

Это процент того, сколько компьютер был занят в радиусе действия периода, например если период с 12-16, а на компьютере был открыт сеанс 14-18, то процент занятости этого компьютера на период был 50%(с 14ти до 16ти)

> > например: K1,K2,K3 ...
>
> как это согласуется с
>
> > Computer
> > 1
> > 1
> > 2
> > ...

Извиняюсь, описался, столбец будет выглядеть так :
Computer
K1
K1
K2
...

> И Count 2, это ты хочешь из Computer_ "K1,K2" вытащить?

Count - это колличество сеансов, которое было активно в пределах периода, например если период с 08.00-12.00, а данный компьютер был занят
08.00-08.30, 09.00-14.00, 14.00-13.00
То Count будет = 2(08.00-08.30, 09.00-14.00)

> СУБД - то какая?

> FireBird 1.5

← →
ЮЮ © (2006-10-17 10:00) [8]

> Если радиус сеанса не "влезает" в радиус периода, но его
> время задевает границы радиуса периода, то нужно отнести
> его к этому периоду

Т.е сеанс с 7.00 до 9.00 я должен учесть в обоих периодах. А 200 рублей показать дваджы?

← →
Sergey13 © (2006-10-17 10:00) [9]

> Если радиус сеанса не "влезает" в радиус периода, но его
> время задевает границы радиуса периода, то нужно отнести
> его к этому периоду

К какому периоду? Ко всем периодам? Ведь сеанс, теоретически, может и в три в пять периодов попасть.
Боюсь, что тут одним запросом не обойтись.

← →
Anatoly Podgoretsky © (2006-10-17 10:07) [10]

> А 200 рублей показать дваджы?

И внести деньги в кассу.

Желательно в чёрную...

ИМХО - автор начал не с того. Пытается рисовать запрос, не проведя анализ да еще и по кривой структуре данных.

> значение вида 05.06.06 13:04:27 должно отнестись к периоду
> 01.01.06 08:00:00 - 01.01.06 23:59:59
Вот это меня больше всего радует. Июньские данные списать на первое января того же года.
Пьяные админы с пьяными же посетителями нагуляли на полгода вперед...

> Вот это меня больше всего радует. Июньские данные списать
> на первое января того же года.
> Пьяные админы с пьяными же посетителями нагуляли на полгода
> вперед...

Не важна дата в периоде, нужно определить сеанс к конкретному периоду суток, и записать его с этим периудом

DATE_ Period Computer Summ Count Busy%
01.01.2006 00:00:00-08:00:00 1 200 2 75

Комент к данному результату: 1го января 2006го года с 00:00:00 до 08:00:00 на компьютере 1 выручка составила 200 y.e. За компьютером сидело 2 клиента, % занятости на период с 00:00:00-08:00:00 составила 75%.

Написал процедуру, которая выводит эти данные методом перебора таблицы, естественно процедура работает медленно :

procedure TDATA.SaveSourceReport(Organization: String); function GetTimeInt(Time: TDateTime): String; var i: Integer; strl: TStringList; begin try strl := TStringList.Create; Result := ""; i := 0; while (Result = "")and(i<frmSettings.lstSourceReportTimeInts.Items.Count) do begin strl.Text := DATA.ExtractWorlds(frmSettings.lstSourceReportTimeInts.Items.Strings[i],"-"); if TestTime(Time,StrToTime(strl.Strings[0]),StrToTime(strl.Strings[1])) then Result := frmSettings.lstSourceReportTimeInts.Items.Strings[i]; Inc(i); end; finally strl.Free; end; end; function GetBusyPercent(TimeS,TimePo: TDateTime; TimeInt: String): Real; var strl: TStringList; PTimeS,PTimePo: TDateTime; Pr,RR: Real; begin try Result := 0; strl := TStringList.Create; strl.Text := ExtractWorlds(TimeInt,"-"); PTimeS := StrToDateTime(FormatDateTime("01.01.2006 hh:nn:ss",StrToTime(strl.Strings[0]))); PTimePo := StrToDateTime(FormatDateTime("01.01.2006 hh:nn:ss",StrToTime(strl.Strings[1]))); TimeS := StrToDateTime(FormatDateTime("01.01.2006 hh:nn:ss",TimeS)); TimePo := StrToDateTime(FormatDateTime("01.01.2006 hh:nn:ss",TimePo)); if PTimeS > PTimePo then PTimePo := PTimePo+1; if TimeS > TimePo then TimePo := TimePo+1; //Preobrazuem do 0go otrezka TimeS := TimeS-PTimeS; TimePo := TimePo-PTimeS; PTimePo := PTimePo-PTimeS; PTimeS := 0; if TestTimeInt(TimeS,TimePo,PTimeS,PTimePo) then begin //Esli granici 2go otrezka vihodyat za predeli obrezaem ego pod osnovnoy if TimeS < PTimeS then TimeS := PTimeS; if TimePo > PTimePo then TimePo := PTimePo; //Summi otrezkov Pr := PTimePo-PTimeS; RR := TimePo-TimeS; //Scitaem procent Result := (RR*100)/Pr; end; finally strl.Free; end; end; function FindRow(Date,Period,Organization,Computer: String): Integer; var i: Integer; begin Result := -1; i := 0; while (Result = -1)and(i<Main.Grid.RowCount) do begin if DATA.CmpStr(Main.Grid.Cells[0,i],Date) then if DATA.CmpStr(Main.Grid.Cells[1,i],Period) then if DATA.CmpStr(Main.Grid.Cells[2,i],Organization) then if DATA.CmpStr(Main.Grid.Cells[3,i],Computer) then Result := i; Inc(i); end; end; var TimeS,TimePo: TDateTime; Per: String; row: Integer; add: Boolean; FileName: String; begin FileName := frmSettings.edtSaveRepsToDir.Text; if FileName[Length(FileName)] <> "\" then FileName := FileName + "\"; FileName := FileName+"SourceReport.xls"; TimeS := ini.ReadDateTime("MAIN","LastSourceRep",now-1); TimePo := now; Main.Grid.Columns.Clear; Main.Grid.Columns.Add(TTextualColumn,"Date"); //0 Main.Grid.Columns.Add(TTextualColumn,"Period"); //1 Main.Grid.Columns.Add(TTextualColumn,"Club"); //2 Main.Grid.Columns.Add(TTextualColumn,"Computer");//3 Main.Grid.Columns.Add(TTextualColumn,"Summ"); //4 Main.Grid.Columns.Add(TTextualColumn,"Seanses"); //5 Main.Grid.Columns.Add(TTextualColumn,"Busy"); //6 DATA.RCaseBase.SelectSQL.Text := "SELECT * FROM CASEBASE WHERE TIME_ >= """+DateTimeToStr(TimeS)+""" and TIME_ <= """+DateTimeToStr(TimePo)+""" ORDER BY TIME_,COMP_"; DATA.RCaseBase.Open; DATA.RCaseBase.First; while not DATA.RCaseBase.Eof do begin add := false; Per := GetTimeInt(DATA.RCaseBase.FieldByName("TIME_").AsDateTime); row := FindRow(FormatDateTime("dd.mm.yy",DATA.RCaseBase.FieldByName("TIME_").AsDateTime ),Per,Organization,DATA.RCaseBase.FieldByName("COMP_").AsString); if row = -1 then begin Main.Grid.AddRow(1); row := Main.Grid.RowCount-1; add := true; end; if add then begin Main.Grid.Cells[0,row] := FormatDateTime("dd.mm.yy",DATA.RCaseBase.FieldByName("TIME_").AsDateTime); Main.Grid.Cells[1,row] := Per; Main.Grid.Cells[2,row] := Organization; Main.Grid.Cells[3,row] := DATA.RCaseBase.FieldByName("COMP_").AsString; Main.Grid.Cells[4,row] := DATA.RCaseBase.FieldByName("SUMM_").AsString; Main.Grid.Cells[5,row] := "1"; Main.Grid.Cells[6,row] := FloatToStr(RoundTo(GetBusyPercent(DATA.RCaseBase.FieldByName("TIMES_").AsDateTim e,DATA.RCaseBase.FieldByName("TIMEPO_").AsDateTime,Per),-2)); end else begin Per := GetTimeInt(DATA.RCaseBase.FieldByName("TIME_").AsDateTime); Main.Grid.Cells[0,row] := FormatDateTime("dd.mm.yy",DATA.RCaseBase.FieldByName("TIME_").AsDateTime); Main.Grid.Cells[1,row] := Per; Main.Grid.Cells[2,row] := Organization; Main.Grid.Cells[3,row] := DATA.RCaseBase.FieldByName("COMP_").AsString; Main.Grid.Cells[4,row] := FloatToStr(DATA.RCaseBase.FieldByName("SUMM_").AsFloat+StrToFloat(Main.Grid.Cells[4,row])); Main.Grid.Cells[5,row] := IntToStr(StrToInt(Main.Grid.Cells[5,row])+1); Main.Grid.Cells[6,row] := FloatToStr(RoundTo(GetBusyPercent(DATA.RCaseBase.FieldByName("TIMES_").AsDateTim e,DATA.RCaseBase.FieldByName("TIMEPO_").AsDateTime,Per),-2)); end; DATA.RCaseBase.Next; end; DATA.GridToExcel(Main.Grid,FileName); end;

Помогите написать SQL запрос ... Найти похожие ветки