SQL Removing and merging results

[jase2006]

The query below suppose to find out some project info (resource id and project id etc) and work out the "last assignment date". However, it creates a problem when there are is a month change during the week e.g Jan 07 finishes on Wednesday and Feb 07 starts on Thursday. It should only give one "last assignment date" for one "project id" with each "WeekCommencing". Since the week got split into two months. It is giving two "last assignment date" with the same "project id" with each "WeekCommencing". one for Jan and one for Feb. Can anyone suggest a way I can remove the earlier (Jan) one and keep the latest one (Feb)? Any help will be appreciate.

The Query
****************************************

SELECT X.RES_ID, X.PROJ_ID, X.WeekCommencing, [WeekCommencing]+(Right(IIf([monday]>0,0) & IIf([tuesday]>0,1) & IIf([wednesday]>0,2) & IIf([thursday]>0,3) & IIf([friday]>0,4) & IIf([saturday]>0,5) & IIf([sunday]>0,6),1)) AS LastAssignedDate
FROM X INNER JOIN Y ON (X.WeekCommencing = Y.WeekCommencing) AND (X.PROJ_ID = Y.PROJ_ID) AND (X.RES_ID = Y.RES_ID);

****************************************

 

[r937]

to help you with your problem, we would need to understand your data

could you please show sample rows of data, illustrating your problem

r937.com | rudy.ca

 

[jase2006]

Sure here it is:

RES_ID PROJ_ID WeekCommencing monday tuesday
katera N/0001 31/12/2007 1 0
katera N/0013 05/02/2007 0 1
katera P/0061 26/02/2007 1 1
katera P/0061 26/02/2007 0 0
katera P/0061 23/04/2007 1 1

...wednesday thursday friday saturday sunday
... 0 0 0 0 0
... 1 1 1 0 0
... 1 0 0 0 0
... 0 1 1 0 0
... 1 1 1 0 0


I have to break it up into two because due to length limit. Problem arise at line 3+4

 

[r937]

okay, thanks, that data looks fine, but i still don't understand what it means

could you please explain what it's for, and how it works?

what is the primary key for this table?

and why are there two rows for katera P/0061 26/02/2007

r937.com | rudy.ca

 

[jase2006]

This is not really a table it is data from another query. The data above shows a member of staff (RES_ID) assign to a particular project (PROJ_ID), the start date (WeekCommencing) and for each day worked (mon - sun) a 1 is entered. And the two rows for katera P/0061 26/02/2007 where the month change from Feb to March, but it's still the same project and start date

 

[r937]

then why not fix the other query instead of this one?

r937.com | rudy.ca

 

[jase2006]

That's the way they want the data to be layed out in that query and I was told to keep it that way. Any way round this?

 

[r937]

could you kindly explain what the expression with all those IIFs is supposed to be doing

there's a + operator being used, as well as a & operator

r937.com | rudy.ca

 

[jase2006]

I've got the problem solved now. I've manage to talk them through to changing the other query. It just some simple errors. Thanks for pointing it out r937!