1

I have database table and execute the below query to achieve certain result.

select  a.c_bpartner_id as emp_id,a.mindatetime::date date1,a.mindatetime,a.maxdatetime, a.maxdatetime-a.mindatetime as w_hour
from    c_pay_daily_attend a 
where   a.C_BPartner_ID=1008337
        and a.mindatetime::date between '2019-12-01' and '2019-12-31'

Above my query i get below result.

emp_id date1 mindatetime maxdatetime w_hour
1008337 2019-12-01 2019-12-01 07:14:41 2019-12-01 14:21:36 07:06:55
1008337 2019-12-03 2019-12-03 14:04:07 2019-12-03 22:09:31 08:05:24
1008337 2019-12-04 2019-12-04 07:11:33 2019-12-04 22:03:42 14:52:09
1008337 2019-12-06 2019-12-06 07:00:18 2019-12-06 22:07:18 15:07:00
1008337 2019-12-05 2019-12-05 07:00:47 2019-12-05 22:04:18 15:03:31
1008337 2019-12-08 2019-12-08 07:19:59 2019-12-08 14:14:16 06:54:17
1008337 2019-12-13 2019-12-13 07:00:49 2019-12-13 22:07:32 15:06:43
1008337 2019-12-14 2019-12-14 07:03:38 2019-12-14 22:07:16 15:03:38
1008337 2019-12-15 2019-12-15 07:19:34 2019-12-15 14:15:07 06:55:33
1008337 2019-12-17 2019-12-17 14:11:59 2019-12-17 22:00:15 07:48:16
1008337 2019-12-18 2019-12-18 07:20:26 2019-12-18 22:12:56 14:52:30
1008337 2019-12-19 2019-12-19 07:04:09 2019-12-19 22:08:13 15:04:04
1008337 2019-12-20 2019-12-20 07:01:48 2019-12-20 22:04:43 15:02:55
1008337 2019-12-21 2019-12-21 07:01:43 2019-12-21 22:00:57 14:59:14
1008337 2019-12-22 2019-12-22 07:14:50 2019-12-22 14:11:41 06:56:51
1008337 2019-12-24 2019-12-24 14:11:21 2019-12-24 22:11:33 08:00:12
1008337 2019-12-25 2019-12-25 07:17:05 2019-12-25 22:04:55 14:47:50
1008337 2019-12-26 2019-12-26 07:00:29 2019-12-26 22:08:52 15:08:23
1008337 2019-12-27 2019-12-27 07:01:25 2019-12-27 22:06:18 15:04:53
1008337 2019-12-28 2019-12-28 07:00:24 2019-12-28 22:00:40 15:00:16
1008337 2019-12-29 2019-12-29 07:18:05 2019-12-29 14:14:25 06:56:20
1008337 2019-12-31 2019-12-31 14:14:43 2019-12-31 22:04:14 07:49:31

Now i need a query to get below result. where a.mindatetime::date between '2019-12-01' and '2019-12-31' condition may vary. like a.mindatetime::date between '2020-01-01' and '2020-01-31 or others.

emp_id 2019-12-01 2019-12-02 2019-12-03 2019-12-04 2019-12-05 2019-12-06 2019-12-07 2019-12-08 2019-12-09 2019-12-10 2019-12-11 2019-12-12 2019-12-13 2019-12-14 2019-12-15 2019-12-16 2019-12-17 2019-12-18 2019-12-19 2019-12-20 2019-12-21 2019-12-22 2019-12-23 2019-12-24 2019-12-25 2019-12-26 2019-12-27 2019-12-28 2019-12-29 2019-12-30 2019-12-31
1008337 07:06:55 null 08:05:24 02:52:09 03:03:31 03:07:00 null 06:54:17 null null null null 03:06:43 03:03:38 06:55:33 null 07:48:16 02:52:30 03:04:04 03:02:55 02:59:14 06:56:51 null 08:00:12 02:47:50 03:08:23 03:04:53 03:00:16 06:56:20 null 07:49:31
3
  • you can use crosstab query in postgressql but as you required dynamic date row so use in procedure. Commented Mar 9, 2020 at 6:14
  • What does your data look like? Commented Mar 9, 2020 at 11:22
  • Dear all, i forget to say that a.mindatetime::date between '2019-12-01' and '2019-12-31' condition may vary. like a.mindatetime::date between '2020-01-01' and '2020-01-31 Commented Mar 9, 2020 at 16:55

4 Answers 4

1

As I said in comment section we can do this using crosstab function below query produces your expected result. Below has been tested on Postgresql.

Execute this before running Crosstab function. It is one time execution which enables crosstab functionality in your DB

CREATE extension tablefunc;

Below is the query having crosstab as per my test table that I have created in my local DB as per your dataset-

   ` SELECT  * from crosstab(
    'SELECT emp_id,date1,w_hour FROM TestStack1 where emp_id=1008337
             and mindatetime::date between ''2019-12-01''::Date and ''2019-12-31''::Date order by 1,2'
            ) AS ct ( "emp_id" number,"2019-12-01" character varying,"2019-12-02" character varying,"2019-12-03" character varying )`

enter image description here

You can see in above query that I have hardcoded all date so that it will produces your expected date columns but it can be make dynamic with the help of procedure.

Sign up to request clarification or add additional context in comments.

Comments

1
select a.c_bpartner_id 
  , max(case when a.date1 = '2019-12-01' then a.w_hour else null end) as "2019-12-01"
  , max(case when a.date1 = '2019-12-02' then a.w_hour else null end) as "2019-12-02"
  , max(case when a.date1 = '2019-12-03' then a.w_hour else null end) as "2019-12-03"
from (
  select  a.c_bpartner_id as emp_id
    , a.mindatetime::date as date1
    , a.mindatetime
    , a.maxdatetime
    , a.maxdatetime-a.mindatetime as w_hour
  from    c_pay_daily_attend a 
  where   a.C_BPartner_ID=1008337
          and a.mindatetime::date between '2019-12-01' and '2019-12-31'
) a
group by a.c_bpartner_id 

Comments

1

Following will give you the expected output. I have used generate_series to get the list of days and in results i have written manually which can be automated using procedure.

SELECT *
FROM   crosstab(
   $$select (select distinct emp_id from test) as emp_id,t1.date1::text as date1,t2.w_hour from 
  (SELECT DATE('2019-12-31') - i date1 FROM generate_series(0, 30) i)t1 
   left join test t2 on t1.date1=t2.date1 order by 2$$
   ) 
  AS t("emp_id" integer,"2019-12-01" time,"2019-12-02" time,"2019-12-03" time,
 "2019-12-04" time,"2019-12-05" time,"2019-12-06" time,"2019-12-07" time,
 "2019-12-08" time,"2019-12-09" time,"2019-12-10" time,"2019-12-11" time,"2019-12-12" time,
 "2019-12-13" time,"2019-12-14" time,"2019-12-15" time,"2019-12-16" time,"2019-12-17" time,
 "2019-12-18" time,"2019-12-19" time,"2019-12-20" time,"2019-12-21" time,
 "2019-12-22" time,"2019-12-23" time,"2019-12-24" time,"2019-12-25" time,
 "2019-12-26" time,"2019-12-27" time,"2019-12-28" time,"2019-12-29" time,
 "2019-12-30" time,"2019-12-31" time
);

Comments

1

You can do this using conditional aggregation. In Postgres, this would use the filter clause:

select a.c_bpartner_id as emp_id,
       (max(a.maxdatetime) filter (where a.date1::date = '2019-12-01') -
        min(a.mindatetime) filter (where a.date1::date = '2019-12-01')
       ) as hours_20191201,
       (max(a.maxdatetime) filter (where a.date1::date = '2019-12-02') -
        min(a.mindatetime) filter (where a.date1::date = '2019-12-02')
       ) as hours_20191202,
       . . . 
from c_pay_daily_attend a 
where a.C_BPartner_ID = 1008337 and
      a.mindatetime >= '2019-12-01' and
      a.mindatetime < '2020-01-01';

This assumes that mindatetime and maxdatetime are all on the same date. This is true for your sample data. If this is not true, then you should ask a new question with more appropriate sample data an desired results.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.