Help With Some SQL

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 SumOfpreviousminutes
SELECT tblHistory.dtmTime
FROM tblHistory
GROUP BY tblHistory.dtmTime
PIVOT tblHistory.status;

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 KostenbaderJohn Kostenbader (john@.kostenbader.com) writes:
> 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 SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> The only thing wrong with this is it lists all entries for 12/24/2005
> instead of one row for 12/24...

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.|||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@.kostenbader.com> wrote in message
news:89CdnS3X8axzVOLfRVn-2g@.rcn.net...
>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 SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> 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|||"John Kostenbader" <john@.kostenbader.com> wrote in message
news:UqadnVFCWKiDih3fRVn-ow@.rcn.net...
> 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

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.|||GROUP BY tblHistory.dtmTime

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

John Kostenbader wrote:
> 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 SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> 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|||John Kostenbader wrote:
> 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.

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.

> 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

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

TKA|||John Kostenbader (john@.kostenbader.com) writes:
> 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.

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with Query Strings

I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @.PROPERITY_ID)"><SelectParameters><asp:QueryStringParameterName="PROPERITY_ID"QueryStringField="key"Type="Int32"/></SelectParameters></asp:SqlDataSource>

Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string

Dimy as string ="'" // " ' "

key = Request.QueryString("key").trim(y.tochararray)

But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?

string

y ="'";

key = Request.QueryString[

"key"].trim(y.tochararray());

If I understand you correctly, you have 5 in database but while you select it, you get '5' correct?

SqlDataSource doesn't put any thing while it gets data. And binding itto Gridview should not be a problem

|||

I am codeing the value of my querystring in depending on a key value in another forms gridview so my code for a query string is

dim key as string 'holds the key value of the selected row in a dataview

response.redirect("Info.aspx?id=' " & key & " ' ")

when i do that, if key is 5 it places a single quote infort and behind five, '5'

So when i have my select statment in my sqldatasource and the paramater is equal to query string id it has the single quote in the select command sent to the database and that produces an erro. How can i take out the " ' " in the query sting in my aspx file ?

|||

Hi draskc03

This is my example. You can try this

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="NewsID" Width ="100%"
DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333" GridLines="None" HorizontalAlign="Center" PageSize="20">
<Columns>
<asp:TemplateField SortExpression="ImageUrl" HeaderText="Edit"><ItemTemplate>
<a href ="AddEditNews.aspx?ID=<%#Eval("NewsID") %>"> <img src ="../images/Edit.gif" border ="0"/></a>

</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ReadOnly="True" DataField="NewsID" InsertVisible="False" Visible="False" SortExpression="NewsID" HeaderText="ID"></asp:BoundField>
<asp:BoundField DataField="AddedDate" SortExpression="AddedDate" HeaderText="AddedDate"></asp:BoundField>
<asp:TemplateField SortExpression="ImageUrl" HeaderText="Title"><ItemTemplate>
<a href ="AddEditNews.aspx?ID=<%#Eval("NewsID") %>"> <%#Eval("Title") %></a>

</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Description" Visible="False" SortExpression="Description" HeaderText="Description"></asp:BoundField>
<asp:BoundField DataField="Body" Visible="False" SortExpression="Body" HeaderText="Body"></asp:BoundField>
<asp:BoundField DataField="ImageUrl" Visible="False" SortExpression="ImageUrl" HeaderText="ImageUrl"></asp:BoundField>
<asp:BoundField DataField="language" SortExpression="language" HeaderText="Language"></asp:BoundField>
<asp:CommandField ShowDeleteButton="True" DeleteText="Xóa" DeleteImageUrl="~/images/Delete.gif" ButtonType="Image" HeaderText="Xoá"></asp:CommandField>
</Columns>

</asp:GridView>

Help with query -SQL Express and ASP.net

I am having trouble with the below query. This is attached to a SQLDataAdapter which in turn is connected to a grid view.

@.pram5 is a dropdownlist
all other perameters such as @.nw in the big OR statement are check boxes.

My tables look similar to this:

Company TblComodity TblRegion

PK CompanyID PK CommodityID PK RegionID
CompanyName FK CompanyID FK CompanyID
CommodityName North
South, East, etc

What I would am trying to do is have a user slect a commodity which is a distinct value from the comodity table. Then select by tick boxes locations, then in the grid view companies with possible locations and commoditys appear. My problem is even when I select a commodity and leave all tick boxes blank (false) the records still display - like its only filltering on commodity name. Can anyone help ? I can provide more info if needed

Here is my query:

SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.NorthWest, TblRegion.NorthEast, TblRegion.SouthEast, TblRegion.SouthWest,
TblRegion.Scotland, TblRegion.Wales, TblRegion.Midlands, TblRegion.UKNational, TblRegion.EuropOotherThanUK, TblComodity.ComName
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID INNER JOIN
TblComodity ON TblCompany.CompanyID = TblComodity.CompanyID AND TblComodity.ComName = @.pram5
WHERE (TblRegion.NorthWest = @.nw) OR
(TblRegion.NorthEast = @.NE) OR
(TblRegion.SouthEast = @.se) OR
(TblRegion.SouthWest = @.sw) OR
(TblRegion.Scotland = @.scot) OR
(TblRegion.Wales = @.wal) OR
(TblRegion.Midlands = @.mid) OR
(TblRegion.EuropOotherThanUK = @.EU) AND (TblRegion.UKNational = @.UKN)More info needed: Are the ticks mapped to the appropiate parameters like @.sw ? What do you pass if the ticks are not selected ? Probably Null ? because of doing an OR, you will get all values which habe in one of the filtered columns the value null then.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de