Click here to Skip to main content
14,422,213 members
Rate this:
Please Sign up or sign in to vote.
See more:
This Query it doesn't work it gives me no Data could you have a look please and tell me where is the erro ?
I will be glad for an explanation

What I have tried:

DECLARE @fromdate datetime = Convert (datetime, '05.10.19', 4), @todate datetime= Convert (datetime, '15.11.19', 4)

select  itemH.ProduktID,p.P_NavArtNr as ProduktNavArtNr,p.P_Name as ProduktName,

Concat(lager.Scancode ,handling.Scancode) as ToLocationScancode, 

Concat (lager2.Scancode ,handling2.Scancode) as FromLocationScancode, itemH.Date, itemH.Art

from ItemHistory itemH

join Produkte p on itemH.ProduktID = p.P_ID
left Join Lagerort lager on itemH.ToLocation = lager.LagerID

left join HandlingUnits handling on itemH.ToLocation = handling.Unit_ID

left join Lagerort lager2 on itemH.FromLocation = lager2.LagerID 

left join HandlingUnits handling2 on itemH.FromLocation = handling2.Unit_ID

where Date = @fromdate and Date =@todate

order by itemH.Date desc
Posted
Updated 2-Dec-19 22:08pm
v2
Comments
0x01AA 2-Dec-19 9:41am
   
WHERE Date = @fromdate and Date =@todate
should be more
WHERE Date >= @fromdate and Date <= @todate
[no name] 2-Dec-19 10:00am
   
thanks it works as i wanted ..many thanks
0x01AA 2-Dec-19 10:02am
   
You are welcome. Maybe you accept SOlution 1, it is equivalent ;)
[no name] 2-Dec-19 10:05am
   
you didn't sumbit that as Solution .. could you edit your answer please . i will accept it as solution
0x01AA 2-Dec-19 10:10am
   
Done
Rate this:
Please Sign up or sign in to vote.

Solution 1

Unless your "valid period" is very short indeed, this condition will never be met:
where Date = @fromdate and Date =@todate
Additionally, "=" is an exact match - if you are using DATETIME values then the time portion must also match to the tick.
I'd suggest you actually want this:
WHERE [Date] BETWEEN @fromdate AND @todate
   
Comments
[no name] 2-Dec-19 9:58am
   
I did that as well "where Date between @fromdate AND @todate order by itemH.Date desc"
but someone told me there is another solution ?!
[no name] 2-Dec-19 10:01am
   
WHERE Date >= @fromdate and Date <= @todate
this one is working now it's from @0x01AA
Rate this:
Please Sign up or sign in to vote.

Solution 2

WHERE Date = @fromdate and Date =@todate
should be more
WHERE Date >= @fromdate and Date <= @todate

But the above is equivalent to Solution 1.

From my expirience in praxis one will find more this (note "<" instead of "<=":
WHERE Date >= @fromdate and Date < @todate
   
Comments
Maciej Los 3-Dec-19 2:15am
   
5ed!
0x01AA 3-Dec-19 4:29am
   
Thank you Maciej!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100