Thursday, October 11, 2018

AX, SQL SERVER - How to get difference between date and time columns in hours

Suppose this code:
select prodid, fromdate, fromtime, todate, totime from prodroutejob
where
prodid = '18-044906' and
jobtype = 4;
..and this result:
prodid               fromdate                fromtime    todate                  totime
-------------------- ----------------------- ----------- ----------------------- -----------
18-044906            2018-10-08 00:00:00.000 42933       2018-10-08 00:00:00.000 53735
18-044906            2018-10-09 00:00:00.000 31905       2018-10-09 00:00:00.000 39106
18-044906            2018-10-10 00:00:00.000 46033       2018-10-10 00:00:00.000 53234

(3 row(s) affected)
How to get difference between FROM and TO in hours ?

Use this code:
select datediff( hour, [start], [end] ) from
(
select dateadd( second, a.fromtime, a.fromdate ) as [start],
dateadd( second, a.totime, a.todate ) as [end]
from prodroutejob a
where
a.prodid = '18-044906' and
a.jobtype = 4
) a
Output:
diff
-----------
3
2
2

(3 row(s) affected)

Tuesday, October 9, 2018

AX, SQL SERVER - How find released items without assigned document of specified type

Question shows all released items without assigned documents of type PictureDB (=where PictureDB document is missing).
select a.* from
(
select itemid, namealias, 
( select count(*) from docuref 
  where
  typeid = 'PictureDB' and
  reftableid = 175 and
  refrecid = a.recid
) as count_
from inventtable a
where
itemid like 'S-653-%' or  
itemid like 'S-697-' or 
itemid like 'S-698-'
) a
where
count_ = 0
order by a.itemid
Output could be:
itemid                                   namealias            count_
---------------------------------------- -------------------- -----------
S-653-000-0001                           AIDA ALBERT 792      0
S-653-000-0008                           AIDA AJA 5006024259  0
S-653-000-0009                           AIDA AVISA5006024257 0

Thursday, October 4, 2018

SQL SERVER - How to get week number

select getdate(), datepart( wk, getdate() )
Output:
----------------------- -----------
2018-10-04 06:32:01.717 40

(1 row(s) affected)

Wednesday, October 3, 2018

DELPHI - How read data from text file divided by semicolon

This example reads two columns from text file saved in this format:
0-0_0_0200;R-IVECO FRANCIE, 57M
0-0_0_2500;KOSTRY/8019-57 MIST
0-00260-X-0_0_0000;PODPĚR. TRUBKA PRAVÁ
For storing is used memory table (TdxMemTable, QData):
procedure TFDrawingImportItems.doReading;
var
  f : TextFile;
  sRow : string;
  aSplitted: TArray< string >;
begin
  { -- delete all rows }

  if QData.Active then
    begin
      QData.Close;
      QData.Open;
    end;

  { -- open file and read data (two columns divided by ";") }

  AssignFile( f, EFileName.Text );
  try
    Screen.Cursor := crHourGlass;
    QData.DisableControls;

    reset( f );
  while not eof( f ) do
    begin
      readln( f, sRow );

      { only two columns }
      aSplitted := sRow.Split( [';'], 2 );

      QData.Insert;
      QDataItemid.AsString := aSplitted[0];
      QDataname.AsString := aSplitted[1];
      QData.Post;
    end;
  finally
    closeFile( f );

    QData.EnableControls;
    Screen.Cursor := crDefault;
  end;

end;
Output should be: