SELECT con ricerca specifica su data

[Norby]

Ciao a tutti, in VB ho una variabile che prende il mese e l'anno nel quale
siamo.

Vorrei fare una select nel DB che mi restituisca solo le entry che hanno sul
campo data (datetime) un giorno del mese/anno nel quale siamo.

Vi faccio un esempio concreto:

tabella X
----------------------------------------------------------
ID (pk int)
ARTICOLO varchar(20)
DATA (datetime)
----------------------------------------------------------
1 FASCAW 31/05/2009
2 ABCDEFG 01/09/2009 <----
3 ASDFAXX 21/09/2009 <----
4 ZDFWFGG 03/10/2009

Mi deve uscire solo le righe con ID 2 e 3.

Come posso fare?

[Lorenzo Benaglia]

Post by Norby
Vorrei fare una select nel DB che mi restituisca solo le entry che hanno sul
campo data (datetime) un giorno del mese/anno nel quale siamo.

Ciao Norby,

Il problema consiste semplicemente nell'escogitare un modo per determinare
l'ultimo giorni del mese.
Una soluzione potrebbe essere quella di definire una UDF che tramite le
funzioni DATEDIFF() e DATEADD() vada a determinare tale valore.
L'algoritmo sembra "incasinato" ma in realtà è molto semplice: come prima
cosa si calcola il numero di mesi intercorsi tra la data passata come
argomento e la base date di SQL Server (1 gennaio 1900 equivalente
all'intero 0) sommando 1 mese, poi si aggiuge tale valore espresso in mesi
alla base date detraendo 1 giorno.
A questo punto non ti resta che scrivere la query:

USE tempdb;
GO

CREATE TABLE dbo.Articoli(
ID int NOT NULL PRIMARY KEY,
Articolo varchar(20) NOT NULL,
Data datetime NOT NULL
);

INSERT dbo.Articoli VALUES
(1, 'FASCAW', '20090531')
, (2, 'ABCDEFG', '20090901')
, (3, 'ASDFAXX', '20090921')
, (4, 'ZDFWFGG', '20091003');
GO

CREATE FUNCTION dbo.ufn_GetLastMonthDay(
@Today datetime
)
RETURNS datetime
BEGIN
RETURN DATEADD(month, DATEDIFF(month, 0, @Today) + 1, 0) - 1;
END
GO

DECLARE @Month int = 9, @Year int = 2009;
DECLARE @From datetime = CAST(@Year AS char(4)) + LEFT('0' + CAST(@Month as
varchar(2)), 2) + '01';
DECLARE @To datetime = dbo.ufn_GetLastMonthDay(@From);

SELECT *
FROM dbo.Articoli
WHERE Data BETWEEN @From AND @To;

/* Output:

ID Articolo Data
----------- -------------------- -----------------------
2 ABCDEFG 2009-09-01 00:00:00.000
3 ASDFAXX 2009-09-21 00:00:00.000

(2 row(s) affected)
*/

DROP FUNCTION dbo.ufn_GetLastMonthDay;
DROP TABLE dbo.Articoli;

Ciao!

[Norby]

Post by Lorenzo Benaglia

Post by Norby
Vorrei fare una select nel DB che mi restituisca solo le entry che hanno sul
campo data (datetime) un giorno del mese/anno nel quale siamo.

Ciao Norby,
Il problema consiste semplicemente nell'escogitare un modo per determinare
l'ultimo giorni del mese.
Una soluzione potrebbe essere quella di definire una UDF che tramite le
funzioni DATEDIFF() e DATEADD() vada a determinare tale valore.
L'algoritmo sembra "incasinato" ma in realtà è molto semplice: come prima
cosa si calcola il numero di mesi intercorsi tra la data passata come
argomento e la base date di SQL Server (1 gennaio 1900 equivalente
all'intero 0) sommando 1 mese, poi si aggiuge tale valore espresso in mesi
alla base date detraendo 1 giorno.
USE tempdb;
GO
CREATE TABLE dbo.Articoli(
ID int NOT NULL PRIMARY KEY,
Articolo varchar(20) NOT NULL,
Data datetime NOT NULL
);
INSERT dbo.Articoli VALUES
(1, 'FASCAW', '20090531')
, (2, 'ABCDEFG', '20090901')
, (3, 'ASDFAXX', '20090921')
, (4, 'ZDFWFGG', '20091003');
GO
CREATE FUNCTION dbo.ufn_GetLastMonthDay(
@Today datetime
)
RETURNS datetime
BEGIN
END
GO
varchar(2)), 2) + '01';
SELECT *
FROM dbo.Articoli
ID Articolo Data
----------- -------------------- -----------------------
2 ABCDEFG 2009-09-01 00:00:00.000
3 ASDFAXX 2009-09-21 00:00:00.000
(2 row(s) affected)
*/
DROP FUNCTION dbo.ufn_GetLastMonthDay;
DROP TABLE dbo.Articoli;
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo
http://italy.mvps.org

Ciao Lorenzo!.
In primis, grazie per l'aiuto. Vorrei chiederti qualche chiarimento in
merito al codice da te riportato...

Naturalmente io non devo ricreare la tabella in quanto esiste già quindi la
parte di costruzione tabella che hai riportato non mi serve :
=====================
USE tempdb;
GO

CREATE TABLE dbo.Articoli(
ID int NOT NULL PRIMARY KEY,
Articolo varchar(20) NOT NULL,
Data datetime NOT NULL
);
=====================
E i valori idem
=====================
INSERT dbo.Articoli VALUES
(1, 'FASCAW', '20090531')
, (2, 'ABCDEFG', '20090901')
, (3, 'ASDFAXX', '20090921')
, (4, 'ZDFWFGG', '20091003');
GO
=====================

Passiamo alla funtion..

Devo inserirla in una Stored Procedure?
Perchè io la devo interpretare poi da ASP.NET mediante un datareader

Quindi non so se devo inglobare tutto quanto hai scritto in una super
stringa oppure mettere il todos in una SP e richiamarla..

Ad ogni modo, mettiamo che prendo una stored procedure:

Devo scrivere questo:

CREATE FUNCTION dbo.ufn_GetLastMonthDay(
@Today datetime
)
RETURNS datetime
BEGIN
RETURN DATEADD(month, DATEDIFF(month, 0, @Today) + 1, 0) - 1;
END
GO

DECLARE @Month int = OXOXOXOXOXOXOXOXOX, @Year int = OXOXOXOXOXOXOXOXOX;
DECLARE @From datetime = CAST(@Year AS char(4)) + LEFT('0' + CAST(@Month as
varchar(2)), 2) + '01';
DECLARE @To datetime = dbo.ufn_GetLastMonthDay(@From);

SELECT *
FROM dbo.Articoli
WHERE Data BETWEEN @From AND @To;
============================================

Dove ho scritto:
OXOXOXOXOXOXOXOXOX
OXOXOXOXOXOXOXOXOX

Gli devo passare le 2 variabili di cui parlavo prima giusto? in formato
intero.

Se ho capito bene, passo a provare :-)

Grazie! Norby

[Lorenzo Benaglia]

Post by Norby
Naturalmente io non devo ricreare la tabella in quanto esiste già quindi la

Eh si, ma è buona norma fornire un esempio completo e funzionante in modo da
illustrare nel dettaglio la soluzione al problema.

Post by Norby
Passiamo alla funtion..
Devo inserirla in una Stored Procedure?

Una user-defined function non può essere definita in una stored procedure,
al più può essere invocata.

Post by Norby
Gli devo passare le 2 variabili di cui parlavo prima giusto? in formato
intero.

Allora, in questo caso puoi definire una stored procedure che andrai a
parametrizzare e richiamare lato VB:

USE tempdb;
GO

CREATE TABLE dbo.Articoli(
ID int NOT NULL PRIMARY KEY,
Articolo varchar(20) NOT NULL,
Data datetime NOT NULL
);

INSERT dbo.Articoli VALUES
(1, 'FASCAW', '20090531')
, (2, 'ABCDEFG', '20090901')
, (3, 'ASDFAXX', '20090921')
, (4, 'ZDFWFGG', '20091003');
GO

CREATE FUNCTION dbo.ufn_GetLastMonthDay(
@Today datetime
)
RETURNS datetime
BEGIN
RETURN DATEADD(month, DATEDIFF(month, 0, @Today) + 1, 0) - 1;
END
GO

CREATE PROCEDURE dbo.up_GetArticlesByDate(
@Month int,
@Year int
)
AS
DECLARE @From datetime = CAST(@Year AS char(4)) + LEFT('0' + CAST(@Month AS
varchar(2)), 2) + '01';
DECLARE @To datetime = dbo.ufn_GetLastMonthDay(@From);

SELECT *
FROM dbo.Articoli
WHERE Data BETWEEN @From AND @To;
GO

EXEC dbo.up_GetArticlesByDate 9, 2009;

/* Output:

ID Articolo Data
----------- -------------------- -----------------------
2 ABCDEFG 2009-09-01 00:00:00.000
3 ASDFAXX 2009-09-21 00:00:00.000

(2 row(s) affected)
*/

DROP PROCEDURE dbo.up_GetArticlesByDate;
DROP FUNCTION dbo.ufn_GetLastMonthDay;
DROP TABLE dbo.Articoli;

Post by Norby
Grazie! Norby

Prego.

Ciao!

[Alx]

Post by Lorenzo Benaglia

Post by Norby
Naturalmente io non devo ricreare la tabella in quanto esiste già quindi la

Eh si, ma è buona norma fornire un esempio completo e funzionante in modo da
illustrare nel dettaglio la soluzione al problema.

Post by Norby
Passiamo alla funtion..
Devo inserirla in una Stored Procedure?

Una user-defined function non può essere definita in una stored procedure,
al più può essere invocata.

Post by Norby
Gli devo passare le 2 variabili di cui parlavo prima giusto? in formato
intero.

Allora, in questo caso puoi definire una stored procedure che andrai a
USE tempdb;
GO
CREATE TABLE dbo.Articoli(
ID int NOT NULL PRIMARY KEY,
Articolo varchar(20) NOT NULL,
Data datetime NOT NULL
);
INSERT dbo.Articoli VALUES
      (1, 'FASCAW', '20090531')
    , (2, 'ABCDEFG', '20090901')
    , (3, 'ASDFAXX', '20090921')
    , (4, 'ZDFWFGG', '20091003');
GO
CREATE FUNCTION dbo.ufn_GetLastMonthDay(
@Today datetime
)
RETURNS datetime
BEGIN
END
GO
CREATE PROCEDURE dbo.up_GetArticlesByDate(
@Month int,
@Year int
)
AS
varchar(2)), 2) + '01';
SELECT *
FROM dbo.Articoli
GO
EXEC dbo.up_GetArticlesByDate 9, 2009;
ID          Articolo             Data
----------- -------------------- -----------------------
2           ABCDEFG              2009-09-01 00:00:00.000
3           ASDFAXX              2009-09-21 00:00:00.000
(2 row(s) affected)
*/
DROP PROCEDURE dbo.up_GetArticlesByDate;
DROP FUNCTION dbo.ufn_GetLastMonthDay;
DROP TABLE dbo.Articoli;

Post by Norby
Grazie! Norby

Prego.
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Serverhttp://blogs.dotnethell.it/lorenzohttp://italy.mvps.org

Ma perchè nessuno ha preso in considerazione di mettere nella where

SELECT *
FROM dbo.Articoli
WHERE month(Data) = @month and year(Data) = @year

è troppo dispendioso ?

[Norby]

Post by Alx

Post by Lorenzo Benaglia

Post by Norby
Naturalmente io non devo ricreare la tabella in quanto esiste già quindi la

Eh si, ma è buona norma fornire un esempio completo e funzionante in modo da
illustrare nel dettaglio la soluzione al problema.

Post by Norby
Passiamo alla funtion..
Devo inserirla in una Stored Procedure?

Una user-defined function non può essere definita in una stored procedure,
al più può essere invocata.

Post by Norby
Gli devo passare le 2 variabili di cui parlavo prima giusto? in formato
intero.

Allora, in questo caso puoi definire una stored procedure che andrai a
USE tempdb;
GO
CREATE TABLE dbo.Articoli(
ID int NOT NULL PRIMARY KEY,
Articolo varchar(20) NOT NULL,
Data datetime NOT NULL
);
INSERT dbo.Articoli VALUES
(1, 'FASCAW', '20090531')
, (2, 'ABCDEFG', '20090901')
, (3, 'ASDFAXX', '20090921')
, (4, 'ZDFWFGG', '20091003');
GO
CREATE FUNCTION dbo.ufn_GetLastMonthDay(
@Today datetime
)
RETURNS datetime
BEGIN
END
GO
CREATE PROCEDURE dbo.up_GetArticlesByDate(
@Month int,
@Year int
)
AS
varchar(2)), 2) + '01';
SELECT *
FROM dbo.Articoli
GO
EXEC dbo.up_GetArticlesByDate 9, 2009;
ID Articolo Data
----------- -------------------- -----------------------
2 ABCDEFG 2009-09-01 00:00:00.000
3 ASDFAXX 2009-09-21 00:00:00.000
(2 row(s) affected)
*/
DROP PROCEDURE dbo.up_GetArticlesByDate;
DROP FUNCTION dbo.ufn_GetLastMonthDay;
DROP TABLE dbo.Articoli;

Post by Norby
Grazie! Norby

Prego.
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Serverhttp://blogs.dotnethell.it/lorenzohttp://italy.mvps.org

Ma perchè nessuno ha preso in considerazione di mettere nella where
SELECT *
FROM dbo.Articoli
è troppo dispendioso ?

Alx, ho fatto funzionare il tutto come consiglia Lorenzo, ma mentre scrivevo
avevo anche io lo stesso tuo dubbio.
La risposta che mi son dato è stata: sicuramente è un impiego migliore di
istruzioni SQL.

Ad ogni modo vi ringrazio entrambi!

[Marcello]

Post by Norby
Alx, ho fatto funzionare il tutto come consiglia Lorenzo, ma mentre scrivevo
avevo anche io lo stesso tuo dubbio.
La risposta che mi son dato è stata: sicuramente è un impiego migliore di
istruzioni SQL.

Ciao,

la scelta di Lorenzo nasce dal bisogno di non applicare funzioni ai
campi e di applicarle invece alle costanti al fine di permettere a sql
server di accedere ad eventuali indici.

Per capire la questione considera questo semplice esempio:

Data la tabella:

test(x int)

estrarre le righe per cui la radice(x)<100.
Risulta naturale scrivere la query come:

where sqrt(x)<100

ma in questo modo sql non può più usare alcun indice su x a meno di non
riuscire ad invertire la funzione sqrt e invertire una funzione,
operazione non sempre possibile, è molto complesso.

La query va quindi scritta come:

where x<(100)^2

in questo modo sql può sfruttare un eventuale indice su x e essere
estremamente efficiente.

Post by Norby
Ad ogni modo vi ringrazio entrambi!

marc.

[Alx]

Post by Marcello

Post by Norby
Alx, ho fatto funzionare il tutto come consiglia Lorenzo, ma mentre scrivevo
avevo anche io lo stesso tuo dubbio.
La risposta che mi son dato è stata: sicuramente è un impiego migliore di
istruzioni SQL.

Ciao,
la scelta di Lorenzo nasce dal bisogno di non applicare funzioni ai
campi e di applicarle invece alle costanti al fine di permettere a sql
server di accedere ad eventuali indici.
test(x int)
estrarre le righe per cui la radice(x)<100.
where sqrt(x)<100
ma in questo modo sql non può più usare alcun indice su x a meno di non
riuscire ad invertire la funzione sqrt e invertire una funzione,
operazione non sempre possibile, è molto complesso.
where x<(100)^2
in questo modo sql può sfruttare un eventuale indice su x e essere
estremamente efficiente.

Post by Norby
Ad ogni modo vi ringrazio entrambi!

marc.

E' vero che bisogna sempre cercare di utilizzare gli indici per
velocizzare al massimo le query, però bisogna anche vedere in che
contesto. Sicuramente la soluzione migliore resta quella di Lorenzo.

[Lorenzo Benaglia]

Post by Alx
E' vero che bisogna sempre cercare di utilizzare gli indici per
velocizzare al massimo le query, però bisogna anche vedere in che
contesto.

Una query efficiente lo è in qualunque contesto, quindi ha poco senso fare
il contrario :-)

Ciao!