Another user trying get around a TEMP table
-
Like the earlier post, I'm also looking for a way to get rid of a temp table. I'm trying to pull a list of shipments which completed yesterday. The only way to tell if it has completed is if it no longer has any scandate entries of '12/30/1899', which is a default value for shipments which still have cartons that have not be received. I have the following SQL which works great:
create table #mindate
(ship Numeric(8),
mndate DATETIME)
insert #mindate(Ship,mndate)
Select Distinct Shipment, Min(scandate)
from carton
group by shipment
select distinct shipment, MAX(scandate) ScanD
from carton, #mindate
where carton.shipment = #mindate.ship
and #mindate.mndate <> '12/30/1899'
and scandate = dateadd(dd, datediff(dd,0,Getdate()),0)-1
and scandate <> dateadd(dd, datediff(dd,0,Getdate()),0)
group by carton.shipment
order by shipment
Each time I try various other methods, I cannot get it to ignore all shipments which still have a scandate of '12/30/1899'. Here's an example of a failed attempt:
select distinct b.shipment, MAX(b.scandate)
from
(select distinct shipment, MIN(scandate) minscan from carton
where scandate <> '1899-12-30 00:00:00.000'
group by shipment) as a
join
carton b on a.shipment = b.shipment
where
b.scandate = dateadd(dd, datediff(dd,0,Getdate()),0)-1
and b.scandate <> dateadd(dd, datediff(dd,0,Getdate()),0)
group by b.shipment, b.scandate
order by b.shipment
Any ideas? What am I doing wrong?
-
In your 2nd attempt your where scandate'1899-12-30 00:00:00.000' needs to be outside of you derived table query.
Loose the DISTINCT from the derived table query, it is superfluous.
Do you have and scandate rather than as this will be faster
Not sure what your outer where clause seems to say as I'm looking at this from home but do you need both an = and for your dateadd?
-
How about a shipdate column???
-
David, the two dateadd statements are to make certain we are only getting data from yesterday (there may be a better way to do it). Because I am pulling MAX scandate I was getting data from both yesterday AND TODAY.
Remi, I'm not sure what you meant by a shipdate column?
-
Sorry, misread the question.
-
It seems too obvious and probably just the way you typed the post but your sample queries are using different datetime formats.
-
Use the DATEDIFF function instead of all the DATEADDS. Without modifying your code too much try this: (i removed the inner distinct as recommend earlier)
select distinct b.shipment, MAX(b.scandate)
from
(select shipment, MIN(scandate) minscan from carton
where scandate <> '1899-12-30 00:00:00.000'
group by shipment) as a
join
carton b on a.shipment = b.shipment
where
datediff(d,b.scandate,getdate() = 1
group by b.shipment, b.scandate
order by b.shipment
If the phone doesn't ring...It's me.
-
DATEDIFF will effectively rule out the use of an index to improve performance. Not the brightest outlook on a large table, if you ask me.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I was offering an option for the trouble of getting yesterday's records. I didn't see anything referring to a large table or performance.
If DATEDIFF rules out indexes, does DATEADD?
If the phone doesn't ring...It's me.
-
Not necessarily. I think.
Consider this
USE Northwind
DECLARE @dt DATETIME
SET @dt = '19960701'
SELECT OrderID, CustomerID
FROM Orders
WHERE OrderDate = DATEADD(DAY,7,@dt)
SELECT OrderID, CustomerID
FROM Orders
WHERE DATEDIFF(d,@dt,OrderDate)=7
The resultset is the same, however, the first query uses an index, while the second scans the table. It's not the best example, but you'll get the idea.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
How about something like this:
select distinct shipment, MAX(scandate) ScanD
from carton
where scandate = dateadd(dd, datediff(dd,0,Getdate()),0)-1
and shipment not in (select shipment from carton where scandate <> '12/30/1899')
group by shipment
order by shipment
Tom
-
If I understood the shipment-carton tables right, this shouldn't be more than straight join with a not exists clause to get this list...?
create table #shipment ( sId int not null, description varchar(10) not null )
create table #cartons ( sId int not null, cId int not null, scandate datetime not null )
go
insert #shipment
select 1, 'ship A' union all
select 2, 'ship B' union all
select 3, 'ship C' union all
select 4, 'ship D'
go
insert #cartons
select 1, 1, '18991230' union all --=== A not completed
select 2, 1, dateadd(day, -1, getdate()) union all --== B one carton completed yesterday,
select 2, 2, '18991230' union all --== but still one undelievered
select 3, 1, '20050101' union all --== C completed long before yesterday
select 4, 1, '20051224' union all --== D one carton completed long time ago,
select 4, 2, dateadd(day, -1, getdate()) --== and final carton completed yesterday
go
select c.sId, max(c.scandate) as 'finalShipDate'
from #cartons c
join #shipment s
on c.sId = s.sId
and c.scandate >= convert(char(8), dateadd(day, -1, getdate()), 112)
and c.scandate < convert(char(8), getdate(), 112)
and not exists ( select * from #cartons ca where s.sId = ca.sId and ca.scandate = '18991230' )
group by c.sId
go
--== this is the one we want
sId finalShipDate
----------- ------------------------------------------------------
4 2005-12-29 13:23:50.377
drop table #shipment, #cartons
go
/Kenneth
-
I don't think there is a need for a join. If a shipment isn't complete then it has a lowval date and that excludes all items from that shipment in the result set. If an item shipments are complete then you just need know when that item completion date is.
so.
select shipment,max(scandate) as Completion from carton where shipment not in(
select shipment from carton where scandate <> '12301899' --all shipments where complete.
)
group by shipment
This query would list the shipment number and the completion date for every shipment that is complete.
Join not needed.
Tom
-
I don't see the need for a temp table here when a derived table is just as easy. Also, the check for Yesterday will go slightly faster in this comparison:
SELECT shipment, MAX(scandate) ScanD
from carton
LEFT JOIN (Select Distinct shipment From Carton WHERE scandate = '18991230') Unshipped
ON carton.shipment = Unshipped.shipment
WHERE unshipped.shipment is null
AND scandate = Cast( Cast(Getdate() -1 as integer) as datetime) -- Only for Yesterday
group by shipment
order by shipment
Of course, if the scandate includes a time, you will need to modify the scandate check:
...AND scandate >= Cast( Cast(Getdate() -1 as integer) as datetime)
AND scandate < Cast( Cast(Getdate() as integer) as datetime)
-
As we all know, there are many ways to skin the same cat, as shown here as well.
If we should go the 'short route', the minimum needed is just to query the cartons table for shipment ID's that has at least one date entry for yesterday, and no dates existing for the default 'undelivered' date.
It's mostly a matter of personal taste if the check should be by IN, LEFT JOIN or NOT EXISTS.. The same goes for the shipment ID itself - either DISTINCT, or a GROUP BY. Most of the times performance is equal, but like with everything else, test it and then decide which you prefer.
The main point is that no, there's no need of a temptable. The rest of the formulations is just different ways of achieving the same endresult.
Oh, and a Happy New Year to you all
/Kenneth
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply