Self-Join Rows as columns - 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

Self-Join Rows as columns

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2012-01-30 : 18:31:50

Hello people,

sorry my english is not the best. My View provides the data correctly as long as if there are no duplicate values ??but I must also select by date and take the only valid value see below:

Create Table:

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)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);

Data insert for the Table:

(FIRMA, PSNR, FELDNR, PFLFDNR, INHALT, PFGLTAB, PFGLTBIS) VALUES
(1, 223, '021', 1, '2901009746', 20110101, 20113112),
(1, 223, '022', 1, '1', 20110101, 20113112),
(1, 223, '022', 2, '0,92', 20120101, 20123112)

My View:
CREATE VIEW [dbo].[MA_EXTRAFLD]
AS
SELECT a.FIRMA,a.PSNR,a.FELDNR,a.PFLFDNR,
a.INHALT as Cardnr,b.INHALT as FTE,a.PFGLTAB,a.PFGLTBIS
FROM PNPERFELD a, PNPERFELD b
WHERE a.PSNR = b.PSNR and a.FIRMA = b.FIRMA and a.FELDNR = 021 and a.INHALT <> b.INHALT

This View gives following result:

MY VIEW GIVES MOMENT THIS OUTPUT:
FIRMA PSNR FELDNR PFLFDNR Cardnr FTE PFGLTAB PFGLTBIS
1 223 021 1 2901009746 1 20110101 20111231
1 223 021 1 2901009746 0,92 20110101 20111231

I NEED VIEW IN THIS OUTPUT:
FIRMA PSNR FELDNR PFLFDNR Cardnr FTE PFGLTAB PFGLTBIS
1 223 021 1 2901009746 0,92 20110101 20111231

But i need the Data in this Format: becouse in the table the row
(1, 223, '022', 2, '0,92', 20120101, 20123112) is valid becouse of date its from 2012.01.01 until 2012.31.12

and my question is, how can I change the viewer so that it outputs only one PSNR per line and not more ??
many THX for your help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-30 : 19:27:01

When you said "(1, 223, '022', 2, '0,92', 20120101, 20123112) is valid becouse of date its from 2012.01.01 until 2012.31.12 , is that because today falls within that date range? If that is the case, then you can add another condition to your view like this:

CREATE VIEW [dbo].[MA_EXTRAFLD]
AS
SELECT a.FIRMA,a.PSNR,a.FELDNR,a.PFLFDNR,
a.INHALT as Cardnr,b.INHALT as FTE,a.PFGLTAB,a.PFGLTBIS
FROM PNPERFELD a, PNPERFELD b
WHERE a.PSNR = b.PSNR and a.FIRMA = b.FIRMA and a.FELDNR = 021 and a.INHALT <> b.INHALT
AND PFGLTAB <= GETDATE() AND PFGLTBIS >= GETDATE();

If you have more than one row that satisfies that condition with the same PSNR, then you would of course, get more than one row - unless you add additional conditions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 19:33:25

why are you storing dates as integer values? always try to use proper data type for columns as it would reduce efforts spend for date manipulation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-01-30 : 20:04:27

the integer values ??are given as I can't do anything. The proposal of yours does not work like this:
Result=0 Rows

SELECT a.FIRMA,a.PSNR,a.FELDNR,a.PFLFDNR,
a.INHALT as Cardnr,b.INHALT as FTE,a.PFGLTAB,a.PFGLTBIS
FROM PNPERFELD a, PNPERFELD b
WHERE a.PSNR = b.PSNR and a.FIRMA = b.FIRMA and a.FELDNR = 021 and a.INHALT <> b.INHALT
AND CAST(CAST(a.PFGLTAB AS VARCHAR) AS DATETIME) <= GETDATE()
AND CAST(CAST(b.PFGLTBIS AS VARCHAR) AS DATETIME) >= GETDATE();

and when i try only with ....one Date condition like this
SELECT a.FIRMA,a.PSNR,a.FELDNR,a.PFLFDNR,
a.INHALT as Cardnr,b.INHALT as FTE,a.PFGLTAB,a.PFGLTBIS
FROM PNPERFELD a, PNPERFELD b
WHERE a.PSNR = b.PSNR and a.FIRMA = b.FIRMA and a.FELDNR = 021 and a.INHALT <> b.INHALT
AND CAST(CAST(a.PFGLTAB AS VARCHAR) AS DATETIME) <= GETDATE()

Result=this is not right
FIRMA PSNR FELDNR PFLFDNR Cardnr FTE PFGLTAB PFGLTBIS
1 223 021 1 2901009746 1 20110101 20113112
1 223 021 1 2901009746 0,92 20110101 20113112

i need this:
(1, 223, '022', 2, '0,92', 20120101, 20123112)

sorry

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-30 : 20:10:07

You need TWO conditions:

AND CAST(CAST(a.PFGLTAB AS VARCHAR) AS DATETIME) <= GETDATE() 
AND CAST(CAST(a.PFGLTBIS AS VARCHAR(8)) AS DATETIME) >= GETDATE()

And, now that I think about it, that is not quite right either because it will not work correctly on the last day. So make it

AND CAST(CAST(a.PFGLTAB AS VARCHAR) AS DATETIME) <= GETDATE() 
AND CAST(CAST(a.PFGLTBIS AS VARCHAR(8)) AS DATETIME) >= CAST(GETDATE() AS DATE)

Edit: If you are using SQL 2005 or earlier versions, casting to DATE would not work. In that case use this:

AND CAST(CAST(a.PFGLTAB AS VARCHAR) AS DATETIME) <= GETDATE() 
AND CAST(CAST(a.PFGLTBIS AS VARCHAR(8)) AS DATETIME) >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

zero1de
Posting Yak Master

105 Posts

Posted - 2012-01-31 : 03:55:23

This is the Table with different Date values.
FIRMA PSNR FELDNR PFLFDNR INHALT PFGLTAB PFGLTBIS
1 223 021 1 2901009746 20110101 20120101
1 223 021 2 11111111111 20120101 20320101
1 223 022 1 1 20110101 20120101
1 223 022 2 0,9 20120101 20120130
1 223 022 3 0,78 20120130 20320101

The View give following:
FIRMA PSNR FELDNR PFLFDNR Cardnr FTE PFGLTAB PFGLTBIS
1 223 021 2 11111111111 2901009746 20120101 20320101
1 223 021 2 11111111111 1 20120101 20320101
1 223 021 2 11111111111 0,9 20120101 20320101
1 223 021 2 11111111111 0,78 20120101 20320101
the last row is the right one becouse as you can see in the table
thrue the PFGLTAB and PFGLTBIS Date condition you should get only the last one row ! how can i eleminate the rest !?

zero1de
Posting Yak Master

105 Posts

Posted - 2012-01-31 : 04:49:00

THX this is the Answer !!
With the following condition it works and i get the right one selected true last date.

SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS Cardnr,
b.INHALT AS FTE,
a.PFGLTAB,
a.PFGLTBIS
FROM PNPERFELD a, PNPERFELD b
WHERE a.PSNR = b.PSNR
AND a.FIRMA = b.FIRMA
AND a.FELDNR = 021
AND a.INHALT <> b.INHALT
AND CAST(CAST(a.PFGLTAB AS VARCHAR) AS DATETIME) <= GETDATE()
AND CAST(CAST(a.PFGLTBIS AS VARCHAR(8)) AS DATETIME) >= CAST(GETDATE() AS DATE)
and b.PSNR = a.PSNR
AND b.FIRMA = a.FIRMA
AND b.FELDNR = 022
AND b.INHALT <> a.INHALT
AND CAST(CAST(b.PFGLTAB AS VARCHAR) AS DATETIME) <= GETDATE()
AND CAST(CAST(b.PFGLTBIS AS VARCHAR(8)) AS DATETIME) >= CAST(GETDATE() AS DATE)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 12:53:38

see the amout of unnecessary cast operations you've to use because of not having correct datatypes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-02-01 : 03:39:18

Yes you're right but what can I do? if the data are given so! Nevertheless, many thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources