October 13, 2015 at 8:50 am
Hello comunity
I need to build a query based on InvoiceHeader joining InvoiceLines, but so far no problem.
The challenge is to get the data in a temporary table as follows:
only show one line with the header data and the various lines of the same document.
In practice will not repeat the header data.
some sample data:
CREATE TABLE #TempTable (Cabstamp VARCHAR(25), nmdoc VARCHAR(20),fno INT, nome VARCHAR(60), no INT, InvoiceLineStamp varchar(60),qtt NUMERIC(12,2), epv NUMERIC(12,2), etiliquido NUMERIC(12,2))
INSERT INTO #temptable (Cabstamp ,nmdoc, fno , nome, no , InvoiceLineStamp, qtt , epv , etiliquido )
SELECT
'ISA15011342134,245229283', 'C10-Fatura',1,'XPTO, LDA.', 1424, 'ISA15011342134,245229283',10.000,1.500000,15.000000
UNION all
SELECT 'ISA15011342134,245229283', 'C10-Fatura',1,'XPTO, LDA.', 1424,'ISA15011342134,245229283',1.000,14.620000,13.890000
UNION all
SELECT 'ISA15011342134,245229283', 'C10-Fatura',1,'XPTO, LDA.', 1424,'ISA15011342134,245229283',2.000,1.000000,2.000000
UNION all
SELECT 'ISA15011342349,416926665', 'C10-Fatura',2,'LUIS SANTOS. LDA', 1585,'ISA15011342349,416926665',10.000,5.000000,50.000000
UNION all
SELECT 'ISA15011342349,416926665', 'C10-Fatura',2,'LUIS SANTOS. LDA', 1585,'ISA15011342349,416926665',7.000,1.190000,8.080000
UNION all
SELECT 'ISA15011342349,416926665', 'C10-Fatura',2,'LUIS SANTOS. LDA', 1585,'ISA15011342349,416926665',1.000,15.090000,14.640000
UNION all
SELECT 'ISA15011342349,416926665', 'C10-Fatura',2,'LUIS SANTOS. LDA', 1585,'ISA15011342349,416926665',1.000,2.000000,2.000000
UNION all
SELECT 'ISA15011342349,416926665', 'C10-Fatura',2,'LUIS SANTOS. LDA', 1585,'ISA15011342349,416926665',4.000,2.000000,8.000000
UNION all
SELECT 'ISA15011942792,499540359', 'C10-Fatura',3,'CLINIC EPE ', 1612,'ISA15011942792,499540359', 10.000,2.000000,20.000000
UNION all
SELECT 'ISA15011942792,499540359', 'C10-Fatura',3,'CLINIC EPE ', 1612,'ISA15011942792,499540359', 1.5000,2.000000,3.000000
UNION all
SELECT 'ISA15011942792,499540359', 'C10-Fatura',3,'CLINIC EPE ', 1612,'ISA15011942792,499540359', 6.000,3.300000,19.800000
UNION all
SELECT 'ISA15011942792,499540359', 'C10-Fatura',3,'CLINIC EPE ',1612,'ISA15011942792,499540359', 10.000,7.050000,70.500000
UNION all
SELECT 'ISA15011942792,499540359', 'C10-Fatura',3,'CLINIC EPE ', 1612,'ISA15011942792,499540359', 6.000,2.000000,12.000000
SELECT * FROM #temptable
I dont know if i must use a Cursor that verify when field CabStamp change, write the first line and after that put the data equal to " ", or another technique to do that, CTE or passing Query to XML ??
the example for the result that i pretend, for example for my 2 first invoices are:
ISA15011342134,245229283C10-Fatura1XPTO, LDA. 1424 10.00 1.50 15.00
1.00 14.6213.89
2.00 1.002.00
ISA15011342349,416926665C10-Fatura2LUIS SANTOS. LDA158510.00 5.0050.00
7.00 1.198.08
1.00 15.0914.64
1.00 2.00 2.00
4.00 2.00 8.00
The first 5 columns are invoice Header, the last 3 ones are invoice lines.
Many thanks for your help.
Best Regards,
LSantos
October 13, 2015 at 9:13 am
This is something that you'd normally handle in your presentation layer, front end, application - call it what you will. It's the kind of thing that Reporting Services can do for you. It's possible to do it in T-SQL, but it's a bit messy. Something like the code below will work, but it doesn't take account of numbers, which can't be converted to blanks, so you'd need to tweak it first.
WITH Numbered AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY
Cabstamp
,nmdoc
,fno
,nome
,no
,InvoiceLineStamp
ORDER BY qtt
) RowNo
FROM #TempTable
)
SELECT
CASE RowNo WHEN 1 THEN Cabstamp ELSE '' END AS Cabstamp
,CASE RowNo WHEN 1 THEN nmdoc ELSE '' END AS nmdoc
,CASE RowNo WHEN 1 THEN fno ELSE '' END AS fno
,CASE RowNo WHEN 1 THEN nome ELSE '' END AS nome
,CASE RowNo WHEN 1 THEN no ELSE '' END AS no
,CASE RowNo WHEN 1 THEN InvoiceLineStamp ELSE '' END AS InvoiceLineStamp
,qtt
,epv
,etiliquido
FROM Numbered
John
October 13, 2015 at 9:30 am
Hello John
Thanks for your reply.
I agree that using any reporting tool or SSRS is much easier, but in this case I have a tool that turns a SELECT statment into HTML format for e-mail Body, That is why i needed help to make sure it could be possible using a Query.
Many thanks again for your help.
Best regards
LSantos
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply