[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql Statment in SQL2008

Posted on 2014-08-25
6
Medium Priority
?
280 Views
Last Modified: 2014-08-28
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.
0
Comment
Question by:taverny
  • 3
  • 2
6 Comments
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 800 total points
ID: 40284332
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 40285134
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40285191
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:taverny
ID: 40290738
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
 

Author Closing Comment

by:taverny
ID: 40290745
Great solution with explanation.
thank you.
0
 

Author Comment

by:taverny
ID: 40290783
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question