October 23, 2006 at 1:29 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 1:57 am
Hi,
Look up CASE in BooksOnline. Something similar to this should do the job:
SELECT c.vcCustomer,
SUM(CASE WHEN c.vcCustomer = 'A' THEN 1 ELSE 0 END) as cntA,
SUM(CASE WHEN c.vcCustomer = 'B' THEN 1 ELSE 0 END) as cntB,
SUM(CASE WHEN c.vcCustomer = 'C' THEN 1 ELSE 0 END) as cntC
FROM Customer c WITH(NOLOCK)
WHERE c.vcCustomer IN ('A','B','C')
AND c.dLoad >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
AND c.dLoad < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)
GROUP BY c.vcCustomer
ORDER BY c.vcCustomer
I have also taken liberty to change the time evaluation in WHERE clause, because of performance. Column from the table should always (if possible) be taken directly, without any conversion and recalculation - otherwise you run the risk of forcing index scan or even table scan instead of index seek. (I suppose there is an index on dLoad - if not, you'll have to create it). Also, DATEADD/DATEDIFF functions have better performance than conversions.
I'm ready to explain, if something is not clear in the posted code.
October 23, 2006 at 5:42 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