How to select rows with max value and datediff

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

How to select rows with max value and datediff

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2015-02-04 : 08:02:18

Hi all,
I have here a query which delivers me the user data from the last month. The problem what I have is, if employee have more then one rows in this month, they will be also deliverd. But exactly this is not needed. I need only the last record from last month.

SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS FTE,
a.PFGLTAB,
a.PFGLTBIS,
C.KSTNR,
C.PSPERSNR,
C.PSVORNA,
C.PSNACHNA
FROM Tisoware.dbo.PNPERFELD a, Tisoware.dbo.PERSTAMM C
WHERE (C.PSNR = a.PSNR)
AND (C.FIRMA = a.FIRMA)
AND ((a.FELDNR = '022' AND a.INHALT>'0' and a.PFGLTAB <= convert(varchar(8),dateadd(month,datediff(month,0,getdate())-0,-1),112))
AND a.PFGLTBIS >=convert(varchar(8),dateadd(month,datediff(month,0,getdate())-1,0),112))

Result from Query at the moment:

FIRMA PSNR FELDNR PFLFDNR FTE PFGLTAB PFGLTBIS KSTNR PSPERSNR
1 351 022 1 1 20130828 20320101 000084 000895
1 47 022 2 0,91 20141001 20320101 000079 000057
1 166 022 1 1 20110101 20320101 000077 000543
1 364 022 1 1 20131001 20150114 000072 000920
1 364 022 2 0,94 20150115 20321231 000072 000920

As you can see, PSNR=364 has two rows and i need only the row from last month and last date. Maybe we can use Field PFLFDNR as counter.
Can anyone help me to get only one row for every employee ?
like this

FIRMA PSNR FELDNR PFLFDNR FTE PFGLTAB PFGLTBIS KSTNR PSPERSNR
1 351 022 1 1 20130828 20320101 000084 000895
1 47 022 2 0,91 20141001 20320101 000079 000057
1 166 022 1 1 20110101 20320101 000077 000543
1 364 022 2 0,94 20150115 20321231 000072 000920

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-04 : 08:15:09

Try this:

With getrank
As
(
SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS FTE,
a.PFGLTAB,
a.PFGLTBIS,
C.KSTNR,
C.PSPERSNR,
C.PSVORNA,
C.PSNACHNA,
Row_Number() Over(Partition By a.PSNR Order By a.PFGLTAB Desc) as rn
FROM Tisoware.dbo.PNPERFELD a, Tisoware.dbo.PERSTAMM C
WHERE (C.PSNR = a.PSNR)
AND (C.FIRMA = a.FIRMA)
AND ((a.FELDNR = '022' AND a.INHALT>'0' and a.PFGLTAB <= convert(varchar(8),dateadd(month,datediff(month,0,getdate())-0,-1),112))
AND a.PFGLTBIS >=convert(varchar(8),dateadd(month,datediff(month,0,getdate())-1,0),112))
)
Select * From getrank
Where rn = 1

We are the creators of our own reality!

zero1de
Posting Yak Master

105 Posts

Posted - 2015-02-04 : 08:26:20

Many thanks sz1 it really works fine.

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-04 : 08:34:06

glad to help :)

We are the creators of our own reality!

zero1de
Posting Yak Master

105 Posts

Posted - 2015-02-05 : 02:17:52

quote:
Originally posted by sz1

glad to help :)

We are the creators of our own reality!

Unfortunately, I was premature. The query also cuts records out which should not be cut. I get instead 274 records only 244 rows back.

zero1de
Posting Yak Master

105 Posts

Posted - 2015-02-05 : 15:11:18

Who can help me ? please It is urgent.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-05 : 15:19:51

If you want help: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-05 : 16:04:26

quote:
Originally posted by zero1de

really !! if you can't help me the fuck off man .. ok

Not the best approach when someone is trying to help you. My guess is you will not get any more help in here. I know I won't even try now!

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-06 : 10:06:05

What happened to this topic, been working out the office and just seen this post. Can anyone explain?

We are the creators of our own reality!

zero1de
Posting Yak Master

105 Posts

Posted - 2015-02-07 : 13:38:22

tkizer i want apologize me for my behavior single days ago. I had a very bad day. I hope you're not angry with me.

quote:
Originally posted by tkizer

If you want help: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

zero1de
Posting Yak Master

105 Posts

Posted - 2015-02-07 : 13:41:52

I would be glad to get a reply to my question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources