Member Avatar for Geekitygeek

Hi, this is giving me a major headache and my boss wants the project wrapped up asap : /

I have two views pulling data from several tables. The end result is this:

CustomerTotals
CustomerID numeric(18,0),
ProductID numeric(18,0),
Delivered numeric(5,0),
Ordered numeric(5,0)

SupplierTotals
SupplierID numeric(18,0),
ProductID numeric(18,0),
Delivered numeric(5,0),
Ordered numeric(5,0)

CustomerTotals shows total of orders we have recieved and items we have sent out. SupplierTotals shows the total we have ordered from our suppliers and how many we have so far received.

I need to calculate some values from these but i tried to create the following view and each row is duplicated : /

SELECT     ISNULL(st.ProductID, ct.ProductID) AS 'ID', ISNULL(st.Ordered, 0) - ISNULL(st.Delivered, 0) AS [On Order], ISNULL(st.Delivered, 0) - ISNULL(ct.Delivered, 0) 
                      AS [In Stock], ISNULL(ct.Ordered, 0) - ISNULL(ct.Delivered, 0) AS [Orders Outstanding], ISNULL(st.Ordered, 0) - ISNULL(ct.Ordered, 0) AS Unassigned
FROM         dbo.SupplierTotals AS st FULL OUTER JOIN
                      dbo.CustomerTotals AS ct ON st.ProductID = ct.ProductID

Can anyone shed some light on where i'm going wrong? C#.net is my strong suit...SQL is still a very alien language to me lol

  • 1 Contributor
  • 1 Reply
  • 129 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Geekitygeek
Member Avatar for Geekitygeek

Doh, figured it out...one of the view was returning multiple lines so the join had duplicates. i've fixed this now..sorry for wasting anyones time..like i said, SQL is still a bit hit and miss for me :)

Thanks to anyone who took the time to read this.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.