Max Query: return

Showing posts with label return. Show all posts

Friday, March 30, 2012

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
Chuck
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>
|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckSELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.T
K2MSFTNGP05.phx.gbl...
> Hello,
>
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
>
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
>
> Table:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
>
> The Query Should Return:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
>
> Thanks,
>
> Chuck
>
>|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL Query

Dear group,
I need to create a stored procedure to return some data. The trick is the
data needs to be AGGREGATED with a SUM of commissions against each line for
each broker...
I have a table (TRADE) with the following data:
trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
1, 1, 1, 150, 13/06/2006
2, 2, 1, 100, 13/06/2006
3, 1, 1, 75, 14/06/2006
4, 1, 2, 165, 14/06/2006
5, 3, 1, 33.50, 14/06/2006
I want to display (for a DataTable to be used in a Crystal Report) a grid
where the headers will be:
Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id =
2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
(where tradeType_id = 2), Sum Monthly Total.
So that the query, when run on (14/06/2006), will look like:
1, 75, 165, 240, 225, 165, 190
2, null, null, null, 100, null, 100
3, 33.50, null, 33.50, 33.50, null, 33.50
The concept here is that I have a table which contains trades that a broker
has made. Each trade has a commission_amount column and a datestamp. I need
to be able to produce a report which has daily totals for different trade
types, but where the data is AGGREGATED by broker_id. All the SQL I've been
coming up with has been a total mess.
Can anyone assist with the above problem?
Many thanks!
MikeLiddle,
I think the trickiest thing here is the grouping. Is the monthly
total the running total since the first of the month, or just the sum
or trade type 2 records for a broker on a given day?
You can pretty easily group by date, broker id, and trade type.
SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
sumcom
FROM TRADE
GROUP BY broker_id, tradetype_id, datestamp
but the most straightforward way to get it into the format you want is
to do two subqueries and join them back together. But, what that looks
like will depend on whether you're looking for a running total or not.
Ion
Liddle Feesh wrote:
> Dear group,
> I need to create a stored procedure to return some data. The trick is the
> data needs to be AGGREGATED with a SUM of commissions against each line fo
r
> each broker...
> I have a table (TRADE) with the following data:
> trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
> 1, 1, 1, 150, 13/06/2006
> 2, 2, 1, 100, 13/06/2006
> 3, 1, 1, 75, 14/06/2006
> 4, 1, 2, 165, 14/06/2006
> 5, 3, 1, 33.50, 14/06/2006
> I want to display (for a DataTable to be used in a Crystal Report) a grid
> where the headers will be:
> Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id
=
> 2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
> (where tradeType_id = 2), Sum Monthly Total.
> So that the query, when run on (14/06/2006), will look like:
> 1, 75, 165, 240, 225, 165, 190
> 2, null, null, null, 100, null, 100
> 3, 33.50, null, 33.50, 33.50, null, 33.50
> The concept here is that I have a table which contains trades that a broke
r
> has made. Each trade has a commission_amount column and a datestamp. I nee
d
> to be able to produce a report which has daily totals for different trade
> types, but where the data is AGGREGATED by broker_id. All the SQL I've bee
n
> coming up with has been a total mess.
> Can anyone assist with the above problem?
> Many thanks!
> Mike|||<ionFreeman@.gmail.com> wrote in message
news:1150320207.374658.305960@.f6g2000cwb.googlegroups.com...
> Liddle,
> I think the trickiest thing here is the grouping. Is the monthly
> total the running total since the first of the month, or just the sum
> or trade type 2 records for a broker on a given day?
There are only four aggregated calculated fields, those are the daily totals
for trade_Type 1 and 2 and the monthly totals for trade_Type 1 and 2. The
sum is just an addition of those two fields and can be calculated easily by
hand.

> You can pretty easily group by date, broker id, and trade type.
> SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
> sumcom
> FROM TRADE
> GROUP BY broker_id, tradetype_id, datestamp
> but the most straightforward way to get it into the format you want is
> to do two subqueries and join them back together. But, what that looks
> like will depend on whether you're looking for a running total or not.
Cheers, Jon - that's close, but not quite right. I'm looking for a distinct
group, so that if there are only 2 broker_id's, there will be only two rows
and the SUM() data will be aggregated on those rows.
Thanks!sql

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckThis is a multi-part message in MIME format.
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message =news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> > With the following table how would I create a query that would return =all > rows whos EndDate minus StartDate is more than 28 Days.
> > TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> > Table:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
> > > The Query Should Return:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
> > > Thanks,
> > Chuck > >
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

SELECT
=ID
, =CustomerID
, =StartDate
, =EndDate
FROM Customers
WHERE datediff( day, StartDate, =EndDate ) > 28
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...> =Hello,> > With the following table how would I create a query that would =return all > rows whos EndDate minus StartDate is more than 28 =Days.> > TableName: Customers> ID - Integer> CustomerID - VarChar> StartDate - Date> EndDate - Date> > =Table:> > ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 2 Mike1 &n=bsp; 8/25/06 =9/15/06> 3 Dinah &n=bsp; 8/23/06 9/1/06> 4 James &n=bsp; 7/11/06 8/30/06> > > The Query Should Return:> => ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 4 James &n=bsp; 7/11/06 8/30/06> > > Thanks,> > Chuck => >

--=_NextPart_000_086D_01C6D8D2.0BEDBF60--|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>sql

Wednesday, March 28, 2012

Help with SQL Function

Hello All:
From the Following Table, I want to enter a Temperature and then have the
SQL Function Return the Web Color
-- TemperatureIndex --
ID TempMin TempMax WebColor
1 0 9 #E59DCB
2 10 19 #8569FA
3 20 29 #3F9CFB
4 30 39 #73E96F
I would like to Enter a Temperature and return the following WebColor output
15 -> #E59DCB
28 -> #3F9CFB
32 -> #73E96FCREATE TABLE TemperatureIndex
(
ID int NOT NULL,
TempMin int NOT NULL,
TempMax int NOT NULL,
WebColor char(7) NOT NULL
)
GO
INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
GO
CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
ON TemperatureIndex(TempMin, TempMax)
GO
ALTER TABLE TemperatureIndex
ADD CONSTRAINT PK_TemperatureIndex
PRIMARY KEY NONCLUSTERED (ID)
GO
CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
RETURNS char(7)
AS
BEGIN
RETURN (SELECT WebColor
FROM TemperatureIndex
WHERE @.Temp BETWEEN TempMin AND TempMax
)
END
GO
SELECT dbo.GetWebColorForTemperature(15)
SELECT dbo.GetWebColorForTemperature(28)
SELECT dbo.GetWebColorForTemperature(32)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Stuart Shay" <sshay@.yahoo.com> wrote in message
news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Hello All:
> From the Following Table, I want to enter a Temperature and then have the
> SQL Function Return the Web Color
> -- TemperatureIndex --
> ID TempMin TempMax WebColor
> 1 0 9 #E59DCB
> 2 10 19 #8569FA
> 3 20 29 #3F9CFB
> 4 30 39 #73E96F
> I would like to Enter a Temperature and return the following WebColor
> output
> 15 -> #E59DCB
> 28 -> #3F9CFB
> 32 -> #73E96F
>|||Dan:
Thanks for your help !!!
Best
Stuart
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uev6wbH%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> CREATE TABLE TemperatureIndex
> (
> ID int NOT NULL,
> TempMin int NOT NULL,
> TempMax int NOT NULL,
> WebColor char(7) NOT NULL
> )
> GO
> INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
> INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
> INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
> INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
> GO
> CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
> ON TemperatureIndex(TempMin, TempMax)
> GO
> ALTER TABLE TemperatureIndex
> ADD CONSTRAINT PK_TemperatureIndex
> PRIMARY KEY NONCLUSTERED (ID)
> GO
> CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
> RETURNS char(7)
> AS
> BEGIN
> RETURN (SELECT WebColor
> FROM TemperatureIndex
> WHERE @.Temp BETWEEN TempMin AND TempMax
> )
> END
> GO
> SELECT dbo.GetWebColorForTemperature(15)
> SELECT dbo.GetWebColorForTemperature(28)
> SELECT dbo.GetWebColorForTemperature(32)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stuart Shay" <sshay@.yahoo.com> wrote in message
> news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
>

Help with Sproc and multi parameter

I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.

Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)OR (PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)OR (StatusISNULL)AND (ContactTypeID = @.ConTypeID)OR (ContactTypeIDISNULL)AND (Caller = @.Caller)OR (CallerISNULL)AND (SubjectLIKE @.Keyword)OR (SubjectISNULL)RETURN

You have the query correct. Your OR's and AND's are misplaced around the brackets.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASBEGINSET NOCOUNT ONSELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROMtblTasksWHERE(ClientID = @.ClientID)AND (Priority = @.PriorityOR @.PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.StatusOR @.StatusISNULL)AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)AND (Caller = @.CallerOR @.CallerISNULL)AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)SET NOCOUNT OFFEND

|||

I tried it and I'm still not getting any rows returned. I have even tried it with all parameters having a good entry.

Just to be sure;

I should be able to enter a clientID, an ActionDate range, a priority, and the other fields of the table could have any entry or null for the other parameters and get returned, YES?

|||I did not notice it but try setting the length for your parameters.|||

Besize the size, could the default values be the reason:

Like:

...

@.Statusnvarchar(50)=NULL,
@.ConTypeIDint=NULL,
@.Callernvarchar(50)=NULL,
@.Keywordnvarchar(50)=NULL

|||I set the size and still I must have an entry in every parameter. Am I missing something? I should be able to do this, right?|||

I really am stuck on this one. Can anyone offer any suggestions? Does anyone understand my problem with this?

|||

You could add default value to parameter as limno suggested.

If that doesn't work. You could try adding following code to your stored procedure and test in Sql Server Management Studio to trace each parameters.:

if (@.PriorityISNULL)begin print N'Priority IS NULL'endelsebegin print @.Priorityend--print other parameters in similar wayHope this helps.

|||

So here is what I have, listed below: I should be able to pass in a clientID and a valid date range and it should not matter what is in the other fields because I'm passing in NULL, right?

ALTER PROCEDURE dbo.eP_BindContactManagementAction(@.ClientIDint,@.Prioritysmallint=NULL,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar(50)=NULL,@.ConTypeIDint=NULL,@.Callernvarchar(50)=NULL,@.Keywordnvarchar(50)=NULL)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)AND (ContactTypeID = @.ConTypeID)AND (Caller = @.Caller)AND (SubjectLIKE @.Keyword)RETURN

I appreciate any help,|||

You WHERE clause has problem to handle NULL values: Please try this one fromndinakar

SELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,
ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROMtblTasks
WHERE(ClientID = @.ClientID)
AND (Priority = @.PriorityOR @.PriorityISNULL)
AND (ActionDateBETWEEN @.TStartAND @.TEnd)
AND (Status = @.StatusOR @.StatusISNULL)
AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)
AND (Caller = @.CallerOR @.CallerISNULL)
AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)

|||

Another question: could you show us your datasource control code too? if you are using SQLDatasource control, you may need to set this CancelSelectOnNullParameter="false".

Just another shot in dark.

|||

Here is the code, it will return a record if I make an entry for all parameters. But if I leave one blank no records are returned.

Sub BindData() Session("TaskStart") =Me.BasicDatePickerHStart.SelectedValue Session("TaskEnd") =Me.BasicDatePickerHEnd.SelectedValueDim TStartAs String = Session("TaskStart")Dim TEndAs String = Session("TaskEnd")Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("SQL2ConnectionString").ConnectionString'If user unchecks use due date sproc.Dim strSprocAs String strSproc ="BindContactManagementAction" MyConnection =New SqlConnection(ConnectStr) MyCommand =New SqlCommand(strSproc, MyConnection) MyCommand.CommandType = CommandType.StoredProcedureDim ClientIDParamAs New SqlParameter("@.ClientID", SqlDbType.Int, 4) MyCommand.Parameters.Add(ClientIDParam) ClientIDParam.Value = Session("lgClientID")Dim TaskStartParamAs New SqlParameter("@.TStart",Me.BasicDatePickerHStart.SelectedValue) MyCommand.Parameters.Add(TaskStartParam) TaskStartParam.Value =Me.BasicDatePickerHStart.SelectedValueDim TaskEndParamAs New SqlParameter("@.TEnd",Me.BasicDatePickerHEnd.SelectedValue) MyCommand.Parameters.Add(TaskEndParam) TaskEndParam.Value =Me.BasicDatePickerHEnd.SelectedValueIf String.IsNullOrEmpty(Me.KeyWordText.Text)Then Dim KeywordParamAs New SqlParameter("@.Keyword", DBNull.Value) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)Else Dim KeywordParamAs New SqlParameter("@.Keyword",Me.KeyWordText.Text) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then Dim StatusParamAs New SqlParameter("@.Status", DBNull.Value) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)Else Dim StatusParamAs New SqlParameter("@.Status",Me.StatusSearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)End If If String.IsNullOrEmpty(Me.PrioritySearchDrop.SelectedItem.Text)Then Dim PriorityParamAs New SqlParameter("@.Priority", DBNull.Value) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)Else Dim PriorityParamAs New SqlParameter("@.Priority",Me.PrioritySearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)End If If String.IsNullOrEmpty(Me.CallerTextSearch.Text)Then Dim CallerParamAs New SqlParameter("@.Caller", DBNull.Value) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)Else Dim CallerParamAs New SqlParameter("@.Caller",Me.CallerTextSearch.Text) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)End If If String.IsNullOrEmpty(Me.ContactTypeSearchDrop.SelectedValue)Then Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID", DBNull.Value) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)Else Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID",Me.ContactTypeSearchDrop.SelectedValue) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)End If MyConnection.Open()Dim DSAs SqlDataReader = MyCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) MyEditDataGrid.DataSource = DS MyEditDataGrid.DataBind()End Sub

|||

We have to find where the problem is:

1. Whether the Stored Procedure is running OK or not in your database?

2. If the sp can return records with limited parameters, we can focus on the code part.

PS:(this is a version I tested on my db which works)( By the way, I would use field name to Name parameters to avoid confusion, but this is not the problem here)

ALTER

PROCEDURE [dbo].[eP_BindContactManagementAction]

(

@.ClientID

int,

@.Priority

smallint=NULL,

@.TStart

datetime,

@.TEnd

datetime,

@.Status

nvarchar(50)=NULL,

@.ConTypeID

int=NULL,

@.Caller

nvarchar(50)=NULL,

@.Keyword

nvarchar(50)=NULL)

SELECT

Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,ReminderDate,

Reminder

, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDate

FROM

tblTasks

WHERE

(ClientID= @.ClientID)

AND

(Priority= @.PriorityOR @.PriorityISNULL)

AND

(ActionDateBETWEEN @.TStartAND @.TEnd)

AND

(Status= @.StatusOR @.StatusISNULL)

AND

(ContactTypeID= @.ConTypeIDOR @.ConTypeIDISNULL)

AND

(Caller= @.CallerOR @.CallerISNULL)

AND

(SubjectLIKE @.KeywordOR @.KeywordISNULL)

END

|||

Ok, progress the sp is working in the database, I tested it in VS 2005 and it worked, so it must be in my code.

Any thoughts from my earlier post?

|||

I changed all my parameters to the code type below and it is working now:

I very much appreciate your help!

If String.IsNullOrEmpty(Me.KeyWordText.Text)Then MyCommand.Parameters.AddWithValue("@.Keyword", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Keyword",Me.KeyWordText.Text)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then MyCommand.Parameters.AddWithValue("@.Status", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Status",Me.StatusSearchDrop.SelectedItem.Text)End If

sql

Monday, March 26, 2012

Help with sp_executesql and return parameter

I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.

While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.

So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:


 Public Function totalrecordsbysql(list as arraylist) as integer
 dim RetVal as new integer
 dim querystring as string Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
 Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
 cmd.commandtype = commandtype.storedprocedure
 dim mydr as SqlDataReader
 cmd.Parameters.add("@.TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
 cmd.Parameters.add("@.age",sqldbtype.int).value = 18
 cn.Open()
 try
 mydr=cmd.executereader()
 catch e as sqlexception
 dim err as sqlerror
 dim strErrorString as string
 for each err in e.Errors
 strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
 trace.write("sqlexception",strErrorString)
 Next
 finally
 RetVal = cmd.parameters("@.TotalRecords").value
 end try
 Return RetVal
 cn.close()
 End Function

Now here is the stored procedure:


CREATE PROCEDURE SimpleDynProfileCount@.age int,
@.TotalRecords int output
AS
Declare @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
select @.sql = 'select @.xTotalRecords = count(*) from profile where 1 = 1 '
// RAISERROR(@.sql, 16, 1)
IF @.age > 0
Select @.sql = @.sql + ' AND age > @.xage '
Select @.paramlist = '@.xage int, @.xTotalRecords int output'
Execute sp_executesql @.sql,@.paramlist,@.age,@.xTotalRecords = @.TotalRecords output
select @.TotalRecords
GO

Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11Your code works fine for me in Query Analyzer -- with one exception. I needed to change the double forward slashes (//) to double dashes (--) in front of the RAISERROR.

You might also place a SET NOCOUNT ON at the top of your stored procedure. This will suppress the information "xx items selected" messages and will avoid having them returned inadvertently as a resultset.

Terri|||That's what is so strange: I don't receive error messages, just the wrong answer (zero). The double dashes are just artistic license: I didn't remember the comment tag. I just eliminate the line, altogether, in production.|||Try adding SET NOCOUNT ON after your AS at the top of the stored procedure.

Terri|||I have added the statement, but the results are the same. Works fine with the RAISERROR statement, but returns zero without the statement.

I don't want to just leave the RAISERROR statement active. It will come back to haunt me later.

rod|||I have resolved the syntax.

I made a mistake in the vb.net when I used a datareader to retrieve the output value!

Instead of:


mydr=cmd.executereader()

the syntax should be:


cmd.executeNonQuery()

Using this syntax I removed from the Stored Procedure, the RAISERROR statement and the last SELECT @.totalrecords.

After doing all that, everything worked as expected.

-rod

Help with SP or Function

I was trying to write this MDX but I am getting errors so maybe I should
create a stored procedure against my CUBE.
I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.
can someone provide an example?
My cube provides the total revenue for any date. I thought I could take the
avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg
'
avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg
'
and get a value but I am getting an error message.What error message? Where?
ML
http://milambda.blogspot.com/

Friday, March 23, 2012

Help with sequel statement please. Thanks.

Sorry for dup. Please ignore the previous one. Thanks.
Hi all,
How can I return the results below. Any help would greatly appreciate. The
business rule is show below.
IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
DROP TABLE #Policy_nb
GO
CREATE TABLE #Policy_nb
(
Policy_Id INT NULL,
CurrAgent_id INT NULL,
UploadTp_id INT NULL
)
GO
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
4894, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
614, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
4710, 2)
GO
IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
DROP TABLE #HuonUpload_nb
GO
CREATE TABLE #HuonUpload_nb
(
Policy_id INT NULL,
UploadTp_Id INT NULL,
UploadStatus_dt DATETIME NULL
)
GO
INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
go
SELECT *
FROM #Policy_nb
GO
Policy_Id CurrAgent_id UploadTp_id
-- -- --
382099 4894 3
374943 614 3
376279 4710 2
SELECT *
FROM #HuonUpload_nb
GO
Policy_id UploadTp_Id UploadStatus_dt
-- -- --
382099 3 2005-10-03 11:27:00.000
382099 2 2005-10-03 11:25:00.000
382099 1 2005-10-03 11:21:00.000
382099 4 2005-09-30 09:24:00.000
382099 1 2005-09-30 09:22:00.000
382099 2 2005-09-30 09:21:00.000
382099 2 2005-09-30 09:12:00.000
382099 1 2005-09-29 23:36:00.000
382099 4 2005-09-29 09:45:00.000
382099 2 2005-09-28 13:18:00.000
374943 3 2005-10-03 11:26:00.000
374943 2 2005-10-03 10:20:00.000
374943 1 2005-10-03 10:15:00.000
376279 1 2005-09-13 14:19:00.000
376279 2 2005-09-13 14:36:00.000
-- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
--Testing... Not working...
SELECT a.CurrAgent_id,
b.Policy_id,
b.UploadTp_Id,
b.UploadStatus_dt
FROM #Policy_nb AS a
JOIN #HuonUpload_nb AS b
ON a.Policy_id = b.Policy_id
JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
MAX(UploadStatus_dt) AS 'UploadStatus_dt'
FROM #HuonUpload_nb
WHERE UploadTp_id IN (1, 2, 3)
GROUP BY Policy_id, UploadTp_id
ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
ON c.Policy_id = b.Policy_id
AND c.UploadTp_id = b.UploadTp_id
AND c.UploadStatus_dt = b.UploadStatus_dt
ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
GO
--Result want:
CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
-- -- -- --
4894 382099 3 2005-10-03 11:27:00.000
4894 382099 2 2005-10-03 11:25:00.000
4894 382099 1 2005-10-03 11:21:00.000
614 374943 3 2005-10-03 11:26:00.000
614 374943 2 2005-10-03 10:20:00.000
614 374943 1 2005-10-03 10:15:00.000There is no duplicate (unless you mean your post from 8/29).
Please consider using a newsreader, which doesn't have as many
synchronization issues as the web-based interfaces.
http://www.aspfaq.com/5007|||Try this,
SELECT p.CurrAgent_id, h.Policy_id, h.UploadTp_Id, max(h.UploadStatus_dt) AS
UploadStatus_dt
FROM HuonUpload_nb h inner join Policy_nb p on h.policy_id = p.policy_id
WHERE h.UploadTp_Id <=3 AND p.CurrAgent_id IN
(SELECT currAgent_id
FROM (SELECT PN.currAgent_id
FROM HuonUpload_nb NB INNER JOIN POLICY_NB PN ON NB.policy_ID =
PN.policy_ID
WHERE (nb.UploadTp_Id <=3)
GROUP BY pn.currAgent_id, nb.Policy_id, nb.UploadTp_id) AS X
GROUP BY currAgent_id
HAVING count(currAgent_id) = 3)
GROUP BY p.CurrAgent_id, h.Policy_id, h.UploadTp_Id
ORDER BY h.Policy_id DESC, h.UploadTP_Id DESC
Note: # were removed
Regards,
David
"Lam Nguyen" wrote:

> Sorry for dup. Please ignore the previous one. Thanks.
> Hi all,
> How can I return the results below. Any help would greatly appreciate. T
he
> business rule is show below.
>
> IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
> DROP TABLE #Policy_nb
> GO
> CREATE TABLE #Policy_nb
> (
> Policy_Id INT NULL,
> CurrAgent_id INT NULL,
> UploadTp_id INT NULL
> )
> GO
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
> 4894, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
> 614, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
> 4710, 2)
> GO
> IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
> DROP TABLE #HuonUpload_nb
> GO
> CREATE TABLE #HuonUpload_nb
> (
> Policy_id INT NULL,
> UploadTp_Id INT NULL,
> UploadStatus_dt DATETIME NULL
> )
> GO
> INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
> INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
> INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
> INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
> INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
> INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
> go
> SELECT *
> FROM #Policy_nb
> GO
> Policy_Id CurrAgent_id UploadTp_id
> -- -- --
> 382099 4894 3
> 374943 614 3
> 376279 4710 2
> SELECT *
> FROM #HuonUpload_nb
> GO
> Policy_id UploadTp_Id UploadStatus_dt
> -- -- --
> 382099 3 2005-10-03 11:27:00.000
> 382099 2 2005-10-03 11:25:00.000
> 382099 1 2005-10-03 11:21:00.000
> 382099 4 2005-09-30 09:24:00.000
> 382099 1 2005-09-30 09:22:00.000
> 382099 2 2005-09-30 09:21:00.000
> 382099 2 2005-09-30 09:12:00.000
> 382099 1 2005-09-29 23:36:00.000
> 382099 4 2005-09-29 09:45:00.000
> 382099 2 2005-09-28 13:18:00.000
> 374943 3 2005-10-03 11:26:00.000
> 374943 2 2005-10-03 10:20:00.000
> 374943 1 2005-10-03 10:15:00.000
> 376279 1 2005-09-13 14:19:00.000
> 376279 2 2005-09-13 14:36:00.000
>
> -- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
> --Testing... Not working...
> SELECT a.CurrAgent_id,
> b.Policy_id,
> b.UploadTp_Id,
> b.UploadStatus_dt
> FROM #Policy_nb AS a
> JOIN #HuonUpload_nb AS b
> ON a.Policy_id = b.Policy_id
> JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
> MAX(UploadStatus_dt) AS 'UploadStatus_dt'
> FROM #HuonUpload_nb
> WHERE UploadTp_id IN (1, 2, 3)
> GROUP BY Policy_id, UploadTp_id
> ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
> ON c.Policy_id = b.Policy_id
> AND c.UploadTp_id = b.UploadTp_id
> AND c.UploadStatus_dt = b.UploadStatus_dt
> ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
> GO
> --Result want:
> CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
> -- -- -- --
> 4894 382099 3 2005-10-03 11:27:00.000
> 4894 382099 2 2005-10-03 11:25:00.000
> 4894 382099 1 2005-10-03 11:21:00.000
> 614 374943 3 2005-10-03 11:26:00.000
> 614 374943 2 2005-10-03 10:20:00.000
> 614 374943 1 2005-10-03 10:15:00.000
>

