bapkins
asked on
SQL View Issue
Hi Guys,
I have created a view to collate some quotation data.
I want the data grouped by the quoteref. All is ok until i add the following line.
"CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE acocmp1.quoteline.totnet END AS WonRevenue"
It then adds a row for each "won revenue" entry in effect it is not "summing" the won revenue but displaying each line thus i now have multiple quoteref rows.
On my GroupBy column i do not have a "Sum" selection. Please can anyone identify what is wrong or even if what i am doing is correct.
Many Thanks.
Here is the Code i am currently using along with a screenshot
SELECT TOP (100) PERCENT ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.QUOTEHEAD.SOTRAN, ACOCMP1.SALESTAF.WRKNUM + ' ' + ACOCMP1.SALESTAF.FORENAMES
ACOCMP1.QUOTEHEAD.QUOTEDAT
ACOCMP1.QUOTEHEAD.NAME, ACOCMP1.QUOTEHEAD.CONTACT,
ACOCMP1.UCPLKUPQuotation.L
- CASE WHEN MONTH(QUOTEDATE) > 3 THEN 0 ELSE 1 END AS FinancialYear, CASE WHEN MONTH(QUOTEDATE) > 3 THEN DATEPART(quarter, QUOTEDATE)
- 1 ELSE 4 END AS FinancialQTR, DATEPART(M, ACOCMP1.QUOTEHEAD.QUOTEDAT
AS CalenderYear, CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE acocmp1.quoteline.totnet END AS WonRevenue, 1 AS CountOfQuotes,
CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE 1 END AS WonQuote
FROM ACOCMP1.QUOTEHEAD LEFT OUTER JOIN
ACOCMP1.QUOTELINE ON ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.UCPVALSQuotation LEFT OUTER JOIN
ACOCMP1.UCPLKUPQuotation ON ACOCMP1.UCPVALSQuotation.Q
ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.SALESTAF ON ACOCMP1.QUOTEHEAD.SALESMAN
GROUP BY ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.SALESTAF.WRKNUM + ' ' + ACOCMP1.SALESTAF.FORENAMES
ACOCMP1.QUOTEHEAD.QUOTEDAT
ACOCMP1.QUOTEHEAD.PHONE, ACOCMP1.QUOTEHEAD.EMAIL, ACOCMP1.UCPLKUPQuotation.L
YEAR(ACOCMP1.QUOTEHEAD.QUO
DATEPART(YYYY, ACOCMP1.QUOTEHEAD.QUOTEDAT
CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE acocmp1.quoteline.totnet END
ORDER BY ACOCMP1.QUOTEHEAD.QUOTEREF
I have created a view to collate some quotation data.
I want the data grouped by the quoteref. All is ok until i add the following line.
"CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE acocmp1.quoteline.totnet END AS WonRevenue"
It then adds a row for each "won revenue" entry in effect it is not "summing" the won revenue but displaying each line thus i now have multiple quoteref rows.
On my GroupBy column i do not have a "Sum" selection. Please can anyone identify what is wrong or even if what i am doing is correct.
Many Thanks.
Here is the Code i am currently using along with a screenshot
SELECT TOP (100) PERCENT ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.QUOTEHEAD.SOTRAN, ACOCMP1.SALESTAF.WRKNUM + ' ' + ACOCMP1.SALESTAF.FORENAMES
ACOCMP1.QUOTEHEAD.QUOTEDAT
ACOCMP1.QUOTEHEAD.NAME, ACOCMP1.QUOTEHEAD.CONTACT,
ACOCMP1.UCPLKUPQuotation.L
- CASE WHEN MONTH(QUOTEDATE) > 3 THEN 0 ELSE 1 END AS FinancialYear, CASE WHEN MONTH(QUOTEDATE) > 3 THEN DATEPART(quarter, QUOTEDATE)
- 1 ELSE 4 END AS FinancialQTR, DATEPART(M, ACOCMP1.QUOTEHEAD.QUOTEDAT
AS CalenderYear, CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE acocmp1.quoteline.totnet END AS WonRevenue, 1 AS CountOfQuotes,
CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE 1 END AS WonQuote
FROM ACOCMP1.QUOTEHEAD LEFT OUTER JOIN
ACOCMP1.QUOTELINE ON ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.UCPVALSQuotation LEFT OUTER JOIN
ACOCMP1.UCPLKUPQuotation ON ACOCMP1.UCPVALSQuotation.Q
ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.SALESTAF ON ACOCMP1.QUOTEHEAD.SALESMAN
GROUP BY ACOCMP1.QUOTEHEAD.QUOTEREF
ACOCMP1.SALESTAF.WRKNUM + ' ' + ACOCMP1.SALESTAF.FORENAMES
ACOCMP1.QUOTEHEAD.QUOTEDAT
ACOCMP1.QUOTEHEAD.PHONE, ACOCMP1.QUOTEHEAD.EMAIL, ACOCMP1.UCPLKUPQuotation.L
YEAR(ACOCMP1.QUOTEHEAD.QUO
DATEPART(YYYY, ACOCMP1.QUOTEHEAD.QUOTEDAT
CASE WHEN acocmp1.quotehead.sotran IS NULL THEN 0 ELSE acocmp1.quoteline.totnet END
ORDER BY ACOCMP1.QUOTEHEAD.QUOTEREF
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
>>I do not have a "sum" option in my drop down in the group by column.<<
That would not make a lot of sense. You cannot have an aggregate function such as SUM, MAX, MIN, AVG, in a GROUP BY clause.
That would not make a lot of sense. You cannot have an aggregate function such as SUM, MAX, MIN, AVG, in a GROUP BY clause.
>>Hi acperkins i tried the code but it has errors. <<
I am sorry, I am neither clairvoyant nor am I in front of your computer. If you can post the error messages, that would be helpful.
I am sorry, I am neither clairvoyant nor am I in front of your computer. If you can post the error messages, that would be helpful.
You would do the sum but not have them listed in the group by.
ASKER
Hi dchorton,
I do not have the sum available to me in the drop down as you can see by the attached screenshot. Doc1.doc
I do not have the sum available to me in the drop down as you can see by the attached screenshot. Doc1.doc
>>I do not have the sum available to me in the drop down as you can see by the attached screenshot. <<
Again, that is because you cannot have it in a GROUP BY. The SUM has to be on item in the SELECT list.
Incidentally, you really need to wean yourself from the Designer mode for VIEWs and just type them in the query window. The Designer mode is notoriously buggy and limiting.
Again, that is because you cannot have it in a GROUP BY. The SUM has to be on item in the SELECT list.
Incidentally, you really need to wean yourself from the Designer mode for VIEWs and just type them in the query window. The Designer mode is notoriously buggy and limiting.
ASKER
Hi acperkins,
Here is the error:
Msg 4104, Level 16, State 1, Procedure PASS_QuoteHeadAnalysis, Line 31
The multi-part identifier "acocmp1.quotehead.sotran"
Msg 4104, Level 16, State 1, Procedure PASS_QuoteHeadAnalysis, Line 32
The multi-part identifier "acocmp1.quoteline.totnet"
Here is the error:
Msg 4104, Level 16, State 1, Procedure PASS_QuoteHeadAnalysis, Line 31
The multi-part identifier "acocmp1.quotehead.sotran"
Msg 4104, Level 16, State 1, Procedure PASS_QuoteHeadAnalysis, Line 32
The multi-part identifier "acocmp1.quoteline.totnet"
ASKER
Hi Guys,
Sorry to be a pain. I'm not an sql guru but i am a fast learner (i think !)
I have the code you gave me above working but it's still giving me a seperate line for each wonrevenue row instead of summing it up.
Any Ideas ?
SELECT qh.QUOTEREF, SUM(ql.QTY) AS NoQuoteLines, SUM(ql.TOTNET) AS TotalNet, qh.SOTRAN, s.WRKNUM + ' ' + s.FORENAMES + ' ' + s.SURNAME AS Salesperson,
qh.QUOTEDATE, DATEDIFF(day, qh.QUOTEDATE, GETDATE()) AS QuoteAgeDays, qh.ACNO, qh.NAME, qh.CONTACT, qh.PHONE, qh.EMAIL,
ul.LOOKCODE + ' ' + ul.LOOKDES AS SourceTelNo, YEAR(qh.QUOTEDATE) - CASE WHEN MONTH(QUOTEDATE) > 3 THEN 0 ELSE 1 END AS FinancialYear,
CASE WHEN MONTH(QUOTEDATE) > 3 THEN DATEPART(quarter, QUOTEDATE) - 1 ELSE 4 END AS FinancialQTR, DATEPART(M, qh.QUOTEDATE) AS CalenderMonth,
DATEPART(YYYY, qh.QUOTEDATE) AS CalenderYear, CASE WHEN qh.sotran IS NULL THEN 0 ELSE ql.totnet END AS WonRevenue, 1 AS CountOfQuotes,
CASE WHEN qh.sotran IS NULL THEN 0 ELSE 1 END AS WonQuote, SUM(CASE WHEN qh.sotran IS NULL THEN 0 ELSE ql.totnet END) AS WonRevenue2
FROM ACOCMP1.QUOTEHEAD AS qh LEFT OUTER JOIN
ACOCMP1.QUOTELINE AS ql ON qh.QUOTEREF = ql.QUOTEREF LEFT OUTER JOIN
ACOCMP1.UCPVALSQuotation AS uv LEFT OUTER JOIN
ACOCMP1.UCPLKUPQuotation AS ul ON uv.QuoteSourceTelNo = ul.LOOKCODE ON qh.QUOTEREF = uv.CODE LEFT OUTER JOIN
ACOCMP1.SALESTAF AS s ON qh.SALESMAN = s.WRKNUM
GROUP BY qh.QUOTEREF, qh.SOTRAN, s.WRKNUM, s.FORENAMES, s.SURNAME, qh.QUOTEDATE, DATEDIFF(day, qh.QUOTEDATE, GETDATE()), qh.ACNO, qh.NAME,
qh.CONTACT, qh.PHONE, qh.EMAIL, ul.LOOKCODE, ul.LOOKDES, YEAR(qh.QUOTEDATE) - CASE WHEN MONTH(QUOTEDATE) > 3 THEN 0 ELSE 1 END, DATEPART(M,
qh.QUOTEDATE), DATEPART(YYYY, qh.QUOTEDATE), CASE WHEN qh.sotran IS NULL THEN 0 ELSE 1 END, CASE WHEN qh.sotran IS NULL
THEN 0 ELSE ql.totnet END
Sorry to be a pain. I'm not an sql guru but i am a fast learner (i think !)
I have the code you gave me above working but it's still giving me a seperate line for each wonrevenue row instead of summing it up.
Any Ideas ?
SELECT qh.QUOTEREF, SUM(ql.QTY) AS NoQuoteLines, SUM(ql.TOTNET) AS TotalNet, qh.SOTRAN, s.WRKNUM + ' ' + s.FORENAMES + ' ' + s.SURNAME AS Salesperson,
qh.QUOTEDATE, DATEDIFF(day, qh.QUOTEDATE, GETDATE()) AS QuoteAgeDays, qh.ACNO, qh.NAME, qh.CONTACT, qh.PHONE, qh.EMAIL,
ul.LOOKCODE + ' ' + ul.LOOKDES AS SourceTelNo, YEAR(qh.QUOTEDATE) - CASE WHEN MONTH(QUOTEDATE) > 3 THEN 0 ELSE 1 END AS FinancialYear,
CASE WHEN MONTH(QUOTEDATE) > 3 THEN DATEPART(quarter, QUOTEDATE) - 1 ELSE 4 END AS FinancialQTR, DATEPART(M, qh.QUOTEDATE) AS CalenderMonth,
DATEPART(YYYY, qh.QUOTEDATE) AS CalenderYear, CASE WHEN qh.sotran IS NULL THEN 0 ELSE ql.totnet END AS WonRevenue, 1 AS CountOfQuotes,
CASE WHEN qh.sotran IS NULL THEN 0 ELSE 1 END AS WonQuote, SUM(CASE WHEN qh.sotran IS NULL THEN 0 ELSE ql.totnet END) AS WonRevenue2
FROM ACOCMP1.QUOTEHEAD AS qh LEFT OUTER JOIN
ACOCMP1.QUOTELINE AS ql ON qh.QUOTEREF = ql.QUOTEREF LEFT OUTER JOIN
ACOCMP1.UCPVALSQuotation AS uv LEFT OUTER JOIN
ACOCMP1.UCPLKUPQuotation AS ul ON uv.QuoteSourceTelNo = ul.LOOKCODE ON qh.QUOTEREF = uv.CODE LEFT OUTER JOIN
ACOCMP1.SALESTAF AS s ON qh.SALESMAN = s.WRKNUM
GROUP BY qh.QUOTEREF, qh.SOTRAN, s.WRKNUM, s.FORENAMES, s.SURNAME, qh.QUOTEDATE, DATEDIFF(day, qh.QUOTEDATE, GETDATE()), qh.ACNO, qh.NAME,
qh.CONTACT, qh.PHONE, qh.EMAIL, ul.LOOKCODE, ul.LOOKDES, YEAR(qh.QUOTEDATE) - CASE WHEN MONTH(QUOTEDATE) > 3 THEN 0 ELSE 1 END, DATEPART(M,
qh.QUOTEDATE), DATEPART(YYYY, qh.QUOTEDATE), CASE WHEN qh.sotran IS NULL THEN 0 ELSE 1 END, CASE WHEN qh.sotran IS NULL
THEN 0 ELSE ql.totnet END
ASKER
Hi Guys,
Figured it out with your help so many thanks !
Figured it out with your help so many thanks !
ASKER
That is what i want to achieve.
the Totnet currently works fine and sums up on a per quoteref basis but as soon as i add wonrevenue the won revenue gives me multiple quoterefs which are not summed.
I do not have a "sum" option in my drop down in the group by column.
Hope that makes sense !
Hi acperkins i tried the code but it has errors.