Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Different Approach Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:10 AM
Points: 3, 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?
Post #1466900
Posted Monday, June 24, 2013 2:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,093, Visits: 12,570
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 Moden's 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)
Post #1466904
Posted Tuesday, June 25, 2013 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:10 AM
Points: 3, 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.
Post #1467113
Posted Tuesday, June 25, 2013 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:10 AM
Points: 3, 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.

  Post Attachments 
example.xlsx (4 views, 9.61 KB)
Post #1467117
Posted Tuesday, June 25, 2013 7:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 6,735, Visits: 8,495
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1467148
Posted Tuesday, June 25, 2013 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,093, Visits: 12,570
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 Moden's 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)
Post #1467150
Posted Tuesday, June 25, 2013 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,826, Visits: 14,060
-- 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
Post #1467194
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse