April 22, 2009 at 12:00 pm
I have this code that run fine when it is just a driver or a helper being passed in, but when I pass in both (or all), this code returns dates from January of 2008. Am I missing something here?
select convert(varchar, delivery_date, 101) as DeliveryDate,
truck_number as TruckNumber,
box as Box,
truck_store as TruckStore,
driver as Driver,
helper as Helper,
total_stops as TotalStops,
crm_fail_3_day as CrmFails,
service_fail_3_day as ServiceFails,
not_at_home_fail as NotAtHomeFails,
total_pieces as TotalPieces,
failure_pieces as FailurePieces,
'Total' as Total,
SUM(driver_damaged_fail + damaged_fail +
missing_harware_fail + missing_parts_fail +
pref_fail + other_fail +
does_not_fit_fail + wrong_pull_fail +
missing_item_fail + truck_late_fail) AS Refusals,
e.fname + ' ' + e.lname as FullName
from warehouse_truck_completion_stops, emp e
where delivery_date BETWEEN '04/15/2009' and '04/22/2009' and
driver = e.emp_cd and
(driver = 'ATK' or '%' = 'ATK') or helper = e.emp_cd and (helper = 'GCH' or '%' = 'GCH')
GROUP BY
delivery_date,
truck_number,
box,
truck_store,
driver,
helper,
total_stops,
crm_fail_3_day,
service_fail_3_day,
not_at_home_fail,
truck_late_fail,
total_pieces,
failure_pieces,
e.fname,
e.lname
And help would be greatly appreciated.
Thank you!
April 22, 2009 at 12:22 pm
the between looks fine, but the rest of your WHERE statement has me puzzled:
where delivery_date BETWEEN '04/15/2009' and '04/22/2009'
and driver = e.emp_cd
and (driver = 'ATK' or '%' = 'ATK') --when would the static string '%' EVER equal the static string 'ATK'?
or helper = e.emp_cd
and (helper = 'GCH' or '%' = 'GCH') --when would the static string '%' EVER equal the static string 'GCH'?
maybe you mean driver IN ('ATK','%') and helper IN ('GCH','%')? what am i missing here?
Lowell
April 22, 2009 at 12:26 pm
Lowell (4/22/2009)
the between looks fine, but the rest of your WHERE statement has me puzzled:
where delivery_date BETWEEN '04/15/2009' and '04/22/2009'
and driver = e.emp_cd
and (driver = 'ATK' or '%' = 'ATK') --when would the static string '%' EVER equal the static string 'ATK'?
or helper = e.emp_cd
and (helper = 'GCH' or '%' = 'GCH') --when would the static string '%' EVER equal the static string 'GCH'?
maybe you mean driver IN ('ATK','%') and helper IN ('GCH','%')? what am i missing here?
Hi Lowell,
Thank you for the reponse. I put in static values for testing. The actual code is:
select convert(varchar, delivery_date, 101) as DeliveryDate,
truck_number as TruckNumber,
box as Box,
truck_store as TruckStore,
driver as Driver,
helper as Helper,
total_stops as TotalStops,
crm_fail_3_day as CrmFails,
service_fail_3_day as ServiceFails,
not_at_home_fail as NotAtHomeFails,
total_pieces as TotalPieces,
failure_pieces as FailurePieces,
'Total' as Total,
SUM(driver_damaged_fail + damaged_fail +
missing_harware_fail + missing_parts_fail +
pref_fail + other_fail +
does_not_fit_fail + wrong_pull_fail +
missing_item_fail + truck_late_fail) AS Refusals,
e.fname + ' ' + e.lname as FullName
from warehouse_truck_completion_stops, emp e
where delivery_date between @StartDate and @EndDate and
driver = e.emp_cd and
(driver = @Driver or '%' = @Driver) or helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
GROUP BY
delivery_date,
truck_number,
box,
truck_store,
driver,
helper,
total_stops,
crm_fail_3_day,
service_fail_3_day,
not_at_home_fail,
truck_late_fail,
total_pieces,
failure_pieces,
e.fname,
e.lname
Thank you.
April 22, 2009 at 12:30 pm
In any case, Lowell, I am getting values back with dates from 2008, which obviously, given my static start and end dates, is wrong. I'm not sure why.
If I just run:
select convert(varchar, delivery_date, 101) as DeliveryDate
from warehouse_truck_completion_stops
where delivery_date between '04/15/2009' and '04/22/2009'
It works fine and returns as it should.
I'm puzzled as well...
Any thoughts or suggestions?
Thank you.
April 22, 2009 at 12:35 pm
These return first, before the BETWEEN dates:
01/02/2008C003CAOakwoodATKGCH13110455Total4GERALD HOOD
01/03/2008C003CAOakwoodATKGCH11000490Total0GERALD HOOD
01/03/2008C103CBOakwoodATKGCH11000281Total0GERALD HOOD
01/04/2008C003CAOakwoodATKGCH11010381Total1GERALD HOOD
01/04/2008C103CBOakwoodATKGCH11110293Total1GERALD HOOD
01/05/2008C003CAOakwoodATKGCH12000402Total1GERALD HOOD
01/05/2008C103CBOakwoodATKGCH7000502Total1GERALD HOOD
01/09/2008C003CAOakwoodATKGCH13010421Total2GERALD HOOD
01/09/2008C103CBOakwoodATKGCH7000230Total0GERALD HOOD
01/10/2008C003CAOakwoodATKGCH8100160Total0GERALD HOOD
01/10/2008C103CBOakwoodATKGCH12010331Total2GERALD HOOD
01/11/2008C003CAOakwoodATKGCH12110450Total0GERALD HOOD
01/11/2008C103CBOakwoodATKGCH12200192Total0GERALD HOOD
01/12/2008C003CAOakwoodATKGCH7000282Total2GERALD HOOD
01/12/2008C103CBOakwoodATKGCH15210384Total3GERALD HOOD
01/15/2008C003CAOakwoodATKGCH12020310Total0GERALD HOOD
01/15/2008C103CBOakwoodATKGCH4000210Total0GERALD HOOD
01/16/2008C003CAOakwoodATKGCH9010551Total0GERALD HOOD
01/16/2008C103CBOakwoodATKGCH9100212Total1GERALD HOOD
01/17/2008C003CAOakwoodATKGCH8010320Total0GERALD HOOD
01/17/2008C103CBOakwoodATKGCH5000253Total1GERALD HOOD
01/18/2008C003CAOakwoodATKGCH8010400Total0GERALD HOOD
01/18/2008C103CBOakwoodATKGCH12002583Total0GERALD HOOD
01/22/2008C003CAOakwoodATKGCH10110230Total0GERALD HOOD
01/22/2008C103CBOakwoodATKGCH14000440Total0GERALD HOOD
01/23/2008C018CACantonSXMGCH8020300Total0GERALD HOOD
01/23/2008C019CBCantonSXMGCH9020252Total0GERALD HOOD
01/24/2008C003CAOakwoodATKGCH8110250Total0GERALD HOOD
01/24/2008C103CBOakwoodATKGCH8010395Total2GERALD HOOD
01/25/2008C003CAOakwoodSXMGCH13110372Total3GERALD HOOD
01/25/2008C103CBOakwoodSXMGCH6010190Total0GERALD HOOD
01/29/2008C003CAOakwoodCHCGCH7010150Total0GERALD HOOD
01/29/2008C103CBOakwoodCHCGCH5000230Total0GERALD HOOD
01/31/2008C003CAOakwoodATKGCH9120370Total0GERALD HOOD
01/31/2008C103CBOakwoodATKGCH200081Total1GERALD HOOD
02/01/2008C003CAOakwoodATKGCH11110461Total0GERALD HOOD
02/01/2008C103CBOakwoodATKGCH6000290Total0GERALD HOOD
02/02/2008C003CAOakwoodATKGCH9000241Total1GERALD HOOD
02/02/2008C103CBOakwoodATKGCH8020310Total0GERALD HOOD
02/07/2008C003CAOakwoodATKGCH11000370Total0GERALD HOOD
02/07/2008C103CBOakwoodATKGCH6000170Total0GERALD HOOD
02/13/2008C003CAOakwoodATKGCH13101304Total0GERALD HOOD
02/13/2008C103CBOakwoodATKGCH5000131Total1GERALD HOOD
02/14/2008C003CAOakwoodATKGCH9000482Total2GERALD HOOD
02/14/2008C103CBOakwoodATKGCH7020380Total0GERALD HOOD
02/15/2008C003CAOakwoodATKGCH9110321Total1GERALD HOOD
02/15/2008C103CBOakwoodATKGCH14110520Total0GERALD HOOD
02/16/2008C003CAOakwoodATKGCH12110413Total2GERALD HOOD
02/16/2008C103CBOakwoodATKGCH5010130Total1GERALD HOOD
02/19/2008C003CAOakwoodATKGCH13010421Total1GERALD HOOD
02/19/2008C103CBOakwoodATKGCH9010272Total2GERALD HOOD
02/20/2008C003CAOakwoodATKGCH9021313Total0GERALD HOOD
02/20/2008C103CBOakwoodATKGCH11120450Total0GERALD HOOD
02/21/2008C003CAOakwoodATKGCH11100391Total0GERALD HOOD
02/21/2008C103CBOakwoodATKGCH9000300Total1GERALD HOOD
02/22/2008C003CAOakwoodATKGCH9110251Total1GERALD HOOD
02/23/2008C003CAOakwoodATKGCH11010561Total1GERALD HOOD
02/23/2008C103CBOakwoodATKGCH7000400Total0GERALD HOOD
02/26/2008C003CAOakwoodATKGCH6000331Total2GERALD HOOD
02/26/2008C103CBOakwoodATKGCH14020341Total0GERALD HOOD
02/27/2008C003CAOakwoodATKGCH13000452Total1GERALD HOOD
02/27/2008C103CBOakwoodATKGCH6000310Total0GERALD HOOD
02/28/2008C003CAOakwoodATKGCH11000200Total0GERALD HOOD
02/28/2008C103CBOakwoodATKGCH7000367Total1GERALD HOOD
02/29/2008C003CAOakwoodATKGCH5020340Total0GERALD HOOD
02/29/2008C103CBOakwoodATKGCH12020322Total1GERALD HOOD
03/01/2008C003CAOakwoodCUBGCH10000410Total1GERALD HOOD
03/01/2008C103CBOakwoodCUBGCH6020283Total3GERALD HOOD
03/05/2008C003CAOakwoodATKGCH10010380Total0GERALD HOOD
03/05/2008C103CBOakwoodATKGCH8000330Total0GERALD HOOD
03/06/2008C003CAOakwoodATKGCH12000381Total1GERALD HOOD
03/06/2008C103CBOakwoodATKGCH7010200Total0GERALD HOOD
03/13/2008C003CAOakwoodATKGCH13020401Total0GERALD HOOD
03/13/2008C103CBOakwoodATKGCH7010551Total1GERALD HOOD
03/14/2008C003CAOakwoodATKGCH10101230Total0GERALD HOOD
03/14/2008C103CBOakwoodATKGCH7010401Total0GERALD HOOD
03/15/2008C003CAOakwoodATKGCH7001152Total2GERALD HOOD
03/15/2008C103CBOakwoodATKGCH10010373Total1GERALD HOOD
03/18/2008C003CAOakwoodATKGCH8000160Total0GERALD HOOD
03/18/2008C103CBOakwoodATKGCH12010371Total1GERALD HOOD
03/19/2008C003CAOakwoodATKGCH9010476Total0GERALD HOOD
03/19/2008C103CBOakwoodATKGCH9100260Total0GERALD HOOD
03/21/2008C003CAOakwoodATKGCH11220420Total0GERALD HOOD
03/25/2008C003CAOakwoodATKGCH9300350Total0GERALD HOOD
03/25/2008C103CBOakwoodATKGCH8210331Total1GERALD HOOD
03/26/2008C001CAOakwoodTAMGCH12100364Total2GERALD HOOD
03/27/2008C003CAOakwoodATKGCH11110400Total0GERALD HOOD
03/27/2008C103CBOakwoodATKGCH7010201Total1GERALD HOOD
04/01/2008C003CAOakwoodATKGCH14101462Total0GERALD HOOD
04/01/2008C103CBOakwoodATKGCH3000100Total0GERALD HOOD
04/02/2008C003CAOakwoodATKGCH13230490Total0GERALD HOOD
04/03/2008C003CAOakwoodATKGCH13200580Total0GERALD HOOD
04/03/2008C103CBOakwoodATKGCH3000120Total0GERALD HOOD
04/04/2008C003CAOakwoodATKGCH12110330Total0GERALD HOOD
04/04/2008C103CBOakwoodATKGCH12020431Total1GERALD HOOD
04/08/2008C003CAOakwoodATKGCH10120421Total0GERALD HOOD
04/08/2008C103CBOakwoodATKGCH400090Total0GERALD HOOD
04/10/2008C003CAOakwoodATKGCH10011362Total0GERALD HOOD
04/10/2008C103CBOakwoodATKGCH6010210Total0GERALD HOOD
04/11/2008C003CAOakwoodATKGCH11100450Total0GERALD HOOD
04/11/2008C103CBOakwoodATKGCH9020331Total1GERALD HOOD
04/12/2008C003CAOakwoodATKGCH13020310Total0GERALD HOOD
04/12/2008C103CBOakwoodATKGCH2000172Total1GERALD HOOD
04/15/2008C003CAOakwoodATKGCH11100374Total0GERALD HOOD
04/15/2008C103CBOakwoodATKGCH7000280Total0GERALD HOOD
04/16/2008C003CAOakwoodATKGCH8000320Total0GERALD HOOD
04/16/2008C103CBOakwoodATKGCH11110491Total1GERALD HOOD
04/17/2008C003CAOakwoodATKGCH9010382Total2GERALD HOOD
04/17/2008C103CBOakwoodATKGCH10011421Total0GERALD HOOD
04/18/2008C003CAOakwoodATKGCH9020325Total1GERALD HOOD
04/18/2008C103CBOakwoodATKGCH10120430Total0GERALD HOOD
04/19/2008C003CAOakwoodATKGCH10010313Total2GERALD HOOD
04/19/2008C103CBOakwoodATKGCH8000253Total4GERALD HOOD
04/22/2008C003CAOakwoodATKGCH13110345Total0GERALD HOOD
04/22/2008C103CBOakwoodATKGCH11101343Total0GERALD HOOD
04/23/2008C003CAOakwoodATKGCH18100521Total1GERALD HOOD
04/23/2008C103CBOakwoodATKGCH11120406Total0GERALD HOOD
04/25/2008C003CAOakwoodATKGCH10010300Total0GERALD HOOD
04/25/2008C103CBOakwoodATKGCH9100301Total1GERALD HOOD
04/29/2008C003CAOakwoodATKGCH8000150Total1GERALD HOOD
04/29/2008C103CBOakwoodATKGCH11120350Total0GERALD HOOD
05/02/2008C003CAOakwoodATKGCH8020381Total1GERALD HOOD
05/02/2008C103CBOakwoodATKGCH10110340Total0GERALD HOOD
05/06/2008C003CAOakwoodATKGCH14020300Total0GERALD HOOD
05/06/2008C103CBOakwoodATKGCH6010581Total1GERALD HOOD
05/07/2008C003CAOakwoodATKGCH9110240Total0GERALD HOOD
05/07/2008C103CBOakwoodATKGCH8000291Total1GERALD HOOD
05/08/2008C003CAOakwoodATKGCH6100361Total1GERALD HOOD
05/08/2008C103CBOakwoodATKGCH9120400Total0GERALD HOOD
05/09/2008C003CAOakwoodATKGCH12100410Total1GERALD HOOD
05/16/2008C003CAOakwoodATKGCH10110460Total0GERALD HOOD
05/16/2008C103CBOakwoodATKGCH7000201Total1GERALD HOOD
05/17/2008C003CAOakwoodCUBGCH5000150Total0GERALD HOOD
05/17/2008C103CBOakwoodCUBGCH8000142Total1GERALD HOOD
05/20/2008C003CAOakwoodATKGCH7000311Total0GERALD HOOD
05/20/2008C103CBOakwoodATKGCH14020310Total1GERALD HOOD
05/22/2008C003CAOakwoodATKGCH11010360Total0GERALD HOOD
05/22/2008C103CBOakwoodATKGCH12020500Total0GERALD HOOD
05/23/2008C003CAOakwoodATKGCH9110360Total0GERALD HOOD
05/23/2008C103CBOakwoodATKGCH13000390Total1GERALD HOOD
05/24/2008C003CAOakwoodATKGCH70003113Total1GERALD HOOD
05/24/2008C103CBOakwoodATKGCH6000280Total0GERALD HOOD
05/28/2008C003CAOakwoodATKGCH14210472Total1GERALD HOOD
05/28/2008C103CBOakwoodATKGCH8010440Total0GERALD HOOD
05/29/2008C003CAOakwoodATKGCH15130302Total1GERALD HOOD
05/29/2008C103CBOakwoodATKGCH12020350Total0GERALD HOOD
05/30/2008C003CAOakwoodATKGCH10011392Total0GERALD HOOD
05/30/2008C103CBOakwoodATKGCH10000541Total1GERALD HOOD
05/31/2008C003CAOakwoodATKGCH13000602Total2GERALD HOOD
05/31/2008C103CBOakwoodATKGCH7020350Total0GERALD HOOD
06/03/2008C003CAOakwoodATKGCH14100371Total1GERALD HOOD
06/03/2008C103CBOakwoodATKGCH9000491Total1GERALD HOOD
06/05/2008C003CAOakwoodATKGCH8110550Total0GERALD HOOD
06/05/2008C103CBOakwoodATKGCH13001413Total0GERALD HOOD
06/06/2008C003CAOakwoodATKGCH6000303Total2GERALD HOOD
06/06/2008C103CBOakwoodATKGCH9200302Total1GERALD HOOD
06/07/2008C003CAOakwoodATKGCH9000524Total1GERALD HOOD
06/07/2008C103CBOakwoodATKGCH6010320Total0GERALD HOOD
06/10/2008C003CAOakwoodATKGCH6000220Total0GERALD HOOD
06/10/2008C103CBOakwoodATKGCH11000424Total3GERALD HOOD
06/11/2008C003CAOakwoodATKGCH12110352Total2GERALD HOOD
06/11/2008C103CBOakwoodATKGCH12020280Total1GERALD HOOD
06/12/2008C003CAOakwoodATKGCH11020340Total0GERALD HOOD
06/12/2008C103CBOakwoodATKGCH10100311Total1GERALD HOOD
06/13/2008C003CAOakwoodATKGCH12120424Total2GERALD HOOD
06/13/2008C103CBOakwoodATKGCH9010340Total0GERALD HOOD
06/14/2008C003CAOakwoodATKGCH11010464Total2GERALD HOOD
06/14/2008C103CBOakwoodATKGCH6000110Total3GERALD HOOD
06/17/2008C003CAOakwoodATKGCH8100600Total0GERALD HOOD
06/17/2008C103CBOakwoodATKGCH11000385Total4GERALD HOOD
06/18/2008C003CAOakwoodATKGCH5000310Total0GERALD HOOD
06/18/2008C103CBOakwoodATKGCH9010272Total1GERALD HOOD
06/19/2008C003CAOakwoodATKGCH13000490Total0GERALD HOOD
06/19/2008C103CBOakwoodATKGCH5011213Total0GERALD HOOD
06/20/2008C003CAOakwoodATKGCH11010220Total1GERALD HOOD
06/20/2008C103CBOakwoodATKGCH9200380Total0GERALD HOOD
06/21/2008C003CAOakwoodATKGCH10000452Total1GERALD HOOD
06/21/2008C103CBOakwoodATKGCH5000150Total1GERALD HOOD
06/24/2008C003CAOakwoodATKGCH12010381Total1GERALD HOOD
06/24/2008C103CBOakwoodATKGCH8001211Total0GERALD HOOD
06/27/2008C003CAOakwoodATKGCH11100412Total1GERALD HOOD
06/27/2008C103CBOakwoodATKGCH10100380Total0GERALD HOOD
06/28/2008C003CAOakwoodBBMGCH8100515Total4GERALD HOOD
06/28/2008C103CBOakwoodBBMGCH8010352Total1GERALD HOOD
07/01/2008C003CAOakwoodATKGCH12121367Total1GERALD HOOD
07/01/2008C103CBOakwoodATKGCH10010350Total0GERALD HOOD
07/02/2008C003CAOakwoodATKGCH10110521Total1GERALD HOOD
07/02/2008C103CBOakwoodATKGCH8000359Total1GERALD HOOD
07/08/2008C003CAOakwoodATKGCH10030331Total1GERALD HOOD
07/10/2008C003CAOakwoodATKGCH9020500Total0GERALD HOOD
07/10/2008C103CBOakwoodATKGCH12010480Total0GERALD HOOD
07/11/2008C003CAOakwoodATKGCH16130522Total2GERALD HOOD
07/11/2008C103CBOakwoodATKGCH10110500Total0GERALD HOOD
07/12/2008C003CAOakwoodATKGCH12030570Total0GERALD HOOD
07/12/2008C103CBOakwoodATKGCH7020261Total0GERALD HOOD
07/16/2008C003CAOakwoodATKGCH14010401Total1GERALD HOOD
07/16/2008C103CBOakwoodATKGCH12001486Total2GERALD HOOD
07/17/2008C003CAOakwoodATKGCH9100371Total1GERALD HOOD
07/17/2008C103CBOakwoodATKGCH9000180Total0GERALD HOOD
07/18/2008C003CAOakwoodATKGCH9020512Total2GERALD HOOD
07/18/2008C103CBOakwoodATKGCH10130352Total0GERALD HOOD
07/19/2008C003CAOakwoodATKGCH13000552Total2GERALD HOOD
07/22/2008C003CAOakwoodATKGCH11110490Total0GERALD HOOD
07/22/2008C103CBOakwoodATKGCH7110292Total2GERALD HOOD
07/24/2008C003CAOakwoodATKGCH4000230Total0GERALD HOOD
07/24/2008C103CBOakwoodATKGCH9020142Total2GERALD HOOD
07/25/2008C003CAOakwoodATKGCH10030410Total0GERALD HOOD
07/25/2008C103CBOakwoodATKGCH7000320Total0GERALD HOOD
07/26/2008C003CAOakwoodATKGCH8200330Total0GERALD HOOD
07/26/2008C103CBOakwoodATKGCH10030320Total0GERALD HOOD
07/29/2008C003CAOakwoodATKGCH6010133Total2GERALD HOOD
07/29/2008C103CBOakwoodATKGCH13010490Total0GERALD HOOD
07/30/2008C003CAOakwoodATKGCH11000384Total1GERALD HOOD
07/31/2008C003CAOakwoodATKGCH13110442Total2GERALD HOOD
07/31/2008C103CBOakwoodATKGCH9110472Total2GERALD HOOD
08/02/2008C003CAOakwoodATKGCH6011342Total0GERALD HOOD
08/02/2008C103CBOakwoodATKGCH9010242Total2GERALD HOOD
08/05/2008C003CAOakwoodATKGCH12020330Total1GERALD HOOD
08/06/2008C003CAOakwoodATKGCH7001191Total1GERALD HOOD
08/06/2008C103CBOakwoodATKGCH9000290Total0GERALD HOOD
08/07/2008C003CAOakwoodATKGCH10100310Total0GERALD HOOD
08/07/2008C103CBOakwoodATKGCH9110458Total2GERALD HOOD
08/09/2008C004CAOakwoodCUBGCH5010450Total0GERALD HOOD
08/09/2008X104CBOakwoodCUBGCH9110490Total1GERALD HOOD
08/12/2008C003CAOakwoodATKGCH11000310Total0GERALD HOOD
08/12/2008C103CBOakwoodATKGCH11021271Total0GERALD HOOD
08/14/2008C003CAOakwoodATKGCH12010370Total0GERALD HOOD
08/14/2008C103CBOakwoodATKGCH7010380Total0GERALD HOOD
08/15/2008C003CAOakwoodATKGCH7000550Total0GERALD HOOD
08/15/2008C103CBOakwoodATKGCH14120534Total3GERALD HOOD
08/16/2008C003CAOakwoodATKGCH8010425Total1GERALD HOOD
08/16/2008C103CBOakwoodATKGCH11000381Total1GERALD HOOD
08/19/2008C003CAOakwoodATKGCH10100393Total0GERALD HOOD
08/19/2008C103CBOakwoodATKGCH9100384Total1GERALD HOOD
08/20/2008C003CAOakwoodATKGCH16000412Total1GERALD HOOD
08/20/2008C103CBOakwoodATKGCH7000380Total0GERALD HOOD
08/21/2008C003CAOakwoodATKGCH12010360Total0GERALD HOOD
08/21/2008C103CBOakwoodATKGCH9000200Total0GERALD HOOD
08/22/2008C003CAOakwoodATKGCH13110560Total0GERALD HOOD
08/26/2008C003CAOakwoodATKGCH9011223Total1GERALD HOOD
08/26/2008C103CBOakwoodATKGCH11110520Total0GERALD HOOD
10/25/2008C003CAOakwoodATKGCH9110530Total0GERALD HOOD
Then I get the vales that I actually want, BETWEEN the start and end date parameters...
April 22, 2009 at 2:22 pm
The problem lies in the where clause...
where delivery_date between @StartDate and @EndDate and
driver = e.emp_cd and
(driver = @Driver or '%' = @Driver) or helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
The or clause is not within parenthesis so the result will return records where
helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 2:37 pm
slange (4/22/2009)
The problem lies in the where clause...
where delivery_date between @StartDate and @EndDate and
driver = e.emp_cd and
(driver = @Driver or '%' = @Driver) or helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
The or clause is not within parenthesis so the result will return records where
helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
I'm sorry... I am a bit new to SQL... Cold you show me please?
Thank you.
April 22, 2009 at 2:43 pm
I don't know exactly what the requirements are but I will try...
where delivery_date between @StartDate and @EndDate and
driver = e.emp_cd and
(driver = @Driver or '%' = @Driver) or helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
It is hard to determine what criteria but you have but the or helper = e.emp_cd
The query will return everything that meets the criteria to the left of this OR to the right of it.
You likely need to add another set of parenthesis. I'm pretty sure I am not making this any clearer. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 2:53 pm
Just judging by the format of the code I will try this.
where delivery_date between @StartDate and @EndDate and
driver = e.emp_cd and
(
(driver = @Driver or '%' = @Driver) or helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
)
Notice the extra () around the bottom line.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 3:04 pm
slange (4/22/2009)
Just judging by the format of the code I will try this.
where delivery_date between @StartDate and @EndDate and
driver = e.emp_cd and
(
(driver = @Driver or '%' = @Driver) or helper = e.emp_cd and (helper = @Helper or '%' = @Helper)
)
Still not working... What I really want to do is have it AND rather than OR... For example:
where delivery_date between @StartDate and @EndDate
AND driver = e.emp_cd
AND (driver = @Driver or '%' = @Driver)
AND helper = e.emp_cd
AND (helper = @Helper or '%' = @Helper)
But it either returns nothing or returns the wrong dates or whatever... this is VERY weird.
April 22, 2009 at 3:10 pm
if you can post the table structure and some sample data i will take a look at it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 4:05 pm
This really comes down to how the where clause is formed. You have the following:
where delivery_date BETWEEN '04/15/2009' and '04/22/2009' and
driver = e.emp_cd and
(driver = 'ATK' or '%' = 'ATK') or helper = e.emp_cd and (helper = 'GCH' or '%' = 'GCH')
The problem in the above is with the OR and how the conditions are paired. Try the following:
WHERE delivery_date >= @Startdate
AND delivery_date < @EndDate
AND (
(driver = e.emp_cd AND (driver = @driver OR @driver = '%'))
OR (helper = e.emp_cd AND (helper = @helper OR @helper = '%'))
)
With the above we have grouped the criteria so that either the driver criteria is true, or the helper criteria is true, and if one of those is true then we'll return the row.
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
April 23, 2009 at 3:38 am
I think The condition should do it.
where
(delivery_date between @StartDate and @EndDate)
And
(
(driver = e.emp_cd and (driver = @Driver or '%' = @Driver))
or
(helper = e.emp_cd and (helper = @Helper or '%' = @Helper))
)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply