0

this is my query.

SELECT atdate,format(atdate,'ddd') as att_date,LvType FROM leavtran 
   WHERE AtDate between ('2019-02-01') and ('2019-02-28')

enter image description here

I want an add Sunday in the DateTime column.

yousuf jamal
  • 1
  • 2
    Why only Sunday, there seems to be more dates/days missing in your output? – Joakim Danielson Mar 18 '19 at 07:49
  • 3
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using –  Mar 18 '19 at 07:49
  • I think that at first you need to get sundays between your 2 dates. Then add them into the table. Please check this answer https://stackoverflow.com/a/37411453/6068342 – ArmKh Mar 18 '19 at 07:54
  • Create a calendar table storing all dates of interest. outer join. – jarlh Mar 18 '19 at 07:54
  • Obviously there aren't any dates that occurs on Sundays in the `leavtran` table between those dates. You just want to add them? What should be the result in the `LvType` column for those dates? – Magnus Mar 18 '19 at 08:02

1 Answers1

0

You want all days in February. Basically, this suggests a LEFT JOIN. You don't mention the database, but the idea is something like this:

SELECT dt.atdate, FORMAT(dt.atdate, 'ddd') as att_date, lt.LvType
FROM (SELECT '2019-02-01' as dte UNION ALL
      SELECT '2019-02-02' as dte UNION ALL
      . . .
      SELECT '2019-02-28' as dte
     ) d LEFT JOIN
     leavtran lt
     ON lt.AtDate = d.dte ;

In your particular case, you could also just use UNION ALL with the Sunday dates:

SELECT lt.atdate, format(lt.atdate,'ddd') as att_date, lt.LvType
FROM leavtran 
WHERE lt.AtDate BETWEEN '2019-02-01' AND '2019-02-28'
UNION ALL
SELECT '2019-02-03', 'Sun', NULL
UNION ALL
SELECT '2019-02-10', 'Sun', NULL
UNION ALL
SELECT '2019-02-17', 'Sun', NULL
UNION ALL
SELECT '2019-02-24', 'Sun', NULL
ORDER BY atdate;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786