Joining Select Statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joining Select Statements

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-23 : 15:30:24

Hi ,

If any one could help me in Joining these two select statements:

How Can I have Order by and Having Clauses in my final select list ?

***********
SELECT MIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost
FROM master..spt_values AS v
INNER JOIN
(
SELECT DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute,
Convert(varchar(10),StartDate,102) as ProductionDate
--,COUNT(AlarmMessage) As Occurances
FROM RPMS.dbo.List_PaintShopAlarms
WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and
Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102)
AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' )
)
AS x ON x.StartMinute <= v.Number
AND x.EndMinute > v.Number
WHERE v.Type = 'p' AND Number < 1440
and (x.ProductionDate BETWEEN Convert(varchar(10),@dtProductionDayStart,102) AND Convert(varchar(10),@dtProductionDayEnd,102))
group by x.ProductionDate
order by x.ProductionDate asc
***********
and
***********
SELECT Convert(varchar(25),[StartDate],102)as ProductionDay
,COUNT(AlarmMessage) As Occurances
FROM [RPMS].[dbo].[List_PaintShopAlarms]
WHERE
([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND
Convert(varchar(25),[StartDate],102) BETWEEN Convert(varchar(25),@dtStartDate,102) AND Convert(varchar(25),@dtEndDate,102)
and DATEPART(dw, [ProductionDay]) <> 1

GROUP BY Convert(varchar(25),[StartDate],102)
HAVING SUM([Duration]) < 86400
ORDER BY Convert(varchar(25),[StartDate],102) asc
*************

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-23 : 17:20:45

DECLARE @Table1 TABLE(ProductionDay datetime , Occurances int)
DECLARE @Table2 TABLE(ProductionDay datetime , TimeLost int)

DECLARE
@dtStartDate DATETIME,
@dtEndDate DATETIME

SET @dtStartDate = '2008.04.01'
SET @dtEndDate = '2008.04.23'

INSERT INTO @Table1
SELECT Convert(varchar(25),[StartDate],102)as 'ProductionDay'
,COUNT(AlarmMessage) As 'Occurances'
FROM [RPMS].[dbo].[List_PaintShopAlarms]
WHERE
([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND
Convert(varchar(25),[StartDate],102) BETWEEN Convert(varchar(25),@dtStartDate,102) AND Convert(varchar(25),@dtEndDate,102)
--and DATEPART(dw, [ProductionDay]) <> 1
GROUP BY Convert(varchar(25),[StartDate],102)
HAVING SUM([Duration]) < 86400
ORDER BY Convert(varchar(25),[StartDate],102) asc

INSERT INTO @Table2
SELECT MIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost
FROM master..spt_values AS v
INNER JOIN
(
SELECT DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute,
Convert(varchar(10),StartDate,102) as ProductionDate
--,COUNT(AlarmMessage) As Occurances
FROM RPMS.dbo.List_PaintShopAlarms
WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and
Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102)
AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' )
)
AS x ON x.StartMinute <= v.Number
AND x.EndMinute > v.Number
WHERE v.Type = 'p' AND Number < 1440
and (x.ProductionDate BETWEEN Convert(varchar(10),@dtStartDate,102) AND Convert(varchar(10),@dtEndDate,102))
group by x.ProductionDate
order by x.ProductionDate asc

SELECT Convert(varchar(10),T1.ProductionDay,102),T1.Occurances,T2.TimeLost FROM @Table1 T1 INNER JOIN @Table2 T2
ON T1.ProductionDay = T2.ProductionDay

Is there any thing better than using Temp Tables?

bfoster
Starting Member

30 Posts

Posted - 2008-04-24 : 11:41:22

You should be able to take the query you gave and just replace @Table1 with the query that fills it and @Table2 with the query that fills it like this.

SELECT Convert(varchar(10),T1.ProductionDay,102),T1.Occurances,T2.TimeLost
FROM
(
SELECT Convert(varchar(10),[StartDate],102)as 'ProductionDay'
,COUNT(AlarmMessage) As 'Occurances'
FROM [RPMS].[dbo].[List_PaintShopAlarms]
WHERE
([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND
Convert(varchar(10),[StartDate],102) BETWEEN Convert(varchar(10),@dtStartDate,102) AND Convert(varchar(10),@dtEndDate,102)
--and DATEPART(dw, [ProductionDay]) <> 1
GROUP BY Convert(varchar(10),[StartDate],102)
HAVING SUM([Duration]) < 86400
)T1
INNER JOIN
(
SELECT MIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost
FROM master..spt_values AS v
INNER JOIN
(
SELECT DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute,
Convert(varchar(10),StartDate,102) as ProductionDate
--,COUNT(AlarmMessage) As Occurances
FROM RPMS.dbo.List_PaintShopAlarms
WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and
Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102)
AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' )
)
AS x ON x.StartMinute <= v.Number
AND x.EndMinute > v.Number
WHERE v.Type = 'p' AND Number < 1440
and (x.ProductionDate BETWEEN Convert(varchar(10),@dtStartDate,102) AND Convert(varchar(10),@dtEndDate,102))
group by x.ProductionDate
) T2
ON T1.ProductionDay = T2.ProductionDay
ORDER BY Convert(varchar(10),T1.ProductionDay,102) asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources