subquery I was using which did yield distinct rows. I have not been able to
duplicate my actual problem. The actual problme is that I have to select
several fields per row from this one table with the criteria that each recID
has to be unique and has to be the Max(maindate) for a given date and has to
be the max(subdate) for the group of rows per recID containing the
Max(mainDate). Heck, let me start the request over because I got all messed
up.
Actually, I think I found the answer I was looking for (I had to change my
data to give me different Max(subDates):
SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
recID subDate mainDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
Where I was messing up was trying to include Max(subDate) in the Having
clause. Now I got it straight. Now I can create a self join from this
table to the main table and get the correct rows with the additional fields
that I need that meet this criteria. Before I was trying to use Max(subDate
)
from the main table but I had to add a Group By Clause and include all of th
e
additional fields in the Group By clause which added rows. Hopefully, this
will work.
Thank you for your reply. It motivated me.
RichLooks like you're on the right. Might I suggest a cup of caffeine? Works
for me. ;-)
Just a note of caution about your query. It will give the max(subDate),
even if that subDate does not correspond to a row that has a mainDate =
2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
2006-01-25 and then s
out the max (subDate) corresponding to those rows.Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
Thank you for your response. Actually, your suggestion was the original
subquery I was using which did yield distinct rows. I have not been able to
duplicate my actual problem. The actual problme is that I have to select
several fields per row from this one table with the criteria that each recID
has to be unique and has to be the Max(maindate) for a given date and has to
be the max(subdate) for the group of rows per recID containing the
Max(mainDate). Heck, let me start the request over because I got all messed
up.
Actually, I think I found the answer I was looking for (I had to change my
data to give me different Max(subDates):
SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
recID subDate mainDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
Where I was messing up was trying to include Max(subDate) in the Having
clause. Now I got it straight. Now I can create a self join from this
table to the main table and get the correct rows with the additional fields
that I need that meet this criteria. Before I was trying to use
Max(subDate)
from the main table but I had to add a Group By Clause and include all of
the
additional fields in the Group By clause which added rows. Hopefully, this
will work.
Thank you for your reply. It motivated me.
Rich|||Thank you for your suggestion. That is a great idea. As for the caffein,
that would also be a great idea except that (I am a general aviation pilot)
I
have a flight physical this Friday - gotta make sure the BP is nice a low fo
r
the nice doctor :).
Thank you for your help.
"Tom Moreau" wrote:
> Looks like you're on the right. Might I suggest a cup of caffeine? Works
> for me. ;-)
> Just a note of caution about your query. It will give the max(subDate),
> even if that subDate does not correspond to a row that has a mainDate =
> 2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
> 2006-01-25 and then s
out the max (subDate) corresponding to those rows.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
> Thank you for your response. Actually, your suggestion was the original
> subquery I was using which did yield distinct rows. I have not been able
to
> duplicate my actual problem. The actual problme is that I have to select
> several fields per row from this one table with the criteria that each rec
ID
> has to be unique and has to be the Max(maindate) for a given date and has
to
> be the max(subdate) for the group of rows per recID containing the
> Max(mainDate). Heck, let me start the request over because I got all mess
ed
> up.
> Actually, I think I found the answer I was looking for (I had to change my
> data to give me different Max(subDates):
> SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
> FROM tbl2
> GROUP BY recid
> HAVING MAX(mainDate)='1/25/06'
> recID subDate mainDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> Where I was messing up was trying to include Max(subDate) in the Having
> clause. Now I got it straight. Now I can create a self join from this
> table to the main table and get the correct rows with the additional field
s
> that I need that meet this criteria. Before I was trying to use
> Max(subDate)
> from the main table but I had to add a Group By Clause and include all of
> the
> additional fields in the Group By clause which added rows. Hopefully, thi
s
> will work.
> Thank you for your reply. It motivated me.
> Rich
>|||I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
month. I used to stay away from coffee on the same day as my medical. Now,
I don't. I've been getting very good BP and resting pulse rates in spite of
it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
Thank you for your suggestion. That is a great idea. As for the caffein,
that would also be a great idea except that (I am a general aviation pilot)
I
have a flight physical this Friday - gotta make sure the BP is nice a low
for
the nice doctor :).
Thank you for your help.
"Tom Moreau" wrote:
> Looks like you're on the right. Might I suggest a cup of caffeine? Works
> for me. ;-)
> Just a note of caution about your query. It will give the max(subDate),
> even if that subDate does not correspond to a row that has a mainDate =
> 2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
> 2006-01-25 and then s
out the max (subDate) corresponding to those> rows.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
> Thank you for your response. Actually, your suggestion was the original
> subquery I was using which did yield distinct rows. I have not been able
> to
> duplicate my actual problem. The actual problme is that I have to select
> several fields per row from this one table with the criteria that each
> recID
> has to be unique and has to be the Max(maindate) for a given date and has
> to
> be the max(subdate) for the group of rows per recID containing the
> Max(mainDate). Heck, let me start the request over because I got all
> messed
> up.
> Actually, I think I found the answer I was looking for (I had to change my
> data to give me different Max(subDates):
> SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
> FROM tbl2
> GROUP BY recid
> HAVING MAX(mainDate)='1/25/06'
> recID subDate mainDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> Where I was messing up was trying to include Max(subDate) in the Having
> clause. Now I got it straight. Now I can create a self join from this
> table to the main table and get the correct rows with the additional
> fields
> that I need that meet this criteria. Before I was trying to use
> Max(subDate)
> from the main table but I had to add a Group By Clause and include all of
> the
> additional fields in the Group By clause which added rows. Hopefully,
> this
> will work.
> Thank you for your reply. It motivated me.
> Rich
>|||This reply is in lieu of the caffeine :). I did the whole route in aviation
,
CFI, miliatary, commercial (part 135). I wasn't really making much headway
after a while, so I threw the towel in on commercial aviation and went for D
B
programming (EE major in college). I just stay current for part 91 flying.
But having the sitdown 8 hrs a day job, drinking caffeine for the last bunch
of years, has taken its toll on me. Now I have to keep an eye on the BP. S
o
I ride my bicycle to the workplace.
As for the project I am working on, man, the data is like an ocean. Nothing
gets deleted, just updated, added, and moved around to various tables. The
architecture is OK, normalizatin OK (data gets replicated - that's OK). But
there are so many twists that I have to compensate for, so I have to write
these crazy queries. Even so, this is still working out a little better
than the aviation scene for me.
--Rich
"Tom Moreau" wrote:
> I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
> month. I used to stay away from coffee on the same day as my medical. No
w,
> I don't. I've been getting very good BP and resting pulse rates in spite
of
> it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
> Thank you for your suggestion. That is a great idea. As for the caffein,
> that would also be a great idea except that (I am a general aviation pilot
)
> I
> have a flight physical this Friday - gotta make sure the BP is nice a low
> for
> the nice doctor :).
> Thank you for your help.
> "Tom Moreau" wrote:
>
>|||I started life as a scientist. One of my first discoveries was that there's
no money in scientific research, so I made a "lateral" move to IT. I had a
lot of computing background while an undergrad and as a grad. It's amazing
how many people are in IT that didn't start there.
Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
I'm inclined. ;-)
I hear ya about the data. I had an assignment with a police database. They
delete nothing - they just buy a bigger server!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
This reply is in lieu of the caffeine :). I did the whole route in
aviation,
CFI, miliatary, commercial (part 135). I wasn't really making much headway
after a while, so I threw the towel in on commercial aviation and went for
DB
programming (EE major in college). I just stay current for part 91 flying.
But having the sitdown 8 hrs a day job, drinking caffeine for the last bunch
of years, has taken its toll on me. Now I have to keep an eye on the BP.
So
I ride my bicycle to the workplace.
As for the project I am working on, man, the data is like an ocean. Nothing
gets deleted, just updated, added, and moved around to various tables. The
architecture is OK, normalizatin OK (data gets replicated - that's OK). But
there are so many twists that I have to compensate for, so I have to write
these crazy queries. Even so, this is still working out a little better
than the aviation scene for me.
--Rich
"Tom Moreau" wrote:
> I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
> month. I used to stay away from coffee on the same day as my medical.
> Now,
> I don't. I've been getting very good BP and resting pulse rates in spite
> of
> it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
> Thank you for your suggestion. That is a great idea. As for the caffein,
> that would also be a great idea except that (I am a general aviation
> pilot)
> I
> have a flight physical this Friday - gotta make sure the BP is nice a low
> for
> the nice doctor :).
> Thank you for your help.
> "Tom Moreau" wrote:
>
>|||I just have to ask this one last question as far as scientists, go - incase
you have not seen a movie that was released just this month called "8 Below*
about 8 sled dogs in Antarctica, their master, and a "scientist" from l thin
k
UCLA in the movie - great movie, but they really make the scientist guy look
pretty happening - had the nice house in LA somewhere, big grant from the
University. I take it this only happens in Hollywood?:).
BTW, it was a great movie, especially if you are a dog person. Oh, and to
keep this on Topic, I have to get a count of items for my current project.
This would seems uneventful except for the part that no one has been able to
get accurate/consistent counts. The last guy to try was a young wiz kid, bu
t
no go. So I am tearing apart the DB on a Dev server and finding a variety o
f
data discrepancies like ID's in history tables pertaining to given entities
and the same entities in the active tables with different ID's. I have
already been instructed to Update the history tables to the Active ID's.
Thus, my crazy queries. This data has been getting collected for over 17
years! I'm just going to John Henry it (sledge hammer my way through the
data).
--Rich
"Tom Moreau" wrote:
> I started life as a scientist. One of my first discoveries was that there
's
> no money in scientific research, so I made a "lateral" move to IT. I had
a
> lot of computing background while an undergrad and as a grad. It's amazi
ng
> how many people are in IT that didn't start there.
> Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
> I'm inclined. ;-)
> I hear ya about the data. I had an assignment with a police database. Th
ey
> delete nothing - they just buy a bigger server!
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
> This reply is in lieu of the caffeine :). I did the whole route in
> aviation,
> CFI, miliatary, commercial (part 135). I wasn't really making much headwa
y
> after a while, so I threw the towel in on commercial aviation and went for
> DB
> programming (EE major in college). I just stay current for part 91 flying
.
> But having the sitdown 8 hrs a day job, drinking caffeine for the last bun
ch
> of years, has taken its toll on me. Now I have to keep an eye on the BP.
> So
> I ride my bicycle to the workplace.
> As for the project I am working on, man, the data is like an ocean. Nothi
ng
> gets deleted, just updated, added, and moved around to various tables. Th
e
> architecture is OK, normalizatin OK (data gets replicated - that's OK). B
ut
> there are so many twists that I have to compensate for, so I have to write
> these crazy queries. Even so, this is still working out a little better
> than the aviation scene for me.
> --Rich
> "Tom Moreau" wrote:
>
>|||I hear ya. Haven't seen the movie, but a scientist with a nice house and
big grant doesn't sound very credible. My cousin took a voluntary pay cut
so he could get lab equipment. Then, he moved from Canada to Wisconsin and
things improved, though I wouldn't call it lucrative.
Good luck on the data cleansing!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:02B08CB1-0C9F-4674-8539-A3A5DD3B076B@.microsoft.com...
I just have to ask this one last question as far as scientists, go - incase
you have not seen a movie that was released just this month called "8 Below*
about 8 sled dogs in Antarctica, their master, and a "scientist" from l
think
UCLA in the movie - great movie, but they really make the scientist guy look
pretty happening - had the nice house in LA somewhere, big grant from the
University. I take it this only happens in Hollywood?:).
BTW, it was a great movie, especially if you are a dog person. Oh, and to
keep this on Topic, I have to get a count of items for my current project.
This would seems uneventful except for the part that no one has been able to
get accurate/consistent counts. The last guy to try was a young wiz kid,
but
no go. So I am tearing apart the DB on a Dev server and finding a variety
of
data discrepancies like ID's in history tables pertaining to given entities
and the same entities in the active tables with different ID's. I have
already been instructed to Update the history tables to the Active ID's.
Thus, my crazy queries. This data has been getting collected for over 17
years! I'm just going to John Henry it (sledge hammer my way through the
data).
--Rich
"Tom Moreau" wrote:
> I started life as a scientist. One of my first discoveries was that
> there's
> no money in scientific research, so I made a "lateral" move to IT. I had
> a
> lot of computing background while an undergrad and as a grad. It's
> amazing
> how many people are in IT that didn't start there.
> Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
> I'm inclined. ;-)
> I hear ya about the data. I had an assignment with a police database.
> They
> delete nothing - they just buy a bigger server!
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
> This reply is in lieu of the caffeine :). I did the whole route in
> aviation,
> CFI, miliatary, commercial (part 135). I wasn't really making much
> headway
> after a while, so I threw the towel in on commercial aviation and went for
> DB
> programming (EE major in college). I just stay current for part 91
> flying.
> But having the sitdown 8 hrs a day job, drinking caffeine for the last
> bunch
> of years, has taken its toll on me. Now I have to keep an eye on the BP.
> So
> I ride my bicycle to the workplace.
> As for the project I am working on, man, the data is like an ocean.
> Nothing
> gets deleted, just updated, added, and moved around to various tables.
> The
> architecture is OK, normalizatin OK (data gets replicated - that's OK).
> But
> there are so many twists that I have to compensate for, so I have to write
> these crazy queries. Even so, this is still working out a little better
> than the aviation scene for me.
> --Rich
> "Tom Moreau" wrote:
>
>
No comments:
Post a Comment