Guru needed for help with cte

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sheldon - 25 Jul 2008 22:48 GMT

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

Reply to this Message

Plamen Ratchev - 25 Jul 2008 23:20 GMT

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

Reply to this Message

Sheldon - 26 Jul 2008 15:34 GMT

Hi Plamen!

Thank you so much!!

Signature

Sheldon

> 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
[quoted text clipped - 22 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Roy Harvey (SQL Server MVP) - 25 Jul 2008 23:32 GMT

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

> Hello -
>
[quoted text clipped - 81 lines]
>6/26/2008 15:52:36 1603 0:30:00 2
>6/26/2008 17:46:41 1603 1:02:00 1

Reply to this Message