Subquery Madness - 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

Subquery Madness

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-01-29 : 22:10:25

Here is my main query. Please notice the area I've bolded.

SELECT 'XXX' AS [BU Name],
       somast.fsono AS [Sales Order #],
       ' ' AS [Sales Order Type],
       somast.forderdate AS [Sales Order Date],
       aritem.fpartno AS [Part Number],
       aritem.FMDESCRIPT AS [Part Description],
       dbo.aritem.FPRODCL AS [Product Class],
       '' AS [Group Code],
       armast.fcinvoice AS [Invoice Number],
       armast.finvdate AS [Invoice Date],
       aritem.ftotprice AS [Invoice Amount],
       aritem.fordqty AS [Quantity Ordered],
       aritem.fshipqty AS [Quantity Invoiced],
       0 AS [List Price Per Unit],
       dbo.aritem.FTOTTXNPRICE AS [Net/Invoice Price Per Unit],
       0 AS [Discount Per Unit],
       0 AS [COGS Material],
       0 AS [COGS Labor],
       0 AS [COGS Overhead],
       slcdpm.FCUSRCHR1 AS [Customer Type],
       somast.fcustno AS [Customer Number],
       somast.fcompany AS [Customer Name],
       Syaddr.fccountry AS [Region],
       somast.fsoldby AS [Sales Rep]
  FROM                armast
                   INNER JOIN
                      aritem
                   ON armast.fcinvoice = aritem.fcinvoice
                INNER JOIN
                   slcdpm
                ON slcdpm.fcustno = armast.fcustno
             INNER JOIN
                somast
             ON left (aritem.fsokey, 6) = somast.fsono
          INNER JOIN
             sorels
          ON aritem.fsokey = sorels.fsono + sorels.finumber + sorels.frelease
       INNER JOIN
          SYADDR
       ON sorels.fshptoaddr = syaddr.fcaddrkey
          AND somast.fcustno = syaddr.fcaliaskey
        INNER JOIN inmast on inmast.fpartno = aritem.fpartno and inmast.frev = aritem.FREV

Now, the following query (which includes the SORELS table) has the values for those above if the query returns something.

SELECT (jopact.flabact + jopact.flabinv) as [COGS Labor],
(jopact.fmatlact + jopact.fmatlinv) as [COGS Material],
(jopact.fovhdact + jopact.fovhdinv) as [COGS Overhead]
  FROM    sorels
       Inner JOIN
          jomast
       ON sorels.fsono = jomast.fsono
          AND sorels.finumber + sorels.frelease = jomast.fkey
          inner join jopact on jomast.fjobno = jopact.fjobno

If nothing is returned by that query above, the values for those above are found in the INMAST table as INMAST.Material, INMAST.Labor, and INMAST.Overhead and I was thinking of using a coalesce function.

How do I put this all together into a working query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 02:03:00

something like


SELECT 'XXX' AS [BU Name],
       somast.fsono AS [Sales Order #],
       ' ' AS [Sales Order Type],
       somast.forderdate AS [Sales Order Date],
       aritem.fpartno AS [Part Number],
       aritem.FMDESCRIPT AS [Part Description],
       dbo.aritem.FPRODCL AS [Product Class],
       '' AS [Group Code],
       armast.fcinvoice AS [Invoice Number],
       armast.finvdate AS [Invoice Date],
       aritem.ftotprice AS [Invoice Amount],
       aritem.fordqty AS [Quantity Ordered],
       aritem.fshipqty AS [Quantity Invoiced],
       0 AS [List Price Per Unit],
       dbo.aritem.FTOTTXNPRICE AS [Net/Invoice Price Per Unit],
       COALESCE(somefieldhere,0) AS [Discount Per Unit],
       COALESCE(tmp.[COGS Material],0) AS [COGS Material],
       COALESCE(tmp.[COGS Labor],0) AS [COGS Labor],
       COALESCE(tmp.[COGS Overhead],0) AS [COGS Overhead],
       slcdpm.FCUSRCHR1 AS [Customer Type],
       somast.fcustno AS [Customer Number],
       somast.fcompany AS [Customer Name],
       Syaddr.fccountry AS [Region],
       somast.fsoldby AS [Sales Rep]
  FROM                armast
                   INNER JOIN
                      aritem
                   ON armast.fcinvoice = aritem.fcinvoice
                INNER JOIN
                   slcdpm
                ON slcdpm.fcustno = armast.fcustno
             INNER JOIN
                somast
             ON left (aritem.fsokey, 6) = somast.fsono
          INNER JOIN
             sorels
          ON aritem.fsokey = sorels.fsono + sorels.finumber + sorels.frelease
       INNER JOIN
          SYADDR
       ON sorels.fshptoaddr = syaddr.fcaddrkey
          AND somast.fcustno = syaddr.fcaliaskey
        INNER JOIN inmast on inmast.fpartno = aritem.fpartno and inmast.frev = aritem.FREV

LEFT JOIN (SELECT jomast.fsono,(jopact.flabact + jopact.flabinv) as [COGS Labor],
(jopact.fmatlact + jopact.fmatlinv) as [COGS Material],
(jopact.fovhdact + jopact.fovhdinv) as [COGS Overhead]
  FROM    sorels
       Inner JOIN
          jomast
       ON sorels.fsono = jomast.fsono
          AND sorels.finumber + sorels.frelease = jomast.fkey
          inner join jopact on jomast.fjobno = jopact.fjobno

)tmp
ON tmp.fsono=somast.fsono

not sure how you get discount calculation. remeber to include it also in second query and use it

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-01-30 : 08:41:31

Thanks again Visakh. I'm sorry, I haven't worked on discount yet and didn't mean to highlight it as a field to watch.

I'll work on this some more this morning.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 08:46:11

If you are using SQL Server 2005, you can also make use of CROSS APPLY, or OUTER APPLY.

E 12°55'05.63"
N 56°04'39.26"

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-01-30 : 09:24:22

Unfortunately I am using 2000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources