January 20, 2015 at 12:50 pm
Hope someone can help. I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.
Is this possible?
This is my current SQL
SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM,
dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,
dbo.VIEW_EXCHANGE_FEES_TOT.PERIOD AS EXCHFEEPERIOD, dbo.VIEW_EXCHANGE_FEES_TOT.YEAR AS EXCHFEEYEAR,
dbo.VIEW_EXCHANGE_FEES_TOT.EXCHFEESUM, dbo.AS400DATA.NI_CODE_CLI_LOGIST AS CUSTACCT, dbo.AS400DATA.TX_NOM_CLI AS CUSTNAME
FROM dbo.AS400DATA RIGHT OUTER JOIN
dbo.VIEW_EXCHANGE_FEES_TOT ON dbo.AS400DATA.NI_CODE_CLI_LOGIST = dbo.VIEW_EXCHANGE_FEES_TOT.CUSTACCT RIGHT OUTER JOIN
dbo.VIEW_FLIGHT_HRS_TOT ON dbo.AS400DATA.NI_CODE_CLI_LOGIST = dbo.VIEW_FLIGHT_HRS_TOT.CUSTACCT RIGHT OUTER JOIN
dbo.VIEW_LATE_FEES_TOT ON dbo.AS400DATA.NI_CODE_CLI_LOGIST = dbo.VIEW_LATE_FEES_TOT.CUSTACCT
GROUP BY dbo.AS400DATA.NI_CODE_CLI_LOGIST, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD, dbo.VIEW_FLIGHT_HRS_TOT.YEAR, dbo.VIEW_LATE_FEES_TOT.PERIOD,
dbo.VIEW_LATE_FEES_TOT.YEAR, dbo.VIEW_LATE_FEES_TOT.PERIOD, dbo.VIEW_EXCHANGE_FEES_TOT.PERIOD, dbo.VIEW_EXCHANGE_FEES_TOT.YEAR,
dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM, dbo.VIEW_EXCHANGE_FEES_TOT.EXCHFEESUM,
dbo.AS400DATA.TX_NOM_CLI
January 20, 2015 at 1:19 pm
Hi and welcome to SSC! It is not exactly clear what you are trying to do. I would recommend you don't need to load mountains of test data, just enough to cover the problem. Also, it is best if the data is in a consumable format and is accompanied by ddl. You can see the first link in my signature for best practices when posting questions.
As for your code I would suggest you use aliases in your queries. It makes your code a LOT more legible. I would also recommend not using ALL CAPS for everything. It is more difficult to read. You should name your columns with a descriptive name and avoid abbreviations.
Here is what your code would look using aliases and a little formatting.
SELECT fh.YEAR AS FLTHRSYEAR
, fh.PERIOD AS FLTHRSPERIOD
, fh.FLIGHTHRSSUM
, vlf.PERIOD AS LATEFEEPERIOD
, vlf.YEAR AS LATEFEEYEAR
, vlf.LATEFEESUM
, vef.PERIOD AS EXCHFEEPERIOD
, vef.YEAR AS EXCHFEEYEAR
, vef.EXCHFEESUM
, a.NI_CODE_CLI_LOGIST AS CUSTACCT
, a.TX_NOM_CLI AS CUSTNAME
FROM dbo.AS400DATA a
RIGHT OUTER JOIN dbo.VIEW_EXCHANGE_FEES_TOT vef ON a.NI_CODE_CLI_LOGIST = vef.CUSTACCT
RIGHT OUTER JOIN dbo.VIEW_FLIGHT_HRS_TOT fh ON a.NI_CODE_CLI_LOGIST = fh.CUSTACCT
RIGHT OUTER JOIN dbo.VIEW_LATE_FEES_TOT ON a.NI_CODE_CLI_LOGIST = vlf.CUSTACCT
GROUP BY a.NI_CODE_CLI_LOGIST
, fh.PERIOD
, fh.YEAR
, vlf.PERIOD
, vlf.YEAR
, vlf.PERIOD
, vef.PERIOD
, vef.YEAR
, fh.FLIGHTHRSSUM
, vlf.LATEFEESUM
, vef.EXCHFEESUM
, a.TX_NOM_CLI
_______________________________________________________________
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/
January 20, 2015 at 1:28 pm
Thank you for the advice and pointers. Much appreciated! I am receiving the error attached.
January 20, 2015 at 1:37 pm
tino.lopez (1/20/2015)
Thank you for the advice and pointers. Much appreciated! I am receiving the error attached.
that's because the code I posted was missing an alias. I don't have anything to work with so this is done blind.
SELECT fh.YEAR AS FLTHRSYEAR
, fh.PERIOD AS FLTHRSPERIOD
, fh.FLIGHTHRSSUM
, vlf.PERIOD AS LATEFEEPERIOD
, vlf.YEAR AS LATEFEEYEAR
, vlf.LATEFEESUM
, vef.PERIOD AS EXCHFEEPERIOD
, vef.YEAR AS EXCHFEEYEAR
, vef.EXCHFEESUM
, a.NI_CODE_CLI_LOGIST AS CUSTACCT
, a.TX_NOM_CLI AS CUSTNAME
FROM dbo.AS400DATA a
RIGHT OUTER JOIN dbo.VIEW_EXCHANGE_FEES_TOT vef ON a.NI_CODE_CLI_LOGIST = vef.CUSTACCT
RIGHT OUTER JOIN dbo.VIEW_FLIGHT_HRS_TOT fh ON a.NI_CODE_CLI_LOGIST = fh.CUSTACCT
RIGHT OUTER JOIN dbo.VIEW_LATE_FEES_TOT vlf ON a.NI_CODE_CLI_LOGIST = vlf.CUSTACCT
GROUP BY a.NI_CODE_CLI_LOGIST
, fh.PERIOD
, fh.YEAR
, vlf.PERIOD
, vlf.YEAR
, vlf.PERIOD
, vef.PERIOD
, vef.YEAR
, fh.FLIGHTHRSSUM
, vlf.LATEFEESUM
, vef.EXCHFEESUM
, a.TX_NOM_CLI
This is NOT a fix for your problem, it just demonstrates a way to write your query more legibly. I still don't understand the problem you are trying to solve.
_______________________________________________________________
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/
January 20, 2015 at 2:21 pm
It would be good to see something like:
Original data
Cust Year Period FLIGHTHRSSUM LATEFEESUM EXCHFEESUM
----- ---- ------ ------------ ---------- ----------
A 2015 1 10 12 0
A 2015 1 5 3 2
A 2015 1 5 3 2
B 2014 1 5 3 2
B 2015 1 5 3 2
and then results: I need customer, grouped by year and period, then sums for these fields.
Cust Year Period FLIGHTHRSSUM LATEFEESUM EXCHFEESUM
----- ---- ------ ------------ ---------- ----------
A 2015 1 20 18 4
B 2014 1 5 3 2
B 2015 1 5 3 2
January 20, 2015 at 2:26 pm
I posted the original data in data example.xls as an attachment.
January 20, 2015 at 2:32 pm
tino.lopez (1/20/2015)
I posted the original data in data example.xls as an attachment.
Right but we don't know what you want for output and the data is not consumable. It is much better if you can post create table statements followed by insert statements. Remember you don't need 80,000 rows, just enough rows to represent the problem you are facing.
_______________________________________________________________
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/
January 20, 2015 at 2:34 pm
If Steve's guess is correct this is a basic SUM with GROUP BY.
_______________________________________________________________
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/
January 20, 2015 at 2:50 pm
The FLIGHTHRSSUM, LATEFEESUM and EXCHFEESUM are already summed. I simply need to group them by CUSTACCT(Customer) and have the records display according to their YEAR and PERIOD(Month).
January 20, 2015 at 2:52 pm
tino.lopez (1/20/2015)
The FLIGHTHRSSUM, LATEFEESUM and EXCHFEESUM are already summed. I simply need to group them by CUSTACCT(Customer) and have the records display according to their YEAR and PERIOD(Month).
ORDER BY CustAcct, Year, Period
_______________________________________________________________
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/
January 21, 2015 at 1:11 pm
I guess what I am not understanding is why is it that I have three types of data, one for Flight hrs, one for Exchange fees and one for late fees. Each have 421 rows. If you multiply this by 3x it equals 1263 rows. Why is it when I join the table and group, it displays thousand of rows with duplicates. What I am looking for is for FLIGHTHRSSUM, LATEFEESUM, and EXCHFEESUM grouped by CUSTACCT and PERIOD(Month).
When I use the ORDER BY clause, it timeouts after 5+ minutes. When i remove the ORDER BY, I receive thousands of rows duplicated.
January 21, 2015 at 1:30 pm
tino.lopez (1/21/2015)
I guess what I am not understanding is why is it that I have three types of data, one for Flight hrs, one for Exchange fees and one for late fees. Each have 421 rows. If you multiply this by 3x it equals 1263 rows. Why is it when I join the table and group, it displays thousand of rows with duplicates. What I am looking for is for FLIGHTHRSSUM, LATEFEESUM, and EXCHFEESUM grouped by CUSTACCT and PERIOD(Month).When I use the ORDER BY clause, it timeouts after 5+ minutes. When i remove the ORDER BY, I receive thousands of rows duplicated.
Because your join criteria is not sufficient. You are getting multiple rows returned in at least one of your joins. This most often happens when you should be using more than 1 column for your join predicates. Without knowing the tables and the data there isn't a lot we can do.
_______________________________________________________________
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/
January 21, 2015 at 1:53 pm
I understand. I have attached the data. I simply(obviously not simple for me) want to display all the data attached grouped by CUSTACCT and by FLTHRSYEAR.
January 21, 2015 at 1:58 pm
tino.lopez (1/21/2015)
I understand. I have attached the data. I simply(obviously not simple for me) want to display all the data attached grouped by CUSTACCT and by FLTHRSYEAR.
Can you convert these spreadsheets in something we can easily consume? You should first have some create table statements followed by some insert statements. I could do this from what you posted but I would rather spend my time working on your problem instead of setting it up. 🙂
_______________________________________________________________
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/
January 21, 2015 at 2:21 pm
Since he's new, I did part of it... Here are the create table statements:
-- seems there's a table missing. Parent of the other 4 tables.
CREATE TABLE Customer (
CustomerAcct CHAR(10)
CONSTRAINT pkCustomer PRIMARY KEY(CustomerAcct));
CREATE TABLE ExchangeFeesTot (
CustAcct CHAR(10),
ExchFeeSum SMALLMONEY,
Period TINYINT,
FeeYear INT
CONSTRAINT fkCustomer FOREIGN KEY CustAcct REFERENCES Customer(CustomerAcct);
);
CREATE TABLE FlightHours (
CustAcct CHAR(10)
,FlightYear TINYINT
,Period TINYINT
,FlightHrSum MONEY)
CONSTRAINT fkCustomer FOREIGN KEY CustAcct REFERENCES Customer(CustomerAcct))
;
CREATE TABLE LateFees (
CustAcct CHAR(10)
,Period TINYINT
,FeeYear INT
,LateFeeSum MONEY
CONSTRAINT fkCustomer FOREIGN KEY CustAcct REFERENCES Customer(CustomerAcct)
);
What I am looking for is for FLIGHTHRSSUM, LATEFEESUM, and EXCHFEESUM grouped by CUSTACCT and PERIOD(Month).
If you look at the structure of the Fees/Hours tables, you'll notice they're all almost the same. The only difference is the "fee type". (Might not be a perfect description, but you get the idea... it's a "bucket" to group them by.) I would put all this information in one table with an extra column to indicate what kind of Fee each is.
CREATE TABLE Fees (
CustAcct CHAR(10),
Fee MONEY,
FeeType VARCHAR(20), -- Flight Fee, Late Fee, Exchange Fee
Period TINYINT,
FeeYear INT
CONSTRAINT fkCustomer FOREIGN KEY CustAcct REFERENCES Customer(CustomerAcct);
);
Once you've lumped all these into ONE table, this is trivial.
SELECT CustAcct, FeeType, FeeYear, Period, SUM(Fee) AS Total
FROM Fees
GROUP BY CustAcct, FeeType, FeeYear, Period
ORDER BY CustAcct, FeeType, FeeYear, Period;
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply