• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

sql 2005 query

i have a table called customerproductinfo
where i have the data like this

SaleInvoiceID      GlobalProductID      SerialNumber      Priority      UnitPrice      UnitCost      AssociationNumber      
46728      14      3234791127      2      0      0      1      
46728      14      3234791181      7      0      0      4      
46728      85      3234791127      4      335      0      1      
46728      85      3234791181      9      335      0      4      
46728      243            3      0      0      1      
46728      243            8      0      0      4      
46728      294      3234791181      11      110      0      7      
46728      469      355751021386726      6      119.99      375      4      
46728      507      359321020456973      1      50      160      1      


is there any way i can ge the data in this format

saleinvoiceid      Globalproductid      serialnumber      unitprice      relatedglobalproductid      relatedserialnumber      runitcost      runitprice
46728      14      3234791127      0      0      0      0      0
46728      14      3234791181      0      0      0      0      0
46728      85      3234791127      335      507      359321020456973      160      50
46728      85      3234791181      335      469      355751021386726      375      119.99
46728      243            0      0      0      0      0
46728      243            0      0      0      0      0
46728      294      3234791181      110      0      0      0      0
0
romeiovasu
Asked:
romeiovasu
  • 3
1 Solution
 
pcelbaCommented:
Yes, but you need another table or column which contains relation between Globalproductid and relatedglobalproductid.
0
 
romeiovasuAuthor Commented:
only associationnumber is reference key is there anyway we can write a store procedure to push the data into temp and make relation and get the output. in they way i have requested.
0
 
pcelbaCommented:
First two rows have associationnumber also but they are not associated probably because they don't have unitprice. Other problem is how to recognize what product to associate as related product. It could be product with higher globalproductid or product with longer serialnumber or something else? Please clarify.
0
 
pcelbaCommented:
How look results from this?
SELECT a.SaleInvoiceId, a.GlobalProductId, a.SerialNumber, a.UnitPrice, 
   ISNULL(b.GlobalProductId, 0) RelatedGlobalProductId, 
   ISNULL(b.SerialNumber, 0) RelatedSerialNumber, 
   ISNULL(b.UnitCost, 0) RUnitCost, ISNULL(b.UnitPrice, 0) RUnitPrice
FROM CustomerProductInfo a
LEFT JOIN CustomerProductInfo b ON b.AssociationNumber = a.AssociationNumber AND a.UnitPrice <> 0 AND a.GlobalProductId < b.GlobalProductId AND a.SaleInvoiceId = b.SaleInvoiceId
WHERE LEN(a.SerialNumber) <= 10 OR a.SerialNumber IS NULL

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now