11-24-2008, 06:37 AM | #1 | ||
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
Ping: lordscarlet and cuervo72 - aka, SQL help :)
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. Code:
I'm RTFM'in but my time constraints are tight and you guys are frickin' SQL-brain-trusts.
__________________
|
||
11-24-2008, 08:49 AM | #2 |
Pro Starter
Join Date: Oct 2005
Location: Washington, DC
|
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.
__________________
Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000 |
11-24-2008, 08:55 AM | #3 |
Head Coach
Join Date: Dec 2002
Location: Maryland
|
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.
__________________
null |
11-24-2008, 10:19 AM | #4 | |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
Quote:
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!
__________________
Last edited by Bonegavel : 11-24-2008 at 10:20 AM. |
|
11-24-2008, 10:20 AM | #5 | |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
Quote:
Not a problem and thanks for taking a look.
__________________
|
|
11-24-2008, 10:47 AM | #6 |
Pro Starter
Join Date: Oct 2005
Location: Washington, DC
|
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.
__________________
Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000 |
11-24-2008, 10:50 AM | #7 |
Pro Starter
Join Date: Oct 2005
Location: Washington, DC
|
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.
__________________
Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000 |
11-24-2008, 11:13 AM | #8 | |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
Quote:
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.
__________________
|
|
11-24-2008, 12:07 PM | #9 | |
Pro Starter
Join Date: Oct 2005
Location: Washington, DC
|
Quote:
Yes, definitely.
__________________
Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000 |
|
11-24-2008, 03:52 PM | #10 |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
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. Code:
When I query it, "C" and "C 1" need to be combined but they are distinct. Any idea how to combine only those 2?
__________________
|
11-24-2008, 04:09 PM | #11 |
Pro Starter
Join Date: Oct 2005
Location: Washington, DC
|
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
__________________
Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000 |
11-25-2008, 08:25 AM | #12 |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
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.
__________________
Last edited by Bonegavel : 11-25-2008 at 08:27 AM. |
11-25-2008, 08:33 AM | #13 |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
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.
__________________
|
11-25-2008, 11:28 AM | #14 |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
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"
__________________
Last edited by Bonegavel : 11-25-2008 at 11:28 AM. |
11-25-2008, 11:35 AM | #15 |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
update table set PN27 = left(PN1,x)
something like this?
__________________
|
11-25-2008, 11:49 AM | #16 |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
Yep, that worked.
__________________
|
11-25-2008, 05:17 PM | #17 |
Pro Starter
Join Date: Oct 2005
Location: Washington, DC
|
If you have the power to do it, PLEASE change your column names.
__________________
Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000 |
11-27-2008, 11:34 AM | #18 |
Awaiting Further Instructions...
Join Date: Nov 2001
Location: Macungie, PA
|
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?
__________________
|
11-28-2008, 07:22 AM | #19 | |
Pro Starter
Join Date: Oct 2005
Location: Washington, DC
|
Quote:
Glad it worked out! Ummm... no.
__________________
Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000 |
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|