ansaurus

Question

Need a the last records of a day, from a range of days.

Answer 1

+2 A:

You did not mention what version of SQL Server (nor many details about how you are currently doing it), however if it is SQL Server 2005+ you do:

With NumberedData As
    (
    Select ...
        , Row_Number() Over ( Partition By DateDiff(d, 0, E.dExD_Fetcha) 
                              Order By E.dExD_Fetcha Desc ) As Num
    From PDFP.dbo.[Existencia_WH_PRISM_BACKUP] As E
    )
Select ...
From Data
Where Num = 1

Thomas 2010-07-20 23:00:10

I am kinda lost with Thomas answer, at the moment I just want to delete the bad data on the database later I would merge them, for what I gather "WITH DATA" I am creating a unision without the needed of use joins.

Enrique 2010-07-20 23:26:38

@Enrique - I'm using a common-table expression. I happened to have named my CTE "Data". I have revised my post to provided a better name for the CTE (in this case "NumberedData"). In short, I'm having the system number every row, such that it restarts numbering on each day and such that the latest time has the lowest number (i.e. 1 for the latest time, 2 for the next latest etc.).

Thomas 2010-07-20 23:55:02

Answer 2

A:

I like the solution Thomas posted and would go with that since a query without having to use a cursor is pretty much always preferable to one that uses one. (SQL is built for SET logic so it's always going to be more efficient doing what it does best.)

You also said that you needed to get potentially multiple rows per day so long as they were all stored at the same time. The CTE expression as written in the Thomas solution gives you the last single one only which may suffice. Otherwise you could easily modify his solution.

Below is a cursor solution since you had curiosity about them. I provide it with the advice that you should not use it but you should know that it is possible.

You would, of course, use a real table rather than a table variable as below if you wanted to keep the results for later analysis.

DECLARE @t1 TABLE ( DtTm datetime )

DECLARE @Dt datetime        
DECLARE c CURSOR FOR 
    SELECT CONVERT(datetime, CONVERT(char(11), dExD_Fecha, 113))
    FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP]
    GROUP BY CONVERT(char(11), dExD_Fecha, 113)

OPEN c
FETCH NEXT FROM c INTO @Dt
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @t1 
    SELECT dExD_Fecha -- add any other fields you care to capture here and above to the table definition
    FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP]
    WHERE dExD_Fecha = (
        SELECT MAX(dExD_Fecha)
        FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP]
        WHERE CONVERT(datetime,CONVERT(char(11), dExD_Fecha, 113)) = @Dt)
    FETCH NEXT FROM c INTO @Dt
END

CLOSE c
DEALLOCATE c

SELECT * FROM @t1

Tahbaza 2010-07-20 23:23:05

Answer 3

A:

Try this:

SELECT e.*
FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP] e
INNER JOIN (
  SELECT MAX(dExd_Fecha) AS dExd_Fecha_Max
  FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP]
  GROUP BY DATEADD(DD, DATEDIFF(DD, 0, dExd_Fecha), 0) 
) m
  ON e.dExD_Fecha = m.dExd_Fecha_Max
ORDER BY e.dEXd_Fecha

The idea here is to create a subquery of the maximum dates grouped by day and then join against the main table.

If you want to identify the values that fall outside that range in order to delete them, you can do a left outer join against the set:

/** DELETE FROM x **/
SELECT * 
FROM (
  SELECT 
    dExd_Fecha,
    YEAR(dExd_Fecha) AS dExd_Ano, 
    MONTH(dExd_Fecha) AS dExd_Semana, 
    NTILE(10) OVER (ORDER BY dExd_Fecha) AS dExd_Groupo  
  FROM [Existencia_WH_PRISM_BACKUP]
) x 
LEFT OUTER JOIN ( 
  SELECT MAX(dExd_Fecha) AS dExd_Fecha_Max 
  FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP]  
  GROUP BY DATEADD(DD, DATEDIFF(DD, 0, dExd_Fecha), 0)   
) y 
ON x.dExd_Fecha = y.dExd_Fecha_Max
WHERE 
  x.dExd_Ano = 2010
  AND x.dExd_Semana = 1
  AND y.dExd_Fecha_Max IS NULL
ORDER BY x.dExd_Fecha

This SELECT statement uses YEAR and MONTH values in the subquery in order to do a batch-processing approach and select / delete records in smaller amounts.

The original answer is below:

SELECT e.* 
FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP] e 
INNER JOIN ( 
  SELECT 
    MAX(dExd_Fecha) AS dExd_Fecha_Max,

    /** get the year of the last date **/
    YEAR(MAX(DATEADD(YY, DATEDIFF(YY, 0, dExd_Fecha), 0))) AS dExd_Ano_Max,

    /** get the month of the last date **/
    MONTH(MAX(DATEADD(MM, DATEDIFF(MM, 0, dExd_Fecha), 0))) AS dExd_Mes_Max,

    /** get the week of the last date **/
    DATEPART(WEEK, MAX(dExd_Fecha)) AS dExd_Semana_Max,

    /** set a number to divide the total rows into ten groups **/
    NTILE(10) OVER (ORDER BY MAX(dExd_Fecha)) AS dExd_Groupo 

  FROM [PDP].[dbo].[Existencia_WH_PRISM_BACKUP] 
  GROUP BY DATEADD(DD, DATEDIFF(DD, 0, dExd_Fecha), 0)  
) m 
  ON e.dExD_Fecha = m.dExd_Fecha_Max 
WHERE 
  m.dExd_Ano_Max = 2010
  AND m.dExd_Mes_Max = 1

8kb 2010-07-21 03:34:03

A quick question here, in case I don´t want to process everything on 1 go, how can I set intervals like 1 week or 1 month? or a number or registers.

Enrique 2010-07-21 17:49:15

Yes, this can be done with new columns in the subquery. Edits above.

8kb 2010-07-21 18:52:03

One last question, in case I want to delete those not equal to the search result? I get a error in the comparation

Enrique 2010-07-21 21:43:25

Interesting. In that case, using the max values in the original subquery won't work. But it can be accomodate. Pls see new edit.

8kb 2010-07-22 00:53:44

ansaurus

tags:

views:

answers:

Need a the last records of a day, from a range of days.

related questions