April 9, 2015 at 8:07 am
I’m trying to do a SQL statement in our application and having some issues.
What I’m struggling with is a compare on the alertmsgdt (Date field) + alertmsgtm (char field for Time) field in the fforder table to the lastprdate (Date field) + lastprtime (char field for Time) field in the acoalert table.
I can get it to work by doing the following, but I wonder if there is a better solution.
SELECT TOP 1000 f.foordno AS "Order #", f.foprogno AS "Program #", f.alertmssg AS "Pick #s" FROM fforder f WHERE f.alertmssg <> '' and
(
(f.alertmsgdt > (select a.lastprdate FROM acoalert a where a.coalertid=3000) )
or
( f.alertmsgdt = (select a.lastprdate FROM acoalert a where a.coalertid=3000) and f.alertmsgtm > (select a.lastprtime FROM acoalert a where a.coalertid=3000 ) )
) ORDER BY foordno
I don’t like having to do a select from the acoalert database three times, but it was the only way that I could get this to work.
I wanted to combine the Date and Time fields in to one as a DateTime field but I couldn’t get that to work. I tried Cast, Convert.
Maybe it was just a simple syntax thing or maybe we have limitations in our ProfitMaker code. I’m not sure.
I do have it working and there would only ever be 1 record in the acoalert table, so that wouldn’t be too big of an issue but there could be thousands of records in the fforder table so I would like to optimize this as much as possible.
April 9, 2015 at 8:23 am
Zohaib Anwar (4/9/2015)
I’m trying to do a SQL statement in our application and having some issues.What I’m struggling with is a compare on the alertmsgdt (Date field) + alertmsgtm (char field for Time) field in the fforder table to the lastprdate (Date field) + lastprtime (char field for Time) field in the acoalert table.
I can get it to work by doing the following, but I wonder if there is a better solution.
SELECT TOP 1000 f.foordno AS "Order #", f.foprogno AS "Program #", f.alertmssg AS "Pick #s" FROM fforder f WHERE f.alertmssg <> '' and
(
(f.alertmsgdt > (select a.lastprdate FROM acoalert a where a.coalertid=3000) )
or
( f.alertmsgdt = (select a.lastprdate FROM acoalert a where a.coalertid=3000) and f.alertmsgtm > (select a.lastprtime FROM acoalert a where a.coalertid=3000 ) )
) ORDER BY foordno
I don’t like having to do a select from the acoalert database three times, but it was the only way that I could get this to work.
I wanted to combine the Date and Time fields in to one as a DateTime field but I couldn’t get that to work. I tried Cast, Convert.
Maybe it was just a simple syntax thing or maybe we have limitations in our ProfitMaker code. I’m not sure.
I do have it working and there would only ever be 1 record in the acoalert table, so that wouldn’t be too big of an issue but there could be thousands of records in the fforder table so I would like to optimize this as much as possible.
This is an excellent opportunity to use APPLY. You can read more about it by following the links in my signature. I renamed your columns because it is just painful to work with aliases that you have to escape. If you really want those column names you should wrap them in [] instead of "".
SELECT TOP 1000 f.foordno AS OrderNumber
, f.foprogno AS ProgramNumber
, f.alertmssg AS Picks
FROM fforder f
cross apply
(
select lastprdate
FROM acoalert
where coalertid = 3000
) a
WHERE f.alertmssg <> ''
and
(
f.alertmsgdt > a.lasstprdate
or
(
f.alertmsgdt = a.lasstprdate
and
f.alertmsgtm > a.lasstprdate
)
)
ORDER BY foordno
_______________________________________________________________
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 9, 2015 at 8:30 am
Thanks for the reply Sean
Can I do it following way ??
WITH ACOALERTCTE
AS (
SELECT a.lastprdate
,a.lastprtime
FROM acoalert a
WHERE a.coalertid = 3000
)
SELECT TOP 1000 f.foordno AS "Order #"
,f.foprogno AS "Program #"
,f.alertmssg AS "Pick #s"
FROM fforder f
WHERE f.alertmssg <> ''
AND f.alertmsgdt > ACOALERTCTE.lastprdate
OR f.alertmsgdt = ACOALERTCTE.lastprdate
AND f.alertmsgtm > ACOALERTCTE.lastprtime
April 9, 2015 at 8:42 am
Zohaib Anwar (4/9/2015)
Thanks for the reply SeanCan I do it following way ??
WITH ACOALERTCTE
AS (
SELECT a.lastprdate
,a.lastprtime
FROM acoalert a
WHERE a.coalertid = 3000
)
SELECT TOP 1000 f.foordno AS "Order #"
,f.foprogno AS "Program #"
,f.alertmssg AS "Pick #s"
FROM fforder f
WHERE f.alertmssg <> ''
AND f.alertmsgdt > ACOALERTCTE.lastprdate
OR f.alertmsgdt = ACOALERTCTE.lastprdate
AND f.alertmsgtm > ACOALERTCTE.lastprtime
Sure that works too with one minor change. You MUST stop using "" to wrap your column names. Using "" has been deprecated and is really hard to read in my opinion. Use the ANSI standard square brackets instead.
WITH ACOALERTCTE
AS (
SELECT a.lastprdate
,a.lastprtime
FROM acoalert a
WHERE a.coalertid = 3000
)
SELECT TOP 1000 f.foordno AS [Order #]
,f.foprogno AS [Program #]
,f.alertmssg AS [Pick #s]
FROM fforder f
WHERE f.alertmssg <> ''
AND f.alertmsgdt > ACOALERTCTE.lastprdate
OR f.alertmsgdt = ACOALERTCTE.lastprdate
AND f.alertmsgtm > ACOALERTCTE.lastprtime
_______________________________________________________________
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 9, 2015 at 9:02 am
Sean Lange (4/9/2015)
Zohaib Anwar (4/9/2015)
Thanks for the reply SeanCan I do it following way ??
WITH ACOALERTCTE
AS (
SELECT a.lastprdate
,a.lastprtime
FROM acoalert a
WHERE a.coalertid = 3000
)
SELECT TOP 1000 f.foordno AS "Order #"
,f.foprogno AS "Program #"
,f.alertmssg AS "Pick #s"
FROM fforder f
WHERE f.alertmssg <> ''
AND f.alertmsgdt > ACOALERTCTE.lastprdate
OR f.alertmsgdt = ACOALERTCTE.lastprdate
AND f.alertmsgtm > ACOALERTCTE.lastprtime
Sure that works too with one minor change. You MUST stop using "" to wrap your column names. Using "" has been deprecated and is really hard to read in my opinion. Use the ANSI standard square brackets instead.
WITH ACOALERTCTE
AS (
SELECT a.lastprdate
,a.lastprtime
FROM acoalert a
WHERE a.coalertid = 3000
)
SELECT TOP 1000 f.foordno AS [Order #]
,f.foprogno AS [Program #]
,f.alertmssg AS [Pick #s]
FROM fforder f
WHERE f.alertmssg <> ''
AND f.alertmsgdt > ACOALERTCTE.lastprdate
OR f.alertmsgdt = ACOALERTCTE.lastprdate
AND f.alertmsgtm > ACOALERTCTE.lastprtime
I am trying to do it with CTE as mentioned above but not sure why it is not working. I haven't join CTE any where does that work for me in where clause??
April 9, 2015 at 9:08 am
Zohaib Anwar (4/9/2015)
I am trying to do it with CTE as mentioned above but not sure why it is not working. I haven't join CTE any where does that work for me in where clause??
Oops. I didn't look very closely at your post with the cte. I assumed you had tested it and it was working. You can't just reference a cte out of the blue. A cte is just an inline view so you must have it in your query. Since it is just a single row a cross join will work here.
WITH ACOALERTCTE
AS (
SELECT a.lastprdate
,a.lastprtime
FROM acoalert a
WHERE a.coalertid = 3000
)
SELECT TOP 1000 f.foordno AS [Order #]
,f.foprogno AS [Program #]
,f.alertmssg AS [Pick #s]
FROM fforder f
cross join ACOALERTCTE a
WHERE f.alertmssg <> ''
AND f.alertmsgdt > a.lastprdate
OR f.alertmsgdt = a.lastprdate
AND f.alertmsgtm > a.lastprtime
_______________________________________________________________
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply