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
 Gathering authors in a stored procedure

Author  Topic 

Lebowski
Starting Member

17 Posts

Posted - 2014-03-18 : 23:40:18
My stored procedure is now close to finished. The only thing left is that it prints out the "Authors" on several lines. So if a ID has several authors, it will print several lines instead of one. Can i resolve this in a Group By statement, or something else? And where should i put it?

How it is shortly showed(not with all the other stuff):

18.01.2014, Author 1, ItemID1
18.01.2014, Author 3, ItemID1
How it should be:

18.01.2014, Author 1, Author 3, ItemID1

ALTER PROCEDURE [dbo].[LOID] @month         INT, 
@year INT,
@report_source NVARCHAR(255),
@is_primary INT
AS
SELECT Cast(isa.sales_date AS DATE) AS
DATO,
BV.name AS
BU,
isa.identifiers AS
IS,
BB.name AS
Fo,
bc.name AS
AUTHOR,
bk.title AS
Tizx,
isa.quantity,
Isnull(id.sales_price, Isnull(u.sales_price, isa.sales_price)) AS
SALES_PRICE
FROM book_sales AS isa
INNER JOIN store AS BV
ON bv.store_id = isa.store_id
LEFT OUTER JOIN discount AS id
ON id.identifiers = isa.identifiers
AND id.store = BV.name
AND id.from_date <= isa.sales_date
AND id.to_date >= isa.sales_date
AND id.to_date < '2999-01-01'
LEFT OUTER JOIN discount AS u
ON u.identifiers = isa.identifiers
AND u.to_date = '2999-01-01'
AND BV.name = u.store
LEFT OUTER JOIN book_contributor AS BC
ON BC.book_id = isa.book_id
LEFT OUTER JOIN books AS BK
ON BK.book_id = isa.book_id
LEFT OUTER JOIN publisher AS BB
ON bb.publisher_id = bk.publisher_id
WHERE Month(isa.sales_date) = @month
AND Year(isa.sales_date) = @year
AND isa.report_source = @report_source
AND bc.is_primary = @is_primary


anyone?

http://mnmt.no

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-19 : 00:11:18
18.01.2014, Author 1, ItemID1
18.01.2014, Author 3, ItemID1
How it should be:

18.01.2014, Author 1, Author 3, ItemID1




declare @tab table(Date1 VARCHAR(20),Des1 varchar(10),Des2 varchar(10))
insert @tab
select '18.01.2014','Author 1','ItemID1'
union all select '18.01.2014','Author 3','ItemID1'

select ta.Date1,ta.Des1 as col1 ,t.Des1 as col2,t.Des2 as col3 from @tab as ta
inner join @tab as t
on ta.Date1 = t.Date1
where t.des1 = 'Author 3' and ta.Des1 = 'Author 1'


Veera
Go to Top of Page

Lebowski
Starting Member

17 Posts

Posted - 2014-03-19 : 00:13:06
Im sorry that i am a little new to this, but is this a modification of the stored procedure? The point is that i have to use the SP, to get monthly sales data. So i run it and choose month, year. etc... The problem is that it returns Authors in many fields.

quote:
Originally posted by VeeranjaneyuluAnnapureddy

18.01.2014, Author 1, ItemID1
18.01.2014, Author 3, ItemID1
How it should be:

18.01.2014, Author 1, Author 3, ItemID1




declare @tab table(Date1 VARCHAR(20),Des1 varchar(10),Des2 varchar(10))
insert @tab
select '18.01.2014','Author 1','ItemID1'
union all select '18.01.2014','Author 3','ItemID1'

select ta.Date1,ta.Des1 as col1 ,t.Des1 as col2,t.Des2 as col3 from @tab as ta
inner join @tab as t
on ta.Date1 = t.Date1
where t.des1 = 'Author 3' and ta.Des1 = 'Author 1'


Veera



http://mnmt.no
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-19 : 00:41:24
can u plz provide the sample data of this tables

book_sales,store,discount,book_contributor,books,publisher

Veera
Go to Top of Page
   

- Advertisement -