SQL Server insert with sub queries

0
by on July 9, 2013 at 1:51 pm

Extremely awesome SQL query I wrote.


use my_db

INSERT INTO
 dbo.my_archive

select
ltrim(rtrim(str(b.my_id)))+ltrim(rtrim(str(b.date))) pid_date,
b.my_id,
b.date,
p.column1+p.column2 ssdd,
p.last_name,
p.first_name,
COUNT (*) Amount,
(
select COUNT(*) from amounts b1
where b1.flags = '1'
and b1.my_id = b.my_id
and b1.date = b.date
) Deleted,
(
select SUM(m.count1)
from mail_counts m
where m.my_id = b.my_id
and m.date = b.date
) Counts

 from amounts b

inner join pusers p
on b.my_id = p.my_id

where (b.date < '20130701' and b.date >= '20120101')
and not exists (
select *
FROM dbo.my_archive mt
where mt.pid_date = ltrim(rtrim(str(b.my_id)))+ltrim(rtrim(str(b.date)))
)
and p.column1 != 'TS'

group by b.date, b.my_id, p.first_name, p.last_name, p.column1+p.column2

in Uncategorized

,

You can skip to the end and leave a response. Pinging is currently not allowed.

Categories