January 12, 2010 at 6:34 am
I have a stored procedure that works at the moment. It takes data from a table and formats it and puts it into a 2nd table.
The issue I have is that they want to group some of the fields together and sum a couple as well and I am getting nowhere with my solution. I realise I am being slow but any pointers would be gratefully received.
Forget all the code around it, so the bit I am having an issue with goes like this.
insert into #interface(record)
values(
case
when@material in ('XXX','YYY','ZZZ')
then
'D' +
'9999' +
'JM' + case @Field1
when 'TEST' then convert( char(6), getdate(), 12)
else @Field1
end + '00' + convert(char(6),@dated,112) + -- GROUP BY 1
left( @Field2 + space(18), 18 ) + -- GROUP BY 2
@Field3 +
right('00000000000000' + convert(varchar, abs(@Field4), 0),14) + -- SUM
@Field5 +GROUP BY 4
right('00000000000000' + convert(varchar, abs(@Field6), 0),14) + -- SUM
@Field7 +
space(8) +
'AAA' +
'0000999999'+ -- GROUP BY 5
space(32) +
left(isnull(@Field8,'008888'), 6) + -- GROUP BY 6
space(86) +
@Field9 +
space(7) +@Field10 +-- GROUP BY 3
@Field11 +
space(208)
else
'D' +
'9999' +
'JM' +
case @Field1
when 'TEST' then convert( char(6), getdate(), 12)
else @Field1
end + '00' + convert(char(6),@dated,112) +
left( @Field2 + space(18), 18 ) +
@Field3 +
right('00000000000000' + convert(varchar, abs(@Field4), 0),14) +
@Field5 +
right('00000000000000' + convert(varchar, abs(@Field6), 0),14) +
@Field7 +
space(8) +
'AAA' +
'0000999999'+
space(32) +
left(isnull(@Field8,'008888'), 6) +
space(86) +
@Field9 +
space(7) +
@Field10 +
@Field11 +
space(208)
end
)
Thanks again
January 12, 2010 at 6:40 am
I don't understand your problem. Are you getting any errors? Are you getting wrong results? What should "correct" results look like?
-- Gianluca Sartori
January 12, 2010 at 6:46 am
Sorry - It is not a problem as such more of a query
The result is basically a header record, detail records then a footer record in a 2nd table. It is 1 long string in each case.
There are no errors until I try and start grouping things.
The code is the detail record part. As it is at the moment it works but in the first section of the case you can see I have put some notes after the lines which I need to then do. -- Group 1, -- Group 2 etc and SUM
Does that make more sense?
Thanks for quick reply
January 12, 2010 at 6:55 am
Spikemarks, is it possible to determine what you want to do from these two posts? I don't think so! How about showing two output lists, one containing what you currently have, the other consisting of what the first should look like after aggregation? That would help immensely.
You will almost certainly find it easier to aggregate first then perform your formatting routines afterwards i.e. preaggregate. Have you tried this yet?
Cheers
ChrisM
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
January 12, 2010 at 7:17 am
Ok Here goes
Field1Field2Field3Field4Field5Field6Field7Field8Field9Field10Field11
Batch1Invoice1Post120.00VAT1.75Dummy1XXXProduct1UOM
Batch1Invoice1Post114.33VAT1.23Dummy2XXXProduct1UOM
Batch1Invoice1Post112.44VAT2.45Dummy3XXXProduct2UOM
Batch1Invoice1Post115.44VAT3.12Dummy4XXXProduct2UOM
Batch1Invoice1Post1123.56VAT4.56Dummy5XXXProduct3UOM
Batch1Invoice2Post120.00VAT1.75Dummy1XXXProduct1UOM
Batch1Invoice2Post114.33VAT1.23Dummy2XXXProduct1UOM
Batch1Invoice2Post112.44VAT2.45Dummy3XXXProduct2UOM
Batch1Invoice2Post115.44VAT3.12Dummy4XXXProduct2UOM
Batch1Invoice2Post1123.56VAT4.56Dummy5XXXProduct3UOM
Batch1Invoice2Post120.00VAT1.75Dummy1XXXProduct1UOM
Batch1Invoice2Post114.33VAT1.23Dummy2XXXProduct1UOM
Batch1Invoice3Post112.44VAT2.45Dummy3XXXProduct2UOM
Batch1Invoice3Post115.44VAT3.12Dummy4XXXProduct2UOM
Batch1Invoice3Post1123.56VAT4.56Dummy5XXXProduct3UOM
Records being used for input
Output as of now :
D9999JMBatch1Invoice1Post120.00VAT1.75 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice1Post114.33VAT1.23 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice1Post112.44VAT2.45 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice1Post115.44VAT3.12 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice1Post1123.56VAT4.56 AAA00009999991 xxx Product3UOM
D9999JMBatch1Invoice1Post120.00VAT1.75 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice2Post114.33VAT1.23 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice3Post112.44VAT2.45 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice4Post115.44VAT3.12 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice5Post1123.56VAT4.56 AAA00009999991 xxx Product3UOM
D9999JMBatch1Invoice1Post120.00VAT1.75 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice2Post114.33VAT1.23 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice3Post112.44VAT2.45 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice4Post115.44VAT3.12 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice5Post1123.56VAT4.56 AAA00009999991 xxx Product3UOM
Required Output:
D9999JMBatch1Invoice1Post134.33VAT2.98 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice1Post127.88VAT5.57 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice1Post1123.56VAT4.56 AAA00009999991 xxx Product3UOM
D9999JMBatch1Invoice2Post134.33VAT2.98 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice2Post127.88VAT5.57 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice2Post1123.56VAT4.56 AAA00009999991 xxx Product3UOM
D9999JMBatch1Invoice3Post134.33VAT2.98 AAA00009999991 xxx Product1UOM
D9999JMBatch1Invoice3Post127.88VAT5.57 AAA00009999991 xxx Product2UOM
D9999JMBatch1Invoice3Post1123.56VAT4.56 AAA00009999991 xxx Product3UOM
Forget the spacing between the fields it doesn't look right but I can sort that out.
I hope this makes sense if not not sure what to do next.
I also saw the suggestion previously about doing stuff prior and that is a possibility but I would like to try and get this right if possible.
If not then I will have to think again.
Thanks
January 12, 2010 at 7:38 am
Before anyone says anything I must apologise as I am new to this game. I have just read the way you should post things here and realise I should have done a create table etc script rather than just put data in. Once again I apologise.
January 12, 2010 at 7:51 am
New code etc
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with CREATE TABLE #mytable
CREATE TABLE #mytable (
Field1 Char(10)
Field2 Char(10)
Field3 Char(10)
Field4 Money
Field5 Char(3)
Field6 Money
Field7 Char(10)
Field8 Char(1)
Field9 Char(3)
Field10 Char(10)
Field11 Char(3)
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8,Field9,Field10,Field11)
SELECT
'Batch1','Invoice1','Post1','20.00','VAT','1.75','Dummy','1','XXX','Product1','UOM'
'Batch1','Invoice1','Post1','14.33','VAT','1.23','Dummy','2','XXX','Product1','UOM'
'Batch1','Invoice1','Post1','12.44','VAT','2.45','Dummy','3','XXX','Product2','UOM'
'Batch1','Invoice1','Post1','15.44','VAT','3.12','Dummy','4','XXX','Product2','UOM'
'Batch1','Invoice1','Post1','123.56','VAT','4.56','Dummy',5','XXX','Product3','UOM'
'Batch1','Invoice2','Post1','20.00','VAT','1.75','Dummy','1','XXX','Product1','UOM'
'Batch1','Invoice2','Post1','14.33','VAT','1.23','Dummy','2','XXX','Product1','UOM'
'Batch1','Invoice2','Post1','12.44','VAT','2.45','Dummy','3','XXX','Product2','UOM'
'Batch1','Invoice2','Post1','15.44','VAT','3.12','Dummy','4','XXX','Product2','UOM'
'Batch1','Invoice2','Post1','123.56','VAT','4.56','Dummy',5','XXX','Product3','UOM'
'Batch1','Invoice3','Post1','20.00','VAT','1.75','Dummy','1','XXX','Product1','UOM'
'Batch1','Invoice3','Post1','14.33','VAT','1.23','Dummy','2','XXX','Product1','UOM'
'Batch1','Invoice3','Post1','12.44','VAT','2.45','Dummy','3','XXX','Product2','UOM'
'Batch1','Invoice3','Post1','15.44','VAT','3.12','Dummy','4','XXX','Product2','UOM'
'Batch1','Invoice3','Post1','123.56','VAT','4.56','Dummy',5','XXX','Product3','UOM'
January 12, 2010 at 8:02 am
Don't worry, you're doing great.
Is this the code that could work on the sample data?
DECLARE @Input TABLE (
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10),
Field4 decimal(18,5),
Field5 varchar(10),
Field6 decimal(18,5),
Field7 varchar(10),
Field8 int,
Field9 varchar(10),
Field10varchar(10),
Field11 varchar(10)
)
INSERT INTO @Input SELECT
'Batch1','Invoice1','Post1',20.00,'VAT',1.75,'Dummy',1,'XXX','Product1','UOM' UNION ALL SELECT
'Batch1','Invoice1','Post1',14.33,'VAT',1.23,'Dummy',2,'XXX','Product1','UOM' UNION ALL SELECT
'Batch1','Invoice1','Post1',12.44,'VAT',2.45,'Dummy',3,'XXX','Product2','UOM' UNION ALL SELECT
'Batch1','Invoice1','Post1',15.44,'VAT',3.12,'Dummy',4,'XXX','Product2','UOM' UNION ALL SELECT
'Batch1','Invoice1','Post1',123.56,'VAT',4.56,'Dummy',5,'XXX','Product3','UOM' UNION ALL SELECT
'Batch1','Invoice2','Post1',20.00,'VAT',1.75,'Dummy',1,'XXX','Product1','UOM' UNION ALL SELECT
'Batch1','Invoice2','Post1',14.33,'VAT',1.23,'Dummy',2,'XXX','Product1','UOM' UNION ALL SELECT
'Batch1','Invoice2','Post1',12.44,'VAT',2.45,'Dummy',3,'XXX','Product2','UOM' UNION ALL SELECT
'Batch1','Invoice2','Post1',15.44,'VAT',3.12,'Dummy',4,'XXX','Product2','UOM' UNION ALL SELECT
'Batch1','Invoice2','Post1',123.56,'VAT',4.56,'Dummy',5,'XXX','Product3','UOM' UNION ALL SELECT
'Batch1','Invoice2','Post1',20.00,'VAT',1.75,'Dummy',1,'XXX','Product1','UOM' UNION ALL SELECT
'Batch1','Invoice2','Post1',14.33,'VAT',1.23,'Dummy',2,'XXX','Product1','UOM' UNION ALL SELECT
'Batch1','Invoice3','Post1',12.44,'VAT',2.45,'Dummy',3,'XXX','Product2','UOM' UNION ALL SELECT
'Batch1','Invoice3','Post1',15.44,'VAT',3.12,'Dummy',4,'XXX','Product2','UOM' UNION ALL SELECT
'Batch1','Invoice3','Post1',123.56,'VAT',4.56,'Dummy',5,'XXX','Product3','UOM'
SELECT CASE WHEN Field9 IN ( 'XXX', 'YYY', 'ZZZ' )
THEN 'D' + '9999' + 'JM'
+ CASE Field1
WHEN 'TEST' THEN CONVERT(CHAR(6), GETDATE(), 12)
ELSE Field1
END
+ '00' + CONVERT(CHAR(6), GETDATE(), 112) + -- GROUP BY 1
LEFT(Field2 + SPACE(18), 18) + -- GROUP BY 2
Field3 + RIGHT('00000000000000'
+ CONVERT(VARCHAR, ABS(Field4), 0), 14) + -- SUM
Field5 + ---GROUP BY 4
RIGHT('00000000000000' + CONVERT(VARCHAR, ABS(Field6), 0),14) + -- SUM
Field7 + SPACE(8) + 'AAA' + '0000999999' + -- GROUP BY 5
SPACE(32) + LEFT(ISNULL(Field8, '008888'), 6) + -- GROUP BY 6
SPACE(86) + Field9 + SPACE(7) + Field10 + -- GROUP BY 3
Field11 + SPACE(208)
ELSE 'D' + '9999' + 'JM'
+ CASE Field1
WHEN 'TEST' THEN CONVERT(CHAR(6), GETDATE(), 12)
ELSE Field1
END + '00' + CONVERT(CHAR(6), GETDATE(), 112)
+ LEFT(Field2 + SPACE(18), 18) + Field3
+ RIGHT('00000000000000' + CONVERT(VARCHAR, ABS(Field4), 0), 14)
+ Field5
+ RIGHT('00000000000000' + CONVERT(VARCHAR, ABS(Field6), 0), 14)
+ Field7
+ SPACE(8) + 'AAA'
+ '0000999999' + SPACE(32) + LEFT(ISNULL(Field8, '008888'), 6)
+ SPACE(86) + Field9
+ SPACE(7) + Field10 + Field11 + SPACE(208)
END
FROM @Input
-- Gianluca Sartori
January 12, 2010 at 8:05 am
Yep that works ok. I realise that there is only a small part of the SP there but if I put it all on it would be I think too big.
January 13, 2010 at 1:47 am
Please close off this thread I have sorted myself by using a temp table
January 13, 2010 at 2:31 am
Thanks for the notice, I was planning to respond later today, you saved me some extra work.
Can you post your solution (or part of it) here so that others having the same problem can take advantage from your experience?
Thanks
Gianluca
-- Gianluca Sartori
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply