min max distinct records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

min max distinct records

Author

Topic

sindhu sarah
Starting Member

34 Posts

Posted - 2012-02-28 : 01:54:48

Hi,

I need help for the following query.i need to get the min and max value for the distinct records.
ex: i have a table called PRFB and i have soem data's as follows
Entity EID FirstPer LastPer SalCury BSal Hous Trnsp Food OtAll SpAll PayCury
cae TST0002 201102 201102 AED 36800.00 0.00 5000.00 0.00 0.00 18200.00 AED
cae TST0002 201103 201103 AED 36800.00 0.00 5000.00 0.00 0.00 18200.00 AED
cae TST0002 201104 201104 AED 36800.00 0.00 5000.00 0.00 0.00 18200.00 AED
cae TST0002 201105 201105 AED 36800.00 0.00 5000.00 0.00 0.00 18200.00 AED
cae TST0002 201106 201106 AED 36800.00 0.00 5000.00 0.00 18200.00 0.00 AED
cae TST0002 201107 201107 AED 36800.00 0.00 5000.00 0.00 0.00 18200.00 AED
cae TST0002 201108 201108 AED 36800.00 0.00 5000.00 0.00 0.00 18200.00 AED

i need to get the min and max of the period.
if i put the following query
select min(firstper),max(firstper) from PRFB1
where Entity='cae' and EID='TST0002' and SalCury='AED' and
BSal='36800.00' and Hous='0.00' and Trnsp='5000.00' and Food='0.00' and
OtAll='0.00' and SpAll='18200.00'

i could get the output as 201102 201108

but i need the output as 201102 201105
the next time when i query i shld get as 201106 201106
and then 201106 201108.Please let me know if the above statements are not clear.

Thanks in Advance,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-28 : 01:59:13

quote:
i could get the output as 201102 201108

but i need the output as 201102 201105

What is the logic here ? Why 201105 instead of 201108 ?

KH
[spoiler]Time is always against us[/spoiler]

sindhu sarah
Starting Member

34 Posts

Posted - 2012-02-28 : 02:18:56

here 201105 is the year with month which is called as period.this is the payroll history.Whenever the payroll changes then the period should be the min and max of the changed period.in the below scnario the payroll from 201102 to 201105 remains same then the payroll changed in 201106 in the OtAll field.again it changed from 201107 to 201108.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-28 : 02:26:11

[code]
WHERE firstper BWTEEN 201102 AND 201108
[/code]

Do you have a table that store the period start and end ?

And how do you determine what is the current period ?

KH
[spoiler]Time is always against us[/spoiler]

sindhu sarah
Starting Member

34 Posts

Posted - 2012-02-28 : 02:45:39

Between i cant use since i wil not be knowing the period value like 201102 etc .and its not only for 1 employee its for many employees.i wrote a loop to get the values of period from the other table as a procedure.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-28 : 02:48:30

all employee should have the same period right ?

Do you have a table that store the period start and end ?

And how do you determine what is the current period ?

KH
[spoiler]Time is always against us[/spoiler]

sindhu sarah
Starting Member

34 Posts

Posted - 2012-02-28 : 02:58:59

ya i have a table which has the the period with the salary changes.all employees has different periods since the salary get changed individually by employee.the current period will be till the current month..bt this dada is something which displays the history.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-28 : 03:39:31

then you should be able to determine the current period start and end for an employee and use in the WHERE clause


WHERE firstper BWTEEN 201102 AND 201108

KH
[spoiler]Time is always against us[/spoiler]

sindhu sarah
Starting Member

34 Posts

Posted - 2012-02-28 : 03:47:54

we can get the min of first per but the end period we cant get since its not the current period.its the period where the salary changes.whenever the salary changes then output should be that start period and the end period.for examsple salary has been changed for the allowances in 201102 and remains same til 201105 then it should display as 201102 to 201105.That am finding out by min and max function.But here problem occured since the salary changed in 201105 in allowances and then again in 201106 the same changes with the same amount occured in special allowances.and remains same as 201105 for the period 201107 to 201108.
its like 201102 - 201105 the changes are other allowance 0 and special allowance is 18200.again in 201106 it is other allowance 18200 and special allowance as 0.Again in 201107 to 201108 it is other allowance 0 and special allowance is 18200.so now i shld get o/p as
201102 - 201105
201106- 201106
201107-201108

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources