Max Query: rows

Showing posts with label rows. Show all posts

Friday, March 30, 2012

Help with SQL query please

I have a table that have 4 fields of interest, A, B, C and D (plus others
that are not of interest).
I am not interested in completely unique rows (based on the fields A,B,C &
D).
What I am interested in rows where the A, B & C fields are identical, but
within these identical "groups" the D field is different.
So, I though I'd write an SQL statement that uses a sub query.
The inner query would bring back all rows that are not unique (by using a
count > 1 statement) and the outer query would then get the details
The SQL I came up with is as follows.
The query is as follows:
---
select A, B, C, D
from X
where exists
(select A, B, C, count(D)
from X
group by A, B, C
having count(D) > 1)
order by A, B, C
---
However...when running the sub query in isolation and ordering the results,
I found that the lowest value of "A" returned was 3. But, when I run the
query as a whole, the first set of results returned have data for rows that
contain A values of 1 & 2.
Puzzled over this, but now need some help.
Thanks in advance if you can provide this...
GriffPlease ALWAYS post DDL with questions like this so that we don't have
to guess what your table looks like. It also helps to include some
sample data and show your required end result.
The problems with the query you posted seem to be twofold. First the
subquery isn't correlated. Second COUNT(D) will count >1 if there is
more than one row even if D is the same (non-null) in each case. That
would make sense if (A,B,C,D) is a key of this table but you didn't
actually specify a key (did I mention about the importance of including
DDL?).
Assuming (A,B,C,D) is not nullable (yes, the DDL would have told us
that too) you can do it with a correlated subquery:
SELECT x.a, x.b, x.c, x.d
FROM x
WHERE EXISTS
(SELECT *
FROM x AS z
WHERE x.a = z.a
AND x.b = z.b
AND x.c = x.c
AND x.d <> z.d)
ORDER BY x.a, x.b, x.c, x.d ;
or with a derived table:
SELECT x.a, x.b, x.c, x.d
FROM
(SELECT a, b, c
FROM x
GROUP BY a, b, c
HAVING MIN(d)<MAX(d)) AS z
JOIN x
ON x.a = z.a
AND x.b = z.b
AND x.c = x.c
ORDER BY x.a, x.b, x.c, x.d ;
(both untested)
Essential reading on the best way to post a problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

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 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

Friday, March 23, 2012

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

Hi
Is there a way to get a limited number of rows results from a select query
?
like select top 50 * from mytable returns only the first 50 records, but
what if a want to get records 50 to 100.
(I woulk like to spread the results over multiple pages.)
I would like to avoid to have all records pulled from the server and do the
job at the client side.
Johan
If you are using SQL Server 2005 you can use a windowing function like
Rownumber() for getting a partial resultsset. See the BOL for more
information. This requires having SQL Server 2005 as this
functionality is not present in SQL Server 2000.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

Help with SELECT please

I need to select 2 rows from a table for every SSN. DDL for the table:

CREATE TABLE [dbo].[tblResidentRotations] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResidentProgram] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RotationID] [int] NULL ,
[MonthName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RotationLocationID] [int] NULL ,
[CallLocationID] [int] NULL ,
[IMClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IMClinicDateLast] [datetime] NULL ,
[IMClinicDateFirst] [datetime] NULL ,
[PedsClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClinicScheduleComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastFirstComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PGYLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcademicYear] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format. I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

Thanks for any help and advice.Stop using that silly redundant "tbl-" prefix; If nobody told you
yet, SQL only has one data structure. Then you might want to read a
basic book on data modeling - you always name a data element for what
it is, not for how it is stored, where it is used, etc.

Next, we need keys to have proper tables. An IDENTITY is **never** a
key by definition. SSN is never VARCHAR() but it is fixed length, so
all you did was invite a loss of data integrity. Ditto when you pulled
oversize numbers out of the air for the other column sizes. Since there
are no non-NOT NULL columns, you cannot ever have a key!! Think about
VARCHAR(1) and what it means.

Do you know of a month name that is CHAR(15)? Why are you using
strings for temporal data in SQL? Why are you using vague strings like
"Monday PM" for temporal data? Why did you violate ISO-8601 formats
for the bad dates?

What does the resident's academic year have to do with rotations?
The whole mess looks denormalized. Just based on a few decades of
prior experience, I would guess this ought o reduce down to something
like this:

CREATE TABLE ResidentRotations
(ssn VARCHAR(9) NOT NULL
REFERENCES Residents(ssn),
resident_program VARCHAR(20) NOT NULL,
rotation_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
rotation_start_time DATETIME NOT NULL,
rotation_end_time DATETIME NOT NULL,
CHECK (rotation_start_time < rotation_end_time),
call_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
PRIMARY KEY (ssn, rotation_start_time));

>> I need to pull what the IMClinicDay would be for this SSN in September. <<

You need to use a Calendar table and insert the scheduled shifts in
advance for the known duration. You can take care of holidays,
re-scheduling, etc. with this approach.

You might want to read Rick Snodgrass' s book on Temporal queries in
SQL after you get thru a basic data modeling book and a few ISO
standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> data in mm/dd/yy format.

No, they are declared as datetime, which means that they are in a
binary format. If you say

SELECT * FROM tbl WHERE datecol = '07/01/05'

You could get rows from from 2007-01-05, 2005-01-07 or any other
of the six possible permutations, depending on the current settings.
On the other hand:

SELECT * FROM tbl WHERE datecol = '20070105'

will always give the same set of data.

OK, so that is not what you asked about, but since you had an apparent
misunderstanding about datetime, I figured I should point it out.

> I need to pull IMClinicDay for the date given
> in IMClinicDateFirst. For example, if my data looks like this:
> SSN: 999999999
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> then I need to pull what the IMClinicDay would be for this SSN in
> September.

If I understand this correctly, you should have a look at the datename()
function in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's a sample of the data I have:

SSN: 999999999 (first row)
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

SSN: 999999999 (nth row)
MonthName: September
IMClinicDay: Wednesday PM
IMClinicDateLast: 09/01/05
IMClinicDateFirst: 10/01/05

With a SELECT statement, I want to return all of the first row and only
the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
in the first row to get this data.

Thanks for any help.

Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> > IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> > "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> > data in mm/dd/yy format.
> No, they are declared as datetime, which means that they are in a
> binary format. If you say
> SELECT * FROM tbl WHERE datecol = '07/01/05'
> You could get rows from from 2007-01-05, 2005-01-07 or any other
> of the six possible permutations, depending on the current settings.
> On the other hand:
> SELECT * FROM tbl WHERE datecol = '20070105'
> will always give the same set of data.
> OK, so that is not what you asked about, but since you had an apparent
> misunderstanding about datetime, I figured I should point it out.
> > I need to pull IMClinicDay for the date given
> > in IMClinicDateFirst. For example, if my data looks like this:
> > SSN: 999999999
> > MonthName: July
> > IMClinicDay: Monday PM
> > IMClinicDateLast: 07/01/05
> > IMClinicDateFirst: 09/01/05
> > then I need to pull what the IMClinicDay would be for this SSN in
> > September.
> If I understand this correctly, you should have a look at the datename()
> function in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I just want to know if I can get to the row I want. If it's impossible
with the structure I have then just say so.

--CELKO-- wrote:
> Stop using that silly redundant "tbl-" prefix; If nobody told you
> yet, SQL only has one data structure. Then you might want to read a
> basic book on data modeling - you always name a data element for what
> it is, not for how it is stored, where it is used, etc.
> Next, we need keys to have proper tables. An IDENTITY is **never** a
> key by definition. SSN is never VARCHAR() but it is fixed length, so
> all you did was invite a loss of data integrity. Ditto when you pulled
> oversize numbers out of the air for the other column sizes. Since there
> are no non-NOT NULL columns, you cannot ever have a key!! Think about
> VARCHAR(1) and what it means.
> Do you know of a month name that is CHAR(15)? Why are you using
> strings for temporal data in SQL? Why are you using vague strings like
> "Monday PM" for temporal data? Why did you violate ISO-8601 formats
> for the bad dates?
> What does the resident's academic year have to do with rotations?
> The whole mess looks denormalized. Just based on a few decades of
> prior experience, I would guess this ought o reduce down to something
> like this:
> CREATE TABLE ResidentRotations
> (ssn VARCHAR(9) NOT NULL
> REFERENCES Residents(ssn),
> resident_program VARCHAR(20) NOT NULL,
> rotation_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> rotation_start_time DATETIME NOT NULL,
> rotation_end_time DATETIME NOT NULL,
> CHECK (rotation_start_time < rotation_end_time),
> call_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> PRIMARY KEY (ssn, rotation_start_time));
> >> I need to pull what the IMClinicDay would be for this SSN in September. <<
> You need to use a Calendar table and insert the scheduled shifts in
> advance for the known duration. You can take care of holidays,
> re-scheduling, etc. with this approach.
> You might want to read Rick Snodgrass' s book on Temporal queries in
> SQL after you get thru a basic data modeling book and a few ISO
> standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> Here's a sample of the data I have:
> SSN: 999999999 (first row)
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> SSN: 999999999 (nth row)
> MonthName: September
> IMClinicDay: Wednesday PM
> IMClinicDateLast: 09/01/05
> IMClinicDateFirst: 10/01/05
> With a SELECT statement, I want to return all of the first row and only
> the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
> in the first row to get this data.

Since your table definition did not include any information about keys,
I cannot be sure that this query works:

SELECT a.SSN, a.ResidentProgram, ..., b.IMClinicDay
FROM tblResidentRotations a
LEFT JOIN b tblResidentRotations
ON a.SSN = b.SSN
AND a.IMClinicDateLast = b.IMClinicDateFirst
WHERE a.IMClinicDateFirst >= @.yearmonth + '01' AND
a.IMClincDateFirst < dateadd(MONTH, 1, @.yearmonth + '01')

I assume that @.yearmonth holds the month you are looking for on the
form YYYYMM.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

help with select

I have 300 records in one table. I want to select that in 3 times, each time
100 rows.
Anyone have some idea how to do that'
So, I would have 3 queries and each query would get 100 records.
If anyone can help...
Thanks!!Here's an example.
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
If you want the resultsets to be guaranteed to be identical, then I'd use a
temporary table first and do the 3 selects from that.
Select top 100 * into #yourtemptable from yourtable order by yourprimarykey
select * from #yourtemptable
select * from #yourtemptable
select * from #yourtemptable
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||(or use REPEATABLEREAD if you need consistency).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0A32_01C6CC1D.EA4583E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perhaps you want to retrieve the 300 rows, by getting 100 rows at a =time.
If so,
-- First 100
SELECT TOP 100 {ColumnList}
FROM MyTable
ORDER BY {SortValue}
-- Second 100
SELECT TOP 100 {ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN ( SELECT TOP 100 PKeyValue {ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100 {ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN ( SELECT TOP 200 PKeyValue {ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"BJ" <bernard@.hi.hinet.hr> wrote in message =news:ed460v$cuh$1@.magcargo.vodatel.hr...
>I have 300 records in one table. I want to select that in 3 times, each =time
> 100 rows.
> Anyone have some idea how to do that'
> So, I would have 3 queries and each query would get 100 records.
> > If anyone can help...
> > Thanks!! > >
--=_NextPart_000_0A32_01C6CC1D.EA4583E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Perhaps you want to retrieve the 300 =rows, by getting 100 rows at a time.
If so,
-- First 100
SELECT TOP 100
{ColumnList}
FROM MyTable
ORDER BY ={SortValue}
-- Second 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN = ( SELECT TOP =100 PKeyValue
{ColumnList}
=FROM MyTable
=ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN = ( SELECT TOP =200 PKeyValue
{ColumnList}
=FROM MyTable
=ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"BJ" =wrote in message news:ed460v$cuh$1@.magcargo.vodatel.hr...>I have 300 =records in one table. I want to select that in 3 times, each time> 100 =rows.> Anyone have some idea how to do that'> So, I would have 3 =queries and each query would get 100 records.> > If anyone can help...> > Thanks!! > =>

--=_NextPart_000_0A32_01C6CC1D.EA4583E0--

help with select

I have 300 records in one table. I want to select that in 3 times, each time
100 rows.
Anyone have some idea how to do that'
So, I would have 3 queries and each query would get 100 records.
If anyone can help...
Thanks!!Here's an example.
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
If you want the resultsets to be guaranteed to be identical, then I'd use a
temporary table first and do the 3 selects from that.
Select top 100 * into #yourtemptable from yourtable order by yourprimarykey
select * from #yourtemptable
select * from #yourtemptable
select * from #yourtemptable
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||(or use REPEATABLEREAD if you need consistency).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Perhaps you want to retrieve the 300 rows, by getting 100 rows at a time.
If so,
-- First 100
SELECT TOP 100
{ColumnList}
FROM MyTable
ORDER BY {SortValue}
-- Second 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN
( SELECT TOP 100 PKeyValue
{ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN
( SELECT TOP 200 PKeyValue
{ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BJ" <bernard@.hi.hinet.hr> wrote in message news:ed460v$cuh$1@.magcargo.vodatel.hr...eagreen">
>I have 300 records in one table. I want to select that in 3 times, each tim
e
> 100 rows.
> Anyone have some idea how to do that'
> So, I would have 3 queries and each query would get 100 records.
>
> If anyone can help...
>
> Thanks!!
>
>

Monday, March 19, 2012

Help with query NOT IN

I have a view containing column X and column Y and a foreign key F. I
want to filter the view so that it does not contain any rows which are
in the foreign table, which also contain columns X and Y.
I want to do something like this:
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t ON t.f = v.f
WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
ThanksTry,
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t
ON v.x = t.x and v.y = t.y
WHERE t.X is null and t.Y is null
AMB
"larzeb" wrote:

> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
>|||larzeb wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
Not sure I understand youtr specs. Are you saying you want to see all
rows from the view that do not have a match of all columns (key, x, and
y) in the foregn key table? I don't understand what you mean by "which
also contain columns X and Y" - I assume you mean the same values in x
and y?
Select col1, col2, col3
From vView v
Where Not Exists (
Select *
From Table1 t
On v.f = t.f
and v.x = t.x
and v.y = t.y)
David Gugick
Imceda Software
www.imceda.com

help with query from a sql newbie

hope someone can help

i have a table a temp table that gets created on a daily basis andcan have between 10 -100 rows in it which looks like this

idstarttimeduration

110:00:00600

210:10:00300

311:33:0015

etc

duration is in seconds

what i want to be able to do is add the start time from a row to the duration from the same row and sutract it from the next rows startime.

Andy

is the value of column id in running ? What is the version of SQL Server are you using ?|||

apwhelan wrote:
what i want to be able to do is add the start time from a row to the duration from the same row and sutract it from the next rows startime.

That is a bit confusing.

What is 'next'?

The row chronologically following?

What are you attempting to 'discover'?

It seems like you may be after the amount of time between events -but that isn't too clear. So in your sample data, there is no 'missing' time between rows 1 and 2, but quite a bit of 'missing' time between rows 2 and 3.

Is that what you wish to display?

|||

I agree this is a little confusing but will the following get you somewhere close to where you want to be?

select t1.id as endoftask, t1.id2 as startoftask, datediff(s,endtime, t2.starttime) as idletime

from (SELECT *, dateadd(s, duration, starttime) as endtime, id+1 as id2

FROM table1) AS t1

inner join table1 t2

on t1.id2 = t2.id

This assumes that your id column is sequential with no missing numbers. If this doesn't work, i guess you could go down the cursor route.

Hope this helps!

|||

Another potential alternative might be something like:

Code Snippet

declare @.temp table
( id integer,
startTime datetime,
duration integer
)

insert into @.temp
select 1, '10:00:00', 600 union all
select 2, '10:10:00', 300 union all
select 3, '11:33:00', 15
--select * from @.temp

;with tempSeq as
( select id,
startTime,
duration,
dateadd(ss, duration, startTime) as endTime,
row_number() over
(order by startTime, id) as Seq
from @.temp
), tempSeq2 as
( select id,
startTime,
duration,
dateadd(ss, duration, startTime) as endTime,
1 + row_number() over
(order by startTime, id) as Seq2
from @.temp
)
select a.id,
a.Seq,
convert(varchar(10), a.startTime, 108) as a_startTime,
a.duration,
convert(varchar(10), a.endTime, 108) as a_endTime,
convert(varchar(10),
case when Seq2 is null then 0
else a.endTime - b.StartTime
end, 108)
as timeDifference
from tempSeq a
left join tempSeq2 b
on a.Seq = b.Seq2

/*
id Seq a_startTime duration a_endTime timeDifference
-- -- -- - --
1 1 10:00:00 600 10:10:00 00:00:00
2 2 10:10:00 300 10:15:00 00:15:00
3 3 11:33:00 15 11:33:15 01:23:15
*/

|||All of these work, but the bigger question is why you need to store that data. The best thing to do is to store the start and end of a thing, and then you can use those to calculate any kind of interval you need, including averages, sums and the like. Remember, avoid storing what you can calculate, unless it becomes a performance issue to do so.

Help with Query - Insert multiple rows and link between tables.

I am trying to do the following:

Insertn rows into A Table calledEAItems. For each row that is inserted intoEAItemsI need to take thatItemID(PK) and insert a row intoEAPackageItems.

I'm inserting rows from a Table calledEATemplateItems.

So far I have something like this: (I have the PackageID already at the start of the query).

 INSERT INTO EAItems(Description, Recommendation, HeadingID)
SELECT Description, Recommendation, HeadingID
FROM EATemplateItemsWHERE EATemplateItems.TemplateID = @.TemplateIDINSERT INTO EAPackageItems(ItemID, PackageID) ...

I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.Any Advice / help would rock! Thanks

I think you will want to do this as a stored procedure. As you insert an individual row you can use the @.@.IDENTITY variable for the last inserted row. You could save that to a variable and insert the record to the second table. In your first query you could adjust it to select the rows into a table variable and then loop over the rows in the table variable and use that loop to take care of your individual inserts.

The T-SQL snippet below is the basic structure for what I am describing.

DECLARE @.MyTableTABLE(IDint IDENTITY,Name varchar(20))INSERT INTO @.MyTable (Name)SELECT NameFROM OtherTableDECLARE @.CurIDintDECLARE @.MaxIDintDECLARE @.RowIDintSET @.MaxID = (SELECT MAX(ID)FROM @.MyTable )SET @.CurID = 1WHILE (@.CurID <= @.MaxID)BEGIN-- use CurID to access the row in @.MyTable-- do your insert-- get the @.@.IDENTITY-- use that value for the next insert-- be sure to increment the @.CurID to the next rowSET @.CurID = @.CurID + 1END

|||

Thanks for the reply.

I'll work with that when I get to work - it seems logically straight forward. The script you put down can work in both SQL 2000 and SQL 2005 right? I hope so :D

|||Yes, there is nothing specific in there for SQL Server 2005.

Monday, March 12, 2012

Help with Query

Hello and Thanks in advance,

I am trying to get the percentage that each row contributes to the total rows for a given time frame on a given line (Select ScrapCat, ScrapLbs/Sum(ScrapLbs ... Group by Category, LineNum) as percentage). We have 11 categories for each line for each day. The percentage for each category would be the sum of all rows for that category for that line that time frame divided the sum of all rows for all categories for that line and time frame.

A return would look like this
Category......ProLine.......ScrapLbs.....Sum( ScrapLbs)...Percentage...
CateA----1-----.25----2.0-----.125--
CateB----1-----.35----2.0-----.175--
CateC----1-----.5----2.0-----.25--
etc
CateA----2-----.25----1.0-----.25--
CateB----2-----.50----1.0-----.5--
etc
Table looks like this

ProDate ..............smalldatetime
ProLine ...............int
Category.............char
ScrapLbs ............float
ProShift ..............int

is this possible?
Thanks,
LeeCan you post the DDL and some sample base data

Like CREATE TABLE myTable99 (Col1 int, ..ect

And sample data that would put the data in to the table, like

INSERT INTO myTable99 (Col1, col2, ect)
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada

That way we can execute the code, set up a test bed and figure it out...

but this kinda throws me right away..

ScrapLbs.....Sum(ScrapLbs)...

How can you have the sum of something, and 1 occurance of something on the same row?|||Sorry my boss shifted my focus!

Hopefully this is what you need.

What I need to do is sum all the scrap for each line for the date range (Sum( lbs) as LineTotal group by line then sum(Category) as EachCategory group by line and Category then divide EachCategory by LineTotal

EachCategory/LineTotal = EachCategory is what percent of Total Line Scrap

Create Table tblScrap
{
thaDate smalldatetime
Category varchar 15
lbs float
LineNum int
Shift int
}
Insert tblScrap Values ( ' 10/29/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2004',DA , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/29/2005',DT , 0.5, 1 ,1)
Insert tblScrap Values ( ' 10/29/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2007',Longs , 3.4, 1 ,1)
Insert tblScrap Values ( ' 10/29/2008',Bent , 1.48, 1 ,1)
Insert tblScrap Values ( ' 10/29/2009',NTA , 4.44, 1 ,1)
Insert tblScrap Values ( ' 10/29/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2011',Caps , 2.36, 1 ,1)
Insert tblScrap Values ( ' 10/29/2012',Paper , 5.26, 1 ,1)
Insert tblScrap Values ( ' 10/29/2013',NAPS , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/28/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2004',DA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2005',DT , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2007',Longs , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2008',Bent , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2009',NTA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2011',Caps , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2012',Paper , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2013',NAPS , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2004',DA , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2005',DT , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2007',Longs , 7.16, 1 ,1)
Insert tblScrap Values ( ' 10/27/2008',Bent , 1.84, 1 ,1)
Insert tblScrap Values ( ' 10/27/2009',NTA , 2.24, 1 ,1)
Insert tblScrap Values ( ' 10/27/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2011',Caps , 3.92, 1 ,1)
Insert tblScrap Values ( ' 10/27/2012',Paper , 7.86, 1 ,1)
Insert tblScrap Values ( ' 10/27/2013',NAPS , 1.76, 1 ,1)|||I'm not sure I fully understood what you are looking for.
This query groups by date and category with the percentage for each category in relation to the total of the day.

SELECT thaDate,
Category,
SUM(lbs) ScrapLbs,
(SELECT NULLIF(SUM(lbs), 0) FROM tblScrap WHERE thaDate = TS.thaDate) SumScrapLbs,
SUM(lbs) / (SELECT NULLIF(SUM(lbs), 0) WHERE thaDate = TS.thaDate) Percentage
FROM tblScrap TS
GROUP BY thaDate, Category

Hope this helps.

Cheers,
Robert|||clinel,

Still not sure what you mean. sum(Category)? Category is a character field. Also, what date ranges?
This should get you started:

select LineCatTotals.LineNum, LineCatTotals.Category, LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select LineNum, sum(lbs) Linelbs from tblScrap group by LineNum) LineTotals
inner join (select LineNum, Category, sum(lbs) LineCatlbs from tblScrap group by LineNum, Category) LineCatTotals
on LineTotals.LineNum = LineCatTotals.LineNum

Add groupings by date or daterange if you want them.

blindman|||I'm sorry,
Category is the label for each category of scrap. I want to sum the lbs of scrap or each category would be a better term. Then I want to sum all lbs of scrap by line to get a line total and then divide the category total (for that line) by the line total to get the percent that each category contributes to the line total. Whew!

As far as date range goes, I will give the user the ability to give a beginning and ending date and I want to find the percentage for that date range.

My bad on the sum of category; I see now how I took a confusing thing and made it even more so.

Thanks for both the patience and help,
Lee|||Thanks to all,
It appears that what Blindman had sent me is what I needed. I was actually able to figure out where to set the critera for the date range. Now I just need to figure out what is going on because I have several reports that I think that this type query will fit the need.

Thanks again,
Lee|||If you'd like, post your final query and we can make sure you implemented the date-range criteria in the most efficient manner.

blindman|||As I am new to this and have had no formal and very little time to read very much, this is how I handled what you gave me.

I created a stored procedure (so I could set the critera for date range easily) and a I am allowing the user to set the begin and end and call it from an asp.

Here is how I handled the date range.

@.begdate smalldatetime, @.enddate smalldatetime
AS

select LineCatTotals.ProLine,
LineCatTotals.ScrapCat,
LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select ProLine, sum(Scraplbs) Linelbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine) LineTotals
inner join (select ProLine, ScrapCat, sum(Scraplbs) LineCatlbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine, ScrapCat) LineCatTotals
on LineTotals.ProLine = LineCatTotals.ProLine Order by LineCatTotals.ProLine
GO

Now that you are looking over this, is it possible to select a total from another table and divide the Line total (scraplbs) by the production total from another table (Select Sum(Production) From tblProduction Where EntryDate Between @.begdate AND @.enddate) LineCatTotals.LineCatlbs/Sum(Production) ? Both tables could be linked on ProLine.|||Looks good to me.

Yes, you can add more subqueries to do additional calculations. It is generally more efficient to run your process as a single query, but if the query gets too confusing then consider breaking it up into separate statements that load temporary tables or table variables with summarized data. Then finish with a query that links these temporary tables to get the answer you need.

blindman

Friday, March 9, 2012

Help with Query

I have to tables
Table A Columns:
IDNumber
Text
Existing

Table B Columns:
TableAForeignKeyID
TableBID
OtherInfo
Existing

I want to select all rows from Table A where Existing = 1.
I also want to select all rows from Table A where TableB refers to it and TableB.Existing = 1 (regardless of TableA.Existing).

Separately the queries are like:

Code Snippet

Select * from TableA where Existing = 1;

Select IDNumber,Text,TableA.Existing from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1;

How can I combine the two (or write a different query) to get both in a single result set?

Use the following query...

Code Snippet

Select

IDNumber

,Text

,TableA.Existing

from

TableA

FULL OUTER JOIN TableB

ON TableA.IDNumber = TableB.TableAForeignKeyID

and TableB.Existing = 1

and TableA.Existing = 1

|||

Code Snippet

Select * from TableA where Existing = 1
UNION ALL --or >> UNION if you don't want duplication
Select IDNumber,Text,TableA.Existing from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1

you could also use a left outer join
coding would vary on whether you want complete sets or an intersection|||Here's another query that would add a column that identifies whether there is additional information in TableB about any row item of TableA.

Code Snippet

Select *, '0' as ExtraInfo from TableA TA where Existing = 1 and not exists
(Select * from TableB TB, TableA where TA.IDNumber = TB.TableAForeignKeyID)
UNION
Select IDNumber,Text,TableA.Existing, '1' as ExtraInfo from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1

DaleJ wrote:

Code Snippet

Select * from TableA where Existing = 1
UNION ALL --or >> UNION if you don't want duplication
Select IDNumber,Text,TableA.Existing from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1
you could also use a left outer join
coding would vary on whether you want complete sets or an intersection

Wednesday, March 7, 2012

Help with outer join query please!

I have a MSDE query that includes a "left outer join..." clause. It runs fine in MSDE Query (a 3rd party GUI tool) and produces 12 rows. column 3 has some NULL values (because of the outer join).

But when I use the same query in an ASP.NET page, and display the result in a datagrid, it only displays 7 rows - the rows with the NULL value in column 3 do not display.

Is there a parameter somewhere in datagrid or dataset that I should be setting?

thank you someone!there should be an AllowDbNull afaik|||


private void AddNullAllowedColumn(){
 DataColumn myColumn;
 myColumn = new DataColumn("classID", System.Type.GetType("System.Int32"));
 myColumn.AllowDBNull = true;
 // Add the column to a new DataTable.
 DataTable myTable;
 myTable = new DataTable();
 myTable.Columns.Add(myColumn);
 }

maybe a foreach (DataColumn column in DataGrid.Columns) { or datatable.
you can specify that.

Monday, February 27, 2012

Help with most efficient column sorting technique

I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.
Thanks,
MattMJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
Huh'
Have u tried the ORDER BY clause?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||MJB skrev:

> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
> Thanks,
> Matt
I don't think there is a silver bullet for this, if the table is
updated also I guess you have to prioritize between the columns and add
indexes on the ones that must be sorted fast.
Hopefully someone else knows better.
/impslayer, aka Birger Johansson|||Have you ever tried an ORDER BY on a non-index column that has 4 million
plus rows... I can tell you it ain't fast...
MGFoster wrote:
> MJB wrote:
> Huh'
> Have u tried the ORDER BY clause?|||Just so I understand, you're exposing 4 million + rows of data in a
paged data grid, and your users want t obe able to sort this data?
My first question is: do they really need to see all 4 million rows of
data? How in the hell is that practical?
Stu|||Well, it's a database that stores Ethernet traffic information. Kinda
what you might get out of Snort or something similar. In most cases the
data will be filtered a bit, but in some cases they may want to "see all
the data" (in the sense that it's paged and globally sortable). The
problem is they want to be able to sort on the ip col, the port col,
date time stamps etc (like i said there are 35+ cols in this table).
Currently only the primary key col is indexed, but it doesn't make sense
to index all of the others. Was wondering if anyone had dealt with this
before - doesn't sound like it.
Stu wrote:
> Just so I understand, you're exposing 4 million + rows of data in a
> paged data grid, and your users want t obe able to sort this data?
> My first question is: do they really need to see all 4 million rows of
> data? How in the hell is that practical?
> Stu
>
MJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.|||Actually, the company I work for manages network appliances for our
customers; the way we address the issue is bring the data off the
server onto the client pc, and let them sort it on their own pc (using
ADO.NET dataasets). We warn them if the data is going to be larger
than a few thousand rows, so they can decline, and only sample the
appropriate amount.
The largest collection of related events processed so far has been
about 200,000; it took about 3 minutes to load to the client's pc, and
then they had to deal with sorting issues. At least that way, the
entire server was not bogged down for one incident.
HTH,
Stu|||Standard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
..
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;

Help with most efficient column sorting technique

I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.
Thanks,
Matt
MJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
Huh?
Have u tried the ORDER BY clause?
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
|||MJB skrev:

> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
> Thanks,
> Matt
I don't think there is a silver bullet for this, if the table is
updated also I guess you have to prioritize between the columns and add
indexes on the ones that must be sorted fast.
Hopefully someone else knows better.
/impslayer, aka Birger Johansson
|||Have you ever tried an ORDER BY on a non-index column that has 4 million
plus rows... I can tell you it ain't fast...
MGFoster wrote:
> MJB wrote:
> Huh?
> Have u tried the ORDER BY clause?
|||Just so I understand, you're exposing 4 million + rows of data in a
paged data grid, and your users want t obe able to sort this data?
My first question is: do they really need to see all 4 million rows of
data? How in the hell is that practical?
Stu
|||Well, it's a database that stores Ethernet traffic information. Kinda
what you might get out of Snort or something similar. In most cases the
data will be filtered a bit, but in some cases they may want to "see all
the data" (in the sense that it's paged and globally sortable). The
problem is they want to be able to sort on the ip col, the port col,
date time stamps etc (like i said there are 35+ cols in this table).
Currently only the primary key col is indexed, but it doesn't make sense
to index all of the others. Was wondering if anyone had dealt with this
before - doesn't sound like it.
Stu wrote:
> Just so I understand, you're exposing 4 million + rows of data in a
> paged data grid, and your users want t obe able to sort this data?
> My first question is: do they really need to see all 4 million rows of
> data? How in the hell is that practical?
> Stu
>
MJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.
|||Actually, the company I work for manages network appliances for our
customers; the way we address the issue is bring the data off the
server onto the client pc, and let them sort it on their own pc (using
ADO.NET dataasets). We warn them if the data is going to be larger
than a few thousand rows, so they can decline, and only sample the
appropriate amount.
The largest collection of related events processed so far has been
about 200,000; it took about 3 minutes to load to the client's pc, and
then they had to deal with sorting issues. At least that way, the
entire server was not bogged down for one incident.
HTH,
Stu
|||Standard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
...
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
.. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;

Friday, February 24, 2012

Help With Making A Query

Hello,
With the following tables how would I create a query that would return all
rows from the Customers Table that have an entry in the Activity Table?
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
TableName: Activity
ID - Integer
CustomerID - VarChar
Extension - Bit
- Customer 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
- Activity Table
ID CustomerID Extension
1 Chuck1 1
3 James 0
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 *
from Customers
inner join Activity on Activity.CustomerID =Customers.CustomerID

Sunday, February 19, 2012

Help with layout of a 'details' report

I'm trying to layout a report with the data from specific rows placed
in TextBoxes, as I want to use the report for printing out details.
My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
QuestionID Answer
--
100 Some text
101 31-Jul-07
102 More text
etc.
I want to position the Answer for a particular QuestionID in a
TextBox, as follows:
[Answer for QuestionId 100] [Answer for QuestionId 101]
[ Answer for Question Id 102
]
I need to be able to vary the position and size of each TextBox, which
I'm not sure I can do if I embed them in a table/list.
I know that I can pivot the data in the query before I fill the
dataset, which would give me a single row with a column for each
question. However I'm dealing with about 100 question id and I was
hoping to avoid the complex pivot query etc (even using SQL Server
2005 Pivot function).
Cheers,
Canice.On Jul 31, 9:19 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> I'm trying to layout a report with the data from specific rows placed
> in TextBoxes, as I want to use the report for printing out details.
> My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> QuestionID Answer
> --
> 100 Some text
> 101 31-Jul-07
> 102 More text
> etc.
> I want to position the Answer for a particular QuestionID in a
> TextBox, as follows:
> [Answer for QuestionId 100] [Answer for QuestionId 101]
> [ Answer for Question Id 102
> ]
> I need to be able to vary the position and size of each TextBox, which
> I'm not sure I can do if I embed them in a table/list.
> I know that I can pivot the data in the query before I fill the
> dataset, which would give me a single row with a column for each
> question. However I'm dealing with about 100 question id and I was
> hoping to avoid the complex pivot query etc (even using SQL Server
> 2005 Pivot function).
> Cheers,
> Canice.
>From what you have described, you may want to have the report broken
into columns. You should use a table control and then follow these
steps to limit the records in the table for column handling.
1. In the 'Layout' view of BIDS, select the table.
2. Right click the table and select 'Properties.'
3. On the 'Groups' tab, select the 'Add...' button.
4. Below 'Group on:' and 'Expression' enter the following:
'=Ceiling(RowNumber(Nothing)/18)'
5. Select 'Page break at end'
5. Select 'OK' and 'OK' again.
[NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
NumberOfRowsPerColumn) ]
Then, to set the number of columns, you would do the following:
1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
2. Select 'Report Properties...' from the drop-down menu.
3. Select the 'Layout' tab.
4. Below 'Columns:' select the number of columns to split the report
into.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
>
> > I'm trying to layout a report with the data from specific rows placed
> > in TextBoxes, as I want to use the report for printing out details.
> > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > QuestionID Answer
> > --
> > 100 Some text
> > 101 31-Jul-07
> > 102 More text
> > etc.
> > I want to position the Answer for a particular QuestionID in a
> > TextBox, as follows:
> > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > [ Answer for Question Id 102
> > ]
> > I need to be able to vary the position and size of each TextBox, which
> > I'm not sure I can do if I embed them in a table/list.
> > I know that I can pivot the data in the query before I fill the
> > dataset, which would give me a single row with a column for each
> > question. However I'm dealing with about 100 question id and I was
> > hoping to avoid the complex pivot query etc (even using SQL Server
> > 2005 Pivot function).
> > Cheers,
> >Canice.
> >From what you have described, you may want to have the report broken
> into columns. You should use a table control and then follow these
> steps to limit the records in the table for column handling.
> 1. In the 'Layout' view of BIDS, select the table.
> 2. Right click the table and select 'Properties.'
> 3. On the 'Groups' tab, select the 'Add...' button.
> 4. Below 'Group on:' and 'Expression' enter the following:
> '=Ceiling(RowNumber(Nothing)/18)'
> 5. Select 'Page break at end'
> 5. Select 'OK' and 'OK' again.
> [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> NumberOfRowsPerColumn) ]
> Then, to set the number of columns, you would do the following:
> 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> 2. Select 'Report Properties...' from the drop-down menu.
> 3. Select the 'Layout' tab.
> 4. Below 'Columns:' select the number of columns to split the report
> into.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Hi Enrique,
Thats for the reply but I don't think your solution is what I was
looking for.
>From my (very basic) knowledge of SSRS, your solution will wrap the
DataSet returned into a number of columns/rows on multiple pages.
What I was looking for is a more free-form layout, where I can place
TextBox fields for specific answers anywhere on the form (this is for
a print layout). The expression for each TextBox would be as follows:
=IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
Where the value 42231 represents a particular answer, and each TextBox
would have a different question id.
I also need all the results on a single page, again so that I can use
it for printing.
Thanks,
Canice.|||On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > I'm trying to layout a report with the data from specific rows placed
> > > in TextBoxes, as I want to use the report for printing out details.
> > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > QuestionID Answer
> > > --
> > > 100 Some text
> > > 101 31-Jul-07
> > > 102 More text
> > > etc.
> > > I want to position the Answer for a particular QuestionID in a
> > > TextBox, as follows:
> > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > [ Answer for Question Id 102
> > > ]
> > > I need to be able to vary the position and size of each TextBox, which
> > > I'm not sure I can do if I embed them in a table/list.
> > > I know that I can pivot the data in the query before I fill the
> > > dataset, which would give me a single row with a column for each
> > > question. However I'm dealing with about 100 question id and I was
> > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > 2005 Pivot function).
> > > Cheers,
> > >Canice.
> > >From what you have described, you may want to have the report broken
> > into columns. You should use a table control and then follow these
> > steps to limit the records in the table for column handling.
> > 1. In the 'Layout' view of BIDS, select the table.
> > 2. Right click the table and select 'Properties.'
> > 3. On the 'Groups' tab, select the 'Add...' button.
> > 4. Below 'Group on:' and 'Expression' enter the following:
> > '=Ceiling(RowNumber(Nothing)/18)'
> > 5. Select 'Page break at end'
> > 5. Select 'OK' and 'OK' again.
> > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > NumberOfRowsPerColumn) ]
> > Then, to set the number of columns, you would do the following:
> > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > 2. Select 'Report Properties...' from the drop-down menu.
> > 3. Select the 'Layout' tab.
> > 4. Below 'Columns:' select the number of columns to split the report
> > into.
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Hi Enrique,
> Thats for the reply but I don't think your solution is what I was
> looking for.
> >From my (very basic) knowledge of SSRS, your solution will wrap the
> DataSet returned into a number of columns/rows on multiple pages.
> What I was looking for is a more free-form layout, where I can place
> TextBox fields for specific answers anywhere on the form (this is for
> a print layout). The expression for each TextBox would be as follows:
> =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> Where the value 42231 represents a particular answer, and each TextBox
> would have a different question id.
> I also need all the results on a single page, again so that I can use
> it for printing.
> Thanks,
> Canice.
Unfortunately, this type of layout is not supported in SSRS. Aside
from the text boxes being individually placed (as you suggested) and
using expression references to the same or different datasets, there
are not very many options available. In order to make the expressions
work that you mentioned above, you will need to use aggregates (i.e.,
=IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
Another option, but still not free form, is to use a table control and
play w/the standard expressions and border styles based on expressions
to give the appearance of free form. Of course, this can be a bit
tricky and will not completely fit your purpose. The closest thing I
can think of to your desired outcome (if PDF export is an option), is
an open source library alternative (iTextSharp) which has some good
documentation (http://sourceforge.net/projects/itextsharp/
http://itextsharp.sourceforge.net/tutorial/ ); however, you will need
to be comfortable w/C# or VB.NET and use the library as part of a
custom application. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 2, 12:16 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
>
> > On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> > > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > > I'm trying to layout a report with the data from specific rows placed
> > > > in TextBoxes, as I want to use the report for printing out details.
> > > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > > QuestionID Answer
> > > > --
> > > > 100 Some text
> > > > 101 31-Jul-07
> > > > 102 More text
> > > > etc.
> > > > I want to position the Answer for a particular QuestionID in a
> > > > TextBox, as follows:
> > > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > > [ Answer for Question Id 102
> > > > ]
> > > > I need to be able to vary the position and size of each TextBox, which
> > > > I'm not sure I can do if I embed them in a table/list.
> > > > I know that I can pivot the data in the query before I fill the
> > > > dataset, which would give me a single row with a column for each
> > > > question. However I'm dealing with about 100 question id and I was
> > > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > > 2005 Pivot function).
> > > > Cheers,
> > > >Canice.
> > > >From what you have described, you may want to have the report broken
> > > into columns. You should use a table control and then follow these
> > > steps to limit the records in the table for column handling.
> > > 1. In the 'Layout' view of BIDS, select the table.
> > > 2. Right click the table and select 'Properties.'
> > > 3. On the 'Groups' tab, select the 'Add...' button.
> > > 4. Below 'Group on:' and 'Expression' enter the following:
> > > '=Ceiling(RowNumber(Nothing)/18)'
> > > 5. Select 'Page break at end'
> > > 5. Select 'OK' and 'OK' again.
> > > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > > NumberOfRowsPerColumn) ]
> > > Then, to set the number of columns, you would do the following:
> > > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > > 2. Select 'Report Properties...' from the drop-down menu.
> > > 3. Select the 'Layout' tab.
> > > 4. Below 'Columns:' select the number of columns to split the report
> > > into.
> > > Hope this helps.
> > > Regards,
> > > Enrique Martinez
> > > Sr. Software Consultant
> > Hi Enrique,
> > Thats for the reply but I don't think your solution is what I was
> > looking for.
> > >From my (very basic) knowledge of SSRS, your solution will wrap the
> > DataSet returned into a number of columns/rows on multiple pages.
> > What I was looking for is a more free-form layout, where I can place
> > TextBox fields for specific answers anywhere on the form (this is for
> > a print layout). The expression for each TextBox would be as follows:
> > =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> > Where the value 42231 represents a particular answer, and each TextBox
> > would have a different question id.
> > I also need all the results on a single page, again so that I can use
> > it for printing.
> > Thanks,
> > Canice.
> Unfortunately, this type of layout is not supported in SSRS. Aside
> from the text boxes being individually placed (as you suggested) and
> using expression references to the same or different datasets, there
> are not very many options available. In order to make the expressions
> work that you mentioned above, you will need to use aggregates (i.e.,
> =IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
> Another option, but still not free form, is to use a table control and
> play w/the standard expressions and border styles based on expressions
> to give the appearance of free form. Of course, this can be a bit
> tricky and will not completely fit your purpose. The closest thing I
> can think of to your desired outcome (if PDF export is an option), is
> an open source library alternative (iTextSharp) which has some good
> documentation (http://sourceforge.net/projects/itextsharp/http://itextsharp.sourceforge.net/tutorial/); however, you will need
> to be comfortable w/C# or VB.NET and use the library as part of a
> custom application. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Thanks again Enrique, at least you've eliminated that possibility.
I'm a C# developer so using the iTextSharp API shouldn't be a problem,
however I was hoping to avoid going down the PDF route from past
experiences.
Looks like I might have to do the pivot operation after all, or use
ASP.NET to do the layout (its from a web-app).
Cheers,
Canice.|||On Aug 2, 4:06 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> On Aug 2, 12:16 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> > > On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> > > > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > > > I'm trying to layout a report with the data from specific rows placed
> > > > > in TextBoxes, as I want to use the report for printing out details.
> > > > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > > > QuestionID Answer
> > > > > --
> > > > > 100 Some text
> > > > > 101 31-Jul-07
> > > > > 102 More text
> > > > > etc.
> > > > > I want to position the Answer for a particular QuestionID in a
> > > > > TextBox, as follows:
> > > > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > > > [ Answer for Question Id 102
> > > > > ]
> > > > > I need to be able to vary the position and size of each TextBox, which
> > > > > I'm not sure I can do if I embed them in a table/list.
> > > > > I know that I can pivot the data in the query before I fill the
> > > > > dataset, which would give me a single row with a column for each
> > > > > question. However I'm dealing with about 100 question id and I was
> > > > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > > > 2005 Pivot function).
> > > > > Cheers,
> > > > >Canice.
> > > > >From what you have described, you may want to have the report broken
> > > > into columns. You should use a table control and then follow these
> > > > steps to limit the records in the table for column handling.
> > > > 1. In the 'Layout' view of BIDS, select the table.
> > > > 2. Right click the table and select 'Properties.'
> > > > 3. On the 'Groups' tab, select the 'Add...' button.
> > > > 4. Below 'Group on:' and 'Expression' enter the following:
> > > > '=Ceiling(RowNumber(Nothing)/18)'
> > > > 5. Select 'Page break at end'
> > > > 5. Select 'OK' and 'OK' again.
> > > > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > > > NumberOfRowsPerColumn) ]
> > > > Then, to set the number of columns, you would do the following:
> > > > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > > > 2. Select 'Report Properties...' from the drop-down menu.
> > > > 3. Select the 'Layout' tab.
> > > > 4. Below 'Columns:' select the number of columns to split the report
> > > > into.
> > > > Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > > Hi Enrique,
> > > Thats for the reply but I don't think your solution is what I was
> > > looking for.
> > > >From my (very basic) knowledge of SSRS, your solution will wrap the
> > > DataSet returned into a number of columns/rows on multiple pages.
> > > What I was looking for is a more free-form layout, where I can place
> > > TextBox fields for specific answers anywhere on the form (this is for
> > > a print layout). The expression for each TextBox would be as follows:
> > > =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> > > Where the value 42231 represents a particular answer, and each TextBox
> > > would have a different question id.
> > > I also need all the results on a single page, again so that I can use
> > > it for printing.
> > > Thanks,
> > > Canice.
> > Unfortunately, this type of layout is not supported in SSRS. Aside
> > from the text boxes being individually placed (as you suggested) and
> > using expression references to the same or different datasets, there
> > are not very many options available. In order to make the expressions
> > work that you mentioned above, you will need to use aggregates (i.e.,
> > =IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
> > Another option, but still not free form, is to use a table control and
> > play w/the standard expressions and border styles based on expressions
> > to give the appearance of free form. Of course, this can be a bit
> > tricky and will not completely fit your purpose. The closest thing I
> > can think of to your desired outcome (if PDF export is an option), is
> > an open source library alternative (iTextSharp) which has some good
> > documentation (http://sourceforge.net/projects/itextsharp/http://itextsharp.sourcefo...however, you will need
> > to be comfortable w/C# or VB.NET and use the library as part of a
> > custom application. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Thanks again Enrique, at least you've eliminated that possibility.
> I'm a C# developer so using the iTextSharp API shouldn't be a problem,
> however I was hoping to avoid going down the PDF route from past
> experiences.
> Looks like I might have to do the pivot operation after all, or use
> ASP.NET to do the layout (its from a web-app).
> Cheers,
> Canice.
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Friday, March 30, 2012

Help with SQL query please

Help With SQL Query

Wednesday, March 28, 2012

Help with Sproc and multi parameter

Friday, March 23, 2012

Help with Select to return Duplicate rows

Help with SELECT query

Help with SELECT please

help with select

help with select

Monday, March 19, 2012

Help with query NOT IN

help with query from a sql newbie

Help with Query - Insert multiple rows and link between tables.

Monday, March 12, 2012

Help with Query

Friday, March 9, 2012

Help with Query

Wednesday, March 7, 2012

Help with outer join query please!

Monday, February 27, 2012

Help with most efficient column sorting technique

Help with most efficient column sorting technique

Friday, February 24, 2012

Help With Making A Query

Sunday, February 19, 2012

Help with layout of a 'details' report

Max Query

Blog Archive

About Me