query inside procedure - 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 2005 Forums

Transact-SQL (2005)

query inside procedure

Author

Topic

kiri
Starting Member

38 Posts

Posted - 2008-02-20 : 12:43:54

create procedure salep
as

Select Distinct sum(vs.totsales) as Totalsales, vs.docdate,vs.itemnmbr,p.section_id
From psection P

inner join ccode C

on C.indx = P.indx and p.section_id=c.section_id

inner join saleview vs

on vs.item = p.item

group by vs.docdate,vs.itemnmbr,p.section_id

order by vs.item,vs.docdate

end

i m getting output:

Totalsales docdate item section_id
---------------------------------------------------------
100.000 1/25/2008 R89 7
100.00 1/25/2008 R89 105
100.00 1/25/2008 R89 44

i want output like:

Totalsales docdate item
-----------------------------------------
100.000 1/25/2008 R89

i m not selecting p.section_id and i m using distinct and not joining on section_id then also i m geting 3rows instead of 1

Totalsales docdate item
--------------------------------------------
100.000 1/25/2008 R89
100.00 1/25/2008 R89
100.00 1/25/2008 R89

can anybody help me to get it?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 12:48:19

remove the section_id from group by and try

kiri
Starting Member

38 Posts

Posted - 2008-02-20 : 12:56:09

i did like this:

create procedure salep
as

Select Distinct sum(vs.totsales) as Totalsales, vs.docdate,vs.itemnmbr
From psection P

inner join ccode C

on C.indx = P.indx

inner join saleview vs

on vs.item = p.item

group by vs.docdate,vs.itemnmbr

order by vs.item,vs.docdate

end

still giving me same 3rows as resultset..instead of one..don't know why?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-21 : 01:14:10

Try

Select sum(totsales) as totsales ,docdate,itemnmbr from
(
Select Distinct vs.totsales as Totalsales, vs.docdate,vs.itemnmbr
From psection P

inner join ccode C

on C.indx = P.indx

inner join saleview vs

on vs.item = p.item
) as t
group by docdate,itemnmbr
order by item,docdate

Madhivanan

Failing to plan is Planning to fail

kiri
Starting Member

38 Posts

Posted - 2008-02-21 : 08:53:12

it gives me error like:

The multi-part identifier "vs.itemnmbr" could not be bound.
The multi-part identifier "vs.docdate" could not be bound.

kiri
Starting Member

38 Posts

Posted - 2008-02-21 : 08:56:03

Select sum(t.totsales) as totsales ,t.docdate,t.itemnmbr from
(
Select Distinct vs.totsales as Totalsales, vs.docdate,vs.itemnmbr
From psection P

inner join ccode C

on C.indx = P.indx

inner join saleview vs

on vs.item = p.item
) as t
group by t.docdate,t.itemnmbr
order by t.itemnmbr,t.docdate

its not giving me error
but still not giving correct results

kiri
Starting Member

38 Posts

Posted - 2008-02-21 : 09:02:36

thanks a lot!!!madhivanan,

but i m getting output:

Totalsales docdate item section_id
---------------------------------------------------------
100.000 1/25/2008 R89 7
100.00 1/25/2008 R89 105
100.00 1/25/2008 R89 44

i want output like:

Totalsales docdate item
-----------------------------------------
100.000 1/25/2008 R89

i think to use condition like -

when item is starting with any character - a,b,c...then result will be one row...totalsales ..docdate..item
but don't know how to use this condition?

can anybody help me to figure this out?

thanks a lot!!

kiri
Starting Member

38 Posts

Posted - 2008-02-21 : 09:51:22

finally, got the correct results..one row in output

Select t.totsales as totsales ,t.docdate,t.itemnmbr from
(
Select Distinct vs.totsales as Totalsales, vs.docdate,vs.itemnmbr
From psection P

inner join ccode C

on C.indx = P.indx

inner join saleview vs

on vs.item = p.item
) as t

order by t.itemnmbr,t.docdate

thanks madhivanan for ur help.

thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-22 : 01:11:41

quote:
Originally posted by kiri

finally, got the correct results..one row in output

Select t.totsales as totsales ,t.docdate,t.itemnmbr from
(
Select Distinct vs.totsales as Totalsales, vs.docdate,vs.itemnmbr
From psection P

inner join ccode C

on C.indx = P.indx

inner join saleview vs

on vs.item = p.item
) as t

order by t.itemnmbr,t.docdate

thanks madhivanan for ur help.

thank you

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources