Friday, February 24, 2012

logical and

Hi,
I want to do a logical comparison between two tinyint-columns in a select
statement.
With logical I mean: if a bit in the tinyints are both 1 then comparison is
true.
00001000 and 11000001 = false
10101010 and 00000010=true
Can this be done in SQLServer?
Thanks
FrankThe bitwise AND logical operator in SQL Server is '&'. This expects integer
values. Converting your binary values to integer:
IF 8 & 193 = 193 PRINT 'true' ELSE PRINT 'false'
IF 170 & 2 = 2 PRINT 'true' ELSE PRINT 'false'
See Bitwize AND in the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank" <frank@.frank.com> wrote in message
news:41e50fbc$0$6211$e4fe514c@.news.xs4all.nl...
> Hi,
> I want to do a logical comparison between two tinyint-columns in a select
> statement.
> With logical I mean: if a bit in the tinyints are both 1 then comparison
> is true.
> 00001000 and 11000001 = false
> 10101010 and 00000010=true
> Can this be done in SQLServer?
> Thanks
> Frank
>|||GREAT!!!!!
Frank
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
news:uaWOg4K%23EHA.1296@.TK2MSFTNGP10.phx.gbl...
> The bitwise AND logical operator in SQL Server is '&'. This expects
> integer values. Converting your binary values to integer:
> IF 8 & 193 = 193 PRINT 'true' ELSE PRINT 'false'
> IF 170 & 2 = 2 PRINT 'true' ELSE PRINT 'false'
> See Bitwize AND in the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank" <frank@.frank.com> wrote in message
> news:41e50fbc$0$6211$e4fe514c@.news.xs4all.nl...
>|||Frank,
Dan pointed you in the right direction, and I'll just note that
to see if X and Y have a common bit set, compare X & Y
with 0.
Steve Kass
Drew University
Frank wrote:

>Hi,
>I want to do a logical comparison between two tinyint-columns in a select
>statement.
>With logical I mean: if a bit in the tinyints are both 1 then comparison is
>true.
>00001000 and 11000001 = false
>10101010 and 00000010=true
>Can this be done in SQLServer?
>Thanks
>Frank
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:uB%23xjUL%23EHA.1188@.tk2msftngp13.phx.gbl...
> Frank,
> Dan pointed you in the right direction, and I'll just note that
> to see if X and Y have a common bit set, compare X & Y
> with 0.
> Steve Kass
> Drew University
>
If X & Y = 0 then you have no bits in common.
X 1001
Y 0110
======
0000
IF X & Y = Y then you have Y bits in common.
X 1001
Y 0001
======
0001 <-- Same as Y
IF X & Y > 0 then you have at lest some bits in common, but not necessarily
all of them.
X 1001
Y 0011
======
0001 > 0 At least one bit in common.
Rick Sawtell
MCT, MCSD, MCDBA

Logical & Physical files relationships

I have two sql backup files, a and b, of the same database but at different
stages of development.
So, I restore backup a, OK. I then restore backup b, the logical name is the
same as a but the physical name is different.
1. will logical b overwrite logical a?
2. if not, can someone please explain the relationship between sql
server, logical and physical files
Thank you
Frank Ashley
I suspect that logical a would disappear when you restore logical b, because
the database is deleted and re-created.
When you create a database, you will have at least 2 physical files. Each of
the files has a logical name. The logical name is used to refer to the files
when using the Alter database modify file commands.(ie Alter database prod
modify file(name=mydatafile, size = 20)
...You may add more physical fles, and delete files as well. At some point in
time your databases was probably dropped and re-created with a different
physical location for the files, using the same logical file names.
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
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have two sql backup files, a and b, of the same database but at
different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is
the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>
|||In addition to Wayne's post:
You can have two databases with the same logical database file names. Logical file named need to be
unique *per database* only.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have two sql backup files, a and b, of the same database but at different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>
|||So Wayne's premise that
'I suspect that logical a would disappear when you restore logical b,
because
the database is deleted and re-created.'
is incorrect?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> In addition to Wayne's post:
> You can have two databases with the same logical database file names.
Logical file named need to be[vbcol=seagreen]
> unique *per database* only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
different[vbcol=seagreen]
the
>
|||It looks to me like Wayne assumes you are overwriting an existing database
during the restore. In this case, he is correct that the existing database
will be overwritten, including logical file names. Understand that this
will not affect other existing databases because the scope of logical file
names is per-database. You can restore to a different database name and it
can have the same logical file names as existing databases.
[vbcol=seagreen]
In summary:
1) a restored database is exactly like the backed-up database except that
the database name and physical file names may be changed during the restore
2) database names must be unique per SQL Server instance
3) logical file names must be unique per database
4) physical file names must be unique per server
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
> So Wayne's premise that
> 'I suspect that logical a would disappear when you restore logical b,
> because
> the database is deleted and re-created.'
> is incorrect?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> Logical file named need to be
> different
> the
>
|||Thanks Dan,
That's exactly what I was after (and suspected).
I had always assumed though that SQL Server used its logical names to
'look-up' the physical files, which is why I was confused as to the
'uniqueness' of logical files in a SQl Server instance. Could you shed any
light on the matter for me? How does SQL Server map its instance and logical
and physical files?
Thank you
Frank Ashley
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
> It looks to me like Wayne assumes you are overwriting an existing database
> during the restore. In this case, he is correct that the existing
database
> will be overwritten, including logical file names. Understand that this
> will not affect other existing databases because the scope of logical file
> names is per-database. You can restore to a different database name and
it
> can have the same logical file names as existing databases.
>
> In summary:
> 1) a restored database is exactly like the backed-up database except that
> the database name and physical file names may be changed during the
restore
> 2) database names must be unique per SQL Server instance
> 3) logical file names must be unique per database
> 4) physical file names must be unique per server
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
>
|||Logical file names are only used in Transact-SQL statements (e.g. RESTORE,
DBCC, CREATE/ALTER DATABASE, etc.) as user-friendly names to 'look-up'
information in the system tables. These Transact-SQL statements are
database-specific so this is essentially why logical names only need to be
unique within the context of a specific database.
The logical file name and fileid both uniquely identify a file within a
database. This information is stored in the master database sysaltfiles
table along with the database id. The combination of dbid and either
logical name or fileid uniquely identify a file in the sysaltfiles table.
Database file information is also stored in each database in the sysfiles
system table, with a row for each file belonging to that database.
See the Books Online <Books\architec.chm::/8_ar_da2_9sab.htm>.for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23Gs6XFtrEHA.1232@.TK2MSFTNGP11.phx.gbl...
> Thanks Dan,
> That's exactly what I was after (and suspected).
> I had always assumed though that SQL Server used its logical names to
> 'look-up' the physical files, which is why I was confused as to the
> 'uniqueness' of logical files in a SQl Server instance. Could you shed any
> light on the matter for me? How does SQL Server map its instance and
> logical
> and physical files?
>
> Thank you
> Frank Ashley
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
> database
> it
> restore
>

Logical & Physical files relationships

I have two sql backup files, a and b, of the same database but at different
stages of development.
So, I restore backup a, OK. I then restore backup b, the logical name is the
same as a but the physical name is different.
1. will logical b overwrite logical a?
2. if not, can someone please explain the relationship between sql
server, logical and physical files
Thank you
Frank AshleyI suspect that logical a would disappear when you restore logical b, because
the database is deleted and re-created.
When you create a database, you will have at least 2 physical files. Each of
the files has a logical name. The logical name is used to refer to the files
when using the Alter database modify file commands.(ie Alter database prod
modify file(name=mydatafile, size = 20)
..You may add more physical fles, and delete files as well. At some point in
time your databases was probably dropped and re-created with a different
physical location for the files, using the same logical file names.
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
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have two sql backup files, a and b, of the same database but at
different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is
the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>|||In addition to Wayne's post:
You can have two databases with the same logical database file names. Logical file named need to be
unique *per database* only.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have two sql backup files, a and b, of the same database but at different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>|||So Wayne's premise that
'I suspect that logical a would disappear when you restore logical b,
because
the database is deleted and re-created.'
is incorrect?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> In addition to Wayne's post:
> You can have two databases with the same logical database file names.
Logical file named need to be
> unique *per database* only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> >I have two sql backup files, a and b, of the same database but at
different
> > stages of development.
> >
> > So, I restore backup a, OK. I then restore backup b, the logical name is
the
> > same as a but the physical name is different.
> >
> > 1. will logical b overwrite logical a?
> > 2. if not, can someone please explain the relationship between sql
> > server, logical and physical files
> >
> > Thank you
> > Frank Ashley
> >
> >
>|||It looks to me like Wayne assumes you are overwriting an existing database
during the restore. In this case, he is correct that the existing database
will be overwritten, including logical file names. Understand that this
will not affect other existing databases because the scope of logical file
names is per-database. You can restore to a different database name and it
can have the same logical file names as existing databases.
>> > 2. if not, can someone please explain the relationship between sql
>> > server, logical and physical files
In summary:
1) a restored database is exactly like the backed-up database except that
the database name and physical file names may be changed during the restore
2) database names must be unique per SQL Server instance
3) logical file names must be unique per database
4) physical file names must be unique per server
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
> So Wayne's premise that
> 'I suspect that logical a would disappear when you restore logical b,
> because
> the database is deleted and re-created.'
> is incorrect?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
>> In addition to Wayne's post:
>> You can have two databases with the same logical database file names.
> Logical file named need to be
>> unique *per database* only.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> >I have two sql backup files, a and b, of the same database but at
> different
>> > stages of development.
>> >
>> > So, I restore backup a, OK. I then restore backup b, the logical name
>> > is
> the
>> > same as a but the physical name is different.
>> >
>> > 1. will logical b overwrite logical a?
>> > 2. if not, can someone please explain the relationship between sql
>> > server, logical and physical files
>> >
>> > Thank you
>> > Frank Ashley
>> >
>> >
>>
>|||Thanks Dan,
That's exactly what I was after (and suspected).
I had always assumed though that SQL Server used its logical names to
'look-up' the physical files, which is why I was confused as to the
'uniqueness' of logical files in a SQl Server instance. Could you shed any
light on the matter for me? How does SQL Server map its instance and logical
and physical files?
Thank you
Frank Ashley
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
> It looks to me like Wayne assumes you are overwriting an existing database
> during the restore. In this case, he is correct that the existing
database
> will be overwritten, including logical file names. Understand that this
> will not affect other existing databases because the scope of logical file
> names is per-database. You can restore to a different database name and
it
> can have the same logical file names as existing databases.
> >> > 2. if not, can someone please explain the relationship between sql
> >> > server, logical and physical files
> In summary:
> 1) a restored database is exactly like the backed-up database except that
> the database name and physical file names may be changed during the
restore
> 2) database names must be unique per SQL Server instance
> 3) logical file names must be unique per database
> 4) physical file names must be unique per server
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
> > So Wayne's premise that
> >
> > 'I suspect that logical a would disappear when you restore logical b,
> > because
> > the database is deleted and re-created.'
> >
> > is incorrect?
> >
> >
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> >> In addition to Wayne's post:
> >>
> >> You can have two databases with the same logical database file names.
> > Logical file named need to be
> >> unique *per database* only.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> >> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> >> >I have two sql backup files, a and b, of the same database but at
> > different
> >> > stages of development.
> >> >
> >> > So, I restore backup a, OK. I then restore backup b, the logical name
> >> > is
> > the
> >> > same as a but the physical name is different.
> >> >
> >> > 1. will logical b overwrite logical a?
> >> > 2. if not, can someone please explain the relationship between sql
> >> > server, logical and physical files
> >> >
> >> > Thank you
> >> > Frank Ashley
> >> >
> >> >
> >>
> >>
> >
> >
>|||Logical file names are only used in Transact-SQL statements (e.g. RESTORE,
DBCC, CREATE/ALTER DATABASE, etc.) as user-friendly names to 'look-up'
information in the system tables. These Transact-SQL statements are
database-specific so this is essentially why logical names only need to be
unique within the context of a specific database.
The logical file name and fileid both uniquely identify a file within a
database. This information is stored in the master database sysaltfiles
table along with the database id. The combination of dbid and either
logical name or fileid uniquely identify a file in the sysaltfiles table.
Database file information is also stored in each database in the sysfiles
system table, with a row for each file belonging to that database.
See the Books Online <Books\architec.chm::/8_ar_da2_9sab.htm>.for more
information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23Gs6XFtrEHA.1232@.TK2MSFTNGP11.phx.gbl...
> Thanks Dan,
> That's exactly what I was after (and suspected).
> I had always assumed though that SQL Server used its logical names to
> 'look-up' the physical files, which is why I was confused as to the
> 'uniqueness' of logical files in a SQl Server instance. Could you shed any
> light on the matter for me? How does SQL Server map its instance and
> logical
> and physical files?
>
> Thank you
> Frank Ashley
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
>> It looks to me like Wayne assumes you are overwriting an existing
>> database
>> during the restore. In this case, he is correct that the existing
> database
>> will be overwritten, including logical file names. Understand that this
>> will not affect other existing databases because the scope of logical
>> file
>> names is per-database. You can restore to a different database name and
> it
>> can have the same logical file names as existing databases.
>> >> > 2. if not, can someone please explain the relationship between sql
>> >> > server, logical and physical files
>> In summary:
>> 1) a restored database is exactly like the backed-up database except
>> that
>> the database name and physical file names may be changed during the
> restore
>> 2) database names must be unique per SQL Server instance
>> 3) logical file names must be unique per database
>> 4) physical file names must be unique per server
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
>> > So Wayne's premise that
>> >
>> > 'I suspect that logical a would disappear when you restore logical
>> > b,
>> > because
>> > the database is deleted and re-created.'
>> >
>> > is incorrect?
>> >
>> >
>> >
>> >
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> > wrote
>> > in
>> > message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
>> >> In addition to Wayne's post:
>> >>
>> >> You can have two databases with the same logical database file names.
>> > Logical file named need to be
>> >> unique *per database* only.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> >> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> >> >I have two sql backup files, a and b, of the same database but at
>> > different
>> >> > stages of development.
>> >> >
>> >> > So, I restore backup a, OK. I then restore backup b, the logical
>> >> > name
>> >> > is
>> > the
>> >> > same as a but the physical name is different.
>> >> >
>> >> > 1. will logical b overwrite logical a?
>> >> > 2. if not, can someone please explain the relationship between
>> >> > sql
>> >> > server, logical and physical files
>> >> >
>> >> > Thank you
>> >> > Frank Ashley
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Logical

I would like to re-index two tables (t1 and t2) if the logical scan
fragmentation is greater 80% on these tables.
Please help me create a t-sql script to create this task.
Thank You,
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D219E592-9000-49FB-AFE1-B109678802AC@.microsoft.com...
> I would like to re-index two tables (t1 and t2) if the logical scan
> fragmentation is greater 80% on these tables.
> Please help me create a t-sql script to create this task.
> Thank You,

Logical

I would like to re-index two tables (t1 and t2) if the logical scan
fragmentation is greater 80% on these tables.
Please help me create a t-sql script to create this task.
Thank You,Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D219E592-9000-49FB-AFE1-B109678802AC@.microsoft.com...
> I would like to re-index two tables (t1 and t2) if the logical scan
> fragmentation is greater 80% on these tables.
> Please help me create a t-sql script to create this task.
> Thank You,

Logical

I would like to re-index two tables (t1 and t2) if the logical scan
fragmentation is greater 80% on these tables.
Please help me create a t-sql script to create this task.
Thank You,Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D219E592-9000-49FB-AFE1-B109678802AC@.microsoft.com...
> I would like to re-index two tables (t1 and t2) if the logical scan
> fragmentation is greater 80% on these tables.
> Please help me create a t-sql script to create this task.
> Thank You,

Logic/Toggle Question

I am trying to do the following (without using a parameter box to drive the results)

I would like to be able to toggle the results instead of using a parameter.

The default view for the table data is any row that meets a threshold criteria (similar to a KPI)

I would then like for all of the data to be exposed when toggled which eliminates using IIF (unless I am missing something).

I would think that if I could reference the toggleState of a textbox I could do this (similar to a yes/no Show All parameter), but I found little to nothing on how to do this within Reporting Services.

I guess I could also use two datasets to achieve this, but I would prefer a more elegant result.

Are you looking for a kind of drill down?

If yes then a visibility condition can be written on the basis of threshhold criteria for the complete row of table and toggle item can be chosen (just below the hidden property).

So on the click of that texbox user would be able to toggle rows which are not crossing threshholds.

Hope this helps!

Priyank

|||

If I understand you correctly, not quite.

I would like the toggle button to show either the rows that meet the criteria or all rows.

Imagine you have 5 rows of data

1997 10

1998 12

1999 11

2000 9

2001 4

The threshold is < 8

The default view would be

2001 4

If you then hit the toggle button, I would like all records to be shown including the record meeting the threshold.

As I noted, I could achieve this with a parameter value or most likely through two datasets (overthreshold and underthreshold), but I was curious if you code obtain the toggle state of a textbox in embedded code. The ToggleState could act as an all or only threshold selection.

Logic statement using select query

I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).

Query example:

select taskID from Users where Login=@.username

Which classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.

Thanks in advance

If you are using the SqlDataSource, in its Selected event, you can check the RowsAffected property. If it is > 0, set your bool to true:

bool Check = False;

protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;

if (RecordCount > 0)
{ Check = True; }
}


|||

Actually I tried to make it in the Page_Load(object sender, EventArgs e) event. Does it change anything?

I'm wondering if there is a way to get to sql query from C# code. Something like:

[for each row from select query]

bool present;

if ( [row.taskID] == null )

{

present = false;

}

|||You will not get rows containing null from the query you showed in your example. You will only get rows that contain the TaskID that belong to the user specified. If there are no TaskIDs for that user, you will get no rows at all.|||

I think i will get a row of some user even if he has NULL in taskID field. I checked this and i got the row that i wanted with empty value of the taskID field.

But even if there would be e.g. -1 the problem still exists and i have no idea how to solve it or even where to look for solution.

|||I beg your pardon. You are right. I didn't read your query correctly. You can access values from a datasource as described here:http://www.mikesdotnetting.com/Article.aspx?ArticleID=45, and check each value, setting your bool accordingly|||

Thaks again, it works fine now. You are gosu ;)

|||

DCVer:

You are gosu ;)

Is that a good thing?Confused

|||

Yes, you are master, the king etcSmile

Logic Question

Ok so I have a little problem. I dont want code or anything I just
want a little help with the logical steps to take to complete the
process.
Little background, we have students who come and take 10 courses to get
this certification. All 10 courses MUST be completed to get certified.
First I have three tables. tblStudent and tblCourse and
tblCourseCompleted.
tblStudent(StudentID) obviously contains all students enrolled,
tblCourse(CourseID) lists all courses offered(Only 10 courses
currently) and tblCourseCompleted lists the Student ID and Course ID
and Date the Course was completed.
So based off tblCourseCompleted i have a complete list of all the
students who have completed various courses.
BUT, What I want to do is generate a report that lists the which
courses the students HAVE NOT taken. So it needs to list Each student
and every course hes missing. So if student A is missing 4 courses, it
will list student A four times with each course he has not yet
completed.
So basically i need the EXACT opposite of tblCourseCompletions.
Can anyone help me with the logic of some how generating this type of
report?Sometimes it is simpler to explain logic with an example:
SELECT *
FROM tblStudent as S
CROSS JOIN tblCourse as C
WHERE NOT EXISTS
(select * from tblCourseCompleted as X
where X.course = C.course
and X.student = S.student)
The cross join gives every possible combination of student and course.
The NOT EXISTS correlated subquery eliminates those that have been
completed.
Roy Harvey
Beacon Falls, CT
On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
wrote:

>Ok so I have a little problem. I dont want code or anything I just
>want a little help with the logical steps to take to complete the
>process.
>Little background, we have students who come and take 10 courses to get
>this certification. All 10 courses MUST be completed to get certified.
>First I have three tables. tblStudent and tblCourse and
>tblCourseCompleted.
>tblStudent(StudentID) obviously contains all students enrolled,
>tblCourse(CourseID) lists all courses offered(Only 10 courses
>currently) and tblCourseCompleted lists the Student ID and Course ID
>and Date the Course was completed.
>So based off tblCourseCompleted i have a complete list of all the
>students who have completed various courses.
>BUT, What I want to do is generate a report that lists the which
>courses the students HAVE NOT taken. So it needs to list Each student
>and every course hes missing. So if student A is missing 4 courses, it
>will list student A four times with each course he has not yet
>completed.
>So basically i need the EXACT opposite of tblCourseCompletions.
>Can anyone help me with the logic of some how generating this type of
>report?|||Try,
select t1.*
from
(
select a.StudentID, b.CourseID
from tblStudent as a cross join tblCourse as b
) as t1
where not exists(
select *
from tblCourseCompleted as t2
where t2.StudentID = t1.StudentID and t2.CourseID = t1.CourseID
)
AMB
"wmureports" wrote:

> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||From a logic point of view , you want to SELECT courses per student NOT IN
the tblCoursesCompalted.
The way you'be described the first report would be using an INNER JOIN , i.e
only display the record , if it exists as a
relationship in the tblCourseCompleted . On a logic level, you need to find
the set that doesn't have a relationship in that table.
You could use a LEFT OUTER JOIN and then put a condition on the WHERE
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"wmureports" <justin.a.moran@.gmail.com> wrote in message
news:1152719723.696320.29420@.75g2000cwc.googlegroups.com...
> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||OK that worked PERFECTLY! thank you very much
Roy Harvey wrote:[vbcol=seagreen]
> Sometimes it is simpler to explain logic with an example:
> SELECT *
> FROM tblStudent as S
> CROSS JOIN tblCourse as C
> WHERE NOT EXISTS
> (select * from tblCourseCompleted as X
> where X.course = C.course
> and X.student = S.student)
> The cross join gives every possible combination of student and course.
> The NOT EXISTS correlated subquery eliminates those that have been
> completed.
> Roy Harvey
> Beacon Falls, CT
> On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
> wrote:
>

Logic Question

Ok so I have a little problem. I dont want code or anything I just
want a little help with the logical steps to take to complete the
process.
Little background, we have students who come and take 10 courses to get
this certification. All 10 courses MUST be completed to get certified.
First I have three tables. tblStudent and tblCourse and
tblCourseCompleted.
tblStudent(StudentID) obviously contains all students enrolled,
tblCourse(CourseID) lists all courses offered(Only 10 courses
currently) and tblCourseCompleted lists the Student ID and Course ID
and Date the Course was completed.
So based off tblCourseCompleted i have a complete list of all the
students who have completed various courses.
BUT, What I want to do is generate a report that lists the which
courses the students HAVE NOT taken. So it needs to list Each student
and every course hes missing. So if student A is missing 4 courses, it
will list student A four times with each course he has not yet
completed.
So basically i need the EXACT opposite of tblCourseCompletions.
Can anyone help me with the logic of some how generating this type of
report?Sometimes it is simpler to explain logic with an example:
SELECT *
FROM tblStudent as S
CROSS JOIN tblCourse as C
WHERE NOT EXISTS
(select * from tblCourseCompleted as X
where X.course = C.course
and X.student = S.student)
The cross join gives every possible combination of student and course.
The NOT EXISTS correlated subquery eliminates those that have been
completed.
Roy Harvey
Beacon Falls, CT
On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
wrote:
>Ok so I have a little problem. I dont want code or anything I just
>want a little help with the logical steps to take to complete the
>process.
>Little background, we have students who come and take 10 courses to get
>this certification. All 10 courses MUST be completed to get certified.
>First I have three tables. tblStudent and tblCourse and
>tblCourseCompleted.
>tblStudent(StudentID) obviously contains all students enrolled,
>tblCourse(CourseID) lists all courses offered(Only 10 courses
>currently) and tblCourseCompleted lists the Student ID and Course ID
>and Date the Course was completed.
>So based off tblCourseCompleted i have a complete list of all the
>students who have completed various courses.
>BUT, What I want to do is generate a report that lists the which
>courses the students HAVE NOT taken. So it needs to list Each student
>and every course hes missing. So if student A is missing 4 courses, it
>will list student A four times with each course he has not yet
>completed.
>So basically i need the EXACT opposite of tblCourseCompletions.
>Can anyone help me with the logic of some how generating this type of
>report?|||Try,
select t1.*
from
(
select a.StudentID, b.CourseID
from tblStudent as a cross join tblCourse as b
) as t1
where not exists(
select *
from tblCourseCompleted as t2
where t2.StudentID = t1.StudentID and t2.CourseID = t1.CourseID
)
AMB
"wmureports" wrote:
> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||From a logic point of view , you want to SELECT courses per student NOT IN
the tblCoursesCompalted.
The way you'be described the first report would be using an INNER JOIN , i.e
only display the record , if it exists as a
relationship in the tblCourseCompleted . On a logic level, you need to find
the set that doesn't have a relationship in that table.
You could use a LEFT OUTER JOIN and then put a condition on the WHERE
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"wmureports" <justin.a.moran@.gmail.com> wrote in message
news:1152719723.696320.29420@.75g2000cwc.googlegroups.com...
> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||OK that worked PERFECTLY! thank you very much :)
Roy Harvey wrote:
> Sometimes it is simpler to explain logic with an example:
> SELECT *
> FROM tblStudent as S
> CROSS JOIN tblCourse as C
> WHERE NOT EXISTS
> (select * from tblCourseCompleted as X
> where X.course = C.course
> and X.student = S.student)
> The cross join gives every possible combination of student and course.
> The NOT EXISTS correlated subquery eliminates those that have been
> completed.
> Roy Harvey
> Beacon Falls, CT
> On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
> wrote:
> >Ok so I have a little problem. I dont want code or anything I just
> >want a little help with the logical steps to take to complete the
> >process.
> >
> >Little background, we have students who come and take 10 courses to get
> >this certification. All 10 courses MUST be completed to get certified.
> >
> >First I have three tables. tblStudent and tblCourse and
> >tblCourseCompleted.
> >
> >tblStudent(StudentID) obviously contains all students enrolled,
> >tblCourse(CourseID) lists all courses offered(Only 10 courses
> >currently) and tblCourseCompleted lists the Student ID and Course ID
> >and Date the Course was completed.
> >
> >So based off tblCourseCompleted i have a complete list of all the
> >students who have completed various courses.
> >
> >BUT, What I want to do is generate a report that lists the which
> >courses the students HAVE NOT taken. So it needs to list Each student
> >and every course hes missing. So if student A is missing 4 courses, it
> >will list student A four times with each course he has not yet
> >completed.
> >
> >So basically i need the EXACT opposite of tblCourseCompletions.
> >
> >Can anyone help me with the logic of some how generating this type of
> >report?

Logic problem in cursor/SPROC

SQL Server 2000

I have a stored procedure that uses an extended SPROC to send an email
notification to my customers when a document is distributed.

However, the SPROC has an unexpected side effect.

If I run it in its current incarnation, it only sends one email and
then exits. However, if I remove or comment out the block

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @.JobID)

then it runs through the whole list as anticipated. Conceptually, it
seems that the records in the cursor are changed if the underlying
table is updated.

Here is pseudo-code for what the SPROC does - whole SPROC below (with
part of the "WHERE" clause removed for readability). I haven't
included any table schemae but I don't think they're relevant.

1. Open a cursor and fetch a list of all companies that need email
notification for pending jobs.

2. While records in the cursor...

a) Format and send email from the cursor
b) Write a record to the audit table
c) Update the jobs table for the current record

3) Fetch next from cursor

There is an update trigger on the tblJobs table thus:

CREATE TRIGGER "tblJobs_UTrig" ON dbo.tblJobs FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tblDistributionMaster' */
IF UPDATE(fldDistributionID)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDistributionMaster, inserted WHERE
(tblDistributionMaster.fldDistributionID = inserted.fldDistributionID))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tblDistributionMaster''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblPrinterJobs' */
IF UPDATE(fldJobID)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tblPrinterJobs WHERE
(deleted.fldJobID = tblPrinterJobs.fldJobID)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or
changed. Since related records exist in table ''tblPrinterJobs'',
referential integrity rules would be violated.'
ROLLBACK TRANSACTION
END
END

I can't see that this is relevant - I think it's something to do with
where I'm updating the fldEmailProcessed field, but I need to do this
here, as outside the loop the fldJobID might be lost.

Sorry it's all such a mess. Hope someone can help!

Thanks

Edward

/*
Checks all Jobs that were set to Despatched more than 24 hours ago,
selects those that the companies elected to get email notification, and
sends them emails.
*/

CREATE PROCEDURE stpSendEmailNotification

AS

DECLARE @.rc int
DECLARE @.JobID int
DECLARE @.CompanyID int
DECLARE @.DocumentNumber varchar(50)
DECLARE @.Email varchar(50)
DECLARE @.DocumentURL varchar(750)
DECLARE @.Dat varchar(20)
DECLARE @.Subject varchar(100)

SET @.Dat = LEFT((CONVERT(varchar, GETDATE(), 100)), 11)

DECLARE MailList CURSOR FOR

SELECT
tblJobs.fldJobID,
tblJobs.fldDocumentNumber,
tblCompany.fldEmail,
tblCompany.fldCompanyID,
tblJobHistory.fldDocumentURL
FROM
tblJobHistory INNER JOIN
tblJobs ON tblJobHistory.fldJobID = tblJobs.fldJobID
INNER JOIN
tblDistributionMaster ON tblJobHistory.fldDistributionID =
tblDistributionMaster.fldDistributionID INNER JOIN
tblCompany ON tblJobHistory.fldCompanyID =
tblCompany.fldCompanyID
WHERE
(tblJobs.fldEmailProcessed = 0)

OPEN MailList

FETCH NEXT FROM MailList INTO
@.JobID,
@.DocumentNumber,
@.Email,
@.CompanyID,
@.DocumentURL

WHILE @.@.FETCH_STATUS = 0
BEGIN

/* Format and send the email to the customer here */
SET @.Subject = N'Document Distribution No: ' + @.DocumentNumber +
N' - Date: ' + @.Dat

exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'techlib@.myco.co.uk',
@.FROM_NAME = N'Edward Collier',
@.replyto = N'techlib@.myco.co.uk',
@.TO = @.Email,
@.CC = N'',
@.BCC = N'',
@.priority = N'NORMAL',
@.subject = @.Subject,
@.type = N'text/plain',
@.message = @.DocumentURL,
@.messagefile = N'',
@.attachment = N'',
@.attachments = N'',
@.codepage = 0,
@.server = N'12.34.5.67',
@.timeout = 10000
select RC = @.rc

/* Write result to audit table */
INSERT INTO tblEmailAudit
(
fldRCNo,
fldEmail,
fldDocumentNumber,
fldDate,
fldCompanyID
)
VALUES
(
@.rc,
@.Email,
@.DocumentNumber,
GETDATE(),
@.CompanyID
)

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @.JobID)

FETCH NEXT FROM MailList INTO
@.JobID,
@.DocumentNumber,
@.Email,
@.CompanyID,
@.DocumentURL
END

CLOSE MailList
DEALLOCATE MailList
GO
teddysnips@.hotmail.com wrote:
> SQL Server 2000
> I have a stored procedure that uses an extended SPROC to send an email
> notification to my customers when a document is distributed.
> However, the SPROC has an unexpected side effect.

[...]

I've sorted it. The cursor needed to be declared STATIC. Panic over.

Edward

Logic problem - Please help :-(

Hi all,
I have a situation that I need some help with as soon as possible. If
youre good at logic puzzles then please help as soon as you have a
minute :-)
As some of you know I have a situation in which I need to copy data from
a table in QuickRes, to an identical table in a new database (called
Arrivals). The databases are on physically separate machines. For
operational reasons, the query that copies the data over from QuickRes
to Arrivals will be kicked off by a Web Service, which will then return
the data as XML.
I only want to copy changed rows. Each time a row is changed, its
LastUpdated field is changed to represent the time the update took
place. The idea is simply that each time the synchronisation process
occurs, it will copy across any rows changed since the last time the
sync process ran.
I'm worried about this though because it seems to me that there might be
the possibility of missing some changes under the following
circumstances (or some permutation of them anyway):
Consider:
Time Event
10:46:10 The query to copy all changed rows begins. It starts
returning any rows that have been updated since, say, 10:31:00.
10:46:11 Under a separate thread, an update statement us executed
that changes a row in the target table. It's LastUpdated field is
changed to 10:46:11. The synchronisation query does *not* detect this
row change because it is just about to complete.
10:46:12 The synchronisation process completes at 10:46:12
The next time the sync process executes, its going to request any rows
changed since 10:46:12.
This will *not* include the row changed in step two because it's
LastUpdated value is 10:46:11
In other words, this update is lost and won't be brought over.
Questions:
1. Have I identified a real problem here, or am I worried about nothing
- perhaps because SQL Server somehow "magically" takes care of stuff
like this. It seems like a real problem to me, but I'm not very good at
logic stuff like this!
2. Assuming there is a real problem - what do I need to do to fix it?
Would putting the sync query in a transaction prevent the problem? I've
thought about that one and it seems that even in a transaction, because
the computer is so fast, the times entered into the last updated field
could still cause this problem.
I did think about using the actual Timestamp data type, but I remember
reading somewhere that the timestamp is based on the machine that it was
generated on. I was worried that it might not work because of there
being two machines involved in the process.
I know how to deal with the synchronisation process in several different
ways but for the moment were assuming that typical sync methods such as
transactional replication are off the table.
Any help or advice would be very greatly appreciated :-)
Many thanks
Kindest Regards
SimonJust thinking out loud, why don't start the sync process next time
to take records having time stamp > 10:46:10|||Simon,
why don't you use replication?|||Alexander Kuznetsov wrote:
> Simon,
> why don't you use replication?
>
Hi Alex,
I would like to use replication but my boss has a thing about web
services! He wants it done that way.
I may have to kick up a stink if there's no sure fire way to do it the
webservice way...
Thanks|||Ahhhh...
The old "newer technology is better" solution. I could go on and on about
how the database is built for replication, tried and tested, and web
services only add extra, unnecessary layers of complexity and ten times the
code (not to mention and extra server and all sorts of extra network
traffic), but I'll let other folks who are more knowledgeable about
replication do that. A separate post on the benefits of replication VS a
web service should get all sorts of expert responses that you can show to
your boss as justification for doing it the right way.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OgJ4EfbeGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Alexander Kuznetsov wrote:
>
> Hi Alex,
> I would like to use replication but my boss has a thing about web
> services! He wants it done that way.
> I may have to kick up a stink if there's no sure fire way to do it the
> webservice way...
> Thanks|||Simon,
I would
1. lock the table.
2. insert getdate() into some other table, say replication_batch
3. release the lock
4. copy the rows changed between the last 2 values in replication_batch
Good luck!|||If you are concerned with missing updates due to timing issues, and if you
ALWAYS want to overwrite the data, just change your date to the start of the
last run, rather than the end of it (as I think Omni suggested). Yes, you
will copy over more rows than is needed, but you will have the accuracy you
are looking for. Although replication is likely a much simpler, and
dependable solution.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1147882907.441123.246700@.38g2000cwa.googlegroups.com...
> Simon,
> I would
> 1. lock the table.
> 2. insert getdate() into some other table, say replication_batch
> 3. release the lock
> 4. copy the rows changed between the last 2 values in replication_batch
> Good luck!
>

Logic problem - Please help :-(

Hi all,
I have a situation that I need some help with as soon as possible. If
youre good at logic puzzles then please help as soon as you have a
minute :-)
As some of you know I have a situation in which I need to copy data from
a table in QuickRes, to an identical table in a new database (called
Arrivals). The databases are on physically separate machines. For
operational reasons, the query that copies the data over from QuickRes
to Arrivals will be kicked off by a Web Service, which will then return
the data as XML.
I only want to copy changed rows. Each time a row is changed, its
LastUpdated field is changed to represent the time the update took
place. The idea is simply that each time the synchronisation process
occurs, it will copy across any rows changed since the last time the
sync process ran.
I'm worried about this though because it seems to me that there might be
the possibility of missing some changes under the following
circumstances (or some permutation of them anyway):
Consider:
Time Event
10:46:10 The query to copy all changed rows begins. It starts returning
any rows that have been updated since, say, 10:31:00.
10:46:11 Under a separate thread, an update statement us executed that
changes a row in the target table. It's LastUpdated field is changed to
10:46:11. The synchronisation query does *not* detect this row change
because it is just about to complete.
10:46:12 The synchronisation process completes at 10:46:12
The next time the sync process executes, its going to request any rows
changed since 10:46:12.
This will *not* include the row changed in step two because it's
LastUpdated value is 10:46:11
In other words, this update is lost and won't be brought over.
Questions:
1. Have I identified a real problem here, or am I worried about nothing
- perhaps because SQL Server somehow "magically" takes care of stuff
like this. It seems like a real problem to me, but I'm not very good at
logic stuff like this!
2. Assuming there is a real problem - what do I need to do to fix it?
Would putting the sync query in a transaction prevent the problem? I've
thought about that one and it seems that even in a transaction, because
the computer is so fast, the times entered into the last updated field
could still cause this problem.
I did think about using the actual Timestamp data type, but I remember
reading somewhere that the timestamp is based on the machine that it was
generated on. I was worried that it might not work because of there
being two machines involved in the process.
I know how to deal with the synchronisation process in several different
ways but for the moment were assuming that typical sync methods such as
transactional replication are off the table.
Any help or advice would be very greatly appreciated :-)
Many thanks
Kindest Regards
SimonHi Simon,
Why didnt you try Update trigger and Insert trigger '
If you have Inserted new rows or updated several rows, you can insert
in to a temporary table and later you can transfer into a destination
table. What you want to do is You have to truncate that temporary table
after transfering the table.
Regards,
Muthu kumaran .D
(Unknown Is Ocean)|||Hi Mutha
Hmmm... Thats an interesting idea.
I'll definately have to have a think about that one - it might just do
the trick! :-)
Many thanks for your advice
Simon|||Simon.
Something else you may consider is using the max last update date from the
synchronised rows instead of the completion time for the thread.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:%23bHjNBaeGHA.3468@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I have a situation that I need some help with as soon as possible. If you
re
> good at logic puzzles then please help as soon as you have a minute :-)
> As some of you know I have a situation in which I need to copy data from a
> table in QuickRes, to an identical table in a new database (called
> Arrivals). The databases are on physically separate machines. For
> operational reasons, the query that copies the data over from QuickRes to
> Arrivals will be kicked off by a Web Service, which will then return the
> data as XML.
> I only want to copy changed rows. Each time a row is changed, its
> LastUpdated field is changed to represent the time the update took place.
> The idea is simply that each time the synchronisation process occurs, it
> will copy across any rows changed since the last time the sync process
> ran.
> I'm worried about this though because it seems to me that there might be
> the possibility of missing some changes under the following circumstances
> (or some permutation of them anyway):
> Consider:
> Time Event
> 10:46:10 The query to copy all changed rows begins. It starts returning
> any rows that have been updated since, say, 10:31:00.
> 10:46:11 Under a separate thread, an update statement us executed that
> changes a row in the target table. It's LastUpdated field is changed to
> 10:46:11. The synchronisation query does *not* detect this row change
> because it is just about to complete.
> 10:46:12 The synchronisation process completes at 10:46:12
> The next time the sync process executes, its going to request any rows
> changed since 10:46:12.
> This will *not* include the row changed in step two because it's
> LastUpdated value is 10:46:11
> In other words, this update is lost and won't be brought over.
> Questions:
> 1. Have I identified a real problem here, or am I worried about nothing -
> perhaps because SQL Server somehow "magically" takes care of stuff like
> this. It seems like a real problem to me, but I'm not very good at logic
> stuff like this!
> 2. Assuming there is a real problem - what do I need to do to fix it?
> Would putting the sync query in a transaction prevent the problem? I've
> thought about that one and it seems that even in a transaction, because
> the computer is so fast, the times entered into the last updated field
> could still cause this problem.
> I did think about using the actual Timestamp data type, but I remember
> reading somewhere that the timestamp is based on the machine that it was
> generated on. I was worried that it might not work because of there being
> two machines involved in the process.
> I know how to deal with the synchronisation process in several different
> ways but for the moment were assuming that typical sync methods such as
> transactional replication are off the table.
> Any help or advice would be very greatly appreciated :-)
> Many thanks
> Kindest Regards
> Simon|||On Wed, 17 May 2006 11:54:42 +0100, Simon Harvey wrote:
(snip)
>Questions:
>1. Have I identified a real problem here, or am I worried about nothing
>- perhaps because SQL Server somehow "magically" takes care of stuff
>like this. It seems like a real problem to me, but I'm not very good at
>logic stuff like this!
Hi Simon,
If you wrote the process to identify changed roows and export them as
XML, then SQL Server will do nothing magically - it will do exactly what
yoou tell it to do. (All else would qualify as a bug).
Would it be a major problem if a changed row can be exported twice (in
rare cases)? If not, thhen there's a simple change - have the export
process select all rows changed since the START, rather than the end, of
the previous execution.

>I did think about using the actual Timestamp data type
Don't. The name is misleading. A timestamp column has nothing to do with
timestamps. It's only intended to be used for optimistic concurrency
control.
Hugo Kornelis, SQL Server MVP|||On 17 May 2006 07:25:54 -0700, mkumaran wrote:

>Hi Simon,
>Why didnt you try Update trigger and Insert trigger '
>If you have Inserted new rows or updated several rows, you can insert
>in to a temporary table and later you can transfer into a destination
>table. What you want to do is You have to truncate that temporary table
>after transfering the table.
Hi Muthu,
That would work, but only if Simon makes sure that the temporary table
is locked as soon as the export process starts and that this lock is
held until the TRUNCATE TABLE command has been executed. Otherwise,
there's still the possibility that a row is inserted in the temp table
after the actual export but before the TRUNCATE TABLE.
Of course, such lokcing would seriously hampper concurrency...
Hugo Kornelis, SQL Server MVP|||Hi Hugo Kornelis,
you are correct. But What I have thought is the ideal transferring time
is very fast enough if we are using Insert Into Command. Even if new
rows are inserted when we are tranferring data, It also getting
transfered I think.If not We can check the existing of such rows and
than we can insert it or update it.
Please tell me Am I Correct'
--
Muthu Kumaran.D|||Hi Guys
Thanks so much for your help!
I think I'm going to go for the trigger option on this one. Seems a bit
simpler to understand and implement - and I can be reasonably confident
that it's working cos I actually understand it!
In terms of performance, I'm not very concerned - this refresh process
is going to be running frequently enough that there will only ever be a
few rows in the temp tables, so locking it will be for a very short
amount of time.
Many thanks again for all your help
Simon
mkumaran wrote:
> Hi Hugo Kornelis,
> you are correct. But What I have thought is the ideal transferring time
> is very fast enough if we are using Insert Into Command. Even if new
> rows are inserted when we are tranferring data, It also getting
> transfered I think.If not We can check the existing of such rows and
> than we can insert it or update it.
> Please tell me Am I Correct'
> --
> Muthu Kumaran.D
>

Logic problem - Please help :-(

Hi all,
I have a situation that I need some help with as soon as possible. If
you?re good at logic puzzles then please help as soon as you have a
minute :-)
As some of you know I have a situation in which I need to copy data from
a table in QuickRes, to an identical table in a new database (called
Arrivals). The databases are on physically separate machines. For
operational reasons, the query that copies the data over from QuickRes
to Arrivals will be kicked off by a Web Service, which will then return
the data as XML.
I only want to copy changed rows. Each time a row is changed, its
LastUpdated field is changed to represent the time the update took
place. The idea is simply that each time the synchronisation process
occurs, it will copy across any rows changed since the last time the
sync process ran.
I'm worried about this though because it seems to me that there might be
the possibility of missing some changes under the following
circumstances (or some permutation of them anyway):
Consider:
Time Event
10:46:10 The query to copy all changed rows begins. It starts returning
any rows that have been updated since, say, 10:31:00.
10:46:11 Under a separate thread, an update statement us executed that
changes a row in the target table. It's LastUpdated field is changed to
10:46:11. The synchronisation query does *not* detect this row change
because it is just about to complete.
10:46:12 The synchronisation process completes at 10:46:12
The next time the sync process executes, its going to request any rows
changed since 10:46:12.
This will *not* include the row changed in step two because it's
LastUpdated value is 10:46:11
In other words, this update is lost and won't be brought over.
Questions:
1. Have I identified a real problem here, or am I worried about nothing
- perhaps because SQL Server somehow "magically" takes care of stuff
like this. It seems like a real problem to me, but I'm not very good at
logic stuff like this!
2. Assuming there is a real problem - what do I need to do to fix it?
Would putting the sync query in a transaction prevent the problem? I've
thought about that one and it seems that even in a transaction, because
the computer is so fast, the times entered into the last updated field
could still cause this problem.
I did think about using the actual Timestamp data type, but I remember
reading somewhere that the timestamp is based on the machine that it was
generated on. I was worried that it might not work because of there
being two machines involved in the process.
I know how to deal with the synchronisation process in several different
ways but for the moment we?re assuming that typical sync methods such as
transactional replication are off the table.
Any help or advice would be very greatly appreciated :-)
Many thanks
Kindest Regards
SimonHi Simon,
Why didnt you try Update trigger and Insert trigger '
If you have Inserted new rows or updated several rows, you can insert
in to a temporary table and later you can transfer into a destination
table. What you want to do is You have to truncate that temporary table
after transfering the table.
Regards,
Muthu kumaran .D
(Unknown Is Ocean)|||Hi Mutha
Hmmm... Thats an interesting idea.
I'll definately have to have a think about that one - it might just do
the trick! :-)
Many thanks for your advice
Simon|||Simon.
Something else you may consider is using the max last update date from the
synchronised rows instead of the completion time for the thread.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:%23bHjNBaeGHA.3468@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I have a situation that I need some help with as soon as possible. If you?re
> good at logic puzzles then please help as soon as you have a minute :-)
> As some of you know I have a situation in which I need to copy data from a
> table in QuickRes, to an identical table in a new database (called
> Arrivals). The databases are on physically separate machines. For
> operational reasons, the query that copies the data over from QuickRes to
> Arrivals will be kicked off by a Web Service, which will then return the
> data as XML.
> I only want to copy changed rows. Each time a row is changed, its
> LastUpdated field is changed to represent the time the update took place.
> The idea is simply that each time the synchronisation process occurs, it
> will copy across any rows changed since the last time the sync process
> ran.
> I'm worried about this though because it seems to me that there might be
> the possibility of missing some changes under the following circumstances
> (or some permutation of them anyway):
> Consider:
> Time Event
> 10:46:10 The query to copy all changed rows begins. It starts returning
> any rows that have been updated since, say, 10:31:00.
> 10:46:11 Under a separate thread, an update statement us executed that
> changes a row in the target table. It's LastUpdated field is changed to
> 10:46:11. The synchronisation query does *not* detect this row change
> because it is just about to complete.
> 10:46:12 The synchronisation process completes at 10:46:12
> The next time the sync process executes, its going to request any rows
> changed since 10:46:12.
> This will *not* include the row changed in step two because it's
> LastUpdated value is 10:46:11
> In other words, this update is lost and won't be brought over.
> Questions:
> 1. Have I identified a real problem here, or am I worried about nothing -
> perhaps because SQL Server somehow "magically" takes care of stuff like
> this. It seems like a real problem to me, but I'm not very good at logic
> stuff like this!
> 2. Assuming there is a real problem - what do I need to do to fix it?
> Would putting the sync query in a transaction prevent the problem? I've
> thought about that one and it seems that even in a transaction, because
> the computer is so fast, the times entered into the last updated field
> could still cause this problem.
> I did think about using the actual Timestamp data type, but I remember
> reading somewhere that the timestamp is based on the machine that it was
> generated on. I was worried that it might not work because of there being
> two machines involved in the process.
> I know how to deal with the synchronisation process in several different
> ways but for the moment we?re assuming that typical sync methods such as
> transactional replication are off the table.
> Any help or advice would be very greatly appreciated :-)
> Many thanks
> Kindest Regards
> Simon|||On Wed, 17 May 2006 11:54:42 +0100, Simon Harvey wrote:
(snip)
>Questions:
>1. Have I identified a real problem here, or am I worried about nothing
>- perhaps because SQL Server somehow "magically" takes care of stuff
>like this. It seems like a real problem to me, but I'm not very good at
>logic stuff like this!
Hi Simon,
If you wrote the process to identify changed roows and export them as
XML, then SQL Server will do nothing magically - it will do exactly what
yoou tell it to do. (All else would qualify as a bug).
Would it be a major problem if a changed row can be exported twice (in
rare cases)? If not, thhen there's a simple change - have the export
process select all rows changed since the START, rather than the end, of
the previous execution.
>I did think about using the actual Timestamp data type
Don't. The name is misleading. A timestamp column has nothing to do with
timestamps. It's only intended to be used for optimistic concurrency
control.
--
Hugo Kornelis, SQL Server MVP|||On 17 May 2006 07:25:54 -0700, mkumaran wrote:
>Hi Simon,
>Why didnt you try Update trigger and Insert trigger '
>If you have Inserted new rows or updated several rows, you can insert
>in to a temporary table and later you can transfer into a destination
>table. What you want to do is You have to truncate that temporary table
>after transfering the table.
Hi Muthu,
That would work, but only if Simon makes sure that the temporary table
is locked as soon as the export process starts and that this lock is
held until the TRUNCATE TABLE command has been executed. Otherwise,
there's still the possibility that a row is inserted in the temp table
after the actual export but before the TRUNCATE TABLE.
Of course, such lokcing would seriously hampper concurrency...
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo Kornelis,
you are correct. But What I have thought is the ideal transferring time
is very fast enough if we are using Insert Into Command. Even if new
rows are inserted when we are tranferring data, It also getting
transfered I think.If not We can check the existing of such rows and
than we can insert it or update it.
Please tell me Am I Correct'
--
Muthu Kumaran.D|||Hi Guys
Thanks so much for your help!
I think I'm going to go for the trigger option on this one. Seems a bit
simpler to understand and implement - and I can be reasonably confident
that it's working cos I actually understand it!
In terms of performance, I'm not very concerned - this refresh process
is going to be running frequently enough that there will only ever be a
few rows in the temp tables, so locking it will be for a very short
amount of time.
Many thanks again for all your help
Simon
mkumaran wrote:
> Hi Hugo Kornelis,
> you are correct. But What I have thought is the ideal transferring time
> is very fast enough if we are using Insert Into Command. Even if new
> rows are inserted when we are tranferring data, It also getting
> transfered I think.If not We can check the existing of such rows and
> than we can insert it or update it.
> Please tell me Am I Correct'
> --
> Muthu Kumaran.D
>

Logic problem - a challenge if you will

This is killing, me and I think that I'm failing to see something simple here:

If I have a table with logins and datetimes. I need to output any logins that have logged in more than 3 times in any 3 hour period of time, and how may times it was done. For example:

Login table:
user1 01:00
user2 01:13
user1 02:32
user2 01:17
user1 01:12
user2 07:00
user1 04:10

I would need:
user1 2 <-- (times user 1 logged in more than 3 times in 3 hours)

Because:
01:00, 02:32, 01:12 are all within 3 hours of each other
02:32, 01:12, 04:10 are all within 3 hours of each other

Obviously I have alot more data than this, but I'm failing to grasp the logic properly. Trying to do this in a Sybase stored proc.create table #tmp (
login char(5),
log_time smalldatetime
)

insert into #tmp
select 'user1', '01:00'
union all
select 'user2', '01:13'
union all
select 'user1', '02:32'
union all
select 'user2', '01:17'
union all
select 'user1', '01:12'
union all
select 'user2', '07:00'
union all
select 'user1', '04:10'

select rs1.login, count(*) as cnt
from (
select #tmp.Login
from #tmp inner join (
select login, log_time as mintime, dateadd(hh,3,log_time) as maxtime from #tmp) rs
on #tmp.login=rs.login
where #tmp.log_time between rs.mintime and rs.maxtime
group by #tmp.login, rs.mintime, rs.maxtime
having count(#tmp.log_time)>=3) rs1
group by rs1.login

drop table #tmp|||This would be assuming a limited data set, though, correct? Suppose I do not know how many logins and times there are?|||This would be assuming a limited data set, though, correct? Why are you thinking that? Did you try the query?

Logic problem

Hi,

This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.

The table:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".

I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then:
if [q5]= 'Happy' then change it to 'Unhappy'
if [q5]= 'Unhappy' then change it to 'Happy'

The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?Is this an abstraction of a real world problem?
Anyway - check out CASE in BoL - it is just the ticket for you.|||Is this an abstraction of a real world problem?
Anyway - check out CASE in BoL - it is just the ticket for you.

I'm not sure what you mean by abstraction? It IS a real-world problem: "Real" as in "my boss is swearing at me". :eek:

But you're right: CASE does the job perfectly. Thankyou very much :)|||Generally it's better to use a lookup table to store your Happy/Unhappy/Neither strings and refer to them through fk ids in tblQuiz. That way you don't waste space storing the same string over and over.

If there are truly only 3 possible values, you could be using a tinyint in the q5 column instead of nvarchar(100), which is a pretty big space savings. this begins to matter pretty quickly in large databases with millions of rows.

Logic on UPDATE query

I am dealing with two tables and I am trying to take one column from a table and match the records with another table and append the data of that column.

I used an update query that looks like this:

UPDATE Acct_table Set Acct_table.Score =
(Select Score_tbl.Score from Score_tbl
Where Acct_table.Acctnb = Score_tbl.Acctnb

This process has been running for over an hour and a half and is building a large log file. I am curious to know if there is a better command that I can use in order to join the tables and then just drop the column from one to the other. Both tables are indexed on Acctnb.

Any insight would truly help.
Thanks!UPDATE A Set A.Score = S.Score
from Acct_table A
JOIN Score_tbl S
ON A.Acctnb = S.Acctnb|||Does anyone else hear an echo?|||Does anyone else hear an echo?No, not a thing. Why?

Yes, I deleted Thrasy's duplicated post

-PatP|||aaaaaaaaaa

logic of sum() with joins and using query hint

hi everybody
I have a question about the sum() function. when I join two tabeles and one
of them is the main table which I used in the from statement, sum function I
used for the joined table is giving the sum incorrectly(it is governing time
s
the other joined tabele). how can i eleminate the problem? do I have to use
query hint or someting. if so how?
the query is the basis of a fifo report. I didnt want to use a cursor and so
I wrote such a query. I solved the problem with UDFs but i want to learn the
logic and how to use query hint for sum function
id is primary key for both tables
CREATE TABLE [order] (
[id] [int] NULL ,
[date_order] [datetime] NULL ,
[product] [char] (10) ,
[quantity] [int] NULL
)
go
CREATE TABLE [distribute] (
[id] [int] NULL ,
[date_distribute] [datetime] NULL ,
[product] [char] (10),
[quantity] [int] NULL
)
--sample data
insert into distribute (id,date_distribute,product,quantity) values
(51,'2005-01-01','aaa',10)
insert into distribute (id,date_distribute,product,quantity) values
(52,'2005-01-04','aaa',13)
insert into distribute (id,date_distribute,product,quantity) values
(53,'2005-01-05','aaa',3)
insert into distribute (id,date_distribute,product,quantity) values
(54,'2005-01-06','aaa',-2)
insert into distribute (id,date_distribute,product,quantity) values
(55,'2005-01-07','aaa',8)
insert into distribute (id,date_distribute,product,quantity) values
(56,'2005-01-08','aaa',45)
insert into distribute (id,date_distribute,product,quantity) values
(57,'2005-01-10','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(11,'2005-01-01','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(12,'2005-01-03','aaa',20)
insert into [order] (id,date_order,product,quantity) values
(13,'2005-01-05','aaa',30)
insert into [order] (id,date_order,product,quantity) values
(14,'2005-01-08','aaa',15)
insert into [order] (id,date_order,product,quantity) values
(15,'2005-01-09','aaa',10)
--query
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
isnull(sum(d2.quantity),0)as forobservingdist,
isnull(sum(o2.quantity),0)as forobservingord
from [order] o1
left join [order] o2 on o1.id>=o2.id and o1.product=o2.product
left join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
left join Distribute d2 on d2.id<=d1.id and d2.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product
the query below has the same logic with the above query. I used UDFs for the
sum functions.
when you run the queries, forobservingord column must be the same as
forobservingord in the results of the query below
CREATE FUNCTION getorderdogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(o2.quantity) from [order] o2 where o2.id<=@.id and
o2.product=@.product
RETURN @.sum
END
go
CREATE FUNCTION getdistributedogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(d2.quantity) from Distribute d2 where d2.id<=@.id and
d2.product=@.product
RETURN isnull(@.sum,0)
END
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
d1.date_distribute,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.productYou can achieve the same results (with better performance) by using
subqueries instead of UDF-s:
select o1.id, o1.date_order, o1.product,o1.quantity,
d1.id as Distribute_id, d1.date_distribute, (
select sum(d2.quantity) from Distribute d2
where d2.id<=d1.id and d2.product=d1.product
) as forobservingdist, (
select sum(o2.quantity) from [order] o2
where o2.id<=o1.id and o2.product=o1.product
) as forobservingord
from dbo.[order] o1
left outer join Distribute d1
on o1.date_order<=d1.date_distribute and o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
Razvan|||tnx Razvan sure I didnt think this:))
do you have any info about using query hint works like that?
also the last form of the query is like that
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
then
isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribute
dogan(d1.id,d1.product))
when
d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
then
(o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.
id,d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))>o1.quantity
then
o1.quantity
when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))<0
then
0
else
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))
end as remainingorder,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having
((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produc
t))+isnull(d1.quantity,0)>0
and
o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.i
d,d1.product)))
or isnull(d1.quantity,0)=0
I changed the UDFs with subqueries and its working. I looked at the query
execution plan and it looks more simple with the udfs. are you sure this wil
l
work with better performance? probably you are:))
also plan of the query with subqueries shows many hash matches. Can't I use
query hint making hash matches with left join?
thanks again
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
then
isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
when
d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
then
(o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))>o1.quantity
then
o1.quantity
when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))<0
then
0
else
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
end as remainingorder,
(select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
d2.product=d1.product)as forobservingdist,
(select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
and o1.quantity>((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
or isnull(d1.quantity,0)=0
"Razvan Socol" wrote:

> You can achieve the same results (with better performance) by using
> subqueries instead of UDF-s:
> select o1.id, o1.date_order, o1.product,o1.quantity,
> d1.id as Distribute_id, d1.date_distribute, (
> select sum(d2.quantity) from Distribute d2
> where d2.id<=d1.id and d2.product=d1.product
> ) as forobservingdist, (
> select sum(o2.quantity) from [order] o2
> where o2.id<=o1.id and o2.product=o1.product
> ) as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1
> on o1.date_order<=d1.date_distribute and o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
> Razvan
>|||> do you have any info about using query hint works like that?
There are no query hints that modify the results; the hints are used
only for optimizations. For informations about hints, see the "query
hints" topic in Books Online:
http://msdn.microsoft.com/library/e..._qd_03_8upf.asp

> I looked at the query execution plan and it looks more simple with the udfs.[/colo
r]
The execution plan for a query that calls multi-statement UDF-s (scalar
or table-valued) do not contain the cost of the statements contained in
the UDF. Only in-line table-valued UDF-s are expaded in the execution
plan of the calling query.
> are you sure this will work with better performance? probably you are:))
To be sure, test it yourself using Profiler or using something like
this:
DECLARE @.t datetime
SET @.t=GETDATE()
SELECT ...
PRINT CONVERT(varchar(10),DATEDIFF(ms,@.t,GETDA
TE()))+' ms'

> also plan of the query with subqueries shows many hash matches.
> Can't I use query hint making hash matches with left join?
SQL Server can execute joins in one of three ways: nested loops, merge
or hash. These ways can be used for inner joins, as well as for outer
joins (left joins, right joins or full outer joins). The query
optimizer automatically selects the best way to execute a join (nested
loops, merge or hash), based on the number of rows and the available
indexes on the joined columns. If the optimizer used a hash join,
that's because this is probably the best way to execute the query in
this particular case. Adding a join hint will force SQL Server to use
nested loops joins or merge joins, but in most cases that would have an
inferior performance. A better idea would be to add indexes to the
columns that are used in the join (in this case: product and id) and
let the query optimizer choose the way it executes the query (the query
optimizer may realize that it's better not to use the index on the id
column, and use only the index on the product column, for example).
For more informations about the ways a join can be executed, see
"Advanced Query Tuning Concepts" topic in Books Online:
http://msdn.microsoft.com/library/e..._tun_1_8pv7.asp
Razvan|||thank you very much for the easy performance test code:))
I've tried it both query gives sometimes 10ms sometimes 20ms result
I know I had to test it with much more data. thanks again.
I've tried to add index as you but results did not changed.
also I tried left hash join which returns a warning about changing the plan
and didnt change anything in result set.
then I tried some unconscious synthax but these instinctly tries didnt
change the result:)
so I give up:))
thanks
"Razvan Socol" wrote:

> There are no query hints that modify the results; the hints are used
> only for optimizations. For informations about hints, see the "query
> hints" topic in Books Online:
> http://msdn.microsoft.com/library/e..._qd_03_8upf.asp
>
> The execution plan for a query that calls multi-statement UDF-s (scalar
> or table-valued) do not contain the cost of the statements contained in
> the UDF. Only in-line table-valued UDF-s are expaded in the execution
> plan of the calling query.
>
> To be sure, test it yourself using Profiler or using something like
> this:
> DECLARE @.t datetime
> SET @.t=GETDATE()
> SELECT ...
> PRINT CONVERT(varchar(10),DATEDIFF(ms,@.t,GETDA
TE()))+' ms'
>
> SQL Server can execute joins in one of three ways: nested loops, merge
> or hash. These ways can be used for inner joins, as well as for outer
> joins (left joins, right joins or full outer joins). The query
> optimizer automatically selects the best way to execute a join (nested
> loops, merge or hash), based on the number of rows and the available
> indexes on the joined columns. If the optimizer used a hash join,
> that's because this is probably the best way to execute the query in
> this particular case. Adding a join hint will force SQL Server to use
> nested loops joins or merge joins, but in most cases that would have an
> inferior performance. A better idea would be to add indexes to the
> columns that are used in the join (in this case: product and id) and
> let the query optimizer choose the way it executes the query (the query
> optimizer may realize that it's better not to use the index on the id
> column, and use only the index on the product column, for example).
> For more informations about the ways a join can be executed, see
> "Advanced Query Tuning Concepts" topic in Books Online:
> http://msdn.microsoft.com/library/e..._tun_1_8pv7.asp
> Razvan
>