Correct syntax using Union with nested query

Ziddy · March 8, 2019, 7:58pm

Using the query on it's own produces the correct result. using it together with union gives me a syntax error. Please guide me to the correct Solution.

 Select  Top 1 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount

from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs
Union
Select Top 1 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs order by dcount desc

ScottPletcher · March 8, 2019, 8:23pm

When I parse the query, SQL (2016) doesn't show any error.

Ziddy · March 8, 2019, 8:42pm

My apologies. the correct query should be
Select Top 1 'v961' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'v961' and processtime <480
) T
group by BTB_Secs order by dcount desc
Union
Select Top 1 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs order by dcount desc

running this query results in a syntax error. running the query. running the query singularly produces the correct result. removing the order by clause before the Union produces the incorrect result. sorry..hope this makes sense.

Ziddy · March 8, 2019, 8:43pm

i am using sql 2012

ScottPletcher · March 8, 2019, 8:51pm

You have to put the ORDER BY in the first query in a derived table ("sub-SELECT"), like so:

select *
from (
Select Top (1) 'v961' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'v961' and processtime <480
) T
group by BTB_Secs order by dcount desc ) as derived
Union
Select Top (1) 'X331' as Recipe, BTB_Secs, Count(BTB_Secs) as DCount
from (SELECT RecipeName, DATEDIFF(ss, LAG([Date]) OVER (ORDER BY ID), [Date])as BTB_Secs
FROM [Millroom].[BizUser].[Ban1_Data]
where recipename = 'X331' and processtime <480
) T
group by BTB_Secs order by dcount desc

Ziddy · March 8, 2019, 9:10pm

some progress thanks. running the the query give the correct result for the first query only.
Recipe BTB_Secs Dcount
v961 289 268
X331 null 0

The correct result for the second query should be
X331 201 53

Any further suggestions?

yosiasz · March 8, 2019, 9:15pm

Please provide sample DDL and DML

create table [Ban1_Data](ID int, BTB_Secs int, RecipeName varchar(50), 
processtime int, [Date] date);
create table BTB_Secs([Date] date, RecipeName varchar(50))

insert into Ban1_Data
select sample data here


``

ScottPletcher · March 8, 2019, 9:27pm

You don't have an ORDER BY on the second SELECT, so "TOP (1)" could be any row in the result.

If there should be only one row in the entire result set, then I have no idea why it comes back as NULL. I know absolutely nothing about any of your data ... how could I??

Ziddy · March 8, 2019, 9:53pm

Some sample data from thousands of entries

ID	Date	Recipename	ProcessTime
117221	2015-03-13 08:54:16.043	X331	182
117222	2015-03-13 08:58:20.573	X331	178
117223	2015-03-13 09:02:01.370	X331	166
117224	2015-03-13 09:07:17.667	X331	176
117225	2015-03-13 09:10:58.447	X331	171
117226	2015-03-13 09:14:40.480	X331	179
117227	2015-03-13 09:18:58.777	X331	185
117228	2015-03-13 09:22:43.557	X331	175
117229	2015-03-13 09:26:11.823	X331	164
117230	2015-03-13 09:29:32.857	X331	163
117450	2015-03-16 13:21:24.423	V961	210
---	---	---	---
117451	2015-03-16 13:25:34.720	V961	213
117452	2015-03-16 14:21:14.563	V961	231
117453	2015-03-16 14:25:37.077	V961	213
117454	2015-03-16 14:29:51.827	V961	214
117455	2015-03-16 14:34:08.077	V961	207
117456	2015-03-16 14:38:28.327	V961	212
117457	2015-03-16 14:42:51.597	V961	212
117458	2015-03-16 14:47:12.097	V961	212
117459	2015-03-16 14:51:32.847	V961	212

hope this help.

Ziddy · March 8, 2019, 9:55pm

the idea is to find the mode of each recipe grouped by the Batch to Batch times.

yosiasz · March 8, 2019, 9:56pm

Please post as proper dml

Select 'd', '2019-10-22'

Etc. Folks take time out of their busy schedule to help you. Help us help you

Or

INSERT INTO employees (first_name, last_name, fname) VALUES ('John', 'Capita', 'xcapit00');

Ziddy · March 8, 2019, 9:58pm

i am sorry. i am novice. i will inquire on dml and reply soonest.

yosiasz · March 8, 2019, 10:30pm

basically dml is sql data manipulation language

so in your case you need to provide us all of that data as follows

select 117221,	'2015-03-13 08:54:16.043',	'X331',	182 union
select 117222,	'2015-03-13 08:54:16.043',	'X331',	178

etc, the whole data set you pasted there.

bitsmed · March 9, 2019, 6:38am

As both selects are almost identical, you could do:

select top(1) with ties
       recipename
      ,btb_secs
      ,dcount
  from (select recipename
              ,btb_secs
              ,count(btb_secs) as dcount
          from (select recipename
                      ,datediff(second
                               ,lag([date],1,null) over(partition by recipename
                                                            order by id
                                                       )
                               ,[date]
                               )
                       as btb_secs
                  from millroom.bizuser.ban1_data
                 where recipename in ('v961','X331')
                   and processtime<480
                ) as a
         group by recipename
                 ,btb_secs
       ) as a
 order by row_number() over(partition by recipename
                                order by dcount desc
                           )
;

harishgg1 · March 11, 2019, 2:16pm

hi

i tried to solve this
its a very simple error

hope this helps

drop create data

drop table #abc 

create table #abc 
(ID	int,
Date1 datetime,	
Recipename varchar(100),
ProcessTime int  )
go 


insert into #abc select 117221,'2015-03-13 08:54:16.043','X331',182
insert into #abc select 117222,'2015-03-13 08:58:20.573','X331',178
insert into #abc select 117223,'2015-03-13 09:02:01.370','X331',166
insert into #abc select 117224,'2015-03-13 09:07:17.667','X331',176
insert into #abc select 117225,'2015-03-13 09:10:58.447','X331',171
insert into #abc select 117226,'2015-03-13 09:14:40.480','X331',179
insert into #abc select 117227,'2015-03-13 09:18:58.777','X331',185
insert into #abc select 117228,'2015-03-13 09:22:43.557','X331',175
insert into #abc select 117229,'2015-03-13 09:26:11.823','X331',164
insert into #abc select 117230,'2015-03-13 09:29:32.857','X331',163
insert into #abc select 117450,'2015-03-16 13:21:24.423','V961',210
insert into #abc select 117451,'2015-03-16 13:25:34.720','V961',213
insert into #abc select 117452,'2015-03-16 14:21:14.563','V961',231
insert into #abc select 117453,'2015-03-16 14:25:37.077','V961',213
insert into #abc select 117454,'2015-03-16 14:29:51.827','V961',214
insert into #abc select 117455,'2015-03-16 14:34:08.077','V961',207
insert into #abc select 117456,'2015-03-16 14:38:28.327','V961',212
insert into #abc select 117457,'2015-03-16 14:42:51.597','V961',212
insert into #abc select 117458,'2015-03-16 14:47:12.097','V961',212
insert into #abc select 117459,'2015-03-16 14:51:32.847','V961',212
go 

select * from #abc 
go

SQL ..

SELECT a.* 
FROM   (SELECT TOP 1 'v961'          AS Recipe, 
                     btb_secs, 
                     Count(btb_secs) AS DCount 
        FROM   (SELECT recipename, 
                       Datediff(ss, Lag([date1]) 
                                      OVER ( 
                               a         ORDER BY id), [date1])AS BTB_Secs 
                FROM   #abc 
                WHERE  recipename = 'v961' 
                       AND processtime < 480) T 
        GROUP  BY btb_secs 
        ORDER  BY dcount DESC 
        UNION ALL 
        SELECT TOP 1 'X331'          AS Recipe, 
                     btb_secs, 
                     Count(btb_secs) AS DCount 
        FROM   (SELECT recipename, 
                       Datediff(ss, Lag([date1]) 
                                      OVER ( 
                                        ORDER BY id), [date1])AS BTB_Secs 
                FROM   #abc 
                WHERE  recipename = 'X331' 
                       AND processtime < 480) T 
        GROUP  BY btb_secs 
        ORDER  BY dcount DESC) a