still dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

still dates

Author

Topic

kemi2299
Starting Member

41 Posts

Posted - 2008-06-25 : 09:49:32

hi guys,

thanks for your solution, its works partly on the time, however I am still getting duplicate records, its not choosing only the maximum date. Instead i am getting 2 dates for a patient record.

max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:52:10

I'm sorry. Are we supposed to read your mind?

Or is this a continuation from this topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105501

Read and understand this blog post before posting next question.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 10:24:00

If you still are haveing the same issue, don't start a new topic.
It will fragment your answers and both you and we will have trouble with finding information.

E 12°55'05.25"
N 56°04'39.16"

kemi2299
Starting Member

41 Posts

Posted - 2008-06-25 : 10:39:22

sorry guys, I did not know, its still the same topic from the previous one

what i mean is that I want to choose the maximum date for a patient attendance in a month,
this statement below is still not choosing the maximum date only, its coming up with 2 dates for a patient attendance in a month.

max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 10:49:45

quote:
Originally posted by kemi2299

sorry guys, I did not know, its still the same topic from the previous one

what i mean is that I want to choose the maximum date for a patient attendance in a month,
this statement below is still not choosing the maximum date only, its coming up with 2 dates for a patient attendance in a month.

max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

Post the full query you used

Madhivanan

Failing to plan is Planning to fail

kemi2299
Starting Member

41 Posts

Posted - 2008-06-25 : 11:11:07

the full query is:

select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)
INTO #attempM0 FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP a
group by mrn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 13:36:48

quote:
Originally posted by kemi2299

the full query is:

select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)
INTO #attempM0 FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP a
group by mrn

where are you selecting patient records in this query? show us the full query used

kemi2299
Starting Member

41 Posts

Posted - 2008-06-26 : 04:42:43

the full query is: I am selecting from the table BLT_DATAWAREHOUSE.dbo.IM_IP_EP

select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 08:52:58

quote:
Originally posted by kemi2299

the full query is: I am selecting from the table BLT_DATAWAREHOUSE.dbo.IM_IP_EP

select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

please provide sample data and the result with the above query and illustrate what do you mean by "what i mean is that I want to choose the maximum date for a patient attendance in a month,
this statement below is still not choosing the maximum date only, its coming up with 2 dates for a patient attendance in a month"

KH
[spoiler]Time is always against us[/spoiler]

kemi2299
Starting Member

41 Posts

Posted - 2008-06-26 : 10:05:19

hi
this is the sample data, now the query its suppose to return only the latest date which the last one on the list, but instead its return 3 dates for the same patient thereby producing duplicate records.
now this is the qyery
select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

6585566 10-Jan-39 E11 4RL G8201490 16-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 17-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 10:17:19

[code]
SELECT mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)
FROM
(
select 6585566 as mrn, '10-Jan-39' as dates, '16-Jun-08' as Ep_St_Dt union all
select 6585566, '10-Jan-39', '17-Jun-08' union all
select 6585566, '10-Jan-39', '18-Jun-08'
)IM_IP_EP group by mrn [/code]

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 10:33:16

quote:
Originally posted by kemi2299

hi
this is the sample data, now the query its suppose to return only the latest date which the last one on the list, but instead its return 3 dates for the same patient thereby producing duplicate records.
now this is the qyery
select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

6585566 10-Jan-39 E11 4RL G8201490 16-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 17-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

You have to tell us which column is mrn, which is Ep_St_Dt ?

KH
[spoiler]Time is always against us[/spoiler]

kemi2299
Starting Member

41 Posts

Posted - 2008-06-26 : 11:17:08

mrn: 6585566

Ep_St_Dt : 16-Jun-08
I am suppose to be getting this record only which is the latest date, not the 3 records
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 11:26:10

quote:
Originally posted by kemi2299

mrn: 6585566

Ep_St_Dt : 16-Jun-08
I am suppose to be getting this record only which is the latest date, not the 3 records
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

Did you run the query I posted?

Madhivanan

Failing to plan is Planning to fail

kemi2299
Starting Member

41 Posts

Posted - 2008-06-26 : 11:40:00

I did not run the query, because the data was from the query i already run.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 11:47:20

quote:
Originally posted by kemi2299

I did not run the query, because the data was from the query i already run.

quote:
Originally posted by kemi2299

hi
this is the sample data, now the query its suppose to return only the latest date which the last one on the list, but instead its return 3 dates for the same patient thereby producing duplicate records.
now this is the qyery
select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

6585566 10-Jan-39 E11 4RL G8201490 16-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 17-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

then no way that data can come from this query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 11:49:08

We can't help you if you can't provide sufficient information.

We need this :
1. Your table DDL
2. sample data of your table
3. the FULL query that you used
4. the result that you want

KH
[spoiler]Time is always against us[/spoiler]

kemi2299
Starting Member

41 Posts

Posted - 2008-06-26 : 12:25:52

Your table DDL: BLT_DATAWAREHOUSE.dbo.IM_IP_EP
2. sample data of your table:
6585566 10-Jan-39 E11 4RL G8201490 16-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 17-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

3. the FULL query that you used:

select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

4. the result that you want:
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 12:51:23

quote:
Originally posted by kemi2299

Your table DDL: BLT_DATAWAREHOUSE.dbo.IM_IP_EP
2. sample data of your table:
6585566 10-Jan-39 E11 4RL G8201490 16-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 17-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

3. the FULL query that you used:

select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

4. the result that you want:
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

table DDL means not table name but table structure i.e the columns of table and also datatype of them at least.
Another thing is you're not telling the truth if you're saying that running
select
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn

yields this output

6585566 10-Jan-39 E11 4RL G8201490 16-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 17-Jun-08
6585566 10-Jan-39 E11 4RL G8201490 18-Jun-08

as you've specified only two fields in your select list and your o/p shows atleast 4 fields (not sure if middle string value is from one field or not. ANyways, you could try with this and see if this gives you desired output (fingers crossed)

select t1.*
FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP t1
INNER JOIN
(
select 
mrn, max_Ep_St_Dt = DATEADD(day, DATEDIFF(day, 0, max(Ep_St_Dt)), 0)

FROM BLT_DATAWAREHOUSE.dbo.IM_IP_EP group by mrn )t2
ON t2.mrn=t1.mrn
AND t2.max_Ep_St_Dt=DATEADD(day,DATEDIFF(day,0,t1.Ep_St_Dt),0)

and even if you didnt get what you desired from above query please keep in mind that its only because you havent given us enough information till now and have kept us guessing always. Also please realise two things while you post in future

1. The people trying to help you out here do not have access to your system,tables & data
2. The people trying to help you out here do not have neither ability to read your minds nor any magical powers

so please in future provide clearly description of what you want along with sample data & structure of tables and also output you desire. This will make both your and our jobs easy & you will get quick and prompt solution for your problem.

kemi2299
Starting Member

41 Posts

Posted - 2008-06-27 : 05:02:21

its a large query that contains some many field, I have only given you the only field affected, I am not suggesting otherwise, you guys are very helpful and very grateful as you have helped me in resolving so many ad-hoc queries, quite frankly happy that you are only on online away. Sorry it took time to understand me and will try this suggested solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources