Problem with Distinct....

[gunapriyan]

Dear Pals,

I have a table named "Payment". It contais the following stucture.

Emp_Name
Emp_Category
Plucked_Date
Rate_of_Wages
Plucked_Days
Sundry_Days
Plucked_Kilos
Plucking_Wages
Sundry_Wages
Incentive_Kilos
Plucking_Incentive
Extra_Hour
Extra_Wages
Sunday_Days
Sunday_Wages
Total_Wages
Advance_Recovery
Total_Payable

The daily entry will fell down into this table…
When I mention 2 days of “Plucked_Days” , it must come single entry for each employee…But now it comes like,
“Guna”, “Dhanush”,”Muthu”, “Karnan” employees came 2 times….I need single time….But I have used distinct……But no result…
Emp_Name
Emp_Category
Plucked_Date
Rate_of_Wages
Plucked_Days
Sundry_Days
Plucked_Kilos
Plucking_Wages
Sundry_Wages
Incentive_Kilos
Plucking_Incentive
Extra_Hour
Extra_Wages
Sunday_Days
Sunday_Wages
Total_Wages
Advance_Recovery
Total_Payable
Guna
Male

5/22/2010​

116​

0​

1​

0​

0​

116​

0​

0​

0​

0​

0​

0​

116​

0​

116​

Muthu
Female

5/22/2010​

100​

1​

0​

34​

100​

0​

4​

2​

1​

10​

0​

0​

112​

0​

112​

Dhanush
Female

5/22/2010​

100​

1​

0​

34​

100​

0​

4​

2​

0​

0​

0​

0​

102​

0​

102​

Karnan
Supervisor

5/22/2010​

120​

0​

1​

0​

0​

120​

0​

0​

0​

0​

0​

0​

120​

0​

120​

Guna
Male

5/23/2010​

116​

0​

1​

0​

0​

116​

0​

0​

0​

0​

0​

0​

116​

0​

116​

Muthu
Female

5/23/2010​

100​

0​

1​

0​

0​

100​

0​

0​

0​

0​

0​

0​

100​

0​

100​

Dhanush
Female

5/23/2010​

100​

0​

1​

0​

0​

100​

0​

0​

0​

0​

0​

0​

100​

0​

100​

Karnan
Supervisor

5/23/2010​

120​

0​

1​

0​

0​

120​

0​

0​

0​

0​

0​

0​

120​

0​

120​

To that I have used following query,

SELECT a.emp_name AS Emp_Name, Sum(a.plucked_days) AS SumOfplucked_days, a.rate_of_wages AS rate_of_wages,
Sum(a.sundry_days) AS SumOfsundry_days, Sum(a.plucked_Kilos) AS SumOfplucked_Kilos, a.plucking_wages AS plucking_wages,
a.sundry_wages AS sundry_wages, Sum(a.incentive_kilos) AS SumOfincentive_kilos, Sum(a.plucking_incentive) AS SumOfplucking_incentive, Sum(a.extra_hour) AS SumOfextra_hour, Sum(a.extra_wages) AS SumOfextra_wages, a.sunday_wages AS sunday_wages,
Sum(a.total_wages) AS SumOftotal_wages, Sum(a.advance_recovery) AS SumOfadvance_recovery, Sum(a.total_payable) AS SumOftotal_payable
FROM (SELECT b.emp_name FROM (SELECT DISTINCT emp_name FROM payment GROUP BY emp_name) AS b GROUP BY b.emp_name)
AS c INNER JOIN payment AS a ON c.emp_Name=a.emp_Name
WHERE a.plucked_date between cdate('22/05/2010') and cdate('23/05/2010')
GROUP BY a.emp_Name,a.rate_of_wages, a.plucking_wages, a.sundry_wages, a.sunday_wages;

I am waiting for your nice reply.
Thanks in advance..
Yours
GUNA…..

 

[beefwellington]

I think we need a little more information. Are you trying to get the most recent date? If that's the case, you'd want to do MAX(Plucked_Days) to return the most recent date.

 

[gunapriyan]

Dear Boss,

Thanks for your reply.

Actually my need is that I need a single row for each employee with manipulation.

All the daily entries will falling in Payment Table...

7 days all employees are worked in different manner i.e plucking, sundry and sunday......

One employee may work in plucking and sundry and sunday....

Consider me, I am working monday as plucker, Tuesday Plucking, Wednesday sundry, thursday sundry, friday plucking, saturday sundry, sunday sunday......

So My consolidation is Plucking is 3 days....Sundry is 3 Days.....Sunday is 1 day...

Normally the user will take the report weekly once.....

At the week end my data must display in report as follow,

"Guna" 3 3 1 375 425 110 910

But now my query gets the data as follow

"Guna" 3 0 0 375 0 0 375
"Guna" 0 3 0 0 425 0 425
"Guna" 0 0 1 0 0 110 110

I have used distinct. But no use. To rectify that I need your help. I request you to please change the query and send it to me...

Thanks in advance...

Guna

 

[gunapriyan]

Dear Boss,

I herewith enclose my database for your kind perusal..

- GUNA

 

[gunapriyan]

Dear Friends,

Due to very urgent i am expecting your nice reply...

Thanks & Regards,

Guna

 

[gunapriyan]

Dear Friends,

Can anybody help me?

I would be happy if you solve this error...

Thanks & Regards,

Guna