regarding to union query result

• huy1002

  SSC-Addicted

  Points: 448

  More actions

  February 14, 2013 at 6:41 pm

  #274155

  the results orders are messed up. Need help...Thanks

  my union query

  SELECT count(distinct IAV) as IavCount, '1/1/2013- 1/5/2013' as EachWeek

  FROM IavInfo

  WHERE IAV <> 'N/A'

  AND ScanDate BETWEEN '1/1/2013' AND '1/5/2013' union

  SELECT count(distinct IAV) as IavCount, '1/6/2013- 1/12/2013' as EachWeek

  FROM IavInfo WHERE IAV <> 'N/A'

  AND ScanDate BETWEEN '1/6/2013' AND '1/12/2013' union

  SELECT count(distinct IAV) as IavCount, '1/13/2013- 1/19/2013' as EachWeek

  FROM IavInfo WHERE IAV <> 'N/A'

  AND ScanDate BETWEEN '1/13/2013' AND '1/19/2013' union

  SELECT count(distinct IAV) as IavCount, '1/20/2013- 1/26/2013' as EachWeek

  FROM IavInfo WHERE IAV <> 'N/A'

  AND ScanDate BETWEEN '1/20/2013' AND '1/26/2013' union

  SELECT count(distinct IAV) as IavCount, '1/27/2013- 1/31/2013' as EachWeek

  FROM IavInfo WHERE IAV <> 'N/A'

  AND ScanDate BETWEEN '1/27/2013' AND '1/31/2013'

  and the result is

  01/1/2013- 1/5/2013

  01/13/2013- 1/19/2013

  01/20/2013- 1/26/2013

  01/27/2013- 1/31/2013

  41/6/2013- 1/12/2013

  I would like the result

  01/1/2013- 1/5/2013

  41/6/2013- 1/12/2013

  01/13/2013- 1/19/2013

  01/20/2013- 1/26/2013

  01/27/2013- 1/31/2013

  Please help

• Ant-Green

  SSC Guru

  Points: 113528

  More actions

  February 15, 2013 at 1:39 am

  #1588225

  Use an ISO date format and not MDY, or better still, dont use a varchar string to store dates.

  declare @table table (count int, eachweek varchar(100))

  insert into @table values

  (0,'1/1/2013- 1/5/2013'),

  (0,'1/13/2013- 1/19/2013'),

  (0,'1/20/2013- 1/26/2013'),

  (0,'1/27/2013- 1/31/2013'),

  (4,'1/6/2013- 1/12/2013')

  select * from @table order by eachweek --wrong order

  declare @table2 table (count int, eachweek varchar(100))

  insert into @table2 values

  (0,'2013-01-01 - 2013-01-05'),

  (0,'2013-01-13 - 2013-01-19'),

  (0,'2013-01-20 - 2013-01-26'),

  (0,'2013-01-27 - 2013-01-31'),

  (4,'2013-01-06 - 2013-01-12')

  select * from @table2 order by eachweek --right order

  declare @table3 table (count int, weekstart date, weekend date)

  insert into @table3 values

  (0,'2013-01-01','2013-01-05'),

  (0,'2013-01-13','2013-01-19'),

  (0,'2013-01-20','2013-01-26'),

  (0,'2013-01-27','2013-01-31'),

  (4,'2013-01-06','2013-01-12')

  select * from @table3 order by weekstart --right order