Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
169 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
169 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  
 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.05 seconds. Powered By: Snitz Forums 2000