Sql Statment in SQL2008

Hi Experts,
I need help pulling data from my SQL Database so here is my table structure and some data and I will tell you what I expect to get :

Table 1: Table Name SOP10100
Table 2: Table Name SOP10102
Table 3: Table Name GL00105

-------------------------------------------------------

SOP 10100
Fields name:
SopNumbr   InvoiceDate   SopType   CustName
101                       01/02/14            3            test1
102                       02/02/14            3            test2
103                       01/10/14            4            test3


SOP10102
fields name:
SopType         SopNumbr     ACTINDX     CREDIT                    DEBIT
3                       101                  3433            1110.00000            0.00000
3                       101                  140                    0.00000             1110.00000
3                       101                  3357             0.00000                  15.00000
3                       101                  3345             120.00000              0.00000
3                       102                  3345             125.00000              0.00000
3                       102                  3357             0.00000                  15.00000
4                       103                  3357             0.00000                   150.00000
4                       103                  3345             0.00000                    12.00000

GL00105
Fields name:

ActIndx         ACTNumbr_3
3357                 4200
3345                  4100
5600                  3200
4500                  2000
3343                   140

----------------------------------------------

now what I need is to have all invoices from 1/1/2014 until 7/25/2014  with SopNumbr, Custname, InvoiceDate, and SOPType from Table 1 and also including Credit or DEBIT from Table 2 and this is accomplish by looking at SOPType if SOPType is 3 then pull credit , if SOPTYPE is 4 pull DEBIT . now to make it even more complicate there is multiple lines on the Table 2 for the same invoice , what I need is all of those records that have an ACTNumbr_3 bigger than 4000 but less than 5000 and table 2 is link to table 3 by ACTIndx that's where we match the ACTNumbr_3,also all those credit or debit needs to be added together.

so here is my output from those data:

sopNumbr          Custname        InvoiceDate  SopType     COST (Credit or Debit)
101                       test1                  01/02/14        3                1230.00000                        <----------this 1110+120
102                       test2                  02/02/14        3                 125.00000
103                       test3                  01/10/14        4                 162.00000                         <------------this is 150+12



I hope it makes sense. let me know if you need some clarification. thanks in advanced.
tavernyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John_VidmarCommented:
SELECT	a.SopNumbr
,	a.Custname
,	a.InvoiceDate
,	a.SOPType
,	COST		=	(	SELECT	SUM(CASE WHEN b.SopType = 3 THEN b.CREDIT ELSE b.DEBIT END)
					FROM	SOP10102	b	
					JOIN	GL00105		c	ON	a.sopNumbr = b.sopNumbr
									AND	a.SopType = b.SopType
									AND	b.ActIndx = c.ActIndx
					WHERE	ACTNumbr_3 > 4000
					AND	ACTNumbr_3 < 5000
				)
FROM	SOP10100	a 
WHERE	a.InvoiceDate BETWEEN 'Jan 1, 2014' AND 'Jul 25, 2014'

Open in new window

0
PortletPaulfreelancerCommented:
Your expected results cannot be matched because ACTNUMBR_3 isn't available for the 1110 credit transaction
| SOPNUMBR | ACTINDX | C_ACTINDX | ACTNUMBR_3 | COST |
|----------|---------|-----------|------------|------|
|      101 |     140 |    (null) |     (null) |    0 |
|      101 |    3433 |      3433 |        140 | 1110 |
|      101 |    3345 |      3345 |       4100 |  120 |
|      101 |    3357 |      3357 |       4200 |    0 |
|      102 |    3345 |      3345 |       4100 |  125 |
|      102 |    3357 |      3357 |       4200 |    0 |
|      103 |    3345 |      3345 |       4100 |   12 |
|      103 |    3357 |      3357 |       4200 |  150 |

Open in new window

The result I achieved is:
| SOPNUMBR | CUSTNAME |                     INVOICEDATE | SOPTYPE | COST |
|----------|----------|---------------------------------|---------|------|
|      101 |    test1 |  January, 02 2014 00:00:00+0000 |       3 |  120 |
|      102 |    test2 | February, 02 2014 00:00:00+0000 |       3 |  125 |
|      103 |    test3 |  January, 10 2014 00:00:00+0000 |       4 |  162 |

Open in new window

Despite this I believe the query is as follows:
SELECT
      a.SopNumbr
    , a.Custname
    , a.InvoiceDate
    , a.SOPType
    , b.COST
FROM SOP10100 a
     LEFT JOIN (
                  SELECT
                        b.sopNumbr
                      , SUM(CASE
                              WHEN b.SopType = 3 THEN b.CREDIT
                              ELSE b.DEBIT END) as cost
                  FROM SOP10102 b
                        JOIN GL00105 c
                                    ON b.ActIndx = c.ActIndx
                  WHERE c.ACTNumbr_3 >= 4000
                        AND c.ACTNumbr_3 < 5000
                  GROUP BY
                        b.sopNumbr
            ) b
                  ON a.sopNumbr = b.sopNumbr
WHERE a.InvoiceDate >= '2014-01-01' AND a.InvoiceDate < '2014-07-26' /* nb: one day more than you asked for */

Open in new window

Note I consider it best practice to AVOID using between for date ranges, for more on that topic please see: "Beware of Between"

See: http://sqlfiddle.com/#!9/5bae9/1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
IF the GL00105 table is amended, like this:
CREATE TABLE GL00105
	(`ActIndx` int, `ACTNumbr_3` int)
;
	
INSERT INTO GL00105
	(`ActIndx`, `ACTNumbr_3`)
VALUES
	(3357, 4200),
	(3345, 4100),
	(5600, 3200), 
	(4500, 2000),
	(3433, 4140)
;

Open in new window

Then you expected result is provided by the query above.
see: http://sqlfiddle.com/#!9/623e7/1
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

tavernyAuthor Commented:
Thank you for your prompt response , both solutions worked but PortletPaul you actually got it right by including the group by.
John Vidmar , your solution was closed but it was returning multiple line for each invoice , the group by solved it.

I have another question that I will open I need to add one more request on the sql statement that I have , as soon as I post it I will post the link here .
Thank you
select 
	left(A.SOPNUMBE,12) as "InvoiceNumber"
	, A.DOCDATE as "InvoiceDate"
	, (select case when a.SOPTYPE='3'then 'SO' else 'RM' end) as "TransType"
	, left(a.LOCNCODE,4) as "Whseld"
	, left(a.SLPRSNID,4) as "OutsideSlsrep"
	, left(a.CUSTNMBR,20) as "CustId"
	, left(a.CUSTNAME,30) as "CustName"
	, left(a.ShipToName,30) as "ShipToName"
	, left(a.ADDRESS1,30) as "ShipToAdd1"
	, left(a.ADDRESS2,30) as "ShipToAddr2"
	, left(a.CITY,30) as "ShipToCity"
	, left(a.STATE,2) as "SHipToState"
	, left(a.ZIPCODE,10) as "ShipToZip"
	, left(a.CCode,2) as "CountryCode"
	, a.CMMSLAMT as "TotInvAmt"
	, b.TotInvCst
	, 0 as TotLines
	, 0 as FrtIn
	, a.FRTAMNT as "FrtOut"
	, a.MISCAMNT as "HandleChg"
	, a.TAXAMNT as "Tax"
	, 0 as Surcharge
	, 10 as Companyid
FROM SOP30200 a
     LEFT JOIN (
                  SELECT
                        b.SOPNUMBE
                      , SUM(CASE
                              WHEN b.SopType = 3 THEN b.DEBITAMT
                              ELSE b.CRDTAMNT END) as TotInvCst
                  FROM SOP10102 b
                        JOIN GL00105 c
                                    ON b.ActIndx = c.ActIndx
                  WHERE c.ACTNumbr_3 >= 4000
                        AND c.ACTNumbr_3 < 5000
                  GROUP BY
                        b.SOPNUMBE
            ) b
                  ON a.SOPNUMBE = b.SOPNUMBE
                
WHERE a.Docdate >= '2014-07-01' AND a.DocDate < '2014-07-26' /* nb: one day more than you asked for */

Open in new window

0
tavernyAuthor Commented:
Great solution with explanation.
thank you.
0
tavernyAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.