Ping: lordscarlet and cuervo72 - aka, SQL help :)

[Bonegavel]

this is a follow up to an old thread.

I need to change the query from Monday - Sunday (The original was through Saturday and I had to start including Sunday ) to Monthly starting at the beginning of the year.

     $query = "select PN1, "
             ."if("
             ."((PN12+PN13+PN14+PN15)/(addtime(PN8, addtime(PN9, PN10)))) > 0,((PN12+PN13+PN14+PN15)/(addtime(PN8, addtime(PN9, PN10)))), (PN12+PN13+PN14+PN15)  "
             .")as Pat2staff "
             ."from physiciannumbers "
             ."where PN1='".$Location."' and PN2 >= date_sub( '".$tmpADate."', INTERVAL dayofweek( '".$tmpADate."' ) -2 DAY ) "
             ."AND PN2 <= date_add( date_sub( '".$tmpADate."', INTERVAL dayofweek( '".$tmpADate."' ) -2 DAY ) , INTERVAL 6 DAY ) "
             ."group by PN1";

I'm RTFM'in but my time constraints are tight and you guys are frickin' SQL-brain-trusts.

[lordscarlet]

I probably can't look at this until after lunch, but let me clarify:

you want every week for a calendar year with data aggregated from monday through sunday?


And, btw, I feel honored -- my first ping thread.

[cuervo72]

I've not actually done much with sql and dates, so I can't give you an answer off the top of my head. Sorry.

[Bonegavel]

Originally Posted by lordscarlet

I probably can't look at this until after lunch, but let me clarify:

you want every week for a calendar year with data aggregated from monday through sunday?


And, btw, I feel honored -- my first ping thread.

Any time

I need the data aggregated every month.

I'll feed the equation a given date (which will always be today) and it will then figure out the current month's last day and first day so I can then feed them into the query:

where PN2 >= $monthStart and PN2 <= $monthEnd

I can then just do a while($monthStart > "2007-12-31") so it only runs back to Jan 1 of this year.

I guess i could just write a query for each month and hardcode it but I may need to go back further and that becomes a pain.

Thanks!

[Bonegavel]

Originally Posted by cuervo72

I've not actually done much with sql and dates, so I can't give you an answer off the top of my head. Sorry.

Not a problem and thanks for taking a look.

[lordscarlet]

OK, I re-read and it looks like you want to group by month instead of week. Based on the old thread, I assume you want to specify the date and it returns the results for that month? I'm going to work on it, let me know if my assumption is wrong.

[lordscarlet]

Actually, this should be much easier than the week one. I think all you need to do is something like

WHERE month(PN2) = month($tmpAdate)

...obvioulsy with the proper quotation marks and such.

[Bonegavel]

Originally Posted by lordscarlet

Actually, this should be much easier than the week one. I think all you need to do is something like

WHERE month(PN2) = month($tmpAdate)

...obvioulsy with the proper quotation marks and such.

Yikes. Nice and simple.

I guess I could do where month(PN2) = month($tmpAdate) and year(PN2) = year($tmpAdate) to make sure it is only this year.

Sweet. Thanks again.

[lordscarlet]

Originally Posted by Bonegavel

Yikes. Nice and simple.

I guess I could do where month(PN2) = month($tmpAdate) and year(PN2) = year($tmpAdate) to make sure it is only this year.

Sweet. Thanks again.

Yes, definitely.

[Bonegavel]

One other thing, I am basing the queries off of location of which all are separate but 2.

In other words, I have:

Locations

A
B
C
C 1
D
E

and I want "C" and "C 1" to be combined in my query so I do a "like" but it isn't combining them.

            $query = "select PN1, "
                     ."round((sum((PN12+PN13+PN14+PN15)))/(Sum(Time_to_Sec(addtime(PN8, addtime(PN9, PN10))))/3600),2)   "
                     ."as Pat2staff "
                     ."from physiciannumbers "
                     ."where PN1 like '".$Location."%' "
                     ."and month(PN2) = month('".$tmpADate."') "
                     ."and year(PN2) = year('".$tmpADate."') "
                     ."group by PN1";

When I query it, "C" and "C 1" need to be combined but they are distinct. Any idea how to combine only those 2?

[lordscarlet]

Hm, I'm not positive if you can group by a calculated column, but maybe something like:

group by case when pn1 <> "C 1" then pn1 else "C" end

my syntax may be slightly off, and it may not work at all, but give it a whirl

[Bonegavel]

since the first 9 characters of the 2 are identical, shouldn't I be able to use that?

I going to mess around with the left() function a bit.

[Bonegavel]

Hmmm, it works but still doesn't group them. Seems that Case is what I need and that looks to be a bitch to incorporate into my query No time like the present.

[Bonegavel]

ugh, this is becoming a huge PITA.

I want to create a new column that has the stripped version of the location. Is there a quick sql statement that will let me take up to the first space or the end of one column and insert it into another?

PN1 is the column I'm taking from and I'm going to put it in PN27.

so if PN1 = "Alentown Podiatry" I want PN27 to be "Allentown"

[Bonegavel]

update table set PN27 = left(PN1,x)

something like this?

[Bonegavel]

Yep, that worked.

[lordscarlet]

If you have the power to do it, PLEASE change your column names.

[Bonegavel]

Thanks for your help. I was able to complete all my stuff before vacation started. Now I can go to my sister's house in Owego NY without guilt for a family filled weekend.

you don't like the column names?

[lordscarlet]

Originally Posted by Bonegavel

Thanks for your help. I was able to complete all my stuff before vacation started. Now I can go to my sister's house in Owego NY without guilt for a family filled weekend.

Glad it worked out!

Originally Posted by Bonegavel

you don't like the column names?

Ummm... no.