January 25, 2013 at 8:39 pm
Hello - I am trying to create a script which could give me all the dates from @StartDate to @EndDate with Count(ID) and if there is any now record for any Date, it should give me Date with Value of 0. Every thing else is working beside its not giving me dates, In my case there is no ID's for 11-13-2011 & 11-17-2011 but i want the script to show dates with zero value.
Advance thanks for your help -
Declare @StartDateDateTime = '2011/11/11'
Declare @EndDateDateTime = '2011/11/18';
;WITH Actions(RPTDate, IdentityId) AS
(
SELECT DATEADD(DAY, n-1,@StartDate) AS RptDate,
ROW_NUMBER() over(order by (select 1)) as IdentityId
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate,@EndDate)+ 1)
ROW_NUMBER() OVER (ORDER BY Number) FROM master..spt_values)AS x(n)
)
SELECTIdentityId,CAST(RPTDate as date) As ReportDate,
Incom = ISNULL(Count(ACTION_ID),0)
FROM Actions
LEFT OUTER JOIN [EVO_DATA50004].[50005].[AM_ACTION]
ON CAST(RptDate AS DATE) = CAST(START_DATE_UT AS DATE)
WHEREACTION_TYPE_ID = 18
ANDGROUP_ID IN ('8', '18','19','20','23','25','28')
GROUP BYCAST(RPTDate as date),IdentityId
ORDER BYCAST(RPTDate as date)
January 25, 2013 at 11:01 pm
Unfortunately you haven't provided enough information to really help you. Please read the first article I reference below in my signature block. Follow the advice in that article regarding what you should post and how to post it to get the best possible answer.
January 26, 2013 at 4:44 am
Move the conditions on ACTION_TYPE_ID and GROUP_ID out of the WHERE clause and into the LEFT JOIN - they're excluding the 0 rows from the results.
That should do it, although as Lynn said, it's a little hard to be sure without knowing the table definitions etc.
January 26, 2013 at 10:36 am
Gazareth (1/26/2013)
Move the conditions on ACTION_TYPE_ID and GROUP_ID out of the WHERE clause and into the LEFT JOIN - they're excluding the 0 rows from the results.That should do it, although as Lynn said, it's a little hard to be sure without knowing the table definitions etc.
The other part is the complete lack of table aliases in the query. Without the table definitions it is impossible to know what columns are from what table. I'm not saying it is a best practice (I don't have any references) but in multi-table queries you really should use table alaises and use them on all columns not just those where column names are the same between tables. I even do this on singe table queries in my production code.
January 26, 2013 at 11:07 am
Thanks Guys ! By adding the Allians and moving the conditions on ACTION_TYPE_ID and GROUP_ID out of the WHERE clause and into the LEFT JOIN works for me. Again Thanks alot.
Cheers!
January 26, 2013 at 3:48 pm
Lynn Pettis (1/26/2013)
Gazareth (1/26/2013)
Move the conditions on ACTION_TYPE_ID and GROUP_ID out of the WHERE clause and into the LEFT JOIN - they're excluding the 0 rows from the results.That should do it, although as Lynn said, it's a little hard to be sure without knowing the table definitions etc.
The other part is the complete lack of table aliases in the query. Without the table definitions it is impossible to know what columns are from what table. I'm not saying it is a best practice (I don't have any references) but in multi-table queries you really should use table alaises and use them on all columns not just those where column names are the same between tables. I even do this on singe table queries in my production code.
Totally agree Lynn, I do the same. Although in this case we can infer the columns are from the table - there's only two objects referenced and we have the CTE definition.
Glad you got it sorted Arman - it can be a bit of a gotcha where outer joins are concerned. I actually recently wrote an interview question asking for this exact type of result so the candidates can demonstrate they know how where clauses & outer joins interact 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply