SQL 2008 Dates

Hi,

SELECT	WaybillNumber,
		CONVERT(VARCHAR(8), ShipmentDate, 112),
		MAX(Scanin),
		ScantoTtruck
FROM	ScanItem 
WHERE	shipmentdate > = (Select datediff(d, 0, getdate())-7) 
		AND ScanIn IS NOT NULL 
GROUP BY WaybillNumber,
		CONVERT(VARCHAR(8), ShipmentDate, 112),
		ScantoTtruck

Select allOpen in new window

Which field do you want updated? ScanIn?

Giannis

you can also try this.

SELECT * 
  FROM (SELECT WaybillNumber, 
               ShipmentDate, 
               Scanin, 
               ScantoTtruck, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY WaybillNumber, CONVERT(DATE, ShipmentDate) 
                   ORDER BY Scanin DESC ) rn 
          FROM ScanItem t1 
         WHERE shipmentdate > = DATEADD(dd, -7, GETDATE()) 
           AND ScanIn IS NOT NULL) t1 
 WHERE rn = 1 

Select allOpen in new window

And clarify on what do you want to update.

hello,
Thanks for your help,
i tried both codes (Giannis and Sharath)
they both give me all orders. (Thousands), I should have got only 7

example, (please see attached).

Update the scanin.

thanks
Sample.xlsx

SELECT	WaybillNumber,
		CONVERT(VARCHAR(8), ShipmentDate, 112),
		MAX(Scanin),
		ScantoTtruck
FROM	ScanItem 
WHERE	shipmentdate > = (Select datediff(d, 0, getdate())-7) 
		AND ScanIn IS NOT NULL 
                AND Scanin = (SELECT MAX(Scanin) FROM ScanItem)
GROUP BY WaybillNumber,
		CONVERT(VARCHAR(8), ShipmentDate, 112),
		ScantoTtruck

Select allOpen in new window

Hello jyparask,
Your code gives me only one day.
0497239009         20130321      2013-03-22 02:27:14.290          2013-03-22 06:16:24.367

thanks

SELECT
      WaybillNumber
	, cast(ShipmentDate as date)
	, Max(ScanIn) as ScanIn --I need to have the max scanin per day.
	, ScantoTtruck
FROM ScanItem
WHERE shipmentdate > = (
		SELECT datediff(d, 0, getdate()) - 7
		)
	
GROUP BY
      WaybillNumber
	, cast(ShipmentDate as date)
	, ScantoTtruck

Select allOpen in new window

Hello Paul,
Results : I still get all of the shipments.

I should have got only 7 or 8  rows.
March 17th, 18th,19th,20th,21st,22nd,23rd,24th

Thanks,

SELECT
          cast(ShipmentDate as date)
	, Max(ScanIn) as ScanIn --I need to have the max scanin per day.
FROM ScanItem
WHERE shipmentdate > = (
		SELECT datediff(d, 0, getdate()) - 7
		)
	
GROUP BY
          cast(ShipmentDate as date)

Select allOpen in new window

YOu do not need that inner select, but I heve left it for now, you could get the same result from:

WHERE shipmentdate > = dateadd(d,0,datediff(d, 0, getdate()) - 7)

Hi Paul,
Appreciate your help,
OK, this takes care of one part,

How do I update the scanin?

Example
on March 17th, I have  5 NULL VALUES  (in the field Scanin)
I need to update the NULL to add 5 seconds.
Update scanitme set scanin =  dateadd(second,5, MAX SCANIN)  FROM ScanItem);--- for that specific Shipmentdate

Example
2013-03-19 03:56:15.577

1st NULL should be   2013-03-19 03:56:20.577
2nd NULL should be   2013-03-19 03:56:25.577
3rd NULL should be   2013-03-19 03:56:30.577
4th NULL should be   2013-03-19 03:56:35.577
5th NULL should be   2013-03-19 03:56:40.577


Thanks again,

Hello,

each NULL has it's own Distinct WaybillNumber.

THANKS

This is a new question isn't it?

Is there a logical 'sequence' to these waybills? Can the scanin of the immediately prior waybill (assuming a sequence) be used

wb0 2013-03-19 03:56:15.577
wb1
wb2
wb4
wb5
wb6 2013-03-20 00:01:05.111

In other words is there a 'gap' with 2 scanin boundaries (low and high) and he scanin should fit between

Hi Paul,

Question was in the first post, (I just made it clearer).
I will increase the points to 500.

There is no logical for the waybills,
as long as I have a scan , I'm ok
Each of the scan has to be 5 seconds between each other after Max scan of that day.

Not sure if I explained it properly.

thanks,

here are some examples

Waybillnumber
0469187001
0480882001
0485310001
0485891001
0485891002
0485891003

you are using SQL 2008? or 2012?

this will work in 2012, not 2008, but will help identify the methods to use

SELECT
  waybill
, scanin  
, LAG(scanin, 1,0) OVER (ORDER BY waybill) AS PreviousScanin
FROM  ScanItem
WHERE shipmentdate > = dateadd(d,0,datediff(d, 0, getdate()) - 7)
and scanin is null
order by
  waybill

Hi Paul,
I have 2008 R2
THANKS

try this query, see if it is calculating an appropriate value for scanin:

select
  waybill
, scanin
, dateadd(second,(5 * row_ref),ScanInMax) as new_scanin
, convert(varchar, dateadd(second,(5 * row_ref),ScanInMax),121) as new_scanin_str
, row_ref
, ScanInMax
from (
        select
          SI.waybill
        , SI.scanin
        , MD.ScanInMax
        , row_number() over (partition by MD.ShipmentDate, SI.scanin order by SI.waybill) row_ref
        from ScanItem SI
        inner join (
                    SELECT
                          cast(ShipmentDate as date) as ShipmentDate
                        , Max(ScanIn) as ScanInMax
                    FROM ScanItem
                    WHERE shipmentdate >= datediff(d, 0, getdate()) - 7
                    GROUP BY
                          cast(ShipmentDate as date)
                      ) as MD on cast(SI.ShipmentDate as date) = MD.ShipmentDate
        where SI.shipmentdate >= datediff(d, 0, getdate()) - 7
        and SI.scanin is null
     ) as derived

Select allOpen in new window

edit: changed partition by to include MD.ShipmentDate

Hi Paul,
yes, The select code calculates properly

waybillNumber      new_scanin                                      new_scanin_str
0486447001              2013-03-21 04:40:25.427        2013-03-21 04:40:25.427
0492949001              2013-03-21 04:40:30.427        2013-03-21 04:40:30.427
0496695001              2013-03-21 04:40:35.427        2013-03-21 04:40:35.427
0496770001              2013-03-21 04:40:40.427        2013-03-21 04:40:40.427
0499520001       2013-03-22 02:27:19.290        2013-03-22 02:27:19.290
0500006001       2013-03-22 02:27:24.290        2013-03-22 02:27:24.290
0500578001              2013-03-22 02:27:29.290        2013-03-22 02:27:29.290

Now how to update,

Thanks for your help

I hope someone else is monitoring because I may not be able to get to the update.

if this goes slow you may need to ask another question (the initial question was answered some time back)

you can reference this questoin by url or take query logic into new question

ok,
Appreciate your help

Thanks

Thanks Paul,
hope someone can help,
I will post the question..

storing this for later reference/re-use (sample data etc.)
http://sqlfiddle.com/#!3/1fc04/15