Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

This is a real life scenario I came across recently. We build applications for the health care industry and one of the modules in the application generates invoices for the services provided to the patients and sent them to the insurance companies. The information is sent to the insurance companies as electronic files. Each company have their own format and rules for the electronic file they accept.

Before I explain the rest of the requirements, let me show you some sample data.

InvID  BatchNumber InvoiceNumber VisitDate   Amount
------ ----------- ------------- ----------- ------ 
1      10000001    20001         2009-01-01  50.00
2      10000001    20001         2009-01-02  50.00
3      10000001    20001         2009-01-03  50.00
4      10000001    20001         2009-01-04  50.00
5      10000001    20002         2009-01-01  50.00
6      10000001    20002         2009-01-02  50.00
7      10000001    20002         2009-01-03  50.00
8      10000001    20003         2009-01-01  50.00
9      10000001    20003         2009-01-02  50.00
10     10000001    20004         2009-01-01  50.00
11     10000001    20004         2009-01-02  50.00
12     10000001    20004         2009-01-03  50.00
13     10000001    20004         2009-01-04  50.00
14     10000001    20004         2009-01-05  50.00
15     10000001    20004         2009-01-06  50.00
16     10000001    20005         2009-01-01  50.00
17     10000001    20005         2009-01-02  50.00
18     10000001    20005         2009-01-13  50.00
19     10000001    20005         2009-01-14  50.00
20     10000001    20005         2009-01-15  50.00
21     10000001    20005         2009-01-06  50.00
22     10000001    20005         2009-01-07  50.00
23     10000001    20005         2009-01-08  50.00
24     10000001    20006         2009-01-01  50.00
25     10000002    20007         2009-01-01  50.00
26     10000002    20007         2009-01-02  50.00
27     10000002    20007         2009-01-03  50.00
28     10000002    20008         2009-01-01  50.00
29     10000002    20008         2009-01-02  50.00
30     10000002    20008         2009-01-03  50.00

Let me start explaining the rules and make the requirement further clear. The insurance company needs an electronic file in a certain format (the format is irrelevant for us). A separate file needs to be generated for each batch. So based on the sample data we need to generate two files (one for each batch).

Now comes the complex part. Each batch should contain not more than 10 rows. The maximum number of lines we can put in a file is 10. So we need to break each batch into parts that contains not more than 10 rows. When breaking the batches, there is one more rule to follow. While breaking the batches, we should take care that we do not break invoices.

The challenge here is to generate an additional column that helps us to break the batches into pieces of 10 or lesser rows. Here is the expected output:

InvID  BatchNumber InvoiceNumber VisitDate   Amount Set
------ ----------- ------------- ----------- ------ ---
1      10000001    20001         2009-01-01  50.00  1
2      10000001    20001         2009-01-02  50.00  1
3      10000001    20001         2009-01-03  50.00  1
4      10000001    20001         2009-01-04  50.00  1
5      10000001    20002         2009-01-01  50.00  1
6      10000001    20002         2009-01-02  50.00  1
7      10000001    20002         2009-01-03  50.00  1
8      10000001    20003         2009-01-01  50.00  1
9      10000001    20003         2009-01-02  50.00  1
10     10000001    20004         2009-01-01  50.00  2
11     10000001    20004         2009-01-02  50.00  2
12     10000001    20004         2009-01-03  50.00  2
13     10000001    20004         2009-01-04  50.00  2
14     10000001    20004         2009-01-05  50.00  2
15     10000001    20004         2009-01-06  50.00  2
16     10000001    20005         2009-01-01  50.00  3
17     10000001    20005         2009-01-02  50.00  3
18     10000001    20005         2009-01-13  50.00  3
19     10000001    20005         2009-01-14  50.00  3
20     10000001    20005         2009-01-15  50.00  3
21     10000001    20005         2009-01-06  50.00  3
22     10000001    20005         2009-01-07  50.00  3
23     10000001    20005         2009-01-08  50.00  3
24     10000001    20006         2009-01-01  50.00  3
25     10000002    20007         2009-01-01  50.00  1
26     10000002    20007         2009-01-02  50.00  1
27     10000002    20007         2009-01-03  50.00  1
28     10000002    20008         2009-01-01  50.00  1
29     10000002    20008         2009-01-02  50.00  1
30     10000002    20008         2009-01-03  50.00  1

Sample Data

Use the following script to generate the sample data needed for this challenge 

DECLARE @t TABLE (
	InvID INT IDENTITY,
	BatchNumber INT,
	InvoiceNumber INT, 
	VisitDate DATETIME,
	Amount MONEY )
INSERT INTO @t(BatchNumber, InvoiceNumber, VisitDate, Amount)
SELECT 10000001,20001,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-05',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-13',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-14',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-15',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-07',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-08',50.00 UNION ALL
SELECT 10000001,20006,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-03',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-03',50.00

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. The solution should work on SQL Server 2005, 2008 or later versions
  3. Use this forum for any questions related to TSQL Challenge #13

About the Author.


 Share

Related Posts

TSQL Challenge 13 – Solution by Neeraj Mathur and other TSQL Heros

TSQL Challenge 13 – Solution by Isham Hamin

TSQL Challenge 13 – Winning Solution by Jim Carter

Congratulations to the Winners of TSQL Challenge 13!

Comments

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Thursday, September 03, 2009 1:43 PM by Fabien "Waldar" Contaminard

That's an interesting challenge !

The longest part was to write a plain SQL query to fill a million rows table which match with your rules.

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Friday, September 04, 2009 3:21 AM by Sergejack

Invoice 2001+2002+2003+2007+2008 altogether makes 15 lines.

What do you mean "we should take care that we do not break invoices", can't I just batch all 2004 with 2008 ?

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Friday, September 04, 2009 3:39 AM by Jacob Sebastian

All the rows that belongs to one invoice should be in one set. You should not put part of the rows in an invoice to one set and the rest to another set. The output listing shows how the output should look like.

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Friday, September 04, 2009 4:24 AM by Sergejack

I finally get that pieces of batches are grouped on BatchNumber. ^^

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Friday, September 04, 2009 10:21 PM by Jacob Sebastian

@Sergejack,

please send your solution to the email address mentioned in beyondrelational.com/.../tsql-challenges-terms-and-conditions-submission-guidelines-evaluation-process-and-faq.aspx

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Saturday, September 05, 2009 1:40 PM by Sergejack

Although I didn't get any recieve confirmation (should I have?), I sent it yesterday. (I sent 2, one of them isn't valid as it doesn't take batchnumber into consideration).

BTW: I can't post on the forum where this challenge is discused about.

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Saturday, September 05, 2009 4:25 PM by Ashish Gilhotra

Yes we get your both solutions and, You need to be a member of the group 'TSQL Challenges' to be able to post questions or comments in the discussion forum.

Refer to FAQ section for general questions here..

beyondrelational.com/.../tsql-challenges-terms-and-conditions-submission-guidelines-evaluation-process-and-faq.aspx

Thanks

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Tuesday, September 08, 2009 4:56 PM by Fabien "Waldar" Contaminard

For interested people, as I said in a previous comment I wrote a plain SQL script to create and fill a one million rows table.

Feel free to grab it :

www.waldar.org/.../Generation_Table.sql

The generation of this table took 15 minutes on my home computer, so be patient !

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Tuesday, September 15, 2009 10:45 AM by kgiagias

Hello,

I'd like to ask something about the rule "When breaking the batches, there is one more rule to follow. While breaking the batches, we should take care that we do not break invoices. "

Do this implies that no invoice has more than 10 records???

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Tuesday, September 15, 2009 10:53 AM by Jacob Sebastian

Yes, you can assume that no invoice will have more than 10 records.

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Tuesday, September 15, 2009 11:54 AM by Aravind

Jacob Sebastian ,

i don't know where to post my query, so I posted in the comments. can you explain me if my query is correct or is it wrong?

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Tuesday, September 15, 2009 12:33 PM by Jacob Sebastian

Hi Arvind,

Please review the 'submission guidelines' at beyondrelational.com/.../tsql-challenges-terms-and-conditions-submission-guidelines-evaluation-process-and-faq.aspx.

Please send us your solution by email as per the guidelines listed above. It will take us a while before we could evaluate it. We will let you know about it once the evaluation is done.

regards

Jacob

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Thursday, September 17, 2009 5:27 PM by calvinlawson

I wasn't able to comment in the forum for some reason, but I wanted to point out that the expected output appears to be wrong. InvID 25-30 should have a value of 4 in the "Set" column, not 1.  If the value was one that would violate your rule of a max of 10 rows per set.

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Thursday, September 17, 2009 5:41 PM by Ashish Gilhotra

@calvinlawson

you must have an account on beyondrelational.com and you must join group TSQL challenges to post in the forums associated with it.

For output "A separate file needs to be generated for each batch. So based on the sample data we need to generate two files (one for each batch)."

So in this it is mentioned that we have to generate column on the basis of 'batch' so when a new batch number comes up we again start it from SET=1.

Hope it helps..

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Monday, October 19, 2009 1:05 PM by JenniC

This is an excellent challenge. Here is a quick script that will break the file across batch number boundaries. It will create files file1.txt, file2.txt, etc  from input file file.txt.

(I work at a bank :-) And we do this a lot.)

# Script split.txt

var str input ; cat "file.txt" > $input

var str prev_batch, batch ; var int num

var str splitfile

set $wsep="\t"

# Keep going thru lines looking for change of batch.

while ($input <> "")

do

   var str line ; lex "1" $input > $line

   # Batch is the second word.

   wex -p "2" $line > $batch

   if (prev_batch <> $batch)

   do

       # Change of batch. Write previous file

       # but only if non empty.

       if ($splitfile <> "")

           echo $splitfile > { echo "file"+$num+".txt" }

       endif

       set $num = $num+1

       set $prev_batch = $batch

       set $splitfile = ""

   done

   echo $line "\n" >> $splitfile

done

# The last batch is in splitfile.

if ($splitfile <> "")

   echo $splitfile > { echo "file"+$num+".txt" }

endif

# End of script

Script is in biterscripting ( http://www.biterscripting.com - It was probably made for such a challenging task). Save the script as C:/Scripts/split.txt. To run, enter the following command in biterscripting.

script "C:/Scripts/split.txt"

Make sure file.txt exists in current directory. (Use the cd command to change directory.) As far splitting across, you will have to add code for that.

(This is a very good site for refreshing ones programming skills. I enjoyed reading the challenge.)

# re: TSQL Challenge 13 - Break a batch of invoices into multiple parts based on rules

Monday, October 19, 2009 3:04 PM by Jacob Sebastian

JenniC,

Glad to know that you liked the challenges we publish here. The challenge however, is to produce the desired output using a SET based operation. I agree that there are several ways (non SET BASED) to get the output done, however our focus is to play with SET based options and come up with different ways of solving a common problem.

Thanks for the comment and keep participating in TSQL Challenges.

regards

Jacob

Copyright © Rivera Informatic Private Ltd.