Conversion failed when converting date and/or time.. CTEs

[nickg21]

Hey Everyone,
I am using the below query to try and group together values by the month of the date selected. the problem is that sometimes CTE2 will not have the same dates as CTE and those values end up omitted. Is there anyway I can just group these on a different parameter, or just on the month of the date?
Any help would be great.

;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID) as total, Inquiry.Date as InqDate from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >='6/1/2012' AND Inquiry.Date <= '9/6/2012' AND Inquiry.Date IS NOT NULL) 
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
)
,CTE2(total, TransDate) as
(
SELECT count(Inquiry.ID) as total, Inquiry.Date as InqDate from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >='6/1/2012' AND Inquiry.Date <= '9/6/2012' AND Inquiry.Date IS NOT NULL) 
AND (Inquirer.Program = 'Res. Referral Coord.') AND TransferInquiryID_fk IS NOT NULL
Group By Inquiry.Date
)
SELECT CASE
When Grouping(A.InitDate) = 1 THEN 'Date'
When Grouping(A.InitDate) = 1 THEN ''
ELSE A.InitDate
End As 'InitDate'
,Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE A
left outer Join CTE2 B on A.InitDate = B.TransDate
group by A.InitDate with rollup

Thank you,
NickG

[kkunodziya]

In your CTE columns you have a column named InitDate but in the selected columns you have the column as InqDate. Same applies to CE2 you have a column named Transdate and yet in your select statement you have it as InqDate. You might need to begin by fixing that part.

[nickg21]

Thanks for your suggestion, I tried that and still receive the same error as above. If I take out the "with rollup" than it let's the query be successful, but as I mentioned it only allows the second query to return results that have the exact same date as the first query when ultimately I just want to group them by month.

[kkunodziya]

--ok. Continue from there. Then you will need to use the function Month(date) in your query. The value 'Date' in your query, might cause an error since it won't be same data type as date, unless you have your InitDate as varchar.

Try the following :

SELECT CASE
When Grouping(Month(A.InitDate)) = 1 THEN ''
else A.InitDate End As 'InitDate'
,Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE A
left outer Join CTE2 B on A.InitDate = B.TransDate
group by Month(A.InitDate),A.InitDate with rollup

[nickg21]

Hey kk,
Thank you for your suggestion, that actually is working almost exactly how I want it to. The one issue I am having is with the

 When Grouping(Month(A.InitDate)) = 1 Then ''

If i'm correct with reading the resultset that I can that basically totals each individual month and puts it into a row that looks like this;

InitDate InitCount TransCount
NULL 25 3

Where 25 is the total of InitCount for the month and 3 is the total of TransCount which is correct in the totalling, it's just that when I try to use this query in Report Builder, it considers the NULL a date value which defaults to 01-01-1900 in SQL Server. Do you know any way to work around that?
Thank you very much for all your help, I wouldn't be able to get this done without you,
Nick G

[kkunodziya]

How about converting the date to varchar(11) and then pass it as varchar.
like so:

When Grouping(Month(A.InitDate)) = 1 Then ' ' else isnull(Convert(varchar(11),A.InitDate,106),' ') End As 'InitDate'
---an empty string should be returned incase of a null.