query assistance: February 2012

Saturday, February 25, 2012

Query by year group by total students sort by total for each county

I haven't a clue how to accomplish this.
All the data is in one table. The data is stored by registration date
and includes county and number of students brokne out by grade.

Any help appreciated!

RobSomething like this, maybe:

SELECT county, YEAR(registration_date), SUM(num_students) AS tot_students
FROM Students
GROUP BY county, YEAR(registration_date)
ORDER BY county, tot_students

If you need more help please post DDL (CREATE TABLE statement) for the
table, including keys and constraints.

--
David Portas
----
Please reply only to the newsgroup
--|||the_ainbinders@.yahoo.com (Rob) wrote in message news:<14be79e4.0312010657.8573393@.posting.google.com>...
> I haven't a clue how to accomplish this.
> All the data is in one table. The data is stored by registration date
> and includes county and number of students brokne out by grade.
> Any help appreciated!
> Rob

Here's the CREATE TABLE

CREATE TABLE [dbo].[EdSales] (
[FName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOV] [datetime] NULL ,
[grade1] [numeric](10, 0) NULL ,
[grade2] [numeric](10, 0) NULL ,
[grade3] [numeric](10, 0) NULL ,
[grade4] [numeric](10, 0) NULL ,
[grade5] [numeric](10, 0) NULL ,
[grade6] [numeric](10, 0) NULL ,
[grade7] [numeric](10, 0) NULL ,
[grade8] [numeric](10, 0) NULL ,
[grade9] [numeric](10, 0) NULL ,
[grade10] [numeric](10, 0) NULL ,
[grade11] [numeric](10, 0) NULL ,
[grade12] [numeric](10, 0) NULL ,
[grade13] [numeric](10, 0) NULL ,
[grade14] [numeric](10, 0) NULL ,
[grade15] [numeric](10, 0) NULL ,
[grade16] [numeric](10, 0) NULL ,
[grade17] [numeric](10, 0) NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateEntered] [datetime] NULL ,
[result] [numeric](18, 0) NULL ,
[ConfirmNum] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[visible] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [PK_EdSales_1] PRIMARY KEY CLUSTERED
(
[ConfirmNum]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [DF_EdSales_visible] DEFAULT ('y') FOR [visible]
GO|||Your table design has some problems: Non-normalised repeating group of
Grades; No natural primary key; Too many NULLable columns (if every column
can be NULL then why would you have a row in the table anyway!). This query
would be a lot simpler if you fixed these things.

SELECT county, YEAR(dateentered) AS year_entered,
SUM(
COALESCE(grade1,0)+
COALESCE(grade2,0)+
COALESCE(grade3,0)+
COALESCE(grade4,0)+
COALESCE(grade5,0)+
COALESCE(grade6,0)+
COALESCE(grade7,0)+
COALESCE(grade8,0)+
COALESCE(grade9,0)+
COALESCE(grade10,0)+
COALESCE(grade11,0)+
COALESCE(grade12,0)+
COALESCE(grade13,0)+
COALESCE(grade14,0)+
COALESCE(grade15,0)+
COALESCE(grade16,0)+
COALESCE(grade17,0))
AS tot_students
FROM EdSales
GROUP BY county, YEAR(dateentered)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<_dGdnR8i6ZJR9VGiRVn-jg@.giganews.com>...
> Your table design has some problems: Non-normalised repeating group of
> Grades; No natural primary key; Too many NULLable columns (if every column
> can be NULL then why would you have a row in the table anyway!). This query
> would be a lot simpler if you fixed these things.
> SELECT county, YEAR(dateentered) AS year_entered,
> SUM(
> COALESCE(grade1,0)+
> COALESCE(grade2,0)+
> COALESCE(grade3,0)+
> COALESCE(grade4,0)+
> COALESCE(grade5,0)+
> COALESCE(grade6,0)+
> COALESCE(grade7,0)+
> COALESCE(grade8,0)+
> COALESCE(grade9,0)+
> COALESCE(grade10,0)+
> COALESCE(grade11,0)+
> COALESCE(grade12,0)+
> COALESCE(grade13,0)+
> COALESCE(grade14,0)+
> COALESCE(grade15,0)+
> COALESCE(grade16,0)+
> COALESCE(grade17,0))
> AS tot_students
> FROM EdSales
> GROUP BY county, YEAR(dateentered)

Well, I am a newbie at this sort of a thing. So, any pointers to
useful info on the diffiencies you mention are appreciated.

QUERY BY USER

I have a datagrid with rows that pertain to multiple users. I want to
write a query statement so that whomever the CURRENT logged-in
user is, he can go to the datagrid page and it will show ONLY rows
that pertain to him (programmatic security). To accomplish this,
I have a column in the datatable that references each user's
username. So I need a query statement that will compare the table's
'username' column to the current logged-in user. Something like...

SELECT... FROM [VendorEquipment]
WHERE [UserName] = Context.Current.User.Name

...or something like this. Question is, can someone help on how to
syntax this. Also, do I need anything special in the login form or in
web.config to 'pave the way' for this query to work.

Thx much.
Reid C.after a successfull login,you can store the username in sessions...|||Thanks for the reply...but I am a novice and will probably need more to clear up
the picture...can you suggest how I can syntax my SELECT command to accomplish
the above-stated objective and is there something I need to do via sessions to
enable this--and where would I do that?

Thx much.
Reid C.|||i believe you are checking for user authorization when the user logs in right ?

just put the username into session after the validation is successfull


session("username")=username

and frm any page
you can get it back as


dim uname as string
uname=session("username")

so when you query the db for the records you can do something like


strsql=select * From table where username='" & uname & "'"

and bind the datagrid.

HTH|||Thanks again...this sounds like what I'm looking for...just two
more questions due to my relative inexperience:

1) the statement: session("username")=username
you suggested...does that need to be in the login form
like in the "sub loginBtn_click" subroutine? and if so where in
the subroutine does it need to go...is the placement of the
statement critical?

2) is "username" interchangeable in this operation with "userid" ?
because I have written my registration form and login with a
"userid" instead of a "username"...but I will change this if it is
important to have "username" for forms authentication.

Thx.
Reid C.|||yep you can have any variable in session you want..username,userid...doesnt matter.
right after you check the userid with the password and if the query returns as a valid user, store the userid in sessions...and you should be able to access it frm any page...

HTH|||Thank you ...do I place the session stmt in global.asax?

like this, for instance:

Sub Session_OnStart()
 Session("userId")=userId
End Sub

Thx.
Reid C.|||no...you put it in ur login.aspx where ever you validate the authenticity of the user...|||o.k., thanks again, ...but I am a complete novice and have been building pages
using WebMatrix, so sorry to be dense...but do I place the "session..." statement
in the "Function GetUser..." or in my "Sub LoginBtn_click..." subroutine, or after
that and right before the </script> tag ? I know placement of this statement is
important, I just don't know where?

Thx again.
Reid C.|||if you post your code for the login page..i can tell you where to place it...|||Thanks again for all your help...here is the code for the login (minus the html) and
I've got the statement placed right before the end </script> tag...tell me if you see
a glaring case of needing to move it to a more appropriate space.


<script runat="server">
Function GetUser(ByVal userId As String, ByVal userPassword As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[UserId] = @.UserId) AND ([Users].[U"& _
 "serPassword] = @.UserPassword))"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userId.ParameterName = "@.UserId"
 dbParam_userId.Value = userId
 dbParam_userId.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userId)
 Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userPassword.ParameterName = "@.UserPassword"
 dbParam_userPassword.Value = userPassword
 dbParam_userPassword.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userPassword)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
End Function
Sub LoginBtn_Click(Sender As Object, E As EventArgs)
 If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId
</script

Thx again.
Reid C.|||i think this should work...


If Page.IsValid Then Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 'valid user...
 session("UserId")=UserId
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId

|||In your revision you have the "session..." statement right after
the "If userDS.Tables(0).Rows.Count = 1 Then" statement which
made sense as soon as I saw it. But you also have the same
"session..." statement again after the "End Sub" statement. Is
this just a typo on your part from copying and pasting what I
sent you, or does it need to be there?

I am assuming it is a copy&paste oversight...unless you answer
back that it needs to be repeated...

Thx much again for your help.
Reid C.|||See if you can review the code for my datagrid page and see where I'm going wrong.
One thing, the "UserId" I need to compare is in the datatable as "PropUserId" because
I also have a column in the table "VendorUserId" and need to reference both. So I
need to compare the session "UserId" with the table's "PropUserId" column and I've
added a statement in the "Function GetVendorEquipment..." that says the "PropUserId"
column is equivalent to "UserId"...but I am getting a blank table on the page when I
run it.


<script runat="server"
 Function GetVendorEquipment() As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 dim UserId as String
 dim PropUserId as String
 PropUserId=session("UserId")
 Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]='" & UserId & "'"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
 End Function
 Sub Page_Load(sender As Object, e As EventArgs)
 DataGrid1.DataBind()
 End Sub
</script>

Thx again for your help.
Reid C.|||ur userid seems to be a number...
so you have to use


Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]=" & PropUserId

also one other suggestion...bind your datagrid within if not ispostback...loop


Sub Page_Load(sender As Object, e As EventArgs)
 if not ispostback then
 DataGrid1.DataBind()
 end if
End Sub

HTH

Query by Time-Slices

Dear NG,
I want to query a table like this:
Customer | Money | Time
A 12 12:01
B 17 12:03
C 25 12:06
D 14 12:09
E 10 12:11
...
My target is to have the SUM of Money Transfers by TIME SLICE, eg.
12:00 - 12:05: 29
12:05 - 12:10: 39
12:10 - 12:15: 10
...
How can I query this?
Thank you very much
RudiI am not sure what format your "Time" column is stored, but perhaps this
will get you started:
SELECT SUM(SomeColumn) AS SomeColumn, 'TheTimeSlice' = CASE WHEN TheTime
BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05' WHEN TheTime BETWEEN
'12:06' AND '12:10' THEN '12:06 - 12:10' END
FROM YourTable
GROUP BY CASE WHEN TheTime BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05'
WHEN TheTime BETWEEN '12:06' AND '12:10' THEN '12:06 - 12:10' END
Keith Kratochvil
<rudolf.ball@.asfinag.at> wrote in message
news:1141651834.317186.293110@.i39g2000cwa.googlegroups.com...
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi
>|||Thank you very much,
but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
CASE?
Thank you
Rudi|||Please post table DDLs so that others can understand the datatypes, keys,
constraints etc. What is the datatype of the time column? Also time and
money are reserved words in t-SQL.
Your sample data is not very clear. Do you need rows for all 5 minute slice
irrespective of whether a transfer occured or not? If a transfer occured
exactly on 12:05 ( AM/PM ) , do you want to account for it in the 12:00 -
12:05 group or 12:05 - 12:10 group?
As a general response, for such problems consider integer division on the
expression that returns the number of minutes from the starting point. For
instance, in this case it would be: DATEDIFF( minute, 0, time_ ) / 5. You
can use this expression in your GROUP BY clause to return the resultset you
want.
Anith|||Yes you can, by relying on integer division.
CREATE TABLE Test(Customer char(1),Money int,Time smalldatetime)
INSERT INTO Test VALUES ('A',12,'12:01')
INSERT INTO Test VALUES ('B',17,'12:03')
INSERT INTO Test VALUES ('C',25,'12:06')
INSERT INTO Test VALUES ('D',14,'12:09')
INSERT INTO Test VALUES ('E',10,'12:11')
SELECT CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:00'),108)
, CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:05'),108)
, SUM("Money")
FROM Test
GROUP BY DATEDIFF(minute,'12:00',"Time")/5
DROP TABLE Test
I guess you have something to study now :-)
Gert-Jan
rudolf.ball@.asfinag.at wrote:
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi|||With SQL 2005, you could create a mapping from time to some integer domain
(say, convert to minutes in the day and divide by 5), then use the AVG(col)
OVER (PARTITION BY integerslice) functionality that was added. This avoids
having to write all of the slices out in a big case statement.
good luck.
Conor
<rudolf.ball@.asfinag.at> wrote in message
news:1141657033.464616.225810@.j33g2000cwa.googlegroups.com...
> Thank you very much,
> but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
> CASE?
> Thank you
> Rudi
>

query by time

I am trying to query a db by date and time ie I want records from 9/2/2007 to 9/22/2007 (selectable @.startdate and @.enddate) also i want to be able to sort by time ie shift (1st 7:00am to 3:00pm 2nd 3:00 pm to 11:00 pm 3rd 11:00pm to 7:00am) i can query for the date but not sure how to by time. my db data is coming in 9/13/2007 10:35:18 AM

Thanks

Quote:

Originally Posted by esadler

Thanks

consider the BETWEEN operator (is it an operator or clause or pharse :D )...

search the online help

query by recent dates

How would I construct a SQL statement that would query the database and return the 5 most recent dates added into the database?

Would I use the TOP SQL keyword to select the 5 most recent entries?

How would I query for the most recent dates?

SELECT TOP 5 *
FROM dbo.tblWeblog
WHERE blogDate = ?

Thanks for any help!
-Dman100-Close! I'd use:SELECT TOP 5 *
FROM dbo.tblWeblog
ORDER BY blogDate DESC-PatP|||Thanks Pat!
-Dman100-

Query by quarter

Hello, everyone:
There is a table named INCOME that has INCOME column for each day and DATE column starting from Aug. 29 1980. How to calculate income summary by each quarter? Thanks.
ZYTDefine quarter. Are you a calendar year quarter, fiscal 4434 or 4344? When does your year begin and end?

After you find that out,

SELECT
SUM(CASE WHEN date BETWEEN @.q1begin AND @.q1end THEN income ELSE 0 END) AS q1,
SUM(CASE WHEN date BETWEEN @.q2begin AND @.q2end THEN income ELSE 0 END) AS q2,
SUM(CASE WHEN date BETWEEN @.q3begin AND @.q3end THEN income ELSE 0 END) AS q3,
SUM(CASE WHEN date BETWEEN @.q4begin AND @.q4end THEN income ELSE 0 END) AS q4
FROM
that_table|||Maybe something like this, though I haven't tested the syntax:

select cast(Month([IncomeDate])/4 as int) as Quarter|||The following dates are UK-Defined Quarter Day
25 March (Lady Day),
24 June (Midsummer Day),
29 September (Michaelmas), and 25 December (Christmas Day).

Try this stored procedure

Create Proc usp_QuarterIncome
As
Set Nocount On
Create Table #Quarter_Income(Id int identity(1,1),Quarters datetime,QuarterIncome money)

insert into #Quarter_Income (Quarters)
select distinct datename(yy,date)+'/'+convert(varchar(2),datepart(mm,date))+'/'+
case datepart(mm,date) when 3 then '25' when 6 then '24' when 9 then '29' when 12 then '25' end as Quarter
from income where datepart(mm,date) in (3,6,9,12)

declare @.id int,
@.StartDate Datetime,
@.Start DateTime,
@.EndDate Datetime,
@.Sum money
Select @.Start = Min(Date) From Income

set @.id = 0
while @.Id < (select max(id) from #quarter_Income)
begin
Select @.StartDate= Quarters From #quarter_Income where Id = @.Id
Select @.EndDate = Quarters From #quarter_Income where Id = @.Id+1
Select @.Sum = Sum(Income) From Income where [Date] >= isnull(@.StartDate,@.Start) and [Date] < @.EndDate
Update #quarter_Income Set QuarterIncome = @.Sum where Id = @.Id+1
Set @.Id = @.id + 1
End
Select Quarters,QuarterIncome from #quarter_Income
Set Nocount Off

--Usage:
--Exec usp_QuarterIncome|||What is the problem with

select sum(income),datepart(qq,date),year(date) from income group by datepart(qq,date),year(date)|||Humph! Sure, any shmuck can use the fancy-pants "built-in functions". But where is the challenge in that? If you are lucky, tomorrow I will post my innovative cursor-based dynamic SQL function for removing leading spaces from strings.|||You will need to write another one for the trailing spaces ... I will need that too ...|||you guy's are geniuses!!!|||What is the problem with

select sum(income),datepart(qq,date),year(date) from income group by datepart(qq,date),year(date)
Man, I like the way that you think!

-PatP|||Humph! Sure, any shmuck can use the fancy-pants "built-in functions". But where is the challenge in that? If you are lucky, tomorrow I will post my innovative cursor-based dynamic SQL function for removing leading spaces from strings.

I almost fell outta my chair... I could've hurt myself! Hysterical laughter at work implies insanity... I hope no one noticed...

Query by length of varchar column

Hi mates,

I have a Table:Test with column text:varchar(255). I want get rows where text length to be longer than 100. Is it possible?

Thx in advance,

Look at LEN and DATALENGTH functions in BOL.

select *

from TableName

where datalength(colName) > 100

|||Thx very much!

query by grouping

Hi,
I have a table with data that every 2 rows have same data for most of
cloumns (11), only 4 columns have different data. I would like to query this
table so that such rows appear as single row in the result ( I don't need th
e
4 cloumns), so I used that 11 columns in my group by clause.
A large group by will be perforance problem?
ThanksGroup by and Distinct do the same thing (well, not really - ones for
aggrigating - but anyway), and using distinct will make your code a little
cleaner, rather than having a group by statement with 11 columns in it, you
can just use the word distinct to return all rows without duplicates.
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query
this
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||If you don't need the 4 columns that have different values, just leave them
out of the query, and add the word Distinct immediatey after the Select
Select Distinct ....
That will do the trick...
"Jen" wrote:

> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query th
is
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||Hi,
How about when i use the word SUM, do i have to put all the columns in the
group by?
Thanks,
Bruno N
"Jen" <Jen@.discussions.microsoft.com> escreveu na mensagem
news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query
this
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||If you are aggrigating, yes, you would use group by instead of distinct.
Simon Worth
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eB$VmrOJFHA.3596@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How about when i use the word SUM, do i have to put all the columns in the
> group by?
> Thanks,
> Bruno N
> "Jen" <Jen@.discussions.microsoft.com> escreveu na mensagem
> news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> this
need
> the
>|||Thanks, I do use min() to get the amount since some rows have null value, so
I need to use group, is there performance issue? Is it a good way or I need
to get all the rows back and let client to massage the data?
"Simon Worth" wrote:

> If you are aggrigating, yes, you would use group by instead of distinct.
> --
> Simon Worth
>
> "Bruno N" <nylren@.hotmail.com> wrote in message
> news:eB$VmrOJFHA.3596@.TK2MSFTNGP14.phx.gbl...
> need
>
>|||Group by limits the amount of records returned to your client, so it is a
good thing in that regard. Less traffic on the highway so to speak.
There's no need for the client application to loop through the records to
find the minimum amount for each group of records - the functionality is
built into SQL to accommodate aggregating data and should be utilized as
such.
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:F65899CA-80FD-45F8-8401-92CAFECA272E@.microsoft.com...
> Thanks, I do use min() to get the amount since some rows have null value,
so
> I need to use group, is there performance issue? Is it a good way or I
need
> to get all the rows back and let client to massage the data?
> "Simon Worth" wrote:
>
the
of
query|||Jen,
From yr orig post
<<< I have a table with data that every 2 rows have same data for most of
cloumns (11), only 4 columns have different data. I would like to query this
table so that such rows appear as single row in the result ( I don't need th
e
4 cloumns),
Is the column you need the minimum from one of the 11, or one of the 4?
If it's one of the 11, then you can't group by the 11, you'll need t ogroup
by the remaining 10... The one you're aggregating on cannot be in the Group
By.
If it's one of the 4, then Iguess you DO need (at least one) of the 4, hmmm
?
"Jen" wrote:
> Thanks, I do use min() to get the amount since some rows have null value,
so
> I need to use group, is there performance issue? Is it a good way or I nee
d
> to get all the rows back and let client to massage the data?
> "Simon Worth" wrote:
>|||Yes, the minimum column if from one of 4 columns.
"CBretana" wrote:
> Jen,
> From yr orig post
> <<< I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query th
is
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns),
> Is the column you need the minimum from one of the 11, or one of the 4?
> If it's one of the 11, then you can't group by the 11, you'll need t ogrou
p
> by the remaining 10... The one you're aggregating on cannot be in the Grou
p
> By.
> If it's one of the 4, then Iguess you DO need (at least one) of the 4, hm
mm?
> "Jen" wrote:
>

Query by Example in Enterprise Manager

Why hasn't Microsoft adopted the same level of excellent QBE in Enterprise Manager that is included with MS Access for building queries? I am just not a die hard fan of using Query Analyzer and templates for building complex queries. I can understand using QA for performance tuning, etc. but for queries like UPDATE queries using RIGHT OUTER JOINS that have many attribute references, I miss my QBE in Access.

When I did my Masters, I wrote a research paper on Query By Example developed in the 70's by Dr. Mosh Zloof of IBM and there are some really good points about it.

1.) Visually seeing the relationships and joins.

2.) Elimination of typo errors.

3.) Eliminates reference ambiguity.

I don't mind keying in a complex query, but a nice visual tool with the relationships/joined mapped out and the elimination of reference ambituity would make me feel a little better before I hit the Execute Query button. Sometimes the Parse Query button does not exactly give me that warm fuzzy feeling but it helps.

Does anyone know if MS ever plans to adopt the level of QBE provided in MS Access or are there any third party tools available in comparison?

I'm not asking for help with keying in queries through Query Analyzer or any arguments about why that is so much greater. I'm just looking for QBE improvements in SQL server's Enterprise Manager.I understand what you are saying about visualizing the query and seeing the relationship. You are right in saying that MS Access does a good job of that, however, as a DBMS, Access just doesn't cut it. SQL Server is great.

If you are looking to visualize in SQL Server, there is a diagram tool under the database that you are working with so you can build relationships... you can also perform a visual query by right clicking on a table and selecting query from the popup menu. From there you can add tables and make relationships all by pointing and clicking... good thing to do if you aren't too sure about sql... it looks just like access's version and runs almost the same way.

But hey, look at it this way... in Access you are not in control... you simply control where you point and click and Access does the rest. In Query Analyzer, you are more than just a user ... you have flexibility and at the same time you are becoming more and more competant and confident in writing SQL and T-SQL... Remember, there is hardly anything that can't be fixed. So don't be afraid to click that button!!!!! Keep going at it and you'll be a SQL pro in no time!!!!!!|||Believe me, I've been working with SQL Server for 2 1/2 years but I still miss the QBE capability of Grid Analyzer in MS Access. For example, in SQL's Query Analyzer you can't add a joined table for an UPDATE query but you can for a SELECT. What's up with that? Seems pretty basic to me.|||Personally, I think it's funny that everyone balks at using QBE in EM for query building. I found that in Access, my percentage of reference ambiguity using Grid Manager was zero. However, when I use QA and templates, it's probably 5 to 25% and then I have to make corrections. To me, it's like comparing BASIC to VISUAL BASIC. Visual tools are awesome! Why people think it's such an insult to use them is beyond me. Just my two cents.

Query by database or code

I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?

Thanks

I'm not exactly sure what you are asking. Are you asking whether or not to do your database access from the ASPX page or the code-behind? Or, are you asking if you should parameterize your queries?

|||

Im using ASP.Net to create a website and heres and example of how i retrieve the data:

Dim strCommandText as String= "SELECT * FROM Player INNER JOIN Manufacturer ON Player.PlayerManufacturerID =
Manufacturer.ManufacturerID ORDER BY Player.PlayerName"
Dim myCommand As SqlCommand = new SqlCommand(strCommandText, myConnection)

Now above i write the SQL syntax into my application to retrieve data. If i have a query created within the DATABASE called ParameterName then i can write the code as:

MyCommand.Paramters.Add(ParameterName)

So my question is really asking which method is approved and why as theyre both doing the same thing?

Thanks

|||

If I still misunderstood your question, then my apologies, but I think what you're asking is if your Select statement requires parameters, then should I inject them directly into my statement, or use a parameter, and add the parameters afterwards. If this is what you're truly asking, then I'd definetely suggest the use of parameters as they create a much less error-proned design. Here's an article on the subject:

http://www.4guysfromrolla.com/webtech/092601-1.shtml

|||

The "common" best practice approach would be to have a separate class library that will return the "Players" via a stored procedure. I would also recommend not using SELECT *, you should only select the fields you need.

|||

If I still misunderstood your question, then my apologies, but I think what you're asking is if your Select statement requires parameters, then should I inject them directly into my statement, or use a parameter, and add the parameters afterwards. If this is what you're truly asking, then I'd definetely suggest the use of parameters as they create a much less error-proned design. Here's an article on the subject:
http://www.4guysfromrolla.com/webtech/092601-1.shtml

Not quite. Im just trying to see if i should use a Select query in my code or should i call a~~parameter~~ Stored Procedure from the database to retrieve the data. Sorry my fault i was using the wrong terminology, i should have said Stored Procedure but got my wires crossed as ASP .Net terms this as a parameter being passed in to the code your writing

jguadagno:
The "common" best practice approach would be to have a separate class library that will return the "Players" via a stored procedure. I would also recommend not using SELECT *, you should only select the fields you need.

I think this may have answered the question. Where can i get started with Class libraries? also thanks for advising not to use SELECT * - i only used that in this question as an example.

Thanks guys

|||

EssCee:
I think this may have answered the question. Where can i get started with Class libraries?

http://www.15seconds.com/issue/050721.htm

Query building based on user selections

I currently have a form that has different options in a list box such as:

Status Codes

Division Codes

Project Managers

Report Types

The user can select a value from any one of these and hit a search button to run a query. Currently I am running through some IF statements to evaluate which combination of values were selected to run the right SQL code. Example

A user wants to view reports that have a status code of "Active" and a division code of "001". So my if statement would be something like (not in perfect syntax, just trying to get the point across):

if strStats <>"" and strDivision <>"" and strProjMgr ="" and strRptType ="" thenselect ...from ...where status_cd = strStats and division_cd = strDivision

end if

So my question is this: Is there a way to build the query dynamically (1 time) without having to run through all the senarios?

Yes. you could write a query as:

select ...
from ...
where status_cd = ISNULL(@.strStats,status_cd)

and division_cd = ISNULL(@.strDivision,division_cd )

and ...

pass the values for @.strStats and @.strDivision. You can pass the parameters in any combination.

|||So this basically allows a condition to be null or a value can be passed?|||Yes.

query builder: how to do a LIKE parameter

hi,

i can do

"select * from products where name = @.name" kind of statements in query builder

but

"select * from products where name LIKE @.name" dosen't work!"

any ideas? i'm using sql server express.

thanks

That statement should look like this:

"select *from productswhere nameLIKE'%' + @.name +'%'"

|||

thanks!! it worked.

just wondering, if i have been using query builder and sql statements to construct tableadapters, would the application be vulnerable to sql injection attacks like classic asp?

|||Yes. Refer toHow To: Protect From SQL Injection in ASP.NET for details.

Query Builder with Oracle?

Is the Query Builder ( not the generic query tool), available for Oracle data
sources..I thought I remembered that it was only available for SQL, but I
can't find any documentation, (and don't have any Oracle access.)
THanks
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.I don't think that should be an issue. With RS 2000 the problem was that the
query builder used OLEDB while the generic query window and runtime used
dotnet provider. This was because VS 2003 did not support dotnet Oracle
provider in the graphical query builder. So it worked but you had to set
things up appropriately for it to do so. I don't know if this has changed
with RS 2005 or not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:A3860E58-9FF8-4EBA-BD32-D1776B93FF3A@.microsoft.com...
> Is the Query Builder ( not the generic query tool), available for Oracle
> data
> sources..I thought I remembered that it was only available for SQL, but I
> can't find any documentation, (and don't have any Oracle access.)
> THanks
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.

Query Builder Tool

Hello -- does anyone know of any good tools to create queries for SQL
Server? I'm currently using Query Analzyer, but would like something a bit
more QUI oriented similar to what Access has -- where I can insert tables
and drag columns, etc.
Any help would be appreciated.
Hello DavidM.
You can use Enterprise Manager for an interface very similar to Access.
Click the tables node to get a listing of all the tables in the right pane.
Select a table and right click. Select open table from the pop up menu.
Select Query. You should now see an interface like Access that you can use to
design queries.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"DavidM" wrote:

> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a bit
> more QUI oriented similar to what Access has -- where I can insert tables
> and drag columns, etc.
> Any help would be appreciated.
>
>
|||Probably the best (and easiest) one I've seen is Crystal Reports.
Regards,
Hank Arnold
"DavidM" <spam@.spam.net> wrote in message
news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a
> bit more QUI oriented similar to what Access has -- where I can insert
> tables and drag columns, etc.
> Any help would be appreciated.
>
>
|||This one seems nice --
http://www.sqlmanager.net/products/mssql/query
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:351eeqF4fnrjtU1@.individual.net...
> Probably the best (and easiest) one I've seen is Crystal Reports.
> --
> Regards,
> Hank Arnold
> "DavidM" <spam@.spam.net> wrote in message
> news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
>

Query Builder Tool

Hello -- does anyone know of any good tools to create queries for SQL
Server? I'm currently using Query Analzyer, but would like something a bit
more QUI oriented similar to what Access has -- where I can insert tables
and drag columns, etc.
Any help would be appreciated.Hello DavidM.
You can use Enterprise Manager for an interface very similar to Access.
Click the tables node to get a listing of all the tables in the right pane.
Select a table and right click. Select open table from the pop up menu.
Select Query. You should now see an interface like Access that you can use t
o
design queries.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"DavidM" wrote:

> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a bi
t
> more QUI oriented similar to what Access has -- where I can insert tables
> and drag columns, etc.
> Any help would be appreciated.
>
>|||Probably the best (and easiest) one I've seen is Crystal Reports.
Regards,
Hank Arnold
"DavidM" <spam@.spam.net> wrote in message
news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a
> bit more QUI oriented similar to what Access has -- where I can insert
> tables and drag columns, etc.
> Any help would be appreciated.
>
>|||This one seems nice --
http://www.sqlmanager.net/products/mssql/query
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:351eeqF4fnrjtU1@.individual.net...
> Probably the best (and easiest) one I've seen is Crystal Reports.
> --
> Regards,
> Hank Arnold
> "DavidM" <spam@.spam.net> wrote in message
> news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
>

Query Builder Tool

Hello -- does anyone know of any good tools to create queries for SQL
Server? I'm currently using Query Analzyer, but would like something a bit
more QUI oriented similar to what Access has -- where I can insert tables
and drag columns, etc.
Any help would be appreciated.Hello DavidM.
You can use Enterprise Manager for an interface very similar to Access.
Click the tables node to get a listing of all the tables in the right pane.
Select a table and right click. Select open table from the pop up menu.
Select Query. You should now see an interface like Access that you can use to
design queries.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"DavidM" wrote:
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a bit
> more QUI oriented similar to what Access has -- where I can insert tables
> and drag columns, etc.
> Any help would be appreciated.
>
>|||Probably the best (and easiest) one I've seen is Crystal Reports.
--
Regards,
Hank Arnold
"DavidM" <spam@.spam.net> wrote in message
news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello -- does anyone know of any good tools to create queries for SQL
> Server? I'm currently using Query Analzyer, but would like something a
> bit more QUI oriented similar to what Access has -- where I can insert
> tables and drag columns, etc.
> Any help would be appreciated.
>
>|||This one seems nice --
http://www.sqlmanager.net/products/mssql/query
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:351eeqF4fnrjtU1@.individual.net...
> Probably the best (and easiest) one I've seen is Crystal Reports.
> --
> Regards,
> Hank Arnold
> "DavidM" <spam@.spam.net> wrote in message
> news:OoefkbD$EHA.3908@.TK2MSFTNGP12.phx.gbl...
>> Hello -- does anyone know of any good tools to create queries for SQL
>> Server? I'm currently using Query Analzyer, but would like something a
>> bit more QUI oriented similar to what Access has -- where I can insert
>> tables and drag columns, etc.
>> Any help would be appreciated.
>>
>

Query Builder Tool

Query builder bug?

HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.

When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.

Am I missing something?

Thank you for your help,
Cctoe

Please report the bug on the Product Feedback site.

Thanks.

Query builder bug?

When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.

Am I missing something?

Thank you for your help,
Cctoe

Please report the bug on the Product Feedback site.

Thanks.

query builder

hi friends
i've one question is it possible to use SMO to replicate "Query Designer" programmatically?
i want to write a query builder for end users and am wondering which objects in SMO i need to use to achieve what i want.
any ideas on this one much appreciated.
Thank you very much for your help.You can use SMO for part of the problem, but not all. You can use SMO to obtain a listing of tables, columns, and views and various properties (e.g. keys, datatypes, and etc). But this is no way in SMO to provide it some tables and conditional statements to generate a query.

Peter|||Yes Peter ,i was able to gather table,columns info from SMO but did not see any class inside this namespace to generate query .
do you have any other ideas/advise in writing a sql engine?
Thanks for your help.|||Sorry, I don't have any other suggestions.

Peter|||SMO will be helpful for you in obtaining metadata of the objects (tables, colums, foreign keys etc.) which will help you building the queries. However the query building portion is something you need to build yourself. SMO will not help you in any way.|||

Thanks for the reply Michiel.

finally we ended up using Report builder that ships with sql reporting services.

query builder

Thanks for the reply Michiel.

finally we ended up using Report builder that ships with sql reporting services.

Query Brain Teaser - Revenue Projections

I have a requirement (motivated by a SOX thing) that is just giving me
fits. I know it should be easy and I'm probably overthinking it, but I
just can seem to find the best way to get where I need to go.

I have some payment projection data derived from a huge procedure that
I'm dumping into a temp table that looks like looks this:

Key Pd Start End AnnualAmt MonthAmt DailyAmt
6789 1 2005-06-01 2010-05-31 49,500.00 4,125.00 135.616438
6789 2 2010-06-01 2015-05-31 54,450.00 4,537.50 149.178082
6789 3 2015-06-01 2020-05-31 59,895.00 4,991.25 164.095890
6789 4 2020-06-01 2024-05-31 65,884.50 5,490.38 180.505479

(there are actually 6 levels of keys, but you get the idea)

I need it to get into a reporting table looking like this:

Key Rev Year ProjectedAmt
6789 2005 29,021.92
6789 2006 49,500.00
6789 2007 49,500.00
6789 2008 49,500.00
6789 2009 49,500.00
6789 2010 20,478.08

6789 2010 31,924.11
6789 2011 54,450.00
6789 2012 54,450.00
6789 2013 54,450.00
6789 2014 54,450.00
6789 2015 22,525.88

6789 2015 35,117.40
6789 2016 59,895.00
6789 2017 59,895.00
6789 2018 59,895.00
6789 2019 59,895.00
6789 2020 24.779.10

etc...

I'm having a problem wrapping my head around how to get the rows in the
middle of each period.

The other, probably minor and statistically insignificant, issue is
proration on a leap year. If a proration occurs on a leap year and I
have to calculate the proration based on a DATEDIFF and an Annual or
Monthly Amount, I'm going to be a day over.

Anybody have any tricks or ideas?

Thanks so much for your help!

JodyFirst, are your interest rules based upon Annual rates, monthly rates,
ro daily rates. You need to know this in order to figure out what is
wrong with your current data. Currently, either your annual or your
daily data is wrong for leap year.

build a table that contains the start of each revenue year.

so, the entry would be
2005 1/1/2005
2006 1/1/2006
2007 1/1/2007
etc.

I will get lamblasted for this, but I would use a, gasp, cursor. Scroll
through your years table calculating the payments for each year, for a
given key/period..
To do this, Using Datediff, calculate the number of days between the
beginning of the year and the end of the year. For the beginning and
end, you will do calcs either on daily or percentages of the annual.

Someone could probably figure out a set based solution to this.
Personally, my brain isn't big enough. There is not that much data, and
a cursor is easier to think about on this one.

Outside of this inner cursor, have another cursor scrolling through
each of the key/periods.
So, two nested cursors. First one is Key/period. Inner is the years.
Make sense?|||Doug:

Thanks for the reply. There's nothing wrong with the data (at least
not up to this point in the process) and I'm not calculating interest,
just the prorated Annual Amount by year.

The Annual Amount is the base, is never adjusted up for a leap year and
could make the full years easy if it makes sense to use it.

MonthlyAmt is Annual/12 and isn't really worth much in this context.

DailyAmt is just Annual/365 (366 in a leap year) and is necessary for
partial month proration. I suppose I could create a date table for the
next 25 years with no 2/29's in it, and do my DATEDIFFs against that...

I was hoping to avoid the cursor thing...

Jody|||I got it! (and it doesn't run too bad...)

SELECTpt.Key,
pt.Period,
y.Year AS RevYear,
CASE WHEN Year = DATEPART(YEAR,Start) THEN

DailyAmt*DATEDIFF(DAY,Start,DATEADD(MS,-3,DATEADD(YY,DATEDIFF(YY,0,Start
)+1,0)+1))
WHEN Year = DATEPART(YEAR,End) THEN
DailyAmt*DATEDIFF(DAY,DATEADD(YY, DATEDIFF(YY,0,End),0)-1,End)
ELSE DailyAmt*365
END as ProratedAnnualRent
INTO Report
FROM#ProjectionTemp pt
JOIN (SELECTDISTINCT Year
FROMdim_fiscal_day) y
ON y.Year >= DATEPART(YEAR,pt.Start)
AND y.Year <= DATEPART(YEAR,pt.End)

Query blues... Help!

I have a security app that is like this: a Facility has many Apps and those
Apps may have any combination Modules, Roles, or Privileges.
Problem Query: Put together a query(s) that will delete all records related
to a single Facility.
CREATE TABLE Applications
(Appl_ID int IDENTITY,
FacilityID char(4) NOT NULL,
ApplName char(200) NOT NULL,
CONSTRAINT PK_Applications PRIMARY KEY CLUSTERED (Appl_ID))
go
CREATE TABLE ModulesRolesPrivileges
(MRP_ID int IDENTITY,
MRP_Name char(150) NOT NULL,
MRP_Type int NOT NULL, -- 1=Role 2=Module 3=Privilege
CONSTRAINT PK_ModulesRolesPrivileges PRIMARY KEY CLUSTERED (MRP_ID))
go
CREATE TABLE ApplicationsModulesRolesPrivileges -- Intersection table
between Applications and ModulesRolesPrivileges
(AMRP_ID int IDENTITY,
Appl_ID int NOT NULL,
MRP_ID int NOT NULL,
CONSTRAINT PK_ApplicationsModulesRolesPrivileges PRIMARY KEY CLUSTERED
(AMRP_ID),
CONSTRAINT R_1 FOREIGN KEY (Appl_ID) REFERENCES Applications (Appl_ID),
CONSTRAINT R_2 FOREIGN KEY (MRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
go
CREATE TABLE ModulesRolesPrivilegesHierarchy
(MRPH_ID int IDENTITY,
Parent_AMRP_ID int NOT NULL,
Child_AMRP_ID int NOT NULL,
CONSTRAINT PK_ModulesRolesPrivilegesHierarchy PRIMARY KEY CLUSTERED (MRPH_ID),
CONSTRAINT R_3 FOREIGN KEY (Parent_AMRP_ID) REFERENCES
ModulesRolesPrivileges (MRP_ID),
CONSTRAINT R_4 FOREIGN KEY (Child_AMRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Host
Financials') -- 1
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'PeopleSoft')
-- 2
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Acct Recv')
-- 3
INSERT Applications (FacilityID, ApplName) VALUES ('0914', 'Acct Recv')
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Budget', 2)
-- 1
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('GL', 2)
-- 2
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 3
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Admin', 1)
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Query', 3)
-- 5
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Update', 3)
-- 6
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 7
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 8
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 9
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 1) -- 1
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 2) -- 2
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (2, 3) -- 3
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (4, 8) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (1, 3) -- 1
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 5) -- 2
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 6) -- 3
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 7) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 4) -- 5
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (4, 5) -- 6
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 3) -- 7
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (8, 9) -- 8
Is Stored Procedure out of the question? If not, you can do the following.
1) SP will have one argument for FacilityID.
2)
Delete *
From ModulesRolesPrivilegesHierarchy
Where Parent_AMRP_ID IN (SELECT AMRP_ID
FROM ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID))
3)
Delete *
From ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID)
4)
Delete *
From Applications
WHERE FacilityID = @.FacilityID
That should do the trick.
Hope that helps.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Please ignore my answer, I misread table statements. So my answer is
incorrect.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Mohit K. Gupta" wrote:

> Is Stored Procedure out of the question? If not, you can do the following.
> 1) SP will have one argument for FacilityID.
> 2)
> Delete *
> From ModulesRolesPrivilegesHierarchy
> Where Parent_AMRP_ID IN (SELECT AMRP_ID
> FROM ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID))
> 3)
> Delete *
> From ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID)
> 4)
> Delete *
> From Applications
> WHERE FacilityID = @.FacilityID
> That should do the trick.
> Hope that helps.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?
>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
--
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
--
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

query between two tables

Hi,
I create a xml template with a sql query that select data from two tables.
I need to create a xsl file to view the results. Do you know some link for
understanding this? Sample?
Thank's
andrea
"Andrea Racca" <nsi@.libero.it> wrote in message
news:eblAwRhHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I create a xml template with a sql query that select data from two tables.
> I need to create a xsl file to view the results. Do you know some link for
> understanding this? Sample?
Here is an example that shows how to do this:
http://sqlxml.org/faqs.aspx?faq=99
Bryant
|||Thank's, but I like to put results in a html table.. any other help?
Andrea
"Bryant Likes" <bryant@.suespammers.org> ha scritto nel messaggio
news:OTTNkYkHEHA.320@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> "Andrea Racca" <nsi@.libero.it> wrote in message
> news:eblAwRhHEHA.548@.TK2MSFTNGP09.phx.gbl...
tables.[vbcol=seagreen]
for
> Here is an example that shows how to do this:
> http://sqlxml.org/faqs.aspx?faq=99
> --
> Bryant
>

Query between Times

i have two DateTime fields One called 'Open' and one 'Closed' . I need to
know if a given time is inbetween the Open and Closed times ?Here's an example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
if @.dt >= @.OpenDate and @.dt <= @.CloseDate
print 'yep'
else
print 'nope'
Bryce|||Woops, I just noticed the word "QUERY" in your post. Here's another
example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
select case when @.dt >= @.OpenDate and @.dt <= @.CloseDate then 1 else 0
end
Of course, when selecting from a table, the variables can be replaced
with column names.
Bryce|||Assuming Open and Cl;osed values are in Variables @.Open and @.Closed,
If this is being done in a Select Statement, then put a Predicate expression
"GivenTimeColName Between Open And Closed"
in the Where Clause.
Select <col List>
From <Table(S)>
Where <GivenTimeColName > Between Open And Closed
If it's being done in T-SQL Code, just put the predicate after an "If "
If @.GivenTimeVariable Between @.Open And @.Closed
Begin
<T-SQL Statements to execute if true
End
Else
Begin
<T-SQL Statements t oexecute if false>
End
"Peter Newman" wrote:

> i have two DateTime fields One called 'Open' and one 'Closed' . I need to
> know if a given time is inbetween the Open and Closed times ?
>|||Use the BETWEEN keyword, as in:
select blablabla
from blablabla
where a_given_time between Open and Closed
Does this help?
Raj|||> set @.dt = '1/5/2005'
Ugh. Is that January 5th or May 1st? Who knows? It depends on several
factors, including regional settings on the machine, SET LANGUAGE, SET
DATEFORMAT settings, etc.
If you're going to hard code dates in the query like that, at least use a
non-ambiguous format.
SET @.dt = '20050105'

> set @.CloseDate = '1/30/2005'
> if @.dt >= @.OpenDate and @.dt <= @.CloseDate
Ugh again. You are including rows from midnight on the close date, but
nothing else. So if something happened at 12:01, it's ignored by your where
clause. I'm not sure if the original poster wanted to include that day or
use it as a cutoff. It is better to use:
IF @.dt >= '20050105' AND @.dt < '20050130'
or
IF @.dt >= '20050105' AND @.dt < '20050131'
(Depending on whether the data from throughout the day on 20050130 should be
included or not.)|||> Select <col List>
> From <Table(S)>
> Where <GivenTimeColName > Between Open And Closed
FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
queries (though it is great for INTs, I will agree). Except in the rare
case where only whole dates (with a midnight timestamp) are stored, it is
always better to use >= start_of_range and < (end_of_range + 1).
http://www.aspfaq.com/2280|||Thanks, Aaron. Very informative.
Bryce|||Aaron,
Is your main rationale for feeling this way becuase of Betweens
inflexible "Inclusive" behavior, which always also returns records which are
equal to the enddate value?
This IS a problem I have not found a clean answer for...
I still like Between, however, because it "reads" much moore naturally, (I
mean closer to the problem you are trying to solve.. It's not a big
difference, I grant you, but when you have constructions like:
Where My_DateCOlumnValue >= @.BeginDateRangeValue And My_DateCOlumnValue <
@.EndDateRangeValue
You have to read the token for the My_DateCOlumnValue twice, in both places,
to ensure that it is indeed the same token... this issue is more significant
when it's NOT a token or column name, but a longer expression based on one o
r
more columns, (Not desired, but sometimes necessary)
In those cases, the Between construction "reads" easier to me than the >= ..
... And ... < ... since the complex expression would only be there once...
But you still have to solve the Inclusive Issue...
on another note, I believe that the Query Processor converts the between
syntax into >= AND <= to execute it anyway ...
"Aaron [SQL Server MVP]" wrote:

> FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
> queries (though it is great for INTs, I will agree). Except in the rare
> case where only whole dates (with a midnight timestamp) are stored, it is
> always better to use >= start_of_range and < (end_of_range + 1).
> http://www.aspfaq.com/2280
>
>|||Aaron,
The only point you made worth speaking to is
<<<
Then you will include rows from 20040105 at exactly midnight, but nothing
later. This trips *many* people up, because they assume any row from
20040105 (say, 20040105 15:43:04.872) should be included.
And there are several solutions ot this, some of which I mentioned already.
You're right, this trips up a lot of folks. But I guess your approach is
everybody else (except for you of course) is too stupid to understand this
and therefore should just NOT use such potentially dangerous tools.
There are unfortunately, quite too many people who think that way in this
world.
I prefer to assume, (until proven otherwise) that anyone is capable of
undersstanding the dangers (and the power) of any tool enough to learn to us
e
it intelligently and wisely, at least if someone is kind enough to teach the
m
how, and offer them the knowledge and the opportunity.
So, t oanswer yr final thought, NO, I will not stop attempting to "drown
out" anyone who attempts to limit or restrict what information orr technique
s
are made available, especially whne done because in someone's opinion, some
technique is "too dangerous" for the average person to use...
"Aaron [SQL Server MVP]" wrote:
> places,
> How complex do you name your columns? This wouldn't be an issue if you us
e
> sensible names and avoid nedlessly long and complicated identifiers. And
> unless you are dealing solely with DATETIME columns throughout your entire
> environment, it's something you have to approach from a broader perspectiv
e
> than simply whether to use BETWEEN or separate clauses.
>
> or
> If you are performing calculations against dates on the left side of the
> equation, it is always possible to do tose same calculations on the right
> side of the equation (not only avoiding your complaint, but also giving th
e
> query a better chance to use an index). For example:
> WHERE DATEADD(MONTH, -2, DateTimeColumn) + 1 < '20050101'
> is the same as
> WHERE DateTimeColumn < DATEADD(MONTH, 2, '20050101') - 1
> Comparing these this way is actually more logical, in my opinion, and stil
l
> allows you to use an index and avoid scanning the left side for expression
s
> involving the column name.
>
> Yes, and that's WHY it's a problem. If you have:
> BETWEEN '20040101' AND '20040105'
> Then you will include rows from 20040105 at exactly midnight, but nothing
> later. This trips *many* people up, because they assume any row from
> 20040105 (say, 20040105 15:43:04.872) should be included. If you have the
> true boundary on the right side, you would have to use
> '2004-01-04T23:59:59.997' and pray it doesn't get implicitly converted to
> SMALLDATETIME (which rounds up, yielding the exact same problem).
> But go ahead, keep using BETWEEN. Doesn't sound like the points I'm raisi
ng
> are very important to you anyway (but please don't try to drown them out f
or
> those people who might consider them worthwhile).
> A
>
>

query between 2 dates error

Having this table with sales
select * from viewcomis2 where date between '05/27/2005' and '05/30/2005'
I got this weird result:
05/27/2005
04/28/2005 <-- this is not suppose to be here
05/28/2005
05/30/2005
I solved:
((substring(datee,1,2)) >= (substring('" & DTPicker1.Value & "',1,2)) and
(substring(datee,4,2)) >= (substring('" & DTPicker1.Value & "',4,2)) and
(substring(datee,7,4)) >= (substring('" & DTPicker1.Value & "',7,4))) AND
((substring(datee,1,2)) <= (substring('" & DTPicker2.Value & "',1,2)) and
(substring(datee,4,2)) <= (substring('" & DTPicker2.Value & "',4,2)) and
(substring(datee,7,4)) <= (substring('" & DTPicker2.Value & "',7,4)))
but still: 05-27-2005 >= 04-27-2005 ?
it was the only way to solve my problem but still have not answered my
original question on why some dates from others months appeared between
5.1.2005 and 5.30.2005
ah-> datee was from a view where convert(char(10),dbo.venta.SaleDate,103)
as datee
SaleDate is Datetime type, but I need to group them by day so I converted
to char(10) in order to query between dates. I know I did something wrong
somewhere but I have no clue on what or where. Any Ideas? ThanksHi
Did you try it this way:
select * from viewcomis2 where cast(date as datetime) between '05/27/2005'
and '05/30/2005'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"humberto gonzalez" wrote:

> Having this table with sales
> select * from viewcomis2 where date between '05/27/2005' and '05/30/2005'
> I got this weird result:
> 05/27/2005
> 04/28/2005 <-- this is not suppose to be here
> 05/28/2005
> 05/30/2005
> I solved:
> ((substring(datee,1,2)) >= (substring('" & DTPicker1.Value & "',1,2)) and
> (substring(datee,4,2)) >= (substring('" & DTPicker1.Value & "',4,2)) and
> (substring(datee,7,4)) >= (substring('" & DTPicker1.Value & "',7,4))) AND
> ((substring(datee,1,2)) <= (substring('" & DTPicker2.Value & "',1,2)) and
> (substring(datee,4,2)) <= (substring('" & DTPicker2.Value & "',4,2)) and
> (substring(datee,7,4)) <= (substring('" & DTPicker2.Value & "',7,4)))
> but still: 05-27-2005 >= 04-27-2005 ?
> it was the only way to solve my problem but still have not answered my
> original question on why some dates from others months appeared between
> 5.1.2005 and 5.30.2005
> ah-> datee was from a view where convert(char(10),dbo.venta.SaleDate,103)
> as datee
> SaleDate is Datetime type, but I need to group them by day so I converted
> to char(10) in order to query between dates. I know I did something wrong
> somewhere but I have no clue on what or where. Any Ideas? Thanks
>

Query behaviour ?

Need some help from you on the following query behaviour:

1).
select Round(convert(float,40000.01),2)
----------------
40000.010000000002

select convert(nvarchar(25),40000.01)
--------
40000.01

2).
select convert(nvarchar(25),Round(convert(float,40000.01) ,2))
--------
40000

Questions:
1. Why does the second query round up the result to an integer ??
2. Why does the first query insert a 2 at the 12th decimal ??
3. Why the results of the two queries are different?

RgdsThe problem is is that you are using float. Go to sql server books online and look at the article "Using decimal, float, and real Data".

Query behaviour - (subquery results)

Hi there,
I'm experiencing some very strange effects when I trying to include a
subquery. I shall demonstrate with a simple example...
create table test
(ind int,
seq int,
message varchar(255))
insert into test (ind,seq, message) values
(1,1,'date=01/06/2006')
insert into test (ind,seq, message) values
(2,1,'date=1/12/2005')
insert into test (ind,seq, message) values
(2,2,'test')
insert into test (ind,seq, message) values
(2,3,'date=2/12/2005')
The column IND is theoretically a foreign key, the SEQ is a primary key. A
quick explanation is that this is a comment table from a main table (main
table being an 'order' table and this being a 'order comment' table.. the
relationship being (order) 1:m (comment) But for this example this doesn't
really matter.
So here are 2 queries.
select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This one simple extracts the date from the text string. It works OK. I've
had to include the IND =1 to avoid the date conversion error. It just shows
that the conversion works.
results
IND SEQ Message
1 1 date=01/06/2006
The second query...
select max(seq) from test t2
where t2.message like 'date=%'
group by ind
This is extrating the highest 'SEQ' for each 'IND'. ie the last comment
(that has got a date component) for each order
results
SEQ
1
3
So thats OK.
Now the fun starts when I try to combine the two...
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This causes a
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.
So, its implying the date format is incorrect. If I remove the convert :-
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
then the results are OK -
IND SEQ Message
1 1 date=01/06/2006
2 3 date=2/12/2005
Any help please?
thanks
Simon(...)
--Replace the where with and AND -->
AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
HTH, jens Suessmeyer.|||Sorry my bad typo... I meant AND... The query should have been
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
this results in the error. This isn't just a syntax error.
Thanks though
"Jens" wrote:

> (...)
> --Replace the where with and AND -->
> AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> HTH, jens Suessmeyer.
>|||A flaw in the substring function:
...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
Maybe that's it.
ML
ML
http://milambda.blogspot.com/|||I don't think this is it...
If I do a
select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
from test
I get the results..
IND SEQ converted date
1 1 01/06/2006
2 1 1/12/2005
2 2 test
2 3 2/12/2005
So this shows that 3 of the rows can be converted into datetime (103 style).
I think that the problem is that the convert is being done on the whole data
set before approriate rows are excluded.
If I turn this into an inline view then I get the same error.
select * from (
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)) test
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
<bte this is where my original typo came from ;-) >
help !
"ML" wrote:

> A flaw in the substring function:
> ...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
> Maybe that's it.
>
> ML
>
> ML
> --
> http://milambda.blogspot.com/|||I've had a quick look at this and get the same odd result.
The Where clause is being applied to all the contents of the input table.
You can see this in the showplan and can confirm it by removing the row
without a date.
Regards,
Craig
"s_clarke" wrote:
> I don't think this is it...
> If I do a
> select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
> from test
> I get the results..
> IND SEQ converted date
> 1 1 01/06/2006
> 2 1 1/12/2005
> 2 2 test
> 2 3 2/12/2005
>
> So this shows that 3 of the rows can be converted into datetime (103 style
).
> I think that the problem is that the convert is being done on the whole da
ta
> set before approriate rows are excluded.
> If I turn this into an inline view then I get the same error.
> select * from (
> select * from test t1
> where t1.seq in (select max(seq) from test t2
> where t2.ind = t1.ind
> and t2.message like 'date=%'
> group by t2.ind)) test
> where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> <bte this is where my original typo came from ;-) >
> help !
>
> "ML" wrote:
>

Query behaviour

Hi there,
I wonder if one of you worthy folks can help me out with some strange behaviour exhibited by a piece of SQL. Its my first post here , so please be gentle. :)

Here is my simple example :-

<my test table>

create table test
(ind int,
message varchar(255))

insert into test (ind, message) values
(1,'date=01/06/2006')

insert into test (ind, message) values
(1,'date=20/12/2005')
insert into test (ind, message) values
(2,'test')

The first query is

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )

fine... 2 rows

second query

select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

fine same 2 rows...

but If I try to combine the 2 clauses in

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

I get a
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Please can anyone help me on this?

thanks

Simonproblem is you try to convert MESSAGE (varchar) to datetime datatype but you have 'test' inserted in your table
so 'test' string can not be converted to datetime datatype

select SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) from test

01/06/2006
20/12/2005
test

then this fails:

select convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) from test

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.|||Hi there,

thanks for the prompt reply. I've already considered this...

the second query eliminates the bad data row...

select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

The nested query in query 1 is trying to do the same thing - but doesn't work for some reason

thanks

Simon|||Hmmm,
I played with it but it's mystery to me. Who can explain this:

table and data:

create table test
(ind int,
message varchar(255))

insert into test (ind, message) values
(1,'date=01/06/2006')
insert into test (ind, message) values
(1,'date=20/12/2005')
insert into test (ind, message) values
(2,'test')

now I have 2 statements which returns the same data:

select *
from test
where message like 'date=%'

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

when I use first select as subquery (temp table) it runs fine:

select *
from
(
select *
from test
where message like 'date=%'
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

when I use secnd select as subquery it fails:

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

problem is in where clause:

...
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

but why?|||her we go----

select *
from test t1
where
message like 'date=%'
and t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()|||that's nice but you missed my point. it worked also with 3rd select I posted. What I'd like to know is why 4th statement fails if select statement is the same:

select * from
(
...
) where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

only difference (comparing to 3rd) is subquery. but it returns the same result... for both (3rd and 4th statement)
so I'd say: I run same query against same data but result is not the same.|||see this two codes first

--this will work
select *
from test t1
where
message like 'date=%'
and t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()

--this will not work
select *
from test t1
where
t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()
and message like 'date=%'

the reason is sql engine filter data based on first condition(ie messege like 'date=%') then it process 'convert' clause in the where clause

Insecond case it doing the opposite|||ok so what's happening in this statement?

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

I assume first of all it convert Message to datetime. But it should convert just
01/06/2006
20/12/2005
as subquery returns just those two records. there's no reason for failure or am I wrong?|||ok so what's happening in this statement?

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

I assume first of all it convert Message to datetime. But it should convert just
01/06/2006

20/12/2005
as subquery returns just those two records. there's no reason for failure or am I wrong?

take the execution plan of that query(Ctrl+L) and see the Argument: in table scan.U can see how sql server query is processing.

I welcome more comments from SQL server gurus|||I finally found the answer to this. I got it from MS (via MSDN)

"The query engine is free to evaluate predicates in whatever order it deems fit. If you need to control the order, you can do so via a CASE expression (but this may slow things down a bit)... try:

select * from (
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)) test
where
CASE WHEN message like 'date=%' THEN
CASE WHEN convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
THEN 1
ELSE 0
END
ELSE 0
END = 1"

While not a solution as such a good work around. The key to the above is query predicates - the workaround forces this. So, good enough for me.

thanks

Simon

Hope this helps others out :)

Query batch completed with errors...

Hi,
I am executin a stored procedure which contains a sequence of Stored
procedures. One of these stored procedures is a cursor-stored procedure
(basically a cursor).
At the end of the execution of this Stp(cursor), it gives me "query batch
completed with errors". I tried to capture the error through Printing
@.@.error, but to no luck. The problem is that this stp is part of a sequence
of stored procedures. Because of this error the subsequent stps dont get
executed.
Could someone give some leads how to resolve this?
Thanx,
S.How are you executing the proc? Did you try running it in isolation in Query
Analyzer? That should at least show you the error message in the results
window. Debug the proc to find the statement that throws the error (In
Query Analyzer's Object Browser right-click the proc name and select Debug).
If you're still stuck once you've narrowed it down a bit then post some code
here to reproduce the error.
David Portas
SQL Server MVP
--|||David,
Yes I was executing it in the Analyser..that how i found out it was this stp
which was givin the prob...
Here is the code for the problem stp..
---
CREATE PROCEDURE CALC_MyDealValue_As_DealPartner
AS
DECLARE
@.pCustomerCivilId nvarchar(15),
@.pInvoiceNo nvarchar(50),
@.pLiability char(1),
@.pAppId char(3),
@.pGrossBalance decimal(15,3),
@.pShareHolderCivilID nvarchar(15),
@.pPartnerShare decimal(15,3)
--Declare the copy cursor
DECLARE DealPartner_Cursor CURSOR FOR
SELECT CustomerCivilId,InvoiceNumber,Liability,
AppId,ShareHolderCivilID,
Convert(Decimal(15,3),SharePercentage)/100 as share
FROM dbo.Shareholder
--where Left(CustomerCivilId,2) ='JA'
where (InvoiceNumber<>'') and (Liability <> '') and (AppId <>'') --For
Deal Partner This is not empty in Shareholder Table
--Open the cursor
BEGIN
OPEN DealPartner_Cursor
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 1' + convert(nvarchar(50),@.@.Error)
End
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Print 'Parnter- ' + convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Share- ' + convert(nvarchar(50),@.pPartnerShare)
SET @.pGrossBalance = (SELECT isnull(GrossBalance,0) FROM vwMyDealAmounts
WHERE ( --dbo.vwMyDealAmounts.CivilID = @.pCustomerCivilId and
dbo.vwMyDealAmounts.InvoiceNumber = @.pInvoiceNo and
dbo.vwMyDealAmounts.Liability = @.pLiability and
dbo.vwMyDealAmounts.AppId = @.pAppId))
IF @.@.Error <>0
Begin
Print 'Fetch GB ' + convert(nvarchar(50),@.@.Error)
End
--Print 'GrossBal - ' + convert(nvarchar(50),@.pGrossBalance)
--Print 'DealCivilId - ' + convert(nvarchar(50),@.pDealCivilID)
Set @.pGrossBalance = isnull(@.pGrossBalance,0)
if (@.pGrossBalance <> 0.000)
Begin
if Left(@.pCustomerCivilId,2) ='JA'
Begin
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pCustomerCivilId -- update TO JA
End
IF @.@.Error <>0
Begin
Print 'UP JA ' + convert(nvarchar(50),@.@.Error)
End
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pShareHolderCivilID
IF @.@.Error <>0
Begin
Print 'UP NON-JA ' + convert(nvarchar(50),@.@.Error)
End
--Print 'Done for ' + convert(nvarchar(50),@.pCustomerCivilId) + ' - ' +
convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Done for Count ' + convert(nvarchar(50),@.iCount)
End
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 2 ' + convert(nvarchar(50),@.@.Error)
End
END
CLOSE DealPartner_Cursor
DEALLOCATE DealPartner_Cursor
END
GO
---
"David Portas" wrote:

> How are you executing the proc? Did you try running it in isolation in Que
ry
> Analyzer? That should at least show you the error message in the results
> window. Debug the proc to find the statement that throws the error (In
> Query Analyzer's Object Browser right-click the proc name and select Debug
).
> If you're still stuck once you've narrowed it down a bit then post some co
de
> here to reproduce the error.
> --
> David Portas
> SQL Server MVP
> --
>
>|||You still didn't tell us the error message. Did you run it in Debug?
Honestly I wouldn't bother though. You should always try to avoid cursors
and 90% of this code is redundant. The effect seems to be the same as that
of a single UPDATE statement.
The following is my best guess of what you need to do. It's untested and as
it's done without a spec you'll probably have to make some mods. In
particular you may want to add WHERE EXISTS depending on requirements.
CREATE PROCEDURE calc_mydealvalue_as_dealpartner
AS
UPDATE TmpMyDealings
SET valasdealpartner = valasdealpartner +
(SELECT ISNULL(SUM(D.grossbalance*
CONVERT(DECIMAL(15,3),S.sharepercentage)/100),0)
FROM vwMyDealAmounts AS D
JOIN Shareholder AS S
ON D.invoicenumber = S.invoicenumber
AND D.liability = S.liability
AND D.appid = S.appid
AND D.grossbalance <>0
AND tmpmydealings.civilid = S.customercivilid
AND S.customercivilid LIKE 'JA%')
RETURN
GO
If you need more help please post DDL, a few rows of sample data (INSERT
statements are the best way to post sample data) and show us what result you
require from the sample. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

Saturday, February 25, 2012

Query by year group by total students sort by total for each county

QUERY BY USER

Query by Time-Slices

query by time

query by recent dates

Query by quarter

Query by length of varchar column

query by grouping

Query by Example in Enterprise Manager

Query by database or code

Query building based on user selections

query builder: how to do a LIKE parameter

Query Builder with Oracle?

Query Builder Tool

Query Builder Tool

Query Builder Tool

Query Builder Tool

Query builder bug?

Query builder bug?

query builder

query builder

Query Brain Teaser - Revenue Projections

Query blues... Help!

Query blocked by negative spid

Query blocked by negative spid

Query blocked by negative spid

query between two tables

Query between Times

query between 2 dates error

Query behaviour ?

Query behaviour - (subquery results)

Query behaviour

Query batch completed with errors...

query assistance

Blog Archive

About Me