Guru needed for help with cte
in [Programming SQL]
|
From: Sheldon on 25 Jul 2008 17:48 Hello - I have the following cte in which I am attempting to take two tables, combine them and FOR EACH distinct date that is listed, return only one date, the amount of only the last AdminAdjustment for that date, only the last DateAdjusted date, etc. I included the data it is returning and for example, it's returning three records for 6/11, 2 records for 6/17, etc. The record I want for 6/11 is 6/11/2008 13:38:47 1603 1:08:47 2 The record I want for 6/17 is 6/17/2008 16:30:01 1603 0:30:00 1 What am I doing wrong? Any help will be greatly appreciated!!!! WITH cteAdjustment (RowNo, row_date, logid, AdminAdjustment, DateAdjusted) AS ( SELECT DENSE_RANK() OVER (PARTITION BY row_date ORDER BY DateAdjusted) as RowNo, row_date, logid, CONVERT(varchar(8),CONVERT(datetime, convert(char(8), (AdminAdjustment)/3600) +':' +convert(char(2),((AdminAdjustment)%3600)/60) +':' +convert(char(2),((AdminAdjustment)%3600)%60 )),108) as AdminAdjustment, MAX(DateAdjusted) FROM tblAdjustment Where row_date >= '5/1/2008' and row_date <= '6/26/2008' and logid = 1603 Group by row_date, logid, AdminAdjustment, DateAdjusted UNION SELECT DENSE_RANK() OVER (PARTITION BY row_date Order by row_date) as RowNo, row_date, logid, '0', DateAdjusted = '1/1/2000' -- DENSE_RANK() OVER (PARTITION BY row_date ORDER BY DateAdjusted) FROM root_dagent Where row_date >= '5/1/2008' and row_date <= '6/26/2008' and logid = 1603) Select row_date, MAX(DateAdjusted) as DateAdjusted, logid, AdminAdjustment as AdminAdjustment, MAX(RowNo) as RowNo from cteAdjustment Group by row_date, logid, AdminAdjustment Order by row_date Asc row_date DateAdjusted logid AdminAdjustment RowNo 5/1/2008 0:00:00 1603 0 1 5/2/2008 0:00:00 1603 0 1 5/12/2008 0:00:00 1603 0 1 5/13/2008 0:00:00 1603 0 1 5/14/2008 0:00:00 1603 0 1 5/15/2008 0:00:00 1603 0 1 5/16/2008 0:00:00 1603 0 1 5/19/2008 0:00:00 1603 0 1 5/20/2008 0:00:00 1603 0 1 5/21/2008 0:00:00 1603 0 1 5/22/2008 0:00:00 1603 0 1 5/23/2008 0:00:00 1603 0 1 5/27/2008 0:00:00 1603 0 1 5/28/2008 0:00:00 1603 0 1 5/29/2008 0:00:00 1603 0 1 5/30/2008 0:00:00 1603 0 1 6/9/2008 0:00:00 1603 0 1 6/10/2008 0:00:00 1603 0 1 6/11/2008 0:00:00 1603 0 1 6/11/2008 13:38:47 1603 1:08:47 2 6/11/2008 0:00:00 1603 2:30:00 1 6/12/2008 0:00:00 1603 0 1 6/16/2008 0:00:00 1603 0 1 6/17/2008 0:00:00 1603 0 1 6/17/2008 16:30:01 1603 0:30:00 1 6/18/2008 0:00:00 1603 0 1 6/19/2008 0:00:00 1603 0 1 6/20/2008 0:00:00 1603 0 1 6/23/2008 0:00:00 1603 0 1 6/23/2008 0:00:00 1603 2:30:00 1 6/23/2008 15:32:21 1603 8:30:00 2 6/24/2008 0:00:00 1603 0 1 6/24/2008 0:00:00 1603 1:20:00 1 6/25/2008 0:00:00 1603 0 1 6/25/2008 0:00:00 1603 1:15:00 1 6/26/2008 0:00:00 1603 0 1 6/26/2008 15:52:36 1603 0:30:00 2 6/26/2008 17:46:41 1603 1:02:00 1
From: Plamen Ratchev on 25 Jul 2008 18:20 It is not clear from your sample data and query if the row_date or the DateAdjusted column defines the latest. Below change in the ORDER BY to the correct date. In essence first UNION the two tables and then do the ranking to get only the latest. Also, DENSE_RANK will give you ties, you can use ROW_NUMBER if you want only one and add to ORDER BY a tiebreaker column (perhaps the second date). WITH UnionCTE AS ( SELECT ... UNION SELECT ...), WITH RankingCTE AS (SELECT DENSE_RANK() OVER (PARTITION BY DATEDIFF(DAY, 0, row_date) ORDER BY row_date DESC) AS rn, ... FROM UnionCTE) SELECT ... FROM RankingCTE WHERE rn = 1; HTH, Plamen Ratchev http://www.SQLStudio.com
From: Roy Harvey (SQL Server MVP) on 25 Jul 2008 18:32 I see is that there is nothing in the code that would even try to pick out the "last" AdminAdjustment for that date. It is kind of hard to say what is being done wrong when nothing is being done at all. First, GROUP BY consolidates rows down the unique combination of all the elements in the GROUP BY set. In the first SELECT that is (row_date, logid, AdminAdjustment, DateAdjusted), and the last it is (row_date, logid, AdminAdjustment). Now logid is selected for a single value, but AdminAdjustment has three different values for 6/11 so of course a GROUP BY that includes AdminAdjustment includes a row for all three within 6/11. One of the common ways to get the "last" row for a group is to use ranking, which you appear to have started to do. However the usual practice when using ranking for a date where you want the "last" is to specify DESC in the ORDER BY so that the "last" row appears first. The rest of that is to somewhere specify that only the rank of 1 is returned. On the other hand, since data from two data sources has to be combined before the determination of which is "last" it would seem necessary to apply the rank AFTER the two are combined. There is more than could be said, but without knowledge of the tblAdjustment and root_dagent tables and their data it is all very uncertain just what the answers would be. Sorry this is so thin on specific changes, but I just didn't find enough to go on to be more constructive. Roy Harvey Beacon Falls, CT On Fri, 25 Jul 2008 14:48:06 -0700, Sheldon <Sheldon(a)discussions.microsoft.com> wrote: > Hello - > >I have the following cte in which I am attempting to take two tables, >combine them and FOR EACH distinct date that is listed, return only one date, >the amount of only the last AdminAdjustment for that date, only the last >DateAdjusted date, etc. I included the data it is returning and for example, >it's returning three records for 6/11, 2 records for 6/17, etc. > >The record I want for 6/11 is 6/11/2008 13:38:47 1603 1:08:47 2 >The record I want for 6/17 is 6/17/2008 16:30:01 1603 0:30:00 1 > >What am I doing wrong? > >Any help will be greatly appreciated!!!! > >WITH cteAdjustment (RowNo, row_date, logid, AdminAdjustment, > DateAdjusted) AS >( SELECT DENSE_RANK() OVER (PARTITION BY row_date ORDER BY DateAdjusted) as >RowNo, > row_date, logid, > CONVERT(varchar(8),CONVERT(datetime, convert(char(8), >(AdminAdjustment)/3600) +':' > +convert(char(2),((AdminAdjustment)%3600)/60) +':' > +convert(char(2),((AdminAdjustment)%3600)%60 )),108) as AdminAdjustment, > MAX(DateAdjusted) > >FROM tblAdjustment >Where row_date >= '5/1/2008' and row_date <= '6/26/2008' and logid = 1603 > Group by row_date, logid, AdminAdjustment, DateAdjusted > >UNION > >SELECT DENSE_RANK() OVER (PARTITION BY row_date Order by row_date) as RowNo, > row_date, logid, '0', DateAdjusted = '1/1/2000' >-- DENSE_RANK() OVER (PARTITION BY row_date ORDER BY DateAdjusted) >FROM root_dagent >Where row_date >= '5/1/2008' and row_date <= '6/26/2008' and logid = 1603) > >Select row_date, MAX(DateAdjusted) as DateAdjusted, > logid, > AdminAdjustment as AdminAdjustment, > MAX(RowNo) as RowNo > from cteAdjustment >Group by row_date, logid, AdminAdjustment >Order by row_date Asc > >row_date DateAdjusted logid AdminAdjustment RowNo >5/1/2008 0:00:00 1603 0 1 >5/2/2008 0:00:00 1603 0 1 >5/12/2008 0:00:00 1603 0 1 >5/13/2008 0:00:00 1603 0 1 >5/14/2008 0:00:00 1603 0 1 >5/15/2008 0:00:00 1603 0 1 >5/16/2008 0:00:00 1603 0 1 >5/19/2008 0:00:00 1603 0 1 >5/20/2008 0:00:00 1603 0 1 >5/21/2008 0:00:00 1603 0 1 >5/22/2008 0:00:00 1603 0 1 >5/23/2008 0:00:00 1603 0 1 >5/27/2008 0:00:00 1603 0 1 >5/28/2008 0:00:00 1603 0 1 >5/29/2008 0:00:00 1603 0 1 >5/30/2008 0:00:00 1603 0 1 >6/9/2008 0:00:00 1603 0 1 >6/10/2008 0:00:00 1603 0 1 >6/11/2008 0:00:00 1603 0 1 >6/11/2008 13:38:47 1603 1:08:47 2 >6/11/2008 0:00:00 1603 2:30:00 1 >6/12/2008 0:00:00 1603 0 1 >6/16/2008 0:00:00 1603 0 1 >6/17/2008 0:00:00 1603 0 1 >6/17/2008 16:30:01 1603 0:30:00 1 >6/18/2008 0:00:00 1603 0 1 >6/19/2008 0:00:00 1603 0 1 >6/20/2008 0:00:00 1603 0 1 >6/23/2008 0:00:00 1603 0 1 >6/23/2008 0:00:00 1603 2:30:00 1 >6/23/2008 15:32:21 1603 8:30:00 2 >6/24/2008 0:00:00 1603 0 1 >6/24/2008 0:00:00 1603 1:20:00 1 >6/25/2008 0:00:00 1603 0 1 >6/25/2008 0:00:00 1603 1:15:00 1 >6/26/2008 0:00:00 1603 0 1 >6/26/2008 15:52:36 1603 0:30:00 2 >6/26/2008 17:46:41 1603 1:02:00 1
From: Sheldon on 26 Jul 2008 10:34 Hi Plamen! Thank you so much!! -- Sheldon "Plamen Ratchev" wrote: > It is not clear from your sample data and query if the row_date or the > DateAdjusted column defines the latest. Below change in the ORDER BY to the > correct date. In essence first UNION the two tables and then do the ranking > to get only the latest. Also, DENSE_RANK will give you ties, you can use > ROW_NUMBER if you want only one and add to ORDER BY a tiebreaker column > (perhaps the second date). > > WITH UnionCTE > AS > ( SELECT ... > UNION > SELECT ...), > WITH RankingCTE > AS > (SELECT DENSE_RANK() OVER (PARTITION BY DATEDIFF(DAY, 0, row_date) > ORDER BY row_date DESC) AS rn, > ... > FROM UnionCTE) > SELECT ... > FROM RankingCTE > WHERE rn = 1; > > HTH, > > Plamen Ratchev > http://www.SQLStudio.com >
|
Pages: 1 Prev: @@ROWCOUNT and @@ERR !?!?! Next: Getting error on INSERT.. SELECT using table valued function |