Finding designation of an employee on a given date

Hi all,

I have two tables

CREATE TABLE [JEMP] (
[EMPID] [int] NOT NULL ,
[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE
[DOB] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [JPRO] (
[PromoID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpID] [int] NOT NULL ,
[EffectiveDate] [smalldatetime] NOT NULL ,
[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION
[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION
) ON [PRIMARY]
GO

INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')

INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2002-15-11 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2003-03-01 00:00:00',8,7)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2002-01-04 00:00:00',20,22)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2005-05-01 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(5,'2001-10-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(6,'2001-08-01 00:00:00',55,NULL)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(7,'2003-10-01 00:00:00',11,8)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(8,'2001-09-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(9,'2002-01-05 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2002-11-01 00:00:00',24,25)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2003-11-15 00:00:00',23,24)

--
I wish to find the designation of employee on given date by using
promotion and master table . I am using the following query to get the
result

select isnull( ( select top 1 newdesigid from JPRO where
empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
JatinderHistory is usually modeled in durations with a (start_time, end_time)
pair; this lets you use a BETWEEN predicate for most of your queries.|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I wish to find the designation of employee on given date by using
> promotion and master table . I am using the following query to get the
> result
> select isnull( ( select top 1 newdesigid from JPRO where
> empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
> , (select desigid from empmast where empid=1) )
>
> It did give the result but looking for better method to solve this.

I don't see anything seriously wrong with that query. Here is an
alternate:

select Top 1 NewDesigID
from (select NewDesigID, EffectiveDate
from JPRO
where EmpID=7 and
EffectiveDate < '20050301'
union
select DESIGID, '19000101'
from JEMP
where EMPID=7) AS x
order by EffectiveDate desc

But I'm not sure in what way it would be "better".

If you are looking for a more ANSI way of doing it, you would have
to get the MAX(EffectiveDate) and then join back to the derived
table again. As you may guess, this is likely to be less effecient.
In SQL 2005, you could use a CTE (Common Table Expresssion) to avoid
repetition of the code for the derived table, but alas the query
plan is likely to be equally ineffecient.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Find uniques on part of a multi-column PK (with where clause)

Hello all,
Follows a simplified DDL of the table where i'm working on:
CREATE TABLE Test
(
TestID bigint NOT NULL ,
SubTestID bigint NOT NULL ,
Name varchar (50),
Master bigint NOT NULL ,
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
(
TestID,
SubTestID
)
)
What I'm trying to achieve is to collect and retrieve all rows of which
Master differs from the value 2 and TestID is unique.
So far, the best I achieved was:
SELECT
counter.TestID
FROM
(
SELECT
TestID,
COUNT(TestID) AS Total
FROM
Test
WHERE Master <> 2
GROUP BY
TestID
) as counter
WHERE counter.Total = 1
However this strategy doesn't suit me since I need the TestID/SubTestID
pair to be returned. Needless to say, introducing SubTestID in the
above query will invalidate de aggregate function.
Can someone please help?Mario
Needless to say, that posting sample data + expected result will help to
bring a right solution. No offence.
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Lazy solution:
SELECT TestID, MAX(SubTestID ) AS SubTestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1
More structured solution:
SELECT t1.TestID, t1.SubTestID
FROM Test t1
INNER JOIN (
SELECT TestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1) a
ON t1.TestID = a.TestID
Jacco Schalkwijk
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Try,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
COUNT(distinct TestID) = 1
) as a
on t.TestID = a.TestID
go
AMB
"Mario F." wrote:

> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||One method:
SELECT
a.TestID,
a.SubTestID
FROM Test a
JOIN
(
SELECT
TestID
FROM Test
WHERE Master <> 2
GROUP BY
TestID
HAVING COUNT(*) = 1
) AS counter ON counter.TestID = a.TestID
Hope this helps.
Dan Guzman
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Correction,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
min(SubTestID) = max(SubTestID)
) as a
on t.TestID = a.TestID
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> t.*
> from
> test as t
> inner join
> (
> SELECT
> TestID
> FROM
> Test
> WHERE
> Master <> 2
> GROUP BY
> TestID
> having
> COUNT(distinct TestID) = 1
> ) as a
> on t.TestID = a.TestID
> go
>
> AMB
> "Mario F." wrote:
>|||The Having clause. Of course. Thank you everyone.
Mario F.
*** Sent via Developersdex http://www.examnotes.net ***sql

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).
Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>
|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>> is this possible in T-SQL? (SQL Server 2000)
>> I want to find if the field is not null but empty (has no text in it).
>>
>>
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>sql

Find subsequent days

I have the following table:
CREATE TABLE [dbo].[ShortStay](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SS_WLID] [int] NULL,
[SS_From] [smalldatetime] NOT NULL,
[SS_Till] [smalldatetime] NOT NULL)
There are these records for example:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-22', '2006-05-25')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (4, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-26', '2006-05-29')
I'm trying to find a query that returns the number of consequent days
based on a certain date:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
You get 2006-05-02 because the SS_Till is the day before 2006-05-05
that's why this row is ok.
You get 2006-05-29 because:
2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
2006-05-29
I hope this makes sense. So the days have to be consequent in order to
be able to be part of the count.
I have been trying to join the table to itself but I'm getting nowhere.
Thanks in advance,
Stijn Verrept.Consider creating an auxiliary Calendar table.
http://www.aspfaq.com/show.asp?id=2519
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Roji. P. Thomas wrote:

> Consider creating an auxiliary Calendar table.
> http://www.aspfaq.com/show.asp?id=2519
Thanks for the link, I already have a Calendar table but don't see how
this solves the subsequent problem, it's in none of the examples on the
site.
Kind regards,
Stijn Verrept.|||Stijn,
google up "How to simplify a query using a calendar table", hope that
helps|||How about something like this (note that it looks for consecutive days,
defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
aren't taken into consideration. Neither are overlapping dates.
DECLARE @.date DATETIME
SELECT @.date = '2006-05-09'
DECLARE @.SS_ID INT
SELECT @.SS_ID = 3
SELECT 'Begin' AS DateType, s1.Start
FROM (
SELECT MIN(SS_From) AS Start
FROM ShortStay
WHERE @.date BETWEEN SS_From AND SS_Till
AND SS_WLID = @.SS_ID
) s1
UNION ALL
SELECT 'End', s2.Finish
FROM
(
SELECT MAX(s1.SS_Till) AS Finish
FROM ShortStay s1, ShortStay s2
WHERE s1.SS_From = s2.SS_Till + 1
AND s1.SS_From <>
(
SELECT MIN(SS_From)
FROM ShortStay
WHERE SS_WLID = @.SS_ID
AND @.date BETWEEN SS_From AND SS_Till
)
AND s1.SS_WLID = s2.SS_WLID
AND s1.SS_WLID = @.SS_ID
) s2
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Hi There,
You may like to try this!!
I assume that SS_From is smaller than SS_Till
Select Min(SS_From),Max(SS_Till) from ShortStay where SS_WLID=3
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_From,112)
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_Till,112)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Mike C# wrote:
> How about something like this (note that it looks for consecutive days,
> defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
> aren't taken into consideration. Neither are overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
> SELECT 'Begin' AS DateType, s1.Start
> FROM (
> SELECT MIN(SS_From) AS Start
> FROM ShortStay
> WHERE @.date BETWEEN SS_From AND SS_Till
> AND SS_WLID = @.SS_ID
> ) s1
> UNION ALL
> SELECT 'End', s2.Finish
> FROM
> (
> SELECT MAX(s1.SS_Till) AS Finish
> FROM ShortStay s1, ShortStay s2
> WHERE s1.SS_From = s2.SS_Till + 1
> AND s1.SS_From <>
> (
> SELECT MIN(SS_From)
> FROM ShortStay
> WHERE SS_WLID = @.SS_ID
> AND @.date BETWEEN SS_From AND SS_Till
> )
> AND s1.SS_WLID = s2.SS_WLID
> AND s1.SS_WLID = @.SS_ID
> ) s2
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...|||Mike C# wrote:

> How about something like this (note that it looks for consecutive
> days, defined exactly as SS_From = prior SS_Till + 1). Hours,
> minutes, etc., aren't taken into consideration. Neither are
> overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
Hi Mike,
Thanks for the reply! This one is very close but it doesn't look for
any prior consecutive rows.
In the examply I gave:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
Your query returns '2006-05-05' and '2006-05-29'
It doesn't consider the row:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Thanks again,
Stijn Verrept.|||People will tell you to use a Calendar table. But your real problem is
a bad design. You have no key (IDENTITY cannot ever be a key! Quit
mimicing a sequential file), and your "wl_id" can be NULL so the query
is impossible to answer. You also have no constraints and use
proprietary data types. Then on top of all of that, you have attribute
splitting -- the fact of a continous stay is spread over many rows.
Why did you use a singular name for a table with more than one element
in it? You have no idea what an identifier is and stuck it on
everything. Your table should look more like this:
CREATE TABLE ShortStays
(wl_id INTEGER NOT NULL,
arrival_date DATETIME NOT NULL,
depart_date DATETIME NOT NULL,
CHECK(arrival_date < depart_date),
PRIMARY KEY (wl_id, arrival_date));
When a stay is extended, you update it instead of mimicking a paper
hotel register form. Ideally, you should have constraint to prevent
overlaps, but that is hard to do in SQL Server since it still lacks
much of the SQL-92 features. You can do it with a TRIGGER or with an
updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
Please learn the differences between rows and records. Fail to know
that lead to attribute splitting.|||--CELKO-- wrote:

> People will tell you to use a Calendar table. But your real problem
> is a bad design. You have no key (IDENTITY cannot ever be a key!
> Quit mimicing a sequential file), and your "wl_id" can be NULL so the
> query is impossible to answer. You also have no constraints and use
> proprietary data types. Then on top of all of that, you have
> attribute splitting -- the fact of a continous stay is spread over
> many rows.
Well Joe, the table creation script given here is just a simplified
version of the real table so hold your horses. People can stay in
different rooms and can be transferred from one room to another so
updating instead of inserting is not an option here. I didn't include
the column of the room because for this query it is irrelevant!

> When a stay is extended, you update it instead of mimicking a paper
> hotel register form. Ideally, you should have constraint to prevent
> overlaps, but that is hard to do in SQL Server since it still lacks
> much of the SQL-92 features. You can do it with a TRIGGER or with an
> updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
I already have a trigger that prevents overlap so don't worry about
that.

> Please learn the differences between rows and records. Fail to know
> that lead to attribute splitting.
LOL
"In the context of a relational database, a rowalso called a record or
tuplerepresents a single, implicitly structured data item in a table."
Wikipedia.
Will learning the naming difference really solve my query? I think not.
Thanks for your very helpful reply!
Kind regards,
Stijn Verrept.

Find empty values in a column

Hello,
I am trying to replicate somthing I did in my access reports.
I need to count how many 0 dollar values or null values show up in a
combination of two colums.
I dont know where to begin in Reporting Services. In Acces this was VBA.
Here is the VBA CODE.
End If
If Nz(Me.sQuotedPrice, 0) = "0" Or Len(Me!sQuotedPrice) = 0 And
Nz(Me.sGuessPrice, 0) = "0" Or Len(Me!sGuessPrice) = 0 Then
sJobsMissingPrices = sJobsMissingPrices + 1
End If
I try this in Reporting Services and it says it is not with in the correct
scope?
Public Shared Function GetEmptyValues()
Dim JobsMissingPrices
If ReportItem!JobPrice.Value= 0 Or Len(ReportItems!JobPrice.value) = 0 And
NzReportItems!Guess_Price = "0" Or Len(ReportItems!Guess_Price) = 0 Then
JobsMissingPrices = JobsMissingPrices + 1
End If
End Function
Any help would be appreciated.
--
Thank You, LeoHi Leo,
It seems there is something wrong here, an additional "Nz" was not removed
NzReportItems!Guess_Price = "0"
If removing Nz doesn't resolve this issue, please provide more detailed
information about this two values and I would like to make a reproduce on
my side.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Leo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

find available time slot

I have a table that looks like:
CREATE TABLE [dbo].[UserAgenda] (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
) ON [PRIMARY]
GO
and I would like to find a time gap for the amount of time I am looking
for.
For example, I want the next available hour in the list or the next
available 15 minutes.
Any help would be appreciated.
Thanks
Richard MeetzeAm I correct that "next available" means a period of time that won't
overlap the period between StartTime and EndTime on any row in the
table? Try this:
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
David Portas
SQL Server MVP
--|||David - you are correct, thanks for the help.
I have another question if you or anyone else it up for it.
Using the same table schema as above, I have the following data:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
Now I want to place an item in from 8:30 to 8:45 and I want my end
result to look like:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
I had to insert the new time in a current time and split the other time
to two parts and make it fit. Does make since?
Thanks
Richard Meetze|||David,
I think will should check for existence of rows between the matching ones.
Somethnig like:
...
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
Example:
create table UserAgenda (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL
)
insert into UserAgenda values('2005-02-23T08:30:00', '2005-02-23T08:35:00')
insert into UserAgenda values('2005-02-23T08:36:00', '2005-02-23T08:46:00')
insert into UserAgenda values('2005-02-23T08:47:00', '2005-02-23T08:57:00')
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
drop table UserAgenda
go
AMB
"David Portas" wrote:

> Am I correct that "next available" means a period of time that won't
> overlap the period between StartTime and EndTime on any row in the
> table? Try this:
> DECLARE @.m INTEGER
> SET @.m = 10
> SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
> FROM UserAgenda AS A
> LEFT JOIN UserAgenda AS B
> ON A.endtime <= B.starttime
> WHERE DATEDIFF(MI,A.endtime,
> COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
> --
> David Portas
> SQL Server MVP
> --
>|||See if it hepls you
create table UserAgenda
(
StartTime datetime not null,
EndTime datetime not null
)
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:00','20000610
10:15')
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:30','20000610
10:50')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:00','20000610
11:25')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:45','20000610
11:55')
insert into UserAgenda(StartTime,EndTime)values ('20000610 12:57','20000610
13:00')
insert into UserAgenda(StartTime,EndTime)values ('20000610 13:15','20000610
13:30')
insert into UserAgenda(StartTime,EndTime)values ('20000610 14:04','20000610
14:40')
SELECT
StartTime,
ISNULL(
(SELECT MIN(EndTime)
FROM UserAgenda AS S3
WHERE S3.StartTime >= S1.StartTime
AND ISNULL(
DATEDIFF(
minute,
S3.EndTime,
(SELECT MIN(StartTime)
FROM UserAgenda AS S4
WHERE S4.StartTime > S3.EndTime)), 15) <= 15),
EndTime) AS EndTime
FROM UserAgenda AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTime)
FROM UserAgenda AS S2
WHERE S2.EndTime < S1.StartTime),S1.StartTime),15)<= 15
<meetze@.gmail.com> wrote in message
news:1109184358.837536.30570@.f14g2000cwb.googlegroups.com...
> David - you are correct, thanks for the help.
> I have another question if you or anyone else it up for it.
> Using the same table schema as above, I have the following data:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> Now I want to place an item in from 8:30 to 8:45 and I want my end
> result to look like:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
> 2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
> 2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> I had to insert the new time in a current time and split the other time
> to two parts and make it fit. Does make since?
> Thanks
> Richard Meetze
>

Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.