|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SELECT does not return all on left of join when right side has limstumped on this one. First here is the DDL: SELECT f.FinanceClass, COUNT(*) AS TotalCharges, SUM(dbo.udfAgeAmount (0, 29, DATEDIFF(day, service_date, GETDATE()), units*fee)) AS Amount00, SUM(dbo.udfAgeAmount (30, 59, DATEDIFF(day, service_date, GETDATE()), units*fee)) AS Amount30, SUM(dbo.udfAgeAmount (60, 89, DATEDIFF(day, service_date, GETDATE()), units*fee)) AS Amount60, SUM(dbo.udfAgeAmount (90, 119, DATEDIFF(day, service_date, GETDATE()), units*fee)) AS Amount90, SUM(CASE WHEN DATEDIFF(day, service_date, GETDATE()) >= 120 THEN units*fee ELSE 0 END) AS Amount120 FROM FinanceClass f LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass WHERE c.PdSeqNum = 180 AND f.delete_flag <> 'Y' AND c.reversed <> 'Y' GROUP BY f.FinanceClass ; And, ALTER FUNCTION dbo.udfAgeAmount (@LowAge int, @HighAge int, @Age int, @Value float) RETURNS float AS BEGIN RETURN CASE WHEN @Age BETWEEN @LowAge AND @HighAge THEN @Value ELSE 0 END END ------------------------------------------------------------------------------------- This returns: FinanceClass TotalCharges Amount00 etc. --------------- --------------- ---------------- MA 1,000.00 500.00 etc. But what I need is to return ALL the Finance Classes in my FinanceClass table, with zeros for those finance classes that do not return data from charges. For example: --------------- --------------- ---------------- MA 1,000.00 500.00 etc. PP 0 0 etc. The user needs to see every finance class showing even the ones that had no charging activity. I can't seem to crack this and would appreciate help. Thank you. I had a few similar problems and what helped me was making the where clause
part of the Join rather than a where clause eg FROM FinanceClass f > LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass AND c.PdSeqNum = HTH> 180 AND f.delete_flag <> 'Y' AND c.reversed <> 'Y' > GROUP BY f.FinanceClass Show quote "richardb" <richa***@discussions.microsoft.com> wrote in message news:DE2A0595-1266-4083-A162-086960049643@microsoft.com... >I have learned much about SELECT from helpful replies to my posts, but am > stumped on this one. First here is the DDL: > > SELECT f.FinanceClass, COUNT(*) AS TotalCharges, > SUM(dbo.udfAgeAmount (0, 29, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount00, > SUM(dbo.udfAgeAmount (30, 59, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount30, > SUM(dbo.udfAgeAmount (60, 89, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount60, > SUM(dbo.udfAgeAmount (90, 119, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount90, > SUM(CASE WHEN DATEDIFF(day, service_date, GETDATE()) >= 120 THEN > units*fee ELSE 0 END) AS Amount120 > FROM FinanceClass f > LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass > WHERE c.PdSeqNum = 180 AND f.delete_flag <> 'Y' AND c.reversed <> 'Y' > GROUP BY f.FinanceClass ; > > And, > > ALTER FUNCTION dbo.udfAgeAmount (@LowAge int, @HighAge int, @Age int, > @Value > float) > RETURNS float AS > BEGIN > RETURN > CASE > WHEN @Age BETWEEN @LowAge AND @HighAge THEN @Value > ELSE 0 > END > END > ------------------------------------------------------------------------------------- > This returns: > FinanceClass TotalCharges Amount00 etc. > --------------- --------------- ---------------- > MA 1,000.00 500.00 etc. > > But what I need is to return ALL the Finance Classes in my FinanceClass > table, with zeros for those finance classes that do not return data from > charges. For example: > > --------------- --------------- ---------------- > MA 1,000.00 500.00 etc. > PP 0 0 > etc. > > The user needs to see every finance class showing even the ones that had > no > charging activity. I can't seem to crack this and would appreciate help. > Thank you. > > > Yea! Way to go... I just replaced AND instead of WHERE and got all my rows
returned. I didn't know I could do that! Show quote "Newbie" wrote: > I had a few similar problems and what helped me was making the where clause > part of the Join rather than a where clause > eg FROM FinanceClass f > > LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass AND c.PdSeqNum = > > 180 AND f.delete_flag <> 'Y' AND c.reversed <> 'Y' > > GROUP BY f.FinanceClass > > HTH > "richardb" <richa***@discussions.microsoft.com> wrote in message > news:DE2A0595-1266-4083-A162-086960049643@microsoft.com... > >I have learned much about SELECT from helpful replies to my posts, but am > > stumped on this one. First here is the DDL: > > > > SELECT f.FinanceClass, COUNT(*) AS TotalCharges, > > SUM(dbo.udfAgeAmount (0, 29, DATEDIFF(day, service_date, GETDATE()), > > units*fee)) AS Amount00, > > SUM(dbo.udfAgeAmount (30, 59, DATEDIFF(day, service_date, GETDATE()), > > units*fee)) AS Amount30, > > SUM(dbo.udfAgeAmount (60, 89, DATEDIFF(day, service_date, GETDATE()), > > units*fee)) AS Amount60, > > SUM(dbo.udfAgeAmount (90, 119, DATEDIFF(day, service_date, GETDATE()), > > units*fee)) AS Amount90, > > SUM(CASE WHEN DATEDIFF(day, service_date, GETDATE()) >= 120 THEN > > units*fee ELSE 0 END) AS Amount120 > > FROM FinanceClass f > > LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass > > WHERE c.PdSeqNum = 180 AND f.delete_flag <> 'Y' AND c.reversed <> 'Y' > > GROUP BY f.FinanceClass ; > > > > And, > > > > ALTER FUNCTION dbo.udfAgeAmount (@LowAge int, @HighAge int, @Age int, > > @Value > > float) > > RETURNS float AS > > BEGIN > > RETURN > > CASE > > WHEN @Age BETWEEN @LowAge AND @HighAge THEN @Value > > ELSE 0 > > END > > END > > ------------------------------------------------------------------------------------- > > This returns: > > FinanceClass TotalCharges Amount00 etc. > > --------------- --------------- ---------------- > > MA 1,000.00 500.00 etc. > > > > But what I need is to return ALL the Finance Classes in my FinanceClass > > table, with zeros for those finance classes that do not return data from > > charges. For example: > > > > --------------- --------------- ---------------- > > MA 1,000.00 500.00 etc. > > PP 0 0 > > etc. > > > > The user needs to see every finance class showing even the ones that had > > no > > charging activity. I can't seem to crack this and would appreciate help. > > Thank you. > > > > > > > > > richardb wrote:
Show quote > I have learned much about SELECT from helpful replies to my posts, but am You must reference the unpreserved table (Charges) only in the ON> stumped on this one. First here is the DDL: > > SELECT f.FinanceClass, COUNT(*) AS TotalCharges, > SUM(dbo.udfAgeAmount (0, 29, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount00, > SUM(dbo.udfAgeAmount (30, 59, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount30, > SUM(dbo.udfAgeAmount (60, 89, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount60, > SUM(dbo.udfAgeAmount (90, 119, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount90, > SUM(CASE WHEN DATEDIFF(day, service_date, GETDATE()) >= 120 THEN > units*fee ELSE 0 END) AS Amount120 > FROM FinanceClass f > LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass > WHERE c.PdSeqNum = 180 AND f.delete_flag <> 'Y' AND c.reversed <> 'Y' > GROUP BY f.FinanceClass ; > > And, > > ALTER FUNCTION dbo.udfAgeAmount (@LowAge int, @HighAge int, @Age int, @Value > float) > RETURNS float AS > BEGIN > RETURN > CASE > WHEN @Age BETWEEN @LowAge AND @HighAge THEN @Value > ELSE 0 > END > END > ------------------------------------------------------------------------------------- > This returns: > FinanceClass TotalCharges Amount00 etc. > --------------- --------------- ---------------- > MA 1,000.00 500.00 etc. > > But what I need is to return ALL the Finance Classes in my FinanceClass > table, with zeros for those finance classes that do not return data from > charges. For example: > > --------------- --------------- ---------------- > MA 1,000.00 500.00 etc. > PP 0 0 > etc. > > The user needs to see every finance class showing even the ones that had no > charging activity. I can't seem to crack this and would appreciate help. > Thank you. clause, otherwise the join effectively becomes an INNER join. Like: [snip]... FROM FinanceClass f LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass AND c.PdSeqNum = 180 AND c.reversed <> 'Y' WHERE f.delete_flag <> 'Y' GROUP BY f.FinanceClass ; -- David Portas SQL Server MVP -- The alternative is to add conditions to the where clause, like this:
.... WHERE (c.PdSeqNum = 180 or c.PdSeqNum is null) AND (f.delete_flag <> 'Y') AND (c.reversed <> 'Y' or c.reversed is null) .... ML --- http://milambda.blogspot.com/ Thanks for all the responses.
Show quote "richardb" wrote: > I have learned much about SELECT from helpful replies to my posts, but am > stumped on this one. First here is the DDL: > > SELECT f.FinanceClass, COUNT(*) AS TotalCharges, > SUM(dbo.udfAgeAmount (0, 29, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount00, > SUM(dbo.udfAgeAmount (30, 59, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount30, > SUM(dbo.udfAgeAmount (60, 89, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount60, > SUM(dbo.udfAgeAmount (90, 119, DATEDIFF(day, service_date, GETDATE()), > units*fee)) AS Amount90, > SUM(CASE WHEN DATEDIFF(day, service_date, GETDATE()) >= 120 THEN > units*fee ELSE 0 END) AS Amount120 > FROM FinanceClass f > LEFT JOIN Charges c ON f.FinanceClass = c.FinanceClass > WHERE c.PdSeqNum = 180 AND f.delete_flag <> 'Y' AND c.reversed <> 'Y' > GROUP BY f.FinanceClass ; > > And, > > ALTER FUNCTION dbo.udfAgeAmount (@LowAge int, @HighAge int, @Age int, @Value > float) > RETURNS float AS > BEGIN > RETURN > CASE > WHEN @Age BETWEEN @LowAge AND @HighAge THEN @Value > ELSE 0 > END > END > ------------------------------------------------------------------------------------- > This returns: > FinanceClass TotalCharges Amount00 etc. > --------------- --------------- ---------------- > MA 1,000.00 500.00 etc. > > But what I need is to return ALL the Finance Classes in my FinanceClass > table, with zeros for those finance classes that do not return data from > charges. For example: > > --------------- --------------- ---------------- > MA 1,000.00 500.00 etc. > PP 0 0 > etc. > > The user needs to see every finance class showing even the ones that had no > charging activity. I can't seem to crack this and would appreciate help. > Thank you. > > >  |
|||||||||||||||||||||||
Other interesting topics