June 24, 2013 at 2:24 pm
I am in the process of writing a stored procedure to extract data from one of our systems, to import it into another. I am basically pulling records from invoice tables to import into the general ledger. So my output is a debit and a credit record. The problem I am having is if there is only one debit and one credit (to balance) I need them to be on one line instead of separate lines.
So here is what I have. I have one cursor for the debit and one cursor for the credits. The reason I did this is incase there are multiple lines to an invoice, I can do the price - discount + tax for each line for the debits, because they may be for different accounts, and then the total as the credit. I can do a case statment within my select to combine them into one line in one cursor but it then shows up in the other cursor. I'd like to get away from the cursor but I don't even know where to begin as I've just learned how to write the cursor not that long ago. And, I think I need a cursor because I do have to do a line by line analysis. I apologize if my post is unclear as I tried to give a brief synopsis of my sproc. Thank you for any help/advice in advance. I think I need a different approach to pulling the records. Am I wrong?
June 24, 2013 at 2:31 pm
Hi and welcome to the forums. I would agree that getting rid of the cursor is a good way to go. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
June 25, 2013 at 6:50 am
Ok, so here is the jyst of my procedure.
CREATE PROCEDURE ForExportSp
@Input nvarchar(20) --this get popualated from a drop down in Excel
,@SdateDateType = NULL --depending on what is chosen above, these parameters will be filled in
,@EdateDateType = NULL
,@Inv1InvNumType = NULL
,@Inv2 InvNumType = NULL
,@Inv3InvNumType = NULL --the user has the ability to enter up to 20 Inv#
AS
DECLARE
@InvDatedatetime
,@BookDatedatetime --system generated date for posting
,@InvTextnvarchar(99) --I concatenate a few fields for these
,@InvAmtAmountType
,@Debitnvarchar(5) --debit account numbers
,@Creditnvarchar(5) --credit account numbers
,@LinePricedecimal(21,8) --price for each line in invoice
,@LineCountint--count of invoice lines
,@LineDiscdecimal(21,8) --line discount
,@LineTaxdecimal(21,8)
,@TaxAmtAmountType
,@NetAmtAmountType
,@GrossAmtAmountType
DECLARE @resultset TABLE (
Belegdatumdatetime, --InvDate
Buchungsdatumdatetime, --BookDate
Buchungstextnvarchar(99), --InvText
Buchungsbetragdecimal(21,8), --InvAmt
Sollkontonvarchar(10), --Debit
Habenkontonvarchar(10), --Credit
)--These are German field names, this is for our German location
IF @Input = 'Rechnung Nr' --This is one of the options in the drop down box, if this is chosen then the @Inv1, @Inv2...are the expected variablew
BEGIN
DECLARE CdtCrs cursor LOCAL STATIC FOR
SELECT
(CONVERT(datetime, ih.inv_date, 104)) --InvDate
,(CONVERT(datetime, getdate(), 104)) --BookDate
,ca.name + ' ' + dbo.NonExpandKy(10, ih.inv_num) + '/1' --The concatenation of fields for the @InvText
,ih.price --@InvAmt
,'' -- Debit
,'CREDIT' = (select z.Uf_FTOldCustNo from customer z where z.cust_num = c.cust_num and z.cust_seq = 0) --Credit Account Numbers
FROM inv_hdr ih
JOIN customer c on ih.cust_num = c.cust_num and c.cust_seq = ih.cust_seq
join custaddr ca on ih.cust_num = ca.cust_num and ih.cust_seq = ca.cust_seq
WHERE ih.inv_num IN (dbo.ExpandKy(10, @Inv1),dbo.ExpandKy(10, @Inv2),
dbo.ExpandKy(10, @Inv3),dbo.ExpandKy(10, @Inv4),dbo.ExpandKy(10, @Inv5),dbo.ExpandKy(10, @Inv6),
dbo.ExpandKy(10, @Inv7),dbo.ExpandKy(10, @Inv8),dbo.ExpandKy(10, @Inv9),dbo.ExpandKy(10, @Inv10),
dbo.ExpandKy(10, @Inv11),dbo.ExpandKy(10, @Inv12),dbo.ExpandKy(10, @Inv13),dbo.ExpandKy(10, @Inv14),
dbo.ExpandKy(10, @Inv15),dbo.ExpandKy(10, @Inv16),dbo.ExpandKy(10, @Inv17),dbo.ExpandKy(10, @Inv18),
dbo.ExpandKy(10, @Inv19),dbo.ExpandKy(10, @Inv20))
and ih.inv_num like 'G%'
and ih.price > 0
OPEN CdtCrs
FETCH NEXT FROM CdtCrs INTO
@InvDate,
@BookDate,
@InvText,
@InvAmt,
@Debit,
@Credit,
WHILE (@@fetch_status =0)
BEGIN
INSERT INTO @resultSet
(Belegdatum
,Buchungsdatum
,Buchungstext
,Buchungsbetrag
,Sollkonto
,Habenkonto
)
VALUES
(@InvDate,
@BookDate,
@InvText,
@InvAmt,
@Debit,
@Credit,
)
FETCH NEXT FROM CdtCrs INTO
@InvDate,
@BookDate,
@InvText,
@InvAmt,
@Debit,
@Credit
END
CLOSE CdtCrs
DEALLOCATE CdtCrs
--DEBITS G%
DECLARE DbtCrs cursor LOCAL STATIC FOR
select
(CONVERT(datetime, ih.inv_date, 104)) as InvDate
,(CONVERT(datetime, getdate(), 104)) as BookDate
,ca.name + ' ' + dbo.NonExpandKy(10, ii.inv_num) + '/1'
,(ii.qty_invoiced * ii.price) as LinePrice
,(ih.disc_amount)/(count(ii.inv_line) OVER(PARTITION BY ih.inv_num)) as LineDisc --The disc_amount is a whole number in the header table, not line by line
,'TaxAmt' = CASE WHEN ih.tax_code1 in ('EU', 'NON-EU') THEN
'0'
ELSE (ix.tax_rate/100) END
,'DEBIT' =CASE WHEN ih.tax_code1 = 'EU'
THEN (SELECT x.eu_account FROM ViewForTaxAccts X WHERE x.item = ii.item)
WHEN ih.tax_code1 = 'NON-EU'
THEN (SELECT x.noneu_account FROM ViewForTaxAccts X WHERE x.item = ii.item)
WHEN ih.tax_code1 is null
THEN (SELECT x.inland_account FROM ViewForTaxAccts X WHERE x.item = ii.item)
END
FROM inv_hdr ih
join inv_item ii on ih.inv_num = ii.inv_num
LEFT join inv_stax ix on ih.inv_num = ix.inv_num
JOIN item i on ii.item = i.item
JOIN customer c on ih.cust_num = c.cust_num and c.cust_seq = ih.cust_seq
join custaddr ca on ih.cust_num = ca.cust_num and ih.cust_seq = ca.cust_seq
WHERE ii.inv_num IN (dbo.ExpandKy(10, @Inv1),dbo.ExpandKy(10, @Inv2),
dbo.ExpandKy(10, @Inv3),dbo.ExpandKy(10, @Inv4),dbo.ExpandKy(10, @Inv5),dbo.ExpandKy(10, @Inv6),
dbo.ExpandKy(10, @Inv7),dbo.ExpandKy(10, @Inv8),dbo.ExpandKy(10, @Inv9),dbo.ExpandKy(10, @Inv10),
dbo.ExpandKy(10, @Inv11),dbo.ExpandKy(10, @Inv12),dbo.ExpandKy(10, @Inv13),dbo.ExpandKy(10, @Inv14),
dbo.ExpandKy(10, @Inv15),dbo.ExpandKy(10, @Inv16),dbo.ExpandKy(10, @Inv17),dbo.ExpandKy(10, @Inv18),
dbo.ExpandKy(10, @Inv19),dbo.ExpandKy(10, @Inv20))
and ih.inv_num like 'G%'
and ii.price > 0
OPEN DbtCrs
FETCH NEXT FROM DbtCrs INTO
@InvDate,
@BookDate,
@InvText,
@LinePrice,
@LineDisc,
@LineTax,
@Debit,
@Credit,
WHILE (@@fetch_status =0)
BEGIN
SET@NetAmt = @LinePrice - @LineDisc
SET@InvAmt = (@NetAmt * @LineTax) + @NetAmt
INSERT INTO @resultSet
(Belegdatum
,Buchungsdatum
,Buchungstext
,Buchungsbetrag
,Sollkonto
,Habenkonto
)
VALUES
(@InvDate,
@BookDate,
@InvText,
@InvAmt,
@Debit,
@Credit
)
FETCH NEXT FROM DbtCrs INTO
@InvDate,
@BookDate,
@InvText,
@LinePrice,
@LineDisc,
@LineTax,
@Debit,
@Credit
END
CLOSE DbtCrs
DEALLOCATE DbtCrs
END
Then I repeat the above for the other input option, which is by date, so my where clause changes. Other then that it is the exact same. So as you can see, I get one row for debit and one for credits. When the inv_num begins with 'G', the customer account will get the credit and the "sales" accounts will get the debit. The problem is there may or may not be multiple accounts that get debited, if there are, this works perfectly, but if its only one account that gets the debit, then I need the credit and the debit to be on the same line.
Thank you for any help in advance, I just am not sure how else to go about it.
June 25, 2013 at 6:55 am
I'm sorry, I forgot to add an example of the output...I attached a spreadsheet because of the way it looked when I pasted it in here. Again, thank you for any help.
June 25, 2013 at 7:44 am
aren't you just killing your engine using all these cursors, UDF in in-clauses, row level nested views ( who knows what is in there ) ...
How about adding a group by to create your wanted row set.
What SQLServer version are you working with ?
Why aren't you using SSIS ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 25, 2013 at 7:47 am
Thanks for posting the proc that you are working with. As I said previously we need to have ddl and sample data. These both seem to be missing from your post. Also, you need to post ddl for the ExpandKy scalar function, that is going to be a performance nightmare. The other scalar function (NonExpandKy) should also be examined.
_______________________________________________________________
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/
June 25, 2013 at 8:29 am
-- no cursors are required here:
-- Construct a table from the list of invoice numbers
IF object_id('TempDB..#InvoiceList') IS NOT NULL
DROP TABLE #InvoiceList
SELECT inv_num
INTO #InvoiceList
FROM (
SELECT inv_num =
dbo.ExpandKy(10, @Inv1) UNION ALL
dbo.ExpandKy(10, @Inv2) UNION ALL
dbo.ExpandKy(10, @Inv3) UNION ALL
dbo.ExpandKy(10, @Inv4) UNION ALL
dbo.ExpandKy(10, @Inv5) UNION ALL
dbo.ExpandKy(10, @Inv6) UNION ALL
dbo.ExpandKy(10, @Inv7) UNION ALL
dbo.ExpandKy(10, @Inv8) UNION ALL
dbo.ExpandKy(10, @Inv9) UNION ALL
dbo.ExpandKy(10, @Inv10) UNION ALL
dbo.ExpandKy(10, @Inv11) UNION ALL
dbo.ExpandKy(10, @Inv12) UNION ALL
dbo.ExpandKy(10, @Inv13) UNION ALL
dbo.ExpandKy(10, @Inv14) UNION ALL
dbo.ExpandKy(10, @Inv15) UNION ALL
dbo.ExpandKy(10, @Inv16) UNION ALL
dbo.ExpandKy(10, @Inv17) UNION ALL
dbo.ExpandKy(10, @Inv18) UNION ALL
dbo.ExpandKy(10, @Inv19) UNION ALL
dbo.ExpandKy(10, @Inv20)
) d
WHERE inv_num IS NOT NULL
-- reconstruct the credits query and run results into temp table #Credits
SELECT
Belegdatum= CONVERT(datetime, ih.inv_date, 104),
Buchungsdatum= GETDATE(), -- GETDATE() doesn't need converting to DATETIME
Buchungstext= ca.name + ' ' + dbo.NonExpandKy(10, ih.inv_num) + '/1', --The concatenation of fields for the @InvText
Buchungsbetrag= ih.price, --@InvAmt
Sollkonto= '',
Habenkonto= (SELECT z.Uf_FTOldCustNo FROM customer z WHERE z.cust_num = c.cust_num and z.cust_seq = 0) --Credit Account Numbers
INTO #Credits
FROM inv_hdr ih
JOIN customer c
ON ih.cust_num = c.cust_num AND c.cust_seq = ih.cust_seq
INNER JOIN custaddr ca
ON ih.cust_num = ca.cust_num AND ih.cust_seq = ca.cust_seq
WHERE ih.inv_num IN (SELECT inv_num FROM #InvoiceList)
AND ih.inv_num like 'G%'
AND ih.price > 0
-----------------------------------------------------------------------
-- reconstruct the debits query and run results into temp table #debits
SELECT
Belegdatum= InvDate,
Buchungsdatum= BookDate,
Buchungstext= InvText,
Buchungsbetrag= ((LinePrice - LineDisc) * LineTax) + (LinePrice - LineDisc),
Sollkonto= DEBIT,
Habenkonto= CREDIT
INTO #Debits
FROM (
SELECT
InvDate= CONVERT(DATETIME, ih.inv_date, 104),
BookDate= GETDATE(),
InvText= ca.name + ' ' + dbo.NonExpandKy(10, ii.inv_num) + '/1',
LinePrice= ii.qty_invoiced * ii.price,
LineDisc= ih.disc_amount / COUNT(ii.inv_line) OVER(PARTITION BY ih.inv_num), --The disc_amount is a whole number in the header table, not line by line
LineTax= CASE
WHEN ih.tax_code1 IN ('EU', 'NON-EU') THEN 0
ELSE ix.tax_rate/100 END,
[DEBIT]= CASE
WHEN ih.tax_code1 = 'EU' THEN x.eu_account
WHEN ih.tax_code1 = 'NON-EU' THEN x.noneu_account
WHEN ih.tax_code1 IS NULL THEN x.inland_account
END,
[CREDIT] = NULL
FROM inv_hdr ih
INNER JOIN inv_item ii on ih.inv_num = ii.inv_num
CROSS APPLY ( -- read the lookup just once
SELECT eu_account, noneu_account, inland_account
FROM ViewForTaxAccts xi
WHERE xi.item = ii.item
) x
LEFT JOIN inv_stax ix on ih.inv_num = ix.inv_num
INNER JOIN item i on ii.item = i.item
INNER JOIN customer c on ih.cust_num = c.cust_num and c.cust_seq = ih.cust_seq
INNER join custaddr ca on ih.cust_num = ca.cust_num and ih.cust_seq = ca.cust_seq
WHERE ih.inv_num IN (SELECT inv_num FROM #InvoiceList)
AND ih.inv_num LIKE 'G%'
AND ii.price > 0
) d
-- now compare the two temp tables, #Credits and #Debits
-- consider aggregating the #debits table
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply