procedure help - 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)

procedure help

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-15 : 11:55:03

I have 2 tables.
Pubs
Purpose: The publications have columns for id and indx. These columns are used to retrieve the pubs for a given campaign (ie. COPD).

Campaign
Purpose: This table defines a campaign.
A campaign can be defined by 1 or more rows.
A the id and indx for a campaign are associated with the campaign in this table.
By referencing the id and indx in Campaign against the values in Pubs get the publications for a given campaign.

Example: The following query easily retrieves all of the publications for the COPD campaign.

SELECT * FROM Pubs
WHERE (id IN
(SELECT id FROM Campaign
WHERE (campaign = 'copd')))

AND (indx IN (SELECT indx FROM Campaign AS Campaign_1
WHERE (campaign = 'copd')))

I have to take this SELECT statement (optimize it if necessary) and create a function or sproc whereby if I pass in COPD as the variable, then it returns back the publications in the COPD campaign?

can u tell me how can i get this?

thanks.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-15 : 12:07:11

Using In (Select ... ) is not advisable. Instead, use Exists. In your case, you should be using a Join. Try the following.


Create Procedure dbo.GetPubs(@Campaign varchar(50))
As Begin
	Select Distinct P.*
	From Pubs P
	Inner Join Campaign C On C.id = P.id And C.indx = P.indx
	Where C.campaign = @Campaign
End

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-15 : 13:04:29

thanks Qualis. yep it works perfectly.

and ok will remeber to use exists instead of IN(Select..

thanks..

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 09:15:43

i have to generate a record set of sales by date ?
For example, i have to pass in the following variables:

Campaign = “COPD”
BeginDate = 9/1/2007
EndDate = 2/14/2008

Then i have a table returned that has the distribution for each of the publications in the COPD campaign between those 2 dates. what should be done to make that happen?

Create Procedure dbo.GetPubs
(@Campaign varchar(50), @startdate datetime, @enddate datetime)
As Begin
Select Distinct P.*
From Pubs P
Inner Join Campaign C On C.id = P.id And C.indx = P.indx
Where C.campaign = @Campaign
End

but i have to return table so have to create function or procedure..don't know??

any idea what ihave to do?

thanks for ur help..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 09:17:27

Can you post your table structure & expected o/p?

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 10:11:10

i have been given 2tables:
sop2 it has typeno,typenum,typedate,typeamt..lots more columns
sop3 it has typeno,typenum,itemnum,itemdesc...lots more columns

i have procesure of getpubs which i had already created to get pubs from

i have one view - which joine these 2tables and created - vwpubs
from which i got typeno,typenum,typedate,typeamt

now i have to use this typedate = startdate and enddate..means in between those dates i want records of pubs..initially i want records monthwise but in final output i want records between those 2dates:
BeginDate = 9/1/2007
EndDate = 2/14/2008
and begindate and endate will be use from typedate column.. i have itemnum column from sop3 table and campaignnum column from pubs table which r same values used in getpubs procedure..
so i can join that column while joining this procedure and that view..

so i want records of publications pass in the following variables:
Campaign= “COPD”
BeginDate = 9/1/2007
EndDate = 2/14/2008

and returned table that has the distribution for each of the publications in the COPD campaign between those 2 dates.

can anyone have idea?

thanks.

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 10:19:00

i think i have to create temptable or view first to get itemnum and itempdate from that sop2 and sop3tables..

then this view or temptable i can use to create new procedure to get results andd i have to join that getpubs procedure and this view...on itemnum column..

or i have to create 2views to get results..

do anyone have any idea?

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 10:21:41

[code]CREATE TABLE #temp
(fields
)
INSERT INTO #Temp
EXEC getpubs(params)

SELECT *
FROM vwpubs v
INNER JOIN sop3 s
ON s.typeno=v.typeno
INNER JOIN #temp t
ON t.campaignnum=joiningfield
WHERE Campaign= 'COPD'
AND BeginDate >= '9/1/2007'
AND EndDate <= '2/14/2008'
[/code]
the above code will give you stub to work on.You have not explained about data returned by SP and also which field to use to join on result. Also i didnt get what you meant by distribution of each publications. DId you mean count?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 10:27:00

Also express dates in universal format YYYYMMDD to avoid conflict with local date settings

Madhivanan

Failing to plan is Planning to fail

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 10:57:16

well, Thanks Visakh16 for ur help and reply..but i don't understand which u have created temptable..

well, instead of temptable i have created view vwpubs:

create view vwpubs
as
select s2.typeno,s2.typenum,s2.typedate,s2.typeamt,s3.itemnmbr
from sop2 s2
right outer join (select typeno,typenum,tiemnmbr,quantity from sop3 ) s3

on S2.typenum= S3.typenum and S2.typeno = S3.typeno

GO

then created procedure :

Create Procedure GetPubssale
(@Campaign varchar(50), @startdate datetime, @enddate datetime )
As
Begin
Select Distinct P.*
From Pubs P
Inner Join Campaign C
On C.id = P.id And C.indx = P.indx

inner join vwpubs vs

on vs.itemnmbr = p.campaignnum

Where C.Campaign = @Campaign and (vs.typedate between @startdate and @enddate)

End

when i m executing procedure:

exec GetPubssale copd,'9/1/2007','2/14/2008' - i m getting only 5rows instead of 599..

don't know where i m wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 11:05:34

Ok . i was thinking you need SP also to get result. Did understood it from your earlier explanation.
Re. your results, why do you think you should get 599 rows? If its number of rows present in Pubs,are you sure you have a record existing in vwpubs & campaign table for each record in pubs? if not change join to left outer and also where condition like

Where (C.Campaign = @Campaign or C.Campaign is null)
and ((vs.typedate between @startdate and @enddate) or vs.typedate is null)

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 11:22:46

well, thanks visakh16 for ur help.

but when i m
Create Procedure GetPubssale
(@Campaign varchar(50), @startdate datetime, @enddate datetime )
As
Begin
Select Distinct P.*From Pubs P
Inner Join Campaign C
On C.id = P.id And C.indx = P.indx

inner join vwpubs vs

on vs.itemnmbr = p.campaignnum

Where C.Campaign = @Campaign and (vs.typedate between @startdate and @enddate)

End

when i m executing procedure:

exec GetPubssale copd,'9/1/2007','2/14/2008' - i m getting only 5rows

and when i m using
Select Distinct vs.typedate,vs.itemnmbr,P.*From Pubs P
Inner Join Campaign C
On C.id = P.id And C.indx = P.indx

inner join vwpubs vs

on vs.itemnmbr = p.campaignnum

Where C.Campaign = @Campaign and (vs.typedate between @startdate and @enddate)

then i m getting 484rows...which is correct but i want output only - pubs rows not view rows-typedate and itemnmbr..these 2rows i have to use to join only...do u have any idea?

thanks a lot!!!

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 11:31:01

do i have to count rows first then use this count function-condition in select statement? but where i have to use this count? do u have any idea?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 12:21:05

Thats because you have only 5 distinct pubs table combination values in those 484 rows i guess. just take distinct out and see the records returned. t should be more. distinct will show the same set of values only once. thats why you get only 5 rows against 484 (if you check them you can see the vs.typedate,vs.itemnmbr combination being different for them.)

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 12:27:54

yes, i got it...thanks..

but i want sum(quantity) for each given date...so i add first quantity column in view

create view vwpubs
as
select s2.typeno,s2.typenum,s2.typedate,s2.typeamt,s3.itemnmbr,s3.quantity
from sop2 s2
right outer join (select typeno,typenum,tiemnmbr,quantity from sop3 ) s3

on S2.typenum= S3.typenum and S2.typeno = S3.typeno

GO

now i want sum(quantity) in output for each date..

means for date '9/1/2007' - sum(quantity) - 25
date '9/2/2007' - sum(quantity) - 15

how can i get that???

Create Procedure GetPubssale
(@Campaign varchar(50), @startdate datetime, @enddate datetime )
As
Begin
Select Distinct sum(vs.quantity),P.*
From Pubs P
Inner Join Campaign C
On C.id = P.id And C.indx = P.indx

inner join vwpubs vs

on vs.itemnmbr = p.campaignnum

Where C.Campaign = @Campaign and (vs.typedate between @startdate and @enddate)

End

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-19 : 13:14:02

create view vwpubs
as
select distinct s2.typeno,s2.typenum,s2.typedate,s2.typeamt,s3.itemnmbr,sum(s3.quantity)
from sop2 s2
right outer join (select distinct typeno,typenum,tiemnmbr,quantity from sop3 ) s3

on S2.typenum= S3.typenum and S2.typeno = S3.typeno

group by s2.typedate,s2.typeno,s2.typenum,s2.typeamt,s3.itemnmbr

GO

but i m getting 4rows of different totalquantity of same date..

i want for date - '10/1/2007' - totalquantity - 100.000
'10/2/2007' - totalquantity - 101.000

but i m getting - '10/1/2007' - totalquantity - 1.000
'10/1/2007' - totalquantity - 10.000
'10/1/2007' - totalquantity - 100.000
'10/1/2007' - totalquantity - 101.000
'10/2/2007' - totalquantity - 100.000
'10/2/2007' - totalquantity - 150.000

can anybody help to figure it out?

thanks

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-20 : 09:07:58

create view vwpubs
as
select distinct s2.typeno,s2.typenum,s2.typedate,s2.typeamt,s3.itemnmbr,sum(s3.quantity)
from sop2 s2
right outer join (select distinct typeno,typenum,itemnmbr,quantity from sop3 ) s3

on S2.typenum= S3.typenum and S2.typeno = S3.typeno

group by s2.typedate,s2.typeno,s2.typenum,s2.typeamt,s3.itemnmbr

GO

in this view i have typeno differents but typedate is same and itemnmbr is same but want total quantity..

for date '10/1/2007' and itemnmbr-5 - type no. - 2,4,7,8
and quantity - 15.99,35.99,48.99,59.99

instead of getting 4rows i want one row as output

so i want for same date '10/1/2007' and same itemnmbr-5 totalquanitty-sum(quantity) - typeno.-2,4,7,8

can anybody tell me how to arrange this in query?

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 11:11:06

quote:
Originally posted by ri16

create view vwpubs
as
select distinct s2.typeno,s2.typenum,s2.typedate,s2.typeamt,s3.itemnmbr,sum(s3.quantity)
from sop2 s2
right outer join (select distinct typeno,typenum,itemnmbr,quantity from sop3 ) s3

on S2.typenum= S3.typenum and S2.typeno = S3.typeno

group by s2.typedate,s2.typeno,s2.typenum,s2.typeamt,s3.itemnmbr

GO

in this view i have typeno differents but typedate is same and itemnmbr is same but want total quantity..

for date '10/1/2007' and itemnmbr-5 - type no. - 2,4,7,8
and quantity - 15.99,35.99,48.99,59.99

instead of getting 4rows i want one row as output

so i want for same date '10/1/2007' and same itemnmbr-5 totalquanitty-sum(quantity) - typeno.-2,4,7,8

can anybody tell me how to arrange this in query?

thanks.

you need to remove typeno from grouping i guess

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources