Jump to content

Sql Query

nikhilboorla

By nikhilboorla
in Old Discussions

 Share

Recommended Posts

nikhilboorla Side Hero

nikhilboorla

Posted
Posted

empid                        jobdate              jobid                duration

151                           5-jan-14              201                   3 hrs

151                           5-apr-14              201                   5 hrs

152                           6-jan-14              202                    2 hrs

152                           6-feb-14              202                   3 hrs

152                            6-mar-14            202                   4 hrs

153                            7-jan-14              203                   3 hrs

153                             7-mar-14            203                   3 hrs

 

each employee is allowed to work for max 7 hrs(trim extra hrs from last possible month if more than 7hrs )

 

desired o/p:

------------------

 

EMPID               JAN            FEB        MAR        APR

151                      3                  -               -               4*        

152                       2                 3              2*             -

153                        3                 -              3              -

 

* indiactes hrs trimmed as max hrs reached

 

 

        

 

Darling999 Celebrity

Darling999

Posted
Posted

As per my understanding, you got to use PIVOT query to active this. Syntax choosi try cheyyi bhayya ull get it.

Yuvatha LEGEND

Yuvatha

Posted
Posted

As per my understanding, you got to use PIVOT query to active this. Syntax choosi try cheyyi bhayya ull get it.

xMdSu.gif

rajivn786 Side Hero

rajivn786

Posted
Posted

You might have to apply something on top of this logic...

X is the table for the values you showed on the top..

 

 
select empid,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),1,duration,0)) JAN,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),2,duration,0)) FEB,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),3,duration,0)) MAR,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),4,duration,0)) APR,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),5,duration,0)) MAY,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),6,duration,0)) JUN,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),7,duration,0)) JUL,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),8,duration,0)) AUG,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),9,duration,0)) SEP,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),10,duration,0)) OCT,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),11,duration,0)) NOV,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),12,duration,0)) DEC
from x
group by empid
order by empid
mtkr LEGEND

mtkr

Posted
Posted

bayya try to post wth sample ddl and dml's

create table employee
(
empid varchar(100),
jobdate varchar(100),
jobid varchar(100),
duration varchar(100)
)


insert into employee values ('151','05-jan-14','201','3')
insert into employee values ('151','5-apr-14','201','5')
insert into employee values ('152','6-jan-14','202','2')
insert into employee values ('152','6-feb-14','202','3')
insert into employee values ('152','6-mar-14','202','4')
insert into employee values ('153','7-jan-14','203','3')
insert into employee values ('153','7-mar-14','203','3')



SELECT empid,
    SUM(CASE WHEN MONTH(jobdate) = 1 THEN duration ELSE 0 END) AS JAN,
    SUM(CASE WHEN MONTH(jobdate) = 2 THEN duration ELSE 0 END) AS FEB,
    SUM(CASE WHEN MONTH(jobdate) = 3 THEN duration ELSE 0 END) AS MAR,
    SUM(CASE WHEN MONTH(jobdate) = 4 THEN duration ELSE 0 END) AS APR,
    SUM(CASE WHEN MONTH(jobdate) = 5 THEN duration ELSE 0 END) AS MAY,
    SUM(CASE WHEN MONTH(jobdate) = 6 THEN duration ELSE 0 END) AS JUN,
    SUM(CASE WHEN MONTH(jobdate) = 7 THEN duration ELSE 0 END) AS JUL,
    SUM(CASE WHEN MONTH(jobdate) = 8 THEN duration ELSE 0 END) AS AUG,
    SUM(CASE WHEN MONTH(jobdate) = 9 THEN duration ELSE 0 END) AS SEP,
    SUM(CASE WHEN MONTH(jobdate) = 10 THEN duration ELSE 0 END) AS OCT,
    SUM(CASE WHEN MONTH(jobdate) = 11 THEN duration ELSE 0 END) AS NOV,
    SUM(CASE WHEN MONTH(jobdate) = 12 THEN duration ELSE 0 END) AS DEC
 FROM employee
GROUP BY empid

abve query will transform data into columns...

 

but hours ni trim cheyyali ante  paina bayya cheppinattu u need to apply some more logic

mtkr LEGEND

mtkr

Posted
Posted

 

You might have to apply something on top of this logic...

X is the table for the values you showed on the top..

 

 
select empid,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),1,duration,0)) JAN,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),2,duration,0)) FEB,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),3,duration,0)) MAR,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),4,duration,0)) APR,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),5,duration,0)) MAY,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),6,duration,0)) JUN,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),7,duration,0)) JUL,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),8,duration,0)) AUG,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),9,duration,0)) SEP,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),10,duration,0)) OCT,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),11,duration,0)) NOV,
SUM(DECODE(extract (month from to_date(x.jobdate, 'dd-mon-yy')),12,duration,0)) DEC
from x
group by empid
order by empid

 

 

agreeed...

mtkr LEGEND

mtkr

Posted
Posted

 naak logic ela apply cheyylo elagatledhu....

 

 

either main table(or dump data into a temp tbl) lone duration and emp id ni parse chesi if one emp id ki sum of duration > 7 unte u need to update last value of emp id to sum(all dutrations) -7

 

something alaa...

 Share
Go to topic listing
×
×
  • Create New...