Help for the specific query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help for the specific query

Author

Topic

Raoulh79
Starting Member

24 Posts

Posted - 2009-09-09 : 05:57:02

Hi all,

I have the following query and the following result set

QUERY
-------
select I.n0_department_no, I.n0_tax_code, sum(I.n0_quantity), sum(I.n2_ext_price), (sum(I.n2_ext_price)+(coalesce(D.n2_allowance,0)+coalesce(D.N2_PERC_OFF_AMOUNT,0))) AS CTOTAL
from TABLE1 I
inner join TABLE2 R
ON R.n0_xact_no = I.n0_xact_no and
R.bl_voided <> 1 and
R.bl_suspended <> 1 and
I.dt_time_stamp between '2009-05-01 00:00:01' and '2009-09-03 23:59:59' and
I.bl_voided <> 1 and
I.n0_unique_str_no= 219 left JOIN TABLE3 D
ON D.n0_xact_no= I.n0_xact_no
group by I.n0_department_no, I.n0_tax_code, D.N2_PERC_OFF_AMOUNT, D.n2_allowance

RESULTSET
---------

Dept.
10020 2 3 169500 169500
10030 2 67 5083000 5083000
10030 2 1 76500 61200
10030 2 1 76500 75500
10030 2 1 76500 76480
12050 2 9 44100 44100
60110 2 1 45000 45000

What i want to do is to group the department (column 1 in the resultset) in one row. As you see the 10030 appears 4 times, i want it to appear once with the right amount offcurse...

Example of resultset that i need:

10020 2 3 169500 169500
10030 2 70 5312500 5296180
12050 2 9 44100 44100
60110 2 1 45000 45000

Any help is appreciated...

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 06:42:05

try


select I.n0_department_no, I.n0_tax_code, sum(I.n0_quantity), sum(I.n2_ext_price), (sum(I.n2_ext_price)+(coalesce(D.n2_allowance,0)+coalesce(D.N2_PERC_OFF_AMOUNT,0))) AS CTOTAL
from TABLE1 I
inner join TABLE2 R
ON R.n0_xact_no = I.n0_xact_no and
R.bl_voided <> 1 and
R.bl_suspended <> 1 and
I.dt_time_stamp between '2009-05-01 00:00:01' and '2009-09-03 23:59:59' and
I.bl_voided <> 1 and
I.n0_unique_str_no= 219 left JOIN TABLE3 D
ON D.n0_xact_no= I.n0_xact_no
group by I.n0_department_no, I.n0_tax_code, D.N2_PERC_OFF_AMOUNT, D.n2_allowance

KH
[spoiler]Time is always against us[/spoiler]

Raoulh79
Starting Member

24 Posts

Posted - 2009-09-09 : 06:47:54

I tried what you are proposing, but i get the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'D.N2_ALLOWANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'D.N2_ALLOWANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'D.N2_PERC_OFF_AMOUNT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'D.N2_PERC_OFF_AMOUNT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any other clue?????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 06:58:40

sorry, didn't notice that part is not in the aggregate function
try


select	n0_department_no, n0_tax_code, sum(quantity), sum(ext_price), sum(CTOTAL)
FROM
(
	select 	I.n0_department_no, 
		I.n0_tax_code, 
		sum(I.n0_quantity) as quantity, 
		sum(I.n2_ext_price) as ext_price, 
		(sum(I.n2_ext_price)+(coalesce(D.n2_allowance,0)+coalesce(D.N2_PERC_OFF_AMOUNT,0))) AS CTOTAL
	from TABLE1 I
	inner join TABLE2 R
	ON R.n0_xact_no = I.n0_xact_no and
	R.bl_voided <> 1 and
	R.bl_suspended <> 1 and
	I.dt_time_stamp between '2009-05-01 00:00:01' and '2009-09-03 23:59:59' and
	I.bl_voided <> 1 and
	I.n0_unique_str_no= 219 left JOIN TABLE3 D
	ON D.n0_xact_no= I.n0_xact_no
	group by I.n0_department_no, I.n0_tax_code, D.N2_PERC_OFF_AMOUNT, D.n2_allowance
) a
group by n0_department_no, n0_tax_code

KH
[spoiler]Time is always against us[/spoiler]

Raoulh79
Starting Member

24 Posts

Posted - 2009-09-09 : 07:36:51

Thanks KH, it is exactly what i wanted...!!!

Raoulh79
Starting Member

24 Posts

Posted - 2009-09-09 : 10:10:25

I would like to ask 2 questions regarding this query that you proposed KH:

1. I have some fields which the user should give some value before the query give the resultset : I.dt_time_stamp, I.n0_unique_str_no. How should i change the query in order that when i run it, it asks for the values in the specific fields to the operator?

Specifically look in the places where i inserted a "?"

select n0_department_no, n0_tax_code, sum(quantity), sum(ext_price), sum(CTOTAL)
FROM
(
select I.n0_department_no,
I.n0_tax_code,
sum(I.n0_quantity) as quantity,
sum(I.n2_ext_price) as ext_price,
(sum(I.n2_ext_price)+(coalesce(D.n2_allowance,0)+coalesce(D.N2_PERC_OFF_AMOUNT,0))) AS CTOTAL
from TABLE1 I
inner join TABLE2 R
ON R.n0_xact_no = I.n0_xact_no and
R.bl_voided <> 1 and
R.bl_suspended <> 1 and
I.dt_time_stamp between '?' and '?' and
I.bl_voided <> 1 and
I.n0_unique_str_no= ? left JOIN TABLE3 D
ON D.n0_xact_no= I.n0_xact_no
group by I.n0_department_no, I.n0_tax_code, D.N2_PERC_OFF_AMOUNT, D.n2_allowance
) a
group by n0_department_no, n0_tax_code

2. Also i was asked to tranfer the result of this query to a table in Access. The resultset field matched with the table layout in access.
Any idea on how i can do it??? Is there a specific command? and from where i should run it from the access or sql or should I create a program in vb for example to do that???

Your help is really appreciated guys...

Thanks in advance...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources