CODEWITHSUNDEEP
javaexceljdbc
Aditya Jain
Aditya Jain

Reputation: 1095

Iterating columns to find column with last non-zero value

I have table effort_sheet as below,

-----------------------------------------------
Pid  WeekendDate  Mon Tue Wed Thr Fri Sat Sun
----------------------------------------------
11   14.10.2012   4    4   5   1   0   0   0
11   07.10.2012   2    2   1   5   3   0   0
12   07.10.2012   2    2   0   0   0   0   0

Here, WeekendDate is date on 'Sun'(last column)

I need to find the date for each Pid when last non-zero value was logged. So my result should be something like

Pid  LastValueDate
11   11.10.2012
12   02.10.2012

11 -> 11.10.2012 was Thr(value 1)
12 -> 02.10.2012 was Tue(value 2)

As my data is in Excel, an d I am using JDBC-ODBC connection. I would prefer we this could be achieved just with SQL.

My incomplete solution (need to find last_effort_day): 

SELECT pid
      ,max(WeekendDate) - (6-last_effort_day) AS end_date
 FROM effort_sheet
GROUP BY pid

Please help.

Upvotes: 1

Views: 250

Answers (5)

Gaurav Soni
Gaurav Soni

Reputation: 6338

Try this 
select es.p_id,es.weekenddate-( CASE 
                             WHEN SUN != 0 THEN 0 
                             WHEN SAT != 0 THEN 1 
                             WHEN FRI != 0 THEN 2 
                             WHEN THR != 0 THEN 3 
                             WHEN WED != 0 THEN 4 
                             WHEN TUE != 0 THEN 5 
                             ELSE 6 
                           END )  from(
select p_id,max(weekenddate)dt from effort_sheet group by p_id
  )inner,effort_sheet es
where inner.p_id =es.p_id
AND inner.dt=es.weekenddate;

sqlfiddle link

Upvotes: 1

fthiella
fthiella

Reputation: 49049

I would start using this union query, which is easier to manipulate:

 Select Pid, WeekendDate-6 As WeekDate, Mon As Num
 From effort_sheet
 Union
 Select Pid, WeekendDate-5 As WeekDate, Tue As Num
 From effort_sheet
 Union
 Select Pid, WeekendDate-4 As WeekDate, Wed As Num
 From effort_sheet

and so on, for every day of the week. Then you can just use a group by query:

 Select Pid, Max(WeekDate)
 From (the union select above)
 Where Num>0
 Group By Pid

this is standard SQL and should work with any DBMS.

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

SELECT Pid, MAX(WeekendDate) - (8 - MIN(CHARINDEX('0', CAST(Mon AS nvarchar(10)
                                                       + CAST(Tue AS nvarchar(10))
                                                       + CAST(Wed AS nvarchar(10))
                                                       + CAST(Thr AS nvarchar(10))
                                                       + CAST(Fri AS nvarchar(10))
                                                       + CAST(Sat AS nvarchar(10))
                                                       + CAST(Sun AS nvarchar(10))))) AS last_effort_day
FROM effort_sheet
GROUP BY pid

Upvotes: 1

Seasoned
Seasoned

Reputation: 1049

try this:

select pid, max(case
when mon = 0 then weekenddate - 7
when mon != 0 and tue = 0 then weekenddate - 6
when tue != 0 and wed = 0 then weekenddate - 5
when wed != 0 and thr = 0 then weekenddate - 4
when thr != 0 and fri = 0 then weekenddate - 3
when fri != 0 and sat = 0 then weekenddate - 2
when sat != 0 and sun = 0 then weekenddate -1 end)
from effort_sheet
group by pid

Upvotes: 0

Gidil
Gidil

Reputation: 4137

SELECT PID, 
       Dateadd(DAY, -1 * ( CASE 
                             WHEN SUN != 0 THEN 0 
                             WHEN SAT != 0 THEN 1 
                             WHEN FRI != 0 THEN 2 
                             WHEN THR != 0 THEN 3 
                             WHEN WED != 0 THEN 4 
                             WHEN TUE != 0 THEN 5 
                             ELSE 6 
                           END ), WEEKENDDATE) AS end_date 
FROM   EFFORT_SHEET 
GROUP  BY PID

Upvotes: 1

Related Questions