Click here to Skip to main content
14,599,112 members
Rate this:
Please Sign up or sign in to vote.
In @MonthTable i have monthno from 1 to 12 and year 2005.and make left join with glpost table
.I need when monthno and month of [date] column match or not every time all data should come.
but this query return on matched record.whats wrong with that query.
Thanks


Declare @MonthTable Table (MonthNo int, YearNo int)

SELECT MT.MonthNo, MT.YearNo, D.ID, Sum(P.DebitAmt), Sum(P.CreditAmt)
 FROM  @MonthTable [MT]
Left Join GLPOST [P] on MT.MonthNo = Month(P.[Date]
 Join  GLData [D] on P.GLDATAID = D.ID
Where D.ID=610
Group By MT.MonthNo, MT.YearNo, D.ID
Order by D.ID, MT.yearNo, MT.MonthNo
Posted
Updated 12-Mar-14 0:18am
v2
Comments
Bernhard Hiller 13-Mar-14 4:35am
   
? Do you think of cases where Month(P.[Date]) is greater than 12 or less than 1? Or of cases where P.[Date] is null?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

There seems to be a typo in the SQL code you provided; on line:
Left Join GLPOST [P] on MT.MonthNo = Month(P.[Date]

There is a closing parenthesis missing.
One should read:
Left Join GLPOST [P] on MT.MonthNo = Month(P.[Date])


Hope this helps.

[Edit]

Since D.ID is included in GROUP BY clause, I would go for a HAVING instead of a WHERE clause for this column. Also, you do not really need the [D] table here (the only column you are retrieving from it can be obtained from P.GLDATAID). Thus:
SELECT MT.MonthNo, MT.YearNo, P.GLDATAID, Sum(P.DebitAmt), Sum(P.CreditAmt)
FROM  @MonthTable [MT]
  LEFT JOIN GLPOST [P] on MT.MonthNo = Month(P.[Date])
HAVING P.GLDATAID=610
GROUP BY MT.MonthNo, MT.YearNo, P.GLDATAID
ORDER BY P.GLDATAID, MT.yearNo, MT.MonthNo

[/Edit]
   
v2
Comments
RahulRana723 12-Mar-14 6:24am
   
Thanks for reply.But there is no error in typing.i only say why left join work like inner join here
phil.o 12-Mar-14 6:34am
   
See my updated answer.
RahulRana723 12-Mar-14 6:47am
   
Not working.This query retrieving only matching rows.why.only thing i say why left join working like inner join

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100