March 16, 2018 at 6:10 am
I need a query to transfer my invoicedetails to another program.
I can find all the data needed, but I receive the same fields in all the rows while I need different fields as follows :
row 1 : journal, accountnumber, date, duedate, amount,
row 2 : journal, ledgeraccount, date, duedate, amount, vatcode, salesnumber, exchangecode,
Then for the next invoice again row 1, row 2
v01 51640539 2018/02/05 2018/03/07 1804.44 2180123 eur 0.000000000000
v01 700100 2018/02/05 2018/03/07 -1311.12 EX 2180123 eur 0.000000000000
v01 700100 2018/02/05 2018/03/07 -133.02 EX 2180123 eur 0.000000000000
v01 700100 2018/02/05 2018/03/07 -330.30 EX 2180123 eur 0.000000000000
v01 744011 2018/02/05 2018/03/07 -30.00 EX 2180123 eur 0.000000000000
v01 744011 2018/02/05 2018/05/13 -29.00 CM 2180327 eur 0.000000000000
v01 700200 2018/02/05 2018/05/13 -328.32 CM 2180327 eur 0.000000000000
March 16, 2018 at 6:59 am
This is the first thing off the top of my head. I'm sure a variation of this will get the output you are looking for. For future posts, please read the link below the signature line to learn how to post data, ddl and expected results.
Select case when RowNum % 2 = 0 then c1 else c2 end,
case when RowNum % 2 = 0 then c3 else c4 end,
case when RowNum % 2 = 0 then c5 else c6 end,
case when RowNum % 2 = 0 then c7 else c8 end
from (
select c1, c2, c3, c3, c4, c5, c6, c7, c8,
row_number() over(Order by (Select Null)) RowNum
from someTable
) x
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 18, 2018 at 5:45 am
I need a query to transfer my invoicedetails to another program.
I can find all the data needed, but I receive the same fields in all the rows while I need different fields as follows :
row 1 : ledtrans.numberseries, ledtrans.accountnumber, debinvjour. invoicedate, debinvjour.invoiceduedate,
rows 2 to ... : ledtrans.numberseries, ledtrans.accountnumber, ledtrans.vatcode, debinvjour.salesnumber,
Then for the next invoice again row 1, rows 2 to ...
select
row_number () over (partition by debinvjour.ledgervoucher order by ledtrans.accountnumber) as generatedrank,
case when ledtrans.numberseries='vk' then 'v01'
when ledtrans.numberseries='vkcn' then 'v02'
else ledtrans.numberseries end,
case when (ledtrans.accountnumber =400000 OR ledtrans.accountnumber=400001) then debinvjour.invoiceaccount else ledtrans.accountnumber end,
convert (numeric(28,2),ledtrans.amountmst),
case when ledtrans.vatcode='vbtwex' then 'EX'
when ledtrans.vatcode='vbtwic' then 'CM'
when ledtrans.vatcode='vtwicd' then 'VCMD'
when ledtrans.vatcode='vbtw03' then '21'
else ledtrans.vatcode end,
ledtrans.vatamount,
debinvjour.invoicenumber,
convert (varchar(10),debinvjour.invoicedate,111),
convert (varchar(10),debinvjour.invoiceduedate,111),
debinvjour.salesnumber,
case when debinvjour.exchangecode is null then debinvjour.exchangecode else 'eur' end
from debinvjour,ledtrans
where (ledtrans.numberseries='vk' or ledtrans.numberseries='vkcn') and (debinvjour.invoicedate between convert(datetime,'2018-02-05 00:00:00',102)
AND CONVERT(datetime,'2018-02-09 00:00:00',102)) and (debinvjour.ledgervoucher = ledtrans.voucher) and (ledtrans.accountnumber not between 410000 and 499999)
March 28, 2018 at 3:02 pm
Part of the reason you will have trouble is because you can't have columns with differing data types. SQL Server just doesn't work that way. As you need to export this data, I'll suggest combining the columns into a "delimited row", so that no matter which columns get selected, from the point of view of the SQL Server row, it's all character data in one column, and it will be the job of the import process to use the delimiter to separate the columns. Take a look at the following query, and see if this helps:WITH ROW_NUMBERS AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN
FROM (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) AS X
CROSS APPLY (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) AS Y
CROSS APPLY (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) AS Z
),
RAW_DATA AS (
SELECT ROW_NUMBER() OVER(PARTITION BY D.ledgervoucher ORDER BY L.accountnumber) AS generatedrank,
CASE
WHEN L.numberseries = 'vk' THEN 'v01'
WHEN L.numberseries = 'vkcn' THEN 'v02'
ELSE L.numberseries
END AS numberseries,
CASE
WHEN L.accountnumber IN (400000, 400001) THEN D.invoiceaccount
ELSE L.accountnumber
END AS accountnumber,
CONVERT(numeric(28,2), L.amountmst) AS MISSING_COL_NAME_AMTMST,
CASE L.vatcode
WHEN 'vbtwex' THEN 'EX'
WHEN 'vbtwic' THEN 'CM'
WHEN 'vtwicd' THEN 'VCMD'
WHEN 'vbtw03' THEN '21'
ELSE L.vatcode
END AS vatcode,
L.vatamount,
D.invoicenumber,
CONVERT(varchar(10), D.invoicedate, 111) AS invoicedate,
CONVERT(varchar(10), D.invoiceduedate, 111) AS invoiceduedate,
D.salesnumber,
CASE
WHEN D.exchangecode IS NULL THEN D.exchangecode
ELSE 'eur'
END AS MISSING_COL_NAME_EXCHGCODE
FROM debinvjour AS D
INNER JOIN ledtrans AS L
ON D.ledgervoucher = L.voucher
WHERE L.numberseries IN('vk', 'vkcn')
AND D.invoicedate BETWEEN CONVERT(datetime, '2018-02-05 00:00:00') AND CONVERT(datetime, '2018-02-09 00:00:00')
AND L.accountnumber NOT BETWEEN 410000 AND 499999
)
SELECT
CASE R.RN
WHEN 1 THEN
ISNULL(D.numberseries, '') + '|' +
ISNULL(CONVERT(varchar(11), D.accountnumber), '') + '|' +
ISNULL(D.invoicedate, '') + '|' +
ISNULL(D.invoiceduedate, '')
ELSE
ISNULL(D.numberseries, '') + '|' +
ISNULL(CONVERT(varchar(11), D.accountnumber), '') + '|' +
ISNULL(D.vatcode, '') + '|' +
ISNULL(D.salesnumber, '')
END AS ROW_DATA
FROM RAW_DATA AS D
INNER JOIN ROW_NUMBERS AS R
ON D.generatedrank = R.RN
ORDER BY D.numberseries, D.accountnumber, D.generatedrank;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply