November 8, 2007 at 9:43 am
Hello, I have a query that pulls totals and percentages from a dataset
what I would like to do is create a query that provides the totals and percents like the query below but list it by date. So the first couple of rows would look like this:
01-01-2007 | @total_905 | @total_attempted905 | percent_attempted905 | total_contacted905 | percent_contacted905
01-02-2007 | @total_905 | @total_attempted905 | percent_attempted905 | total_contacted905 | percent_contacted905
and I would have a row for each day of the year...
sum(case when a.npa = 'South' then 1 else 0 end) as total_905,
sum(case when b.attempted = 'on' and a.npa = 'South' then 1 else 0 end) as total_attempted905,
left(cast(sum(case when b.attempted = 'on' and a.npa = 'South' then 1 else 0 end) as decimal (6,2)) /
cast(sum(case when a.npa = 'South' then 1 else 0 end) as decimal (6,2)) * 100, 5) as percent_attempted905,
sum(case when b.contacted = 'on' and a.npa = 'South' then 1 else 0 end) as total_contacted905,
left(cast(sum(case when b.contacted = 'on' and a.npa = 'South' then 1 else 0 end) as decimal (6,2)) /
cast(sum(case when a.npa = 'South' then 1 else 0 end) as decimal (6,2)) * 100, 5) as percent_contacted905
from
tbl1 as a
left tbl2 as b
on a.id = b.jobid COLLATE Latin1_General_CI_AS
where
convert(varchar, timeofcode, 112) =
convert(varchar, getdate()-1, 112)
I hope this makes sense, thank you in advance...
November 8, 2007 at 9:50 am
This doesn't make sense.
Can you separate columns and data, or is that not what you're showing.
You have an incomplete SQL statement that includes stuff that doesn't seem to relate to percentages and totals. The CASE statements don't mean anything to us.
Work with a smaller data set, we don't need your exact business logic for everything, just tell us what you are trying to get from the data set. If you have a row for each day, then what is the percentage and totals you are trying to get? running for each day, month, year?
November 8, 2007 at 10:31 am
Ok fair enough Steve, let's say I have a table with a date column and I want perform a calculation like a count for each record that appears for each day this year. So my resulting query will give me (as an example):
dayofyear daily totals
01-01-2007 30
01-02-2007 28
01-03-2007 42
November 8, 2007 at 11:02 am
Is this what you are looking for?
declare @test-2 table
(
[dayofyear] datetime,
dailytotals int
)
insert into @test-2
select '01-01-2007', 01 union all
select '01-01-2007', 05 union all
select '01-01-2007', 10 union all
select '01-02-2007', 28 union all
select '01-03-2007', 42 union all
select '01-03-2007', 01 union all
select '01-03-2007', 15
Select [dayofyear], sum(dailytotals)
from @test-2
group by [dayofyear]
order by [dayofyear] ASC
November 8, 2007 at 1:58 pm
Hi Adam and Steve, I've gone another rout and posted a new question under the title: display contents of while loop.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply