January 11, 2020 at 6:34 pm
Morning,
Hope you can set me straight. on this left join problem I'm having.
I have a left Join to collect all records with todays date , but the right table or Table B. total Tons is off , seems it summing all records with join conditions, but not just for todays date, how do i get the right table to sync with the date in the where clause.
- thanks.
Ive attached both table schemes.
SELECT
y.[Date]
,y.[Customer]
,y.[Product]
,sum( t.nettons) Tons
FROM [SA-Releases] Y
left Join [LoadoutTransaction] t
on y.customer = t.companyname
and y.Product = t.productname
WHERE [Date] = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
--and t.companyname is null
-- and y. customer is not null
GROUP BY y.[Date],y.customer,
y.product, t.productname
--, t.nettons
order by 1
January 11, 2020 at 8:09 pm
What is the relationship between [SA Releases].Date and LoadTransaction? Is there a date column in LoadTransaction?
Assuming you want all records for today from [SA Releases] and only those transactions from LoadTransaction that have occurred some time today (and assuming [Date] is a date data type and not a datetime data type):
Select y.[Date]
, y.[Customer]
, y.[Product]
, sum(t.nettons) Tons
From [SA-Releases] Y
Left Join [LoadoutTransaction] t On t.companyname = y.customer
And t.productname = y.Product
And t.{some date column} >= y.[Date]
And t.{some date Column} < dateadd(day, 1, y.[Date])
Where [Date] = dateadd(day, datediff(day, 0, getdate()), 0)
--and t.companyname is null
-- and y. customer is not null
Group By
y.[Date]
, y.customer
, y.product
, t.productname
--, t.nettons
Where [Date] >= dateadd(day, datediff(day, 0, getdate()), 0)
And y.[Date] < dateadd(day, 1, dateadd(day, datediff(day, 0, getdate()), 0))
If [SA Release].Date is actually a datetime data type then you need to consider the time portion and use this:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 11, 2020 at 8:43 pm
Hey Jeff,
That worked like a charm , thanks much !
The other Date was TransactionDate, I added that in , and although both Tables use column Datetime, it worked right without the using the other where clause. I will test and maybe use it if necessary.
Looking at what you did, adding those extra conditions looks logical but I'm not sure I would have ever figured it out. but now will remember for next time - So thanks again.,
January 12, 2020 at 4:13 pm
Glad this helped...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 14, 2020 at 12:34 am
Jeff,
I noticed that the query stops retuning records after 6:00pm , so I used the other date option you suggested. because both tables date columns are Datetime data type.
Where [Date] >= dateadd(day, datediff(day, 0, getdate()), 0)
And y.[Date] < dateadd(day, 1, dateadd(day, datediff(day, 0, getdate()), 0))
I tried a few variations, even using cast
Where
--CAST([TransactionDate] AS DATE) > DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
This returns records but the -7 days messes with totals.
any help appreciated
thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply