Solved

select rows from differnt tables

Posted on 2014-02-20
17
260 Views
Last Modified: 2014-02-21
hi there ,
i have 3 tables involves in the query this is the code:

select OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
,Sum(TMparitKamot) as quant
from dbo.CustOrdersMain inner join dbo.CustOrderDet ON CustOrderRunId=CustOrderNum
left join dbo.TMdetiles on TmDocNum=CustOrdersDetRunId
left join dbo.TedotMisMain on TMNum=TeodotMisRunId
group by OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate

in the query i select this column : TMOpenDate from TedotMisMain table
i need to change the query that select only the last : TMOpenDate
i will try to explain :

for each row in CustOrderDet can be more then 1 row in TMdetiles
i need to pull out only the last value of the date in TMOpenDate value
now i am geting duplicated rows for each row in the CustOrderDet
i hope that i write a good explain for this issue

thanks...
0
Comment
Question by:Tech_Men
17 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39875217
you can write it as below


;with C AS
(
SELECT ROW_NUMBER() OVER(partition by TmDocNum order by TMOpenDate desc) rn, * FROM TMdetiles 
)
select OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
,Sum(TMparitKamot) as quant
from dbo.CustOrdersMain inner join dbo.CustOrderDet ON CustOrderRunId=CustOrderNum
left join C on TmDocNum=CustOrdersDetRunId and c.rn = 1
left join dbo.TedotMisMain on TMNum=TeodotMisRunId
group by OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate

Open in new window

0
 

Author Comment

by:Tech_Men
ID: 39875229
hi
thanks for your answer i try to run your code but i am getting this EX :
Msg 207, Level 16, State 1, Line 5
Invalid column name 'TMOpenDate'.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39875245
ooops I misread your question a little bit, check the below one out

;with C AS 
(
select OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
,Sum(TMparitKamot) as quant
,row_number() OVER(partition by CustOrdersDetRunId order by  TMOpenDate desc) RN
from dbo.CustOrdersMain inner join dbo.CustOrderDet ON CustOrderRunId=CustOrderNum
left join C on TmDocNum=CustOrdersDetRunId 
left join dbo.TedotMisMain on TMNum=TeodotMisRunId
group by OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
)
select * FROM C WHERE RN = 1

Open in new window

0
 

Author Comment

by:Tech_Men
ID: 39875260
sorry :
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'C' does not contain a top-level UNION ALL operator.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39875274
can you give this a try

;with C AS 
(
select OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
,Sum(TMparitKamot) as quant
from dbo.CustOrdersMain inner join dbo.CustOrderDet ON CustOrderRunId=CustOrderNum
left join C on TmDocNum=CustOrdersDetRunId 
left join dbo.TedotMisMain on TMNum=TeodotMisRunId
group by OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
),C1 AS
(
SELECT ,row_number() OVER(partition by CustOrdersDetRunId order by  TMOpenDate desc) RN, * FROM C
)
select * FROM C1 WHERE RN = 1

Open in new window

0
 

Author Comment

by:Tech_Men
ID: 39875292
again :
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'C' does not contain a top-level UNION ALL operator.
0
 

Author Comment

by:Tech_Men
ID: 39875314
hi again this is code is working now :
;with C AS
(
SELECT ROW_NUMBER() OVER(partition by TmDocNum order by TMpirotRunId desc) rn, * FROM TMdetiles
)
select OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
,Sum(TMparitKamot) as quant
from dbo.CustOrdersMain inner join dbo.CustOrderDet ON CustOrderRunId=CustOrderNum
left join C on TmDocNum=CustOrdersDetRunId and c.rn = 1
left join dbo.TedotMisMain on TMNum=TeodotMisRunId
group by OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate

your original code whit this line :
OVER(partition by TmDocNum order by TMOpenDate desc)
u can see that i change it and its working but the : quant is not calculate correctly
its calc only the last date and not all the rows
i hope that u understand
0
 

Author Comment

by:Tech_Men
ID: 39875323
this :
Sum(TMparitKamot) as quant

calculate  only the last row that shown in the result
i need it to calc all the rows that has the same TmDocNum even that i see only the last
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39875324
;with C AS
(
SELECT ROW_NUMBER() OVER(partition by TeodotMisRunId order by TMOpenDate desc) rn, * FROM TedotMisMain
)
select OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
,Sum(TMparitKamot) as quant
from dbo.CustOrdersMain inner join dbo.CustOrderDet ON CustOrderRunId=CustOrderNum
left join TMdetiles on TmDocNum=CustOrdersDetRunId
left join C on TMNum=TeodotMisRunId and c.rn = 1
group by OrderDate,CustOrderParitSerial,CustOrderParitName,CustOrderPKamot,TMOpenDate
0
 

Author Comment

by:Tech_Men
ID: 39875334
no this return all the rows not good sorry
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39875375
Post some sample data from your tables with expected result.
0
 

Author Comment

by:Tech_Men
ID: 39875481
this is the data from the : CustOrdersMain
CustOrderRunId      OrderDate
325                             2014-02-21 00:35:49.000
326                             2014-02-21 00:36:07.000
327                             2014-02-21 00:44:52.000
328                            2014-02-21 01:00:44.000

this is the data from the : CustOrderDet
CustOrdersDetRunId      CustOrderNum      CustOrderParitSerial      CustOrderPKamot
479                                               325                                3200                                100
480                                               325                           1020                                80
481                                               326                         1478                                70
482                                               327                          3200                                120
483                                               328                        147258                                50
484                                               328                          1020                                45
485                                               328                         1010                                67

this is the data from : TMdetiles
TMpirotRunId      TMNum      TMmkt      TMparitKamot      TmDocNum
911                                600        3200              50                             479
912                                600      1020              40                              480
913                                601      3200               50                             479
914                                601      1020               40                             480
915                                602      3200              40                          482
916                                603      3200              40                             482
917                                604      3200              40                             482
918                                605      147258             20                             483

this is the data from :TedotMisMain
TeodotMisRunId      TMOpenDate
600                              2014-02-21 00:36:18.390
601                              2014-02-23 00:37:09.000
602                              2014-02-21 00:45:01.337
603                              2014-02-23 00:45:15.000
604                              2014-03-02 00:45:30.000
605                              2014-02-21 01:00:52.480

for example  i need this res for order num : 325
for this order i see that i have 2 rows in CustOrderDet  for 2 items
3200 quant : 100 the id for this row is : 479
1020 quant : 80 the id for this row is : 480
now in the table : TMdetiles in the column : TmDocNum u can see that we have
2 rows for this id 479 so for this row in res i need to see this res :

OrderDate                CustOrderParitSerial   CustOrderPKamot  TMOpenDate               quant                          
2014-02-21 00:35             3200                               100                    2014-02-23 00:37         100

the TMOpenDate need to be the last that there is for 479 (id) this is the 601 from :TedotMisMain
the quant need to sum all the 479 id TMparitKamot column
i hope now that its more understood
thanks...
0
 
LVL 3

Expert Comment

by:smilieface
ID: 39875616
If you just need the Latest Date then your query is much simpler.

Go back to your original query and change TMOpenDate to be MAX(TMOpenDate).
0
 

Author Comment

by:Tech_Men
ID: 39876541
hi
not good if u do :  MAX(TMOpenDate)
i am getting 2 rows i need only 1 row whit the last date
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39876640
are you looking for something like this...

The table creation and insert statements are just for creating the temporary data... check the select statement at the end

declare @CustOrdersMain TABLE
(
CustOrderRunId INT, OrderDate DATETIME
)

INSERT INTO @CustOrdersMain
VALUES ('325','2014-02-21 00:35:49.000')
,('326','2014-02-21 00:36:07.000')
,('327','2014-02-21 00:44:52.000')
,('328','2014-02-21 01:00:44.000')

declare @CustOrderDet TABLE
(
CustOrdersDetRunId INT, CustOrderNum INT, CustOrderParitSerial INT,CustOrderPKamot  INT
)
INSERT INTO @CustOrderDet
VALUES 
(479,325,3200,100  )
,(480,325,1020,80   )
,(481,326,1478,70   )
,(482,327,3200,120  )
,(483,328,147258,50 )
,(484,328,1020,45   )
,(485,328,1010,67   )

DECLARE @TMdetiles TABLE 
(
  TMpirotRunId INT, TMNum INT, TMmkt INT, TMparitKamot INT, TmDocNum INT
)
INSERT INTO @TMdetiles
VALUES 
(911,600,3200  ,50,479)
,(912,600,1020  ,40,480)
,(913,601,3200  ,50,479)
,(914,601,1020  ,40,480)
,(915,602,3200  ,40,482)
,(916,603,3200  ,40,482)
,(917,604,3200  ,40,482)
,(918,605,147258,20,483)

DECLARE @TedotMisMain TABLE
(TeodotMisRunId INT, TMOpenDate DATETIME)

INSERT INTO @TedotMisMain 
VALUES 
(600,'2014-02-21 00:36:18.390')
,(601,'2014-02-23 00:37:09.000')
,(602,'2014-02-21 00:45:01.337')
,(603,'2014-02-23 00:45:15.000')
,(604,'2014-03-02 00:45:30.000')
,(605,'2014-02-21 01:00:52.480')

select OrderDate,CustOrderParitSerial,CustOrderPKamot,MAX(TMOpenDate)
,Sum(TMparitKamot) as quant
from @CustOrdersMain 
inner join @CustOrderDet ON CustOrderRunId=CustOrderNum
AND CustOrderRunId = 325
left join @TMdetiles on TmDocNum=CustOrdersDetRunId
left join @TedotMisMain on TMNum=TeodotMisRunId
group by OrderDate,CustOrderParitSerial,CustOrderPKamot

Open in new window

0
 

Author Closing Comment

by:Tech_Men
ID: 39876744
thanks its working
0
 

Author Comment

by:Tech_Men
ID: 39876748
i replace the @pram whit tables names


select OrderCustNum,OrderDate,CustOrderParitSerial,CustOrderPKamot,MAX(TMOpenDate)
,Sum(TMparitKamot) as quant
from CustOrdersMain
inner join CustOrderDet ON CustOrderRunId=CustOrderNum
left join TMdetiles on TmDocNum=CustOrdersDetRunId
left join TedotMisMain on TMNum=TeodotMisRunId
group by OrderCustNum,OrderDate,CustOrderParitSerial,CustOrderPKamot
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now