June 8, 2011 at 10:32 am
Hi Guru,
I need some help to query these two below tables:
CREATE TABLE [dbo].[Transactions](
[transID] [int] identity(1,1),
[radid] [int] NOT NULL,
[procid] [int] NOT NULL,
[wu] [decimal](6, 3) NULL,
[groupID] [decimal](6, 3) NULL)
GO
CREATE TABLE [dbo].[Unit](
[comboID] [int] identity(1,1),
[groupID] [int] NULL,
[procid] [int] NULL,
[WU] [decimal](6, 3) NULL)
GO
insert into Transactions(radID, procID) values (1, 1)
insert into Transactions (radID, procID) values (1, 2)
insert into Transactions(radID, procID) values (1, 3)
insert into Transactions (radID, procID) values (2, 1)
insert into Transactions (radID, procID) values (2, 3)
insert into Transactions (radID, procID) values (2, 4)
insert into Transactions (radID, procID) values (3, 2)
insert into Transactions (radID, procID) values (3, 3)
insert into Unit (Groupid, procid, wu) values (1,1,11)
insert into Unit (Groupid, procid, wu) values (1,3,13)
insert into Unit (Groupid, procid, wu) values (2,1,21)
insert into Unit (Groupid, procid, wu) values (2,3,23)
insert into Unit (Groupid, procid, wu) values (2,4,24)
How can I get this result below from above tables?
transIDradidprocidwugroupID
---------------------------------------------------
111111
212NULLNULL
313131
421212
523232
624242
732NULLNULL
833NULLNULL
Thanks much,
Attopeu
June 8, 2011 at 11:14 am
Have you attempted to write a query yet? Please share what you have done so far.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 11:22 am
I can only get to TransID,RadID and ProcID. I am struggling to get WU and GroupID correctly. Here is my query:
select
T.transID,
T.radid,
DENSE_RANK()OVER(ORDER BY T.procid)AS ProcID
from sarah_testTrans T
left join sarah_comboWU C
on T.procid=C.procid
group by T.transID,T.radid,T.procid
order by T.transID
Thanks much,
Attopeu
June 8, 2011 at 11:54 am
Thanks. When I simply add those columns to the group I get more than one column per transId/radid/ProcID combination...which I see you do not want based on your expected results.
From this:
SELECT T.transID,
T.radid,
DENSE_RANK() OVER (ORDER BY T.procid) AS ProcID,
c.WU,
C.groupId
FROM dbo.Transactions T
LEFT JOIN dbo.Unit C ON T.procid = C.procid
GROUP BY T.transID,
T.radid,
T.procid,
c.WU,
C.groupId
ORDER BY T.transID ;
I get this:
transIDradidProcIDWUgroupId
-------------------------------
11111.0001
11121.0002
212NULLNULL
31313.0001
31323.0002
42111.0001
42121.0002
52313.0001
52323.0002
62424.0002
732NULLNULL
83313.0001
83323.0002
I think you want to bring your query into a CTE and use another Window function like ROW_NUMBER()...OVER...PARTITION and select from the CTE where ROW_NUMBER = 1, or possibly something with OUTER APPLY since some rows have no related WU/groupID, however I am failing to infer the criteria you want for choosing one row to present when there are multiple. Why would I choose this row:
transIDradidProcIDWUgroupId
-------------------------------
11111.0001
Instead of this one:
transIDradidProcIDWUgroupId
-------------------------------
11121.0002
--
I thought maybe because the groupId was lower, however then I see you choose this row:
transIDradidProcIDWUgroupId
-------------------------------
42121.0002
Instead of this one:
transIDradidProcIDWUgroupId
-------------------------------
42111.0001
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply