SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Different Approach


Different Approach

Author
Message
volleygirl029
volleygirl029
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 23
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?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61893 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
volleygirl029
volleygirl029
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 23
Ok, so here is the jyst of my procedure.

CREATE PROCEDURE ForExportSp

@Input nvarchar(20) --this get popualated from a drop down in Excel
, @Sdate DateType = NULL --depending on what is chosen above, these parameters will be filled in
, @Edate DateType = NULL
, @Inv1 InvNumType = NULL
, @Inv2 InvNumType = NULL
, @Inv3 InvNumType = NULL --the user has the ability to enter up to 20 Inv#


AS

DECLARE
@InvDate datetime
, @BookDate datetime --system generated date for posting
, @InvText nvarchar(99) --I concatenate a few fields for these
, @InvAmt AmountType
, @Debit nvarchar(5) --debit account numbers
, @Credit nvarchar(5) --credit account numbers
, @LinePrice decimal(21,8) --price for each line in invoice
, @LineCount int --count of invoice lines
, @LineDisc decimal(21,8) --line discount
, @LineTax decimal(21,8)
, @TaxAmt AmountType
, @NetAmt AmountType
, @GrossAmt AmountType

DECLARE @resultset TABLE (
Belegdatum datetime, --InvDate
Buchungsdatum datetime, --BookDate
Buchungstext nvarchar(99), --InvText
Buchungsbetrag decimal(21,8), --InvAmt
Sollkonto nvarchar(10), --Debit
Habenkonto nvarchar(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.
volleygirl029
volleygirl029
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 23
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.
Attachments
example.xlsx (12 views, 9.00 KB)
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29431 Visits: 8986
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61893 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40956 Visits: 20000
-- 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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search