SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Gathering authors in a stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lebowski
Starting Member

17 Posts

Posted - 03/18/2014 :  23:40:18  Show Profile  Reply with Quote
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

Edited by - Lebowski on 03/19/2014 00:06:01

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
164 Posts

Posted - 03/19/2014 :  00:11:18  Show Profile  Reply with Quote
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 - 03/19/2014 :  00:13:06  Show Profile  Reply with Quote
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

India
164 Posts

Posted - 03/19/2014 :  00:41:24  Show Profile  Reply with Quote
can u plz provide the sample data of this tables

book_sales,store,discount,book_contributor,books,publisher

Veera
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000