November 8, 2007 at 1:59 pm
Hi I have a while loop that I've pasted below. I know it works because I can use the print command to display the results, what I'd like to do is use the select into command to build a table out of this or to create a view out of the dataset below. But I can't figure out how to do this I keep getting syntax errors. Can anyone help me modify the query below to dump into a table or view? Thank you.
declare @total416 as int
declare @total_attempted416 as int
declare @percent_attempted416 as decimal
declare @total_contacted416 as int
declare @percent_contacted416 as decimal
declare @avgdate as datetime
set @avgdate = convert(varchar, '11-01-2007', 112)
while @avgdate < getdate()
begin
select
@total416 = sum(case when a.npa = 'Metro' then 1 else 0 end),
@total_attempted416 = sum(case when b.attempted = 'on' and a.npa = 'Metro' then 1 else 0 end),
@percent_attempted416 = left(cast(sum(case when b.attempted = 'on' and a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) /cast(sum(case when a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) * 100, 5),
@total_contacted416 = sum(case when b.contacted = 'on' and a.npa = 'metro' then 1 else 0 end),
@percent_contacted416 = left(cast(sum(case when b.contacted = 'on' and a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) /cast(sum(case when a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) * 100, 5),
from
MCommPLOrders as a
left join MCommPLOrderAnalysis as b
on a.id = b.jobid COLLATE Latin1_General_CI_AS
where
convert(varchar, @avgdate, 112) = convert(varchar, timeofcode, 112)
set @avgdate = dateadd(dd, 1, @avgdate)
end
November 8, 2007 at 2:15 pm
I only briefly scanned your post but SELECT INTO only works for the first loop through. That command is used to build a table, on the fly, where the table doesn't exist. Therefore, on the second pass, where it has already been created, it will throw an error. You can build a table first and then use an INSERT command if you want to create a record of the counters/values of the loop.
November 8, 2007 at 2:17 pm
While you should have gotten an error, I'm not sure why it was a syntax error. SELECT INTO won't work because it's a loop, so on the second pass, it should tell you that the table already exists.
If you want to store the results, just create a table and do an INSERT for each pass through the loop.
ETA: I see Dave I beat me to it. 🙂
November 8, 2007 at 2:19 pm
or - you SKIP the loop and do it in one shot...
select
convert(varchar, timeofcode, 112) avgdate,
sum(case when a.npa = 'Metro' then 1 else 0 end) total416,
sum(case when b.attempted = 'on' and a.npa = 'Metro' then 1 else 0 end) total_attempted416,
left(cast(sum(case when b.attempted = 'on' and a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) /cast(sum(case when a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) * 100, 5) percent_attempted416,
sum(case when b.contacted = 'on' and a.npa = 'metro' then 1 else 0 end) total_contacted416,
left(cast(sum(case when b.contacted = 'on' and a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) /cast(sum(case when a.npa = 'Metro' then 1 else 0 end) as decimal (6,2)) * 100, 5) percent_contacted416,
from
MCommPLOrders as a
left join MCommPLOrderAnalysis as b
on a.id = b.jobid COLLATE Latin1_General_CI_AS
where timeofcode>'11/1/2007'
group by convert(varchar, timeofcode, 112)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 3:14 pm
If all calculations are made upon 'Metro', move the filter to the WHERE clause.SELECT DATEADD(DAY, ToC, '19000101') AS TimeOfCode,
Total416,
TotalAttempted416,
TotalAttempted416 / Total416 AS PercentAttempted416,
TotalContacted416,
TotalContacted416 / Total416 AS PercentContacted416
FROM (
SELECT DATEDIFF(DAY, '19000101', TimeOfCode) AS ToC,
COUNT(*) AS Total416,
SUM(CASE WHEN b.Attempted = 'On' THEN 100.0 ELSE 0.0 END) AS TotalAttempted416,
SUM(CASE WHEN b.Contacted = 'On' THEN 100.0 ELSE 0.0 END) AS TotalContacted416
FROM MCommPLOrders AS a
LEFT JOIN MCommPLOrderAnalysis AS b ON b.jobid COLLATE Latin1_General_CI_AS = a.id
WHERE a.npa = 'Metro'
AND TimeOfCode >= '11-01-2007'
AND TimeOfCode < DATEDIFF(DAY, '18991231', CURRENT_TIMESTAMP)
GROUP BY DATEDIFF(DAY, '19000101', TimeOfCode)
)
ORDER BY ToC
N 56°04'39.16"
E 12°55'05.25"
November 9, 2007 at 11:35 am
Matt,
your solution worked best thanks everyone for responding.
November 9, 2007 at 1:03 pm
Great job, Matt!
N 56°04'39.16"
E 12°55'05.25"
November 9, 2007 at 1:16 pm
I don't know what constitutes "best" in this case - I'm pretty Peter's beats mine performance-wise.
That being said - thanks for the feedback!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply