Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
===================
ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
================================================== =========
You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================
> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay =
> (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth =
> (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ================================================== =========
>
|||BTW and FWIW, I added this exact example to the article.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
sqlsql

Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
=================== ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
===========================================================You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay => (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth => (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ===========================================================>|||BTW and FWIW, I added this exact example to the article.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)

composite key structure

I'm just looking to get suggestions as to what the best way to handle this
key structure is, in terms of performance. At present, the current pk is on
an identity value. I 'inherited' this and am sing to change post haste
for many reasons. These three columns combined equate to the primary key
from a business-perspective: tradetime,endpoint,ordernumber
Because of this, I have created a clustered compound primary key with the
columns in this order: tradetime,endpoint,ordernumber
Tradetime has the most selectivity, as it nearly always montonically
increases.
Endpoint is used first in most of the ad hoc where clauses.
All my procedures, however, are very date-specific. So this, too, puts
tradetime in the where clauses a lot.
tradetime is datetime, endpoint is varchar(8) and ordernumber is varchar(32)
hence, the thing is kind of wide. but, as i said before, it is the only
uniqueID from a business perspective. the db structure is both current and
historical. current is about 300-600K and nearly all continuous inserts,
historical averages around 38M and it's where the reporting and analysis is
done. the data is rarely ever updated in either.
So, should it be clustered or non? Are the columns ordered correctly in the
compound key? And any thoughts as to how this will impact insertion
performance?
thank you in advance,
-- LynnLynn,

Base on the this, you should already have a unique index by these three
columns. I think the key is to wide to be used as a clustered index. Remembe
r
that the key of a clustered index is used by the rest of nonclustered indexe
s.
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Lynn" wrote:
> I'm just looking to get suggestions as to what the best way to handle this
> key structure is, in terms of performance. At present, the current pk is
on
> an identity value. I 'inherited' this and am sing to change post haste
> for many reasons. These three columns combined equate to the primary key
> from a business-perspective: tradetime,endpoint,ordernumber
> Because of this, I have created a clustered compound primary key with the
> columns in this order: tradetime,endpoint,ordernumber
> Tradetime has the most selectivity, as it nearly always montonically
> increases.
> Endpoint is used first in most of the ad hoc where clauses.
> All my procedures, however, are very date-specific. So this, too, puts
> tradetime in the where clauses a lot.
> tradetime is datetime, endpoint is varchar(8) and ordernumber is varchar(3
2)
> hence, the thing is kind of wide. but, as i said before, it is the only
> uniqueID from a business perspective. the db structure is both current an
d
> historical. current is about 300-600K and nearly all continuous inserts,
> historical averages around 38M and it's where the reporting and analysis i
s
> done. the data is rarely ever updated in either.
> So, should it be clustered or non? Are the columns ordered correctly in t
he
> compound key? And any thoughts as to how this will impact insertion
> performance?
> thank you in advance,
> -- Lynn|||ok, amb, but are you suggesting i do not create a compound pk on these three
columns and that a unique index or unique constraint will suffice, or are yo
u
suggesting i should do the compound pk but that it should be non-clustered?
Lynn
"Alejandro Mesa" wrote:
> Lynn,
>
>
> Base on the this, you should already have a unique index by these three
> columns. I think the key is to wide to be used as a clustered index. Remem
ber
> that the key of a clustered index is used by the rest of nonclustered inde
xes.
> Tips on Optimizing SQL Server Clustered Indexes
> http://www.sql-server-performance.c...red_indexes.asp
>
> AMB
> "Lynn" wrote:
>|||> ok, amb, but are you suggesting i do not create a compound pk on these
> three
> columns and that a unique index or unique constraint will suffice, or are
> you
> suggesting i should do the compound pk but that it should be
> non-clustered?
Do you have related tables (like OrderDetails) that need referential
integrity back to this table? Do you want to store OrderNumber there, or do
you want to repeat the tradetime,endpoint,ordernumber combo in every related
table?|||Actually, no, there are very few related tables. It's a trading repository
that we continually load with trades. The other tables are specific to
accounts and bi, but not to the ordernumbers themselves. There is a need to
go back in a report on/query the data regularly, but it's done against the
historical table, which is structured identically.
--
Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Do you have related tables (like OrderDetails) that need referential
> integrity back to this table? Do you want to store OrderNumber there, or
do
> you want to repeat the tradetime,endpoint,ordernumber combo in every relat
ed
> table?
>
>|||Lynn,
What I meant was that an identity column as pk, does not asure that there
will not be duplicated rows by (tradetime,endpoint,ordernumber) and because
these columns, as you said, equate to the primary key from a
business-perspective then it should exists already a constraint to force the
uniqueness by these columns. The question is if this unique index should be
clustered or not?. I will also consider what Aaron is asking you for (is
there any other table that is referencing this table?). Well, there are othe
r
things to consider like if there are other columns that you use for range
queries or are used in the "group by" clause, what is more important for you
"select" or "insert" performance, etc. Take a look to the article in the
link, it can help you to understand what are the columns in your table prope
r
for a clustered index.
AMB
"Lynn" wrote:
> ok, amb, but are you suggesting i do not create a compound pk on these thr
ee
> columns and that a unique index or unique constraint will suffice, or are
you
> suggesting i should do the compound pk but that it should be non-clustered
?
> --
> Lynn
>
> "Alejandro Mesa" wrote:
>|||Yes, I understand that the identity column pk didn't prevent dupes, nor is i
t
portable at all. All it does is sequentially number the records and it has
no pertinence whatsoever to the actual data value. Except for very few
maintenace scripts, the data is never queried looking on identity value.
Hence, I'm changing it. But I'm not done yet. That's the reason for my
inquiry. I'm trying to find the best way to get us where we need to be. I
understand the three columns already imply a constraint to force the
uniqueness. But that constraint does not yet exist. I intend to drop the
existing pk and replace with one that is actually meaningful, and I feel it
should be based upon these three fields, which uniquely identify our data.
I've already done this in my dev bed as clustered, and I've actually read
that article before several times - it is what prompted me to cluster the pk
in the first place because of the remark near the bottom about wide indices.
But today i've been researching and have seen the nonclustered composite pk
referenced several times, so now I'm debating whether I've done it properly
and I thought I'd s a little advice.
--
Lynn
"Alejandro Mesa" wrote:
> Lynn,
> What I meant was that an identity column as pk, does not asure that there
> will not be duplicated rows by (tradetime,endpoint,ordernumber) and becaus
e
> these columns, as you said, equate to the primary key from a
> business-perspective then it should exists already a constraint to force t
he
> uniqueness by these columns. The question is if this unique index should b
e
> clustered or not?. I will also consider what Aaron is asking you for (is
> there any other table that is referencing this table?). Well, there are ot
her
> things to consider like if there are other columns that you use for range
> queries or are used in the "group by" clause, what is more important for y
ou
> "select" or "insert" performance, etc. Take a look to the article in the
> link, it can help you to understand what are the columns in your table pro
per
> for a clustered index.
>
> AMB
> "Lynn" wrote:
>|||>> Except for very few
maintenace scripts, the data is never queried looking on identity
value.
Hence, I'm changing it.<<
usually we need a stronger reason for changing a working system. What
are your other reasons?|||We have no unique ID. And many, many holes because of this.
--
Lynn
"AK" wrote:

> maintenace scripts, the data is never queried looking on identity
> value.
> Hence, I'm changing it.<<
> usually we need a stronger reason for changing a working system. What
> are your other reasons?
>|||> We have no unique ID. And many, many holes because of this.
AK is merely suggesting that you could easily add a UNIQUE CONSTRAINT or
INDEX to your three columns, and leave the rest as is. The IDENTITY doesn't
have to be the PK but you don't have to remove it completely if the system
will continue to work while it is still there. And you never know, you may
later want to use a skinnier pk than you three wide columns for related
tables (given that such things don't exist now, think about trying to undo
this change later).

Comparison with Oracle

In Oracle, there is something called dbms_application_info
where developers can set some application information for
a current session and that piece of information can be
retrieved or updated within the same session. Is there
something similar in SQL Server? Thanks."Peter" <pchow@.ureach.com> wrote in message
news:06b201c36061$a50fdbc0$a301280a@.phx.gbl...
> In Oracle, there is something called dbms_application_info
> where developers can set some application information for
> a current session and that piece of information can be
> retrieved or updated within the same session. Is there
> something similar in SQL Server?
Package variables do that much better, BTW.
The closest SQLServer has is
SET CONTEXT_INFO
You can store 128 bytes of binary data on the session.
The data just goes into master.dbo.sysprocesses.context_info
This is also the only way to store data outside of the current transaction
context.
David|||I found it a lot easier to use a permanent table with information relevant
to the spid than to use CONTEXT_INFO (but that was also caused by our
particular requirements), see the code below.
CREATE TABLE session_role(
spid INT NOT NULL,
role_id INT NOT NULL
CONSTRAINT PK_session_role PRIMARY KEY CLUSTERED (spid),
CONSTRAINT FK_session_role_role FOREIGN KEY (role_id)
REFERENCES role_definitions (id))
GO
CREATE PROCEDURE SetSessionRole @.nRoleID INT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
UPDATE session_role
SET role_id = @.nRoleID
WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id)
VALUES (@.@.spid, @.nRoleID)
GO
CREATE PROCEDURE GetSessionRole @.nRoleID INT OUPUT
AS
SET NOCOUNT ON
SET @.nRoleID = (SELECT role_id FROM session_role WHERE spid = @.@.spid)
GO
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Obu0gMGYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> "Peter" <pchow@.ureach.com> wrote in message
> news:06b201c36061$a50fdbc0$a301280a@.phx.gbl...
> > In Oracle, there is something called dbms_application_info
> > where developers can set some application information for
> > a current session and that piece of information can be
> > retrieved or updated within the same session. Is there
> > something similar in SQL Server?
> Package variables do that much better, BTW.
> The closest SQLServer has is
> SET CONTEXT_INFO
> You can store 128 bytes of binary data on the session.
> The data just goes into master.dbo.sysprocesses.context_info
> This is also the only way to store data outside of the current transaction
> context.
> David
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OJ4rYXNYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> I found it a lot easier to use a permanent table with information relevant
> to the spid than to use CONTEXT_INFO (but that was also caused by our
> particular requirements), see the code below.
>
The caviat here is that this session table can become a trouble spot for
locking in the application. Two otherwise unrelated transactions can
serialize or even deadlock because they involve reads and writes to this
table.
David|||Unlikely, as there is an index and a primary key on the spid column, and no
two transactions can access the same row because they have different spids.
The only situation I can think of when blocking might occur is when a new
row is inserted, as it will then take out a range lock on the index, and the
spids next to it won't be available. This won't happen very often and can
even be avoided by prepopulating the table with an appropriate range of
spids.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uOn4EGOYDHA.2648@.TK2MSFTNGP09.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OJ4rYXNYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> > I found it a lot easier to use a permanent table with information
relevant
> > to the spid than to use CONTEXT_INFO (but that was also caused by our
> > particular requirements), see the code below.
> >
> The caviat here is that this session table can become a trouble spot for
> locking in the application. Two otherwise unrelated transactions can
> serialize or even deadlock because they involve reads and writes to this
> table.
> David
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OwGlUbOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> Unlikely, as there is an index and a primary key on the spid column, and
no
> two transactions can access the same row because they have different
spids.
> The only situation I can think of when blocking might occur is when a new
> row is inserted, as it will then take out a range lock on the index, and
the
> spids next to it won't be available. This won't happen very often and can
> even be avoided by prepopulating the table with an appropriate range of
> spids.
>
As written, the EXISTS check in SetSessionRole will require a shared read
lock on every row in session_role. This will block if another transaction
has run SetSessionRole inside an open transaction. And if SetSessionRole is
run in SERIALIZABLE isolation, these shared read locks will be held until
the end of that transaction.
It's probably not a big deal, but it's a big difference from Oracle package
variables, and it's something to keep an eye on.
David|||Hi David,
The EXISTS check can be run WITH(NOLOCK) and then there will be no blocking,
and every process will only lock the row with it's own spid. Thanks for
pointing out the problem.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
IF EXISTS(SELECT NULL FROM session_role WITH(NOLOCK) WHERE spid = @.@.spid )
UPDATE session_role SET role_id = 1 WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id) VALUES(@.@.spid, 1)
-- COMMIT TRAN
The whole thing of working with spids is a bit dubious by the way if you
work with connection pooling. You have the same connection but it doesn't
have to be the same user, in which case it might be better to keep the
information on the middle tier.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23GUoJlOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OwGlUbOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> > Unlikely, as there is an index and a primary key on the spid column, and
> no
> > two transactions can access the same row because they have different
> spids.
> > The only situation I can think of when blocking might occur is when a
new
> > row is inserted, as it will then take out a range lock on the index, and
> the
> > spids next to it won't be available. This won't happen very often and
can
> > even be avoided by prepopulating the table with an appropriate range of
> > spids.
> >
> As written, the EXISTS check in SetSessionRole will require a shared read
> lock on every row in session_role. This will block if another transaction
> has run SetSessionRole inside an open transaction. And if SetSessionRole
is
> run in SERIALIZABLE isolation, these shared read locks will be held until
> the end of that transaction.
> It's probably not a big deal, but it's a big difference from Oracle
package
> variables, and it's something to keep an eye on.
> David
>

Comparing values between 2 matrices (matrix)

Hello
I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets

I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.

Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".

Any suggestions are appreciated.
Thanks

I would like to know the official answer to this too...

see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673719&SiteID=1 for simular question...