December 20, 2016 at 7:54 am
Maybe my skull is a little too thick today but I cannot figure out why this is not grouping correctly:
SELECT CAST(Coalesce (RIGHT(EDI_Batch_Number,4), 1325 ) AS VARCHAR(4)) AS 'Batch Number'
,COALESCE(LEFT(Reference_2, 2), 'Grand Total') AS 'Sum of Amount Reference 2 Type'
,sum(CASE Document_Type
WHEN 'T1'
THEN Amount
ELSE 0
END) AS 'T1 - Cash Distribution / Liabilities'
,sum(CASE Document_Type
WHEN 'T2'
THEN Amount
ELSE 0
END) AS 'T2 - Labor Distribution / Gross Wages'
,sum(CASE Document_Type
WHEN 'T3'
THEN Amount
ELSE 0
END) AS 'T3 - Burden / Taxes'
,sum(Amount) AS 'Totals'
FROM DBO.STG_LEDGER_DETAILS_TI
GROUP BY GROUPING SETS(EDI_Batch_Number, Reference_2),()
The result set looks like this:
Batch NumberSum of Amount Reference 2 TypeT1 - Cash Distribution / LiabilitiesT2 - Labor Distribution / Gross WagesT3 - Burden / TaxesTotals
1325 AL -3463.74 0.00 0.00 -3463.74
1325 AT 4122.97 0.00 0.00 4122.97
1325 AT 109.50 0.00 0.00 109.50
1325 AT -458920.50 0.00 0.00 -458920.50
DDL for the table:
USE [Datamart]
GO
/****** Object: Table [dbo].[STG_LEDGER_DETAILS_TI] Script Date: 12/20/2016 8:53:43 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[STG_LEDGER_DETAILS_TI](
[Date_GL] [varchar](8) NULL,
[Account_Number] [varchar](29) NULL,
[Business_Unit] [varchar](12) NULL,
[Object_Account] [varchar](6) NULL,
[Subsidiary] [varchar](8) NULL,
[EDI_User_ID] [varchar](10) NULL,
[EDI_Transaction_Number] [varchar](22) NULL,
[EDI_Batch_Number] [varchar](15) NULL,
[Document_Company] [varchar](5) NULL,
[Document_Type] [varchar](2) NULL,
[Amount] [numeric](15, 2) NULL,
[Units] [numeric](15, 2) NULL,
[Name_Alpha_Explanation] [varchar](30) NULL,
[Name_Remark_Explanation] [varchar](30) NULL,
[Reference_2] [varchar](8) NULL,
[EDI_Line_Number] [numeric](7, 0) NULL,
[Employee_ID] [numeric](11, 0) NULL,
[Pay_Code] [varchar](16) NULL,
[Check_Date] [varchar](8) NULL,
[Pay_End_Date] [varchar](8) NULL,
[Currency] [varchar](3) NULL,
[Create_Date] [datetime] NULL,
[Create_User_Id] [varchar](20) NULL,
[Src_Create_Date] [datetime] NULL,
[Src_Create_User_Id] [varchar](20) NULL,
[Change_Status_Flag] [varchar](1) NULL,
[Changed_By] [varchar](50) NULL,
[Changed_Dateime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Thoughts?
December 20, 2016 at 8:02 am
Here is the code, formatted so that others have at least a chance of parsing it. Please use IFCode tags in future.
SELECT
[Batch Number] = CAST(COALESCE(RIGHT(EDI_Batch_Number, 4), 1325) AS VARCHAR(4))
, [Sum of Amount Reference 2 Type] = COALESCE(LEFT(Reference_2, 2), 'Grand Total')
, [T1 - Cash Distribution / Liabilities] = SUM( CASE Document_Type
WHEN 'T1' THEN
Amount
ELSE
0
END
)
, [T2 - Labor Distribution / Gross Wages] = SUM( CASE Document_Type
WHEN 'T2' THEN
Amount
ELSE
0
END
)
, [T3 - Burden / Taxes] = SUM( CASE Document_Type
WHEN 'T3' THEN
Amount
ELSE
0
END
)
, Totals = SUM(Amount)
FROM dbo.STG_LEDGER_DETAILS_TI
GROUP BY
GROUPING SETS(EDI_Batch_Number, Reference_2)
, ();
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 20, 2016 at 8:30 am
Can you provide some sample data for us to use and what the desired output would be for that sample data? Just showing us the results of your query and saying it isn't right does not do a good job of explaining the issue at hand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2016 at 8:50 am
I suspect what you want is
GROUP BY GROUPING SETS((EDI_Batch_Number, Reference_2),())
which will give you groups by EDI_Batch_Number/Reference and then a grand total.
What you currently have gives your one group on EDI_Batch_Number/empty set and another on Reference_2/empty set.
To put it another way, what you have is the same as
GROUP BY (GROUPING SETS(EDI_Batch_Number, Reference_2)),()
Drew
Edit: Added clarification.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2016 at 10:08 am
Got the same results - but thanks...
December 20, 2016 at 10:15 am
Sean Lange (12/20/2016)
Can you provide some sample data for us to use and what the desired output would be for that sample data? Just showing us the results of your query and saying it isn't right does not do a good job of explaining the issue at hand.
^^this is what we need
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 20, 2016 at 10:49 am
Brad Feaker-195979 (12/20/2016)
Got the same results - but thanks...
The query I gave you will produce a row with NULL values for the grouped fields. Since your results DO NOT contain such a row, you cannot have run the suggested query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2016 at 11:15 am
Brad Feaker-195979 (12/20/2016)
Maybe my skull is a little too thick today but I cannot figure out why this is not grouping correctly:SELECT CAST(Coalesce (RIGHT(EDI_Batch_Number,4), 1325 ) AS VARCHAR(4)) AS 'Batch Number'
,COALESCE(LEFT(Reference_2, 2), 'Grand Total') AS 'Sum of Amount Reference 2 Type'
,sum(CASE Document_Type
WHEN 'T1'
THEN Amount
ELSE 0
END) AS 'T1 - Cash Distribution / Liabilities'
,sum(CASE Document_Type
WHEN 'T2'
THEN Amount
ELSE 0
END) AS 'T2 - Labor Distribution / Gross Wages'
,sum(CASE Document_Type
WHEN 'T3'
THEN Amount
ELSE 0
END) AS 'T3 - Burden / Taxes'
,sum(Amount) AS 'Totals'
FROM DBO.STG_LEDGER_DETAILS_TI
GROUP BY GROUPING SETS(EDI_Batch_Number, Reference_2),()
The result set looks like this:
Batch NumberSum of Amount Reference 2 TypeT1 - Cash Distribution / LiabilitiesT2 - Labor Distribution / Gross WagesT3 - Burden / TaxesTotals
1325 AL -3463.74 0.00 0.00 -3463.74
1325 AT 4122.97 0.00 0.00 4122.97
1325 AT 109.50 0.00 0.00 109.50
1325 AT -458920.50 0.00 0.00 -458920.50
DDL for the table:
USE [Datamart]
GO
/****** Object: Table [dbo].[STG_LEDGER_DETAILS_TI] Script Date: 12/20/2016 8:53:43 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[STG_LEDGER_DETAILS_TI](
[Date_GL] [varchar](8) NULL,
[Account_Number] [varchar](29) NULL,
[Business_Unit] [varchar](12) NULL,
[Object_Account] [varchar](6) NULL,
[Subsidiary] [varchar](8) NULL,
[EDI_User_ID] [varchar](10) NULL,
[EDI_Transaction_Number] [varchar](22) NULL,
[EDI_Batch_Number] [varchar](15) NULL,
[Document_Company] [varchar](5) NULL,
[Document_Type] [varchar](2) NULL,
[Amount] [numeric](15, 2) NULL,
[Units] [numeric](15, 2) NULL,
[Name_Alpha_Explanation] [varchar](30) NULL,
[Name_Remark_Explanation] [varchar](30) NULL,
[Reference_2] [varchar](8) NULL,
[EDI_Line_Number] [numeric](7, 0) NULL,
[Employee_ID] [numeric](11, 0) NULL,
[Pay_Code] [varchar](16) NULL,
[Check_Date] [varchar](8) NULL,
[Pay_End_Date] [varchar](8) NULL,
[Currency] [varchar](3) NULL,
[Create_Date] [datetime] NULL,
[Create_User_Id] [varchar](20) NULL,
[Src_Create_Date] [datetime] NULL,
[Src_Create_User_Id] [varchar](20) NULL,
[Change_Status_Flag] [varchar](1) NULL,
[Changed_By] [varchar](50) NULL,
[Changed_Dateime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Thoughts?
Given your query takes SUMS only for records with a Document_Type having values of T1, T2, or T3, I'm noticing that your Grand Total line does not match the sum of the other lines, and my guess is, that the rest of that grand total number comes from document types OTHER than those specified, as your query doesn't limit the contributing rows to those values. You might want to include either a WHERE Document_Type IN ('T1', 'T2', 'T3') or a HAVING Document_Type IN ('T1', 'T2', 'T3')... Let me know if that's not the problem...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2016 at 1:14 pm
In your select you have ",COALESCE(LEFT(Reference_2, 2), 'Grand Total') AS 'Sum of Amount Reference 2 Type'"
which the result shown gives you "AL" and multiple "AT"'s and you are expecting one "AT".
I see that in the GROUP BY you are grouping by the Reference_2 column, you may need to change that to
GROUP BY GROUPING SETS(EDI_Batch_Number, COALESCE(LEFT(Reference_2, 2), 'Grand Total')),() for you to get the results you are expecting If you have multiple "AT" values.
December 21, 2016 at 8:06 am
Thanks Zane - Steve Miron said that fixed the query!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply