question about selecting records

I have a mssql database like this:

Table1:
ID GuestName Agent CheckInDate OtherColumn
1 Guest 1 Peter 1/11/2005 whatever
2 Guest 2 Peter 1/11/2005 whatever
3 Guest 3 John 1/11/2005 whatever
4 Guest 4 John 1/13/2005 whatever
5 Guest 5 Peter 1/11/2005 whatever

I want to display it group by same Agent and CheckInDate. Like this:

Agent CheckInDate Records
Peter 1/11/2005 3
John 1/11/2005 1
John 1/13/2005 1

How can I do this? Please help, thank you.

select agent,checkindate,count(agent) Records
from <table>
group by agent,checkindate

|||Thank you. And there's another question. The CheckInDate values has time value in it. for example, 1/11/2005 1:15:45PM. If I use GROUP BY CheckInDate, the time value matters. How can ingore time value and just GROUP BY date value?|||cast or convert it to a varchar|||CONVERT(VARCHAR(12), myDateColumn, 101)

Question about move large amount of data from database to database

guys,

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)

Thanks a lot!On Apr 23, 2:23 pm, Lee <lee.jenkins...@.gmail.comwrote:

Quote:

Originally Posted by

guys,
>
I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).
>
My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)
>
Thanks a lot!

Use a Select Into statement and make sure the destination db is set to
a simple recovery model.|||Yes, BCP will be a good option for fast data transfer. All of the BULK
operations (BULK INSERT, SELECT INTO, BCP) are minimally logged when a non
FULL recovery model is set.

Another issue could be the purging of the archived records from your main
table. If you have it as a single DELETE and it takes long time to complete,
then you can break it into smaller DELETE chunks.

If you have SQL Server 2005 Enterprise Edition, an interesting alternative
is to use partitioned tables. Specifically range partitions based on date
ranges (in your case could be weekly) can help with archiving. Take a look
at the following article (in particular the section about Range Partitions):
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Lee (lee.jenkins.ca@.gmail.com) writes:

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?

Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Plamen, Thanks a lot. I will try it and let you know the result.
Thanks again!

On Apr 23, 2:08 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Yes, BCP will be a good option for fast data transfer. All of the BULK
operations (BULK INSERT, SELECT INTO, BCP) are minimally logged when a non
FULL recovery model is set.
>
Another issue could be the purging of the archived records from your main
table. If you have it as a single DELETE and it takes long time to complete,
then you can break it into smaller DELETE chunks.
>
If you have SQL Server 2005 Enterprise Edition, an interesting alternative
is to use partitioned tables. Specifically range partitions based on date
ranges (in your case could be weekly) can help with archiving. Take a look
at the following article (in particular the section about Range Partitions):http://msdn2.microsoft.com/en-us/library/ms345146.aspx
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||Erland, Thanks a lot for the reply, also forgot to say thanks to Brad,
Here is the table:

CREATE TABLE [dbo].[tbl_record](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[record_CC_id] [int] NOT NULL,
[record_content] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_tbl_record_record_content] DEFAULT (''),
[record_date] [datetime] NOT NULL CONSTRAINT
[DF_tbl_record_record_date] DEFAULT (getdate()),
[record_ip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_tbl_record_record_ip] DEFAULT (''),
[record_active] [bit] NOT NULL CONSTRAINT
[DF_tbl_record_record_archive] DEFAULT (1),
CONSTRAINT [PK_tbl_record] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]

And The stored procedure is here:

ALTER PROCEDURE [dbo].[ArchiveRecords]
(
@.ddate datetime
)
AS
BEGIN TRAN
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive ON;
INSERT INTO record_archive.dbo.tbl_record_archive
(
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
)
SELECT
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
FROM tbl_record WHERE record_date <= @.ddate;
DELETE FROM tbl_record WHERE record_date <= @.ddate;
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive OFF;
IF @.@.ERROR = 0 BEGIN COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN END

On Apr 23, 2:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Lee (lee.jenkins...@.gmail.com) writes:

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

>
It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?
>
Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Should I remove the clusterd index on the record_id field and create
nonclustered index on this field and create a clustered index on
record_date field since in my query, I always select a range of data
by date.

On Apr 23, 3:16 pm, Lee <lee.jenkins...@.gmail.comwrote:

Quote:

Originally Posted by

Erland, Thanks a lot for the reply, also forgot to say thanks to Brad,
Here is the table:
>
CREATE TABLE [dbo].[tbl_record](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[record_CC_id] [int] NOT NULL,
[record_content] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_tbl_record_record_content] DEFAULT (''),
[record_date] [datetime] NOT NULL CONSTRAINT
[DF_tbl_record_record_date] DEFAULT (getdate()),
[record_ip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_tbl_record_record_ip] DEFAULT (''),
[record_active] [bit] NOT NULL CONSTRAINT
[DF_tbl_record_record_archive] DEFAULT (1),
CONSTRAINT [PK_tbl_record] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]
>
And The stored procedure is here:
>
ALTER PROCEDURE [dbo].[ArchiveRecords]
(
@.ddate datetime
)
AS
BEGIN TRAN
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive ON;
INSERT INTO record_archive.dbo.tbl_record_archive
(
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
)
SELECT
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
FROM tbl_record WHERE record_date <= @.ddate;
DELETE FROM tbl_record WHERE record_date <= @.ddate;
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive OFF;
IF @.@.ERROR = 0 BEGIN COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN END
>
On Apr 23, 2:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>

Quote:

Originally Posted by

Lee (lee.jenkins...@.gmail.com) writes:

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

>

Quote:

Originally Posted by

It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?

>

Quote:

Originally Posted by

Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

>
- Show quoted text -

|||Brad, Thanks for the reply, my situation is the target table already
have lots of records and I will just append the data to that table.

On Apr 23, 1:58 pm, Brad <Brad.Marsh...@.Teksouth.comwrote:

Quote:

Originally Posted by

On Apr 23, 2:23 pm, Lee <lee.jenkins...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

guys,

>

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

>

Quote:

Originally Posted by

My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)

>

Quote:

Originally Posted by

Thanks a lot!

>
Use a Select Into statement and make sure the destination db is set to
a simple recovery model.- Hide quoted text -
>
- Show quoted text -

|||Lee (lee.jenkins.ca@.gmail.com) writes:

Quote:

Originally Posted by

Should I remove the clusterd index on the record_id field and create
nonclustered index on this field and create a clustered index on
record_date field since in my query, I always select a range of data
by date.

Yes, that was precisely my reaction when I saw the table. Make the primary
key on record_id non-clustered, and add a clustered index on the date
column. I would guess you should do this on the archive table as well.

Also, I don't see the point with having the IDENTITY property on the
archive table. Just make it a normal column, and you don't need that
SET IDENTITY_INSERT. Not that it affects performance, but it looks cleaner.
However to change this, you would need rename the existing table, create
it a new and copy over. There is no ALTER syntax for changing the
IDENTITY property.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Question about How Data can be rendered on Report

