Help With Some SQL

[John Kostenbader]

I have tried getting this right from within an Access Grid but without
success. My table looks like this:

12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60


My ultimate output would be a crosstab sort of statment that would have the
four uniqe levels as columns and the dates as rows with a summation of the
amount of time at each level (level is the last value furthest from the
left)

My rudimentary SQL as come up with this...

TRANSFORM Sum(tblHistory. previousminutes ) AS SumOfpreviousmi nutes
SELECT tblHistory.dtmT ime
FROM tblHistory
GROUP BY tblHistory.dtmT ime
PIVOT tblHistory.stat us;

The only thing wrong with this is it lists all entries for 12/24/2005
instead of one row for 12/24....

Can anyonoe suggest a corrrection

Regards

John Kostenbader

[Erland Sommarskog]

Re: Help With Some SQL

John Kostenbader (john@kostenbad er.com) writes:[color=blue]
> I have tried getting this right from within an Access Grid but without
> success. My table looks like this:
>
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
>
> My ultimate output would be a crosstab sort of statment that would have
> the four uniqe levels as columns and the dates as rows with a summation
> of the amount of time at each level (level is the last value furthest
> from the left)
>
> My rudimentary SQL as come up with this...
>
> TRANSFORM Sum(tblHistory. previousminutes ) AS SumOfpreviousmi nutes
> SELECT tblHistory.dtmT ime
> FROM tblHistory
> GROUP BY tblHistory.dtmT ime
> PIVOT tblHistory.stat us;
>
> The only thing wrong with this is it lists all entries for 12/24/2005
> instead of one row for 12/24....[/color]

Since the you think that the result is almost right, I assume that
you are looking for answer in Access. In this case, you should post
to an Access newsgroup, as the syntax you are using is peculiar to
Access.

If you want to run your question in SQL Server, this is the right
place, but alas I have problem to understnad what is what. The
recommendation for this sort of questions is to include:

o CREATE TABLE statement for your table.
o INSERT statement with sample data.
o The desired result given the sample.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Help With Some SQL

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You show time as Chronons and not durations, so your schema is probably
wrong . You show times in non -ISO-8601 fomats in violation of
Standard SQL. Also, read ISO-11179 so you will stop using those silly,
redundant "tbl-" prefixes. It makes you look like an OO programmer!

Try again and we can help when you give us enough to work with.

[John Kostenbader]

Re: Help With Some SQL

I've posted in plenty of groups in the past without this rudeness...I'm very
sorry to the gentleman who thought I posted in the wrong group and I
apologize to the gentleman who believes me an armature and still uses "tbl"
(it happens to be a table scheme I'm comfortable with and use regularly

I've got news for you...I am an armature looking for some assistance. I
believe that is the original intent of such newsgroups.

Thank you for the intention of your posts

"John Kostenbader" <john@kostenbad er.com> wrote in message
news:89CdnS3X8a xzVOLfRVn-2g@rcn.net...[color=blue]
>I have tried getting this right from within an Access Grid but without
>success. My table looks like this:
>
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
>
> My ultimate output would be a crosstab sort of statment that would have
> the four uniqe levels as columns and the dates as rows with a summation of
> the amount of time at each level (level is the last value furthest from
> the left)
>
> My rudimentary SQL as come up with this...
>
> TRANSFORM Sum(tblHistory. previousminutes ) AS SumOfpreviousmi nutes
> SELECT tblHistory.dtmT ime
> FROM tblHistory
> GROUP BY tblHistory.dtmT ime
> PIVOT tblHistory.stat us;
>
> The only thing wrong with this is it lists all entries for 12/24/2005
> instead of one row for 12/24....
>
> Can anyonoe suggest a corrrection
>
> Regards
>
> John Kostenbader
>[/color]

[Tom Travolta]

Re: Help With Some SQL

"John Kostenbader" <john@kostenbad er.com> wrote in message
news:UqadnVFCWK iDih3fRVn-ow@rcn.net...[color=blue]
> I've posted in plenty of groups in the past without this rudeness...I'm
> very sorry to the gentleman who thought I posted in the wrong group and I
> apologize to the gentleman who believes me an armature and still uses
> "tbl" (it happens to be a table scheme I'm comfortable with and use
> regularly
>
> I've got news for you...I am an armature looking for some assistance. I
> believe that is the original intent of such newsgroups.
>
> Thank you for the intention of your posts[/color]


Don't worry abou the ISO-Nazis. Although they would like to prosecute
offenders, ISO standards are not legally binding and you may name your
tables as you please. If they had their way, they would be laying down
standards for the naming of children and have secret police to re-educate
those who used non-standard names.

[andy]

Re: Help With Some SQL

GROUP BY tblHistory.dtmT ime

That'd be a datetime.
And you're sorta totalling by second.
You need to do something like format(tblHisto ry.dtmTime, "yyyymmdd")
to get all the stuff for one day totalled together.

John Kostenbader wrote:[color=blue]
> I have tried getting this right from within an Access Grid but[/color]
without[color=blue]
> success. My table looks like this:
>
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
>
> My ultimate output would be a crosstab sort of statment that would[/color]
have the[color=blue]
> four uniqe levels as columns and the dates as rows with a summation[/color]
of the[color=blue]
> amount of time at each level (level is the last value furthest from[/color]
the[color=blue]
> left)
>
> My rudimentary SQL as come up with this...
>
> TRANSFORM Sum(tblHistory. previousminutes ) AS SumOfpreviousmi nutes
> SELECT tblHistory.dtmT ime
> FROM tblHistory
> GROUP BY tblHistory.dtmT ime
> PIVOT tblHistory.stat us;
>
> The only thing wrong with this is it lists all entries for 12/24/2005[/color]
[color=blue]
> instead of one row for 12/24....
>
> Can anyonoe suggest a corrrection
>
> Regards
>
> John Kostenbader[/color]

[timseal]

Re: Help With Some SQL

John Kostenbader wrote:[color=blue]
> I've posted in plenty of groups in the past without this[/color]
rudeness...I'm very[color=blue]
> sorry to the gentleman who thought I posted in the wrong group and I
> apologize to the gentleman who believes me an armature and still uses[/color]
"tbl"[color=blue]
> (it happens to be a table scheme I'm comfortable with and use[/color]
regularly[color=blue]
>
> I've got news for you...I am an armature looking for some assistance.[/color]
I[color=blue]
> believe that is the original intent of such newsgroups.[/color]

He was assisting you by pointing you to some documentation, and
suggesting places where you should rethink your schema. With regard to
the tbl prefix - I think that Hungarian notation has fallen out of
favour in recent years, and the reasoning seems pretty good to me.
[color=blue]
> My table looks like this:[color=green]
> >
> > 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> > 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310[/color][/color]

You would do well to learn about normalization, try googling it. With
an improved design, you will avoid many problems in the future.


TKA

[Erland Sommarskog]

Re: Help With Some SQL

John Kostenbader (john@kostenbad er.com) writes:[color=blue]
> I've posted in plenty of groups in the past without this rudeness...I'm
> very sorry to the gentleman who thought I posted in the wrong group and
> I apologize to the gentleman who believes me an armature and still uses
> "tbl" (it happens to be a table scheme I'm comfortable with and use
> regularly
>
> I've got news for you...I am an armature looking for some assistance. I
> believe that is the original intent of such newsgroups.[/color]

And my pointer to an Access newsgroup was an attempt to assist you. It
does happen that people post to this newsgroup when they should have had
posted to an Access newsgroup. This is a newsgroup for SQL Server, where
many don't know Access, and while both SQL Server and Access uses something
they both call SQL, there are considerable differences. For instance,
the Transform function is not in SQL Server.

My suggestion that should include CREATE TABLE etc, was also an attempt
to assist you. You see, if you don't tell us what you want, you can't
get it. It may seem to rude to point out that I don't like guessing what
you want. But the story is that I spend some time per day answering posts
in this newsgroups (and in some other places). If I can spend some time N
on a well-stated problem, where I can even can test a solution, or spend
the same time to try to understand what you want to achieve, guess what
is my pick.

Sure, I could have left your post unanswered, but assuming that you want
assistance, I posted my note so that you can help us to help you.

Remember, that in these newsgroups, you never get less than what you pay
for.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp