Alguen tiene un enlace de como hacer un proyecto crud con postgrate sql con interfaz python flask?

ya tengo la base de datos, pero nose como conectarla a python y poder hacer botones de consulas crud usando flask, alguien tiene algún enlace de algun video o alguna pagina o sugerencia? me ayudaría mucho por favor

Can I restore my SQL Server database to a point before the last full backup with my current backup strategy?

If I have this backup strategy(weekly full backup and 1 hour log backup), can I restore the database to the green highlighted period of time? Btw, does log backup 2 includes all log records that have their lsn great than the last_lsn of log backup 1?

enter image description here

SQL Left-Join Statement for cart

Can someone explain this sql code for me we use it to add items on cart

SELECT c.crt_id, i.item_id, i.item_name, i.price,i.image FROM cart c LEFT JOIN item i ON c.item_id = i.item_id WHERE c.customer_no = 1 AND c.delete_flg = 0

database design – Best practices when designing SQL DB with “redundant” tables

I have a design dilemma for a DB I’m creating for an e-commerce platform I want to develop.

I have 3 different actors interacting with the website:

  • Customer
  • Manager
  • Supplier

Those 3 actors have the same table structure: email, username, address…

My initial design for the DB was to create a single table (StoreUser), with an additional field to distinguish between the 3 different types of actors.

The issue I see with this design is that when referencing a Customer in the “Order” table for instance, to assign an Order to a Customer, it would be technically possible to assign a “Manager” or a “Supplier” to the order, even though it isn’t wanted. Same thing for the “Product” table, to which a Supplier’s foreign key should be provided, the “StoreUser” FK would not distinguish between the 3 actors.

On the other hand, creating 3 tables, containing the exact same datafields, seems really redundant, especially from the code perspective (I’m using Django for the website, and I really don’t like the idea of having 3 different classes with the same structure.

Which one seems the most logical to you? What’s the good practice here?

sql server – Check if contract conflict with another contract query

I have a database with these tables as the bellow picture showed:

enter image description here

The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts, everything works perfectly until now.
my problem in renewing the contract, I have a feature that allows the user to renew a contract, in this case, I get all the original contract info and make the new contract start from the end date of the original contract + 1 day with the same original contract info.

my question here is how to check if this renewal contract does not conflict with other contracts for the same customer and the same shops (customer can have many contracts)

E.G.:

I have contracts like this:

con_id    shop_id   start_date    end_date
--------------------------------------------
  1         1       14-04-2021    14-04-2022
  2         1       15-04-2022    15-04-2023

If the user clicked on contract number 1 and tried to renew it, I want a query to check if the new contract start date does not conflict with another contract for this user and these shops.

Like in my above data I want to prevent the user from renewing contract number 1 again because there is already a contract renewed before in that period.

This is what I tried:

--IF EXISTS (SELECT * FROM contracts_view where cust_id=123456789 and @date_start >= date_start and @date_start <= date_end and shop_id in (select shop_id from contracts_shops where contract_id =@old_contract_id)) 
--BEGIN
--    SELECT @ErrorMessage = ERROR_MESSAGE()
--  RAISERROR ('asdasd', 10, 1)
--  ROLLBACK TRAN
--   return
--END

and here is my stored procedure for renewing a contract:

ALTER PROCEDURE (dbo).(contract_renew)
-- Add the parameters for the stored procedure here
@cust_id int,
@duration int,
@price decimal(10,2),
@tax decimal(10,2),
@usage nvarchar(20),
@rent_type nvarchar(10),
@price2 decimal(10,2),
@note2 nvarchar(max),
@date_start date,
@date_end date,
@note nvarchar(max),
@app_user nvarchar(20),
@old_contract_id int
AS
BEGIN

DECLARE @ErrorMessage NVARCHAR(MAX)
DECLARE @ID int

BEGIN TRAN
BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--insert data
INSERT INTO (dbo).(contracts)
       ((cust_id)
       ,(duration)
       ,(price)
       ,(tax)
       ,(usage)
       ,(rent_type)
       ,(price2)
       ,(note2)
       ,(date_start)
       ,(date_end)
       ,(note)
       ,(app_user))
 VALUES
       (@cust_id,
       @duration,
       @price,
       @tax,
       @usage,
       @rent_type,
       @price2,
       @note2,
       @date_start,
       @date_end,
       @note,
       @app_user) SELECT SCOPE_IDENTITY();

       SET @ID = SCOPE_IDENTITY();


insert into contracts_shops (contract_id, shop_id)
select @ID, shop_id
FROM contracts_shops WHERE contract_id = @old_contract_id;

COMMIT

END TRY
BEGIN CATCH
    SELECT @ErrorMessage = ERROR_MESSAGE()
    RAISERROR (@ErrorMessage, 10, 1)
    ROLLBACK TRAN
END CATCH
END

SQL Cambiar formato de fecha a texto

necesito de su ayuda para cambiar el formato de un campo fecha para una consulta select en SQL (formato original del campo 1947-08-07 00:00:00.000) a un formato de texto simple algo así 19470807000000000

utilizando lo comandos CONVERT (CHAR (50),FechaNacimiento, 112) AS fecha obtengo 19470807 como fecha bien, pero la hora CONVERT (CHAR (50),FechaNacimiento, 114) AS hora obtengo 00:00:00:000, si utilizo el CAST (RIGHT (FechaNacimiento, 7) AS CHAR (50)) AS HORA obtengo 12:00AM,
utilizando CAST (FechaNacimiento AS varchar ) AS fecha_hora obtengo Aug 7 1947 12:00AM

y necesito obtener el texto simple de la hora 000000000 000000 o en su defecto de todo el campo 19470807000000000

Como verna en los ejemplos utilizando el CONVERT y el CAST no me son muy útiles para la conversion que necesito.

alguien conocerá algún otro método en SQL

Nota: el Objetivo es crear una vista alterando los formatos de fecha y hora originales de la tabla en un texto simple.

subquery – Subqueries in SQL Server

I am taking a SQL course and am completely stumped on this question.

Using a subquery have a query that returns:
CustomerID, EmailAddress, FirstName, LastName, ‘#3: Customers who have ordered than 1 product’ as
queryInfo
The subquery from this query will be: All customers with who have ordered more than 1 product

Note: More than one Product, not Quantity > 1 So a customer who ordered 10 of the same
product is NOT what I’m looking for.

Here is the full query I used:

SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE c.CustomerID IN (
           SELECT p.ProductID 
             FROM Customers c 
             JOIN Orders o
                  ON c.CustomerID = o.CustomerID
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             JOIN Products p
                  ON oi.ProductID = p.ProductID 
         GROUP BY p.ProductID 
           HAVING COUNT(*) > 1
         )
     ;

Not sure what I am doing wrong, but any input is greatly appreciated. Thanks!

sql – Getting new vs. returning customers in Postgres

I have this work task, to write a SQL query that would show how many of the active customers are new (= do not have prior transactions) as opposed to returning per a given period.

There is nothing particular in the DB to get this, so my solution takes a transaction and compares customer_id to the pool of customer_ids that belong to all transactions that took place prior. Here’s how it looks:

SELECT allthem.period, allthem.c "all", newonly.c "new", (allthem.c - newonly.c) "returning"
FROM (
    SELECT date_trunc('week', t.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
    FROM transactions t
    WHERE t.status = 'paid' AND (t.price->>'payment_total')::real > 35
    GROUP BY date_trunc('week', t.paid::timestamptz)
    ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS allthem
JOIN (
    SELECT date_trunc('week', b.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
    FROM transactions t
    WHERE t.status = 'paid' AND (t.price->>'payment_total')::real > 35
    AND t.customer_id NOT IN (SELECT customer_id FROM transactions WHERE status='paid' AND (price->>'payment_total')::real > 35 AND paid::timestamptz < t.paid::timestamptz)
    GROUP BY date_trunc('week', t.paid::timestamptz)
    ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS newonly ON allthem.period=newonly.period
WHERE allthem.period > date_trunc('week', now()::timestamptz at time zone 'pst') - interval '12 months'

It works, but the problem is that it is quite slow.

Is there any way to compute the required data with less server load?

development – Query sharepoint list data in sql server


Your privacy


By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.




sql server – How to Cast today data as bigint to Data – 202104171830


Your privacy


By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.