May 14, 2009 at 12:56 pm
Hello,
I'm looking for some help please....
I have a script in which I want to insert a row_number function which has multiple joins. I was having some trouble with that so I broke it down into one statement so my simpleton mind could understand and it worked beautifully. Now, I would like to insert that into my original statement and I do fine until I get to the joins and then I get a bit confused.
Here's my original working statement:
SELECTCRM_StatusCodes.TRANSACTION_ID as [Service Order ID]
, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION [Function]
, CRM_StatusCodes.SYSTEM_STATUS as [Confirmation Status]
, CRM_Partners.ADDRESS
, CRM_PartsLabor.DESCRIPTION
, CRM_orders.created_at as [Back Log Date]
, crm_statuscodes.end_date as [LastStatus]
, count(CRM_StatusCodes.TRANSACTION_ID) as [Service Order Count] /* this displays all transactions associated with the FSE */
FROMCRM_Partners
INNER JOINCRM_Orders ON CRM_Partners.PARTNER_ID = CRM_Orders.PARTNER_ID
INNER JOINCRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOINCRM_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID
WHERE
CRM_StatusCodes.SYSTEM_STATUS in
('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',
'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',
'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',
'In Process','Open')
AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
AND CRM_PartsLabor.ordered_prod IN ('FSE_LABOR')
AND CRM_StatusCodes.TRANSACTION_ID not like ('9%')
AND cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())
GROUP BY
CRM_StatusCodes.TRANSACTION_ID
, CRM_Partners.ADDRESS, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION
, CRM_StatusCodes.SYSTEM_STATUS
, CRM_orders.created_at
, CRM_PartsLabor.DESCRIPTION
, crm_statuscodes.end_date
And here's the row_number statement:
select
transaction_ID
, end_date
from
(select transaction_ID, end_date,
row_number () over (partition by transaction_ID order by end_date desc) as recid
from
crm_statuscodes
) as d
where recid=1
I'm basically looking for the stuff after the 'from', of course.
For some reason I seem to have so much trouble when I get into doing nested statements. I don't know whether to do it after the 'select' or in my 'where' clause.
I've never had any formal sql training. I just try to read books, do many google searches and follow others example. So I'm really appreciative of the help I get here on this forum! 🙂
Thank you in advance!! Michelle
May 14, 2009 at 1:10 pm
I guess I'm not clear on what you're using the row number for. I see you limiting it to the first row, but I'm not sure what you're trying to do after that. Are you joining that to all the rest of the first query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2009 at 1:25 pm
Yes, that's exactly what I want to do. I would like to add my row_number () to my first script.
I get multiple duplicate rows but with different statuses. So essentially what I want to do is take the last status that was posted.
Here is an example:
Service Order IDConfirmation StatusLastStatus
3000004506Confirmed 2009-05-10 00:37:11.000
3000004506Released 2009-05-09 23:45:53.000
I only want to display the first line that says confirmed because it has a later date.
I hope I'm being more clear.....
Thanks! Michelle
May 14, 2009 at 2:35 pm
Will this do what you need, or am I getting this backwards?
;with
CTEMain as
(SELECT CRM_StatusCodes.TRANSACTION_ID as [Service Order ID]
, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION [Function]
, CRM_StatusCodes.SYSTEM_STATUS as [Confirmation Status]
, CRM_Partners.ADDRESS
, CRM_PartsLabor.DESCRIPTION
, CRM_orders.created_at as [Back Log Date]
, crm_statuscodes.end_date as [LastStatus]
, count(CRM_StatusCodes.TRANSACTION_ID) as [Service Order Count] /* this displays all transactions associated with the FSE */
FROM CRM_Partners
INNER JOIN CRM_Orders ON CRM_Partners.PARTNER_ID = CRM_Orders.PARTNER_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID
WHERE
CRM_StatusCodes.SYSTEM_STATUS in
('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',
'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',
'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',
'In Process','Open')
AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
AND CRM_PartsLabor.ordered_prod IN ('FSE_LABOR')
AND CRM_StatusCodes.TRANSACTION_ID not like ('9%')
AND cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())
GROUP BY
CRM_StatusCodes.TRANSACTION_ID
, CRM_Partners.ADDRESS, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION
, CRM_StatusCodes.SYSTEM_STATUS
, CRM_orders.created_at
, CRM_PartsLabor.DESCRIPTION
, crm_statuscodes.end_date),
CTE2 as
(select *,
row_number() over (partition by [Service Order ID] order by [LastStatus]) as Row
from CTEMain)
select *
from CTE2
where Row = 1;
Instead of nesting them, I'm using CTEs (Common Table Expressions), because I think they're a lot easier to read.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2009 at 2:42 pm
Hi
I would use a CTE to encapsulate the CRM_STATUSCODES and join it instead of the table.
; WITH
status_codes (TransactionId, End_Date, System_Status, RecId) AS
(
select
transaction_ID,
end_date,
System_Status,
row_number () over (partition by transaction_ID order by end_date desc) as recid
from crm_statuscodes
WHERE
SYSTEM_STATUS in
('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',
'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',
'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',
'In Process','Open')
)
SELECT status_codes.TRANSACTION_ID as [Service Order ID]
, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION [Function]
, status_codes.SYSTEM_STATUS as [Confirmation Status]
, CRM_Partners.ADDRESS
, CRM_PartsLabor.DESCRIPTION
, CRM_orders.created_at as [Back Log Date]
, status_codes.end_date as [LastStatus]
, count(status_codes.TRANSACTION_ID) as [Service Order Count] /* this displays all transactions associated with the FSE */
FROM CRM_Partners
INNER JOIN CRM_Orders ON CRM_Partners.PARTNER_ID = CRM_Orders.PARTNER_ID
INNER JOIN status_codes ON CRM_Orders.TRANSACTION_ID = status_codes.TRANSACTION_ID
INNER JOIN CRM_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID
WHERE
CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
AND CRM_PartsLabor.ordered_prod IN ('FSE_LABOR')
AND status_codes.TRANSACTION_ID not like ('9%')
AND status_codes.RecId = 1
AND cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())
GROUP BY
status_codes.TRANSACTION_ID
, CRM_Partners.ADDRESS, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION
, status_codes.SYSTEM_STATUS
, CRM_orders.created_at
, CRM_PartsLabor.DESCRIPTION
, status_codes.end_date
Greets
Flo
May 14, 2009 at 2:47 pm
Wow, Holy Snap! I think that's what I wanted GSquared. I didn't know about CTE's.
I'm going to take a closer look now and make sure I got everything I need.
Hhmmm.... I think I've got some studying to do.
Thank you very much! Michelle 😀
May 14, 2009 at 2:52 pm
Hi Flo,
I tried your script and I got a 'Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('.' error.
It looks very interesting though, I'd like to try to fix this because seeing yours as well, will help give me perspective, as I'm obviously a newbie.
Thank you Flo.... Michelle 🙂
May 14, 2009 at 2:55 pm
Oups, sorry. I forgot the "AS" at the end of the second line (as most times... :hehe:)
I just corrected my previous post. Now it should work.
Greets
Flo
May 14, 2009 at 2:57 pm
Can I get your opinion? Is my script pretty beginner stuff? I was just wondering because I have no frame of reference, I'm the only one in my department who knows how to write this kind of stuff.
...just curious, what you experts think!
Michelle
May 14, 2009 at 3:10 pm
Hi Flo,
I tried the new script and I got some errors. I noticed some typos with crm_statuscodes so I fixed all of them and now I just get this error.
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'crm_statuscodes' does not contain a top-level UNION ALL operator.
I looked at the joins and I guess I'm not really sure where to put the union all.
Michelle :unsure:
May 14, 2009 at 3:16 pm
:w00t:
Maybe I should shutdown my system and go to bed... 😛
Changed the script again.
Greets
Flo
May 14, 2009 at 3:20 pm
mmunson (5/14/2009)
Can I get your opinion? Is my script pretty beginner stuff? I was just wondering because I have no frame of reference, I'm the only one in my department who knows how to write this kind of stuff....just curious, what you experts think!
Michelle
I don't know that I'd call this beginner stuff. It's hard for me to judge, because some stuff that I consider basic now, I most certainly would not have considered basic when I was first starting out, but it's hard to tell which is which.
I'd say CTEs and nested queries of this sort are beyond beginner, though. Definitely. Maybe somewhere in the middle-skill area.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2009 at 3:27 pm
mmunson (5/14/2009)
Can I get your opinion? Is my script pretty beginner stuff? I was just wondering because I have no frame of reference, I'm the only one in my department who knows how to write this kind of stuff.
I cannot answer this question depending on your script.
I would say you should have a look for CTEs in BOL because they are a great feature introduced with SSE2k5.
Anyway, the fact that you didn't know them does not say very much about your overall experiences. The magic is more to find a good solution for every requirement.
In my book the best way to increase your knowledge about available functions and techniques is hanging around on pages like SSC.
Hope this helps.
Greets
Flo
May 14, 2009 at 3:31 pm
Flo, I tried it again and I got this error. :hehe:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CRM_Orders.CREATED_AT" could not be bound.
No worries though, I try working through it again tomorrow. I'm about sql'd out for the day.
Thanks so much for your help!
Michelle
May 14, 2009 at 3:36 pm
Thanks for the reply GSquared. I appreciate your opinion!
I continue to learn new functions based on the requirements from the users, and well, they can be pretty demanding! :hehe: And from this forum of course!
Michelle
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply