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, ItemID118.01.2014, Author 3, ItemID1How it should be:18.01.2014, Author 1, Author 3, ItemID1ALTER 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, ItemID118.01.2014, Author 3, ItemID1How it should be:18.01.2014, Author 1, Author 3, ItemID1declare @tab table(Date1 VARCHAR(20),Des1 varchar(10),Des2 varchar(10))insert @tabselect '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 tainner join @tab as ton ta.Date1 = t.Date1where t.des1 = 'Author 3' and ta.Des1 = 'Author 1'Veera |
|
|
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, ItemID118.01.2014, Author 3, ItemID1How it should be:18.01.2014, Author 1, Author 3, ItemID1declare @tab table(Date1 VARCHAR(20),Des1 varchar(10),Des2 varchar(10))insert @tabselect '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 tainner join @tab as ton ta.Date1 = t.Date1where t.des1 = 'Author 3' and ta.Des1 = 'Author 1'Veera
http://mnmt.no |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2014-03-19 : 00:41:24
|
can u plz provide the sample data of this tablesbook_sales,store,discount,book_contributor,books,publisherVeera |
|
|
|
|
|