May 9, 2011 at 9:44 am
I will admit I have always been a brute force programmer. I am self taught and did not learn a lot of correct methods...
I have been fine tuning my skills lately, and I am learning to use SQL joins more now to get performance out of my queries...
I've been doing pretty good until I come across a report I am trying to get working, that is now bombing on sql 2005, that worked on sql 2000.
Could you guys help me assemble these separate queries into one sql statement with joins? I am having problems with needing to pull the top 1 record, to get a date of a flag that was inserted.
#1st statement:
SELECT RecordStatus,ActiveClient,SystemEffective,Firstname,Lastname,MiddleI,SSN,ClientID,City,State,Zip,HomePhone,Address1 FROM Clients WHERE AgentID=20 ORDER BY Lastname,Firstname,MiddleI
Then from that statement I pull these 2 separate queries based on the ClientID of the record above. Each will supply a different value representing a different activity.
SELECT TOP 1 FlagDate FROM Flags WHERE FlagCode=1001 AND ClientID=" & TheRec("ClientID")
SELECT TOP 1 FlagDate FROM Flags WHERE ClientID=" & TheRec("ClientID") & " AND (FlagCode=3001 OR FlagCode=3002) ORDER BY FlagID DESC
I would be pretty happy if I could just get that above combined...
But then It goes even further and executes one more sql query. But, lets start with this and if I can get past this I will see if you guys can help me dive a little further.
I HIGHLY appreciate any help you could give... Thanks.
May 9, 2011 at 9:50 am
FYI... All the joins I put together kept returning NULLS for the flagdates, when I know there is data there. That's why I didnt even include my attempts at the join...
May 9, 2011 at 10:03 am
something like this, maybe?
SELECT
cl.RecordStatus,
cl.ActiveClient,
cl.SystemEffective,
cl.Firstname,
cl.Lastname,
cl.MiddleI,
cl.SSN,
cl.ClientID,
cl.City,
cl.State,
cl.Zip,
cl.HomePhone,
cl.Address1,
fl.FlagDate
FROM Clients cl
LEFT OUTER JOIN
( SELECT
ClientID,
MAX(FlagDate) As FlagDate
FROM Flags
WHERE (FlagCode=3001 OR FlagCode=3002)
GROUP BY ClientID
) fl
ON cl.ClientID = fl.ClientID
WHERE cl.AgentID=20
ORDER BY
cl.Lastname,
cl.Firstname,
cl.MiddleI
Lowell
May 9, 2011 at 10:06 am
Possibly something like that, but keep in mid the 2 statements return 2 different flagdates representing 2 different events... Looks like what you have there will only give me the last date of one situation.
SELECT TOP 1 FlagDate FROM Flags WHERE FlagCode=1001 AND ClientID="&TheRec("ClientID")
^---The date the record was entered
SELECT TOP 1 FlagDate FROM Flags WHERE ClientID="&TheRec("ClientID")&" AND (FlagCode=3001 OR FlagCode=3002) ORDER BY FlagID DESC
^---The last date money was received
May 9, 2011 at 10:15 am
ok just a slight variation:
gotcha, i think this will do it: try the inner query witht he row number and see that it''s returning the client id and the flagdate you were looking for:
SELECT
cl.RecordStatus,
cl.ActiveClient,
cl.SystemEffective,
cl.Firstname,
cl.Lastname,
cl.MiddleI,
cl.SSN,
cl.ClientID,
cl.City,
cl.State,
cl.Zip,
cl.HomePhone,
cl.Address1,
bg.FlagDate as EnteredDate
fl.FlagDate as MoneyReceived
FROM Clients cl
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=1001)
) bg
ON cl.ClientID = bg.ClientID
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY ClientID,FlagCode DESC) AS RW,
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=3001 OR FlagCode=3002)
)myAlias
WHERE RW = 1
) fl
ON cl.ClientID = fl.ClientID
WHERE cl.AgentID=20
ORDER BY
cl.Lastname,
cl.Firstname,
cl.MiddleI
[/code]
Lowell
May 9, 2011 at 10:26 am
I do appreciate your help...
When running this query SQL 2005 gives this error.
Error in SELECT clause: expression near 'fl'.
The OVER SQL construct or statement is not supported.
May 9, 2011 at 10:29 am
bg.FlagDate as EnteredDate,
Missing the comma at the end of the line.
I think Lowell's having issues with that today :hehe:.
May 9, 2011 at 10:29 am
the database compatibility is set to 80 instead of 90, or you posted a SQL2000 question in the 2005 forum;
the ROW_NUMBER function was introduced in 2005, so i assumed you can use it.
Lowell
May 9, 2011 at 10:31 am
Ninja's_RGR'us (5/9/2011)
bg.FlagDate as EnteredDate,Missing the comma at the end of the line.
I think Lowell's having issues with that today :hehe:.
man that's for sure; i im flubbing up syntax a lot today; thanks!
Lowell
May 9, 2011 at 10:31 am
Sorry, didnt realize this would be a SQL 2000 since I have 80 compatibility on...
And that is am awesome catch on the comma... I never would have found that.
Should I repost in that forum?
May 9, 2011 at 10:35 am
Lowell (5/9/2011)
the database compatibility is set to 80 instead of 90, or you posted a SQL2000 question in the 2005 forum;the ROW_NUMBER function was introduced in 2005, so i assumed you can use it.
R u sure about that??
create DATABASE test
GO
EXEC sp_dbcmptlevel test, 80;
GO
USE test
GO
SELECT object_name(object_id), name, ROW_NUMBER() OVER(PARTITION BY object_id ORDER BY name) As Colid2 FROM sys.columns
GO
use master
GO
DROP DATABASE test
GO
May 9, 2011 at 10:35 am
mikeincape (5/9/2011)
Sorry, didnt realize this would be a SQL 2000 since I have 80 compatibility on...And that is am awesome catch on the comma... I never would have found that.
Should I repost in that forum?
no, much better to keep everything in a single thread;
so there's no way to change compatibility on your database? it is 11 years plus since SQL 2000 was launched...it might be ok to use some of the newer features available.
I'll look and see if we can sue a CASE statement to replace row number...
Lowell
May 9, 2011 at 10:36 am
Lowell (5/9/2011)
Ninja's_RGR'us (5/9/2011)
bg.FlagDate as EnteredDate,Missing the comma at the end of the line.
I think Lowell's having issues with that today :hehe:.
man that's for sure; i im flubbing up syntax a lot today; thanks!
Only if you are using sql 2000 as the server. You can run that query in sql 2005 with compatibility level 80. (See previous script)
May 9, 2011 at 10:36 am
I just looked and I am in 90 compatibility mode.
May 9, 2011 at 10:38 am
As a general rule (with exceptions), you are able to use the new t-sql functions even for lower compatibility levels.
It's the engine that needs to understand the query, not the DB.
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply