2

I am making a program for Time and Attendance.

I have a table with this fields

  ATTEND_PK INTEGER NOT NULL,
  EMP VARCHAR,
  ATTEND_DATETIME TIMESTAMP,
  USER_PK SMALLINT,
  ATTEND_UPDATETIME TIMESTAMP);

here is the sample data

| ATTEND_PK  |     EMP       |      ATTEND_DATETIME       |
|     1      |    john       |     1/23/2018 7:00:00      |
|     2      |    john       |     1/23/2018 12:00:00     |
|     3      |    john       |     1/23/2018 13:00:00     |
|     4      |    john       |     1/23/2018 16:00:00     |
|     5      |    john       |     1/24/2018 7:30:00      |
|     6      |    john       |     1/24/2018 11:50:00     |
|     7      |    john       |     1/24/2018 13:20:00     |
|     8      |    john       |     1/24/2018 16:40:00     |

and my desired output is this

 |   EMP    |    DATE     |    AM_IN   |   AM_OUT   |   AM_IN  |   AM_OUT   |
 |   john   |  1/1/2018   |     NULL   |    NULL    |   NULL   |    NULL    | 
 |   john   |  1/2/2018   |     NULL   |    NULL    |   NULL   |    NULL    |
 |   ....   |   .....     |     NULL   |    NULL    |   NULL   |    NULL    |
 |   john   |  1/23/2018  |   7:00:00  |  12:00:00  |  1:00:00 |   4:00:00  |
 |   john   |  1/23/2018  |   7:30:00  |  11:50:00  |  1:20:00 |   4:40:00  |
 |   ....   |   .....     |     NULL   |    NULL    |   NULL   |    NULL    |
 |   john   |  1/31/2018  |     NULL   |    NULL    |   NULL   |    NULL    |

the time format of my desired output and my data is different and display the complete dates of the given month and year

Don Juan
  • 145
  • 1
  • 10
  • 1
    Are you sure it will have only 2 in & out for a day? – Sivaprasath Feb 22 '18 at 13:30
  • Stack Overflow is not a code writing service, you can't just dump your requirements here and expect us to solve it for you. Please show what you tried and where you're stuck. – Mark Rotteveel Feb 22 '18 at 13:52
  • Cross-tabulation Report is better done by client application using one of many available report engines. Making it inside SQL servers is unlucky choice. https://stackoverflow.com/questions/48262968/crosstab-counting-the-same-string-in-a-field-and-display-it-as-field-name Granted, it is not EXACTLY cross-tab, but very similar thing, as you do not know how many columns you would have, @Sivaprasath nailed it perfectly. – Arioch 'The Feb 22 '18 at 14:01
  • Possible duplicate of [crosstab: Counting the same string in a field and display it as field name](https://stackoverflow.com/questions/48262968/crosstab-counting-the-same-string-in-a-field-and-display-it-as-field-name) – Arioch 'The Feb 22 '18 at 14:01

1 Answers1

3

You could solve this problem with a selectable stored procedure.

As a start, here's a procedure that lists the days in a month:

create procedure days_in_month (y int, m int)
returns (d date) as
begin
  d = cast(y || '-' || m || '-01' as date);
  while (extract(month from d) = m) do
  begin
    suspend;
    d = d + 1;
  end
end

Can be called like this

select * from days_in_month (2018, 1)

and will return (in this case) 31 rows with all dates of the days in january 2018.

The table definition in your question text is only pseudo-code, so I cannot use it to provide a working sample. But with the real data table you may either use (left) join syntax (with the above days_in_month procedure on the left side and the physical table on the other side) and make use of Firebirds built-in functions extract and case, or you may put it all in the stored procedure, which may allow to program in a more imperative way.

KarloX
  • 370
  • 3
  • 14