Hi !
I have some office documents which I am storing as image
type (as blobs) in a table. I have some additional header
data in the blob other than the content of the office
documents. Is there a way to integrate just the content
of teh office document with the SQL server search?
I know that one way to do it is by implementing IFilter.
Can someone explain how that will work or send me
appropriate links for that.
Another question is about the html files that have images
in it. How does that get stored in the database and yet
qualify for SQL server search? How can one store the html
file and the folder with images as blobs and yet enable
the search on the document?
Any help would be appreciated. I am using SQL server 2000
with all the service packs applied.
Thanks.
SQL FTS can only index document contents, not properties. Header and
footers of word docs are indexed as part of the document body.
So if by header data you mean document summary or custom office properties,
these are not indexed by SQL FTS. If you mean the header of the footer this
will work.
Images in html files referenced by metatags, ie img src will not be indexed,
as only document properties are indexed, not the contents of meta or src
tags.
The indexing that is done of image documents is rudimentary. Some of the
custom image iFilters do expose interfaces to index these properties, but
not in SQL FTS. In other search services like Sharepoint portal server,
Indexing services, and Exchange content indexing it is possible to index
some properties and ocr'd content of tiffs. But these are normally not
indexed as attachments or embedded objects of documents, and are not indexed
ever when they are parts of html docs. Again SQL FTS does not index them as
they are, for the most part properties.
Your best approach would be to extract the textual data from these documents
and store the metadata/properties in column in the table you are FTI'ing.
"kay" <anonymous@.discussions.microsoft.com> wrote in message
news:f6fa01c43de7$0591afb0$a501280a@.phx.gbl...
> Hi !
> I have some office documents which I am storing as image
> type (as blobs) in a table. I have some additional header
> data in the blob other than the content of the office
> documents. Is there a way to integrate just the content
> of teh office document with the SQL server search?
> I know that one way to do it is by implementing IFilter.
> Can someone explain how that will work or send me
> appropriate links for that.
> Another question is about the html files that have images
> in it. How does that get stored in the database and yet
> qualify for SQL server search? How can one store the html
> file and the folder with images as blobs and yet enable
> the search on the document?
> Any help would be appreciated. I am using SQL server 2000
> with all the service packs applied.
> Thanks.
|||Kay,
Could you provide more info in regards to your table structures, i.e.,
CREATE TABLE statements as this may be possible, if I understand your
requirement correctly. There is a way to integrate the content of the office
documents with SQL Server Full-Text Search (FTS) in SQL Server 2000.
Checkout the SQL Server 2000 Books Online (BOL) title "Filtering Supported
File Types"
As for your images (jpg files, etc.) you will need to store them separately
in a column defined with the IMAGE datatype. See the following KB articles
on importing & extracting binary files (images) into and out of SQL Server:
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/default...b;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/default...b;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502
Depending upon what you want to search on, you can implement a JPEG IFilter
or use the achnor text in the HTML file as the search string for the image.
If you have further questions, please post your table structures as well as
SQL FTS queries.
Regards,
John
"kay" <anonymous@.discussions.microsoft.com> wrote in message
news:f6fa01c43de7$0591afb0$a501280a@.phx.gbl...
> Hi !
> I have some office documents which I am storing as image
> type (as blobs) in a table. I have some additional header
> data in the blob other than the content of the office
> documents. Is there a way to integrate just the content
> of teh office document with the SQL server search?
> I know that one way to do it is by implementing IFilter.
> Can someone explain how that will work or send me
> appropriate links for that.
> Another question is about the html files that have images
> in it. How does that get stored in the database and yet
> qualify for SQL server search? How can one store the html
> file and the folder with images as blobs and yet enable
> the search on the document?
> Any help would be appreciated. I am using SQL server 2000
> with all the service packs applied.
> Thanks.
|||Thanks a lot Hilary, for your prompt reply.
I could get the Office document blobs working with FTS,
that is not where i faced the problems. I have a couple
of questions regarding issues pertaining these.
1) I want to add some of my own customized data that our
programming system is using other than the office
document blob. Say, I first add my own serialized data in
the blob and then add the office document data in the
blob and upload it in the image field. If I do that, is
it possible to still be able to use the FTS on the part
of the blob which is the actual office document data? I
mean, is there some way that I could write some code that
can give the FTS only the relevant data to be used for
indexing.
2) If I have a word document which has embedded pictures
and then I save it as a filtered Html, I get some images
in a folder and the images are linked in the html file.
How can I upload this filtered html document as a blob?
Is it that the folder containing the images has to be
stored separately from teh html blob? or is it that there
is some way in which both html and the folder with the
image are added in the same blob and yet work with FTS?
I hope my questions are clear. Any help from you would be
appreciated. I always can split up the blob and store as
separate fields, but if there is a way to do them all up
as the same blob, it would be great.
Thanks a lot !
Regards,
kay
>--Original Message--
>SQL FTS can only index document contents, not
properties. Header and
>footers of word docs are indexed as part of the document
body.
>So if by header data you mean document summary or custom
office properties,
>these are not indexed by SQL FTS. If you mean the header
of the footer this
>will work.
>Images in html files referenced by metatags, ie img src
will not be indexed,
>as only document properties are indexed, not the
contents of meta or src
>tags.
>The indexing that is done of image documents is
rudimentary. Some of the
>custom image iFilters do expose interfaces to index
these properties, but
>not in SQL FTS. In other search services like Sharepoint
portal server,
>Indexing services, and Exchange content indexing it is
possible to index
>some properties and ocr'd content of tiffs. But these
are normally not
>indexed as attachments or embedded objects of documents,
and are not indexed
>ever when they are parts of html docs. Again SQL FTS
does not index them as
>they are, for the most part properties.
>Your best approach would be to extract the textual data
from these documents
>and store the metadata/properties in column in the table
you are FTI'ing.
>
>"kay" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:f6fa01c43de7$0591afb0$a501280a@.phx.gbl...
image[vbcol=seagreen]
header[vbcol=seagreen]
IFilter.[vbcol=seagreen]
images[vbcol=seagreen]
html[vbcol=seagreen]
2000
>
>.
>
|||Thanks a lot John, for your prompt reply.
I have posted the same question content to Hilary too. I
would appreciate it if you could also send me your
thoughts and expertise on this.
I could get the Office document blobs working with FTS,
that is not where i faced the problems. The search
results are satisfactory. I have a couple of questions
regarding issues pertaining these.
1) I want to add some of my own customized data that our
programming system is using other than the office
document blob. Say, I first add my own serialized data in
the blob and then add the office document data in the
blob and upload it in the image field. If I do that, is
it possible to still be able to use the FTS on the part
of the blob which is the actual office document data? I
mean, is there some way that I could write some code that
can give the FTS only the relevant data to be used for
indexing.
2) If I have a word document which has embedded pictures
and then I save it as a filtered Html, I get some images
in a folder and the images are linked in the html file.
How can I upload this filtered html document as a blob?
Is it that the folder containing the images has to be
stored separately from teh html blob? or is it that there
is some way in which both html and the folder with the
image are added in the same blob and yet work with FTS?
I hope my questions are clear. Any help from you would be
appreciated. I always can split up the blob and store as
separate fields, but if there is a way to do them all up
as the same blob, it would be great.
Thanks a lot !
Regards,
kay
>--Original Message--
>Kay,
>Could you provide more info in regards to your table
structures, i.e.,
>CREATE TABLE statements as this may be possible, if I
understand your
>requirement correctly. There is a way to integrate the
content of the office
>documents with SQL Server Full-Text Search (FTS) in SQL
Server 2000.
>Checkout the SQL Server 2000 Books Online (BOL)
title "Filtering Supported
>File Types"
>As for your images (jpg files, etc.) you will need to
store them separately
>in a column defined with the IMAGE datatype. See the
following KB articles
>on importing & extracting binary files (images) into and
out of SQL Server:
>258038 (Q258038) HOWTO: Access and Modify SQL Server
BLOB Data by Using the
>ADO Stream Object
>http://support.microsoft.com/?kbid=258038
>309158 (Q309158) HOW TO: Read and Write BLOB Data by
Using ADO.NET with C#
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;309158
>308042 (Q308042) HOW TO: Read and Write BLOB Data by
Using ADO.NET with
>VB.NET
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;308042
>326502 (Q326502) HOW TO: Read and Write BLOB Data by
Using ADO.NET Through
>ASP.NET
>http://support.microsoft.com/?id=326502
>Depending upon what you want to search on, you can
implement a JPEG IFilter
>or use the achnor text in the HTML file as the search
string for the image.
>If you have further questions, please post your table
structures as well as
>SQL FTS queries.
>Regards,
>John
>
>
>"kay" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:f6fa01c43de7$0591afb0$a501280a@.phx.gbl...
image[vbcol=seagreen]
header[vbcol=seagreen]
IFilter.[vbcol=seagreen]
images[vbcol=seagreen]
html[vbcol=seagreen]
2000
>
>.
>
|||1) no, unless the iFilter which is associated with the extension of the document knows how to handle attachments or embedded documents, this is not possible. The iFilter interface is able to handle streams and storages, so the question is whether this iF
ilter has implemented it.
2) save the html doc as a web archive page (mht). This will contains all the html, images, etc. You will be able to only index the document body, and not of the "attachements", ie only pure text is exposed.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- kay wrote: --
Thanks a lot Hilary, for your prompt reply.
I could get the Office document blobs working with FTS,
that is not where i faced the problems. I have a couple
of questions regarding issues pertaining these.
1) I want to add some of my own customized data that our
programming system is using other than the office
document blob. Say, I first add my own serialized data in
the blob and then add the office document data in the
blob and upload it in the image field. If I do that, is
it possible to still be able to use the FTS on the part
of the blob which is the actual office document data? I
mean, is there some way that I could write some code that
can give the FTS only the relevant data to be used for
indexing.
2) If I have a word document which has embedded pictures
and then I save it as a filtered Html, I get some images
in a folder and the images are linked in the html file.
How can I upload this filtered html document as a blob?
Is it that the folder containing the images has to be
stored separately from teh html blob? or is it that there
is some way in which both html and the folder with the
image are added in the same blob and yet work with FTS?
I hope my questions are clear. Any help from you would be
appreciated. I always can split up the blob and store as
separate fields, but if there is a way to do them all up
as the same blob, it would be great.
Thanks a lot !
Regards,
kay
>--Original Message--
>SQL FTS can only index document contents, not
properties. Header and
>footers of word docs are indexed as part of the document
body.[vbcol=seagreen]
office properties,
>these are not indexed by SQL FTS. If you mean the header
of the footer this[vbcol=seagreen]
>will work.
will not be indexed,
>as only document properties are indexed, not the
contents of meta or src[vbcol=seagreen]
>tags.
rudimentary. Some of the
>custom image iFilters do expose interfaces to index
these properties, but
>not in SQL FTS. In other search services like Sharepoint
portal server,
>Indexing services, and Exchange content indexing it is
possible to index
>some properties and ocr'd content of tiffs. But these
are normally not
>indexed as attachments or embedded objects of documents,
and are not indexed
>ever when they are parts of html docs. Again SQL FTS
does not index them as[vbcol=seagreen]
>they are, for the most part properties.
from these documents
>and store the metadata/properties in column in the table
you are FTI'ing.[vbcol=seagreen]
message[vbcol=seagreen]
>news:f6fa01c43de7$0591afb0$a501280a@.phx.gbl...
image[vbcol=seagreen]
header[vbcol=seagreen]
IFilter.[vbcol=seagreen]
images[vbcol=seagreen]
html[vbcol=seagreen]
2000
>
|||Hi !
Thanks again for your reply. Have you come across any
working examples of a customized IFilter code? If so, can
you send me the link?
Thanks for your help.
Regards,
kay
>--Original Message--
>1) no, unless the iFilter which is associated with the
extension of the document knows how to handle attachments
or embedded documents, this is not possible. The iFilter
interface is able to handle streams and storages, so the
question is whether this iFilter has implemented it.
>2) save the html doc as a web archive page (mht). This
will contains all the html, images, etc. You will be able
to only index the document body, and not of
the "attachements", ie only pure text is exposed.
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
> -- kay wrote: --
>
> Thanks a lot Hilary, for your prompt reply.
> I could get the Office document blobs working with
FTS,
> that is not where i faced the problems. I have a
couple
> of questions regarding issues pertaining these.
> 1) I want to add some of my own customized data
that our
> programming system is using other than the office
> document blob. Say, I first add my own serialized
data in
> the blob and then add the office document data in
the
> blob and upload it in the image field. If I do
that, is
> it possible to still be able to use the FTS on the
part
> of the blob which is the actual office document
data? I
> mean, is there some way that I could write some
code that
> can give the FTS only the relevant data to be used
for
> indexing.
> 2) If I have a word document which has embedded
pictures
> and then I save it as a filtered Html, I get some
images
> in a folder and the images are linked in the html
file.
> How can I upload this filtered html document as a
blob?
> Is it that the folder containing the images has to
be
> stored separately from teh html blob? or is it that
there
> is some way in which both html and the folder with
the
> image are added in the same blob and yet work with
FTS?
> I hope my questions are clear. Any help from you
would be
> appreciated. I always can split up the blob and
store as
> separate fields, but if there is a way to do them
all up[vbcol=seagreen]
> as the same blob, it would be great.
> Thanks a lot !
> Regards,
> kay
> properties. Header and
document[vbcol=seagreen]
> body.
custom[vbcol=seagreen]
> office properties,
header[vbcol=seagreen]
> of the footer this
img src[vbcol=seagreen]
> will not be indexed,
> contents of meta or src
> rudimentary. Some of the
index[vbcol=seagreen]
> these properties, but
Sharepoint[vbcol=seagreen]
> portal server,
it is[vbcol=seagreen]
> possible to index
these[vbcol=seagreen]
> are normally not
documents,[vbcol=seagreen]
> and are not indexed
FTS[vbcol=seagreen]
> does not index them as
textual data[vbcol=seagreen]
> from these documents
table[vbcol=seagreen]
> you are FTI'ing.
wrote in[vbcol=seagreen]
> message
as[vbcol=seagreen]
> image
additional[vbcol=seagreen]
> header
office[vbcol=seagreen]
content[vbcol=seagreen]
search?[vbcol=seagreen]
> IFilter.
have[vbcol=seagreen]
> images
and yet[vbcol=seagreen]
the[vbcol=seagreen]
> html
enable[vbcol=seagreen]
server
> 2000
>.
>
|||MSDN is your best source for this. I'd also check out iFilterShop.com for custom iFilters.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Would you know if a customized IFilter could call the
Office filters?
>extension of the document knows how to handle
attachments
>or embedded documents, this is not possible. The
iFilter
>interface is able to handle streams and storages, so the
>question is whether this iFilter has implemented it.
>will contains all the html, images, etc. You will be
able[vbcol=seagreen]
>to only index the document body, and not of
>the "attachements", ie only pure text is exposed.
>FTS,
>couple
>that our
>data in
>the
>that, is
>part
>data? I
>code that
>for
>pictures
>images
>file.
>blob?
>be
that[vbcol=seagreen]
>there
>the
>FTS?
>would be
>store as
>all up
>document
or[vbcol=seagreen]
>custom
>header
>img src
>index
>Sharepoint
>it is
>these
>documents,
>FTS
>textual data
the[vbcol=seagreen]
>table
>wrote in
>as
>additional
>office
>content
>search?
me[vbcol=seagreen]
>have
>and yet
store[vbcol=seagreen]
>the
yet
>enable
>server
>.
>
|||Would you know if a customized IFilter could call the
Office filters, and these office filters can then be used
for the catalog building?
>--Original Message--
>MSDN is your best source for this. I'd also check out
iFilterShop.com for custom iFilters.
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>.
>
sql
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Tuesday, March 27, 2012
Customized Error Message
Hi,
I have a table like this:
CREATE TABLE Table1 (
[ID] int NOT NULL Primary Key)
After inserting some records, obviously when I try to update all records to
a particular value, SQL server raises an error (number 2627) that indicates
the "Violation of PRIMARY KEY constraint" has happened.
What I need to do is to return an error message instead of SQL server's.
Suppose that I have this SP:
CREATE PROCEDURE UpdateTable1 AS
BEGIN TRAN
UPDATE table1 set id=1
IF @.@.Error = 2627
begin
print 'Duplicate Value'
raiserror('Duplicate Value',16,1)
rollback tran
end
else
begin
print 'Update was ok'
commit tran
end
GO
SQL server returns two error descriptions when I execute this SP: One from
its original messages and the other one from my raiserror statement.
I want to display my own error description to the client without writing
extra code for error handling in my client app(and also for centralizing my
own error descriptions those are returned instead of SQL server's error
messages).
Any help would be greatly appreciated.
Amin
See my reply in .programming. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message news:uy1qx39FEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table like this:
>
> CREATE TABLE Table1 (
> [ID] int NOT NULL Primary Key)
>
> After inserting some records, obviously when I try to update all records to
> a particular value, SQL server raises an error (number 2627) that indicates
> the "Violation of PRIMARY KEY constraint" has happened.
> What I need to do is to return an error message instead of SQL server's.
> Suppose that I have this SP:
>
> CREATE PROCEDURE UpdateTable1 AS
> BEGIN TRAN
> UPDATE table1 set id=1
> IF @.@.Error = 2627
> begin
> print 'Duplicate Value'
> raiserror('Duplicate Value',16,1)
> rollback tran
> end
> else
> begin
> print 'Update was ok'
> commit tran
> end
> GO
> SQL server returns two error descriptions when I execute this SP: One from
> its original messages and the other one from my raiserror statement.
> I want to display my own error description to the client without writing
> extra code for error handling in my client app(and also for centralizing my
> own error descriptions those are returned instead of SQL server's error
> messages).
> Any help would be greatly appreciated.
> Amin
>
>
>
I have a table like this:
CREATE TABLE Table1 (
[ID] int NOT NULL Primary Key)
After inserting some records, obviously when I try to update all records to
a particular value, SQL server raises an error (number 2627) that indicates
the "Violation of PRIMARY KEY constraint" has happened.
What I need to do is to return an error message instead of SQL server's.
Suppose that I have this SP:
CREATE PROCEDURE UpdateTable1 AS
BEGIN TRAN
UPDATE table1 set id=1
IF @.@.Error = 2627
begin
print 'Duplicate Value'
raiserror('Duplicate Value',16,1)
rollback tran
end
else
begin
print 'Update was ok'
commit tran
end
GO
SQL server returns two error descriptions when I execute this SP: One from
its original messages and the other one from my raiserror statement.
I want to display my own error description to the client without writing
extra code for error handling in my client app(and also for centralizing my
own error descriptions those are returned instead of SQL server's error
messages).
Any help would be greatly appreciated.
Amin
See my reply in .programming. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message news:uy1qx39FEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table like this:
>
> CREATE TABLE Table1 (
> [ID] int NOT NULL Primary Key)
>
> After inserting some records, obviously when I try to update all records to
> a particular value, SQL server raises an error (number 2627) that indicates
> the "Violation of PRIMARY KEY constraint" has happened.
> What I need to do is to return an error message instead of SQL server's.
> Suppose that I have this SP:
>
> CREATE PROCEDURE UpdateTable1 AS
> BEGIN TRAN
> UPDATE table1 set id=1
> IF @.@.Error = 2627
> begin
> print 'Duplicate Value'
> raiserror('Duplicate Value',16,1)
> rollback tran
> end
> else
> begin
> print 'Update was ok'
> commit tran
> end
> GO
> SQL server returns two error descriptions when I execute this SP: One from
> its original messages and the other one from my raiserror statement.
> I want to display my own error description to the client without writing
> extra code for error handling in my client app(and also for centralizing my
> own error descriptions those are returned instead of SQL server's error
> messages).
> Any help would be greatly appreciated.
> Amin
>
>
>
customize the table format
I don't want every row divided by a solid line,
how can I customize the table format so every third row
is divided by a solid line? is that even possible in reporting service? I
don't know what to do to make it happen."Britney" <britneychen_2001@.yahoo.com> wrote in message news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> I don't want every row divided by a solid line,
> how can I customize the table format so every third row
> is divided by a solid line? is that even possible in reporting service? I
> don't know what to do to make it happen.
Hi Britney,
You can customize the table format in reporting services.
To have every third row divided by a solid line, the Borderstyle of
the textboxes inside the table have to be set based on the following
expression
=iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
where DataSet1 is the dataset name you are going to use..
Hope this helps you.
Cheers,
Prathima.C|||YES, it worked partially,
the table looked pretty ugly now.
but I want the format with one line only. is that possible?
______________
1 3 4 5
1 4 5 5
2 4 5 6
______________
1 3 4 5
1 4 5 5
2 4 5 6
______________
.........
..........
"Prathima" <prathima.chandramouli@.gmail.com> wrote in message
news:f941b84.0411220300.5888d832@.posting.google.com...
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > I don't want every row divided by a solid line,
> > how can I customize the table format so every third row
> > is divided by a solid line? is that even possible in reporting service?
I
> > don't know what to do to make it happen.
> Hi Britney,
> You can customize the table format in reporting services.
> To have every third row divided by a solid line, the Borderstyle of
> the textboxes inside the table have to be set based on the following
> expression
> =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> where DataSet1 is the dataset name you are going to use..
> Hope this helps you.
> Cheers,
> Prathima.C|||Can u please define the problem more clearly so that i can help u. I'm
not able to get your problem correctly.
"Britney" <britneychen_2001@.yahoo.com> wrote in message news:<u8ELx2K0EHA.3976@.TK2MSFTNGP09.phx.gbl>...
> YES, it worked partially,
> the table looked pretty ugly now.
> but I want the format with one line only. is that possible?
>
> ______________
> 1 3 4 5
> 1 4 5 5
> 2 4 5 6
> ______________
> 1 3 4 5
> 1 4 5 5
> 2 4 5 6
> ______________
> .........
> ..........
>
> "Prathima" <prathima.chandramouli@.gmail.com> wrote in message
> news:f941b84.0411220300.5888d832@.posting.google.com...
> > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > > I don't want every row divided by a solid line,
> > > how can I customize the table format so every third row
> > > is divided by a solid line? is that even possible in reporting service?
> I
> > > don't know what to do to make it happen.
> >
> > Hi Britney,
> >
> > You can customize the table format in reporting services.
> > To have every third row divided by a solid line, the Borderstyle of
> > the textboxes inside the table have to be set based on the following
> > expression
> >
> > =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> >
> > where DataSet1 is the dataset name you are going to use..
> > Hope this helps you.
> > Cheers,
> > Prathima.C|||never mind, I figured it out.
your logic is correct, I specified it in default input of border style, it
draw all the four lines (top, bottom, left ,right) as solid lines, but I
really should just specify it on "bottom" input box of border style.
thanks for your help
"Prathima" <prathima.chandramouli@.gmail.com> wrote in message
news:f941b84.0411232053.66adc74d@.posting.google.com...
> Can u please define the problem more clearly so that i can help u. I'm
> not able to get your problem correctly.
>
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
news:<u8ELx2K0EHA.3976@.TK2MSFTNGP09.phx.gbl>...
> > YES, it worked partially,
> >
> > the table looked pretty ugly now.
> >
> > but I want the format with one line only. is that possible?
> >
> >
> >
> > ______________
> > 1 3 4 5
> > 1 4 5 5
> > 2 4 5 6
> > ______________
> > 1 3 4 5
> > 1 4 5 5
> > 2 4 5 6
> > ______________
> > .........
> > ..........
> >
> >
> >
> > "Prathima" <prathima.chandramouli@.gmail.com> wrote in message
> > news:f941b84.0411220300.5888d832@.posting.google.com...
> > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > > > I don't want every row divided by a solid line,
> > > > how can I customize the table format so every third row
> > > > is divided by a solid line? is that even possible in reporting
service?
> > I
> > > > don't know what to do to make it happen.
> > >
> > > Hi Britney,
> > >
> > > You can customize the table format in reporting services.
> > > To have every third row divided by a solid line, the Borderstyle of
> > > the textboxes inside the table have to be set based on the following
> > > expression
> > >
> > > =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> > >
> > > where DataSet1 is the dataset name you are going to use..
> > > Hope this helps you.
> > > Cheers,
> > > Prathima.C
how can I customize the table format so every third row
is divided by a solid line? is that even possible in reporting service? I
don't know what to do to make it happen."Britney" <britneychen_2001@.yahoo.com> wrote in message news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> I don't want every row divided by a solid line,
> how can I customize the table format so every third row
> is divided by a solid line? is that even possible in reporting service? I
> don't know what to do to make it happen.
Hi Britney,
You can customize the table format in reporting services.
To have every third row divided by a solid line, the Borderstyle of
the textboxes inside the table have to be set based on the following
expression
=iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
where DataSet1 is the dataset name you are going to use..
Hope this helps you.
Cheers,
Prathima.C|||YES, it worked partially,
the table looked pretty ugly now.
but I want the format with one line only. is that possible?
______________
1 3 4 5
1 4 5 5
2 4 5 6
______________
1 3 4 5
1 4 5 5
2 4 5 6
______________
.........
..........
"Prathima" <prathima.chandramouli@.gmail.com> wrote in message
news:f941b84.0411220300.5888d832@.posting.google.com...
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > I don't want every row divided by a solid line,
> > how can I customize the table format so every third row
> > is divided by a solid line? is that even possible in reporting service?
I
> > don't know what to do to make it happen.
> Hi Britney,
> You can customize the table format in reporting services.
> To have every third row divided by a solid line, the Borderstyle of
> the textboxes inside the table have to be set based on the following
> expression
> =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> where DataSet1 is the dataset name you are going to use..
> Hope this helps you.
> Cheers,
> Prathima.C|||Can u please define the problem more clearly so that i can help u. I'm
not able to get your problem correctly.
"Britney" <britneychen_2001@.yahoo.com> wrote in message news:<u8ELx2K0EHA.3976@.TK2MSFTNGP09.phx.gbl>...
> YES, it worked partially,
> the table looked pretty ugly now.
> but I want the format with one line only. is that possible?
>
> ______________
> 1 3 4 5
> 1 4 5 5
> 2 4 5 6
> ______________
> 1 3 4 5
> 1 4 5 5
> 2 4 5 6
> ______________
> .........
> ..........
>
> "Prathima" <prathima.chandramouli@.gmail.com> wrote in message
> news:f941b84.0411220300.5888d832@.posting.google.com...
> > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > > I don't want every row divided by a solid line,
> > > how can I customize the table format so every third row
> > > is divided by a solid line? is that even possible in reporting service?
> I
> > > don't know what to do to make it happen.
> >
> > Hi Britney,
> >
> > You can customize the table format in reporting services.
> > To have every third row divided by a solid line, the Borderstyle of
> > the textboxes inside the table have to be set based on the following
> > expression
> >
> > =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> >
> > where DataSet1 is the dataset name you are going to use..
> > Hope this helps you.
> > Cheers,
> > Prathima.C|||never mind, I figured it out.
your logic is correct, I specified it in default input of border style, it
draw all the four lines (top, bottom, left ,right) as solid lines, but I
really should just specify it on "bottom" input box of border style.
thanks for your help
"Prathima" <prathima.chandramouli@.gmail.com> wrote in message
news:f941b84.0411232053.66adc74d@.posting.google.com...
> Can u please define the problem more clearly so that i can help u. I'm
> not able to get your problem correctly.
>
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
news:<u8ELx2K0EHA.3976@.TK2MSFTNGP09.phx.gbl>...
> > YES, it worked partially,
> >
> > the table looked pretty ugly now.
> >
> > but I want the format with one line only. is that possible?
> >
> >
> >
> > ______________
> > 1 3 4 5
> > 1 4 5 5
> > 2 4 5 6
> > ______________
> > 1 3 4 5
> > 1 4 5 5
> > 2 4 5 6
> > ______________
> > .........
> > ..........
> >
> >
> >
> > "Prathima" <prathima.chandramouli@.gmail.com> wrote in message
> > news:f941b84.0411220300.5888d832@.posting.google.com...
> > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > > > I don't want every row divided by a solid line,
> > > > how can I customize the table format so every third row
> > > > is divided by a solid line? is that even possible in reporting
service?
> > I
> > > > don't know what to do to make it happen.
> > >
> > > Hi Britney,
> > >
> > > You can customize the table format in reporting services.
> > > To have every third row divided by a solid line, the Borderstyle of
> > > the textboxes inside the table have to be set based on the following
> > > expression
> > >
> > > =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> > >
> > > where DataSet1 is the dataset name you are going to use..
> > > Hope this helps you.
> > > Cheers,
> > > Prathima.C
Customize dimension attributes in SSAS
I have a time dimension table with an integer column "Quarter". In the SSAS Dimension I would like to display this as "Q 1" etc. instead of just "1".
I could easily add a column in the root table, but would prefer to format the column in the SSAS dimension. Can't find a "Format" property for an attribute. Is there a way to do this?
The most simple way to do this is to add a named calculation(a new column) in the data source view for the dimension table.
Add this attribute to the dimension or use is as the name column for the quarter attribute/column.
Add: 'Q' + ' ' + Cast(Quarter as Char(1))
HTH
Thomas Ivarsson
|||if u have choosen "typical" time dimension in analysis services, u would get "Quarter" in the display.Sunday, March 25, 2012
Customers & Phones
Hello,
I have a customers table linked to a phones table.
Is there a way to write an update query which will take the first & second
phone number of every customer and put it into respective fields (strPhone1
&
strPhone2) on the customer table, and dump any other phones into a memo fiel
d?
Thanks,
LomasWhy? There is so much wrong with this is is not funny. First normal form
violations in each of your columns, for starters, but beyond that:
Data put into memo fields (text columns, I assume) is pretty much useless in
your database from then on. All of the things you can do with it is
basically lost. Best case, you use a query to make a view of the data like
you are asking for and abstract it out from there. I know it takes more
work to do it right at first but it is worth it in the long run. Not to
mention that you can ask here for any query help to make the abstractions
work.
A moderately reasonable solution would be to have two columns <purpose>Phone
and <otherPurpose>Phone in your customer table if they will always have the
same purpose and then the phone table to keep other phone types.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||Ok, let me explain a little bit better.
I am writing this sql code as part of a modification to a database. After
this update query comes the drop tblPhones statement. This sql statement, i
n
its final form, will handle the range of phone types.
It seems to me that allowing a user an unlimited number of phone numbers
will have little practical value, and will be more difficult to query.
Modifying at this stage, while there are still few users, shouldn't cause
too much of an impact.
"Louis Davidson" wrote:
> Why? There is so much wrong with this is is not funny. First normal form
> violations in each of your columns, for starters, but beyond that:
> Data put into memo fields (text columns, I assume) is pretty much useless
in
> your database from then on. All of the things you can do with it is
> basically lost. Best case, you use a query to make a view of the data lik
e
> you are asking for and abstract it out from there. I know it takes more
> work to do it right at first but it is worth it in the long run. Not to
> mention that you can ask here for any query help to make the abstractions
> work.
> A moderately reasonable solution would be to have two columns <purpose>Pho
ne
> and <otherPurpose>Phone in your customer table if they will always have th
e
> same purpose and then the phone table to keep other phone types.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "optidev" <optidev@.community.nospam> wrote in message
> news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
>
>|||On a conceptual level, SSN, LastName and Ethnicity are attributes of the
Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
are related to a specific Customer at a given moment in time, but could
belong to an entirely different Customer tomorrow. Consider, relating them
using a CustomerPhone table with StartDate and EndDate columns to keep an
audit whenever their profile changes.
"optidev" <optidev@.community.nospam> wrote in message
news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
> Ok, let me explain a little bit better.
> I am writing this sql code as part of a modification to a database. After
> this update query comes the drop tblPhones statement. This sql statement,
> in
> its final form, will handle the range of phone types.
> It seems to me that allowing a user an unlimited number of phone numbers
> will have little practical value, and will be more difficult to query.
> Modifying at this stage, while there are still few users, shouldn't cause
> too much of an impact.
>
> "Louis Davidson" wrote:
>|||This is exactly what he wants to avoid, though it is still not a good idea
:)
To answer your original question, you will have to define what data makes a
phone number first or second. If you go ahead with this change, I would at
least suggest you have a column for home phone, one for mobile phone, etc.
rather than phone1, phone2. This is really bad design. On the other hand,
limiting the user to only one phone number of a specific type is a valid
design that doesn't leave the end user guessing what they are storing where.
Post your table structures and someone will help write the query..
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
> On a conceptual level, SSN, LastName and Ethnicity are attributes of the
> Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
> are related to a specific Customer at a given moment in time, but could
> belong to an entirely different Customer tomorrow. Consider, relating them
> using a CustomerPhone table with StartDate and EndDate columns to keep an
> audit whenever their profile changes.
> "optidev" <optidev@.community.nospam> wrote in message
> news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
>|||Hi ,
Write a update trigger for your main table and put a code like this , i cant
exactly remember right now
if updated(firstPhone) || updated(secondPhone)
......
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||I *was* planning to have have different columns for the different phone
types. I just didn't want to make the question too complicated. :)
Do you still think it is better to completely normilize the tables? In that
case I would need to keep track of the current primary address for use in
display and reporting. I don't see much practicality in knowing a customer'
s
previous addresses. Either way, I would also split the phones table if I wa
s
going to keep it this way.
Here are the tables with the added columns for phone types:
tblFamilies: --
pkFamilyID
strLastName
strAddress1
strAddress2
strCity
strZip
strHomePhone1
strHomePhone2
strHomeFax
strFamilyNotes
tblCustomers: --
pkCustomerID
fkFamilyID
strTitle
strFirstName
strMiddleInitial
strPostTitle
strWorkPhone1
strWorkPhone2
strWorkFax
strMobilePhone
strPager
strVoiceMail
strCustomerNotes
tblPhones: --
pkPhoneID
fkPhoneTypeID
fkFamilyID
fkCustomerID (one of these is null - either family or customer)
strPhoneNumber
strExtension (I also want to combine the extension with the phone number)
tblPhoneTypes: --
pkPhoneTypeID
numOrder
strDescription
The primary key currently defines what makes the phone number first or secon
d.
This code, for example, retrives all home phone numbers:
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblFamilies ON tblPhones.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
UNION
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblCustomers ON tblPhones.fkCustomerID =
tblCustomers.pkCustomerID INNER JOIN
tblFamilies ON tblCustomers.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
I can add this kind of complexity to the statement after I know the coding
method.
Thanks,
Lomas
"Louis Davidson" wrote:
> This is exactly what he wants to avoid, though it is still not a good idea
> :)
> To answer your original question, you will have to define what data makes
a
> phone number first or second. If you go ahead with this change, I would a
t
> least suggest you have a column for home phone, one for mobile phone, etc.
> rather than phone1, phone2. This is really bad design. On the other hand
,
> limiting the user to only one phone number of a specific type is a valid
> design that doesn't leave the end user guessing what they are storing wher
e.
> Post your table structures and someone will help write the query..
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||Interesting... Now I am thinking of normalizing the addresses and
denormalizing the phones... I still don't really see a point in keeping a
history of phone numbers.
What would be the best way to set up the query for retriveing the current
primary address in such a case?
As for the normalized phones table, I ran into a problem when a customer
asked me to export a list of them. He needed a column for each phone type
(Home1, Home2, HomeFax, etc.). How would I set up the query to put the firs
t
home number (it found) into the Home1 column, the second into Home2, etc.
What it there are (let's say) 15 of them? Also, how would I set up the quer
y
for reporting purposes. (I don't want every one to print when I print the
report.)
Thanks,
Lomas
"JT" wrote:
> Ask the users if they need a history of changes to a user's profile. If yo
u
> are shipping product to a customer's address, then knowing the address whe
re
> you actually shipped a product on a specific date would not only be useful
> but critical. Also, the marketing department may be interested in datamini
ng
> the changes in their customer's demographics. If the customers who generat
e
> the most sales are migrating to another geographical area, then that would
> be significant.
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||What is is the customer's most recent address?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID
order by
StartDate desc
What was the customer's address on the date that the product was shipped?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID and
StartDate <= @.ShipDate
order by
StartDate desc
If you have a normalized database and want to return a crosstab query
(Home1,Home2,Cell1,Cell2,etc) for reporting purposes, you can use inline
sub-queries like below. However, this will perform better and is more
flexible when implemented on the client side using Crystal, Excel or
whatever your reporting tool happens to be.
select
CustomerName,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='H' order by StartDate desc) as Home1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home3,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='C' order by StartDate desc) as Cell1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell3
from
Customers
where
CustomerID = @.CustomerID
"optidev" <optidev@.community.nospam> wrote in message
news:A0E6085D-D6EE-42FC-BCC9-524FFFDEAF5A@.microsoft.com...
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the
> first
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the
> query
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>|||select bottom doesn't work
"optidev" wrote:
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the fi
rst
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the qu
ery
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>
I have a customers table linked to a phones table.
Is there a way to write an update query which will take the first & second
phone number of every customer and put it into respective fields (strPhone1
&
strPhone2) on the customer table, and dump any other phones into a memo fiel
d?
Thanks,
LomasWhy? There is so much wrong with this is is not funny. First normal form
violations in each of your columns, for starters, but beyond that:
Data put into memo fields (text columns, I assume) is pretty much useless in
your database from then on. All of the things you can do with it is
basically lost. Best case, you use a query to make a view of the data like
you are asking for and abstract it out from there. I know it takes more
work to do it right at first but it is worth it in the long run. Not to
mention that you can ask here for any query help to make the abstractions
work.
A moderately reasonable solution would be to have two columns <purpose>Phone
and <otherPurpose>Phone in your customer table if they will always have the
same purpose and then the phone table to keep other phone types.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||Ok, let me explain a little bit better.
I am writing this sql code as part of a modification to a database. After
this update query comes the drop tblPhones statement. This sql statement, i
n
its final form, will handle the range of phone types.
It seems to me that allowing a user an unlimited number of phone numbers
will have little practical value, and will be more difficult to query.
Modifying at this stage, while there are still few users, shouldn't cause
too much of an impact.
"Louis Davidson" wrote:
> Why? There is so much wrong with this is is not funny. First normal form
> violations in each of your columns, for starters, but beyond that:
> Data put into memo fields (text columns, I assume) is pretty much useless
in
> your database from then on. All of the things you can do with it is
> basically lost. Best case, you use a query to make a view of the data lik
e
> you are asking for and abstract it out from there. I know it takes more
> work to do it right at first but it is worth it in the long run. Not to
> mention that you can ask here for any query help to make the abstractions
> work.
> A moderately reasonable solution would be to have two columns <purpose>Pho
ne
> and <otherPurpose>Phone in your customer table if they will always have th
e
> same purpose and then the phone table to keep other phone types.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "optidev" <optidev@.community.nospam> wrote in message
> news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
>
>|||On a conceptual level, SSN, LastName and Ethnicity are attributes of the
Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
are related to a specific Customer at a given moment in time, but could
belong to an entirely different Customer tomorrow. Consider, relating them
using a CustomerPhone table with StartDate and EndDate columns to keep an
audit whenever their profile changes.
"optidev" <optidev@.community.nospam> wrote in message
news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
> Ok, let me explain a little bit better.
> I am writing this sql code as part of a modification to a database. After
> this update query comes the drop tblPhones statement. This sql statement,
> in
> its final form, will handle the range of phone types.
> It seems to me that allowing a user an unlimited number of phone numbers
> will have little practical value, and will be more difficult to query.
> Modifying at this stage, while there are still few users, shouldn't cause
> too much of an impact.
>
> "Louis Davidson" wrote:
>|||This is exactly what he wants to avoid, though it is still not a good idea
:)
To answer your original question, you will have to define what data makes a
phone number first or second. If you go ahead with this change, I would at
least suggest you have a column for home phone, one for mobile phone, etc.
rather than phone1, phone2. This is really bad design. On the other hand,
limiting the user to only one phone number of a specific type is a valid
design that doesn't leave the end user guessing what they are storing where.
Post your table structures and someone will help write the query..
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
> On a conceptual level, SSN, LastName and Ethnicity are attributes of the
> Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
> are related to a specific Customer at a given moment in time, but could
> belong to an entirely different Customer tomorrow. Consider, relating them
> using a CustomerPhone table with StartDate and EndDate columns to keep an
> audit whenever their profile changes.
> "optidev" <optidev@.community.nospam> wrote in message
> news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
>|||Hi ,
Write a update trigger for your main table and put a code like this , i cant
exactly remember right now
if updated(firstPhone) || updated(secondPhone)
......
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||I *was* planning to have have different columns for the different phone
types. I just didn't want to make the question too complicated. :)
Do you still think it is better to completely normilize the tables? In that
case I would need to keep track of the current primary address for use in
display and reporting. I don't see much practicality in knowing a customer'
s
previous addresses. Either way, I would also split the phones table if I wa
s
going to keep it this way.
Here are the tables with the added columns for phone types:
tblFamilies: --
pkFamilyID
strLastName
strAddress1
strAddress2
strCity
strZip
strHomePhone1
strHomePhone2
strHomeFax
strFamilyNotes
tblCustomers: --
pkCustomerID
fkFamilyID
strTitle
strFirstName
strMiddleInitial
strPostTitle
strWorkPhone1
strWorkPhone2
strWorkFax
strMobilePhone
strPager
strVoiceMail
strCustomerNotes
tblPhones: --
pkPhoneID
fkPhoneTypeID
fkFamilyID
fkCustomerID (one of these is null - either family or customer)
strPhoneNumber
strExtension (I also want to combine the extension with the phone number)
tblPhoneTypes: --
pkPhoneTypeID
numOrder
strDescription
The primary key currently defines what makes the phone number first or secon
d.
This code, for example, retrives all home phone numbers:
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblFamilies ON tblPhones.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
UNION
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblCustomers ON tblPhones.fkCustomerID =
tblCustomers.pkCustomerID INNER JOIN
tblFamilies ON tblCustomers.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
I can add this kind of complexity to the statement after I know the coding
method.
Thanks,
Lomas
"Louis Davidson" wrote:
> This is exactly what he wants to avoid, though it is still not a good idea
> :)
> To answer your original question, you will have to define what data makes
a
> phone number first or second. If you go ahead with this change, I would a
t
> least suggest you have a column for home phone, one for mobile phone, etc.
> rather than phone1, phone2. This is really bad design. On the other hand
,
> limiting the user to only one phone number of a specific type is a valid
> design that doesn't leave the end user guessing what they are storing wher
e.
> Post your table structures and someone will help write the query..
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||Interesting... Now I am thinking of normalizing the addresses and
denormalizing the phones... I still don't really see a point in keeping a
history of phone numbers.
What would be the best way to set up the query for retriveing the current
primary address in such a case?
As for the normalized phones table, I ran into a problem when a customer
asked me to export a list of them. He needed a column for each phone type
(Home1, Home2, HomeFax, etc.). How would I set up the query to put the firs
t
home number (it found) into the Home1 column, the second into Home2, etc.
What it there are (let's say) 15 of them? Also, how would I set up the quer
y
for reporting purposes. (I don't want every one to print when I print the
report.)
Thanks,
Lomas
"JT" wrote:
> Ask the users if they need a history of changes to a user's profile. If yo
u
> are shipping product to a customer's address, then knowing the address whe
re
> you actually shipped a product on a specific date would not only be useful
> but critical. Also, the marketing department may be interested in datamini
ng
> the changes in their customer's demographics. If the customers who generat
e
> the most sales are migrating to another geographical area, then that would
> be significant.
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||What is is the customer's most recent address?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID
order by
StartDate desc
What was the customer's address on the date that the product was shipped?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID and
StartDate <= @.ShipDate
order by
StartDate desc
If you have a normalized database and want to return a crosstab query
(Home1,Home2,Cell1,Cell2,etc) for reporting purposes, you can use inline
sub-queries like below. However, this will perform better and is more
flexible when implemented on the client side using Crystal, Excel or
whatever your reporting tool happens to be.
select
CustomerName,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='H' order by StartDate desc) as Home1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home3,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='C' order by StartDate desc) as Cell1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell3
from
Customers
where
CustomerID = @.CustomerID
"optidev" <optidev@.community.nospam> wrote in message
news:A0E6085D-D6EE-42FC-BCC9-524FFFDEAF5A@.microsoft.com...
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the
> first
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the
> query
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>|||select bottom doesn't work
"optidev" wrote:
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the fi
rst
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the qu
ery
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>
Customer table and customer address table
I am trying to do a select query where I select the customer
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only onehi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'=
CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
bye
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only onehi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'=
CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
bye
Customer table and customer address table
I am trying to do a select query where I select the customer
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only one
hi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'=
CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
bye
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only one
hi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'=
CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
bye
Customer table and customer address table
I am trying to do a select query where I select the customer
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no = cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only onehi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'= CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
byesql
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no = cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only onehi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'= CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
byesql
customer table
I have a customer table with the column "invoice_customer" (this is the customer_acccount of account where the bill is invoiced..)
so to get the invoice_customer address for the customer account - 13301
SELECT B1.address as InvoiceAddress
from CUSTOMER AS B1 , CUSTOMER AS E1
WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'
i want to add the above InvoiceAddress to the query below:
select customer_account, order_no, date_req, del_address (InvoiceAddress)
from CUSTOMERS
INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account
where status 'D'
How would I put the 2 togtheer...
Thanks in advance!!!please always identify exactly which table each of your columns comes from
if you don't, you're making us guess, and do extra work trying to figure out what you're doingselect E1.customer_account
, O.order_no
, O.date_req
, B1.address as InvoiceAddress
from CUSTOMERS as B1
inner
join CUSTOMERS as E1
on E1.invoice_customer = B1.customer_account
and E1.customer_account = '13310'
INNER
JOIN Orders as O
on O.account = B1.customer_account
where B1.status = 'D'|||Im sorry I will try and be more careful next time..
Thanks for your help :)
so to get the invoice_customer address for the customer account - 13301
SELECT B1.address as InvoiceAddress
from CUSTOMER AS B1 , CUSTOMER AS E1
WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'
i want to add the above InvoiceAddress to the query below:
select customer_account, order_no, date_req, del_address (InvoiceAddress)
from CUSTOMERS
INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account
where status 'D'
How would I put the 2 togtheer...
Thanks in advance!!!please always identify exactly which table each of your columns comes from
if you don't, you're making us guess, and do extra work trying to figure out what you're doingselect E1.customer_account
, O.order_no
, O.date_req
, B1.address as InvoiceAddress
from CUSTOMERS as B1
inner
join CUSTOMERS as E1
on E1.invoice_customer = B1.customer_account
and E1.customer_account = '13310'
INNER
JOIN Orders as O
on O.account = B1.customer_account
where B1.status = 'D'|||Im sorry I will try and be more careful next time..
Thanks for your help :)
Customer Passwords in SQL Server
I currently have a table with user passwords
Ideally I want to encrypt these passwords...
I have read in places to use pwdencrypt and pwdcompare as this is a SQL
function. But I have also read the microsoft may update this Hash function,
and may lead to me losing all of my passwords...
Can anybody assist in the correct way to store passwords in a SQL table...
Kind RegardsWhat is your application programmed in? The asp.net security
whitepaper has a section on how to encrypt, salt and store information
securely in a SQL Server database. It's available at
http://www.microsoft.com/downloads/...ReleaseID=44047
--Mary
On Wed, 1 Sep 2004 01:21:05 -0700, "Jay_Reborn"
<JayReborn@.discussions.microsoft.com> wrote:
>I currently have a table with user passwords
>Ideally I want to encrypt these passwords...
>I have read in places to use pwdencrypt and pwdcompare as this is a SQL
>function. But I have also read the microsoft may update this Hash function,
>and may lead to me losing all of my passwords...
>Can anybody assist in the correct way to store passwords in a SQL table...
>Kind Regards
>|||If you are using ASP.NET, this article might be helpful to you:
rl]
erik perez
[url]www.solien.com" target="_blank">http://www.dotnetjunkies.com/Tutori...]www.solien.com
"Jay_Reborn" <JayReborn@.discussions.microsoft.com> wrote in message
news:D673C5FE-DB92-4E8B-A598-F843AEE40176@.microsoft.com...
> I currently have a table with user passwords
> Ideally I want to encrypt these passwords...
> I have read in places to use pwdencrypt and pwdcompare as this is a SQL
> function. But I have also read the microsoft may update this Hash
function,
> and may lead to me losing all of my passwords...
> Can anybody assist in the correct way to store passwords in a SQL table...
> Kind Regards
>sql
Ideally I want to encrypt these passwords...
I have read in places to use pwdencrypt and pwdcompare as this is a SQL
function. But I have also read the microsoft may update this Hash function,
and may lead to me losing all of my passwords...
Can anybody assist in the correct way to store passwords in a SQL table...
Kind RegardsWhat is your application programmed in? The asp.net security
whitepaper has a section on how to encrypt, salt and store information
securely in a SQL Server database. It's available at
http://www.microsoft.com/downloads/...ReleaseID=44047
--Mary
On Wed, 1 Sep 2004 01:21:05 -0700, "Jay_Reborn"
<JayReborn@.discussions.microsoft.com> wrote:
>I currently have a table with user passwords
>Ideally I want to encrypt these passwords...
>I have read in places to use pwdencrypt and pwdcompare as this is a SQL
>function. But I have also read the microsoft may update this Hash function,
>and may lead to me losing all of my passwords...
>Can anybody assist in the correct way to store passwords in a SQL table...
>Kind Regards
>|||If you are using ASP.NET, this article might be helpful to you:
rl]
erik perez
[url]www.solien.com" target="_blank">http://www.dotnetjunkies.com/Tutori...]www.solien.com
"Jay_Reborn" <JayReborn@.discussions.microsoft.com> wrote in message
news:D673C5FE-DB92-4E8B-A598-F843AEE40176@.microsoft.com...
> I currently have a table with user passwords
> Ideally I want to encrypt these passwords...
> I have read in places to use pwdencrypt and pwdcompare as this is a SQL
> function. But I have also read the microsoft may update this Hash
function,
> and may lead to me losing all of my passwords...
> Can anybody assist in the correct way to store passwords in a SQL table...
> Kind Regards
>sql
Customer Count
Hello,
I have a Fact table containing sales information, and is linked to a customer dimension through a CustomerKey and to a product dimension. The Customer Dimension is a slowly changing dimension having CustomerKey as primary key and CustomerPIN as BusinessKey. A customer can have historical records in the Customer Dimension.
I want to count the number of clients per product. If I create a measure Customer Count as Distinct Count on the CustomerKey, my results are not quite correct because 2 different Customer Keys might belong to the same Customer since the latter is an SCD. So what I want is actually Distinct Count on CustomerPIN, but I am not able to create this measure correctly. It is being created as a measure group under Customer but is not linked to the sales.
I am not sure if I'm doing the right way or if I'm missing something.
Appreciate your help,
Grace
Hi Grace,
One possibility is to use a Named Query for the fact table, where the original fact table is joined to the Customer table, so as to add a Customer PIN field on which a distinct count measure can be created.
|||Thanks Deepak,
I was able to create a Named Query having SalesKey as Logical Key with Person PIN according to the Person Key of each Sales transaction. And I added a relation between SalesKey from the New Named Query and the Fact table. Then, I was able to create a measure as Distinct Count on Person PIN and use it correctly.
However I was wondering if maybe since I can modify my underlying fact table to originally include this Customer PIN in addition to the Customer Key, and fill both from the SSIS, wouldn't that be faster and less complex? What do you suggest?
Thanks again,
Grace
|||Grace,
My own inclination would be avoid loading redundant data, unless the cube processing performance with the Named Query is a problem. But I can't say that I've dealt frequently with this scenario, so maybe others on this Forum could comment as well?
custom(and dynamic) record fields query
Hello,
I need to create a query that depends on custom fields (virtual).
First I'll try to outline the background.
I got Nodes table, that has NodeID field, and other (not really important)
fields like name, some boolean fields and so on.
Lets define node as an item for sale (just for the example purposes).
User of the application can create custom attribute values for each each
item (node).
To make the attribute names consistant through application I got another
table Attributes (AttID, AttributeName)
And the last table is association table between Node and Attributes
containing the value of the attribute as well.
I'd like to get following resultset:
NodeID, NodeName, CustomField1, CustomField2, CustomField3, ... and so on
(names of the fields would depend on Attribute name)
with values:
1,FirstNode, 'one of the values', 'another value', NULL, ...
2,SecondNode, NULL, NULL, NULL , ...
3,ThirdNode, NULL, NULL, 'some value', ...
SQL for creation such tables would look like (I ommited constrains and
indexes for siplicity):
CREATE TABLE Nodes (
NodeID INT IDENTITY (1, 1) NOT NULL,
NodeName NVARCHAR(255) NULL
)
CREATE TABLE Attributes (
AttID INT IDENTITY (1, 1) NOT NULL,
AttributeName NVARCHAR(50) NOT NULL
)
CREATE TABLE NodeAttr (
NodeID INT NOT NULL,
AttID INT NOT NULL,
Value NVARCHAR(255)
)
some data insertion:
INSERT INTO Nodes(NodeName) VALUES ('FirstNode')
INSERT INTO Nodes(NodeName) VALUES ('SecondNode')
INSERT INTO Nodes(NodeName) VALUES ('ThirdNode')
INSERT INTO Nodes(NodeName) VALUES ('FourthNode')
INSERT INTO Nodes(NodeName) VALUES ('FifthNode')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField1')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField2')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,1,'My Value for Field1')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,2,'My Value for Field2')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,3,'My Value for Field3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (3,3,'My Value for Field3
in third node')
Second I describe my approach to the problem.
I created function that retrieves data based on nodeID and Attribute name:
CREATE FUNCTION myfun(@.NodeID INT, @.AttributeName NVARCHAR(50))
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @.Value NVARCHAR(255)
SET @.Value=NULL
SELECT @.Value=Value FROM NodeAttr NA
INNER JOIN Attributes A ON (NA.AttID=A.AttID)
WHERE NodeID=@.NodeID AND AttributeName=@.AttributeName
RETURN @.Value
END
Next, I create dynamic SQL for data retrieval:
declare @.query NVARCHAR(4000)
SELECT
@.query=isnull(@.query+',','SELECT NodeID,
')+'dbo.myfun(NodeID,'''+AttributeName+''') AS ['+AttributeName+']'
FROM Attributes
SET @.query = @.query + ' FROM Nodes'
and execute it:
EXECUTE sp_executesql @.query
Dynamicly generated SQL looks like this:
SELECT NodeID,
dbo.myfun(NodeID,'CustomField1') AS [CustomField1],
dbo.myfun(NodeID,'CustomField2') AS [CustomField2],
dbo.myfun(NodeID,'CustomField3') AS [CustomField3]
FROM Nodes
It is great when you deal with small number of nodes. When node number is
about 2000 the perfomarnce is really bad (executes over a minute). And I'd
like to have even more.
Anyone can point me to better solution? :)
regards,
Pawel Rogozinski
Software Engineer at Eracent Inc.
pawel.rogozinski@.eracent.com>> I need to create a query that depends on custom fields (virtual). <<
This flawed approach is called an EAV design and newbies re-invent it
about once a month here. The basic fallacy that leads to meltdown in
about a year is that you are mixing data and metadata. Also, columns
are not fields and rows are not records -- totally different concepts.
Then give the users a copy of SQL and get out of the way. If any
random user is a better database programmers and designer than you are,
you should not be programming.
It is not just exponently slow; since it has no data integrity, it is
error-prone, too!!|||>> I'd like to get following resultset:
It is better to use a client programming language or a report writer to
generate such cross tabulations. In t-SQL, most general approaches are
clumsy. For some methods refer to: http://tinyurl.com/ayjwa
Anith|||This sounds like an EAV (Entity-Attribute-Value) design. One of it's biggest
failings is that reporting is significantly more difficult and slow. Another
di
vanatage is that it requires dynamic SQL and all of its issues. Thus, y
ou
should be aware that the difficulties you are having in finding a solution a
re
directly related to the design.
That said and assuming you wanted to continue with this design, one solution
that takes the dynamic SQL out of the equation would be to make the equivale
nt
of a materialized view. In essence, create a permanent temp table where each
"attribute" in your design is a column in this temp table. You would then
populate and maintain this table with every entity's attribute values. To do
that you can use a massive case statement like so:
Select NA.NodeId
, Min(Case When NA.AttId = 1 Then NA.Value End) As Attribute1Name
, Min(Case When NA.AttId = 2 Then NA.Value End) As Attribute2Name
...
From NodeAttr As NA
Group By NA.NodeId
In order to maitain the contents of the table, you'll need to use triggers t
o
update the entity's appropriate attribute values in the temp table and so on
.
The solutions to the problems created by this type of design get uglier and
uglier and the performance gets worse and worse.
Thomas|||Celko,
thanks for your "constructive" comments.
> This flawed approach is called an EAV design
> and newbies re-invent it about once a month here.
Nice. I did not reinvent this, I'm just working on such flawed design,
as you called it.
Do you know better design, that could substitute EAV then?
[...cut...]
> It is not just exponently slow; since it has no data
> integrity, it is error-prone, too!!
Didn't I mention that I did not put integrity constraitns for simplicity
of the example? I believie I did.
I would be very gratefull if you could lead me to a better solution and
not criticizing my approach, as I am aware of the flaws that come with
my approach (and the design as well).
*** Sent via Developersdex http://www.examnotes.net ***|||I like the temporary table solution.
As the attributes are not added/deleted frequently it could be one of the
best approaches.
regards,
Pawel Rogozinski
Software Engineer at Eracent
pawel.rogozinski@.eracent.com|||>> Do you know better design, that could substitute EAV then? <<
The point everyone is trying to get across to you is that you need a
totally different approach; it is not a matter of finding a substutute.
Why not use an actual data model, based on the specifications from the
user. There is no "magical, one size fits all" answer that you s
with kludges like EAV. Each database is a thing in itself, but you can
look for patterns for reuse.
simplicity of the example? I believe I did. <<
Did I meniton that doing that is virtually impossible without a few
hundred lines of procedural code in triggers and procs if you have a
database of any size?
Let's say I have 50 attributes of type INTEGER in my EAV model. They
are crammed into one table. Each one has 1 or 2 CHECK() constraints
that are totally different. Now try to write the CASE expression with
50 WHEN clauses to validlate each one.
Let's say that I have a primary key among those 50 attributes. Can you
show me the uniquenss constraint to enforce that business rule?
Let's say that I have a a foreign key among those 50 attributes. Can
you show me the uniquness constraint to enforce that business rule?
And do the DRI Actions required to maintain this relationship?
Thomas Coleman had a recent posting where he worked out the equivalent
of a simple GROUP BY statement for an EAV. Google it.sql
I need to create a query that depends on custom fields (virtual).
First I'll try to outline the background.
I got Nodes table, that has NodeID field, and other (not really important)
fields like name, some boolean fields and so on.
Lets define node as an item for sale (just for the example purposes).
User of the application can create custom attribute values for each each
item (node).
To make the attribute names consistant through application I got another
table Attributes (AttID, AttributeName)
And the last table is association table between Node and Attributes
containing the value of the attribute as well.
I'd like to get following resultset:
NodeID, NodeName, CustomField1, CustomField2, CustomField3, ... and so on
(names of the fields would depend on Attribute name)
with values:
1,FirstNode, 'one of the values', 'another value', NULL, ...
2,SecondNode, NULL, NULL, NULL , ...
3,ThirdNode, NULL, NULL, 'some value', ...
SQL for creation such tables would look like (I ommited constrains and
indexes for siplicity):
CREATE TABLE Nodes (
NodeID INT IDENTITY (1, 1) NOT NULL,
NodeName NVARCHAR(255) NULL
)
CREATE TABLE Attributes (
AttID INT IDENTITY (1, 1) NOT NULL,
AttributeName NVARCHAR(50) NOT NULL
)
CREATE TABLE NodeAttr (
NodeID INT NOT NULL,
AttID INT NOT NULL,
Value NVARCHAR(255)
)
some data insertion:
INSERT INTO Nodes(NodeName) VALUES ('FirstNode')
INSERT INTO Nodes(NodeName) VALUES ('SecondNode')
INSERT INTO Nodes(NodeName) VALUES ('ThirdNode')
INSERT INTO Nodes(NodeName) VALUES ('FourthNode')
INSERT INTO Nodes(NodeName) VALUES ('FifthNode')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField1')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField2')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,1,'My Value for Field1')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,2,'My Value for Field2')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,3,'My Value for Field3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (3,3,'My Value for Field3
in third node')
Second I describe my approach to the problem.
I created function that retrieves data based on nodeID and Attribute name:
CREATE FUNCTION myfun(@.NodeID INT, @.AttributeName NVARCHAR(50))
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @.Value NVARCHAR(255)
SET @.Value=NULL
SELECT @.Value=Value FROM NodeAttr NA
INNER JOIN Attributes A ON (NA.AttID=A.AttID)
WHERE NodeID=@.NodeID AND AttributeName=@.AttributeName
RETURN @.Value
END
Next, I create dynamic SQL for data retrieval:
declare @.query NVARCHAR(4000)
SELECT
@.query=isnull(@.query+',','SELECT NodeID,
')+'dbo.myfun(NodeID,'''+AttributeName+''') AS ['+AttributeName+']'
FROM Attributes
SET @.query = @.query + ' FROM Nodes'
and execute it:
EXECUTE sp_executesql @.query
Dynamicly generated SQL looks like this:
SELECT NodeID,
dbo.myfun(NodeID,'CustomField1') AS [CustomField1],
dbo.myfun(NodeID,'CustomField2') AS [CustomField2],
dbo.myfun(NodeID,'CustomField3') AS [CustomField3]
FROM Nodes
It is great when you deal with small number of nodes. When node number is
about 2000 the perfomarnce is really bad (executes over a minute). And I'd
like to have even more.
Anyone can point me to better solution? :)
regards,
Pawel Rogozinski
Software Engineer at Eracent Inc.
pawel.rogozinski@.eracent.com>> I need to create a query that depends on custom fields (virtual). <<
This flawed approach is called an EAV design and newbies re-invent it
about once a month here. The basic fallacy that leads to meltdown in
about a year is that you are mixing data and metadata. Also, columns
are not fields and rows are not records -- totally different concepts.
Then give the users a copy of SQL and get out of the way. If any
random user is a better database programmers and designer than you are,
you should not be programming.
It is not just exponently slow; since it has no data integrity, it is
error-prone, too!!|||>> I'd like to get following resultset:
It is better to use a client programming language or a report writer to
generate such cross tabulations. In t-SQL, most general approaches are
clumsy. For some methods refer to: http://tinyurl.com/ayjwa
Anith|||This sounds like an EAV (Entity-Attribute-Value) design. One of it's biggest
failings is that reporting is significantly more difficult and slow. Another
di
vanatage is that it requires dynamic SQL and all of its issues. Thus, you
should be aware that the difficulties you are having in finding a solution a
re
directly related to the design.
That said and assuming you wanted to continue with this design, one solution
that takes the dynamic SQL out of the equation would be to make the equivale
nt
of a materialized view. In essence, create a permanent temp table where each
"attribute" in your design is a column in this temp table. You would then
populate and maintain this table with every entity's attribute values. To do
that you can use a massive case statement like so:
Select NA.NodeId
, Min(Case When NA.AttId = 1 Then NA.Value End) As Attribute1Name
, Min(Case When NA.AttId = 2 Then NA.Value End) As Attribute2Name
...
From NodeAttr As NA
Group By NA.NodeId
In order to maitain the contents of the table, you'll need to use triggers t
o
update the entity's appropriate attribute values in the temp table and so on
.
The solutions to the problems created by this type of design get uglier and
uglier and the performance gets worse and worse.
Thomas|||Celko,
thanks for your "constructive" comments.
> This flawed approach is called an EAV design
> and newbies re-invent it about once a month here.
Nice. I did not reinvent this, I'm just working on such flawed design,
as you called it.
Do you know better design, that could substitute EAV then?
[...cut...]
> It is not just exponently slow; since it has no data
> integrity, it is error-prone, too!!
Didn't I mention that I did not put integrity constraitns for simplicity
of the example? I believie I did.
I would be very gratefull if you could lead me to a better solution and
not criticizing my approach, as I am aware of the flaws that come with
my approach (and the design as well).
*** Sent via Developersdex http://www.examnotes.net ***|||I like the temporary table solution.
As the attributes are not added/deleted frequently it could be one of the
best approaches.
regards,
Pawel Rogozinski
Software Engineer at Eracent
pawel.rogozinski@.eracent.com|||>> Do you know better design, that could substitute EAV then? <<
The point everyone is trying to get across to you is that you need a
totally different approach; it is not a matter of finding a substutute.
Why not use an actual data model, based on the specifications from the
user. There is no "magical, one size fits all" answer that you s
with kludges like EAV. Each database is a thing in itself, but you can
look for patterns for reuse.
simplicity of the example? I believe I did. <<
Did I meniton that doing that is virtually impossible without a few
hundred lines of procedural code in triggers and procs if you have a
database of any size?
Let's say I have 50 attributes of type INTEGER in my EAV model. They
are crammed into one table. Each one has 1 or 2 CHECK() constraints
that are totally different. Now try to write the CASE expression with
50 WHEN clauses to validlate each one.
Let's say that I have a primary key among those 50 attributes. Can you
show me the uniquenss constraint to enforce that business rule?
Let's say that I have a a foreign key among those 50 attributes. Can
you show me the uniquness constraint to enforce that business rule?
And do the DRI Actions required to maintain this relationship?
Thomas Coleman had a recent posting where he worked out the equivalent
of a simple GROUP BY statement for an EAV. Google it.sql
Thursday, March 22, 2012
Custom xml format with FOR XML EXPLICIT
I'm working on Sql Server 2005 xml capabilities, but I'm not able to obtain what I want.
Let's say I have a table with 10 fields: Field1, Field2, ..., Field10.
I would, with a "FOR XML" clause, obtain an xml document like this one:
<MyTable>
<Field1>F1value</Field1>
<Field2 Field3="F3value">F2value</Field2>
</MyTable>
<MyTable>
...
</MyTable>
I think correct way is with the "EXPLICIT" mode, but I'm not able to find the syntax to make Field3 become an attribute of the element generated from Field2.
Does someone knows if it's possible and, if it is, how?
Any help will be truly appreciated.
Easier to do this
select Field1 as "Field1",
Field3 as "Field2/@.Field3",
Field2 as "Field2"
from MyTable
for xml path('MyTable')
This is exactly what I was looking for.sql
Custom Weeks
In CODE section below, I'm grouping the Sum of Freight by the w/year. We
have a custom year begin and end year table that the DDL section will
create. Thus, I use the t_w table to calculate our custom ws with w
1 of any year starting with the date in the w_beginyeardate field and the
last w of the year ending on the w_endyeardate date value.
Is there a way I could return the same results created by my CODE section in
Northwind except use custom ws derived from my t_w table? Also, I also
need a fix to properly sort the w/year field from start to end w/year
field.
CODE:
SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
sumFreight
FROM Orders
GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4))
ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4))
DDL ****************************************
*****
USE [Northwind]
GO
CREATE TABLE [t_w] (
[w_id] [int] IDENTITY(1,1) NOT NULL,
[w_beginyeardate] [datetime] NULL,
[w_endyeardate] [datetime] NULL,
[w_year] [int] NULL
) ON [PRIMARY]
GO
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19951226', '19961230', '1996')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19961231', '19971229', '1997')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19971230', '19981228', '1998')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19981229', '19981227', '1999')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19991228', '20001225', '2000')A sub-query, like
SELECT CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w WHERE
w_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))
would get you the custom w number for a given date, but you can't use
sub-queries in a GROUP BY clause. The solution here is to create a function.
The function below formats OrderDate into yyyy-ww
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
RETURN @.output
END
You can then use the function to both group and order by
SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomWFormat(OrderDate)
ORDER BY dbo.udfCustomWFormat(OrderDate)
"Scott" wrote:
> In CODE section below, I'm grouping the Sum of Freight by the w/year. W
e
> have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with we
ek
> 1 of any year starting with the date in the w_beginyeardate field and t
he
> last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
in
> Northwind except use custom ws derived from my t_w table? Also, I al
so
> need a fix to properly sort the w/year field from start to end w/yea
r
> field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19991228', '20001225', '2000')
>
>
>|||You almost have it. Please run my DDL below (I simulated my production db).
The only thing wrong is for example at the end of 2004, your w 53 2004
should be w 1 2005. If you run my example below, you'll see no w 1
2005. otherwise, the wly sums are correct. 2004 should only have 52
ws.
I think this is happening because you aren't taking the year ending dates
into consideration.
DDL *********************************
CREATE TABLE [dbo].[test_prod](
[prod_id] [int] IDENTITY(1,1) NOT NULL,
[prod_date] [datetime] NULL,
[log_count] [int] NULL
) ON [PRIMARY]
CREATE TABLE [t_w] (
[w_id] [int] IDENTITY(1,1) NOT NULL,
[w_beginyeardate] [datetime] NULL,
[w_endyeardate] [datetime] NULL,
[w_year] [int] NULL
) ON [PRIMARY]
GO
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19951226', '19961230', '1996')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19961231', '19971229', '1997')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19971230', '19981228', '1998')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19981229', '19981227', '1999')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19991228', '20001225', '2000')
CREATE FUNCTION udfWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
RETURN @.output
END
insert into test_prod (prod_date, log_count) values
('20041220', '6456')
insert into test_prod (prod_date, log_count) values
('20041221', '5830')
insert into test_prod (prod_date, log_count) values
('20041222', '3480')
insert into test_prod (prod_date, log_count) values
('20041227', '6542')
insert into test_prod (prod_date, log_count) values
('20041228', '6669')
insert into test_prod (prod_date, log_count) values
('20041229', '6524')
insert into test_prod (prod_date, log_count) values
('20041230', '6541')
insert into test_prod (prod_date, log_count) values
('20050104', '6370')
insert into test_prod (prod_date, log_count) values
('20050105', '6926')
insert into test_prod (prod_date, log_count) values
('20050106', '5002')
insert into test_prod (prod_date, log_count) values
('20050107', '6736')
insert into test_prod (prod_date, log_count) values
('20050108', '5822')
-- execute to see results
SELECT dbo.udfWFormat(prod_date), SUM(log_count) AS SumOflog_count
FROM dbo.test_prod
GROUP BY dbo.udfWFormat(prod_date)
ORDER BY dbo.udfWFormat(prod_date)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>A sub-query, like
> SELECT CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w WHERE
> w_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))
> would get you the custom w number for a given date, but you can't use
> sub-queries in a GROUP BY clause. The solution here is to create a
> function.
> The function below formats OrderDate into yyyy-ww
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
> WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
> RETURN @.output
> END
> You can then use the function to both group and order by
> SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomWFormat(OrderDate)
> ORDER BY dbo.udfCustomWFormat(OrderDate)
> "Scott" wrote:
>|||Yep, I realized my mistake about 15 minutes after my post, but couldn't get
back to it until after a few hours. This should do it:
DROP FUNCTION udfCustomWFormat
GO
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w_year AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, w_beginyeardate, @.date) / 7 + 1 AS
varchar(3)), 2)
FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
RETURN @.output
END
SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomWFormat(OrderDate)
ORDER BY dbo.udfCustomWFormat(OrderDate)
The function should now pick up the dates in late December correctly. For
example,
select dbo.udfCustomWFormat('19951228') results in 1996-01
Your example t_w table only goes to 2000, but you are running it against
a table that has dates in 2005; is the table cyclic?
"Scott" wrote:
> You almost have it. Please run my DDL below (I simulated my production db)
.
> The only thing wrong is for example at the end of 2004, your w 53 2004
> should be w 1 2005. If you run my example below, you'll see no w 1
> 2005. otherwise, the wly sums are correct. 2004 should only have 52
> ws.
> I think this is happening because you aren't taking the year ending dates
> into consideration.
> DDL *********************************
> CREATE TABLE [dbo].[test_prod](
> [prod_id] [int] IDENTITY(1,1) NOT NULL,
> [prod_date] [datetime] NULL,
> [log_count] [int] NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19991228', '20001225', '2000')
> CREATE FUNCTION udfWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
> WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
> RETURN @.output
> END
> insert into test_prod (prod_date, log_count) values
> ('20041220', '6456')
> insert into test_prod (prod_date, log_count) values
> ('20041221', '5830')
> insert into test_prod (prod_date, log_count) values
> ('20041222', '3480')
> insert into test_prod (prod_date, log_count) values
> ('20041227', '6542')
> insert into test_prod (prod_date, log_count) values
> ('20041228', '6669')
> insert into test_prod (prod_date, log_count) values
> ('20041229', '6524')
> insert into test_prod (prod_date, log_count) values
> ('20041230', '6541')
> insert into test_prod (prod_date, log_count) values
> ('20050104', '6370')
> insert into test_prod (prod_date, log_count) values
> ('20050105', '6926')
> insert into test_prod (prod_date, log_count) values
> ('20050106', '5002')
> insert into test_prod (prod_date, log_count) values
> ('20050107', '6736')
> insert into test_prod (prod_date, log_count) values
> ('20050108', '5822')
>
> -- execute to see results
> SELECT dbo.udfWFormat(prod_date), SUM(log_count) AS SumOflog_count
> FROM dbo.test_prod
> GROUP BY dbo.udfWFormat(prod_date)
> ORDER BY dbo.udfWFormat(prod_date)
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>
>|||First, thanks big for solving this head-hurter. My t_w table does have
the last 5 years plus always 1 year in advance. I had the old years so it'd
work in northwind.
what did you mean by the table being "cyclic"?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
> Yep, I realized my mistake about 15 minutes after my post, but couldn't
> get
> back to it until after a few hours. This should do it:
> DROP FUNCTION udfCustomWFormat
> GO
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w_year AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, w_beginyeardate, @.date) / 7 + 1 AS
> varchar(3)), 2)
> FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
> RETURN @.output
> END
> SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomWFormat(OrderDate)
> ORDER BY dbo.udfCustomWFormat(OrderDate)
> The function should now pick up the dates in late December correctly. For
> example,
> select dbo.udfCustomWFormat('19951228') results in 1996-01
> Your example t_w table only goes to 2000, but you are running it
> against
> a table that has dates in 2005; is the table cyclic?
> "Scott" wrote:
>|||Just curious - what kind of ws start on TUES?
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w/year.
> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with
> w 1 of any year starting with the date in the w_beginyeardate field
> and the last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom ws derived from my t_w table? Also, I
> also need a fix to properly sort the w/year field from start to end
> w/year field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19991228', '20001225', '2000')
>
>
>|||That the start and end year dates repeat in a pattern, every 5 years or so.
I
got from your second post that you were adjusting your data to fit Northwind
so a solution could be tested (and that's appreciated!)
I noticed when I ran the query against Northwind, one of the years (1996 I
think) shows up as having 53 ws in it. The difference between the year
start date and end date for 1996 in your sample is 370 days, so that does go
beying 52 ws. However, in a "normal" year, my math shows the last day of
the year being in w 53. It needs some twing. If I have time, I'll post
an adjusted version later.
"scott" wrote:
> First, thanks big for solving this head-hurter. My t_w table does have
> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Should have been this way from the start.
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w_year AS char(4)) + '-'
+ RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w_beginyeardate, @.date) / 7.00)
AS
varchar(3)), 2)
FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
RETURN @.output
END
Now the last (364th) day of the year shows up as being in w 52. For years
that go beyond 364 days, you will still end up with a w 53 out of this
function.
"scott" wrote:
> First, thanks big for solving this head-hurter. My t_w table does have
> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Are these calendar ws starting with Sunday?
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w/year.
> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with
> w 1 of any year starting with the date in the w_beginyeardate field
> and the last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom ws derived from my t_w table? Also, I
> also need a fix to properly sort the w/year field from start to end
> w/year field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19991228', '20001225', '2000')
>
>
>|||what's the difference in this 2nd correct function and the 1st or previous
correct function?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D4583358-03D7-4EAD-9B4C-F1E69B884861@.microsoft.com...
> Should have been this way from the start.
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w_year AS char(4)) + '-'
> + RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w_beginyeardate, @.date) / 7.00)
> AS
> varchar(3)), 2)
> FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
> RETURN @.output
> END
> Now the last (364th) day of the year shows up as being in w 52. For
> years
> that go beyond 364 days, you will still end up with a w 53 out of this
> function.
> "scott" wrote:
>
/year. Wehave a custom year begin and end year table that the DDL section will
create. Thus, I use the t_w
table to calculate our custom ws with w1 of any year starting with the date in the w
_beginyeardate field and thelast w
of the year ending on the w_endyeardate date value.Is there a way I could return the same results created by my CODE section in
Northwind except use custom w
s derived from my t_w table? Also, I alsoneed a fix to properly sort the w
/year field from start to end w/yearfield.
CODE:
SELECT CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS w
num, SUM(Freight) ASsumFreight
FROM Orders
GROUP BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +CAST(DATEPART(yyyy, OrderDate) AS char(4))
ORDER BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +CAST(DATEPART(yyyy, OrderDate) AS char(4))
DDL ****************************************
*****
USE [Northwind]
GO
CREATE TABLE [t_w
] ([w
_id] [int] IDENTITY(1,1) NOT NULL,[w
_beginyeardate] [datetime] NULL,[w
_endyeardate] [datetime] NULL,[w
_year] [int] NULL) ON [PRIMARY]
GO
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19951226', '19961230', '1996')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19961231', '19971229', '1997')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19971230', '19981228', '1998')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19981229', '19981227', '1999')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19991228', '20001225', '2000')A sub-query, like
SELECT CAST(DATEDIFF(dd, (SELECT w
_beginyeardate FROM t_w WHEREw
_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))would get you the custom w
number for a given date, but you can't usesub-queries in a GROUP BY clause. The solution here is to create a function.
The function below formats OrderDate into yyyy-ww
CREATE FUNCTION udfCustomW
Format (@.date datetime)RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w
_beginyeardate FROM t_wWHERE w
_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)RETURN @.output
END
You can then use the function to both group and order by
SELECT dbo.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreightFROM Orders
GROUP BY dbo.udfCustomW
Format(OrderDate)ORDER BY dbo.udfCustomW
Format(OrderDate)"Scott" wrote:
> In CODE section below, I'm grouping the Sum of Freight by the w
/year. We
> have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w
table to calculate our custom ws with week
> 1 of any year starting with the date in the w
_beginyeardate field and the
> last w
of the year ending on the w_endyeardate date value.> Is there a way I could return the same results created by my CODE section
in
> Northwind except use custom w
s derived from my t_w table? Also, I also
> need a fix to properly sort the w
/year field from start to end w/year
> field.
>
> CODE:
> SELECT CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS w
num, SUM(Freight) AS> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w
] (> [w
_id] [int] IDENTITY(1,1) NOT NULL,> [w
_beginyeardate] [datetime] NULL,> [w
_endyeardate] [datetime] NULL,> [w
_year] [int] NULL> ) ON [PRIMARY]
> GO
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19951226', '19961230', '1996')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19961231', '19971229', '1997')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19971230', '19981228', '1998')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19981229', '19981227', '1999')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19991228', '20001225', '2000')
>
>
>|||You almost have it. Please run my DDL below (I simulated my production db).
The only thing wrong is for example at the end of 2004, your w
53 2004should be w
1 2005. If you run my example below, you'll see no w 12005. otherwise, the w
ly sums are correct. 2004 should only have 52w
s.I think this is happening because you aren't taking the year ending dates
into consideration.
DDL *********************************
CREATE TABLE [dbo].[test_prod](
[prod_id] [int] IDENTITY(1,1) NOT NULL,
[prod_date] [datetime] NULL,
[log_count] [int] NULL
) ON [PRIMARY]
CREATE TABLE [t_w
] ([w
_id] [int] IDENTITY(1,1) NOT NULL,[w
_beginyeardate] [datetime] NULL,[w
_endyeardate] [datetime] NULL,[w
_year] [int] NULL) ON [PRIMARY]
GO
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19951226', '19961230', '1996')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19961231', '19971229', '1997')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19971230', '19981228', '1998')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19981229', '19981227', '1999')
insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values('19991228', '20001225', '2000')
CREATE FUNCTION udfW
Format (@.date datetime)RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w
_beginyeardate FROM t_wWHERE w
_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)RETURN @.output
END
insert into test_prod (prod_date, log_count) values
('20041220', '6456')
insert into test_prod (prod_date, log_count) values
('20041221', '5830')
insert into test_prod (prod_date, log_count) values
('20041222', '3480')
insert into test_prod (prod_date, log_count) values
('20041227', '6542')
insert into test_prod (prod_date, log_count) values
('20041228', '6669')
insert into test_prod (prod_date, log_count) values
('20041229', '6524')
insert into test_prod (prod_date, log_count) values
('20041230', '6541')
insert into test_prod (prod_date, log_count) values
('20050104', '6370')
insert into test_prod (prod_date, log_count) values
('20050105', '6926')
insert into test_prod (prod_date, log_count) values
('20050106', '5002')
insert into test_prod (prod_date, log_count) values
('20050107', '6736')
insert into test_prod (prod_date, log_count) values
('20050108', '5822')
-- execute to see results
SELECT dbo.udfW
Format(prod_date), SUM(log_count) AS SumOflog_countFROM dbo.test_prod
GROUP BY dbo.udfW
Format(prod_date)ORDER BY dbo.udfW
Format(prod_date)"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>A sub-query, like
> SELECT CAST(DATEDIFF(dd, (SELECT w
_beginyeardate FROM t_w WHERE> w
_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))> would get you the custom w
number for a given date, but you can't use> sub-queries in a GROUP BY clause. The solution here is to create a
> function.
> The function below formats OrderDate into yyyy-ww
> CREATE FUNCTION udfCustomW
Format (@.date datetime)> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w
_beginyeardate FROM t_w> WHERE w
_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)> RETURN @.output
> END
> You can then use the function to both group and order by
> SELECT dbo.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreight> FROM Orders
> GROUP BY dbo.udfCustomW
Format(OrderDate)> ORDER BY dbo.udfCustomW
Format(OrderDate)> "Scott" wrote:
>|||Yep, I realized my mistake about 15 minutes after my post, but couldn't get
back to it until after a few hours. This should do it:
DROP FUNCTION udfCustomW
FormatGO
CREATE FUNCTION udfCustomW
Format (@.date datetime)RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w
_year AS char(4)) + '-'+ RIGHT('0' + CAST(DATEDIFF(dd, w
_beginyeardate, @.date) / 7 + 1 ASvarchar(3)), 2)
FROM t_w
WHERE @.date BETWEEN w_beginyeardate AND w_endyeardateRETURN @.output
END
SELECT dbo.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreightFROM Orders
GROUP BY dbo.udfCustomW
Format(OrderDate)ORDER BY dbo.udfCustomW
Format(OrderDate)The function should now pick up the dates in late December correctly. For
example,
select dbo.udfCustomW
Format('19951228') results in 1996-01Your example t_w
table only goes to 2000, but you are running it againsta table that has dates in 2005; is the table cyclic?
"Scott" wrote:
> You almost have it. Please run my DDL below (I simulated my production db)
.
> The only thing wrong is for example at the end of 2004, your w
53 2004> should be w
1 2005. If you run my example below, you'll see no w 1> 2005. otherwise, the w
ly sums are correct. 2004 should only have 52> w
s.> I think this is happening because you aren't taking the year ending dates
> into consideration.
> DDL *********************************
> CREATE TABLE [dbo].[test_prod](
> [prod_id] [int] IDENTITY(1,1) NOT NULL,
> [prod_date] [datetime] NULL,
> [log_count] [int] NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [t_w
] (> [w
_id] [int] IDENTITY(1,1) NOT NULL,> [w
_beginyeardate] [datetime] NULL,> [w
_endyeardate] [datetime] NULL,> [w
_year] [int] NULL> ) ON [PRIMARY]
> GO
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19951226', '19961230', '1996')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19961231', '19971229', '1997')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19971230', '19981228', '1998')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19981229', '19981227', '1999')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year) values
> ('19991228', '20001225', '2000')
> CREATE FUNCTION udfW
Format (@.date datetime)> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w
_beginyeardate FROM t_w> WHERE w
_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)> RETURN @.output
> END
> insert into test_prod (prod_date, log_count) values
> ('20041220', '6456')
> insert into test_prod (prod_date, log_count) values
> ('20041221', '5830')
> insert into test_prod (prod_date, log_count) values
> ('20041222', '3480')
> insert into test_prod (prod_date, log_count) values
> ('20041227', '6542')
> insert into test_prod (prod_date, log_count) values
> ('20041228', '6669')
> insert into test_prod (prod_date, log_count) values
> ('20041229', '6524')
> insert into test_prod (prod_date, log_count) values
> ('20041230', '6541')
> insert into test_prod (prod_date, log_count) values
> ('20050104', '6370')
> insert into test_prod (prod_date, log_count) values
> ('20050105', '6926')
> insert into test_prod (prod_date, log_count) values
> ('20050106', '5002')
> insert into test_prod (prod_date, log_count) values
> ('20050107', '6736')
> insert into test_prod (prod_date, log_count) values
> ('20050108', '5822')
>
> -- execute to see results
> SELECT dbo.udfW
Format(prod_date), SUM(log_count) AS SumOflog_count> FROM dbo.test_prod
> GROUP BY dbo.udfW
Format(prod_date)> ORDER BY dbo.udfW
Format(prod_date)>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>
>|||First, thanks big for solving this head-hurter. My t_w
table does havethe last 5 years plus always 1 year in advance. I had the old years so it'd
work in northwind.
what did you mean by the table being "cyclic"?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
> Yep, I realized my mistake about 15 minutes after my post, but couldn't
> get
> back to it until after a few hours. This should do it:
> DROP FUNCTION udfCustomW
Format> GO
> CREATE FUNCTION udfCustomW
Format (@.date datetime)> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w
_year AS char(4)) + '-'> + RIGHT('0' + CAST(DATEDIFF(dd, w
_beginyeardate, @.date) / 7 + 1 AS> varchar(3)), 2)
> FROM t_w
WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate> RETURN @.output
> END
> SELECT dbo.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreight> FROM Orders
> GROUP BY dbo.udfCustomW
Format(OrderDate)> ORDER BY dbo.udfCustomW
Format(OrderDate)> The function should now pick up the dates in late December correctly. For
> example,
> select dbo.udfCustomW
Format('19951228') results in 1996-01> Your example t_w
table only goes to 2000, but you are running it> against
> a table that has dates in 2005; is the table cyclic?
> "Scott" wrote:
>|||Just curious - what kind of w
s start on TUES?William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w
/year.> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w
table to calculate our custom ws with> w
1 of any year starting with the date in the w_beginyeardate field> and the last w
of the year ending on the w_endyeardate date value.> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom w
s derived from my t_w table? Also, I> also need a fix to properly sort the w
/year field from start to end> w
/year field.>
> CODE:
> SELECT CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS w
num, SUM(Freight) AS> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w
] (> [w
_id] [int] IDENTITY(1,1) NOT NULL,> [w
_beginyeardate] [datetime] NULL,> [w
_endyeardate] [datetime] NULL,> [w
_year] [int] NULL> ) ON [PRIMARY]
> GO
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19951226', '19961230', '1996')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19961231', '19971229', '1997')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19971230', '19981228', '1998')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19981229', '19981227', '1999')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19991228', '20001225', '2000')
>
>
>|||That the start and end year dates repeat in a pattern, every 5 years or so.
I
got from your second post that you were adjusting your data to fit Northwind
so a solution could be tested (and that's appreciated!)
I noticed when I ran the query against Northwind, one of the years (1996 I
think) shows up as having 53 w
s in it. The difference between the yearstart date and end date for 1996 in your sample is 370 days, so that does go
beying 52 w
s. However, in a "normal" year, my math shows the last day ofthe year being in w
53. It needs some twing. If I have time, I'll postan adjusted version later.
"scott" wrote:
> First, thanks big for solving this head-hurter. My t_w
table does have> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Should have been this way from the start.
CREATE FUNCTION udfCustomW
Format (@.date datetime)RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w
_year AS char(4)) + '-'+ RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w
_beginyeardate, @.date) / 7.00)AS
varchar(3)), 2)
FROM t_w
WHERE @.date BETWEEN w_beginyeardate AND w_endyeardateRETURN @.output
END
Now the last (364th) day of the year shows up as being in w
52. For yearsthat go beyond 364 days, you will still end up with a w
53 out of thisfunction.
"scott" wrote:
> First, thanks big for solving this head-hurter. My t_w
table does have> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Are these calendar w
s starting with Sunday?William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w
/year.> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w
table to calculate our custom ws with> w
1 of any year starting with the date in the w_beginyeardate field> and the last w
of the year ending on the w_endyeardate date value.> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom w
s derived from my t_w table? Also, I> also need a fix to properly sort the w
/year field from start to end> w
/year field.>
> CODE:
> SELECT CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS w
num, SUM(Freight) AS> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w
, OrderDate) AS varchar(2)) + '-' +> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w
] (> [w
_id] [int] IDENTITY(1,1) NOT NULL,> [w
_beginyeardate] [datetime] NULL,> [w
_endyeardate] [datetime] NULL,> [w
_year] [int] NULL> ) ON [PRIMARY]
> GO
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19951226', '19961230', '1996')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19961231', '19971229', '1997')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19971230', '19981228', '1998')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19981229', '19981227', '1999')
> insert into t_w
(w_beginyeardate, w_endyeardate, w_year)> values
> ('19991228', '20001225', '2000')
>
>
>|||what's the difference in this 2nd correct function and the 1st or previous
correct function?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D4583358-03D7-4EAD-9B4C-F1E69B884861@.microsoft.com...
> Should have been this way from the start.
> CREATE FUNCTION udfCustomW
Format (@.date datetime)> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w
_year AS char(4)) + '-'> + RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w
_beginyeardate, @.date) / 7.00)> AS
> varchar(3)), 2)
> FROM t_w
WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate> RETURN @.output
> END
> Now the last (364th) day of the year shows up as being in w
52. For> years
> that go beyond 364 days, you will still end up with a w
53 out of this> function.
> "scott" wrote:
>
Subscribe to:
Posts (Atom)