November 11, 2018 at 6:16 pm
its only because this way it ieasier to output all text fields to text file via SSIS.
Likes to play Chess
November 11, 2018 at 6:22 pm
easier does not mean better or correct.
And as the output is being loaded by a COBOL program what is the exact layout it needs?
fixed length file?
fixed length columns?
delimited?
leading zeros?
trailing zeros?
leading sign?
leading positive sign?
overpunched sign?
implicit or explicit decimal symbol?
any of the above will require a possible different solution - many times its better to define a function to format according to the requirements than to try and manipulate SSIS to do what it needs.
November 11, 2018 at 6:47 pm
VoldemarG - Sunday, November 11, 2018 6:16 PMits only because this way it ieasier to output all text fields to text file via SSIS.
Ok... I never let the presentation layer dictate the database layer but that's just me.
The following should do the trick for you. Details are in the comments. As you can see, the code for a "matrix" like this is pretty trivial. I also included a reference for what I used in case you want to learn more as an introduction to what can actually be done. The Microsoft documentation for this has a major suck factor that I prefer to avoid. 😀
This first bit of code just adds some values to the test data you provided so that you can visually verify that things are working as desired. DON'T run it against a table that has real data in it because it'll overwrite the real data with random data.
--===== Add some values other than zero for entry and withdrawals to make sure the totals are working correctly.
-- To keep things simple, the values will range from 0 thru 9.
-- THIS IS NOT A PART OF THE SOLUTION. It's a part of setting up test data to test the solution with.
UPDATE dbo.MobilityRates
SET EntryNumber = ABS(CHECKSUM(NEWID())%10)
,WithdrawNumber = ABS(CHECKSUM(NEWID())%10)
;
The is the code that does what you want.
--===== This solves the problem quickly and easily.
--Decent Reference: https://www.red-gate.com/simple-talk/sql/t-sql-programming/summarizing-data-using-grouping-sets-operator/
SELECT School
,Grade
,Sex
,Race = CASE WHEN GROUPING(Race) = 0 THEN Race ELSE 'TOTAL' END
,EntryNumber = SUM(CONVERT(INT,EntryNumber)) --Not to worry. Sum of a detail row is the detail row.
,WithdrawNumber = SUM(CONVERT(INT,WithdrawNumber)) --Not to worry. Sum of a detail row is the detail row.
--===== Comment the following 4 columns out for production.
-- These are here just so you can see what "GROUPING" does and
-- why the ORDER BY uses it as a sort guarantee
,GrpSchool = GROUPING(School)
,GrpGrade = GROUPING(Grade)
,GrpSex = GROUPING(Sex) --Couldn't avoid the pun
,GrpRace = GROUPING(Race)
FROM dbo.MobilityRates
GROUP BY GROUPING SETS (
(School, Grade, Sex, Race) --Detail Rows
,(School, Grade, Sex) --Total Rows
)
ORDER BY School,Grade,Sex,GROUPING(Race) --GROUPING(RACE) is either 0 (detail) or 1 (total)
;
Just to help a bit with the attachment problems you've been having...
1. Most people won't open spreadsheets. If you need to provide some clarification like that, "take a picture", save it as a GIF, PNG, or JPG and attach that.
2. Most people like "readily consumable" data, which means you provide the CREATE TABLE and some inserts. I agree that the amount of data that you posted was a good thing but that should have been an attachment. If you feel the need to compress something like that, stick with ZIP files. A lot of people won't touch a .RAR file. And, the embedded files should be .TXT files. With all the "attacks" out there, people are very leery and anything you can do to make it easier for them to help you will go a long way with them. You can also take a look at the first link in my signature line below for how to easily covert real data (but don't include PII, etc) to INSERT statements that most will trust.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2018 at 6:50 pm
p.s. SSIS, SSRS, SSAS, SSSB.... those are all "four letter words" to me. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2018 at 8:10 am
Did that do what you needed it to do?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2018 at 8:20 am
Jonathan AC Roberts - Sunday, November 11, 2018 5:17 PMVoldemarG - Sunday, November 11, 2018 9:58 AMThis is the actual SQL table, imported into Excel.
The attached Excel file shows that I need to fill the 2 values for the group of 7 races into the row where race = 'TOTAL'.in other words, the calculations in the right two columns show the numbers for the unique row, which is defined by School+Grade+Sex+Race..
the TOTAL row shows totals for all races per School+Grade+Sex.I know it looks weird to calculate the totals in T-SQL (i know how easy it is in SSRS, etc... but in this case i am producing a text file feed from SSIS pakage that must be in this precise format for the receiving old cobol program to be able to pick up and read the text file... ) .
I am looking for a compact SQL Statement to accomplish such update. Thanks.
I'm assuming the "TOTAL" rows already exist but EntryNumber and WithdrawNumber are unpopulated.
UPDATE m
SET m.EntryNumber=m2.TotalEntryNumber,
m.WithdrawNumber= m2.TotalWithdrawNumber
FROM [dbo].[MobilityRates] m
CROSS APPLY(SELECT SUM(m2.EntryNumber) TotalEntryNumber,
SUM(m2.WithdrawNumber) TotalWithdrawNumber
FROM [dbo].[MobilityRates] m2
WHERE m2.Race <> 'TOTAL'
AND m2.School = m.School
AND m2.Grade = m.Grade
AND m2.Sex= m.Sex) m2
WHERE m.Race = 'TOTAL'
Thank you!
This works perfect.
Likes to play Chess
November 12, 2018 at 12:11 pm
VoldemarG - Monday, November 12, 2018 8:20 AMJonathan AC Roberts - Sunday, November 11, 2018 5:17 PMVoldemarG - Sunday, November 11, 2018 9:58 AMThis is the actual SQL table, imported into Excel.
The attached Excel file shows that I need to fill the 2 values for the group of 7 races into the row where race = 'TOTAL'.in other words, the calculations in the right two columns show the numbers for the unique row, which is defined by School+Grade+Sex+Race..
the TOTAL row shows totals for all races per School+Grade+Sex.I know it looks weird to calculate the totals in T-SQL (i know how easy it is in SSRS, etc... but in this case i am producing a text file feed from SSIS pakage that must be in this precise format for the receiving old cobol program to be able to pick up and read the text file... ) .
I am looking for a compact SQL Statement to accomplish such update. Thanks.
I'm assuming the "TOTAL" rows already exist but EntryNumber and WithdrawNumber are unpopulated.
UPDATE m
SET m.EntryNumber=m2.TotalEntryNumber,
m.WithdrawNumber= m2.TotalWithdrawNumber
FROM [dbo].[MobilityRates] m
CROSS APPLY(SELECT SUM(m2.EntryNumber) TotalEntryNumber,
SUM(m2.WithdrawNumber) TotalWithdrawNumber
FROM [dbo].[MobilityRates] m2
WHERE m2.Race <> 'TOTAL'
AND m2.School = m.School
AND m2.Grade = m.Grade
AND m2.Sex= m.Sex) m2
WHERE m.Race = 'TOTAL'Thank you!
This works perfect.
So the test data was a whole lot further off than anyone knew.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2018 at 12:54 pm
Jeff Moden - Monday, November 12, 2018 12:11 PMVoldemarG - Monday, November 12, 2018 8:20 AMJonathan AC Roberts - Sunday, November 11, 2018 5:17 PMVoldemarG - Sunday, November 11, 2018 9:58 AMThis is the actual SQL table, imported into Excel.
The attached Excel file shows that I need to fill the 2 values for the group of 7 races into the row where race = 'TOTAL'.in other words, the calculations in the right two columns show the numbers for the unique row, which is defined by School+Grade+Sex+Race..
the TOTAL row shows totals for all races per School+Grade+Sex.I know it looks weird to calculate the totals in T-SQL (i know how easy it is in SSRS, etc... but in this case i am producing a text file feed from SSIS pakage that must be in this precise format for the receiving old cobol program to be able to pick up and read the text file... ) .
I am looking for a compact SQL Statement to accomplish such update. Thanks.
I'm assuming the "TOTAL" rows already exist but EntryNumber and WithdrawNumber are unpopulated.
UPDATE m
SET m.EntryNumber=m2.TotalEntryNumber,
m.WithdrawNumber= m2.TotalWithdrawNumber
FROM [dbo].[MobilityRates] m
CROSS APPLY(SELECT SUM(m2.EntryNumber) TotalEntryNumber,
SUM(m2.WithdrawNumber) TotalWithdrawNumber
FROM [dbo].[MobilityRates] m2
WHERE m2.Race <> 'TOTAL'
AND m2.School = m.School
AND m2.Grade = m.Grade
AND m2.Sex= m.Sex) m2
WHERE m.Race = 'TOTAL'Thank you!
This works perfect.So the test data was a whole lot further off than anyone knew.
You needed to open the spreadsheet.
When I open a spreadsheet from the internet Excel always opens it in "Protected Mode" so I don't think it's much of a risk.
November 12, 2018 at 1:52 pm
Jonathan AC Roberts - Monday, November 12, 2018 12:54 PMJeff Moden - Monday, November 12, 2018 12:11 PMVoldemarG - Monday, November 12, 2018 8:20 AMJonathan AC Roberts - Sunday, November 11, 2018 5:17 PMVoldemarG - Sunday, November 11, 2018 9:58 AMThis is the actual SQL table, imported into Excel.
The attached Excel file shows that I need to fill the 2 values for the group of 7 races into the row where race = 'TOTAL'.in other words, the calculations in the right two columns show the numbers for the unique row, which is defined by School+Grade+Sex+Race..
the TOTAL row shows totals for all races per School+Grade+Sex.I know it looks weird to calculate the totals in T-SQL (i know how easy it is in SSRS, etc... but in this case i am producing a text file feed from SSIS pakage that must be in this precise format for the receiving old cobol program to be able to pick up and read the text file... ) .
I am looking for a compact SQL Statement to accomplish such update. Thanks.
I'm assuming the "TOTAL" rows already exist but EntryNumber and WithdrawNumber are unpopulated.
UPDATE m
SET m.EntryNumber=m2.TotalEntryNumber,
m.WithdrawNumber= m2.TotalWithdrawNumber
FROM [dbo].[MobilityRates] m
CROSS APPLY(SELECT SUM(m2.EntryNumber) TotalEntryNumber,
SUM(m2.WithdrawNumber) TotalWithdrawNumber
FROM [dbo].[MobilityRates] m2
WHERE m2.Race <> 'TOTAL'
AND m2.School = m.School
AND m2.Grade = m.Grade
AND m2.Sex= m.Sex) m2
WHERE m.Race = 'TOTAL'Thank you!
This works perfect.So the test data was a whole lot further off than anyone knew.
You needed to open the spreadsheet.
When I open a spreadsheet from the internet Excel always opens it in "Protected Mode" so I don't think it's much of a risk.
Understood but shouldn't have to open a spreadsheet when the OP provided test data, which was incorrect in a couple of ways. And, even if you do open the spreadsheet, the test data should match.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply