Solved

select rows from differnt tables

Posted on 2014-02-20
17
263 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

15 Experts available now in Live!

Get 1:1 Help Now