data not selecting on current date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

data not selecting on current date

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-10-27 : 18:08:49

the following is the sql being executed in my Crystal report.
There seems to be an issue with the same date request. Sometimes it shows data, other times not. We have data every day, we are a mass market company. Is there anything i can do for the sql to do select when from and to dates are the same?

SELECT "OEHIS1"."ODORD#", "OEHIS1"."ODORDT", "OEHIS1"."ODNTU$", "OEHIS1"."ODSHP#", "ICPRT1"."IARC11", "OEHIS1"."ODORDD", "ICPRT1"."IARCC4", "OEHIS1"."ODQTY#", "OEHIS1"."ODRQSD", "MFHHMH"."MHAWGT", "OEHIS1"."ODPRT#", "OEHIS1"."ODPRLC"
FROM ("S10M10"."ASTCCDTA"."EODDETAILS" "OEHIS1" INNER JOIN "S10M10"."ASTDTA"."ICPRT1" "ICPRT1" ON "OEHIS1"."ODPRT#"="ICPRT1"."IAPRT#") INNER JOIN "S10M10"."DLIB"."MFHHMH" "MFHHMH" ON "OEHIS1"."ODORD#"="MFHHMH"."MHORDP"
WHERE "OEHIS1"."ODPRT#" NOT LIKE 'FR%' AND "ICPRT1"."IARCC4"='FIN' AND "OEHIS1"."ODORDD"=20141027 AND NOT ("ICPRT1"."IARC11"='' OR "ICPRT1"."IARC11"='DRS') AND ("OEHIS1"."ODORDT"='CSA' OR "OEHIS1"."ODORDT"='CUS' OR "OEHIS1"."ODORDT"='INT' OR "OEHIS1"."ODORDT"='N4H' OR "OEHIS1"."ODORDT"='NFM' OR "OEHIS1"."ODORDT"='RTR') AND NOT ("OEHIS1"."ODPRLC" LIKE 'CMY%' OR "OEHIS1"."ODPRLC" LIKE 'DSC%' OR "OEHIS1"."ODPRLC" LIKE 'LGC%' OR "OEHIS1"."ODPRLC" LIKE 'X%')
ORDER BY "ICPRT1"."IARC11"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 18:15:13

"OEHIS1"."ODORDD">=20141027 AND "OEHIS1"."ODORDD"<20141028

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-10-27 : 18:18:14

ok thanks, is the issue that the date is 'character ' not number or date?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 18:28:17

I wouldn't modify the report to make the user also select the next date. Change the report so that it automatically does it. I don't have experience with Crystal Reports, so I can't help on the specifics.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 18:42:27

quote:
Originally posted by AdamWest

ok thanks, is the issue that the date is 'character ' not number or date?

You edited your last post instead of creating a new one.

I have no idea as to your new question. I don't have experience with Crystal Reports. In Management Studio, it would be like this:

where datecol >= '10/27/2014' and datecol < '10/28/2014'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources