October 23, 2006 at 1:05 am
Hi all,
Please help me to generate automated email for the following.
Throughout the day data will be loaded into Table X. Type of records can be broadly clasiffied into A, B, C. Now, I have to generate automated email at the end of the day to inform how many records have been loaded for record type A, B and C ...
select vcCustomer, count(*) as cnt1, count(*) as cnt2, count(*) as cnt3 from Customer c (nolock)
where vcCustomer in (
'A',
'B',
'C'
)
and convert(char(8), dLoad, 3) = convert(char(8), getdate()-1, 3)
group by vcCustomer
order by vcCustomer
But here, I can not assign count of B and C into cnt2 and cnt3 in global variables. Please let me know how can I do this? Any article related to this is also usefull.
Thanks in advance.
October 23, 2006 at 4:31 pm
Select Customer.vcCustomer,Sum(Case when vcCustomer ='A' Then 1 Else 0 End) as ACustCounts,
Sum(Case when vcCustomer ='B' Then 1 Else 0 End) as BCustCounts,
Sum(Case when vcCustomer ='C' Then 1 Else 0 End) as CCustCounts
from Customer where Customer.vcCustomer in ('A','B','C')
and convert(char(8), dLoad, 3) = convert(char(8), getdate()-1, 3)
group by vcCustomer
order by vcCustomer
October 23, 2006 at 5:43 pm
Thanks a lot Shree
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply