SQL Server : complicated query

7z5jn7bk 于 2023-02-18 发布在 SQL Server

关注(0)|答案(1)|浏览(189)

I have two tables document and documentd ; the first one contains the numbers of the invoices doc_num as primary key, document types doc_type (FACA, BLCO, BLCM, BLCK .....) and the document date doc_date .

Each invoice has one DOC_TYPE and one date, and each date my be contained in one or more invoices.

Table DOCUMENT :
| DOC_NUM | DOC_TYPE | DOC_DATE |
| ------------ | ------------ | ------------ |
| INVOICE1901221 | FACA | 22/01/2019 |
| INVOICE1902221 | FACA | 22/02/2019 |
| INVOICE1902222 | FACA | 22/02/2019 |
| INVOICE1903221 | FACA | 22/03/2019 |
| BLCO190122001 | BLCO | 22/01/2019 |
| BLCO190123001 | BLCO | 23/01/2019 |
| BLCM190122001 | BLCM | 22/01/2019 |
| INVOICE1901021 | FACA | 02/01/2019 |
| INVOICE1903011 | FACA | 01/03/2019 |
| INVOICE1904221 | FACA | 22/04/2019 |
| INVOICE1904222 | FACA | 22/04/2019 |

The second table is the details of each invoices he contains as foreign key doc_num the code of products for each invoice art_code and finally the prices of the products art_price .

Table DOCUMENTD :
| DOC_NUM | ART_CODE | ART_PRICE |
| ------------ | ------------ | ------------ |
| INVOICE1901221 | PRODUCT1 | 1000 |
| INVOICE1901221 | PRODUCT2 | 2000 |
| INVOICE1902221 | PRODUCT3 | 950 |
| INVOICE1902221 | PRODUCT4 | 980 |
| INVOICE1904221 | PRODUCT1 | 1200 |
| INVOICE1903011 | PRODUCT2 | 900 |
| BLCO190122001 | ARTICLE1 | 900 |
| BLCO190123001 | ARTICLE2 | 800 |

[DOCUMENTD TABLE][2]

My goal in first step is to join the two tables using doc_num selects all FACA type invoices and their products except the prices they must be THE LAST UPDATED PRICE IN FACA TYPE.

RESULT:
| | | | |
| ------------ | ------------ | ------------ | ------------ |
| INVOICE1904221 | PRODUCT1 | 1200 | 22/04/2019 |
| INVOICE1903011 | PRODUCT2 | 900 | 01/03/2019 |
| INVOICE1902221 | PRODUCT3 | 950 | 22/02/2019 |
| INVOICE1902221 | PRODUCT4 | 980 | 22/02/2019 |

The second step I have another table how contain ORDER and ART_CODE :

ORDER	ART_CODE
1	PRODUCT1
2	PRODUCT2
3	PRODUCT3

I want to fetch the first result depend on this table:


INVOICE1904221	PRODUCT1	1200	22/04/2019
INVOICE1903011	PRODUCT2	900	01/03/2019
INVOICE1902221	PRODUCT3	950	22/02/2019

I try this but he fetch same product with different prices

SELECT 
    d1.DOC_NUM, dd1.ART_CODE, dd2.ART_PRICE, d2.DOC_DATE 
FROM
    document d1 
INNER JOIN 
    documentd dd1 ON dd1.DOC_NUM = d1.DOC_NUM
INNER JOIN 
    documentd dd2 ON dd2.ART_CODE = dd1.ART_CODE
INNER JOIN 
    document d2 ON d2.DOC_NUM = dd2.DOC_NUM 
                AND d2.DOC_TYPE <> d1.DOC_TYPE
WHERE 
    d1.DOC_TYPE = 'FACA'

sql-server

来源：https://stackoverflow.com/questions/75460767/sql-server-complicated-query

1条答案

按热度按时间

6vl6ewon1#

@MOHAMED NEJI it's really hard to understand what you meant. Please try to be more clear on your questions. The proposed solution below shows how to get the results you asked in both RESULTS tables with the input data that you gave.

CREATE TABLE #DOCUMENT (
    DOC_NUM VARCHAR(30)
    , DOC_TYPE  CHAR(4)
    , DOC_DATE DATE)
CREATE TABLE #DOCUMENTD (
    DOC_NUM VARCHAR(30)
    , ART_CODE VARCHAR(20)
    , ART_PRICE DECIMAL(10,4))
CREATE TABLE #OTHERTABLE(
    [ORDER] int  
    , ART_CODE VARCHAR(20)
)

INSERT INTO #DOCUMENT
          SELECT 'INVOICE1901221' DOC_NUM, 'FACA' DOC_TYPE, '2019-01-22' DOC_DATE
UNION ALL SELECT 'INVOICE1902221' DOC_NUM, 'FACA' DOC_TYPE, '2019-02-22' DOC_DATE
UNION ALL SELECT 'INVOICE1902222' DOC_NUM, 'FACA' DOC_TYPE, '2019-02-22' DOC_DATE
UNION ALL SELECT 'INVOICE1903221' DOC_NUM, 'FACA' DOC_TYPE, '2019-03-22' DOC_DATE
UNION ALL SELECT 'BLCO190122001'  DOC_NUM, 'BLCO' DOC_TYPE, '2019-01-22' DOC_DATE
UNION ALL SELECT 'BLCO190123001'  DOC_NUM, 'BLCO' DOC_TYPE, '2019-01-23' DOC_DATE
UNION ALL SELECT 'BLCM190122001'  DOC_NUM, 'BLCM' DOC_TYPE, '2019-01-22' DOC_DATE
UNION ALL SELECT 'INVOICE1901021' DOC_NUM, 'FACA' DOC_TYPE, '2019-01-02' DOC_DATE
UNION ALL SELECT 'INVOICE1903011' DOC_NUM, 'FACA' DOC_TYPE, '2019-03-01' DOC_DATE
UNION ALL SELECT 'INVOICE1904221' DOC_NUM, 'FACA' DOC_TYPE, '2019-04-22' DOC_DATE
UNION ALL SELECT 'INVOICE1904222' DOC_NUM, 'FACA' DOC_TYPE, '2019-04-22' DOC_DATE

INSERT INTO #DOCUMENTD
          SELECT 'INVOICE1901221' DOC_NUM, 'PRODUCT1' ART_CODE, 1000  ATR_PRICE
UNION ALL SELECT 'INVOICE1901221' DOC_NUM, 'PRODUCT2' ART_CODE, 2000  ATR_PRICE
UNION ALL SELECT 'INVOICE1902221' DOC_NUM, 'PRODUCT3' ART_CODE, 950   ATR_PRICE
UNION ALL SELECT 'INVOICE1902221' DOC_NUM, 'PRODUCT4' ART_CODE, 980   ATR_PRICE
UNION ALL SELECT 'INVOICE1904221' DOC_NUM, 'PRODUCT1' ART_CODE, 1200  ATR_PRICE
UNION ALL SELECT 'INVOICE1903011' DOC_NUM, 'PRODUCT2' ART_CODE, 900   ATR_PRICE
UNION ALL SELECT 'BLCO190122001'  DOC_NUM, 'ARTICLE1' ART_CODE, 900   ATR_PRICE
UNION ALL SELECT 'BLCO190123001'  DOC_NUM, 'ARTICLE2' ART_CODE, 800   ATR_PRICE

INSERT INTO #OTHERTABLE
          SELECT 1 [ORDER], 'PRODUCT1' ART_CODE
UNION ALL SELECT 2 [ORDER], 'PRODUCT2' ART_CODE
UNION ALL SELECT 3 [ORDER], 'PRODUCT3' ART_CODE

;WITH Docs AS (
    SELECT  dd.DOC_NUM, dd.ART_CODE, dd.ART_PRICE, d.DOC_DATE ,ROW_NUMBER() OVER (PARTITION BY  art_code ORDER BY DOC_DATE DESC ) rn
    FROM #DOCUMENTD  dd
    INNER JOIN #DOCUMENT d 
        ON d.DOC_NUM = dd.DOC_NUM
    WHERE DOC_TYPE =  'FACA'
)
SELECT  DOC_NUM, ART_CODE, ART_PRICE, DOC_DATE 
FROM Docs
WHERE rn = 1

;WITH Docs AS (
    SELECT  dd.DOC_NUM, dd.ART_CODE, dd.ART_PRICE, d.DOC_DATE ,ROW_NUMBER() OVER (PARTITION BY  art_code ORDER BY DOC_DATE DESC ) rn
    FROM #DOCUMENTD  dd
    INNER JOIN #DOCUMENT d 
        ON d.DOC_NUM = dd.DOC_NUM
    WHERE DOC_TYPE =  'FACA'
)
SELECT  DOC_NUM, Docs.ART_CODE, ART_PRICE, DOC_DATE 
FROM Docs
INNER JOIN #OTHERTABLE ot
   ON ot.ART_CODE = Docs.ART_CODE
WHERE rn = 1

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server : complicated query

1条答案

相关问题

热门标签

最新问答