Select query works fine, but data showing haphazardly how to use order by? please help me Date wise not show
My query is
alter proc [dbo].[P_GetLgrRptByPartyID]
--@acid int,
@LegID int,
@Sdate date,
@EDate Date
As
BEGIN
--create table ##temp6 (id [int] IDENTITY(1,1) NOT NULL ,Date Date,
--PURTICULARS varchar (50), Vou_no varchar (50), Vou_Type varchar (50),
--vOUCHERVREF_NO varchar (50),
--DR_Amount decimal(18, 2),
--CR_Amount decimal(18, 2),
--nrr nvarchar(255))
--insert into ##temp6(Date, PURTICULARS, Vou_no, Vou_Type, vOUCHERVREF_NO, DR_Amount, CR_Amount, nrr)
select convert(varchar(15),Invdt,105) AS Date,'SALE A/c' AS PURTICULARS,invid AS Vou_no,case TM_type when 'S' then 'SALE' else 'SALE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, invamt as DR_Amount,'0.00'AS CR_Amount ,nrr from Tbl_Trade_Msater
where partyID=@LegID AND TM_type='S' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
union all
select convert(varchar(15),Invdt,105) AS Date,'PURCHASE A/c' AS pURTICULARS,invid AS Vou_no,case TM_type when 'P' then 'PURCHASE' else 'PURCHASE' end AS Vou_Type, R_invno AS vOUCHERVREF_NO ,'0.00'AS DR_Amount ,invamt as CR_Amount,nrr
from Tbl_Trade_Msater where partyID=@LegID AND TM_type='P' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,'S.RETURN A/c' AS PURTICULARS,invid AS Vou_no,case TM_type when 'SR' then 'SALE-RETURN' else 'SALE-RETURN' end AS Vou_Type,'N/A' AS vOUCHERVREF_NO,'0.00'AS DR_Amount , invamt as CR_Amount,nrr
from Tbl_Trade_Msater where partyID=@LegID AND TM_type='SR' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
union all
select convert(varchar(15),Invdt,105) AS Date,'P.RETURN A/c' AS pURTICULARS,invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,'N/A' AS vOUCHERVREF_NO,invamt as DR_Amount,'0.00'AS CR_Amount ,nrr
from Tbl_Trade_Msater where partyID=@LegID AND TM_type='PR' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL2.ladgerName, VID AS Vou_no,case VType when 'REC' then 'RECEIPT' else 'RECEIPT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00' AS DR_Amount ,Amount as CR_Amount,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL2 ON av.AcID =PL2.partyID
where LegID=@LegID and VType='REC' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL3.ladgerName, VID AS Vou_no,case VType when 'REC' then 'RECEIPT' else 'RECEIPT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO, Amount AS DR_Amount, '0.00'as CR_Amount,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL3 ON av.LegID =PL3.partyID
where AcID=@LegID and VType='REC' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL4.ladgerName, VID AS Vou_no,case VType when 'PAY' then 'PAYMENT' else 'PAYMENT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,Amount as DR_Amount,'0.00'AS CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL4 ON av.AcID =PL4.partyID
where LegID=@LegID and VType='PAY' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL5.ladgerName, VID AS Vou_no,case VType when 'PAY' then 'PAYMENT' else 'PAYMENT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00' as DR_Amount,Amount AS CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL5 ON av.LegID =PL5.partyID
where AcID=@LegID and VType='PAY' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL6.ladgerName, VID AS Vou_no,case VType when 'CON' then 'CONTRA' else 'CONTRA' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,Amount as DR_Amount,'0.00'AS CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL6 ON av.LegID =PL6.partyID
where AcID=@LegID and VType='CON' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL7.ladgerName, VID AS Vou_no,case VType when 'CON' then 'CONTRA' else 'CONTRA' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00'AS DR_Amount,Amount as CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL7 ON av.AcID =PL7.partyID
where LegID=@LegID and VType='CON' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL8.ladgerName, VID AS Vou_no,case VType when 'JUN' then 'JOURNAL' else 'JOURNAL' end
AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00'AS DR_Amount,Amount as CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL8 ON av.AcID =PL8.partyID
where LegID=@LegID and VType='JUN' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),VDate,105) AS Date,PL9.ladgerName, VID AS Vou_no,case VType when 'JUN' then 'JOURNAL' else 'JOURNAL' end
AS Vou_Type,VRefNo AS vOUCHERVREF_NO,Amount as DR_Amount, '0.00'AS CR_Amount,AV.nrr from Tbl_Ac_Voucher AS AV
INNER JOIN tbl_party_ldg AS PL9 ON av.LegID =PL9.partyID
where AcID=@LegID and VType='JUN' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL10.ladgerName, invid AS Vou_no,case TM_type when 'S' then 'SALE' else 'SALE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,'0.00' as DR_Amount, totaldis AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL10 ON av.dis_ldr_id =PL10.partyID
where dis_ldr_id=@LegID AND TM_type='S' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL11.ladgerName, invid AS Vou_no,case TM_type when 'P' then 'PURCHASE' else 'PURCHASE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, totaldis as DR_Amount,'0.00' AS CR_Amount,AV.nrr from Tbl_Trade_Msater
AS AV
INNER JOIN tbl_party_ldg AS PL11 ON av.dis_ldr_id =PL11.partyID
where dis_ldr_id=@LegID AND TM_type='P' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL12.ladgerName, invid AS Vou_no,case TM_type when 'SR' then 'SALE-RETURN' else
'SALE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, totaldis as DR_Amount, '0.00' AS CR_Amount,AV.nrr from
Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL12 ON av.dis_ldr_id =PL12.partyID
where dis_ldr_id=@LegID AND TM_type='SR' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL13.ladgerName, invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, '0.00' as DR_Amount,totaldis AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL13 ON av.dis_ldr_id =PL13.partyID
where dis_ldr_id=@LegID AND TM_type='PR' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate
--Sale
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no,case TM_type when 'S' then 'SALE' else 'SALE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS
AV
INNER JOIN tbl_party_ldg AS PL14 ON av.RoundUpLdrID =PL14.partyID
where RoundUpLdrID=@LegID AND TM_type='S' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtD!=0.00 --or RuAmtC!=0.00
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no, case TM_type when 'S' then 'SALE' else 'SALE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, '0.00' as DR_Amount, RuAmtC AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS
AV
INNER JOIN tbl_party_ldg AS PL14 ON av.RoundUpLdrID =PL14.partyID
where RoundUpLdrID=@LegID AND TM_type='S' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtC!=0.00
--Purchase
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no,case TM_type when 'P' then 'PURCHASE' else 'PURCHASE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL14 ON av.RoundUpLdrID =PL14.partyID
where RoundUpLdrID=@LegID AND TM_type='P' And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtD!=0.00
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no,case TM_type when 'P' then 'PURCHASE' else 'PURCHASE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, '0.00' as DR_Amount, av.RuAmtC
AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL14 ON av.RoundUpLdrID =PL14.partyID
where RoundUpLdrID=@LegID AND TM_type='P' And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtC!=0.00
--
--- Sale Return
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL15.ladgerName, invid AS Vou_no ,case TM_type when 'SR' then
'SALE-RETURN' else 'SALE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,
AV.nrr from Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL15 ON av.RoundUpLdrID =PL15.partyID
where RoundUpLdrID=@LegID AND TM_type='SR' And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtD!=0.00
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL15.ladgerName, invid AS Vou_no,case TM_type when 'SR'
then 'SALE-RETURN' else 'SALE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,'0.00' as DR_Amount,
RuAmtC AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL15 ON av.RoundUpLdrID =PL15.partyID
where RoundUpLdrID=@LegID AND TM_type='SR' And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtC!=0.00
-- Purchase Return
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL16.ladgerName, invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,AV.nrr from
Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL16 ON av.RoundUpLdrID =PL16.partyID
where RoundUpLdrID=@LegID AND TM_type='PR' And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtD!=0.00
UNION ALL
select convert(varchar(15),Invdt,105) AS Date,PL16.ladgerName, invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,'0.00' as DR_Amount, av.RuAmtC AS CR_Amount,AV.nrr from
Tbl_Trade_Msater AS AV
INNER JOIN tbl_party_ldg AS PL16 ON av.RoundUpLdrID =PL16.partyID
where RoundUpLdrID=@LegID AND TM_type='PR' And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtC!=0.00
--select PURTICULARS, Vou_no, Vou_Type, vOUCHERVREF_NO, DR_Amount, CR_Amount, nrr from ##temp6 order by ##temp6.PURTICULARS asc
END
-- P_GetLgrRptByPartyID '48','2000-04-30','2025-04-26'
--drop table ##temp6
What I have tried:
--select PURTICULARS, Vou_no, Vou_Type, vOUCHERVREF_NO, DR_Amount, CR_Amount, nrr from ##temp6 order by ##temp6.PURTICULARS asc
--drop table ##temp6
I directly try
<pre lang="VB">
order by date ASC but not work