Problems with rollup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problems with rollup

Author

Topic

gretchog
Starting Member

2 Posts

Posted - 2008-11-14 : 05:07:22

Hi all,

I'm having a problem with a recently changed SQL statement which uses ROLLUP. Since the change, the statement is producing duplicate rollup rows. Can anyone suggest why these duplicate rows have started appearing since the code below was added? Thanks.

Before code:

SELECT LC.LocalCurrency,
LC.CurrencyDescription,
MIN(T.SupplierRefNo) AS SupplierRefNo,
T.Juliandate,
T.TCode,
B.Bcode,
MIN(REPLACE(T.TourName, CHAR(39), '')) AS TourName,
MIN(B.Lead) AS Lead,
MIN(Case B.Status WHEN '1' THEN 'Conf' WHEN '2' THEN 'Optn' ELSE 'Canx' END) AS BkgStatus,
MIN(B.Bdate) AS Bdate,
MIN(DATEADD(day, T.Juliandate-2, '01-Jan-1900')) AS Tdate,
SUM(B.Adults + B.Children) AS BPax,
SUM(B.Tot) AS BRev,
SUM(B.COM) AS BCom,
SUM(Isnull(BC.Projected_Amount, 0)) AS BCosts,
SUM(Isnull(B.TotalCosts, 0)) AS BCosts_Currency,
SUM(ISNULL(NewsPaperCodeAmount, 0)) As NPCom,
SUM(ISNULL(IP.InsPrice,0)) as IPrice,
SUM(ISNULL(IC.InsCost,0)) as ICost,
SUM(ISNULL(INo.InsPax,0)) AS IPax ,
SUM(Isnull(BC.HomeCost, 0)) as TourCosts_Currency ,
SUM(Isnull(BC.Projected_Amount, 0)) as TourCosts_Converted

FROM Tour_Det TD
INNER JOIN Bookings B ON TD.Bcode = B.Bcode
INNER JOIN ( Select bcode, Sum(HomeCost) as HomeCost,
Sum(Projected_Amount) as Projected_Amount,
Min(LocalCurrency) as LocalCurrency
from Bookingcosts with (nolock)
group by bcode ) BC ON BC.Bcode = B.Bcode
INNER JOIN ToursInd T ON T.Tcode = TD.Tour_Code
INNER JOIN LocalCurrency LC ON B.LocalCurrency = LC.LocalCurrency
LEFT JOIN (SELECT BC.BCode, SUM(BC.Projected_Amount) As NewsPaperCodeAmount
FROM bookingcosts BC WHERE BC.type = 'Newspapercode'
GROUP BY BC.Bcode) NPC ON NPC.Bcode = B.Bcode
LEFT JOIN (SELECT SUM(ES.Qty * ES.Cost1) As InsPrice, ES.Bcode FROM Extras E
INNER JOIN ExtrasSales ES ON E.NBRcode = ES.NBRcode WHERE E.Type = 'Insurance'
GROUP BY ES.Bcode) IP ON B.Bcode = IP.Bcode
LEFT JOIN (SELECT COUNT(*) AS InsPax, INSCount.Bcode
FROM ( SELECT DISTINCT LPE.Passenger, LPE.Bcode FROM LinkPaxToExtras LPE
INNER JOIN Passengers P ON P.[Unique ID] = LPE.Passenger
INNER JOIN Extras E1 ON E1.NBRcode = LPE.NBRCode WHERE P.PaxType <> '3' AND E1.Type = 'Insurance' ) INSCount
GROUP BY Bcode) INo ON B.Bcode = INo.Bcode
LEFT JOIN (SELECT BC.Bcode, SUM(BC.Projected_Amount) AS InsCost From BookingCosts BC
INNER JOIN Extras E ON BC.Code = E.NBRcode WHERE E.Type = 'Insurance'
GROUP BY BC.Bcode) IC ON IC.Bcode = B.Bcode

Where b.status in ('XX','1') and DATEADD(day, T.Juliandate - 2, '01-Jan-1900' )
Between '1-Oct-2008' and '1-Oct-2008'

GROUP BY LC.LocalCurrency, LC.CurrencyDescription, T.Juliandate, T.TCode, B.Bcode
WITH ROLLUP

After code:

SELECT LC.LocalCurrency, LC.CurrencyDescription,
MIN(T.SupplierRefNo) AS SupplierRefNo, T.Juliandate,
T.TCode, B.Bcode,
MIN(REPLACE(T.TourName, CHAR(39), '')) + ' ' + min(isnull(PricingTypeDescription, '')) AS TourName,
min(isnull(PricingTypeDescription, '')) as PricingTypeDescription,
MIN(B.Lead) AS Lead,
MIN(Case B.Status WHEN '1' THEN 'Conf' WHEN '2' THEN 'Optn' ELSE 'Canx' END) AS BkgStatus, MIN(B.Bdate) AS Bdate,
MIN(DATEADD(day, T.Juliandate-2, '01-Jan-1900')) AS Tdate,
SUM(B.Adults + B.Children) AS BPax,
SUM(B.Tot) AS BRev, SUM(B.COM) AS BCom,
SUM(Isnull(BC.Projected_Amount, 0)) AS BCosts,
SUM(Isnull(B.TotalCosts, 0)) AS BCosts_Currency,
SUM(ISNULL(NewsPaperCodeAmount, 0)) As NPCom,
SUM(ISNULL(IP.InsPrice,0)) as IPrice,
SUM(ISNULL(IC.InsCost,0)) as ICost,
SUM(ISNULL(INo.InsPax,0)) AS IPax ,
SUM(Isnull(BC.HomeCost, 0)) as TourCosts_Currency ,
SUM(Isnull(BC.Projected_Amount, 0)) as TourCosts_Converted,
TD.fk_TourMasterPricingTypes_ID

FROM Tour_Det TD
INNER JOIN Bookings B ON TD.Bcode = B.Bcode
INNER JOIN ( Select bcode, Sum(HomeCost) as HomeCost,
Sum(Projected_Amount) as Projected_Amount,
Min(LocalCurrency) as LocalCurrency
from Bookingcosts with (nolock)
group by bcode ) BC ON BC.Bcode = B.Bcode
INNER JOIN ToursInd T ON T.Tcode = TD.Tour_Code
INNER JOIN LocalCurrency LC ON B.LocalCurrency = LC.LocalCurrency
LEFT JOIN (SELECT BC.BCode, SUM(BC.Projected_Amount) As NewsPaperCodeAmount
FROM bookingcosts BC WHERE BC.type = 'Newspapercode'
GROUP BY BC.Bcode) NPC ON NPC.Bcode = B.Bcode
LEFT JOIN (SELECT SUM(ES.Qty * ES.Cost1) As InsPrice, ES.Bcode FROM Extras E
INNER JOIN ExtrasSales ES ON E.NBRcode = ES.NBRcode WHERE E.Type = 'Insurance'
GROUP BY ES.Bcode) IP ON B.Bcode = IP.Bcode
LEFT JOIN (SELECT COUNT(*) AS InsPax, INSCount.Bcode
FROM ( SELECT DISTINCT LPE.Passenger, LPE.Bcode FROM LinkPaxToExtras LPE
INNER JOIN Passengers P ON P.[Unique ID] = LPE.Passenger
INNER JOIN Extras E1 ON E1.NBRcode = LPE.NBRCode WHERE P.PaxType <> '3' AND E1.Type = 'Insurance' ) INSCount
GROUP BY Bcode) INo ON B.Bcode = INo.Bcode
LEFT JOIN (SELECT BC.Bcode, SUM(BC.Projected_Amount) AS InsCost From BookingCosts BC
INNER JOIN Extras E ON BC.Code = E.NBRcode WHERE E.Type = 'Insurance'
GROUP BY BC.Bcode) IC ON IC.Bcode = B.Bcode

LEFT JOIN ToursMaster_TourPricingTypes TMPT
on TMPT.Pk_ToursMasterPricingTypes_ID = TD.fk_TourMasterPricingTypes_ID

LEFT JOIN TourPricingTypes TPT on TMPT.Fk_TourPricingType_ID = TPT.pk_TourPricingType_ID

Where b.status in ('XX','1') and DATEADD(day, T.Juliandate - 2, '01-Jan-1900' )
Between '1-Oct-2008' and '1-Oct-2008'

GROUP BY LC.LocalCurrency, LC.CurrencyDescription, T.Juliandate, T.TCode, TD.fk_TourMasterPricingTypes_ID, B.Bcode
WITH ROLLUP

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-11-14 : 05:57:53

Without data it is difficult to say, but are you sure they are duplicate rows and not different in some way?

If they are exact duplicates, use DISTINCT.

gretchog
Starting Member

2 Posts

Posted - 2008-11-14 : 06:13:42

Thanks for the reply. The first three rows produced by the statement look like this:

GBP	Sterling	NULL	39722	Nor00006621	A001878	North Cyprus - Dome Hotel 		Thomas	Conf	2008-05-22 11:24:48.000	2008-10-01 00:00:00.000	2	1854.0000	0.0000	0.0000	0.0000	0.0000	0	0.0000	0	0.0000	0.0000	NULL

GBP	Sterling	NULL	39722	Nor00006621	NULL	North Cyprus - Dome Hotel 		Thomas	Conf	2008-05-22 11:24:48.000	2008-10-01 00:00:00.000	2	1854.0000	0.0000	0.0000	0.0000	0.0000	0	0.0000	0	0.0000	0.0000	NULL

GBP	Sterling	NULL	39722	Nor00006621	NULL	North Cyprus - Dome Hotel 		Thomas	Conf	2008-05-22 11:24:48.000	2008-10-01 00:00:00.000	2	1854.0000	0.0000	0.0000	0.0000	0.0000	0	0.0000	0	0.0000	0.0000	NULL

The second row should be a ROLLUP result, but as you can see, the second and third lines are identical. I'll give it a shot using DISTINCT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources