Avatar of W.E.B
W.E.B
 asked on

SQL 2008 Dates

Hello,
Can you please help,

I use below code
SELECT WaybillNumber, ShipmentDate,Scanin,ScantoTtruck FROM ScanItem WHERE shipmentdate > = (Select datediff(d, 0, getdate())-7) AND ScanIn IS NOT NULL and Scanin = (SELECT MAX(Scanin) FROM ScanItem);

This will give me the max scanin for all 7 days,

I need to have the max scanin per day.

Also,
how do I update
Update scanitme set scanin =  dateadd(second,5, MAX SCANIN)  FROM ScanItem);--- for that specific shipmentdate

Any help is appreciated,

Thanks,
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Ioannis Paraskevopoulos

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

Open in new window



Which field do you want updated? ScanIn?

Giannis
Sharath S

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 

Open in new window


And clarify on what do you want to update.
W.E.B

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ioannis Paraskevopoulos

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

Open in new window

W.E.B

ASKER
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
PortletPaul

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

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
W.E.B

ASKER
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,
PortletPaul

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)

Open 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)
W.E.B

ASKER
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,
Your help has saved me hundreds of hours of internet surfing.
fblack61
W.E.B

ASKER
Hello,

each NULL has it's own Distinct WaybillNumber.

THANKS
PortletPaul

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
W.E.B

ASKER
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,
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
W.E.B

ASKER
here are some examples

Waybillnumber
0469187001
0480882001
0485310001
0485891001
0485891002
0485891003
PortletPaul

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
W.E.B

ASKER
Hi Paul,
I have 2008 R2
THANKS
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PortletPaul

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

Open in new window

edit: changed partition by to include MD.ShipmentDate
W.E.B

ASKER
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
PortletPaul

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
W.E.B

ASKER
ok,
Appreciate your help

Thanks
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
W.E.B

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

storing this for later reference/re-use (sample data etc.)
http://sqlfiddle.com/#!3/1fc04/15
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy