October 12, 2011 at 11:06 am
Main query:
select JB.ID1, JB.ID2, JB.stat,JB.item,JR.oper_num, JR.complete, JR.qty_complete
from TABLE1 JB
join TABLE2 JR on JB.ID1 = JR.ID1 and JB.ID2 = JR.ID2
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete = 0 and (JR.complete >0 or JR.qty_complete >0))
order by JB.item, JR.oper_num
I want my first query to pull the max trans date from this query (to be the last field in above query):
select JT.ID1, JT.ID2, JT.oper_num, MAX(trans_date)
from TABLE3 JT
join TABLE2 JR on JT.ID1 = JR.ID1 and JT.ID2 = JR.ID2 and JT.oper_num = JR.oper_num
join TABLE1 JB on JT.ID1 = JB.ID1 and JT.ID2 = JB.ID2
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete = 0 and (JR.complete >0 or JR.qty_complete >0))
group by JT.ID1, JT.ID2, JT.oper_num
October 12, 2011 at 11:33 am
have you ever tried using Rank By Partition() ?
October 12, 2011 at 11:36 am
nitin1309 (10/12/2011)
have you ever tried using Rank By Partition() ?
Only in easier examples than this. Not sure how to do it here.
October 12, 2011 at 11:47 am
what table is trans_Date coming from and what are the other columns in that table ?
Just isolate the trans_date and the other columns you need to group on to get the max(trans_date)
and make it a sub-query.
October 12, 2011 at 11:48 am
Good example of what I just told you:
http://riteshshah.wordpress.com/2009/03/03/use-of-rank-and-partition-by-clause-in-sql-server-2005/
October 12, 2011 at 12:00 pm
nitin1309 (10/12/2011)
I didn't sleep at all last night so I'm not able to even comprehend that today. To put it in simple terms - this is what I'm trying to do:
Using the northwind db as an example (let me know if you need me to post the code to build it)
let's say this is my query1:
SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.City, Customers.Region, Region.RegionDescription
FROM Customers CROSS JOIN
Region
and this is my query2:
SELECT Orders.CustomerID, customers.companyname, max(Orders.ShippedDate)
FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID
where Customers.Country='usa'
GROUP BY Orders.CustomerID, Customers.CompanyName
I want query 1 to show the max shipped date (when available - because query 2 limits the results) for each customerID
October 12, 2011 at 12:50 pm
I dont know if I get this right:
You said
"and this is my query2:
SELECT Orders.CustomerID, customers.companyname, max(Orders.ShippedDate)
FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID
where Customers.Country='usa'
GROUP BY Orders.CustomerID, Customers.CompanyName
I want query 1 to show the max shipped date (when available - because query 2 limits the results) for each customerID"
If I assume for every CustomerID in the Orders table, there may be multiple orders and each order will have a Order-Shipping Date then you can modify the query like this:
SELECT ord.CustomerID, c.companyname, ord.Max_Shipped_Date
FROM
( Select CustomerID, max(ShippedDate) as Max_Shipped_Date
From Orders
Group by CustomerID
)ord
JOIN
Customers c ON ord.CustomerID = c.CustomerID
where c.Country='usa'
GROUP BY ord.CustomerID, c.CompanyName
becuase of the sub-query you will automatically get the max-shipped-date for every customer ID.
Hope this helps!
October 12, 2011 at 12:53 pm
I haven't tested it (I don't have nw installed), but this should do what you need using a sub-query construction. You can also use the window-functions (f.e. row_number()) to do the same thing, sometimes one is better and otyher times the other is better. Test it for your situation.
SELECT c.CustomerID, c.CompanyName, c.ContactName, c.City, c.Region, r.RegionDescription, o.MaxShippedDate
FROM Customers c
CROSS JOIN Region r
LEFT OUTER JOIN (
SELECT o.CustomerID, MAX(o.ShippedDate) AS MaxShippedDate
FROM Orders o
) o ON (o.CustomerID = c.CustomerID AND c.Country = 'usa')
I have a feeling that the cross join should actually be an inner join on the customer's region. But this is how you described it, so that is what I put in here. Hope it helps you.
October 12, 2011 at 1:50 pm
R.P.Rozema (10/12/2011)
I haven't tested it (I don't have nw installed), but this should do what you need using a sub-query construction. You can also use the window-functions (f.e. row_number()) to do the same thing, sometimes one is better and otyher times the other is better. Test it for your situation.
SELECT c.CustomerID, c.CompanyName, c.ContactName, c.City, c.Region, r.RegionDescription, o.MaxShippedDateFROM Customers c
CROSS JOIN Region r
LEFT OUTER JOIN (
SELECT o.CustomerID, MAX(o.ShippedDate) AS MaxShippedDate
FROM Orders o
) o ON (o.CustomerID = c.CustomerID AND c.Country = 'usa')
I have a feeling that the cross join should actually be an inner join on the customer's region. But this is how you described it, so that is what I put in here. Hope it helps you.
I'm getting the following error with that query.....
Msg 8120, Level 16, State 1, Line 5
Column 'Orders.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
UPDATE: I think I made the necessary change to get what I expected. Now I just need to adapt this to my 'real' database with different tables/fields. Thank you!!!! (I'll update if I get stuck)
SELECT c.CustomerID, c.CompanyName, c.ContactName, c.City, c.Region, r.RegionDescription, o.MaxShippedDate
FROM Customers c
CROSS JOIN Region r
LEFT OUTER JOIN (
SELECT o.CustomerID, MAX(o.ShippedDate) AS MaxShippedDate
FROM Orders o
group by CustomerID
) o ON (o.CustomerID = c.CustomerID AND c.Country = 'usa')
October 12, 2011 at 1:51 pm
nitin1309 (10/12/2011)
I dont know if I get this right:You said
"and this is my query2:
SELECT Orders.CustomerID, customers.companyname, max(Orders.ShippedDate)
FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID
where Customers.Country='usa'
GROUP BY Orders.CustomerID, Customers.CompanyName
I want query 1 to show the max shipped date (when available - because query 2 limits the results) for each customerID"
If I assume for every CustomerID in the Orders table, there may be multiple orders and each order will have a Order-Shipping Date then you can modify the query like this:
SELECT ord.CustomerID, c.companyname, ord.Max_Shipped_Date
FROM
( Select CustomerID, max(ShippedDate) as Max_Shipped_Date
From Orders
Group by CustomerID
)ord
JOIN
Customers c ON ord.CustomerID = c.CustomerID
where c.Country='usa'
GROUP BY ord.CustomerID, c.CompanyName
becuase of the sub-query you will automatically get the max-shipped-date for every customer ID.
Hope this helps!
Getting the following error with your query:
Msg 8120, Level 16, State 1, Line 1
Column 'ord.Max_Shipped_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
And I'm not seeing the columns that I need from Query 1 (e.g. the regions table column) in that sql statement
I appreciate the help so far!!!
October 12, 2011 at 2:09 pm
SELECT c.CustomerID, c.CompanyName, c.ContactName, c.City, c.Region, r.RegionDescription, o.MaxShippedDate
FROM Customers c
CROSS JOIN Region r
LEFT OUTER JOIN (
SELECT o.CustomerID, MAX(o.ShippedDate) AS MaxShippedDate
FROM Orders o
group by CustomerID
) o ON (o.CustomerID = c.CustomerID AND c.Country = 'usa')
This should be correct.
I forgot to delete the last Group By xxxxxxxxx line.. You can delete it
October 12, 2011 at 2:32 pm
SELECT ord.CustomerID, c.companyname, ord.Max_Shipped_Date
FROM
( Select CustomerID, max(ShippedDate) as Max_Shipped_Date
From Orders
Group by CustomerID
)ord
JOIN
Customers c ON ord.CustomerID = c.CustomerID
where c.Country='usa'
-- GROUP BY ord.CustomerID, c.CompanyName -- Error because your ord.Max_Ship_Date. I don't think you need this GROUP BY it's handled in the sub select. Try removing the group by and see if the data is correct.
October 12, 2011 at 3:39 pm
I can't seem to get it to translate to my actual tables (I thought I could use Northwind as an example - but it's not quite the same)
use [tempdb]
go
CREATE TABLE [dbo].[Job](
[job] [nvarchar](20) NOT NULL,
[suffix] [smallint] NOT NULL,
[type] [char](1) NULL,
[item] [nvarchar](30) NOT NULL,
[stat] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Jobroute](
[job] [nvarchar](20) NOT NULL,
[suffix] [smallint] NOT NULL,
[oper_num] [int] NOT NULL,
[complete] [tinyint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Jobtran](
[trans_num] [decimal](11, 0) NOT NULL,
[job] [nvarchar](20) NULL,
[suffix] [smallint] NULL,
[trans_date] [date] NULL,
[oper_num] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [tempdb].[dbo].[job]
([job]
,[suffix]
,[type]
,[item]
,[stat])
VALUES
('JOB1',0,'J','PENCIL','R'),
('JOB1',1,'J','PENCIL','R'),
('JOB2',0,'J','MARKER','R'),
('JOB2',1,'J','MARKER','R'),
('JOB3',0,'J','ERASER','R'),
('JOB3',1,'J','ERASER','R'),
('JOB4',0,'K','RULER','C'),
('JOB4',1,'K','RULER','R')
GO
INSERT INTO [tempdb].[dbo].[jobroute]
([job]
,[suffix]
,[oper_num]
,[complete])
VALUES
('JOB1',0,10,0),
('JOB1',0,20,0),
('JOB1',0,30,1),
('JOB1',0,40,1),
('JOB1',0,50,1),
('JOB1',1,10,1),
('JOB1',1,20,0),
('JOB2',0,10,0),
('JOB2',0,20,0),
('JOB2',0,30,1),
('JOB2',1,10,1),
('JOB3',0,10,0),
('JOB3',0,20,1),
('JOB4',0,10,0),
('JOB4',0,20,1),
('JOB2',0,10,0),
('JOB2',0,20,0),
('JOB2',0,30,1),
('JOB2',1,10,1)
GO
INSERT INTO [tempdb].[dbo].[jobtran]
([trans_num]
,[job]
,[suffix]
,[trans_date]
,[oper_num])
VALUES
(100,'JOB1',0,'2011-01-01',10),
(101,'JOB1',0,'2011-01-15',10),
(102,'JOB1',0,'2011-04-15',10),
(103,'JOB3',0,'2011-01-16',20),
(104,'JOB2',0,'2011-04-01',10),
(105,'JOB3',0,'2011-09-15',20),
(106,'JOB1',0,'2011-07-01',10),
(107,'JOB1',0,'2011-08-12',10),
(108,'JOB2',0,'2011-03-03',10),
(109,'JOB4',0,'2003-06-02',20),
(110,'JOB3',0,'2006-01-01',10),
(111,'JOB4',0,'2011-07-15',20),
(112,'JOB4',0,'2011-10-01',20),
(113,'JOB1',0,'2011-03-02',40),
(114,'JOB2',0,'2011-05-28',30),
(115,'JOB1',0,'2011-04-01',40),
(116,'JOB1',0,'2011-08-02',40),
(117,'JOB1',0,'2011-01-01',30),
(118,'JOB1',0,'2011-06-06',30),
(119,'JOB2',1,'2011-12-31',10)
GO
Desired query:
select JB.job, JB.suffix, JB.stat,JB.item,JR.oper_num
,jb.stat, (maxdate from query 2 below)
from job JB
join jobroute JR on JB.job = JR.job and JB.suffix = JR.suffix
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete > 0 )
order by JB.item, JR.oper_num
My query 2 (join on fields job, suffix and oper_num to above if necessary):
select JT.job, JT.suffix, JT.oper_num, MAX(trans_date) AS maxdate
from jobtran JT
join jobroute JR on JT.job = JR.job and JT.suffix = JR.suffix and JT.oper_num = JR.oper_num
join job JB on JT.job = JB.job and JT.suffix = JB.suffix
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete > 0)
group by JT.job, JT.suffix, JT.oper_num
October 12, 2011 at 4:36 pm
You could run the below query :
select JB.ID1, JB.ID2, JB.stat,JB.item,JR.oper_num, JR.complete, JR.qty_complete,max_date
from TABLE1 JB
join TABLE2 JR on JB.ID1 = JR.ID1 and JB.ID2 = JR.ID2
inner join (
select JT.ID1, JT.ID2, JT.oper_num, MAX(trans_date) AS amax_date
from TABLE3 JT
join TABLE2 JR on JT.ID1 = JR.ID1 and JT.ID2 = JR.ID2 and JT.oper_num = JR.oper_num
join TABLE1 JB on JT.ID1 = JB.ID1 and JT.ID2 = JB.ID2
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete = 0 and (JR.complete >0 or JR.qty_complete >0))
group by JT.ID1, JT.ID2, JT.oper_num
)S ON JB.ID1 =S.ID1 AND JB.ID2 =S.ID2 AND JT.oper_num=s.oper_num
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete = 0 and (JR.complete >0 or JR.qty_complete >0))
order by JB.item, JR.oper_num
Regarding performance wise , you would have to create some non covering clustered indexes on the 3 tables particularly more with huge data entity
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 12, 2011 at 4:45 pm
Performace Guard (Shehap) (10/12/2011)
You could run the below query :select JB.ID1, JB.ID2, JB.stat,JB.item,JR.oper_num, JR.complete, JR.qty_complete,max_date
from TABLE1 JB
join TABLE2 JR on JB.ID1 = JR.ID1 and JB.ID2 = JR.ID2
inner join (
select JT.ID1, JT.ID2, JT.oper_num, MAX(trans_date) AS amax_date
from TABLE3 JT
join TABLE2 JR on JT.ID1 = JR.ID1 and JT.ID2 = JR.ID2 and JT.oper_num = JR.oper_num
join TABLE1 JB on JT.ID1 = JB.ID1 and JT.ID2 = JB.ID2
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete = 0 and (JR.complete >0 or JR.qty_complete >0))
group by JT.ID1, JT.ID2, JT.oper_num
)S ON JB.ID1 =S.ID1 AND JB.ID2 =S.ID2 AND JT.oper_num=s.oper_num
where JB.type = 'j' and JB.stat = 'R'
and (JR.complete = 0 and (JR.complete >0 or JR.qty_complete >0))
order by JB.item, JR.oper_num
Regarding performance wise , you would have to create some non covering clustered indexes on the 3 tables particularly more with huge data entity
I'm getting the following error:
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "JT.oper_num" could not be bound.
Fixed error (made alias) but getting over 2,000 rows when query 1 gave me 104. This should just add a column with max date to query 1 and return the same 104 rows. I'm getting way too much data now.
Update 2: Problem was this line:
)S ON JB.ID1 =S.ID1 AND JB.ID2 =S.ID2 AND JT.oper_num=s.oper_num
When I made it JR.oper_num=s.oper_num I had the expected 104 rows.
THANK YOU!!!!!!!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply