Is there a better way to build this SP? Joins?

[cpuphantom]

Hello!

Here is what I have:

select 
	(select sum(b.billings) from tbNumbers as b where b.datestamp between dateadd(d, -6, a.datestamp) and a.datestamp and b.user_id = a.user_id) as metric_week,
	a.datestamp
from
	tbNumbers as A
where
	a.user_id in (64, 152, 72, 118, 95) and
	a.datestamp between '1/1/2006' and '8/5/2006' and
	datepart(dw, a.datestamp) = 7 
group by
        a.datestamp,
        a.user_id
order by
	a.datestamp

What is does is supposed to do is give me a printout of rows; each row being the sum of a week's worth of records ending on a Saturday. That part works great... but unfortunately I get a row for each user_id for each week. I just want a row per week for a sum of all users, but I can't remove "a.user_id" from the group by clause because I use it above.

So is there a better way to organize this?

Thanks.

 

[Chopstik]

You could create another query that will reference the A.usr_id filter and then reference that query (view) in the above query. Not sure if it would be any more efficient, though... (The following is NOT tested but is meant to display my general idea.)

[COLOR=green]--This is the first view. We can call it V_FIRST.[/color]
select a.datestamp, a.usr_id
from
    tbNumbers as A
where
    a.user_id in (64, 152, 72, 118, 95) and
    a.datestamp between '1/1/2006' and '8/5/2006' and
    datepart(dw, a.datestamp) = 7 
group by
        a.datestamp,
        a.user_id
order by
    a.datestamp

select 
    (select sum(b.billings) from tbNumbers as b where b.datestamp between dateadd(d, -6, a.datestamp) and a.datestamp and b.user_id = a.user_id) as metric_week,
    a.datestamp
from
    V_FIRST as A
group by
        a.datestamp
order by
    a.datestamp

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[cpuphantom]

You sir, are a genius.

I didn't separate the views since actually all of those things such as the dates and id list are dynamic... but took your idea and combined them like so:

select
    (select sum(b.billings) from tbNumbers as b where b.datestamp between dateadd(d, -6, a.datestamp) and a.datestamp) as metric_week,
    a.datestamp
from
    (	select 
		datestamp, 
		user_id
	from tbNumbers
	where
    		user_id in (select id from tbMembers where office = 1) and
    		datestamp between '1/1/2006' and '8/5/2006' and
    		datepart(dw, datestamp) = 7
	group by
        	datestamp,
		user_id
	) as A
group by
	a.datestamp
order by
    	a.datestamp

The result so far appears to give me exactly what I was hoping for.

Thanks for your help! That's awesome!

 

[Chopstik]

Glad you were able to find a solution.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[cpuphantom]

wait... I spoke too soon.

You're still a genius... but my script was bad. It was giving me one row per week... but the sum was for everyone in the DB... not the specific group I wanted.

This worked much better... though I have a week that went bad for some reason...

select
    sum(b.billings) as metric_week,
    a.datestamp
from
    (	select 
		datestamp, 
		user_id
	from tbNumbers
	where
    		user_id in (select id from tbMembers where office = 1) and
    		datestamp between '1/1/2006' and '8/5/2006' and
    		datepart(dw, datestamp) = 7
	group by
        	datestamp,
		user_id
	) as A,
	tbNumbers as B
where
	b.datestamp between dateadd(d, -6, a.datestamp) and a.datestamp and
	a.user_id = b.user_id
	
group by
	a.datestamp
order by
    	a.datestamp