Help with Select to return Duplicate rows

Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.

SELECT TOP (100)PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.AddressFROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVING (COUNT(dbo.tblClientAddresses.Address) > 1)ORDER BY dbo.tblClientAddresses.Address

TOP 100 PERCENT is redundant.

SELECT dbo.tblClient.LastName, dbo.tblClientAddresses.Address,count(*)FROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVINGCOUNT(*) > 1ORDER BY dbo.tblClientAddresses.Address

|||

ndinakar,

Is there a way to also return the Client_ID of each row?

|||

Hi Jack,

If the Client_ID is a primary key with unique values, so the duplicate rows have different Client_ID, we cannot return it.

If duplicate rows have the same Client_ID, yes, we can return it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

try dinakar's query just add client id in select list. but dont group by client id. i think that should work.

thanks,

satish.

Help with SELECT Query

I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.

I have a user table that links to a transaction table that links to a
transaction items table that links to the products table:

(User Table)
UserID
Other user data

(Transaction Table)
TransactionID
UserID
Other transaction data such as the date and the transaction result

(TransactionItem Table)
TransactionItemID
TransactionID
ProductID

(Product Table)
ProductID
Other product data

If I try to nest a SELECT query to give me the list of UserIDs for all
users who purchased a given ProductID then SQL Server gets very upset
as Nested Querys are only supposed to return a single value.

So, how do I do this? Build the first list of UserIDs and then select
all other ProductIDs for the users in the list excluding the original
ProductID?

I'm certain this must be a fairly straight forward thing for a SQL
server guru so any help would be appreciated...

Thanks

JimHi

See how to help use by posting DDL
http://www.aspfaq.com/etiquett*e.asp?id=5006 and example data.

This will get them for products X abd Y if they bought them in the same
transaction and you want details from both products:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItemID],
P.[ProductID],P.[Other product data], J.[TransactionItemID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
JOIN [TransactionItem Table] J ON T.[TransactionID] = J.[TransactionID]
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'

Alternatively:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItemID],
P.[ProductID],P.[Other product data], J.[TransactionItemID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
WHERE EXISTS ( SELECT * FROM
JOIN [TransactionItem Table] J
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'
WHERE T.[TransactionID] = J.[TransactionID])

Or if you want it at user level and not necessarily in the same transaction:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItemID],
P.[ProductID],P.[Other product data], J.[TransactionItemID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
WHERE EXISTS ( SELECT * FROM
JOIN [Transaction Table] S
JOIN [TransactionItem Table] J ON S.[TransactionID] = J.[TransactionID]
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'
WHERE U.[UserID] = S.[UserID] )

John

<jimh@.netwasp.com> wrote in message
news:1115485245.378812.90490@.f14g2000cwb.googlegro ups.com...
> I'm not a SQL expert. I want to be able to write a stored procedure
> that will return 'people who bought this product also bought this...'.
> I have a user table that links to a transaction table that links to a
> transaction items table that links to the products table:
> (User Table)
> UserID
> Other user data
> (Transaction Table)
> TransactionID
> UserID
> Other transaction data such as the date and the transaction result
> (TransactionItem Table)
> TransactionItemID
> TransactionID
> ProductID
> (Product Table)
> ProductID
> Other product data
> If I try to nest a SELECT query to give me the list of UserIDs for all
> users who purchased a given ProductID then SQL Server gets very upset
> as Nested Querys are only supposed to return a single value.
> So, how do I do this? Build the first list of UserIDs and then select
> all other ProductIDs for the users in the list excluding the original
> ProductID?
> I'm certain this must be a fairly straight forward thing for a SQL
> server guru so any help would be appreciated...
> Thanks
> Jim|||This is called a relational division. Here is my usual "cut & paste"
about it. You might want to get a copy of SQL FOR SMARTIES for your
desk:

Relational division is one of the eight basic operations in Codd's
relational algebra. The idea is that a divisor table is used to
partition a dividend table and produce a quotient or results table.
The quotient table is made up of those values of one column for which a
second column had all of the values in the divisor.

This is easier to explain with an example. We have a table of pilots
and the planes they can fly (dividend); we have a table of planes in
the hangar (divisor); we want the names of the pilots who can fly every
plane (quotient) in the hangar. To get this result, we divide the
PilotSkills table by the planes in the hangar.

CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane));

PilotSkills
pilot plane
=========================
'Celko' 'Piper Cub'
'Higgins' 'B-52 Bomber'
'Higgins' 'F-14 Fighter'
'Higgins' 'Piper Cub'
'Jones' 'B-52 Bomber'
'Jones' 'F-14 Fighter'
'Smith' 'B-1 Bomber'
'Smith' 'B-52 Bomber'
'Smith' 'F-14 Fighter'
'Wilson' 'B-1 Bomber'
'Wilson' 'B-52 Bomber'
'Wilson' 'F-14 Fighter'
'Wilson' 'F-17 Fighter'

CREATE TABLE Hangar
(plane CHAR(15) NOT NULL PRIMARY KEY);

Hangar
plane
=============
'B-1 Bomber'
'B-52 Bomber'
'F-14 Fighter'

PilotSkills DIVIDED BY Hangar
pilot
=============================
'Smith'
'Wilson'

In this example, Smith and Wilson are the two pilots who can fly
everything in the hangar. Notice that Higgins and Celko know how to
fly a Piper Cub, but we don't have one right now. In Codd's original
definition of relational division, having more rows than are called for
is not a problem.

The important characteristic of a relational division is that the CROSS
JOIN (Cartesian product) of the divisor and the quotient produces a
valid subset of rows from the dividend. This is where the name comes
from, since the CROSS JOIN acts like a multiplication operator.

Division with a Remainder

There are two kinds of relational division. Division with a remainder
allows the dividend table to have more values than the divisor, which
was Codd's original definition. For example, if a pilot can fly more
planes than just those we have in the hangar, this is fine with us.
The query can be written in SQL-89 as

SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar
WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot = PS2.pilot)
AND (PS2.plane = Hangar.plane)));

The quickest way to explain what is happening in this query is to
imagine an old World War II movie where a cocky pilot has just walked
into the hangar, looked over the fleet, and announced, "There ain't no
plane in this hangar that I can't fly!" We are finding the pilots for
whom there does not exist a plane in the hangar for which they have no
skills. The use of the NOT EXISTS() predicates is for speed. Most SQL
systems will look up a value in an index rather than scan the whole
table. The SELECT * clause lets the query optimizer choose the column
to use when looking for the index.

This query for relational division was made popular by Chris Date in
his textbooks, but it is not the only method nor always the fastest.
Another version of the division can be written so as to avoid three
levels of nesting. While it is not original with me, I have made it
popular in my books.

SELECT PS1.pilot
FROM PilotSkills AS PS1, Hangar AS H1
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);

There is a serious difference in the two methods. Burn down the
hangar, so that the divisor is empty. Because of the NOT EXISTS()
predicates in Date's query, all pilots are returned from a division by
an empty set. Because of the COUNT() functions in my query, no pilots
are returned from a division by an empty set.

In the sixth edition of his book, INTRODUCTION TO DATABASE SYSTEMS
(Addison-Wesley; 1995 ;ISBN 0-201-82458-2), Chris Date defined another
operator (DIVIDEBY ... PER) which produces the same results as my
query, but with more complexity.

Exact Division

The second kind of relational division is exact relational division.
The dividend table must match exactly to the values of the divisor
without any extra values.

SELECT PS1.pilot
FROM PilotSkills AS PS1
LEFT OUTER JOIN
Hangar AS H1
ON PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);

This says that a pilot must have the same number of certificates as
there planes in the hangar and these certificates all match to a plane
in the hangar, not something else. The "something else" is shown by a
created NULL from the LEFT OUTER JOIN.

Please do not make the mistake of trying to reduce the HAVING clause
with a little algebra to:

HAVING COUNT(PS1.plane) = COUNT(H1.plane)

because it does not work; it will tell you that the hangar has (n)
planes in it and the pilot is certified for (n) planes, but not that
those two sets of planes are equal to each other.

Note on Performance

The nested EXISTS() predicates version of relational division was made
popular by Chris Date's textbooks, while the author is associated with
popularizing the COUNT(*) version of relational division. The Winter
1996 edition of DB2 ON-LINE MAGAZINE
(http://www.db2mag.com/96011ar:htm) had an article entitled "Powerful
SQL:Beyond the Basics" by Sheryl Larsen which gave the results of
testing both methods. Her conclusion for DB2 was that the nested
EXISTS() version is better when the quotient has less than 25% of the
dividend table's rows and the COUNT(*) version is better when the
quotient is more than 25% of the dividend table.|||John

Thanks very much for your response. I think I may not have been quite
clear enough in my question. What I want to query the database for is:
'for every person who bought product 'X' give me a list of all the
other products they have bought?' This is very similar to the Amazon
site when you are about to purchase a book - underneath it says "people
who bought this book also bought these ones..."

Jim|||>> 'for every person who bought product 'X' give me a list of all the
other products they have bought? <<

SELECT DISTINCT T1.user_id, T1.product_id
FROM Transactions AS T1
WHERE EXISTS
(SELECT *
FROM Transactions AS T2
WHERE T1.user_id = T2.user_id
AND product_id = 'X');

But I would do it this way to get more meaningful data in the
aggregate:

SELECT T1.product_id, COUNT(*) AS pairing_tally, COUNT(DISTINCT
user_id) AS buyer_tally
FROM Transactions AS T1
WHERE EXISTS
(SELECT *
FROM Transactions AS T2
WHERE T1.user_id = T2.user_id
AND product_id = 'X')
AND T1.product_id <> 'X'
GROUP BY T1.product_id
HAVING COUNT(*) > @.my_threshold;sql

Wednesday, March 21, 2012

Help with returning a certain # of records from a view.

I have a view that will return say 5000 records when I do a simple
select query on that view like.

select *
from vw_test_view

How can I set up my query to only return a certain # of records, say
the first 300?

Here is what is going on, we have a large amount of data that returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. So my thoughts were as follows:

1. To run a query to return X amount of the total data for us to work
with.
2. Update these records with a flag in a table that the vw_test_view
filters out.
3. The next time I run the query to pull data from the view it will
skip the records that I have already looked at (because of step 2) and
pull the next X amount of records.

Thanks in advance,
MikeOn 24 Jun 2004 08:43:30 -0700, Mike wrote:

>I have a view that will return say 5000 records when I do a simple
>select query on that view like.
>select *
>from vw_test_view
>
>How can I set up my query to only return a certain # of records, say
>the first 300?
>
>Here is what is going on, we have a large amount of data that returns
>in a view and we need to work with all of it eventually, However we
>want to do it in chunks. So my thoughts were as follows:
>1. To run a query to return X amount of the total data for us to work
>with.
>2. Update these records with a flag in a table that the vw_test_view
>filters out.
>3. The next time I run the query to pull data from the view it will
>skip the records that I have already looked at (because of step 2) and
>pull the next X amount of records.
>Thanks in advance,
>Mike

Hi Mike,

You could use the TOP clause of the SELECT statement:

SELECT TOP 300 Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....

Without the order by, you'll still get maximum 300 rows, but there's no
way predicting which 300 out of the total number of matching rows will be
selected. With the ORDER BY, you'll get the first 300 according to the
specified sort order.

An alternative is to use SET ROWCOUNT:

SET ROWCOUNT 300
SELECT Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....
SET ROWCOUNT 0-- restored default behaviour

The SET ROWCOUNT gives the maximum number of rows to affect for all future
commands from the same connection. Note that this applies to UPDATE and
DELETE as well!! To return to the default behaviour of affecting all rows,
use SET ROWCOUNT 0 or close and re-open the connection.

Note that both methods use proprietary Transact-SQL syntax. An ANSI
standard version can only be done with a specified order (you'll have to
specify by which order you want the 300 "first" rows) and requires a
correlated subquery. It will be much slower.

SELECT Column1, Column2
FROM MyView AS a
WHERE ....-- if necessary
AND (SELECT COUNT(*)
FROM MyView AS b
WHERE ....-- same as in outer join
AND b.OrderingColumn < a.OrderingColumn)
< 300
ORDER BY OrderingColumn-- may be omitted

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> How can I set up my query to only return a certain # of records
[sic], say
the first 300? <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

You will have to get out the RDBMS world and use a cursor of some
kind.

>> Here is what is going on, we have a large amount of data that
returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. <<

1) A mere 5000 rows is not a lot of data.

2) The idea of "doing it in chunks" is dangerous; do you know anything
about transactions, isolation levels and shared data?|||
Sometimes it benefits programmers to get out of in front of their
screens for a while and see how what they do affects end users.
Unfortunately too many of them do not take the time to do this or to try
and understand things from an end users point of view. No 5000 rows is
not a lot of data from a programmers point of view, but from a user who
has to go through this and verify certain information this can seem like
a daunting task, if you can break it down either feed it to them slowly
or split it amongst several people it becomes much more manageable for
them. This by the way is not what I am trying to accomplish, nor is
5000 the # of rows that I have of total data or 300 how many that I want
to pull out at a time. All that this is are made-up scenarios to
illustrate the type of things that I am trying to accomplish.

If you want to crucify me with semantics go ahead. It doesn't matter,
all that does is that people understand my question and through their
generosity point me in the right direction.

Hugo, thanks again for the help this will give me what I need to get the
job done.
And I already have the view using an order by clause on the data and it
returns exactly what I need, so if I add in the top clause it should
give me exactly what I need.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Help with reading datetime with DATEPART

I was hoping someone could help me with the sql syntax in trying to return the date from a datetime value. I'm trying to get the month and day and year from a datetime value in the database but I keep getting a token error. This is the code I'm using to try to read the date, from everything I've read for sql, it should work but it doesn't.

Dim sql As String = "SELECT * FROM People WHERE DATEPART(month, dtime) = '" & _
DateTime.Month & "' & DATEPART(year, dtime) = '" & DateTime.Year & '"

Dim Sqlreader As SqlCeDataReader = cmd.ExecuteReader

The error I get is:
There was an error parsing the query. [ Token line number = 1,Token line offset = 82,Token in error = = ]

It doesn't seem to recognize the second DATEPART search and the = sign is a syntax error.

What am I doing wrong here?

in the "& DATEPART(year, dtime)" part, replace "&" with "and"

regards

|||Hmmm, I tried the '&' symbol and also tried 'AND' but not a lowercase 'and'.
Thanks.

crt

Monday, March 19, 2012

Help with query....

