Select into temp table query

Last Post 09 Jul 2010 03:04 AM by Andrew Anthony. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
Andrew Anthony
New Member
New Member

--
07 Jul 2010 11:37 AM
I have been struggling with this problem all day, I am nearly there but can't quite get the desired results: I have a table which contains amongst others, some field names called ID, DatePaid and WeekUID. I have created a temporary table that contains just the ID and DatePaid fields. My main table structure (showing only the fields that concern this query) looks like the following sample: ID DatePaid WeekUID ------------------------------------- 0132 01/04/2010 1 0342 01/04/2010 1 0233 02/04/2010 1 0231 07/04/2010 2 0237 08/04/2010 2 Basically, what I need into my temp table is the last entry for each 'week number' for the entire year. My query is as follows: insert into #TempTable (ID, DatePaid) select ID, DatePaid from tblAccounts (nolock) where DatePaid = (select max(DatePaid) from tblAccounts (nolock) as A where A.WeekUID = tblAccounts.WeekUID) this works except for the fact it inserts (using the above sample as way of demonstration) duplicate entries for a specific date, in this case 01/04/2010 would appear in the temporary table twice (or however many entries there were in the original table). So what I need is either: 1) A way to delete the duplicate 'DatePaid' figures leaving just one DatePaid and one ID (preferably the highest ID) per WeekUID. or 2) A way of selecting both the Max(ID) and the Max(DatePaid) when I use my INSERT query to push the data into the Temporary table. As I said, I have been searching for hours, tried a few techniques and none have worked as expected (or indeed at all!). Any suggestions welcome. Thanks.
Andrew Anthony
New Member
New Member

--
07 Jul 2010 11:40 AM
Apologies - the forum has lost all the formatting I entered, I have re-printed my sample table structure:

ID DatePaid WeekUID
-----------------------------------------
0132 01/04/2010 1
0342 01/04/2010 1
0233 02/04/2010 1
0231 07/04/2010 2
0237 08/04/2010 2
gunneyk
New Member
New Member

--
08 Jul 2010 04:28 PM
What good is the ID in the results? If you have 3 rows where the date is all the same which ID do you want? So in othere words what purpose does the ID serve in the temp table and how do you pick the right one? You might be able to use a TOP 1 with an order by on the ID if you want the largest or smallest ID for instance.
Andrew Anthony
New Member
New Member

--
09 Jul 2010 02:50 AM
To cut a long story short, the ID is basically a unique ID for the payment that was made and which is held in my permanent table. The above is a temporary table. I need to calculate weekly totals (Mon-Fri) and monthly totals. Monthly totals are easy. Weekly totals I thought would be easy using the calendar week number for uniqueness - but this went out the window when 30th June was a Wednesday and 1st July a Thursday - both are the same week number, but because Thursday onwards is a new month I needed to split it apart.

To overcome this, I created a WeekUniqueID column which is basically Year + Month + WeekNumber concatenated, eg: 20100613 - which is unique for every week and will always be incremental. I can split out my July payments because the unique ID for those two days would be 20100713, with the following Monday falling under 20100714.

Anyway... I am extracting the above info into a temp table in order to 'mark' which payment is the LastEntryOfWeek - ie: the row in the database (for that week number) that will hold the weekly total next to it, the others are to be null.

So once I can strip out all but the highest ID for each date, I can then mark the LastEntryOfWeek = True and then update the permanent table from the temp table and dispose of it.

I know it's probably crap or inefficient way of doing things but it's the only way I can get it to work. Happy to provide full details of what I am actually doing if you can think of a better way of doing things, but for now if I can just get the following fields deleted from my temp table - that willl do all I need it to do...

ID DatePaid WeekUID
-----------------------------------------
0132 01/04/2010 1 *delete*
0342 01/04/2010 1 *delete*
0233 02/04/2010 1 *keep*
0231 07/04/2010 2 *delete*
0237 08/04/2010 2 *keep*

I just need some kind of

Delete * from TempTable where ID < MaxID for the rows where the DatePaid is the same

Sorry this sounds confusing, but if I was any good at SQL I guess I wouldn't be here asking for help...!

Thanks.
Andrew Anthony
New Member
New Member

--
09 Jul 2010 03:04 AM
Someone has supplied a sample on another forum which works perfectly - thanks to everyone who replied. Here is the working code:

UPDATE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY [weekUID] ORDER BY [DatePaid] DESC, ID DESC) AS [rowPos]
FROM #TempTable
) AS f
WHERE [rowPos] > 1
You are not authorized to post a reply.

Acceptable Use Policy