SQL Server SQL Help - Sales by State (SAP Business One)

e5nqia27 于 2023-06-04 发布在其他

关注(0)|答案(2)|浏览(121)

Wondering if anyone can help me with a query that I'm trying to use to extract sales information from my database. I'm trying to build a report to show all sales by state. I have come across this code and while it is close to what I need it doesn't go the whole way.

Along with the sales from each state I need to include the warehouses (T1.[WhsCode]) that stock was sold from. I also need to sum each column (I know that you can press Ctrl and Click to get totals at the bottom of each column but would like them to appear automatically on the final report). Can anyone help?

Here is the query so far:

SELECT  T0.State1 AS 'Bill-to State',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 1 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'JAN Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 2 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'FEB Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 3 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'MAR Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 4 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'APR Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 5 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'MAY Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 6 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'JUN Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 7 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'JUL Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 8 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'AUG Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 9 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'SEP Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 10 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'OCT Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 11 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'NOV Amt',
        (
          SELECT    SUM(T1.DocTotal)
          FROM      OINV T1 WITH ( NOLOCK )
          INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
          WHERE     MONTH(T1.DOCDATE) = 12 AND
                    T2.State1 = T0.State1 AND
                    YEAR(T1.DOCDATE) = YEAR(GETDATE())
        ) AS 'DEC Amt'
FROM    dbo.OCRD T0
LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode
GROUP BY T0.State1
ORDER BY T0.State1

sql-server

来源：https://stackoverflow.com/questions/23221302/sql-help-sales-by-state-sap-business-one

2条答案

按热度按时间

5tmbdcev1#

Rather use SQL Pivot:

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

赞(0）回复(0）举报 2023-06-04

ttisahbt2#

You could use a conditional aggregate:

SELECT  [Bill-To-State] =  ISNULL(T0.State1, 'Total'),
        Warehouse = T1.[WhsCode],
        [Jan Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 1 THEN T1.DocTotal ELSE 0 END),
        [Feb Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 2 THEN T1.DocTotal ELSE 0 END),
        [Mar Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 3 THEN T1.DocTotal ELSE 0 END),
        [Apr Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 4 THEN T1.DocTotal ELSE 0 END),
        [May Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 5 THEN T1.DocTotal ELSE 0 END),
        [Jun Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 6 THEN T1.DocTotal ELSE 0 END),
        [Jul Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 7 THEN T1.DocTotal ELSE 0 END),
        [Aug Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 8 THEN T1.DocTotal ELSE 0 END),
        [Sep Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 9 THEN T1.DocTotal ELSE 0 END),
        [Oct Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 10 THEN T1.DocTotal ELSE 0 END),
        [Nov Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 11 THEN T1.DocTotal ELSE 0 END),
        [Dec Amt] = SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 12 THEN T1.DocTotal ELSE 0 END)
FROM    dbo.OCRD T0
        LEFT JOIN dbo.OINV T1 
            ON T1.CardCode = T0.CardCode
WHERE   T1.DocDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND     T1.DocDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
GROUP BY GROUPING SETS ((T0.State1, T1.[WhsCode]), ());

N.B I have removed the deprecated literals for aliases with my preferred method of Alias = Expression . I prefer this because I find it easier to be able to see what a column is called immediately, rather than having to scan to the right to find the end of the column. This is entirely personal preference and if you are more comfortable with expression AS Alias then you should use the following, as it still avoids using literals.

SELECT  ISNULL(T0.State1, 'Total') AS [Bill-To-State],
        T1.[WhsCode] AS Warehouse,
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 1 THEN T1.DocTotal ELSE 0 END) AS [Jan Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 2 THEN T1.DocTotal ELSE 0 END) AS [Feb Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 3 THEN T1.DocTotal ELSE 0 END) AS [Mar Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 4 THEN T1.DocTotal ELSE 0 END) AS [Apr Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 5 THEN T1.DocTotal ELSE 0 END) AS [May Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 6 THEN T1.DocTotal ELSE 0 END) AS [Jun Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 7 THEN T1.DocTotal ELSE 0 END) AS [Jul Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 8 THEN T1.DocTotal ELSE 0 END) AS [Aug Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 9 THEN T1.DocTotal ELSE 0 END) AS [Sep Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 10 THEN T1.DocTotal ELSE 0 END) AS [Oct Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 11 THEN T1.DocTotal ELSE 0 END) AS [Nov Amt],
        SUM(CASE WHEN DATEPART(MONTH, T1.DocDate) = 12 THEN T1.DocTotal ELSE 0 END) AS [Dec Amt]
FROM    dbo.OCRD T0
        LEFT JOIN dbo.OINV T1 
            ON T1.CardCode = T0.CardCode
WHERE   T1.DocDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND     T1.DocDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
GROUP BY GROUPING SETS ((T0.State1, T1.[WhsCode]), ());

Also note that I have replaced

WHERE YEAR(T1.DocDate) = YEAR(GETDATE())

With

WHERE   T1.DocDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND     T1.DocDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)

This may seem long winded but it will allow the optimser to use any indexes on t1.DocDate because the YEAR function does not have to be performed on every row.

One other thing to not is the use of

GROUP BY GROUPING SETS ((T0.State1, T1.[WhsCode]), ());

This is what will add your total row, you essential have two result sets, one grouping by " (T0.State1, T1.[WhsCode]) ", and one grouping by nothing " () ". In this total row both T0.State1 and T1.[WhsCode] will be null, so I have used ISNULL(T0.State1, 'Total') to make it display total in this row.

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server SQL Help - Sales by State (SAP Business One)

2条答案

相关问题

热门标签

最新问答