I have a dataset that is comprised of several records of employees leave
types and hours. Each leave type record is unique(sicks on record and
vacation another). 2 fields of each record,I group on (Employee and
TimePeriod) and the Leave type hours I want to display in separate columns.
What I am trying to do is group by Employee with a page break after each
employee(this was easy), then group by period(same page, this was done too),
but I also need to put the hours in different columns depending on the Leave
type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
CREATED.
i.e)
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 8
1/8/2005-1/15/2005 4 0
BUT INSTEAD I AM GETTING THIS
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 0
1/1/2005-1/8/2005 0 8
1/8/2005-1/15/2005 4 0
1/8/2005-1/15/2005 0 4
I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?Create the two text boxes for Sick and Vacation... For the values use an
expression like
for Sick
=Iif(Fields!LeaveType.Value="Sick",Hours,0)
for Leave textbox
=Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
too),
> but I also need to put the hours in different columns depending on the
Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||I used the Iif statement you wrote. It works fine, but what I don't want it
to do is to put the next record in the dataset onto the next row. I want the
next record to go into the next column as in previous post example. I only
want the next dataset record to go into the next row if the group changes.
See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values use an
> expression like
> for Sick
> =Iif(Fields!LeaveType.Value="Sick",Hours,0)
> for Leave textbox
> =Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees leave
> > types and hours. Each leave type record is unique(sicks on record and
> > vacation another). 2 fields of each record,I group on (Employee and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break after each
> > employee(this was easy), then group by period(same page, this was done
> too),
> > but I also need to put the hours in different columns depending on the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Create a grouping on Employee and DateRange and drop the Employee name, =date range (week, whatever it's called), and the Sum() for Vacation and =Sick in the Group Footer. Make sure you use the group name as the scope =for the Sum (IOW, =3DSum(Fields!SickTime.Value, "GroupName") where ="GroupName" is the name fo the group).
You won't have any values dropping into the detail rows and you can =place the Employee Name in the group header row if you don't want it to =repeat for each row. Make sure you also sort the data set by Employee =and Time.
HTH.
-- TIM ELLISON
"dillig" <dillig@.discussions.microsoft.com> wrote in message =news:1C382976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...
I used the Iif statement you wrote. It works fine, but what I don't =want it to do is to put the next record in the dataset onto the next row. I =want the next record to go into the next column as in previous post example. I =only want the next dataset record to go into the next row if the group =changes. See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values =use an
> expression like
> for Sick
> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)
> for Leave textbox
> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)
> > Hope this helps...
> > -- > Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> > I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> > "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees =leave
> > types and hours. Each leave type record is unique(sicks on record =and
> > vacation another). 2 fields of each record,I group on (Employee =and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break =after each
> > employee(this was easy), then group by period(same page, this was =done
> too),
> > but I also need to put the hours in different columns depending on =the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT =RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I =WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE =HELP'?
> >
> > >
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Create a grouping on Employee and =DateRange and drop the Employee name, date range (week, whatever it's called), and the =Sum() for Vacation and Sick in the Group Footer. Make sure you use the =group name as the scope for the Sum (IOW, ==3DSum(Fields!SickTime.Value, "GroupName") where "GroupName" is the name fo the =group).
You won't have any values dropping into =the detail rows and you can place the Employee Name in the group header row if you =don't want it to repeat for each row. Make sure you also sort the data set by =Employee and Time.
HTH.
-- TIM ELLISON
"dillig" wrote in message news:1C3=82976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...I used the Iif statement you wrote. It works fine, but what I don't want =it to do is to put the next record in the dataset onto the next row. =I want the next record to go into the next column as in previous post =example. I only want the next dataset record to go into the next row if the =group changes. See the example for clarification."Wayne Snyder" wrote:> Create the two text boxes for Sick and Vacation... =For the values use an> expression like> for Sick> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)> for Leave =textbox> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)> > =Hope this helps...> > -- > Wayne Snyder, MCDBA, SQL Server MVP> Mariner, Charlotte, NC>">http://www.mariner-usa.com">www.mariner-usa.com> =(Please respond only to the newsgroups.)> > I support the =Professional Association of SQL Server (PASS) and it's> community of SQL =Server professionals.>">http://www.sqlpass.org">www.sqlpass.org> > ="dillig" wrote in message> news:889=F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...> > I have a dataset that is comprised of several records of =employees leave> > types and hours. Each leave type record is =unique(sicks on record and> > vacation another). 2 fields of each record,I =group on (Employee and> > TimePeriod) and the Leave type hours I want =to display in separate> columns.> > What I am trying to =do is group by Employee with a page break after each> > =employee(this was easy), then group by period(same page, this was done> =too),> > but I also need to put the hours in different columns depending =on the> Leave> > type. BUT I DONT WANT IT TO CREATE A =NEW ROW. I WANT THE NEXT RECORD TO GO> IN> > THE NEXT COLUMN =UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW> ROW> => CREATED.> > i.e)> = > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> >> >> > BUT INSTEAD I AM GETTING =THIS> > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 0> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =0 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =0 = 4> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. =CAN ANYONE HELP'?> >> > =>

---

--=_NextPart_000_01AB_01C4F4BE.37F23400--|||Have you tried using a Matrix instead of a table? Put time in the rows and
leave type in the columns.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
>I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
> columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
> too),
> but I also need to put the hours in different columns depending on the
> Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||Thanks to all who replied to this post. Creating a second grouping from
timeperiod and putting the totals for the inside group in group2's footer
worked great.
Your guys are awesome.
"dillig" wrote:
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done too),
> but I also need to put the hours in different columns depending on the Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>

