Thanks for replies.

i will always want to select previous 52 prior to the previous sunday in the current week.

thanks

Many Thanks, Works a treat.

Hi

Thanks for the reply. It works but assigns the same value to every row. I think now i have my query structure wrong.

Below is a sample data table script...

Cheers I'll take a look tomoroow and let you know how i get on when using the main data set.]

Thanks again for your help.

Thanks for your help,

the reason that on the 7th WC would count as 1 is because patient 5678 was on ward WC on the 6th , and 7th and then...

Right I have the expected results here, please ignore the ones in the original post.

The results should look as follows

DATE WARD...

Hi,

Basically I mucked up a bit with the expected results. I will try and re-do and re-post expected results

Patient 5678 had 3 different wards. They started on WC (position 3)...

Sorry my mistake.

As far as Ward = BB goes there shouuld only be patient CRN = "8888" in for one night on the 17th Jan and Patient "7777" in...

Hi

Just to let you know i went with the linked access table solution as it works fine and also the maintenance if the data is done by the user...

OK thanks, I see what the Over is doing by counttng the rows depending on the grouping. This is fine.

Further to that I would like to also have an aditional...

They are, I'm trying to achive the results in my .txt file in one query.

The first part of my query is the bit i'm stuck with. It give sme the...

No, it's an addition. trying to work through this in stages. This is the last stage.

Sorry for the confusion.

Many Thanks for your reply

I have already added in your suggestion to the original query and it works fine.

I now need to add in the total numner of...

Many thanks for your speedy reply. Works fine.

The next think i was trying to achieve is to gain another column which has the total for each grouping period based on...

Hi,

Thanks for your reply and apologies for my poor exectution of this post.

Please find attached the Create table script below.

---------------------------------------------------------------------------------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[sample_data_for_SQL_Central](

[arrival] [datetime] NULL,

[atd_id] [float]...