Churn Rate - two different date dim''s

I am trying to create a calculated measure in my cube (MDX Exprsssion), but first I want to write the query in MDX so I can test it. I am having issues getting this to be "dynamic"

WITH

MEMBER Measures.[Disconnects] AS

(

(

[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],

[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]

),

[Measures].[Subscription Cancels]

)

MEMBER Measures.[Subscriptions] AS

(

(

[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],

[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]

),

[Measures].[Subscriptions New]

)

SELECT {

Measures.[Subscriptions]

,[Measures].[Disconnects]

} ON COLUMNS

FROM [MyCube]

this query above gives me the correct values for the day I have in there, but I want it to work over all days, and so the end users can slice by other dim's as well. Basically I am trying to get the number of disconnects that occured on the same day as that disconnect subscribed. I have a feeling my underlying cube/dw is too aggregated already or something else, but I can get this pretty close to what I want. Does anyone have any insight? I have been looking at this

http://sqljunkies.com/WebLog/mosha/archive/2007/06/01/count_in_flight_mdx.aspx

because it is similar, with the two different date dim's, but I just want to always get results where the two date dim's are the same day...

Thanks

If we just take the first measure (as both can use the same pattern) using link member should make the date range dynamic. I needed to pick one of the dates as the one that you will be analyzing by, so I assumed [Transaction Time] was probably the one.

eg.

MEMBER Measures.[Disconnects] AS

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

This should work OK when you are looking at specific days for Transaction Time, but if you wanted to look at the sum of all transactions where the cancellation and subscription occurred on the same date for a month, week, quarter etc. , assuming that you had a [Calendar] hierarchy then you would need to do something like the following in order to sum over a set of days.

MEMBER Measures.[Disconnects] AS

SUM(

DESCENDANTS([Transaction Time].[Calendar].CurrentMember,

[Transaction Time].[Calendar].[Calendar Date])

,

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

)

)

|||

wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy?

When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run

I think the solution above is totally the right track though, maybe I just need to tweak something more...

|||

dart_board wrote:

wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy?

When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run

In regards to the hierarchy, I used Calendar as an example as it is something that most people can understand. When I set up a date hierarchy, personally, I usually set up one (or more) hierarchies and then I usually hide the "raw" attributes that are present in the hierarchy. You don't have to do this, you can leave the attributes separate, but the important thing is to get your attribute relationships set up correctly, which it sounds like might be an issue in your case as this would lead to apparent duplicating values.

It's important the each attribute either has a direct or indirect (not both) relationship to the key attribute. And indirect relationship is where one attribute is related to another to form a sort of chain back to the key attribute. One way of thinking of relationships is that an attribute has a relationship to it's parent. So a TimeID has a relationship to a Date and a Date has a relationship to a month and so on. Notice that Year is at the top level of granularity so it is not related to any other attributes.

eg.

TimeID

- Date

Date

- Month

Month

- Quarter

Quarter

- Year

Year

If you don't create explicit hierarchies in your date dimension, or you want to leave the "raw" attributes visible, you should be able to replace the descendants function with the existing keyword which should reduce the set of Date members down to just those that are related to what ever Time attribute is currently selected. (it figures this out using attribute relationships, so these need to be right). Notice that I have used a naming convention of <dimension>.<Attribute>.<level>.members, this is important as if you just used <dimension>.<attribute>.members, it would include the default "All" member and would return all dates which is not what you want.

MEMBER Measures.[Disconnects] AS

SUM(

EXISTING [Transaction Time].[Date].[Date].Members)

,

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

)

)

Hope this helps

|||yeah that totally makes sense. I brought up a while ago that our hierarchies weren't set up correctly, but no one would beleive me - The EXISTING seems to work well. My only issue now is that when I try to use BOTH caclulated measures together, one is summed up over all and one is correct, depending on which date I slice on (transaction or new subscription) Its like I can get either one or the other to work, but not both together.

I will keep digging into that, if you have any quick insight that would be awesome. You really know your stuff

Choosing one record from many

I have a table that lists user ID's and their last login times, and most of the records have duplicates with the only difference being the date field showing the last login time. How can I retrieve only the most current record for each ID from this table?

For example, I have ID's ABC and XYZ. Both are listed in the table 6 times each, but I want a resultant table of only 2 records, one for ABC and one for XYZ, and each of these records is the one with the most current (latest)date.

I am using CR 8.5

ThanksWhy not do this in your SQL query, Select Distinct(yourId) from...|||Note: You cannot change the SELECT clause of the SQL statement.

This note is from Crystal Reports Online Help. It seems that when I open the "Show SQL Query", I can edit anything else but the SELECT clause. So it seems that I can't use the DISTINCT from here.

Any other help please!!!

CHILD functions?

How can I pick out a "top child" in SQL Server 2000?

Example:

----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000001 || DDD || 01/02/2001 |
| 00000001 || CCC || 01/06/2000 |
| 00000002 || AAA || 01/02/2006 |
| 00000002 || CCC || 01/10/2004 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
| 00000004 || SSS || 09/06/2000 |
----------

I want to pick out the record with the latest date for every employee so I end up with something like this:

----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000002 || AAA || 01/02/2006 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
----------

Any ideas?
Thanks in advance!

-GeorgeV??:
SELECT Employee, code, MAX([action date])
FROM YourTable
GROUP BY Employee, code|||Hmm, nice idea but it doesn't want to work.

Any more suggestions?

-GeorgeV|||SELECT Employee, code, [action date]
FROM YourTable YT
WHERE [action date] = (SELECT MAX([action date])
FROM YourTable
WHERE Employee = YT.Employee)

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?
Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checking values with range parameters

How can i match a value in my query with a parameter that has range e.g:
i want to check where order_date between {?Datapara}
where date parameter is a single field that has range.I have CR XI, can not swear this is supported in earlier releases.
Go to the sample reports and open the Record selection on date range.
look at the select statement and check out the report custom formulas.
You may be able to do this same thing in earlier versions, but I do not know that for certain. Only have CR XI

Checking the datatype of a columns

Hello,

I have the following sql statement

Code Snippet

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

If you're intention is to ultimately convert the datatype, what's preventing you from initially setting the datatype as datetime?

I know this doesn't answer your question, but I'm curious.

Adamus

|||Hello,

The table was created initially with a nvarchar. This is a live database and the dates that have already been entered have to be formated in order for the alter column will work.

If the customer runs this script more than once, I don't want to have to execute the alter statement again.

Many thanks,

Steve
|||

There's really not a problem if that statement executes EVEN if the datatype has been previously changed.

However, if you need to:

Code Snippet

IF NOT EXISTS
( SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME = 'OtherCall'
AND COLUMN_NAME = 'Date'
AND DATA_TYPE = 'datetime'
)
)
BEGIN
UPDATE OtherCall

SET [Date] = convert( nvarchar(50), convert( datetime, [Date], 103), 111)

ALTER TABLE OtherCall ALTER COLUMN [Date] datetime

END

|||

Try the below SQL Statement, this is my version of solution there may be other ways to

' Returns row if the column is already converted to the required data type

IF EXISTS (select c.name, c.xtype from sysobjects o, syscolumns c where o.id = c.id and o.name = tablename and c.xtype= 61(xtype value of datatime datatype, can be found in systypes table in master database) and c.name =column name)
BEGIN
// The column is already converted to datatime format. In your case this step will be blank
END
ELSE

BEGIN

// The column is not in datatime datatype so we need to convert it. Below is your code for conversion

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

END

checking if a particular DATE LIES IN CURRENT QUARTER

I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
Thanks
Use a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>

checking if a particular DATE LIES IN CURRENT QUARTER

I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name
= "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name =
"SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>

checking if a particular DATE LIES IN CURRENT QUARTER

I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>

Checking Format of a string in Report Parameter

Is there a way to find out if the user entered the date in correct format for the report parameter? For example, I am using string data type and requring the user to enter date in yyyy/mm/dd format. Can I capture and prompt the user accordingly if the date string is not provided in the above format?

Why dont you create the parameter in a Datetime format and let the user enter it in a date time format. After that you use the functions to convert itto a string in your desired format.

|||

You might be able to take advantage of the isDate() function; however, at the moment I don't see how to apply it.

Checking for NULL dates in Report

Hi,
I have a date field in my report dataset. I want to check for NULL date
before displaying it in the report. I have tried using the exp
=IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
But this doesnt work. I see #error in my report where ever date value is
NULL or blank. I have also tried using Len and IsDate functions but they too
dont seem to work. Can anyone help me ?
Thanks,
RKHi,
Tue and False part are evaluated in a IIF function. That's why you have an
#error.
You can use Visibility property.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a date field in my report dataset. I want to check for NULL date
> before displaying it in the report. I have tried using the exp
> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
> But this doesnt work. I see #error in my report where ever date value is
> NULL or blank. I have also tried using Len and IsDate functions but they
> too dont seem to work. Can anyone help me ?
> Thanks,
> RK
>
>
>|||Hi,
Thanks for the reply. I want to display the date only when its not null. So
I have used the IIF expression. But the checking using the function
IsNothing or IsDate doesnt seem to work.
--
RK
"Jean-Pierre Riehl" <jean-pierre.riehl@.bewise.fr> wrote in message
news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
> Hi,
> Tue and False part are evaluated in a IIF function. That's why you have an
> #error.
> You can use Visibility property.
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
>
> "ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
> news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a date field in my report dataset. I want to check for NULL date
>> before displaying it in the report. I have tried using the exp
>> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
>> But this doesnt work. I see #error in my report where ever date value is
>> NULL or blank. I have also tried using Len and IsDate functions but they
>> too dont seem to work. Can anyone help me ?
>> Thanks,
>> RK
>>
>>
>|||On Feb 5, 11:49=A0pm, "ramakrishna" <ramakris...@.translogicsys.com>
wrote:
> Hi,
> Thanks for the reply. I want to display the date only when its not null. S=o
> I have used the IIF expression. But the checking using the function
> IsNothing or IsDate doesnt seem to work.
> --
> RK"Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr> wrote in message
> news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
>
> > Hi,
> > Tue and False part are evaluated in a IIF function. That's why you have =an
> > #error.
> > You can use Visibility property.
> > --
> > Jean-Pierre Riehl
> >http://blog.djeepy1.net
> >http://www.bewise.fr
> > "ramakrishna" <ramakris...@.translogicsys.com> wrote in message
> >news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> >> Hi,
> >> I have a date field in my report dataset. I want to check for NULL date=
> >> before displaying it in the report. I have tried using the exp
> >> =3DIIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day=(Field=ADs!Date.Value)&"/"&Year(Fields!Date.Value)).
> >> But this doesnt work. I see #error in my report where ever date value i=s
> >> NULL or blank. I have also tried using Len and IsDate functions but the=y
> >> too dont seem to work. Can anyone help me ?
> >> Thanks,
> >> RK- Hide quoted text -
> - Show quoted text -
Any value that returns "NULL" from your database is treated like a
"Nothing" object in .Net. Wherever possible, you will want to use the
IsNothing function. If I recall, Len(Nothing) returns Nothing, not 0
Second, the Month, Day and Year functions expect a Date object. It is
possible that you are passing them a Text fieldand it doesn't know how
to parse it. This could be why the function is returning a #error.
Third, when doing Date formatting, you should probably use the Format
function instead of breaking the date apart.
=3DIIF( IsNothing(Fields!Date.Value), "", Format( CDate(Fields!
Date.Value), "M/d/yyyy" ) )
-- Scott

checking dates with triggers or constraints

Hi,

I have a table that contains various data with a start and end date. When a user enters the information I need to check that the end date is older than the start date.

What is the easiest way to do this - using a trigger?

And is the code below correct ?

CREATE TRIGGER checkdate
ON Prodn_Problem
FOR INSERT,UPDATE
AS
DECLARE @.start_date datetime
SELECT @.start_date = (SELECT startdate FROM inserted)
DECLARE @.end_date datetime
SELECT @.end_date = (SELECT enddate FROM inserted)
BEGIN
IF(@.end_date < @.start_date)
ROLLBACK
END

Thanks.By using a trigger, you check the data when it's already inserted or updated in the table. When you use a check constraint you can verify before the information is entered. Another method is to create a stored procedure which does the checking an let you users enter information through this stored procedure.

Checking Date column within the same table - DDL included

Hi all,
I have included All sample data, just copy and paste in Query Analizer
I would like to modify the query below to return all that had a prior
"pretest" 30 days prior or equal to
the "test". so the return result that I would like would look like this:
1 A 1997/12/08 pretest
1 A 1997/12/09 test
1 A 1997/12/11 test
3 C 1997/12/18 pretest
3 C 1997/12/19 test
4 D 1997/12/15 pretest
4 D 1997/12/16 test
5 E 1997/12/17 test
5 E 1997/12/17 pretest
6 F 1998/08/03 pretest
6 F 1998/08/04 test
6 F 1998/08/18 test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'AppointmentTable')
Begin
DROP TABLE AppointmentTable
End
CREATE TABLE [AppointmentTable] (
[tableid] [int] IDENTITY (1, 1) NOT NULL ,
[nameid] [numeric](18, 0) NOT NULL ,
[fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appointmentdate] [datetime] NOT NULL ,
[type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/08', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/09', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/11', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '2003/05/07', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '2003/05/08', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('2','B', '1997/12/12', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('2','B', ' 1998/02/24', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('3','C', '1997/12/18', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('3','C', '1997/12/19', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('4','D', '1997/12/15', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('4','D', '1997/12/16', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('5','E', '1997/12/17', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('5','E', '1997/12/17', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/03', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/04', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/18', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1999/07/07', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1999/08/31', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '2001/05/15', 'test')
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment Date], type as [Test
Type]
FROM AppointmentTable
ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type]DESC
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'AppointmentTable')
Begin
DROP TABLE AppointmentTable
End
Thanks for your help
GerryThis works using a UNION!! would there be a better way to write it?
Select u1.nameid as [nameid],u1.fullname as [Full Name], u1.appointmentdate
as [Exam Date], u1.type as [Procedure Type]
from appointmenttable u1
where u1.type = 'pretest'
and exists (SELECT * from appointmenttable u2
where u1.nameid = u2.nameid
and u2.type = 'test'
and u1.appointmentdate <= u2.appointmentdate
and Datediff(day,u1.appointmentdate ,u2.appointmentdate)
<= 30)
UNION
Select u2.nameid as [nameid],u2.fullname as [Full Name], u2.appointmentdate
as [Exam Date], u2.type as [Procedure Type]
from appointmenttable u2
where u2.type = 'test'
and exists (SELECT * from appointmenttable u1
where u1.nameid = u2.nameid
and u1.type = 'pretest'
and u1.appointmentdate <= u2.appointmentdate
and Datediff(day,u1.appointmentdate ,u2.appointmentdate)
<= 30)
ORDER BY [nameid],[Full Name],[Exam Date],[Procedure Type]DESC
thanks
Gerry
"gv" <viatorg@.musc.edu> wrote in message
news:OgIrxTr7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have included All sample data, just copy and paste in Query Analizer
> I would like to modify the query below to return all that had a prior
> "pretest" 30 days prior or equal to
> the "test". so the return result that I would like would look like this:
> 1 A 1997/12/08 pretest
> 1 A 1997/12/09 test
> 1 A 1997/12/11 test
> 3 C 1997/12/18 pretest
> 3 C 1997/12/19 test
> 4 D 1997/12/15 pretest
> 4 D 1997/12/16 test
> 5 E 1997/12/17 test
> 5 E 1997/12/17 pretest
> 6 F 1998/08/03 pretest
> 6 F 1998/08/04 test
> 6 F 1998/08/18 test
>
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
> 'AppointmentTable')
> Begin
> DROP TABLE AppointmentTable
> End
> CREATE TABLE [AppointmentTable] (
> [tableid] [int] IDENTITY (1, 1) NOT NULL ,
> [nameid] [numeric](18, 0) NOT NULL ,
> [fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [appointmentdate] [datetime] NOT NULL ,
> [type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/08', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/09', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/11', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '2003/05/07', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '2003/05/08', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('2','B', '1997/12/12', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('2','B', ' 1998/02/24', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('3','C', '1997/12/18', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('3','C', '1997/12/19', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('4','D', '1997/12/15', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('4','D', '1997/12/16', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('5','E', '1997/12/17', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('5','E', '1997/12/17', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/03', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/04', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/18', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1999/07/07', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1999/08/31', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '2001/05/15', 'test')
>
> SELECT nameid as [Name ID],fullname as [Full Name],
> CONVERT(varchar,appointmentdate, 111) as [Appointment Date], type as [Test
> Type]
> FROM AppointmentTable
> ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type]DESC
>
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
> 'AppointmentTable')
> Begin
> DROP TABLE AppointmentTable
> End
> Thanks for your help
> Gerry
>
>
>
>
>
>
>
>
>
>
>|||See if this helps:
select aouter.*
from #AppointmentTable aouter
where exists
(select * from #AppointmentTable
where aouter.nameid = nameid
and ((aouter.type = 'test'
and type = 'pretest'
and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
or (aouter.type = 'pretest'
and type = 'test'
and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
)
"gv" wrote:

> This works using a UNION!! would there be a better way to write it?
> Select u1.nameid as [nameid],u1.fullname as [Full Name], u1.appointmentdate
> as [Exam Date], u1.type as [Procedure Type]
> from appointmenttable u1
> where u1.type = 'pretest'
> and exists (SELECT * from appointmenttable u2
> where u1.nameid = u2.nameid
> and u2.type = 'test'
> and u1.appointmentdate <= u2.appointmentdate
> and Datediff(day,u1.appointmentdate ,u2.appointmentdat
e)
> <= 30)
> UNION
> Select u2.nameid as [nameid],u2.fullname as [Full Name], u2.appointmentdate
> as [Exam Date], u2.type as [Procedure Type]
> from appointmenttable u2
> where u2.type = 'test'
> and exists (SELECT * from appointmenttable u1
> where u1.nameid = u2.nameid
> and u1.type = 'pretest'
> and u1.appointmentdate <= u2.appointmentdate
> and Datediff(day,u1.appointmentdate ,u2.appointmentdat
e)
> <= 30)
> ORDER BY [nameid],[Full Name],[Exam Date],[Procedure Type]DESC
> thanks
> Gerry
>
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:OgIrxTr7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
>|||THANKS!!! BIG HELP!!!!
Gerry
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:0EF60332-ADE8-4C4D-9481-A1E8FB733234@.microsoft.com...
> See if this helps:
> select aouter.*
> from #AppointmentTable aouter
> where exists
> (select * from #AppointmentTable
> where aouter.nameid = nameid
> and ((aouter.type = 'test'
> and type = 'pretest'
> and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
> or (aouter.type = 'pretest'
> and type = 'test'
> and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
> )
> "gv" wrote:
>|||thanks again for your help
What if I wanted to only return the latest 65 distinct nameid based on
appointmentdate?
thanks
Gerry
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:0EF60332-ADE8-4C4D-9481-A1E8FB733234@.microsoft.com...
> See if this helps:
> select aouter.*
> from #AppointmentTable aouter
> where exists
> (select * from #AppointmentTable
> where aouter.nameid = nameid
> and ((aouter.type = 'test'
> and type = 'pretest'
> and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
> or (aouter.type = 'pretest'
> and type = 'test'
> and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
> )
> "gv" wrote:
>|||Hopefully this will help u:
select top 65 a.nameid
from #AppointmentTable a
where exists
(select * from #AppointmentTable b
where a.nameid = b.nameid and a.type <> b.type
and
((a.type = 'pretest' and datediff(d,a.appointmentdate,b.appointmentdate)
between 0 and 30)
or
(a.type = 'test' and datediff(d,b.appointmentdate,a.appointmentdate) between
0 and 30))
)
group by a.nameid
order by max(a.appointmentdate) desc
"gv" wrote:

> thanks again for your help
> What if I wanted to only return the latest 65 distinct nameid based on
> appointmentdate?
> thanks
> Gerry|||Thank you so much for your help!!!
Very Close!
The 65 needs to be Distinct from column namid so there could actually be
over 130 rows returned but only 65 different
nameid and within each nameid the pretest needs to be listed first. But
within each different nameid
you have it right where the order of Max appointmentdate is coming first.
Thanks again for your help Absar
Gerry :< )
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:74B787F2-88D4-4120-858B-1F1B8FA8B34A@.microsoft.com...
> Hopefully this will help u:
> select top 65 a.nameid
> from #AppointmentTable a
> where exists
> (select * from #AppointmentTable b
> where a.nameid = b.nameid and a.type <> b.type
> and
> ((a.type = 'pretest' and datediff(d,a.appointmentdate,b.appointmentdate)
> between 0 and 30)
> or
> (a.type = 'test' and datediff(d,b.appointmentdate,a.appointmentdate)
> between
> 0 and 30))
> )
> group by a.nameid
> order by max(a.appointmentdate) desc
> "gv" wrote:
>
>|||On Wed, 23 Nov 2005 14:29:56 -0500, gv wrote:

>Thank you so much for your help!!!
>Very Close!
>The 65 needs to be Distinct from column namid so there could actually be
>over 130 rows returned but only 65 different
>nameid and within each nameid the pretest needs to be listed first. But
>within each different nameid
>you have it right where the order of Max appointmentdate is coming first.
>Thanks again for your help Absar
>Gerry :< )
Hi Gerry,
Maybe this is what you need?
(Note - I also changed the way to determine the date range in order to
have a better chance of using an index -if there is any- on the
appointmentdate column).
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment
Date],
type as [Test Type]
FROM AppointmentTable AS a
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS b
WHERE b.nameid = a.nameid
AND b.type <> a.type
AND b.appointmentdate BETWEEN CASE WHEN a.type = 'test'
THEN DATEADD (day, -30,
a.appointmentdate)
ELSE a.appointmentdate
END
AND CASE WHEN a.type = 'test'
THEN a.appointmentdate
ELSE DATEADD (day, 30,
a.appointmentdate)
END)
AND a.nameid IN
(SELECT TOP 65 c.nameid
FROM AppointmentTable AS c
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS d
WHERE d.nameid = c.nameid
AND d.type <> c.type
AND d.appointmentdate BETWEEN CASE WHEN c.type = 'test'
THEN DATEADD (day, -30,
c.appointmentdate)
ELSE c.appointmentdate
END
AND CASE WHEN c.type = 'test'
THEN c.appointmentdate
ELSE DATEADD (day, 30,
c.appointmentdate)
END)
GROUP BY c.nameid
ORDER BY MAX(appointmentdate))
ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type] DESC
I've tested this against the test data you supplied (thanks for that, by
the way!). If I change the TOP 65 to TOP 3, I only get the results for
nameid 1, 4, and 5.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much for your help!
Almost got it.
I've added some more test data and included your query. Doesn't return the c
orrect top 6 based on appointmentdate. And
within each id the appointmentdate should be ordered by earlist date first b
ut it is correct to order the latest date of the id first.
sorry if I was not clear enough before. I would also like it to only return
those that had "checked"
at least once next to "test" within a group. See all sample data below tha
nks again Gerry
Returns this:
7 G 2005/07/07 test checked
7 G 2005/07/06 pretest -
8 H 2005/06/24 test checked
8 H 2005/06/22 test checked
8 H 2005/06/19 pretest -
9 I 2005/05/22 test
9 I 2005/05/20 pretest -
10 J 2005/04/10 test checked
10 J 2005/04/05 pretest -
11 K 2005/03/16 test checked
11 K 2005/03/14 pretest -
7 G 2004/05/05 test
7 G 2004/05/04 pretest -
7 G 2002/09/25 test
7 G 2002/09/04 pretest -
6 F 1998/08/18 test
6 F 1998/08/04 test checked
6 F 1998/08/03 pretest -
7 G 1992/07/30 test
7 G 1992/07/08 pretest -
should return this and in this order:
7 G 2005/07/06 pretest -
7 G 2005/07/07 test checked
8 H 2005/06/19 pretest -
8 H 2005/06/22 test checked
8 H 2005/06/24 test
9 I 2005/05/20 pretest -
9 I 2005/05/22 test checked
10 J 2005/04/05 pretest -
10 J 2005/04/10 test checked
11 K 2005/03/14 pretest -
11 K 2005/03/16 test checked
7 G 2004/05/04 pretest -
7 G 2004/05/05 test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Appo
intmentTable')
Begin
DROP TABLE AppointmentTable
End
CREATE TABLE [AppointmentTable] (
[tableid] [int] IDENTITY (1, 1) NOT NULL ,
[nameid] [numeric](18, 0) NOT NULL ,
[fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appointmentdate] [datetime] NOT NULL ,
[type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/09', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/11', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '2003/05/07', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '2003/05/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('2','B', '1997/12/12', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('2','B', ' 1998/02/24', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('3','C', '1997/12/18', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('3','C', '1997/12/19', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('4','D', '1997/12/15', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('4','D', '1997/12/16', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('5','E', '1997/12/17', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('5','E', '1997/12/17', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/03', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/04', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/18', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1999/07/07', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1999/08/31', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '2001/05/15', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2005/07/06', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2005/07/07', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2004/05/04', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2004/05/05', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2002/09/04', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2002/09/25', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '1992/07/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '1992/07/30', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/19', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/22', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/24', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('9','I', '2005/05/20', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('9','I', '2005/05/22', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('10','J', '2005/04/05', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('10','J', '2005/04/10', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('11','K', '2005/03/14', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('11','K', '2005/03/16', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('12','L', '2005/02/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('12','L', '2005/03/11', 'test','checked')
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment Date],
type as [Test Type],status
FROM AppointmentTable AS a
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS b
WHERE b.nameid = a.nameid
AND b.type <> a.type
AND b.appointmentdate BETWEEN CASE WHEN a.type = 'test'
THEN DATEADD (day, -30,a.appointmentdate)
ELSE a.appointmentdate
END
AND CASE WHEN a.type = 'test'
THEN a.appointmentdate
ELSE DATEADD (day, 30,a.appointmentdate)
END)
AND a.nameid IN
(SELECT TOP 6 c.nameid
FROM AppointmentTable AS c
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS d
WHERE d.nameid = c.nameid
AND d.type <> c.type
AND d.appointmentdate BETWEEN CASE WHEN c.type = 'test'
THEN DATEADD (day, -30,c.appointmentdate)
ELSE c.appointmentdate
END
AND CASE WHEN c.type = 'test'
THEN c.appointmentdate
ELSE DATEADD (day, 30,c.appointmentdate)
END)
GROUP BY c.nameid
ORDER BY MAX(appointmentdate)DESC )
ORDER BY [Appointment Date]desc
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Appo
intmentTable')
Begin
DROP TABLE AppointmentTable
End|||On Mon, 28 Nov 2005 15:06:38 -0500, gv wrote:

>Thanks so much for your help!
>Almost got it.
>I've added some more test data and included your query. Doesn't return the
correct top 6 based on appointmentdate. And
>within each id the appointmentdate should be ordered by earlist date first
but it is correct to order the latest date of the id first.
>sorry if I was not clear enough before. I would also like it to only return
those that had "checked"
>at least once next to "test" within a group. See all sample data below thanks
again Gerry
Hi Gerry,
Thanks for posting CREATE TABLE and INSERT statements. Helps a lot!
Before I start writing a query, let me clarify what I think you want to
get from your data. Correct me if I'm wrong.
- You need to find "pretest" with a "test" for the same nameid in the
next 30 days.
- Of those, you only wwant to select the 6 (or 65) most recent rows.
- The "pretest" rows should be returned in the order of most recent row
first.
- But the "test" rows should FOLLOW the accompanying "pretest" row, and
should be presented oldest first.
Finally, in the required output you have given, you have changed the
contents of these rows:

>8 H 2005/06/24 test checked
>8 H 2005/06/22 test checked
to this:

>8 H 2005/06/22 test checked
>8 H 2005/06/24 test
Why did you omit "checked" on this row? Why didn't you omit it on any
other rows? I don't understand that part of your requirement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Checking contents of column and replacing contents if first character is a letter

Hi All,

I have come up against a wall which i cannot get over.

I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.

I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.

I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.

Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.

This is how far i have got: casting the varchar column to datetime and comparing.

SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"

Many thanks in advance!

You could adapt your query as follows:

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 0001'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

However, solution two is to add a new column called "Start_DateTime" and populate it with genuine dates. You can then phase out "Start_Date" entirely. It's always easier to data-clean sooner rather than later!

Also, read up on dynamic SQL and SQL injection when you get a minute.

|||

Hi Sohnee,

Thanks for the quick reply!

I've tested your query and I think it is still trying to cast the 'various' entrants as a date, as it returns the following:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Also, how would compare the date selected to the dates in db as i'm unsure of where to place the '>'.

I'll take you advice and start reading!

Thanks again!

|||

Can anybody else help at all?? PLEASEEEEE!!

Ta!

|||

SELECT *FROM table1WHERE CASE WHEN ISDATE(Cast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime))=1 THENCast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime)ELSENULLEND='" + date + "'"

|||

Apologies - in my example I used the minimum date from .NET - which is a bit early for SQL.

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 1900'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

|||

Hi All,

Thanks for all the responses...unfortunately the suggested solutions aren't doing the trick.

I think its still trying convert the varchar 'various' to a date format even when you've asked it set 'various' as '01/01/2010'

Oh well!

|||

Run this query to see if you get any clues back...

SELECT DISTINCT
Start_Date,
ISDATE(Start_Date),
CASE Start_Date
WHEN 'Various' THEN 'Text'
ELSE 'Date'
END = '" + date + "'
FROM
table1

My guess is that you will find other values (as well as 'Various') that aren't dates. - Problem areas will be lines that think they are dates, but that aren't.

Check to see if a row exists in another table before insert

I have a stored procedure that selects invoices based on the date range

delete from BillingCurrent

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

delete from Billing30

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing3

Now, I need to check to see if the row exists in Billing30, if it exists in Billing 30 then I don't want it to insert into BillingCurrent.

You can use something along the lines of the code listed below.

Chris

IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is found

END

ELSE

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is not found

END

|||

Ok I understand that and it's very helpful but,

for

IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is found

I want it to do Nothing if the row exists but I don't want it to exit because I need to do this for 5 tables

END

|||

There's nothing to stop you using multiple IF statements or even nesting them if you desire, see below - note that I've reversed the logic.

Chris

IF NOT EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is not found

END

IF NOT EXISTS (SELECT 1 FROM Table2 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Table2 record is not found

END

IF NOT EXISTS (SELECT 1 FROM Table3 WHERE <insert criteria>)

etc....

|||

ok so I can nest all of the if not exists and then after those just

if exists

end

to make it do nothing if the row already exists?

|||

In my previous example none of the code within the BEGIN END blocks will execute if at least one row meeting the relevant criteria exists in each of the tables that you are checking. There's no need to add any additional code to make SQL Server do nothing - if a condition fails then the code within the associated BEGIN END block will not be executed, it's as simple as that. If all of the conditions fail then the batch will complete without executing any of the code within any of the BEGIN END blocks.

It isn't clear from the description of your scenario whether you will need to use nested or multiple IF statements so I can't help any further in that respect without more info.

Chris

|||

Ok, Heres my SP

This prints out (because I use a relation from the billing tables to the InvoiceDetails Table) A Billing statement for each customer, problem is: if a customer has an invoice this month and last month then if prints out two invoices. I'm trying to get it to check each table first billing120 then billing90 then billing60..... So the customer row only gets inserted once.

delete from Billing120

insert into Billing120(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120

delete from Billing90

IF NOT EXISTS (SELECT CustomerID FROM Billing120)--WHERE <insert criteria>)

BEGIN

insert into Billing90(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90

END

delete from Billing60

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into Billing60(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60

End

delete from Billing30

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30

END

delete from BillingCurrent

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

END

RETURN

|||

Maybe you should try a different approach then, see below. This approach allows you to analyze the contents of the tables before performing any INSERTs etc...

Chris

DECLARE @.Billing120Exists BIT

DECLARE @.Billing90Exists BIT

DECLARE @.Billing60Exists BIT

DECLARE @.Billing30Exists BIT

SET @.Billing120Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing120) THEN 1 ELSE 0 END

SET @.Billing90Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing90) THEN 1 ELSE 0 END

SET @.Billing60Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing60) THEN 1 ELSE 0 END

SET @.Billing30Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing30) THEN 1 ELSE 0 END

--Insert logic here that examines the values of the @.BillingExists variables and performs the appropriate actions.

--If you want you can declare additional variables to indicate whether or not rows have subsequently been inserted into one of the tables.

|||

Actually I ended up doing it this way,

delete from Billing120

insert into Billing120(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120

delete from Billing90

BEGIN

insert into Billing90(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90

END

delete from Billing60

BEGIN

insert into Billing60(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60

End

delete from Billing30

BEGIN

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30

END

delete from BillingCurrent

BEGIN

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)

And not exists (select 1 from billing30 e where E.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

END

RETURN

Thanks for the Help!