0
Reply

How to avoid dublicate values in SQL Server?

magesh manavalan

magesh manavalan

Jan 18 2012 12:19 AM
1.9k
i have one doubt how to avoid dublicate values in SQL Server my doubt is the amount value repeated all rows. and i mentions this my errors in Red Circule


SELECT VPRC_60.YYYYMM,
ISNULL(m_rptunit.attribute1,'') AS Attribute1,
ISNULL(m_rptunit.parentunitid,'') AS Rpt_Unit,

CONVERT(Numeric(10),0) AS V_1,
CONVERT(Numeric(10),0) AS V_2,
CONVERT(Numeric(10),0) AS V_3,
CONVERT(Numeric(10),0) AS V_4,
(select count(VPRC_60.receipt_rf) as Before from VPRC_60, VPRC_13 where VPRC_13.proj_adopt>= VPRC_60.dt_for and VPRC_60.receipt_rf <> '' and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_5,
(select SUM(VPRC_60.receipt_rf_amt) as Before from VPRC_60, VPRC_13 where VPRC_13.proj_adopt>= VPRC_60.dt_for and VPRC_60.receipt_rf_amt <> 0 and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_6,
(select count(VPRC_60.receipt_rf) as After1 from VPRC_60, VPRC_13 where VPRC_13.proj_adopt<= VPRC_60.dt_for and VPRC_60.receipt_rf <> '' and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_7,
(select SUM(VPRC_60.receipt_rf_amt) as After1 from VPRC_60, VPRC_13 where VPRC_13.proj_adopt<= VPRC_60.dt_for and VPRC_60.receipt_rf_amt <> 0 and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_8,
(select count(VPRC_60.receipt_dl) as Before from VPRC_60, VPRC_13 where VPRC_13.proj_adopt>= VPRC_60.dt_for and VPRC_60.receipt_dl <> '' and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_9,
(select SUM(VPRC_60.receipt_dl_amt) as Before from VPRC_60, VPRC_13 where VPRC_13.proj_adopt>= VPRC_60.dt_for and VPRC_60.receipt_dl_amt <> 0 and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_10,
(select count(VPRC_60.receipt_dl) as After1 from VPRC_60, VPRC_13 where VPRC_13.proj_adopt<= VPRC_60.dt_for and VPRC_60.receipt_dl <> '' and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_11,
(select SUM(VPRC_60.receipt_dl_amt) as After1 from VPRC_60, VPRC_13 where VPRC_13.proj_adopt<= VPRC_60.dt_for and VPRC_60.receipt_dl_amt <> 0 and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_12,
(select count(VPRC_60.receipt_ea) as Before from VPRC_60, VPRC_13 where VPRC_13.proj_adopt>= VPRC_60.dt_for and VPRC_60.receipt_ea <> '' and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_13,
(select SUM(VPRC_60.receipt_ea_amt) as Before from VPRC_60, VPRC_13 where VPRC_13.proj_adopt>= VPRC_60.dt_for and VPRC_60.receipt_ea_amt <> 0 and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_14,
(select count(VPRC_60.receipt_ea) as After1 from VPRC_60, VPRC_13 where VPRC_13.proj_adopt<= VPRC_60.dt_for and VPRC_60.receipt_ea <> '' and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_15,
(select SUM(VPRC_60.receipt_ea_amt) as After1 from VPRC_60, VPRC_13 where VPRC_13.proj_adopt<= VPRC_60.dt_for and VPRC_60.receipt_ea_amt <> 0 and VPRC_60.Rpt_unit = VPRC_13.Rpt_unit) AS V_16



FROM VPRC_60,VPRC_13, m_rptunit
WHERE
m_rptunit.RU_tree_seq LIKE '0000400006%'
ANDm_rptunit.rpt_unit_id = VPRC_60.Rpt_Unit

GROUP BY
VPRC_60.YYYYMM, m_rptunit.attribute1, m_rptunit.parentunitid