-1

I'm looking for a script to create an new table based on an other.

Input table:
Input table:

OutputTable:
Output table:
Original was built in QlikView but now I have to convert it to SQL

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tom Hovens
  • 11
  • What [tag:rdbms] are you using? – Mureinik Apr 01 '20 at 18:33
  • It's a Azure SQL and use SQL Server Management Studio as tool – Tom Hovens Apr 01 '20 at 18:42
  • 1
    Tips and tools to improve the quality of your question, and help us help you. [How to post a SQL question on a public forum](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/). – Eric Brandt Apr 01 '20 at 18:47
  • Hey Tom, can you explain what you're trying to do with the data? It appears that you're just consolidating the `StartDate` and `EndDate` columns into a new `Date` column—presumably based on the `StartDate` value?—and moving that column to the front of the table. Is that correct? – Jeremy Caney Apr 01 '20 at 19:22
  • Hi Jeremy, thank you for answering me. Yes based on the startdate and enddate, it should create a new record per date with the quantity. – Tom Hovens Apr 01 '20 at 19:52
  • Learn to use sets in your tsql code. A [table of numbers](https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) can be generated in many different ways. When you cross join your current table to the table of numbers you should see a path to your goal. [Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=33985f41136cc3b7572785d951e6eae3) to demonstrate. Also, a calendar table (which is just a purpose-built table of numbers) is something you should have (or learn to create dynamically). – SMor Apr 01 '20 at 20:08

1 Answers1

0

Your question is not crystal clear, but I'll take a shot at what i think you want. This is a great scenario for using a calendar table! I've abbreviated the calendar table here, and used US style date, but you get the idea. Also, in your example, I think you've added an extraneous row in the results. There probably should not be a row with 1-4-2020 X B 2.

DECLARE @cal TABLE
(
    dt DATE
);

INSERT  @cal
(
    dt
)
VALUES
('04-01-2020'),('04-02-2020'),('04-03-2020'),('04-04-2020'),
('04-05-2020'),('04-06-2020'),('04-07-2020'),('04-08-2020'),
('04-09-2020'),('04-10-2020'),('04-11-2020'),('04-12-2020'),
('04-13-2020'),('04-14-2020'),('04-16-2020'),('04-17-2020'),
('04-18-2020'),('04-19-2020'),('04-20-2020'),('04-21-2020'),
('04-22-2020'),('04-23-2020'),('04-24-2020'),('04-25-2020'),
('04-26-2020'),('04-27-2020'),('04-28-2020'),('04-29-2020'),
('04-30-2020');

DECLARE @Input TABLE
(
    ProjectID CHAR(1),
    Item CHAR(1),
    Quantity INT,
    Startdate DATE,
    EndDate DATE
);

INSERT  @Input
(
    ProjectID,
    Item,
    Quantity,
    Startdate,
    EndDate
)
VALUES
(   'X',
    'A',
    1,
    '04-01-2020',
    '04-05-2020'),
(   'Y',
    'A',
    2,
    '04-01-2020',
    '04-06-2020'),
(   'X',
    'B',
    2,
    '04-02-2020',
    '04-05-2020');

/* Join to the calendar table. */

SELECT  c.dt,
        i.ProjectID,
        i.Item,
        i.Quantity
FROM    @Input AS i
JOIN    @cal   AS c
  ON    c.dt
        BETWEEN i.startdate AND i.EndDate
        ORDER BY i.ProjectID, i.Item, c.dt
R Evans
  • 36
  • 4
  • 1
    Aside: If you use the ISO date format of `YYYYMMDD`, SQL Server will always interpret it correctly, regardless of regional/language settings. (Other RDMSs prefer the ANSI `YYYY-MM-DD`, but SQL Server will get confused with that one.) – Eric Brandt Apr 01 '20 at 21:40