need help to group this view correctly

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

need help to group this view correctly

Author

Topic

dhilmer
Starting Member

3 Posts

Posted - 2012-03-27 : 16:40:27

I can't get the below view to work the way I want. I want one row for each order number and I am getting two..1 for first date and 1 for last date. I can't figure this out, so please help!

SELECT dbo.OEORDHDR_SQL.ord_no, CASE WHEN EDI_01.dbo.[303v850d].DTM_01 = N'063' THEN (CONVERT(VARCHAR(8),
dbo.mDate(EDI_01.dbo.[303v850d].DTM_02), 1)) ELSE '' END AS First_arrival_date,
CASE WHEN EDI_01.dbo.[303v850d].DTM_01 = N'064' THEN (CONVERT(VARCHAR(8), dbo.mDate(EDI_01.dbo.[303v850d].DTM_02), 1))
ELSE '' END AS Last_arrival_date
FROM dbo.OESHPLDT_SQL RIGHT OUTER JOIN
dbo.OEEDIREF_SQL RIGHT OUTER JOIN
EDI_01.dbo.[303v850d] ON dbo.OEEDIREF_SQL.Po_Id = EDI_01.dbo.[303v850d].PO_ID LEFT OUTER JOIN
dbo.OEORDHDR_SQL ON dbo.OEEDIREF_SQL.Ord_No = dbo.OEORDHDR_SQL.ord_no ON
dbo.OESHPLDT_SQL.cus_no = dbo.OEORDHDR_SQL.cus_no AND dbo.OESHPLDT_SQL.cus_alt_adr_cd = dbo.OEORDHDR_SQL.cus_alt_adr_cd AND
dbo.OESHPLDT_SQL.ship_via_cd = dbo.OEORDHDR_SQL.ship_via_cd
WHERE (dbo.mDate(dbo.OEORDHDR_SQL.entered_dt) = CONVERT(CHAR(8), GETDATE(), 112)) AND (dbo.OEORDHDR_SQL.status >= '4') AND
(dbo.OEORDHDR_SQL.ord_type = 'O') AND (dbo.OEORDHDR_SQL.cus_no = '000000871207')

Dawn Hilmer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 16:47:08

[code]
SELECT dbo.OEORDHDR_SQL.ord_no,
MAX( CASE WHEN EDI_01.dbo.[303v850d].DTM_01 = N'063' THEN (CONVERT(VARCHAR(8),
dbo.mDate(EDI_01.dbo.[303v850d].DTM_02), 1)) ELSE '' END) AS First_arrival_date,
MAX( CASE WHEN EDI_01.dbo.[303v850d].DTM_01 = N'064' THEN (CONVERT(VARCHAR(8), dbo.mDate(EDI_01.dbo.[303v850d].DTM_02), 1))
ELSE '' END) AS Last_arrival_date
FROM dbo.OESHPLDT_SQL RIGHT OUTER JOIN
dbo.OEEDIREF_SQL RIGHT OUTER JOIN
EDI_01.dbo.[303v850d] ON dbo.OEEDIREF_SQL.Po_Id = EDI_01.dbo.[303v850d].PO_ID LEFT OUTER JOIN
dbo.OEORDHDR_SQL ON dbo.OEEDIREF_SQL.Ord_No = dbo.OEORDHDR_SQL.ord_no ON
dbo.OESHPLDT_SQL.cus_no = dbo.OEORDHDR_SQL.cus_no AND dbo.OESHPLDT_SQL.cus_alt_adr_cd = dbo.OEORDHDR_SQL.cus_alt_adr_cd AND
dbo.OESHPLDT_SQL.ship_via_cd = dbo.OEORDHDR_SQL.ship_via_cd
WHERE (dbo.mDate(dbo.OEORDHDR_SQL.entered_dt) = CONVERT(CHAR(8), GETDATE(), 112)) AND (dbo.OEORDHDR_SQL.status >= '4') AND
(dbo.OEORDHDR_SQL.ord_type = 'O') AND (dbo.OEORDHDR_SQL.cus_no = '000000871207
GROUP BY dbo.OEORDHDR_SQL.ord_no
[/code]

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 16:51:28

Not to be rude...but this is a mess


     SELECT dbo.OEORDHDR_SQL.ord_no
			, CASE  WHEN EDI_01.dbo.[303v850d].DTM_01 = N'063' 
					THEN (CONVERT(VARCHAR(8), dbo.mDate(EDI_01.dbo.[303v850d].DTM_02), 1)) 
					ELSE '' 
			  END AS First_arrival_date
			, CASE  WHEN EDI_01.dbo.[303v850d].DTM_01 = N'064' 
				    THEN (CONVERT(VARCHAR(8), dbo.mDate(EDI_01.dbo.[303v850d].DTM_02), 1)) 
					ELSE '' 
			  END AS Last_arrival_date
	FROM dbo.OESHPLDT_SQL
RIGHT JOIN dbo.OEEDIREF_SQL  
RIGHT JOIN EDI_01.dbo.[303v850d] 
		ON dbo.OEEDIREF_SQL.Po_Id = EDI_01.dbo.[303v850d].PO_ID 
 LEFT JOIN dbo.OEORDHDR_SQL 
		ON dbo.OEEDIREF_SQL.Ord_No = dbo.OEORDHDR_SQL.ord_no 
		ON dbo.OESHPLDT_SQL.cus_no = dbo.OEORDHDR_SQL.cus_no 
	   AND dbo.OESHPLDT_SQL.cus_alt_adr_cd = dbo.OEORDHDR_SQL.cus_alt_adr_cd 
	   AND dbo.OESHPLDT_SQL.ship_via_cd = dbo.OEORDHDR_SQL.ship_via_cd
	 WHERE (dbo.mDate(dbo.OEORDHDR_SQL.entered_dt) = CONVERT(CHAR(8), GETDATE(), 112)) 
	   AND (dbo.OEORDHDR_SQL.status >= '4') 
	   AND (dbo.OEORDHDR_SQL.ord_type = 'O') 
	   AND (dbo.OEORDHDR_SQL.cus_no = '000000871207')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dhilmer
Starting Member

3 Posts

Posted - 2012-03-27 : 16:55:05

Perfect....seriously, I knew it was something simple. Thank you so much for your help!!

Dawn Hilmer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 16:56:22

quote:
Originally posted by dhilmer

Perfect....seriously, I knew it was something simple. Thank you so much for your help!!

Dawn Hilmer

hope you read what Brett posted
To reiterate, The code within view is a real mess

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

dhilmer
Starting Member

3 Posts

Posted - 2012-03-27 : 18:12:42

I am just starting to learn.... It may be a mess and as I learn, I will go back and straighten my messes. Thanks again for your help!!

Dawn Hilmer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 19:49:23

quote:
Originally posted by dhilmer

I am just starting to learn.... It may be a mess and as I learn, I will go back and straighten my messes. Thanks again for your help!!

Dawn Hilmer

Welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources