Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 grouping problem

Author  Topic 

sent_sara
Constraint Violating Yak Guru

377 Posts
Posted - 2009-02-12 : 05:25:47
Hi friends i need the output as shown below can i anyone help in query

Table structure:
Invoice_No Invoice_Date Invoice_Amount Receipt_doc_No Receipt_amount Adjustment_doc_No Adjustment_Amount
ADCH/000001/08-09 4/30/2008 253089.2 NULL 0 CHNCN/000101/08-09 86702.49
ADCH/000001/08-09 4/30/2008 253089.2 ARBR/000139/08-09 166386.71 NULL 0
INCH/000010/08-09 4/16/2008 276225.83 NULL 0 CHNCN/000027/08-09 10877
INCH/000010/08-09 4/16/2008 276225.83 ARBR/000014/08-09 197840 NULL 0
INCH/000010/08-09 4/16/2008 276225.83 ARBR/000034/08-09 37123 NULL 0

output should be:
Invoice_No Invoice_Da Invoice_Amount Receipt_doc_No Receipt_amount Adjustment_doc_No Adjustment_Amount
ADCH/000001/08-09 4/30/2008 253,089.20 ARBR/000139/08-09 166,386.71 CHNCN/000101/08-09 86,702.49
INCH/000010/08-09 4/16/2008 276,225.83 ARBR/000014/08-09 197,840.00 CHNCN/000027/08-09 10,877.00
INCH/000010/08-09 4/16/2008 276,225.83 ARBR/000034/08-09 37,123.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-12 : 05:41:03
Post data again, and use the [ code ] and [ /code ] tags (without spaces).


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts
Posted - 2009-02-12 : 06:18:00
[code]

Receipt_doc_No Receipt_amount Adjustment_doc_No Adjustment_Amount
NULL 0 CHNCN/000101/08-09 86702.49
ARBR/000139/08-09 166386.71 NULL 0
NULL 0 CHNCN/000027/08-09 10877
ARBR/000014/08-09 197840 NULL 0
ARBR/000034/08-09 37123 NULL 0

[/code]
output should be:

[code]
Receipt_doc_No Receipt_amount Adjustment_doc_No Adjustment_Amount
ARBR/000139/08-09 166,386.71 CHNCN/000101/08-09 86,702.49
ARBR/000014/08-09 197,840.00 CHNCN/000027/08-09 10,877.00
ARBR/000034/08-09 37,123.00

[/code]

quote:
Originally posted by Peso

Post data again, and use the [ code ] and [ /code ] tags (without spaces).


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-12 : 06:22:52
Great!
Can you also adjust column names to column values so that we can see which value belong to which column?

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts
Posted - 2009-02-12 : 06:51:49
[code]
Receipt_doc_No Receipt_amount Adjustment_doc_No Adjustment_Amount
NULL 0 CHNCN/000101/08-09 86702.49
ARBR/000139/08-09 166386.71 NULL 0
NULL 0 CHNCN/000027/08-09 10877
ARBR/000014/08-09 197840 NULL 0
ARBR/000034/08-09 37123 NULL 0
[/code]
output should be:

[code]
Receipt_doc_No Receipt_amount Adjustment_doc_No Adjustment_Amount
ARBR/000139/08-09 166,386.71 CHNCN/000101/08-09 86,702.49
ARBR/000014/08-09 197,840.00 CHNCN/000027/08-09 10,877.00
ARBR/000034/08-09 37,123.00

[/code]


quote:
Originally posted by Peso

Great!
Can you also adjust column names to column values so that we can see which value belong to which column?

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-02-12 : 06:53:02
Also, What links your first column and second? What business logic did you apply when you combined 1st with 2nd row,3rd with 4th ??
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts
Posted - 2009-02-12 : 07:04:10
Business Logic is just showing adjustment document pertaining to/Against Receipt Document in Line-Wise




quote:
Originally posted by sakets_2000

Also, What links your first column and second? What business logic did you apply when you combined 1st with 2nd row,3rd with 4th ??
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts
Posted - 2009-02-12 : 07:29:22
quote:
Originally posted by sent_sara

Business Logic is just showing adjustment document pertaining to/Against Receipt Document in Line-Wise




quote:
Originally posted by sakets_2000

Also, What links your first column and second? What business logic did you apply when you combined 1st with 2nd row,3rd with 4th ??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-12 : 07:37:44
In a relational databas as SQL Server, there is no "linewise" order. The records may not be physically bo stored in the order they are displayed.
The ONLY way to fix this, is to have some kind of sequential expression, a timeline.
Do you have a "DateAdded" column? Or an IDENTITY column?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-02-12 : 07:41:19
This is more a design issue. I suggest you correct your design, have an identity column in there.
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts
Posted - 2009-02-12 : 07:47:30
receipt_sno identity column is there

quote:
Originally posted by Peso

In a relational databas as SQL Server, there is no "linewise" order. The records may not be physically bo stored in the order they are displayed.
The ONLY way to fix this, is to have some kind of sequential expression, a timeline.
Do you have a "DateAdded" column? Or an IDENTITY column?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-12 : 07:51:47
Can you add that to the sample data above?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts
Posted - 2009-02-12 : 08:07:43
Correct Data is 

Receipt_sno	Receipt_doc_No     Receipt_amount	Adjustment_doc_No	Adjustment_Amount
1		ARBR/000139/08-09  0	        	CHNCN/000101/08-09	86702.49
1		ARBR/000139/08-09  166386.71		NULL			0
2		ARBR/000014/08-09  0			CHNCN/000027/08-09	10877
2		ARBR/000014/08-09  197840		NULL			0
3		ARBR/000034/08-09  37123		NULL			0

output should be:


receipt_no  Receipt_doc_No	   Receipt_amount Adjustment_doc_No	 Adjustment_Amount
1	    ARBR/000139/08-09  	   166,386.71 	  CHNCN/000101/08-09	 86,702.49 
2           ARBR/000014/08-09      197,840.00 	  CHNCN/000027/08-09	 10,877.00 
3	    ARBR/000034/08-09      37,123.00




quote:
Originally posted by Peso

Can you add that to the sample data above?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-12 : 08:23:48
See how easy things are when we get the full picture!?
DECLARE	@Sample TABLE
	(
		Receipt_sno INT,
		Receipt_doc_No VARCHAR(20),
		Receipt_amount MONEY,
		Adjustment_doc_No VARCHAR(20),
		Adjustment_Amount MONEY
	)

INSERT	@Sample
SELECT	1, 'ARBR/000139/08-09',      0	 , 'CHNCN/000101/08-09', 86702.49 UNION ALL
SELECT	1, 'ARBR/000139/08-09', 166386.71, NULL                ,     0    UNION ALL
SELECT	2, 'ARBR/000014/08-09',      0	 , 'CHNCN/000027/08-09', 10877    UNION ALL
SELECT	2, 'ARBR/000014/08-09', 197840	 , NULL		       ,     0    UNION ALL
SELECT	3, 'ARBR/000034/08-09',  37123	 , NULL		       ,     0

SELECT		Receipt_sno,
		Receipt_doc_No,
		MAX(Receipt_amount) AS Receipt_amount,
		MAX(Adjustment_doc_No) AS Adjustment_doc_No,
		NULLIF(MAX(Adjustment_Amount), 0) AS Adjustment_Amount
FROM		@Sample
GROUP BY	Receipt_sno,
		Receipt_doc_No
ORDER BY	Receipt_sno,
		Receipt_doc_No


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-02-12 : 08:24:16
[code]select receipt_no=Receipt_sno ,Receipt_doc_No ,Receipt_amount=sum(Receipt_amount) ,max(Adjustment_doc_No) , Adjustment_Amount=sum(Adjustment_Amount)
from
<yourtable>
group by
Receipt_sno ,Receipt_doc_No [/code]

This should be ok if you have 0 or a single Adjustment_doc_No against a Receipt_doc_No. How do you want the output if incase there are 2 or more Adjustment_doc_No ??
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site