Question about get multiple records !

I have table with format :

ID Name ParentID
1 England 0
2 Canada 0
3 Manchester 1
4 Chelsi 1
5 Arsenal 1
6 Canada_1 2
7 Canada_2 2
8 MU_1 3
9 MU_2 3
10 MU_1_1 8
...

Now , I write a function which to get information ,such as :
England -- Manchester --MU_1--MU_1_1(*)
My Function :
FunctionGetInformation(ID as int16) as string
' my code here
End function

My problem : (With ID=10)
When i pass this ID , i have to get string which format :(*)
So , what is " my code here " ?
Thank any suggestions ,any advices ...
And thank you very much.

Could you explain what you are looking for in a bit more detail?

|||

I store data in "tree" form .
Example :
+England--Manchester--Manchester1--Manchester1_1 .....
+A--A1--A1_1--A1_1_1 ....
so on !
And i design above table.
My problem :
When i know of node of tree , i can not identify path from root .
such as , i know Manchester1_1 , how can indentify the string :
+England--Manchester--Manchester1--Manchester1_1 .....
You understand me !!
Thank you very much .

|||

Any one help me ? or my bad explaining !
Thank you very much.

|||

CREATE FUNCTION dbo.fnSplit
(
@.List nvarchar(2000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@.SplitOn,@.List)>0)
BEGIN
INSERT INTO
@.RtnValue
(
value
)
SELECT
LTRIM(RTRIM(SUBSTRING(@.List,1,CHARINDEX(@.SplitOn,@.List)-1)))
SET @.List = SUBSTRING(@.List,CHARINDEX(@.SplitOn,@.List)+LEN(@.SplitOn),LEN(@.List))
END
INSERT INTO
@.RtnValue
(
Value
)
SELECT LTRIM(RTRIM(@.List))
RETURN
END

and then use sp or dynamic sql like this

SELECT {something}
FROM {sometable}

INNER JOIN dbo.fnSplit({coma delimited list of ids},{delimter-in this case comma) A ON [dbo].sometable.your id= A.Value

|||

thanh for your guide !! (very much )
It's perfect if you have some comment or explainning what you writting !!
Thank you .

|||I did not really read what you need, other then the heading "get multiple records". This above allows you to pass in a comma delimited list of IDs and return the records back from the stored procedure.|||

There is probably a better way of doing this, and I'm coding this from memory, so some of it may not be 100% correct, but the idea is sound.

function GetInformation(ID as int16) as string

if id<>0 then

dim conn as new sqlConnection(...)

dim cmd as new sqlCommand("SELECT * FROM MyTable WHERE ID=" & ID.tostring)

conn.open

dim dr as SqlDataReader =cmd.Execute(conn)

dim parentID as string =dr("ParentID")

dim n as string=dr("Name")

dr.close

conn.close

return GetInformation(parentID) & n & "--"

else

return ""

end if

end function

|||Thank all !!
To Motley :
You put connection string in function ! and call it each loop !
Performance is good ?|||

It's not the best thing to do, no. In my projects I would have put the connection someplace else. Depending on the dataset size, I might have read the whole thing either into a dataset or a strongly typed collection, and did all my processing there. Depends on how much you need to process and how much data there is.

question about display mask & select number N to M records

Two questions:
1. With SQL, how can I setup display mask for value:
eg. display 100000 as $100,000
2. How can I select number N to M records in a table.
eg. select No. 50 - 100 records from a table. ( not top 50)
Thanks,
Guyang> 1. With SQL, how can I setup display mask for value:
> eg. display 100000 as $100,000
(a) I wouldn't rely on Enterprise Manager for data viewing / modification...
use a development tool for that, like Query Analyzer.
(b) there is no such thing as a "display mask" in SQL Server... this is
something that cute GUIs do. The data is not stored that way; if you want
it to be *presented* that way, write a view, e.g. SELECT CONVERT(VARCHAR,
moneyColumn, 1) FROM table
> 2. How can I select number N to M records in a table.
> eg. select No. 50 - 100 records from a table. ( not top 50)
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC
If you really need it to come back 50 -> 100, then
SELECT * FROM
(
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC) y
ORDER BY some_column

question about database design

I have a table, named car. It normally have 10000 records and will growth in
the furture.
Administrator can delete the car record through an web based administrator
platform
And we not like delete the car record physically.
My question is that, what is the best method to keep the database
performance.
1. add additional field, which called is_deleted, in the car table, and mark
it as 'T' if the record is deleted, and 'F' when the record is active.
2. create new table, which have the same as schema as car table, and named
deleted_car, when administrator delete a car record, the program will remove
a record from car table and insert it into the deleted_car table.
Thanks~I prefer the first one as there is no need for additional table
Madhivanan|||> My question is that, what is the best method to keep the database
> performance.
Make sure that you have properly designed database.
Make sure that you have properly defined indexes on the tables.
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
> I have a table, named car. It normally have 10000 records and will growth
in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||Either method will work. For a table that small either method will work
well.
If the table starts to get into the hundreds of thousands of records you
will see a performance improvment (small) by using two tables. If you get
into the millions of records it will start to actuall make a difference...
This is assuming that you are using indexes correctly. If you are not, then
having 2 tables will have a large speed improvement even with the smaller
record sets.
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I have a table, named car. It normally have 10000 records and will growth
>in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
> mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
> remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||The second option might cause you some maintenance headaches down the
road... If you update your application you have to make sure you apply
updates to both sets of tables, your application will need to be modified to
work with the changes in both sets of tables, etc. What if someone
accidentally "deletes" a record and has to bring it back/make it active
again? What if, somehow, a single item gets copied to both tables?
For 10,000 -- or even 100,000 -- records I don't think it would be worth the
hassle to split it up.
Thx
Mike C
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I have a table, named car. It normally have 10000 records and will growth
>in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
> mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
> remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||right~~|||hehe~~
right...|||thanks~
^.^|||thanks~
you made my mind clear again~|||>> 1. add additional field [sic], which called is_deleted, in the car
table, and mark it as 'T' if the record [sic] is deleted, and 'F' when
the record [sic] is active. <<
Rows are not records; columns are not fields; an RDBMS is not a
sequential file system. The basic idea is right, but make this
attribute a general status code that can take other values or make it a
timestamp so you know when the event occurred.

Question about counting records based on conditions

Hi all,
I'm doing some reports where counting records is extensively used; some of these counts have to be done based on some data in the returning records; for example, I've got the following data set:

Name Sex IsMember
-- -- ----
... M Y
... F N
... M N

and so on...

If I group my report based on 'Sex' field, for example, I would like my report to show group name (M or F), number of members in this group (1 male member) and number of non-members in this group (1 non-member male), so the report should be something like:

Male - 1 member and 1 non-member
... *records where Sex = M will go here* ...

Female - 0 member and 1 non member
... *records where sex = F will go here* ...

So, how can I do counting based on some conditions in the retured data within a group?

Any hint will be very much appreciated,

Thanks in advance,

Essamyou can create a formula field that calulates where the person is male or female and member or non-member

if sex = "M" and Member = "Y" then
"Male Member"
else sex = "M" and Member = "N" then
"Male Non-Member"

etc.

You can then create formulas to count the number of each value or you can create a cross tab report that will group them for you.|||Thanks abyerly for your reply, and sorry I saw your post just today!

Well, my problem was solved by this reply I got from someone called MrBill somewhere in the web :) and yes it is what I was looking for, and as I was doing it in groups, it was better to do it this way:

You would group on sex and create two formulas:
// formual @.MemberCount
If {table.IsMember} = "y" then 1 else 0

// formula @.NonMemberCount
If {table.IsMember} = "n" then 1 else 0

Place the formulas in the detail section. You can suppress them if you want. For each formula do the following:
Right click > Insert > Summary. In the "calculate this summary" box select 'sum'. In the summary location box select group 1 (sex). Click ok. This will place a sum of your formula fields in the group 1 footer. Drag the 'sum' fields to the group header if you want or leave in the footer.

question about best way to store an up or down value

I'm creating a table for maintenance records.

In each record many of the values are simply checkboxes.

In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?

-DavidWithout getting into design issues, the best way would be to use a BIT datatype, with 0 used to indicate FALSE or OFF, and 1 to indicate TRUE or ON.

Question

Hello all,

I have the following dataset returning top 20 records for a day, changing daily

STATE

USER ID

SALES REP

SALES

QLD

6758

Liam Maddrell

12

QLD

6677

Lisa-Maree Findlay

11

QLD

6133

Benjamin Matthews

11

QLD

6299

Simon Tabet

11

QLD

6112

Deborah Williams

10

VIC

3428

Peter Mahar

10

QLD

5134

Russell Den Engelse

9

WA

5279

Julie Isitt

8

NSW

5677

Pranav Agarwal

8

NSW

6093

Rosa Delizia

8

QLD

6684

Ron Prasad

8

WA

7578

Francis Williams

8

Qld

7511

LigIA Borodi

7

QLD

6804

Rupert Ryan

7

QLD

6856

Maxwell MacLean

7

QLD

6904

Trent Sticklen

7

QLD

7236

Drazen Knezevic

7

QLD

7406

Amy Donaldson

7

NSW

5601

Michael Phillips

7

QLD

5940

Harnake guraya

7

I would like too now sum up the figures to get in a separate layout (total sales off the above reps by state)

Queensland 168

South
Australia

0

Western
Australia

176

Victoria

40

New South
Wales

15

Northern
Territory

0

I have tried a different dataset but it doesnt allow me to do this due to the use off top 20 in the query which needs to be grouped by state this time and to do that it doesnt know the topn 20 records.

...I am trying to figure out a way by using textboxes , tables etc, I have managed to get the results coming out in a table using grouping by state and sum of the values but because there are no records for Sth Australia and Northern Territory these are not on the list...and i need these also in the list...can someone please help

thanks

Can someone help me with some code to achieve the above ?

thanks

|||

I don't have code for you, but in your query, you need to specify something like isnull(NorthernTerritory,0)

This will return a 0 (zero) instead of a null and then the data should show up on your report.

Since you are doing a topN, why don't you use a group by in your report?

|||

I cant do a group by as a rep can have more than 1 sale and to get the top 20 it is adding up all his sales already

ie

wa John 20 item 15

wa john 15 item 20

To get the top 20 i add the sum off the values together to get the rep...

ie

wa john 35

if i group by state john will be treated as 2 records and the numbers wont be for the top 20 sales rep...but just the top 20 sales

thanks for trying can anyone else help?

|||

Hi,

I found it hard to understand why cant you use grouping and othere data sets,

I have a solution but It is an ugly one.... (-:

first change your Data set to return a sum colums
by addind a join in your SQL to a query tha group by the State and sum the sales

after you do that you will have all the results you need in the same data set

now you jost have to add the colums to the feilds and use the First function in the expresions to

get the currect total sales for the state

STATE

USER ID

SALES REP

SALES sum

QLD

6758

Liam Maddrell

12 168

QLD

6677

Lisa-Maree Findlay

11 168

QLD

6133

Benjamin Matthews

11 168

QLD

6299

Simon Tabet

11

QLD

6112

Deborah Williams

10

VIC

3428

Peter Mahar

10

QLD

5134

Russell Den Engelse

9

WA

5279

Julie Isitt

8

NSW

5677

Pranav Agarwal

8

NSW

6093

Rosa Delizia

8

QLD

6684

Ron Prasad

8

WA

7578

Francis Williams

8

Qld

7511

LigIA Borodi

7

QLD

6804

Rupert Ryan

7

QLD

6856

Maxwell MacLean

7

QLD

6904

Trent Sticklen

7

QLD

7236

Drazen Knezevic

7

QLD

7406

Amy Donaldson

7

NSW

5601

Michael Phillips

7

QLD

5940

Harnake guraya

7