Problem To produce Report - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem To produce Report

Author

Topic

dearabi
Starting Member

2 Posts

Posted - 2011-01-09 : 05:12:43

Hi

I want to Produce some reports in my Project that They Use 3 Tables in the name of samples,Tcash,TVarizi that I Attach their Codes.

I Use StoreProcedure for produce them.
In Both when I execute Them If I have a Data In samples I will Get Result But If I Have not Data I Get Nothing.

It Is Obvious.

My Request IS if There are not any Data result show Nulls for me. let me say an example:

I want to Get Report between '89/01' and '89/04' i want to get result for 4 months .if I have data show else write Null.
As bellow

for ex:

CompanyName Date value
CN1 89/01 V1
CN2 89/02 ---(Or null)
CN3 89/03 ---(Or null)
CN4 89/04 V2

and If I Determine a Company Name ,Only Get The Result belonging to That Company_Name

Here is First SP:
{----Start
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[AmalKArdRep]

@DateStart nvarchar(50),
@EndDate nvarchar(50),
@CompName nvarchar(50)

AS
BEGIN
declare @Query nvarchar(4000)

set @Query ='

SELECT Samples.companyName AS SC,Samples.Month AS SM, Samples.[Year] AS SY, Samples.TotallValuePay AS STVP, TVarizi.BillNum AS TB, TVarizi.Money AS TM, TCash.BillNum AS CB,
TCash.Money AS CM,Samples.Date
FROM Samples Left JOIN
TVarizi ON Samples.Date = TVarizi.SabtDate Left JOIN
TCash ON Samples.Date = TCash.SabtDate
Where 1=1 '

if isnull(@CompName,'')<>''
set @Query = @Query + ' and
(
((Samples.companyName =N'''+@CompName+'''))or
(
(TCash.CompanyName =N'''+@CompName+''')
or (Tvarizi.CompanyName =N'''+@CompName+''')
)
)'

if isnull(@DateStart,'')<>'' and isnull(@EndDate,'')<>''
set @Query = @Query + ' and (Samples.Date between '''+@DateStart+''' and '''+@EndDate+''')'

if isnull(@DateStart,'')<>''
set @Query = @Query + ' and (Samples.Date >='''+@DateStart+''')'

if isnull(@EndDate,'')<>''
set @Query = @Query + ' and (Samples.Date <='''+@EndDate+''')'

set @Query = @Query + 'order by SC, SY, SM'

print @Query
exec (@Query)

END

----End}

it is Code Of My tables:

[url]http://www.4shared.com/file/QrZdzOR_/CodeOfTables.html[/url]

and the only thing That Is Common between Them is Company_Name

Please Help Me It is Urgent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 12:57:16

sorry your question is not clear. Post some sample data and then explain what you want out of it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources