CODEWITHSUNDEEP
sqlsql-server-2008
user25730
user25730

Reputation: 709

SQL - getting data twice from the same table

Good people of the internet, I need your help!

I'm trying to put together some SQL code to create a report. Basically, I'm needing to look at one table - tbl_Schedules - and fetch the maximum of the field SchedDone, which is a regular date field.

This is fair enough. I manage this using GROUP BY and MAX.

The same table also contains SchedFrom and SchedTo fields, and I need to get this from another record, but run alongside this.

Basically, I need a "LatestDate" field (which shows the max SchedDone), and then a "next scheduled" field (or fields), showing when it is next to be done, pulled from a row where the SchedDone is null.

My current code, displayed below (ignore everything but the tbl_Schedules stuff) is working to an extent. It shows the LatestDate as described above, but also shows the maximum of that Task's SchedFrom and SchedTo. These are all from different rows in the tbl_Schedules, which is what I want. I just need to know how to set up rules for the SchedFrom and SchedTo, preferably without involving any other tables or setting up multiple views.

I do have this working, but it's taking up several views and the speed involved is not good. I'd hope I can get it working in a single chunk of SQL code.

PS - tbl_PhysicalAssets is a one-to-many relationship with tbl_Operations (one row in tbl_PhysicalAssets to many in tbl_Operations), and tbl_Operations is a one-to-many relationship with tbl_Schedules (one row in tbl_Operations to many in tbl_Schedules).

Current code below (again, please ignore the other tables!) -

SELECT     
   dbo.tbl_PhysicalAsset.FKID_Contract, 
   dbo.tbl_PhysicalAsset.MyLevel, 
   dbo.tbl_PhysicalAsset.L1_Name, 
   dbo.tbl_PhysicalAsset.L2_Name, 
   dbo.tbl_PhysicalAsset.L3_Name, 
   dbo.tbl_OpList.Operation_Name, 
   dbo.tbl_Teams.Team_Name, 
   MAX(tbl_Schedules_1.SchedDone) AS LatestDate, 
   MAX(tbl_Schedules_1.SchedFrom) AS Expr1, 
   MAX(tbl_Schedules_1.SchedTo) AS Expr2
FROM
   dbo.tbl_Schedules AS tbl_Schedules_1 
RIGHT OUTER JOIN
   dbo.tbl_PhysicalAsset 
INNER JOIN
   dbo.tbl_Operations ON dbo.tbl_PhysicalAsset.PKID_PhysicalAsset = dbo.tbl_Operations.FKID_PhysicalAsset 
INNER JOIN
   dbo.tbl_OpList ON dbo.tbl_Operations.FKID_Operation = dbo.tbl_OpList.PKID_Op 
INNER JOIN
   dbo.tbl_Teams ON dbo.tbl_Operations.FKID_Team = dbo.tbl_Teams.PKID_Team ON tbl_Schedules_1.FKID_Operation = dbo.tbl_Operations.PKID_Operation
GROUP BY 
   dbo.tbl_PhysicalAsset.FKID_Contract, 
   dbo.tbl_PhysicalAsset.MyLevel, 
   dbo.tbl_PhysicalAsset.L1_Name, 
   dbo.tbl_PhysicalAsset.L2_Name, 
   dbo.tbl_PhysicalAsset.L3_Name, 
   dbo.tbl_OpList.Operation_Name, 
   dbo.tbl_Teams.Team_Name
HAVING      
   (dbo.tbl_PhysicalAsset.FKID_Contract = 6) 
   AND (dbo.tbl_PhysicalAsset.MyLevel = 3)

Upvotes: 0

Views: 100

Answers (1)

cjb110
cjb110

Reputation: 1471

This is rough as we don't know the exact details of your table structure.

But basically you need to first write the queries that get the two separate bits of information, make sure they work in isolation. You can then just join them together.

So something like (I've assumed FKID_Operation is the 'common' piece of info):

select
   a.FKID_Operation
   , b.LatestDate
   , c.NextToDate
   , c.NextFromDate
from tbl_Schedules a

inner join
(
select 
   m.FKID_Operation
   , Max(m.SchedDone) as LatestDate
from tbl_Schedules m
where SchedDone is not null
) b
on a.FKID_Operation = b.FKID_Operation

inner join
(
select
   n.FKID_Operation
   , n.SchedTo as NextToDate
   , n.SchedFrom as NextFromDate
from tbl_Schedules n
where SchedDone is null
) c
on a.FKID_Operation = c.FKID_Operation

I'd also look into CTE's as they can make this kind of query much easier to understand.

Upvotes: 1

Related Questions