Inserting dummy values at the end of select

Question

i have got query which returns address and completed orders against each address for last week. i need to insert three dummy values within the result set.

select  ADDRESS
    ,SUM(Case When  OrderDate >= dateadd(dd,(datediff(dd,-53690,getdate()-1)/7)*7,-53690)
              Then 1
            Else 0 
            End) as Completed
from orders 
GROUP BY ADDRESS
order by ADDRESS

result

Address--------------Completed

address1----------------3

address2----------------3

address3----------------3

address4----------------3

all those values are coming from the database, but i want to insert three rows with hard coded values

Expected result

Address--------------Completed

address1----------------3

address2----------------3

address3----------------3

address4----------------3

dummy1------------------0

dummy2------------------0

dummy3------------------0

unsuccessful attempt

select  ADDRESS
    ,SUM(Case When  OrderDate >= dateadd(dd,(datediff(dd,-53690,getdate()-1)/7)*7,-53690)
              Then 1
            Else 0 
            End) as Completed
from orders 

union all 
select
    'dummy1', 0
GROUP BY ADDRESS
order by ADDRESS

score 1 · Answer 1 · answered Jun 26 '14 at 16:32

Try -

select  ADDRESS
,SUM(Case When  OrderDate >= dateadd(dd,(datediff(dd,-53690,getdate()-1)/7)*7,-53690)
          Then 1
        Else 0 
        End) as Completed
from orders 
union all 
select
  'dummy1' AS Address, SUM(0) AS Completed 
GROUP BY ADDRESS
order by ADDRESS

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

Check This - It May Help You Out:

Adding a static value to the results of an SQL query

After the Union - Do

Select 'dummy1' as Address, 1 as Completed

edited May 23 '17 at 12:29

Community

1
1

answered Jun 26 '14 at 16:33

PWilliams0530

170
1
12

paparazzo · Answer 3 · 2014-06-26T17:31:26.650

0

select ADDRESS
      ,SUM(Case When  OrderDate >= dateadd(dd,(datediff(dd,-53690,getdate()-1)/7)*7,-53690)
              Then 1
              Else 0 
              End) as Completed
from orders 
GROUP BY ADDRESS
UNION
select 'dummy1', 0
UNION
select 'dummy2', 0
UNION
select 'dummy3', 0
order by ADDRESS

edited Jun 26 '14 at 17:31

answered Jun 26 '14 at 17:20

paparazzo

44,497
23
105
176

score -1 · Answer 4 · answered Jun 26 '14 at 16:41

You could create a dummy Table variable and then insert your selected records like this

DECLARE @ordertable (Address varchar(100), Completed int);

//Add your data
INSERT INTO @ordertable (Address, Completed) (
    select  ADDRESS
           ,SUM(Case When  OrderDate >= dateadd(dd,(datediff(dd,-53690,getdate()-1)/7)*7,-53690)
                Then 1
                Else 0 
                End) as Completed
    from orders 
    GROUP BY ADDRESS
    order by ADDRESS)

//Insert dummy data
INSERT INTO @ordertable (Address, Completed) VALUES(dummy1, 0)
INSERT INTO @ordertable (Address, Completed) VALUES(dummy2, 0)
INSERT INTO @ordertable (Address, Completed) VALUES(dummy3, 0)

Then just SELECT * from @ordertable and you're good to go!

your approach seems to work just amend your table creating code as below DECLARE @table TABLE ( Address NVARCHAR(100) , Completed TINYINT ) — Hakan Zim, Jun 27 '14 at 08:14

Inserting dummy values at the end of select

4 Answers4