Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Different Approach


Different Approach

Author
Message
volleygirl029
volleygirl029
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16991
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)
volleygirl029
volleygirl029
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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         
   Wink         
VALUES
   (   @InvDate,
      @BookDate,
      @InvText,
      @InvAmt,
      @Debit,
      @Credit,
   Wink

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         
   Wink            
VALUES
   (   @InvDate,
      @BookDate,
      @InvText,
      @InvAmt,
      @Debit,
      @Credit
   Wink

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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
Attachments
example.xlsx (4 views, 9.00 KB)
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6966 Visits: 8839
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 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16991
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)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8929 Visits: 19009
-- 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
   Wink 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