Select rows from last month - 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

Select rows from last month

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-09 : 06:31:28

Hi all,

this is my Statment.

SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS Cardnr,
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 = '021' AND a.INHALT>'0'
and a.PFGLTAB>=convert(varchar(8),dateadd(month,datediff(month,0,getdate())-1,0),112)
GO and a.a.PFGLTBIS<convert(varchar(8),dateadd(month,datediff(month,0,getdate())-0,0),112)

But it deliver me also rows which are not in valid. I need only rows from last month from 01.10 until 31.10 and this rows are over the time.
How can i select dyncamicaly all row between 01.10 and 31.10 ?

FIRMA PSNR FELDNR PFLFDNR Cardnr PFGLTAB PFGLTBIS KSTNR PSPERSNR PSVORNA PSNACHNA
1 398 021 1 DU957 20141112 20321231 000043 000957 xxxxx xxxxxx
1 399 021 1 DU955 20141101 20321231 000010 000955 xxxxx xxxx
1 403 021 1 DU960 20141201 20321231 000086 000960 xxxx xxxxx

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-09 : 07:50:30

Sorry posted too quick this won't work with multiple years. We'll need to add a year component to this.

You need to convert the table columns to MONTH and then compare it to GETDATE()

See Below

WHERE DATEPART(MONTH,CONVERT(DATE, a.PFGLTAB)) = DATEPART(MONTH, GETDATE()) -1
AND DATEPART(MONTH,CONVERT(DATE, aPFGLTBIS)) = DATEPART(MONTH, GETDATE()) -1

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-09 : 08:23:07

I'm sure there will be more elegant solutions but I think this will work

WHERE (DATEPART(MONTH,CONVERT(DATE, a.PFGLTAB)) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, a.PFGLTAB)) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))
AND (DATEPART(MONTH,CONVERT(DATE, a.PFGLTBIS)) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, a.PFGLTBIS)) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-09 : 11:22:29

Hi mandm,

thx for your reply but i get error msg:

SQL Server Database Error: Explicit conversion from data type int to date is not allowed.

The PFGLTAB and PFGLTBIS are integer !!

quote:
Originally posted by mandm

I'm sure there will be more elegant solutions but I think this will work

WHERE (DATEPART(MONTH,CONVERT(DATE, a.PFGLTAB)) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, a.PFGLTAB)) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))
AND (DATEPART(MONTH,CONVERT(DATE, a.PFGLTBIS)) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, a.PFGLTBIS)) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-09 : 14:24:32

I added a convert to VARCHAR before the convert DATE and it's working on my test data. Give this a try.

WHERE (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))
AND (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-09 : 15:10:23

sorry but i get only one row with you last condition !! there should be 252 rows and not only one.

smal part of Table

FIRMA PSNR FELDNR PFLFDNR INHALT PFGLTAB PFGLTBIS
1 99 021 1 2901009735 20120301 20140531
1 99 021 2 176 20140601 20140630
1 99 021 3 176 20140818 20321231
1 99 022 1 0 20110101 20120229
1 99 022 2 1 20120301 20120930
1 99 022 3 0,9 20121001 20130531
1 99 022 4 0 20130601 20140131
1 99 022 5 0,75 20140201 20140630
1 99 022 6 0 20140701 20140817
1 99 022 7 0,8 20140818 20320131

CREATE TABLE [dbo].[PNPERFELD] (
[FIRMA] smallint NOT NULL,
[PSNR] int NOT NULL,
[FELDNR] varchar(10) NOT NULL,
[PFLFDNR] int NOT NULL,
[INHALT] varchar(254) NULL,
[PFGLTAB] int NULL,
[PFGLTBIS] int NULL)

Thx
zero1de

quote:
Originally posted by mandm

I added a convert to VARCHAR before the convert DATE and it's working on my test data. Give this a try.

WHERE (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))
AND (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-09 : 15:12:41

So you want the data from last month for any year?

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-09 : 15:26:13

If you want last month for any year current or prior then the following should work. If that's not what you want then I am not understanding the requirements.

WHERE (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) <= DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))
AND (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) <= DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-09 : 16:16:07

sorry still not works.

I have in the table valid records of several years for each employee. For each employee is only one record is valid(defined PFGLTAB,PFGLTBIS this means Date from TO) true, even if he has 10 entries. They become bonus places. Important it is what i find the record which is valid for each employee.

This should be the output.

FIRMA PSNR FELDNR PFLFDNR Cardnr PFGLTAB PFGLTBIS KSTNR PSPERSNR PSVORNA PSNACHNA
1 191 021 2 726 20140601 20321231 000071 000726 xxxx
1 192 021 3 730 20140601 20321231 000071 000730 xxxdx
1 197 021 2 737 20140601 20321231 000072 000737 xxxxx
1 198 021 2 738 20140601 20321231 000079 000738 xxxx
1 200 021 2 742 20140601 20321231 000078 000742 xxxx

Thx
zero1de

quote:
Originally posted by mandm

If you want last month for any year current or prior then the following should work. If that's not what you want then I am not understanding the requirements.

WHERE (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTAB))) <= DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))
AND (DATEPART(MONTH,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR,CONVERT(DATE, CONVERT(VARCHAR(8), a.PFGLTBIS))) <= DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-09 : 16:28:41

But your original post stated

"But it deliver me also rows which are not in valid. I need only rows from last month from 01.10 until 31.10 and this rows are over the time. How can i select dyncamicaly all row between 01.10 and 31.10 ?"

The dates in your example don't show any for month 10. What am I missing?

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-10 : 03:23:06

I have converted the system date. Now it seems to work.

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))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources