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.
Author |
Topic |
navya krishna
Starting Member
39 Posts |
Posted - 2013-01-20 : 00:00:29
|
Dear all, i am new for ssrs.i need some clarifications on ssrs reports.we have one client he is maintaining databases every year sepeatly in single server.so for example he maintained 2008,09,10 ,11,12 lyk that so i need sales report customer wise in single report .i need to display customer name,one column 2008 database,2009,10,11,12 to every column in single report.how can i display .could you please any one explain the process clearly.navya krishna katta |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-04 : 02:55:42
|
Hello Visakh, could you please tell me how to display top 10 customer detailes in ssrs.i am trying to do i am not getting solution for that please solve my problem.navya krishna katta |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 03:03:08
|
apply rank over field based on which you need the top 10 and then apply a filter like Rank <=10------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-04 : 03:32:50
|
Hello Visakh, i am not getting idea where can i give rank to field.could you explain clearly.navya krishna katta |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 03:37:13
|
show me how your dataset is currently in report------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-04 : 04:04:24
|
Hello Visakh, see I have taken in dataset customer name and customer amount so i need to dispaly top 10 customer detailes in report.where can i give that rank to field.navya krishna katta |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 04:15:51
|
assuming based on customer amount you want to rank. useSELECT *FROM(SELECT dense_Rank() over (order by [customer amount] as rnk,*FROM table)tWHERE rnk<=10 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-04 : 05:16:30
|
SELECT *FROM(SELECT dense_Rank() over (order by [Amount] as rnk,*FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer])tWHERE rnk<=10i am getting error "Incorrect syntax near the keyword 'as'".....navya krishna katta |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 05:40:21
|
quote: Originally posted by navya krishna SELECT *FROM(SELECT dense_Rank() over (order by [Amount]) as rnk,*FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer])tWHERE rnk<=10i am getting error "Incorrect syntax near the keyword 'as'".....navya krishna katta
missed a )------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-04 : 06:40:54
|
Hello visakh, could you please provide your email id .i need to contact with you regular touch.navya krishna katta |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 06:44:57
|
quote: Originally posted by navya krishna Hello visakh, could you please provide your email id .i need to contact with you regular touch.navya krishna katta
please post your questions here. I will try to answer when I can.There are lots of experts here and it will ensure any one of them would be able to see and give solutions to questions.I may not be available always for checking and answering questions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-13 : 02:20:07
|
Hello Visakh,I got one error "Expression type decimal is invalid for COLLATE clause " please solve that issue.it's urgent to me.USE [CCIL12]GO/****** Object: StoredProcedure [dbo].[Netrevenue] Script Date: 02/13/2013 11:49:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Netrevenue2](@fromdate date, @todate date)ASBEGINDECLARE @YEAR INTDECLARE @MONTH INTDECLARE @DAY INTDECLARE @DATE DATE/*SET @DAY = 01SET @MONTH = MONTH(@todate)set @YEAR = YEAR(@fromdate) SET @DATE = dbo.fnDateTime2FromParts(@Year , @Month ,@DAY,00,00,00,00)*/ SELECT CCIL11.dbo.[CCIL-2011$G_L Account].No_ COLLATE DATABASE_DEFAULT as [N0_], CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate)))COLLATE DATABASE_DEFAULT AS TEST01FROM CCIL11.dbo.[CCIL-2011$G_L Account] WHERE CCIL11.dbo.[CCIL-2011$G_L Account].No_ IN('41120','41130','41140','41150') union all SELECT CCIL12.dbo.[CCIL-2012$G_L Account].No_ COLLATE DATABASE_DEFAULT as [No_], CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) COLLATE DATABASE_DEFAULT AS TEST01 FROM CCIL12.dbo.[CCIL-2012$G_L Account]WHERE CCIL12.dbo.[CCIL-2012$G_L Account].No_ IN('41120','41130','41140','41150')ENDnavya krishna katta |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 04:49:48
|
you need to add COLLATE only for varchar fields. dont add it for decimal or other numeric fields------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-18 : 00:50:03
|
Hello visakh,below report just displaying 2011,12,13 db's result in future also we need to use this report only.for example we need to display in future 2014,2015 report .how can we write dynamic query for this below query.USE [CCIL12]GO/****** Object: StoredProcedure [dbo].[Netrevenue4] Script Date: 02/18/2013 11:16:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[Netrevenue4](@fromdate date, @todate date)ASBEGINDECLARE @YEAR INTDECLARE @MONTH INTDECLARE @DAY INTDECLARE @DATE DATE /*frome date for actual month like 01-10-2011 */DECLARE @pr_month_f DATE /*prior month from_date in this year only*/DECLARE @pr_month_t DATE /*prior month to_date in this year only*/DECLARE @pr_month_f1 DATE /*actual month prior year*/DECLARE @pr_month_t1 DATE /*actual month prior year*/DECLARE @pr_year_f DATE /*prior year from_date*/DECLARE @pr_year_t DATE /*prior year to_date*/DECLARE @pr_month2_f DATE /*prior month of prior month from_date in this year only*/DECLARE @pr_month2_t DATE /*prior month of prior month to_date in this year only*/DECLARE @pr_year2_f DATE /*prior year prior month Eg: 01-09-2010*/DECLARE @pr_year2_t DATE /*prior year prior month Eg: 31-09-2010*/SET @DAY = 01 SET @MONTH = MONTH(@todate)set @YEAR = YEAR(@todate) SET @DATE = dbo.fnDateTime2FromParts(@Year , @Month ,@DAY,00,00,00,00)SET @pr_month_f = DATEADD(MM,-1,@DATE) SET @pr_month_t = DATEADD(MM,-1,@todate)SET @pr_month2_f = DATEADD(MM,-1,@pr_month_f)SET @pr_month2_t = DATEADD(MM,-1,@pr_month_t)SET @pr_year2_f = DATEADD(yyyy,-1,@pr_month_f)SET @pr_year2_t = DATEADD(yyyy,-1,@pr_month_t)set @DAY = DAY(@todate)SET @pr_month_f1 = dbo.fnDateTime2FromParts(@Year-1 , @Month ,01,00,00,00,00)SET @pr_month_t1 = dbo.fnDateTime2FromParts(@Year-1 , @Month ,@DAY,00,00,00,00)SET @pr_year_f = DATEADD(YYYY,-1,@fromdate)SET @pr_year_t = DATEADD(YYYY,-1,@todate) SELECT CCIL11.dbo.[CCIL-2011$G_L Account].No_ COLLATE DATABASE_DEFAULT as [N0_], /*YTD actual from_date to to_date */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01, /* YTD actual from_date to to_date budget */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Amount]) as Test1 FROM CCIL11.dbo.[CCIL-2011$G_L Budget Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Date] BETWEEN @fromdate AND @todate)))AS TEST02, /*month actual Eg: 01-10-2011 to 31-10-2011*/ CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test2 FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @DATE AND @todate))) AS TEST03,/*month actual Eg: 01-10-2011 to 31-10-2011 budget */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Amount]) as Test3 FROM CCIL11.dbo.[CCIL-2011$G_L Budget Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Date] BETWEEN @DATE AND @todate)))AS TEST04,/*prior month in this year only */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test4 FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST05,/*budget*/ CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Amount]) as Test6 FROM CCIL11.dbo.[CCIL-2011$G_L Budget Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Date] BETWEEN @pr_month_f AND @pr_month_t)))AS TEST06, /*prior month of prior month in this year only*//* CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test41 FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_month2_f AND @pr_month2_t))) AS TEST051,*//*prior month from_date last year only Eg: last year september*/ CONVERT(Decimal(38,2),(SELECT SUM(CCIL10.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test7 FROM CCIL10.dbo.[CCIL-2013$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL10.dbo.[CCIL-2013$G_L Entry].[G_L Account No_]) AND (CCIL10.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_year2_f AND @pr_year2_t)))AS TEST052, /*Prior year Eg: 01-01-2010 to 31-10-2010 */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL10.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test7 FROM CCIL10.dbo.[CCIL-2013$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL10.dbo.[CCIL-2013$G_L Entry].[G_L Account No_]) AND (CCIL10.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t))) AS TEST07, /*prior year same month Eg01-10-2010 to 31-10-2010 */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL10.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test8 FROM CCIL10.dbo.[CCIL-2013$G_L Entry] WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL10.dbo.[CCIL-2013$G_L Entry].[G_L Account No_]) AND (CCIL10.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1))) AS TEST08 FROM CCIL11.dbo.[CCIL-2011$G_L Account] WHERE CCIL11.dbo.[CCIL-2011$G_L Account].No_ IN('41120','41130','41140','41150') UNION ALL /*union with another db 2013*/SELECT CCIL12.dbo.[CCIL-2012$G_L Account].No_ COLLATE DATABASE_DEFAULT as [No_], /*YTD actual from_date to to_date */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01, /* YTD actual from_date to to_date budget */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Amount]) as Test1 FROM CCIL12.dbo.[CCIL-2012$G_L Budget Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Date] BETWEEN @fromdate AND @todate))) AS TEST02, /*month actual Eg: 01-10-2011 to 31-10-2011*/ CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test2 FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @DATE AND @todate))) AS TEST03, /*month actual Eg: 01-10-2011 to 31-10-2011 budget */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Amount]) as Test3 FROM CCIL12.dbo.[CCIL-2012$G_L Budget Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Date] BETWEEN @DATE AND @todate))) AS TEST04, /*prior month in this year only */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test4 FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST05, /*budget*/ CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Amount]) as Test5 FROM CCIL12.dbo.[CCIL-2012$G_L Budget Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST06, /*prior month of prior month same year*//* CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test4 FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_month2_f AND @pr_month2_t))) AS TEST051, */ /*prior month from_date last year only Eg: last year september*/ CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test6 FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_year2_f AND @pr_year2_t)))AS TEST052, /*Prior year Eg: 01-01-2010 to 31-10-2010 */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test6 FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t))) AS TEST07, /*prior year same month Eg01-10-2010 to 31-10-2010 */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test7 FROM CCIL11.dbo.[CCIL-2011$G_L Entry] WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_]) AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1))) AS TEST08 FROM CCIL12.dbo.[CCIL-2012$G_L Account] WHERE CCIL12.dbo.[CCIL-2012$G_L Account].No_ IN('41120','41130','41140','41150') UNION ALL /*union with another db 2013*/ SELECT CCIL13.dbo.[CCIL-2013$G_L Account].No_ COLLATE DATABASE_DEFAULT as [No_], /*YTD actual from_date to to_date */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test FROM CCIL13.dbo.[CCIL-2013$G_L Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_]) AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01, /* YTD actual from_date to to_date budget */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Amount]) as Test1 FROM CCIL13.dbo.[CCIL-2013$G_L Budget Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[G_L Account No_]) AND (CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Date] BETWEEN @fromdate AND @todate))) AS TEST02, /*month actual Eg: 01-10-2011 to 31-10-2011*/ CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test2 FROM CCIL13.dbo.[CCIL-2013$G_L Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_]) AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @DATE AND @todate))) AS TEST03, /*month actual Eg: 01-10-2011 to 31-10-2011 budget */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Amount]) as Test3 FROM CCIL13.dbo.[CCIL-2013$G_L Budget Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[G_L Account No_]) AND (CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Date] BETWEEN @DATE AND @todate))) AS TEST04, /*prior month in this year only */ CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test4 FROM CCIL13.dbo.[CCIL-2013$G_L Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_]) AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST05, /* CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test4 FROM CCIL13.dbo.[CCIL-2013$G_L Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_]) AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_month2_f AND @pr_month2_t))) AS TEST051,*/ /*prior month from_date last year only Eg: last year september*/ CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test6 FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_year2_f AND @pr_year2_t)))AS TEST052, /*budget*/ CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Amount]) as Test5 FROM CCIL13.dbo.[CCIL-2013$G_L Budget Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[G_L Account No_]) AND (CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST06, /*Prior year Eg: 01-01-2010 to 31-10-2010 */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test6 FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t))) AS TEST07, /*prior year same month Eg01-10-2010 to 31-10-2010 */ CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test7 FROM CCIL12.dbo.[CCIL-2012$G_L Entry] WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_]) AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1))) AS TEST08 FROM CCIL13.dbo.[CCIL-2013$G_L Account] WHERE CCIL13.dbo.[CCIL-2013$G_L Account].No_ IN('41120','41130','41140','41150') ENDnavya krishna katta |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 00:55:27
|
it seems you're using separate tables for holding each years data. in that case you can make it dynamic in a straightforward way.One solution is to create a view over these group of tables and use view instead in the above procedureThe view still has to be modified to add latest set of tables in each yearI would have scrapped this design and would have come up with single table with a year or date column to indicate period of transaction and kept the entire data in same table itself to avoid these complications.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-18 : 01:07:41
|
Visakh, could you please provide a query how to write. am not getting idea how to write it.pls modify that query into dynamic query or you are telling way.navya krishna katta |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-21 : 04:18:55
|
Hello Visakh,I am getting Below error how can i solve that problem."Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"USE [CCIL-2012]GO/****** Object: StoredProcedure [dbo].[ITem] Script Date: 02/21/2013 12:47:35 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOalter PROCEDURE [dbo].[ITem1](@fromdate date, @todate date)ASBEGINSELECT [CCIL-2012].dbo.[CCIL-2012$Item].No_ ,(SELECT [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code] as PrdgrpFROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]where [CCIL-2012].dbo.[CCIL-2012$Item].No_ = [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_)as Ctgry,CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Sales Amount(LCY)]) as Test1FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_)and ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01,CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Sales Amount(LCY)]) as Test2FROM [CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line]WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_)and ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST02FROM [CCIL-2012].dbo.[CCIL-2012$Item]endnavya krishna katta |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 04:42:28
|
The reason is your first subquery might return more than one row... Are you having same value for Ctgry? i.e.If [Product Group Code] for [CCIL-2012$Item].No_ is same, then you can use as below: SELECT MAX([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code]) as PrdgrpFROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]where [CCIL-2012].dbo.[CCIL-2012$Item].No_ = [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_)as Ctgry,--Chandu |
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-02-21 : 05:13:39
|
Hi Chandu,I am getting same error Please modify that above report .navya krishna katta |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 05:34:47
|
Try this....SELECT [CCIL-2012].dbo.[CCIL-2012$Item].No_ , (SELECT MAX([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code]) as Prdgrp FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line] where [CCIL-2012].dbo.[CCIL-2012$Item].No_ = [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_ )as Ctgry, CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Sales Amount(LCY)]) as Test1 FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line] WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_) and ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Posting Date] BETWEEN @fromdate AND @todate) ) ) AS TEST01, CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Sales Amount(LCY)]) as Test2 FROM [CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line] WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_) and ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Posting Date] BETWEEN @fromdate AND @todate) ) ) AS TEST02FROM [CCIL-2012].dbo.[CCIL-2012$Item] --Chandu |
|
|
Next Page
|
|
|
|
|