I have this query which I've left running for around 8 hours and does not
return (but is eating a lot of CPU and DISK IO).
The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
above 25% used, 1.1 disk queue. This server is not being used by anyone
else accept my query.
Integrations_activity has 25million rows, candidate has 1.4 million rows.
Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
using MAXDOP 1 to see if parallelism made a difference and it still ran for
8 hours before I killed it.
The query plan is shown below
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock)
join Candidates (nolock) ON candidates.indnum = Integrations_Activity.IndNum
AND candidates.indofficenum = Integrations_Activity.Indofficenum
WHERE Integrations_Activity.typeofactivity = 'CAN' AND
Integrations_Activity.IndNum IS NOT NULL
StmtText
----
----
----
----
----
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock), Candidates (nolock)
Where Integrations_Activity.IndNum IS NOT NULL
And Integrations_Activity.typeofactivity = 'CAN'
And Integrations_Activity.IndNum = Candidates.IndNum
And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
(1 row(s) affected)
StmtText
----
----
----
---
|--Table Update(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
SET:([Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Hash Match(Aggregate, HASH:([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000])
DEFINE:([Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Bmk1000]))
|--Hash Match(Inner Join,
HASH:([Candidates].[IndNum],
[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
RESIDUAL:([Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
[Expr1006]=[Candidates].[IndOfficeNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([Candidates].[IndNum], [Candidates].[IndOfficeNum]))
| |--Hash Match(Inner Join,
HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Bmk1002]))
| | |--Index
Scan(OBJECT:([RMTEST].[dbo].[Candidates].[pk_Candidates]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Bmk1002]))
| |--Index
Scan(OBJECT:([RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([Integrations_Activity].[IndNum], [Expr1006]))
|--Compute
Scalar(DEFINE:([Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
|--Table
Scan(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
WHERE:([Integrations_Activity].[IndNum]<>NULL AND
[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
(15 row(s) affected)Answered in .programming. Please don't multi-post as we can't guess
everywhere where you posted the question.
Regards
Mike
"Paul" wrote:
> I have this query which I've left running for around 8 hours and does not
> return (but is eating a lot of CPU and DISK IO).
> The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
> 8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
> above 25% used, 1.1 disk queue. This server is not being used by anyone
> else accept my query.
> Integrations_activity has 25million rows, candidate has 1.4 million rows.
> Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
> using MAXDOP 1 to see if parallelism made a difference and it still ran for
> 8 hours before I killed it.
> The query plan is shown below
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum => Integrations_Activity.IndNum
> AND candidates.indofficenum => Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
> Integrations_Activity.IndNum IS NOT NULL
>
> StmtText
> ----
> ----
> ----
> ----
> ----
>
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock), Candidates (nolock)
> Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
> (1 row(s) affected)
> StmtText
> ----
> ----
> ----
> ---
> |--Table Update(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
> SET:([Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH:([Bmk1000]),
> RESIDUAL:([Bmk1000]=[Bmk1000])
> DEFINE:([Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([Bmk1000]))
> |--Hash Match(Inner Join,
> HASH:([Candidates].[IndNum],
> [Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
> RESIDUAL:([Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
> [Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS:([Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
> HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS:([Bmk1002]))
> | | |--Index
> Scan(OBJECT:([RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS:([Bmk1002]))
> | |--Index
> Scan(OBJECT:([RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS:([Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
> Scalar(DEFINE:([Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
> Scan(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
> WHERE:([Integrations_Activity].[IndNum]<>NULL AND
> [Integrations_Activity].[typeofactivity]='CAN') ORDERED)
> (15 row(s) affected)
>
>
>|||Can you post the CREATE TABLE AND CREATE INDEX statements, and the
non-parallel plan? Also, are there any constraints, indexed views, or
other dependent objects?
Do you have any idea how many rows this query will update?
Steve Kass
Drew University
Paul wrote:
>I have this query which I've left running for around 8 hours and does not
>return (but is eating a lot of CPU and DISK IO).
>The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
>8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
>above 25% used, 1.1 disk queue. This server is not being used by anyone
>else accept my query.
>Integrations_activity has 25million rows, candidate has 1.4 million rows.
>Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
>using MAXDOP 1 to see if parallelism made a difference and it still ran for
>8 hours before I killed it.
>The query plan is shown below
>Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum =>Integrations_Activity.IndNum
> AND candidates.indofficenum =>Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
>Integrations_Activity.IndNum IS NOT NULL
>
>StmtText
>----
>----
>----
>----
>----
>
>Update Integrations_Activity
>Set CandidateID = Candidates.CandidateID
>From Integrations_Activity (nolock), Candidates (nolock)
>Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
>(1 row(s) affected)
>StmtText
>----
>----
>----
>---
> |--Table Update(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
>SET:([Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH:([Bmk1000]),
>RESIDUAL:([Bmk1000]=[Bmk1000])
>DEFINE:([Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
>COLUMNS:([Bmk1000]))
> |--Hash Match(Inner Join,
>HASH:([Candidates].[IndNum],
>[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
>RESIDUAL:([Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
>[Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS:([Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
>HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS:([Bmk1002]))
> | | |--Index
>Scan(OBJECT:([RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS:([Bmk1002]))
> | |--Index
>Scan(OBJECT:([RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS:([Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
>Scalar(DEFINE:([Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
>Scan(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
>WHERE:([Integrations_Activity].[IndNum]<>NULL AND
>[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
>(15 row(s) affected)
>
>
>

Help with Query.

Friends,
I have a table as shown below,
ABC DEF
23 2156
34 2156
41 2156
34 2157
38 2157
41 2157
I would like to return data for ABC column in a comma seperated format, I
would like to import the data into a new table.
23,34,41 2156
34,38,41 2157
How can i do that?
Any help is greatly appreciated.
TIA,
Santosh
Santhosh,
Here is an example. modify it to fit your schema.
CREATE TABLE Users(Uid int, Username VARCHAR(35))
CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
CREATE TABLE UserRoles(Uid int, Rid int)
Go
INSERT INTO Users VALUES(1, 'A')
INSERT INTO Users VALUES(2, 'B')
INSERT INTO Users VALUES(3, 'C')
INSERT INTO Users VALUES(4, 'D')
INSERT INTO Roles Values(1,'Admin')
INSERT INTO Roles Values(2,'Accounts')
INSERT INTO Roles Values(3,'Operations')
INSERT INTO Roles Values(4,'Marketing')
INSERT INTO UserRoles VALUES(1,1)
INSERT INTO UserRoles VALUES(1,4)
INSERT INTO UserRoles VALUES(2,3)
INSERT INTO UserRoles VALUES(2,4)
INSERT INTO UserRoles VALUES(3,1)
INSERT INTO UserRoles VALUES(3,2)
INSERT INTO UserRoles VALUES(3,3)
INSERT INTO UserRoles VALUES(4,2)
Go
CREATE Function dbo.GetRoles(@.Uid int)
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.vchRoleList VARCHAR(400)
SET @.vchRoleList = ''
SELECT @.vchRoleList = @.vchRoleList +
CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
FROM Roles R
INNER JOIN UserRoles UR
ON R.Rid = UR.Rid AND UR.UId = @.Uid
RETURN @.vchRoleList
END
GO
SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
FROM Users U
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Friends,
> I have a table as shown below,
> ABC DEF
> --
> 23 2156
> 34 2156
> 41 2156
> 34 2157
> 38 2157
> 41 2157
>
> I would like to return data for ABC column in a comma seperated format, I
> would like to import the data into a new table.
> 23,34,41 2156
> 34,38,41 2157
> How can i do that?
> Any help is greatly appreciated.
> TIA,
> --
> Santosh
>
>
|||It works.
Thanks,
Appreciate it.
Santosh
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:eD8U7354EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Santhosh,
> Here is an example. modify it to fit your schema.
>
> CREATE TABLE Users(Uid int, Username VARCHAR(35))
> CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
> CREATE TABLE UserRoles(Uid int, Rid int)
> Go
> INSERT INTO Users VALUES(1, 'A')
> INSERT INTO Users VALUES(2, 'B')
> INSERT INTO Users VALUES(3, 'C')
> INSERT INTO Users VALUES(4, 'D')
> INSERT INTO Roles Values(1,'Admin')
> INSERT INTO Roles Values(2,'Accounts')
> INSERT INTO Roles Values(3,'Operations')
> INSERT INTO Roles Values(4,'Marketing')
>
> INSERT INTO UserRoles VALUES(1,1)
> INSERT INTO UserRoles VALUES(1,4)
> INSERT INTO UserRoles VALUES(2,3)
> INSERT INTO UserRoles VALUES(2,4)
> INSERT INTO UserRoles VALUES(3,1)
> INSERT INTO UserRoles VALUES(3,2)
> INSERT INTO UserRoles VALUES(3,3)
> INSERT INTO UserRoles VALUES(4,2)
> Go
> CREATE Function dbo.GetRoles(@.Uid int)
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.vchRoleList VARCHAR(400)
> SET @.vchRoleList = ''
> SELECT @.vchRoleList = @.vchRoleList +
> CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
> FROM Roles R
> INNER JOIN UserRoles UR
> ON R.Rid = UR.Rid AND UR.UId = @.Uid
> RETURN @.vchRoleList
> END
> GO
> SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
> FROM Users U
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
>

Help with Query.

Friends,
I have a table as shown below,
ABC DEF
--
23 2156
34 2156
41 2156
34 2157
38 2157
41 2157
I would like to return data for ABC column in a comma seperated format, I
would like to import the data into a new table.
23,34,41 2156
34,38,41 2157
How can i do that?
Any help is greatly appreciated.
TIA,
--
SantoshSanthosh,
Here is an example. modify it to fit your schema.
CREATE TABLE Users(Uid int, Username VARCHAR(35))
CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
CREATE TABLE UserRoles(Uid int, Rid int)
Go
INSERT INTO Users VALUES(1, 'A')
INSERT INTO Users VALUES(2, 'B')
INSERT INTO Users VALUES(3, 'C')
INSERT INTO Users VALUES(4, 'D')
INSERT INTO Roles Values(1,'Admin')
INSERT INTO Roles Values(2,'Accounts')
INSERT INTO Roles Values(3,'Operations')
INSERT INTO Roles Values(4,'Marketing')
INSERT INTO UserRoles VALUES(1,1)
INSERT INTO UserRoles VALUES(1,4)
INSERT INTO UserRoles VALUES(2,3)
INSERT INTO UserRoles VALUES(2,4)
INSERT INTO UserRoles VALUES(3,1)
INSERT INTO UserRoles VALUES(3,2)
INSERT INTO UserRoles VALUES(3,3)
INSERT INTO UserRoles VALUES(4,2)
Go
CREATE Function dbo.GetRoles(@.Uid int)
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.vchRoleList VARCHAR(400)
SET @.vchRoleList = ''
SELECT @.vchRoleList = @.vchRoleList +
CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
FROM Roles R
INNER JOIN UserRoles UR
ON R.Rid = UR.Rid AND UR.UId = @.Uid
RETURN @.vchRoleList
END
GO
SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
FROM Users U
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Friends,
> I have a table as shown below,
> ABC DEF
> --
> 23 2156
> 34 2156
> 41 2156
> 34 2157
> 38 2157
> 41 2157
>
> I would like to return data for ABC column in a comma seperated format, I
> would like to import the data into a new table.
> 23,34,41 2156
> 34,38,41 2157
> How can i do that?
> Any help is greatly appreciated.
> TIA,
> --
> Santosh
>
>|||It works.
Thanks,
Appreciate it.
--
Santosh
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:eD8U7354EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Santhosh,
> Here is an example. modify it to fit your schema.
>
> CREATE TABLE Users(Uid int, Username VARCHAR(35))
> CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
> CREATE TABLE UserRoles(Uid int, Rid int)
> Go
> INSERT INTO Users VALUES(1, 'A')
> INSERT INTO Users VALUES(2, 'B')
> INSERT INTO Users VALUES(3, 'C')
> INSERT INTO Users VALUES(4, 'D')
> INSERT INTO Roles Values(1,'Admin')
> INSERT INTO Roles Values(2,'Accounts')
> INSERT INTO Roles Values(3,'Operations')
> INSERT INTO Roles Values(4,'Marketing')
>
> INSERT INTO UserRoles VALUES(1,1)
> INSERT INTO UserRoles VALUES(1,4)
> INSERT INTO UserRoles VALUES(2,3)
> INSERT INTO UserRoles VALUES(2,4)
> INSERT INTO UserRoles VALUES(3,1)
> INSERT INTO UserRoles VALUES(3,2)
> INSERT INTO UserRoles VALUES(3,3)
> INSERT INTO UserRoles VALUES(4,2)
> Go
> CREATE Function dbo.GetRoles(@.Uid int)
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.vchRoleList VARCHAR(400)
> SET @.vchRoleList = ''
> SELECT @.vchRoleList = @.vchRoleList +
> CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
> FROM Roles R
> INNER JOIN UserRoles UR
> ON R.Rid = UR.Rid AND UR.UId = @.Uid
> RETURN @.vchRoleList
> END
> GO
> SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
> FROM Users U
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> I have a table as shown below,
>> ABC DEF
>> --
>> 23 2156
>> 34 2156
>> 41 2156
>> 34 2157
>> 38 2157
>> 41 2157
>>
>> I would like to return data for ABC column in a comma seperated format, I
>> would like to import the data into a new table.
>> 23,34,41 2156
>> 34,38,41 2157
>> How can i do that?
>> Any help is greatly appreciated.
>> TIA,
>> --
>> Santosh
>>
>

Help with query! Ranking of Sum() column

Are you sure this works with an aggregate function?
Also, can I return ONE row and get the correct rank?Can you provide some useful DDL, sample data, and desired output? (
http://www.aspfaq.com/5006 )
I'm having a hard time visualizing " I think loop through in my code to find
what row number a specific ID is."
A
<andymilk@.gmail.com> wrote in message
news:1146493870.042951.123210@.i39g2000cwa.googlegroups.com...
> Are you sure this works with an aggregate function?
> Also, can I return ONE row and get the correct rank?
>

Monday, March 12, 2012

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
Chuck
Use the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

help with query

Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
================================================== ==
attempt at storedprocedure
================================================== ==
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
================================================== ==
table structure being used
================================================== ==
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
I think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan

Help with Query

Hello I have a table simular to the following:
ID = int (Key)
Temp1 = nChar
Temp2 = nChar
Temp3 = nChar
The goal is to return something like this:
SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
"Hello Mr. Chuck".
The problem is, if Temp2 equals Nothing or DBNull I don't want it included
in MyValue.
I just want it to return "Hello Chuck".
Any assistance will be greatly appreciated,
ChuckUse the coalesce function something like this:
SELECT COALESCE(Temp1, '')+' "+COALESCE(Temp2, '')...
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:%23qrgNRPXHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hello I have a table simular to the following:
> ID = int (Key)
> Temp1 = nChar
> Temp2 = nChar
> Temp3 = nChar
> The goal is to return something like this:
> SELECT Temp1+ ' ' + Temp2+ ' ' +Temp3 AS MyValue
> If Temp1 = "Hello" and Temp2 = "Mr." and Temp3 = "Chuck" it would return
> "Hello Mr. Chuck".
> The problem is, if Temp2 equals Nothing or DBNull I don't want it included
> in MyValue.
> I just want it to return "Hello Chuck".
> Any assistance will be greatly appreciated,
> Chuck
>

Friday, March 30, 2012

Help With SQL Query

Help With SQL Query

Help with SQL Query

Help With SQL Query

Wednesday, March 28, 2012

Help with SQL Function

Help with Sproc and multi parameter

Monday, March 26, 2012

Help with sp_executesql and return parameter

Help with SP or Function

Friday, March 23, 2012

Help with sequel statement please. Thanks.

Help with Select to return Duplicate rows

Help with SELECT Query

Wednesday, March 21, 2012

Help with returning a certain # of records from a view.

Help with reading datetime with DATEPART

Monday, March 19, 2012

Help with query....

Help with Query.

Help with Query.

Help with query! Ranking of Sum() column

Monday, March 12, 2012

Help with Query

help with query

Help with Query

Max Query

Blog Archive

About Me