cancel
Showing results for 
Search instead for 
Did you mean: 

Trying to cast correct layout in query manager

jbrotto
Active Contributor
0 Kudos
96

Making a report with sections in the query manager but my issue is converting year to an int value and sales amount as 2 decimals. I am working on the header portion where column 1 will be a combiination of cardcode and cardname. The other columns are going to be blank

jbrotto
Active Contributor
0 Kudos

My code where only the years in the middle are being converted to money

SELECT T0.CardCode ,NULL AS ' ',NULL AS ' ',NULL AS ' ',NULL AS ' ' FROM OCRD T0 WHERE T0.[CARDNAME] = '[%0]' UNION ALL SELECT T0.CardName ,NULL AS ' ',NULL AS ' ',NULL AS ' ',NULL AS ' ' FROM OCRD T0 WHERE T0.[CARDNAME] = '[%0]' UNION ALL SELECT NULL AS ' ', CAST('2022' AS VARCHAR) AS [2022],CAST('2021' AS VARCHAR) AS [2021],CAST('2020' AS VARCHAR) [2020],CAST('2019' AS VARCHAR) AS [2019] UNION ALL SELECT INSIDE.[Months], CAST(INSIDE.[2022] AS MONEY), CAST(INSIDE.[2021] AS MONEY), CAST(INSIDE.[2020] AS MONEY), CAST(INSIDE.[2019] AS MONEY) FROM ( SELECT TheMonth, Months, [2022], [2021], [2020], [2019] FROM ( SELECT CASE WHEN MONTH(T0.[DocDate]) = 1 THEN 'January' WHEN MONTH(T0.[DocDate]) = 2 THEN 'February' WHEN MONTH(T0.[DocDate]) = 3 THEN 'March' WHEN MONTH(T0.[DocDate]) = 4 THEN 'April' WHEN MONTH(T0.[DocDate]) = 5 THEN 'May' WHEN MONTH(T0.[DocDate]) = 6 THEN 'June' WHEN MONTH(T0.[DocDate]) = 7 THEN 'July' WHEN MONTH(T0.[DocDate]) = 8 THEN 'August' WHEN MONTH(T0.[DocDate]) = 9 THEN 'September' WHEN MONTH(T0.[DocDate]) = 10 THEN 'October' WHEN MONTH(T0.[DocDate]) = 11 THEN 'November' WHEN MONTH(T0.[DocDate]) = 12 THEN 'December' ELSE 'Unknown' END AS 'Months', MONTH(T0.[DocDate]) AS 'TheMonth', YEAR(T0.[DocDate]) AS 'Years', T1.[LineTotal] AS 'LineTotals' FROM OINV T0 LEFT JOIN INV1 T1 ON T1.[DOCENTRY] = T0.[DOCENTRY] INNER JOIN OCRD T2 ON T2.[CARDCODE] = T0.[CARDCODE] WHERE T2.[CardName] = '[%0]' UNION ALL SELECT CASE WHEN MONTH(T0.[DocDate]) = 1 THEN 'January' WHEN MONTH(T0.[DocDate]) = 2 THEN 'February' WHEN MONTH(T0.[DocDate]) = 3 THEN 'March' WHEN MONTH(T0.[DocDate]) = 4 THEN 'April' WHEN MONTH(T0.[DocDate]) = 5 THEN 'May' WHEN MONTH(T0.[DocDate]) = 6 THEN 'June' WHEN MONTH(T0.[DocDate]) = 7 THEN 'July' WHEN MONTH(T0.[DocDate]) = 8 THEN 'August' WHEN MONTH(T0.[DocDate]) = 9 THEN 'September' WHEN MONTH(T0.[DocDate]) = 10 THEN 'October' WHEN MONTH(T0.[DocDate]) = 11 THEN 'November' WHEN MONTH(T0.[DocDate]) = 12 THEN 'December' ELSE 'Unknown' END AS 'Months', MONTH(T0.[DocDate]) AS 'TheMonth', YEAR(T0.[DocDate]) AS 'Years', -T1.[LineTotal] AS 'LineTotals' FROM ORIN T0 LEFT JOIN RIN1 T1 ON T1.[DOCENTRY] = T0.[DOCENTRY] INNER JOIN OCRD T2 ON T2.[CARDCODE] = T0.[CARDCODE] WHERE T2.[CardName] = '[%0]' ) AS SourceTable PIVOT ( SUM(LineTotals) FOR Years IN ( [2019], [2020], [2021], [2022] ) ) AS PivotTable ) INSIDE
jbrotto
Active Contributor
0 Kudos

I got some more modification but the number values are aligned left and wish to align them to the right.

 SELECT
T0.CardCode ,NULL AS ' ',NULL AS ' ',NULL AS ' ',NULL AS ' '
FROM OCRD T0 WHERE T0.[CARDNAME] = '[%0]'

UNION ALL


SELECT
T0.CardName ,NULL AS ' ',NULL AS ' ',NULL AS ' ',NULL AS ' ' 
FROM OCRD T0 WHERE T0.[CARDNAME] = '[%0]'


UNION ALL


SELECT
NULL AS ' ',
SUBSTRING(CAST('2022.' AS VARCHAR),1,4) AS [2022],
SUBSTRING(CAST('2021.' AS VARCHAR),1,4) AS [2021],
SUBSTRING(CAST('2020.' AS VARCHAR),1,4) AS [2020],
SUBSTRING(CAST('2019.' AS VARCHAR),1,4) AS [2019] 

UNION ALL

SELECT INSIDE.[Months], 
CAST(INSIDE.[2022] AS VARCHAR), 
CAST(CAST(INSIDE.[2021] AS MONEY) AS VARCHAR), 
CAST(CAST(INSIDE.[2020] AS MONEY) AS VARCHAR), 
CAST(CAST(INSIDE.[2019] AS MONEY) AS VARCHAR) 
FROM
(
SELECT 
TheMonth, Months,
CAST([2022] AS MONEY) AS [2022], [2021], [2020], [2019]
FROM 
(
SELECT 
CASE
    WHEN MONTH(T0.[DocDate]) = 1 THEN 'January'
    WHEN MONTH(T0.[DocDate]) = 2 THEN 'February'
    WHEN MONTH(T0.[DocDate]) = 3 THEN 'March'
    WHEN MONTH(T0.[DocDate]) = 4 THEN 'April'
    WHEN MONTH(T0.[DocDate]) = 5 THEN 'May'
    WHEN MONTH(T0.[DocDate]) = 6 THEN 'June'
    WHEN MONTH(T0.[DocDate]) = 7 THEN 'July'
    WHEN MONTH(T0.[DocDate]) = 8 THEN 'August'
    WHEN MONTH(T0.[DocDate]) = 9 THEN 'September'
    WHEN MONTH(T0.[DocDate]) = 10 THEN 'October'
    WHEN MONTH(T0.[DocDate]) = 11 THEN 'November'
    WHEN MONTH(T0.[DocDate]) = 12 THEN 'December'
    ELSE 'Unknown'
END  AS 'Months',
MONTH(T0.[DocDate]) AS 'TheMonth',
YEAR(T0.[DocDate]) AS 'Years', T1.[LineTotal] AS 'LineTotals' 
FROM OINV T0 LEFT JOIN INV1 T1 ON T1.[DOCENTRY] = T0.[DOCENTRY]
INNER JOIN OCRD T2 ON T2.[CARDCODE] = T0.[CARDCODE]
WHERE T2.[CardName] = '[%0]' 

UNION ALL

SELECT 
CASE
    WHEN MONTH(T0.[DocDate]) = 1 THEN 'January'
    WHEN MONTH(T0.[DocDate]) = 2 THEN 'February'
    WHEN MONTH(T0.[DocDate]) = 3 THEN 'March'
    WHEN MONTH(T0.[DocDate]) = 4 THEN 'April'
    WHEN MONTH(T0.[DocDate]) = 5 THEN 'May'
    WHEN MONTH(T0.[DocDate]) = 6 THEN 'June'
    WHEN MONTH(T0.[DocDate]) = 7 THEN 'July'
    WHEN MONTH(T0.[DocDate]) = 8 THEN 'August'
    WHEN MONTH(T0.[DocDate]) = 9 THEN 'September'
    WHEN MONTH(T0.[DocDate]) = 10 THEN 'October'
    WHEN MONTH(T0.[DocDate]) = 11 THEN 'November'
    WHEN MONTH(T0.[DocDate]) = 12 THEN 'December'
    ELSE 'Unknown'
END  AS 'Months',
MONTH(T0.[DocDate]) AS 'TheMonth',
YEAR(T0.[DocDate]) AS 'Years', -T1.[LineTotal] AS 'LineTotals' 
FROM ORIN T0 LEFT JOIN RIN1 T1 ON T1.[DOCENTRY] = T0.[DOCENTRY]
INNER JOIN OCRD T2 ON T2.[CARDCODE] = T0.[CARDCODE]
WHERE T2.[CardName] = '[%0]'

) AS SourceTable

PIVOT 
(
SUM(LineTotals)
FOR Years 
IN ( [2019], [2020], [2021], [2022] )
) 
AS PivotTable
) INSIDE

Accepted Solutions (0)

Answers (0)