DB and tables information

Hi there!
Using SQL Enterprise Manager I can see some information
about DB usage and tables and indexes (right click over
DB name and View option)
How ca I print or export this information? Is there any
store procedure(s) to get this information?
Thanks!
LJLJ,
SQL Profiler is great for determining the queries used by an application.
Here's the code that EM uses to populate the tables/indexes Taskpad view:
select
sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName,
sysindexes.rows,
case indid
when 1 then 1
else 0
end as IsClusteredIndex,
sysindexes.indid,
sysobjects.name,
sysusers.name
from
sysusers, sysobjects, sysindexes
where
sysusers.uid = sysobjects.uid
and
sysindexes.id = sysobjects.id
and
sysobjects.name not like '#%'
and
OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and
OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by
ObjectName,
IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'),
IndexName
Dan Farino
Sr. Systems Engineer
Stamps.com, Inc.
news.danATstamps.com
"LJ" <leyla.garcia@.unisabana.edu.co> wrote in message
news:00f801c3424b$0f0a4ea0$a301280a@.phx.gbl...
> Hi there!
> Using SQL Enterprise Manager I can see some information
> about DB usage and tables and indexes (right click over
> DB name and View option)
> How ca I print or export this information? Is there any
> store procedure(s) to get this information?
> Thanks!
> LJ

Daylight Savings Time Change

Can you tell me what kind of impact we are looking at from SQL server point
of view for the daylight savings time change that is happening soon.
SQL 7.0
SQL 2000
SQL 2005
Will there be a hot fix?
ThanksSQL Server takes its date and time from Windows. There is no hot fix
required for SQL Server, since the DST change is dealt with at the operating
system layer.
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||Take a look into this URL:-
http://www.microsoft.com/windows/timezone/dst2007.mspx
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server takes its date and time from Windows. There is no hot fix
> required for SQL Server, since the DST change is dealt with at the operati
ng
> system layer.
Technically, not quite. SQL Server Notification Services 2.0 RTM and SP1,
and SQL Server Notification Services 2005 SP1 are affected. They store time
zone info in a table NSTimeZoneDstOffsets. A fix will be required.
Linchi

Daylight Savings Time Change

Can you tell me what kind of impact we are looking at from SQL server point
of view for the daylight savings time change that is happening soon.
SQL 7.0
SQL 2000
SQL 2005
Will there be a hot fix?
Thanks
SQL Server takes its date and time from Windows. There is no hot fix
required for SQL Server, since the DST change is dealt with at the operating
system layer.
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks
|||Take a look into this URL:-
http://www.microsoft.com/windows/timezone/dst2007.mspx
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server takes its date and time from Windows. There is no hot fix
> required for SQL Server, since the DST change is dealt with at the operating
> system layer.
Technically, not quite. SQL Server Notification Services 2.0 RTM and SP1,
and SQL Server Notification Services 2005 SP1 are affected. They store time
zone info in a table NSTimeZoneDstOffsets. A fix will be required.
Linchi

Daylight Savings Time Change

Can you tell me what kind of impact we are looking at from SQL server point
of view for the daylight savings time change that is happening soon.
SQL 7.0
SQL 2000
SQL 2005
Will there be a hot fix?
ThanksSQL Server takes its date and time from Windows. There is no hot fix
required for SQL Server, since the DST change is dealt with at the operating
system layer.
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||Take a look into this URL:-
http://www.microsoft.com/windows/timezone/dst2007.mspx
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||"Aaron Bertrand [SQL Server MVP]" wrote:
> SQL Server takes its date and time from Windows. There is no hot fix
> required for SQL Server, since the DST change is dealt with at the operating
> system layer.
Technically, not quite. SQL Server Notification Services 2.0 RTM and SP1,
and SQL Server Notification Services 2005 SP1 are affected. They store time
zone info in a table NSTimeZoneDstOffsets. A fix will be required.
Linchi

datetime format

Hi.
I have two parameters called StartDate and EndDate.These parameters are from datetime type.
I want to view the records between these dates.I have some questions:

1)In the database, these parameters' values are like 15.11.1984 23:59:14. It has time value near the date value.But I don't want to view the time value.I only want the date part.

2)In the preview tab, I choose a date clicking the calendar image near the parameter textbox.
For example I choose 02.05.2001 and when I click the view report button, it changes to 05.02.2001.So there is a format difference.I want it to show like dd.mm.yyyy

3)By default, if the user doesn't enter a date, I want to view all the records.Any idea about this?

