set QUOTED_IDENTIFIER ON
go
--select * from tblSize
--sp_help tblPhotosettingsMaster
--sp_help tblPhotosettings_transaction
--sp_help tblSize
--[uspTransactionPhotoSettingsDetail] 'raju','4/1/2011','4/27/2011',0,0,0
--[uspTransactionPhotoSettingsDetail] 'ram','01-Apr-2011','03-Apr-2011',0,0,0
--select getdate()
ALTER Procedure [dbo].[uspTransactionManualPhotoSettingsDetail]
(
@Date DateTime,
@DeliveryDate DateTime,
@DateAll As int,
@DelivDateAll As int
)
As
Begin
Declare @Table Table
(
CustomerName Varchar(200),
Phoneno varchar(50),
Size varchar(50),
Noofcopies Numeric,
Rate Numeric,
TotalAmount Numeric,
Date DateTime,
DeliverDate DateTime,
PaidAmount Numeric,
UnPaidAmount Numeric,
ID Numeric,
Int1 Numeric(18,2),
Int2 Numeric(18,2),
Int3 Numeric(18,2),
Int4 Numeric(18,2),
Str1 Varchar(100),
Str2 Varchar(100),
Str3 Varchar(100),
Str4 Varchar(100),
Str5 Varchar(100)
)
Insert into @Table
Select
[Name] As CustomerName,
Phoneno,
Size,
Noofcopies,
Rate,
Total As TotalAmount,
Startdate As [Date],
Enddate As DeliverDate,
PaidAmount,
0,
tblManualPhotosettingsMaster.Id,
0 As Int1,---CreditAmount
0 As Int2,
0 As Int3,
0 As Int4,
'' As Str1,
'' As Str2,
'' As Str3,
'' As Str4,
'' As Str5
From
tblManualPhotosettingsMaster
Join tblManualPhotosettings_transaction on
tblManualPhotosettings_transaction.Id=tblManualPhotosettingsMaster.Id
Join tblSize on
tblManualPhotosettings_transaction.SizeID=tblSize.SizeId
Where
-- ((Convert(DateTime,Convert(VarChar(11),Startdate,106),106)>=@Date and Convert(DateTime,Convert(VarChar(11),Startdate,106),106)<=@Date)) And
-- ((Convert(DateTime,Convert(VarChar(11),Enddate,106),106)>=@DeliveryDate and
--(Convert(DateTime,Convert(VarChar(11),Enddate,106),106)<=@DeliveryDate)) And
-- ([Name]=@CustomerName))
Convert(DateTime,Convert(VarChar(11),Startdate,106),106)=Convert(DateTime,Convert(VarChar(11),@Date,106),106)
And Convert(DateTime,Convert(VarChar(11),Enddate,106),106)= Convert(DateTime,Convert(VarChar(11),@DeliveryDate,106),106)
Update
@Table
Set
UnPaidAmount=isnull(A.Rate,0) -isnull(PaidAmount,0)
From
(
Select
Sum(Isnull(Total,0)) As Rate,
ID As Sid
From
tblManualPhotosettings_transaction
Group By ID
) A
Where ID=Sid
select * from @Table
End
The error
An error has occurred during report processing.
Invalid cast from 'Boolean' to 'DateTime'.