|
--------------------------------------------------------------------------------
hi all i am using given below procedure
what i want (i am using sql server2000 with vb.net in window advanceserver2000)
1.i want records between two given date for particular account statement.
2.if record is present in both tables than all the colums of query has value.
3.if not any side than corresponding table columns fill with null and other with
value.
4.i want to group records based on challan no and acc_stmt for same date in credit_details.
5.i want to group records based on debit date and acc_stmt for same date in debit_details.
6.there is two date columns in query sales date and debit date
and i require this format of date dd/mm/yyyy
i am showing this result in crystal report.
this given procedure works well till 4 step.
so please help me solve this query.
CREATE PROCEDURE credit_debit_information @acc_stmt as varchar(300),@from as smalldatetime,@to as smalldatetime
as
begin
select convert(varchar,c.sales_date,3),c.particular,c.cha llan_no,sum(c.cartoon) as Cartoon,sum(c.total_amount) as Total_amount,convert(varchar,d.debit_date,3),d.par ticular,d.chq_no,d.amount from
credit_details c left outer join debit_details d
on(c.acc_stmt=d.acc_stmt and c.sales_date=d.debit_date)
where c.acc_stmt=@acc_stmt and
sales_date between @from and @to
group by c.sales_date,c.particular,c.challan_no,d.debit_dat e,d.particular,d.chq_no,d.amount
union
select convert(varchar,c.sales_date,3),c.particular,c.cha llan_no,sum(c.cartoon) as Cartoon,sum(c.total_amount) as Total_amount,convert(varchar,d.debit_date,3),d.par ticular,d.chq_no,d.amount from
credit_details c right outer join debit_details d
on(c.acc_stmt=d.acc_stmt and c.sales_date=d.debit_date)
where d.acc_stmt=@acc_stmt and
debit_date between @from and @to
group by c.sales_date,c.particular,c.challan_no,d.debit_dat e,d.particular,d.chq_no,d.amount
order by convert(varchar,debit_date,3) asc
end
GO
| 
