Often I see an OP get "chastised" for not providing enough information
- I'll probably be chastised for providing "too much" information. :-)

Here's a little background

I have a table (computed_visits) that, amonst other columns, has
columns for
employee - employee number
sdate - start date
paidt - paid time

For each day of the pay week, an employee will have from 1 to "n" rows
for any given day.

To get the total for an employee for a single day, I can easily
sum(paidt) as paidtime where employee = 1234 and sdate = 'somedate'

To get the week to date total for an employee, I can easily
sum(paidt) as paidtime where employee = 1234 and sdate >=
'some_start_date' and sdate <= 'some_end_date'

Now, my question:
Is there a query that would allow me to get "both" the employee's
total for a single day (yesterday) AND their week to date total?

What I am doing now is first getting the week to date totals and if I
get a row returned, I run another query for just the previous day (if
I don't get any week to date, the employee obviously doesn't have any
time for yesterday).
I then combine the data (in code) from both of these queries for my
report. But I'm dealing with 825+ employees, so the ability to do
this in 1 query would eliminate 825+ selects.

TIA
Lee

On Jul 4, 3:33 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> Often I see an OP get "chastised" for not providing enough information
> - I'll probably be chastised for providing "too much" information. :-)
>
> Here's a little background
>
> I have a table (computed_visits) that, amonst other columns, has
> columns for
> employee - employee number
> sdate - start date
> paidt - paid time
>
> For each day of the pay week, an employee will have from 1 to "n" rows
> for any given day.
>
> To get the total for an employee for a single day, I can easily
> sum(paidt) as paidtime where employee = 1234 and sdate = 'somedate'
>
> To get the week to date total for an employee, I can easily
> sum(paidt) as paidtime where employee = 1234 and sdate >=
> 'some_start_date' and sdate <= 'some_end_date'
>
> Now, my question:
> Is there a query that would allow me to get "both" the employee's
> total for a single day (yesterday) AND their week to date total?
>
> What I am doing now is first getting the week to date totals and if I
> get a row returned, I run another query for just the previous day (if
> I don't get any week to date, the employee obviously doesn't have any
> time for yesterday).
> I then combine the data (in code) from both of these queries for my
> report. But I'm dealing with 825+ employees, so the ability to do
> this in 1 query would eliminate 825+ selects.
>
> TIA
> Lee

Suppose this week runs from 2007-07-02 to 2007-07-06 and that
"yesterday" is 2007-07-03. Then a query like this should work:

select sum(paidt) totalPaidt,
sum(case when sdate = '2007-07-03' then paidt else 0 end case)
yesterdayPaidt
where employee = 1234 and sdate between '2007-07-02' and '2007-07-06'

Never underestimate the power of a CASE statement inside an
aggregate

On Wed, 04 Jul 2007 20:15:15 -0000, ZeldorBlat <zeldorblat@gmail.com>
wrote:

>On Jul 4, 3:33 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
>> Often I see an OP get "chastised" for not providing enough information
>> - I'll probably be chastised for providing "too much" information. :-)
>>
>> Here's a little background
>>
>> I have a table (computed_visits) that, amonst other columns, has
>> columns for
>> employee - employee number
>> sdate - start date
>> paidt - paid time
>>
>> For each day of the pay week, an employee will have from 1 to "n" rows
>> for any given day.
>>
>> To get the total for an employee for a single day, I can easily
>> sum(paidt) as paidtime where employee = 1234 and sdate = 'somedate'
>>
>> To get the week to date total for an employee, I can easily
>> sum(paidt) as paidtime where employee = 1234 and sdate >=
>> 'some_start_date' and sdate <= 'some_end_date'
>>
>> Now, my question:
>> Is there a query that would allow me to get "both" the employee's
>> total for a single day (yesterday) AND their week to date total?
>>
>> What I am doing now is first getting the week to date totals and if I
>> get a row returned, I run another query for just the previous day (if
>> I don't get any week to date, the employee obviously doesn't have any
>> time for yesterday).
>> I then combine the data (in code) from both of these queries for my
>> report. But I'm dealing with 825+ employees, so the ability to do
>> this in 1 query would eliminate 825+ selects.
>>
>> TIA
>> Lee
>
>Suppose this week runs from 2007-07-02 to 2007-07-06 and that
>"yesterday" is 2007-07-03. Then a query like this should work:
>
>select sum(paidt) totalPaidt,
> sum(case when sdate = '2007-07-03' then paidt else 0 end case)
>yesterdayPaidt
>where employee = 1234 and sdate between '2007-07-02' and '2007-07-06'
>
>Never underestimate the power of a CASE statement inside an
>aggregate
>

Looks quite workable - will give it a shot when I get back to the
office on Thu. Of course the result inside the case will have to
"sum()" as well. Note my statement above "an employee will have from
1 to "n" rows for any given day."

Thanks!

Lee

On Jul 4, 7:25 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> On Wed, 04 Jul 2007 20:15:15 -0000, ZeldorBlat <zeldorb...@gmail.com>
> wrote:
>
>
>
> >On Jul 4, 3:33 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> >> Often I see an OP get "chastised" for not providing enough information
> >> - I'll probably be chastised for providing "too much" information. :-)
>
> >> Here's a little background
>
> >> I have a table (computed_visits) that, amonst other columns, has
> >> columns for
> >> employee - employee number
> >> sdate - start date
> >> paidt - paid time
>
> >> For each day of the pay week, an employee will have from 1 to "n" rows
> >> for any given day.
>
> >> To get the total for an employee for a single day, I can easily
> >> sum(paidt) as paidtime where employee = 1234 and sdate = 'somedate'
>
> >> To get the week to date total for an employee, I can easily
> >> sum(paidt) as paidtime where employee = 1234 and sdate >=
> >> 'some_start_date' and sdate <= 'some_end_date'
>
> >> Now, my question:
> >> Is there a query that would allow me to get "both" the employee's
> >> total for a single day (yesterday) AND their week to date total?
>
> >> What I am doing now is first getting the week to date totals and if I
> >> get a row returned, I run another query for just the previous day (if
> >> I don't get any week to date, the employee obviously doesn't have any
> >> time for yesterday).
> >> I then combine the data (in code) from both of these queries for my
> >> report. But I'm dealing with 825+ employees, so the ability to do
> >> this in 1 query would eliminate 825+ selects.
>
> >> TIA
> >> Lee
>
> >Suppose this week runs from 2007-07-02 to 2007-07-06 and that
> >"yesterday" is 2007-07-03. Then a query like this should work:
>
> >select sum(paidt) totalPaidt,
> > sum(case when sdate = '2007-07-03' then paidt else 0 end case)
> >yesterdayPaidt
> >where employee = 1234 and sdate between '2007-07-02' and '2007-07-06'
>
> >Never underestimate the power of a CASE statement inside an
> >aggregate
>
> Looks quite workable - will give it a shot when I get back to the
> office on Thu. Of course the result inside the case will have to
> "sum()" as well. Note my statement above "an employee will have from
> 1 to "n" rows for any given day."
>
> Thanks!
>
> Lee

Ummm, it already does...

On Wed, 04 Jul 2007 20:42:54 -0700, ZeldorBlat <zeldorblat@gmail.com>
wrote:

>> Looks quite workable - will give it a shot when I get back to the
>> office on Thu. Of course the result inside the case will have to
>> "sum()" as well. Note my statement above "an employee will have from
>> 1 to "n" rows for any given day."
>>
>> Thanks!
>>
>> Lee
>
>Ummm, it already does...

Yep, my fingers where ahead of my brain. I decided to VNC to the
office to run a test only to discover that our Novell server had
abended. :-(

So, it was get on the road and make the 70 mile commute to the office.
All is back up and fine now, so decided to give your suggestion a try.

Here is my actual query statement:

select coof,type,employee,sdate,hs,
sum(paidt) paidtime,
sum(paidm) paidmile,
sum(case when sdate = '20070703' then paidt else 0 end) paidty
from computed_visits
where sdate between '20070628' and '20070703'
and type = 'Normal'
and employee = '41-000100015'
group by employee


"coof" "type" "employee" "sdate" "hs" "paidtime"
"paidmile" "paidty"
"0303" "Normal" "41-000100015" "20070628" "H" "1620"
"6" "562"

Note: My dates are stored in a "non-normal" format for reasons beyond
the scope of this thread and times are in minutes.

The above statement works GREAT - thank you. The only thing I had to
change was the ending of the case statement - I had to use just "end"
vs. "end case". ???

Thanks again
Lee

On Jul 5, 12:16 am, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> On Wed, 04 Jul 2007 20:42:54 -0700, ZeldorBlat <zeldorb...@gmail.com>
> wrote:
>
> >> Looks quite workable - will give it a shot when I get back to the
> >> office on Thu. Of course the result inside the case will have to
> >> "sum()" as well. Note my statement above "an employee will have from
> >> 1 to "n" rows for any given day."
>
> >> Thanks!
>
> >> Lee
>
> >Ummm, it already does...
>
> Yep, my fingers where ahead of my brain. I decided to VNC to the
> office to run a test only to discover that our Novell server had
> abended. :-(
>
> So, it was get on the road and make the 70 mile commute to the office.
> All is back up and fine now, so decided to give your suggestion a try.
>
> Here is my actual query statement:
>
> select coof,type,employee,sdate,hs,
> sum(paidt) paidtime,
> sum(paidm) paidmile,
> sum(case when sdate = '20070703' then paidt else 0 end) paidty
> from computed_visits
> where sdate between '20070628' and '20070703'
> and type = 'Normal'
> and employee = '41-000100015'
> group by employee
>
> "coof" "type" "employee" "sdate" "hs" "paidtime"
> "paidmile" "paidty"
> "0303" "Normal" "41-000100015" "20070628" "H" "1620"
> "6" "562"
>
> Note: My dates are stored in a "non-normal" format for reasons beyond
> the scope of this thread and times are in minutes.
>
> The above statement works GREAT - thank you. The only thing I had to
> change was the ending of the case statement - I had to use just "end"
> vs. "end case". ???
>
> Thanks again
> Lee

Yep -- my fault. END CASE is used inside procedures and triggers; END
is used in queries.