sqlcurious
asked on
develop reports
how to design reports like in the attachment. my stored proc is not giving as i expected.
how my procedure can be modified.help needed my proc is below.
CREATE PROCEDURE [dbo].[DBSP_Report_GetProj ectDetailC ost1]
(
--@projectID int =null,
@ProjectNumber VARCHAR(50)=Null,
@ProjectControlNumber VARCHAR(50)= Null,
@ProjectLettingDateFrom DATETIME,
@ProjectLettingDateTo DATETIME,
@ProjectName varchar(50) = Null,
@DivisionName VARCHAR(Max) = Null
)
AS
begin
DECLARE @SQL VARCHAR(8000)
set @SQL=
'SELECT
P.ProjectID,
P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
convert(varchar(10),p.Proj ectStartDa te,110)as ProjectStartDate,
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPric e As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItem_ItemTotalPri ce * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
QI.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot eSurcharge 2+Q.QuoteS urcharge3) AS TotalFreight
FROM TBL_Project P
inner join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner join TBL_Division D ON D.CreatedBy = Q.QuoteCreatedBy
inner join TBL_County C ON P.ProjectCountyID = C.CountyID
where P.projectID>=1'
if(@projectnumber is not null and @projectnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectnumber = '''+ convert(varchar(5),@projec tnumber ) +''''
End
if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectcontrolnumber = '''+ convert(varchar(5),@Projec tcontrolnu mber) + ''''
End
if @projectname <> ''
begin
set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%'''
end
If @ProjectLettingDateFrom Is Not Null
Begin
Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLetting DateFrom As Varchar(30))+''''
End
If @ProjectLettingDateTo Is Not Null
Begin
Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLetting DateTo As Varchar(30))+''''
End
if @DivisionName <> ''
begin
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
end
print @sql
exec (@sql)
END
rpteBidProjectDetailCosting--1-.pdf
how my procedure can be modified.help needed my proc is below.
CREATE PROCEDURE [dbo].[DBSP_Report_GetProj
(
--@projectID int =null,
@ProjectNumber VARCHAR(50)=Null,
@ProjectControlNumber VARCHAR(50)= Null,
@ProjectLettingDateFrom DATETIME,
@ProjectLettingDateTo DATETIME,
@ProjectName varchar(50) = Null,
@DivisionName VARCHAR(Max) = Null
)
AS
begin
DECLARE @SQL VARCHAR(8000)
set @SQL=
'SELECT
P.ProjectID,
P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
convert(varchar(10),p.Proj
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPric
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItem_ItemTotalPri
QI.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot
FROM TBL_Project P
inner join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner join TBL_Division D ON D.CreatedBy = Q.QuoteCreatedBy
inner join TBL_County C ON P.ProjectCountyID = C.CountyID
where P.projectID>=1'
if(@projectnumber is not null and @projectnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectnumber = '''+ convert(varchar(5),@projec
End
if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectcontrolnumber = '''+ convert(varchar(5),@Projec
End
if @projectname <> ''
begin
set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%'''
end
If @ProjectLettingDateFrom Is Not Null
Begin
Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLetting
End
If @ProjectLettingDateTo Is Not Null
Begin
Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLetting
End
if @DivisionName <> ''
begin
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
end
print @sql
exec (@sql)
END
rpteBidProjectDetailCosting--1-.pdf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
solution was partially complete
ASKER