MsSQL

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesMsSQL

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old July 13th, 2012, 05:56 AM
aaviegas aaviegas is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Jul 2012
Posts: 1 aaviegas User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 58 m 48 sec
Reputation Power: 0
Help getting the highest value

HELLO,

I'm trying to create a PHP export module that gets all the information from an SQL database to import it in a Website using MySQL, but cant get the real number of rows because many of them are duplicated.

There are 4 tables in the SQL Query Statment:
dbo.ItemNames
dbo.Item
dbo.Stock
dbo.ItemFirstGroup

Everithing is working fine but when i try to get the OrganizationName from dbo.Supplier so many ItemID's are returned duplicated.
I just want to get the one how's CostPriceDate is the more recent date.

ItemID Description PrecoC Preco2 Preco3 Preco4 Comment Qty FamilyID Marca CostPriceDate OrganizationName

SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2010-10-26 00:00:00.000 AG
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2012-04-07 00:00:00.000 AL
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2012-01-05 00:00:00.000 ES
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2012-06-26 00:00:00.000 JO
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2012-06-08 00:00:00.000 LU
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2011-07-04 00:00:00.000 MA
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2012-04-30 00:00:00.000 MI
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 1900-01-01 00:00:00.000 MO
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2010-12-10 00:00:00.000 MS
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2011-07-04 00:00:00.000 PO
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2012-06-06 00:00:00.000 PU
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2010-10-29 00:00:00.000 RI
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2012-07-06 00:00:00.000 RO
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2011-12-23 00:00:00.000 SE
SGPU SIG PNEUS 0,245 3,5414634 3,5414634 3,5414634 171 14 2011-05-27 00:00:00.000 VO


Can anyone help please?

Here is my SQL Query:

SELECT
ISP.ItemID,
INA.Description,
MAX( CASE WHEN ISP.PriceLineID = 0 THEN UnitPrice ELSE NULL END ) AS PrecoC,
MAX( CASE WHEN ISP.PriceLineID = 2 THEN UnitPrice ELSE NULL END ) AS Preco2,
MAX( CASE WHEN ISP.PriceLineID = 3 THEN UnitPrice ELSE NULL END ) AS Preco3,
MAX( CASE WHEN ISP.PriceLineID = 4 THEN UnitPrice ELSE NULL END ) AS Preco4,
CAST (I.Comments AS VARCHAR(250)) AS Comment, --converte para VARCHAR
MAX(CASE WHEN S.WarehouseID = 2 THEN S.AvailableQty ELSE NULL END) AS Qty,
I.FamilyID,
IFG.Description AS Marca,
MAX ( CASE WHEN ISU.ItemID=I.ItemID THEN ISU.CostPriceDate ELSE NULL END) AS CostPriceDate,
SU.OrganizationName
FROM
ItemSellingPrices ISP
INNER JOIN ItemNames INA ON
ISP.ItemID=INA.ItemID
INNER JOIN Item I ON
ISP.ItemID=I.ItemID
INNER JOIN Stock S ON
ISP.ItemID=S.ItemID
INNER JOIN (SELECT DISTINCT ItemID , SupplierID , CostPriceDate FROM ItemSupplier
WHERE CONVERT(VARCHAR,CostPriceDate,120) + CAST(SupplierID AS VARCHAR(250)) IN
(
SELECT MAX(CONVERT(VARCHAR,CostPriceDate,120) + CAST(SupplierID AS VARCHAR(250)))
FROM ItemSupplier
GROUP BY ItemID
)
) ISU ON
ISP.ItemID=ISU.ItemID
INNER JOIN ItemFirstGroup IFG ON
IFG.ItemFirstGroupID=I.ItemFirstGroupID
INNER JOIN Supplier SU ON
ISU.SupplierID=SU.SupplierID
WHERE
INA.LanguageID='PTG' AND
I.ItemID!='0' AND
I.ItemID!='X'
GROUP BY
ISU.ItemID,
INA.Description,
I.FamilyID,
IFG.Description,
ISP.ItemID,
SU.OrganizationName,
CAST (I.Comments AS VARCHAR(250))
ORDER BY ISP.ItemID

Reply With Quote
  #2  
Old July 13th, 2012, 08:59 AM
jamespayne's Avatar
jamespayne jamespayne is offline
Editor-in-Beef
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Sep 2008
Location: South Florida
Posts: 31 jamespayne User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 48 m 44 sec
Reputation Power: 10
I would suggest you post this question at our sister site, www.forums.devshed.com

You will have to create a new username, but we have many PHP and MSSQL experts that would be more than happy to assist you.

Hope to see you there!

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesMsSQL > Help getting the highest value


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap