Generate sequence of stations

• ramrajan

  SSCommitted

  Points: 1737

  More actions

  August 16, 2019 at 5:02 am

  #3671751

  The solution would be compatible with sql 2000 edition

  below are my tables

  create table dates( Dates date )

  create table Stations( Dates date, Station int )

  -- Dates Master Table ( I have data for 30 Years, But only provided sample data )

  -- Date format (MM/DD/YYYY)

  Jan 1 to Jan 31

  insert into dates values('01/01/2019')

  insert into dates values('01/02/2019')

  insert into dates values('01/03/2019')

  ..................................

  ......................................

  insert into dates values('01/31/2019')

  -- From Feb 1 to Feb 28

  insert into dates values('02/01/2019')

  insert into dates values('02/02/2019')

  insert into dates values('02/03/2019')

  .............................................................

  .............................................................

  .............................................................

  insert into dates values('02/28/2019')

  -- Stations Data

  insert into Stations values('01/01/2019',1)

  insert into Stations values('01/02/2019',1)

  insert into Stations values('01/02/2019',5)

  insert into Stations values('02/03/2019',3)

  I need the below output if there was a station for one day also the data should be generate for the whole month with series

  Dates Stations

  01/01/2019 1

  01/02/2019 1

  ...........................

  ...........................

  ...........................

  ...........................

  ...........................

  01/31/2019 1

  01/01/2019 5

  01/02/2019 5

  ...........................

  ...........................

  ...........................

  ........................... .

  ..........................

  01/31/2019 5

  02/01/2019 3

  02/02/2019 3

  ...........................

  ...........................

  ...........................

  ...........................

  ...........................

  02/28/2019 3

  • This topic was modified 4 years, 8 months ago by  ramrajan.

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  August 16, 2019 at 12:27 pm

  #3671805

  You have 2 tables?

  1. Dates  -- contains the master list of dates.  Table has 1 column.
  2. Stations -- contains the date and # of stations.  Table has 2 columns

  The output you're looking for:

  1. A list of days where there was only 1 station?
  2. A list of months with the series of station counts as single column?

  Does the "Dates" table contain a date for every day on the calendar?  What format does the "series" returned with the month need to be in?

   

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können