Thanks!

Try doing a convert on your database datetime field similar to this in your query

convert(datetime, "datefield", 104)

This will format the date as dd.mm.yyyy. You can also do this on the parameter value so they are both in the same format. Your query would look something like this:

select * from table where convert(datetime, "datefield", 104) >= convert(datetime, @.StartDate, 104) and convert(datetime, "datefield", 104) <= convert(datetime, @.EndDate,104)

To display all records you can set the default values to the maximum and minimum dates in your database. The issue with this is that everytime the report is opened, it will automatically run for all dates. Not sure how to make it work only if the user doesn't select dates.

|||kmcclung thanks for the reply.
But it didn't work.
I wrote convert(datetime, myDateField, 104) and then tried the third parameter for 103, 4, ...
But it didn't change.
Then I realized that it is not dependent on that number.
It uses only the default datetime format.
The records in my database are like dd.mm.yyyy hh:mm:ss
And after I used the CONVERT function NOTHING changed.
I only want the date part to be visible.(only want this)
And the second problem is that as I said before when I click the calendar button near the date texbox area and select a date like 15.12.2001 then it is written to textbox like 12.15.2001.
And because of not existing a month number like 15 an error occurs.
I mean that I want to change that calendar's format.

How can I correct this?|||What type of database you are using?|||

0) It sounds like your database is NOT storing dates with a DATETIME format. Why not?

1) To take '15.11.1984 23:59:14' and store it as a DATETIME with time stripped off (set to midnite):

CONVERT(DATETIME, CAST(CONVERT(DATETIME, '15.11.1984 23:59:14', 104) AS INT))

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
Clint
SQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>
|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I have a column in a view which is of the DATETIME datatype. This is
> fine,
> > but when I output this to MS Reporting Services it also shows the time
> > (which is always 12:00 as we are not using time as a field).
> >
> > How do I use the cast statement or another statement to have only the
> date?
> > I have read BOL without success.
> >
> > Thanks for any help provided.
> >
> > Clint
> >
> >
>

DateTime

I have a datetime field and would like to get a new field (in a view) that substracts 10mn from it. Any function out there to do that?
Thanks
A quick example:
CREATE TABLE Tx (i datetime)
INSERT INTO Tx (i) SELECT '20040512 16:50'
GO
CREATE VIEW Vx AS SELECT i, DATEADD(mi, -10, i) AS NewColumn FROM Tx
GO
SELECT * FROM Vx
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Niles" <anonymous@.discussions.microsoft.com> wrote in message
news:157DB94F-A674-4A2F-A2A1-0704B278C5BB@.microsoft.com...
I have a datetime field and would like to get a new field (in a view) that
substracts 10mn from it. Any function out there to do that?
Thanks

DateTime

I have a datetime field and would like to get a new field (in a view) that s
ubstracts 10mn from it. Any function out there to do that?
ThanksA quick example:
CREATE TABLE Tx (i datetime)
INSERT INTO Tx (i) SELECT '20040512 16:50'
GO
CREATE VIEW Vx AS SELECT i, DATEADD(mi, -10, i) AS NewColumn FROM Tx
GO
SELECT * FROM Vx
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Niles" <anonymous@.discussions.microsoft.com> wrote in message
news:157DB94F-A674-4A2F-A2A1-0704B278C5BB@.microsoft.com...
I have a datetime field and would like to get a new field (in a view) that
substracts 10mn from it. Any function out there to do that?
Thanks

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,- 30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112
))
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.droptable.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it c
ame back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.droptable.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of[vbcol=seagreen]
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
James
Try:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
David Portas
SQL Server MVP
|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.sqlmonster.com
|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.sqlmonster.com
|||Thank you both.
|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>
|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>
|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...[vbcol=seagreen]
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
--
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
--
Message posted via http://www.sqlmonster.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
--
Message posted via http://www.sqlmonster.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
--
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
--
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> >I have a view that I'm building in which I need to get all records that
> >have
> > an InvoicedDate in in the past 30 days. So basically I set up part of
the
> > where clause as:
> >
> > (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> >
> > Which doesn't work because the function returns a date and a time,
whereas
> > I'm only storing dates in the InvoicedDate field. So, long story short,
> > it
> > doesn't get the dates from exactly 30 days ago. I could roll some kind
of
> > hack where I add one I guess, but that seems really hokey. What's a
> > better
> > way to accomplish this?
> >
> > Thanks!
> > James
> >
> >
>

Dates and Parameters

I have created a report with parameters, I select the dates (01/05/2007) to
(01/07/07) using the calendar and then click view report the dates then reset
to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
are displayed in the report are returning the correct values. When you click
view report again without amending the dates the data is returned and the
parameters values as displayed in the report are incorrect. Please help.On Jul 6, 6:28 am, Changing Dates in reports & parameters <Changing
Dates in reports & paramet...@.discussions.microsoft.com> wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.
You most likely want to check/change your computer's/server's regional
settings. This can be done through the control panel. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Checked regional settings on both the Server and my local pc and both are
British English.
thanks
lisa
"Changing Dates in reports & parameters" wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.

dates

I have a view that shows me how many visits i have had on my website.
UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April (using
datename)), TheDay (Sunday (using datename),TheDate (10 (using datepart)).
The result is like this:
.......
23, 2005, April, Sunday, 10
So for April i so far has 10 records since it is April 10.
The table is like this:
CREATE TABLE [dbo].[T_PageStat] (
[IDStat] [int] IDENTITY (1, 1) NOT NULL ,
[DateRegistered] [datetime] NULL ,
[Counter] [numeric](18, 0) NULL ,
[IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
[BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
[LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
) ON [PRIMARY]
As you see i am collecting the date the visitor entered, a counter telling
me how many times this visitor entered, the IP, what kind of browser, and
finally the language the user has set in browser language. I use SPROC to
populate the table
I want to update my view so that i can get a record for every day in the
month even tho it is only April 10. The rest of the days will be 0
(11,12....)
Not sure how to do that so i was hoping for some help.
Any tip will be appreciated. I am using a SQL 2000 server
Best regards, Trond
The code for the view:
CREATE VIEW dbo.statUniquePrMonth
AS
SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
DateRegistered) AS TheDate
FROM dbo.T_PageStat
GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE())) AND
(DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
ORDER BY DATEPART(dd, DateRegistered)Hi
This may be easiest with a calander table e.g
http://www.aspfaq.com/show.asp?id=2519
You can then use an outer join to get all the days in the given month.
John
"Trond" <thoiberg@.broadpark.no> wrote in message
news:4258d99c$1@.news.broadpark.no...
>I have a view that shows me how many visits i have had on my website.
> UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April
> (using datename)), TheDay (Sunday (using datename),TheDate (10 (using
> datepart)).
> The result is like this:
> .......
> 23, 2005, April, Sunday, 10
> So for April i so far has 10 records since it is April 10.
> The table is like this:
> CREATE TABLE [dbo].[T_PageStat] (
> [IDStat] [int] IDENTITY (1, 1) NOT NULL ,
> [DateRegistered] [datetime] NULL ,
> [Counter] [numeric](18, 0) NULL ,
> [IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
> [BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
> [LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
> ) ON [PRIMARY]
> As you see i am collecting the date the visitor entered, a counter telling
> me how many times this visitor entered, the IP, what kind of browser, and
> finally the language the user has set in browser language. I use SPROC to
> populate the table
>
> I want to update my view so that i can get a record for every day in the
> month even tho it is only April 10. The rest of the days will be 0
> (11,12....)
> Not sure how to do that so i was hoping for some help.
> Any tip will be appreciated. I am using a SQL 2000 server
> Best regards, Trond
> The code for the view:
> CREATE VIEW dbo.statUniquePrMonth
> AS
> SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
> DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
> DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
> DateRegistered) AS TheDate
> FROM dbo.T_PageStat
> GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
> DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
> HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE()))
> AND (DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
> ORDER BY DATEPART(dd, DateRegistered)
>

Dateproblem

Hello
I'm trying to get data from a view and it's not going well, im trying to
"group" the rows abit so I dont end up with a million rows to display a date
range....
Hope this makes sense....
CREATE TABLE #Test (
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(Startdate,Enddate,Avalible)
SELECT
'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Testerr... use this DLL instead..
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,Avalible)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse Edsvik wrote:

> I'm trying to get data from a view and it's not going well, im trying
> to "group" the rows abit so I dont end up with a million rows to
> display a date range....
> Hope this makes sense....
I once had the same problem and question asked: http://tinyurl.com/7gnst
HTH,
Stijn Verrept.|||Or this if it makes it more simple.... :S
CREATE TABLE #Test (
ObjectID int,
Wkday tinyint,
StartTime char(5),
EndTime char(5),
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Wkday,StartTime,EndTime,Avalib
le)
SELECT
1,1,'13:00','13:30','Y'
UNION SELECT
1,1,'13:30','14:00','Y'
UNION SELECT
1,1,'14:00','14:30','N'
UNION SELECT
1,1,'14:30','15:00','N'
UNION SELECT
1,1,'15:00','15:30','Y'
UNION SELECT
1,1,'15:30','16:00','Y'
UNION SELECT
1,1,'16:00','16:30','N'
UNION SELECT
1,2,'15:00','15:30','Y'
UNION SELECT
1,2,'15:30','16:00','N'
SELECT * FROM #Test
/*
Desired result:
ObjectID Wkday StartTime EndTime
1 1 '13:00' '14:00'
1 1 '15:00' '16:00'
1 2 '15:00' '15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse,
select ObjectID,Startdate, coalesce(last_available, enddate) enddate
from
(
SELECT t.*,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='Y') prev_available,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='N')
prev_not_available,
(select max(enddate) FROM #Test t1
where t1.startdate>t.startdate and t1.available='Y'
and not exists(select 1 from #test t2
where t2.available='N' and t2.startdate between t.startdate and
t1.startdate)
) last_available
FROM #Test t
where available='Y'
) t
where prev_available<prev_not_available
or prev_available is null
ObjectID Startdate
enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
(3 row(s) affected)
Nice puzzle, thanks!|||hmm,
Server: Msg 207, Level 16, State 3, Line 30
Invalid column name 'available'.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139324776.706400.210190@.f14g2000cwb.googlegroups.com...
> Lasse,
> select ObjectID,Startdate, coalesce(last_available, enddate) enddate
> from
> (
> SELECT t.*,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='Y') prev_available,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='N')
> prev_not_available,
> (select max(enddate) FROM #Test t1
> where t1.startdate>t.startdate and t1.available='Y'
> and not exists(select 1 from #test t2
> where t2.available='N' and t2.startdate between t.startdate and
> t1.startdate)
> ) last_available
> FROM #Test t
> where available='Y'
> ) t
> where prev_available<prev_not_available
> or prev_available is null
> ObjectID Startdate
> enddate
> -- ---
> ---
> 1 2006-01-31 13:00:00.000
> 2006-01-31 14:00:00.000
> 1 2006-01-31 15:00:00.000
> 2006-01-31 16:00:00.000
> 1 2006-02-01 15:00:00.000
> 2006-02-01 15:30:00.000
> (3 row(s) affected)
> Nice puzzle, thanks!
>|||yes the spell checker replaced Avalible with Available. I did not argue
with it ;)|||Alexander,
oh k :) You have any idea why ObjectID=2 doesnt show up?
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
available char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,available)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
2,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
2,'2006-01-31 14:00','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-02 15:00','2006-02-02 15:30','Y'
UNION SELECT
1,'2006-02-02 16:00','2006-02-02 16:30','Y'
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t1.startdate>t.startdate and t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.Available='N' and t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139325716.672360.263040@.g47g2000cwa.googlegroups.com...
> yes the spell checker replaced Avalible with Available. I did not argue
> with it ;)
>|||because I did not think of ObjectId at all. It's easy to take care of
however:
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate>t.startdate and
t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.ObjectID=t1.ObjectID and t2.Available='N' and
t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
ObjectID Startdate
Enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
2 2006-01-31 13:30:00.000
2006-01-31 16:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
1 2006-02-02 15:00:00.000
2006-02-02 16:30:00.000
(5 row(s) affected)

Datediff problem

Any one please tell me how to fix the view..it's producing lots of errors -

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT E.EmpNo,

IF E.ResignDate IS NOT NULL

MonthWorked = DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )

FROM Employees E

Code Snippet

Msg 156, Level 15, State 1, Procedure vw_Issues_Deducts, Line 10

Incorrect syntax near the keyword 'IF'.

Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 11

Incorrect syntax near 'MonthWorked'.

Regards

Kapalic

You have to use the CASE WHEN...

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT

E.EmpNo,

MonthWorked =

Case When E.ResignDate IS NOT NULL

DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )

END

FROM

Employees E

|||

Still geting error -

Code Snippet

Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 16
Incorrect syntax near 'DATEDIFF'.

Regards

Kapalic

|||

Fixed it!

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT E.EmpNo,

MonthWorked = Case When E.ResignDate IS NOT NULL THEN

DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

DATEDIFF( M, E.JoinDate, GETDATE() )

END

FROM Employees E

Regards

Kapalic