February 1, 2006 at 11:55 am
Hello comunity
I need some help to make a row that return a grand total by field fc.nome who as the name of my supplier
like that :
datalc no supplier name doc. nºdoc. value
01-01-2003 10 luis invoice 5 100€
05-10-2003 10 luis invoice 10 10€
TOTAL 110€
10-10-2003 11 pedro invoice 55 55€
TOTAL 55€
the TSQL is :
SELECT fc.datalc as Data , cast(fc.no as int(10)) as No,fc.nome as Nome ,cmdesc as Documento,adoc NoDoc, fc.ecred- ISNULL((SELECT sum(EREC) FROM PL WHERE PL.RDATA <='20031231' AND PL.FCSTAMP=FC.FCSTAMP),0) AS Pendente FROM FC
WHERE (fc.ecred > 0) and fc.ecred- ISNULL((SELECT sum(EREC) FROM PL WHERE PL.RDATA <='20031231' AND PL.FCSTAMP=FC.FCSTAMP),0)>0 and (fc.datalc between '20030101' and '20031231')
group by fc.datalc,fc.no,fc.nome,fc.cmdesc,fc.adoc,fc.ecred,fc.fcstamp
order by cast(fc.no as int(10)), fc.datalc
Many thanks
Luis Santos
February 1, 2006 at 12:10 pm
Try this article
February 2, 2006 at 2:13 am
hello David
Thanks for your help, but with theses functions i don´t improve the result that i want in my example.
i write this TSQL, that was very near to i want, but the problem is that i have a subquery and i can´t make the SUM of them, then , the SELECT return to me n lines with TOTAL repeating the value for each invoice.
Example :
datalc no supplier name doc. nºdoc. value
01-01-2003 10 luis invoice 5 100€
05-10-2003 10 luis invoice 10 10€
TOTAL 100€
TOTAL 10€
SELECT fc.datalc as Data , cast(fc.no as int(10)) as NR,fc.nome as Nome ,cmdesc as Documento,adoc NoDoc,
fc.ecred- ISNULL((SELECT SUM(EREC) FROM PL WHERE PL.RDATA <='20031231' AND PL.FCSTAMP=FC.FCSTAMP),0) AS Pendente
FROM FC
WHERE (fc.ecred > 0) and fc.ecred- ISNULL((SELECT sum(EREC) FROM PL WHERE PL.RDATA <='20031231'
AND PL.FCSTAMP=FC.FCSTAMP),0)>0 and (fc.datalc between '20030101' and '20031231')
group by fc.datalc,fc.no,fc.nome,fc.cmdesc,fc.adoc,fc.ecred,fc.fcstamp
UNION
SELECT ' ', cast(fc.no as int(10)) as NR,'TOTAL' ,' ',' ', CAST(fc.ecred- ISNULL((SELECT sum(EREC) FROM PL WHERE PL.RDATA <='20031231' AND PL.FCSTAMP=FC.FCSTAMP),0) AS DECIMAL(10,2))
FROM FC
WHERE (fc.ecred > 0) and fc.ecred- ISNULL((SELECT sum(EREC) FROM PL WHERE PL.RDATA <='20031231'
AND PL.FCSTAMP=FC.FCSTAMP),0)>0 and (fc.datalc between '20030101' and '20031231')
GROUP BY fc.datalc,fc.no,fc.nome,fc.cmdesc,fc.adoc,FC.ECRED,FC.FCSTAMP
ORDER BY cast(fc.no as int(10))ASC, fc.datalc DESC
Could you give me a suggestion.
Thanks
Luis Santos
February 2, 2006 at 9:53 am
You'll need WITH ROLLUP in the GROUP BY clause.
Skipping the middle columns and getting the totals in the right place requires a complex solution. It is not trivial and here is only a very general process that you'll need to explore in depth. I get paid a lot of money to do this kind of work and it takes time so I can't present a complete solution. An alternative is to use Crystal Reports or Report Services.
The approach to solving this involves the following steps:
1. write the basic query
2. write a query around that and add the GROUP BY WITH ROLLUP and add a column or two that specify the GROUPING function. Use the GROUPING function in a HAVING clause to eliminate the subtotal rows that you don't need.
3. write a query around that and add ORDER BY that specifies the appropriate labels and the GROUPING function column(s).
Use temporary tables for each step to save your sanity and then you can combine it into one statement if performance dictates. The proper label generation can occur with the GROUPING function or in the next step because you retain that column.
Study the GROUP BY WITH ROLLUP and HAVING features and the GROUPING function. Search the web for examples of GROUPING too. It can be very complicated and difficult but you'll have an impressive capability in your toolbox.
--Peter
February 2, 2006 at 10:03 am
On the other hand...
You can get the information that you need with COMPUTE BY but it won't come back in one result set. This is one reason I've had to go the other, more complicated route; the other reason being that it had to port to/from Oracle.
--Peter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply