T-SQL: data reading from SQL database using excel custom function. PART 2
CREATE TABLE TABLE2
(ORD nvarchar(10),
DT datetime,
CUSTOMER nvarchar(200),
PRODUCT_NAME nvarchar(200),
QUANTITY decimal(18,2),
PRICE decimal(18,2))
INSERT INTO TABLE2 VALUES
('001','2018-07-01','Customer Apple','TABLE 40x60',50,32.55),
('002','2018-07-02','Customer Orange','TABLE 100x60',10,65.00),
('003','2018-07-02','Customer Apple','CHAIR X1',30,15),
('004','2018-07-03','Customer Orange','CHAIR X2',20,13),
('005','2018-07-03','Customer Melon','PC IBM',10,800),
('006','2018-07-04','Customer Apple','PC HP',10,720.12),
('007','2018-07-04','Customer Orange','PC DELL',8,950),
('011','2018-08-11','Customer Apple','PC DELL',1,320.55),
('012','2018-08-12','Customer Orange','TABLE 100x60',10,25.00),
('013','2018-08-12','Customer Apple','CHAIR X1',30,15),
('014','2018-08-13','Customer Orange','CHAIR X2',50,13),
('015','2018-08-17','Customer Melon','PC IBM',5,800),
('016','2018-08-20','Customer Apple','PC HP',5,720.12),
('017','2018-08-22','Customer Orange','CHAIR X1',8,50),
('021','2018-09-01','Customer Apple','PC LENOVO',3,551),
('022','2018-09-08','Customer Orange','TABLE 200x220',1,250.00),
('023','2018-09-09','Customer Apple','CHAIR X1',30,15),
('024','2018-09-10','Customer Orange','CHAIR X2',50,13),
('025','2018-09-14','Customer Melon','CHAIR X3',90,20),
('026','2018-09-19','Customer Apple','CHAIR X4',5,12.50),
('027','2018-09-28','Customer Melon','CHAIR X1',8,50)
-------------------------------------------
SELECT *
FROM TABLE2
select
TOP 1
PRODUCT_NAME,
count(ORD) as OrderCount,
sum(QUANTITY) as TotalQuantity,
sum(QUANTITY *PRICE) as TotalPrice
FROM TABLE2
WHERE month(DT)=7 and PRODUCT_NAME ='TABLE 40x60'
group by PRODUCT_NAME
select top 1 count(ORD) as OrderCount, sum(QUANTITY) as TotalQuantity, sum(QUANTITY *PRICE) as TotalPrice FROM TABLE2 WHERE month(DT)=9 and PRODUCT_NAME ='CHAIR X2' group by PRODUCT_NAME
-------------------------------------------
DROP TABLE TABLE2
Script for Excel custom function:
http://www.dargelis.net/idea/sql-scri...
Nesta página do site você pode assistir ao vídeo on-line T-SQL: data reading from SQL database using excel custom function. PART 2 duração hora minuto segundo em boa qualidade , que foi baixado pelo usuário Alex D 07 Dezembro 2018, compartilhe o link com seus amigos e conhecidos, no youtube este vídeo já foi visto 3,156 vezes e gostou 5 espectadores. Boa visualização!
