CODEWITHSUNDEEP
sqlsql-server
Max
Max

Reputation: 3

SQL Server : creating a table from procedure with selected data

I'm pretty new to SQL Server and been trying to brush up on my skills. I came across this problem today and its stumped me. I can return the products I need but I'm unsure how to create a table using the date/month/name of product from the procedure. If anyone could help or steer me in the right direction that be greatly appreciated.

Data:

CREATE TABLE products 
(
    id INTEGER NOT NULL PRIMARY KEY,
    fruit VARCHAR(30) NOT NULL,
    dateBought DATE NOT NULL
);

INSERT INTO products (id, fruit, dateBought) VALUES (0, 'Banana', '2021-07-01');
INSERT INTO products (id, fruit, dateBought) VALUES (1, 'Apple', '2021-06-23');
INSERT INTO products (id, fruit, dateBought) VALUES (2, 'Pear', '2021-01-11');
INSERT INTO products (id, fruit, dateBought) VALUES (3, 'Peach', '2021-08-01');
INSERT INTO products (id, fruit, dateBought) VALUES (4, 'Grape', '2021-08-02');

Executing procedure:

EXEC ProductsBought '2021-07-01'

Expected output:

day    month    name
----------------------
 1      7        Banana
 1      8        Peach

My stored procedure:

CREATE PROCEDURE ProductsBought 
    (@date DATE) 
AS
BEGIN
    SELECT * 
    FROM products 
    WHERE dateBought >= @date 
      AND dateBought <= DATEADD(MONTH, 1, @date);
END;

Upvotes: 0

Views: 55

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You can use below procedure to get what you are looking for:

Create PROCEDURE ProductsBought (@date DATE) AS
BEGIN
     SELECT day(datebought)day,month(datebought)Month,fruit name FROM products WHERE dateBought >= @date AND dateBought <= DATEADD(month,1, @date);
END;

Output:

| day | month | name   |
 ----- ------- -------- 
|   1 |     7 | Banana |
|   1 |     8 | Peach  |

Upvotes: 0

critical_error
critical_error

Reputation: 6696

I assume you are looking for a resultset to be returned, not actually create a table--two very different things.

To get the date parts of a date, use DATEPART in SQL Server.

Run the following in SSMS:

-- Data mock-up.
DECLARE @products table (
    id int NOT NULL PRIMARY KEY,
    fruit varchar(30) NOT NULL,
    dateBought date NOT NULL
);

INSERT INTO @products VALUES 
    ( 0, 'Banana', '2021-07-01' ),
    ( 1, 'Apple', '2021-06-23' ),
    ( 2, 'Pear', '2021-01-11' ),
    ( 3, 'Peach', '2021-08-01' ),
    ( 4, 'Grape', '2021-08-02' );

-- Date var.
DECLARE @date date = '07/01/2021';

-- Return resultset.
SELECT
    DATEPART ( day, dateBought ) AS [day],
    DATEPART ( month, dateBought ) AS [month],
    fruit
FROM @products 
WHERE 
    dateBought BETWEEN @date AND DATEADD( month, 1, @date );

Returns

+-----+-------+--------+
| day | month | fruit  |
+-----+-------+--------+
|   1 |     7 | Banana |
|   1 |     8 | Peach  |
+-----+-------+--------+

Upvotes: 1

Related Questions