T-SQL

Re: functie sql server 2000 data anterioara

Alex — Fri, 19 Sep 2008 07:55:21 GMT

multumesc mult, e perfect.

alex.

Re: functie sql server 2000 data anterioara

B_gd_n[ ]Sahlean — Fri, 19 Sep 2008 07:30:55 GMT

O soluţie puţin mai complicată:
ALTER FUNCTION PrajestePestele (@data DATETIME)
RETURNS @tabel TABLE (Data DATETIME)
AS
BEGIN
  DECLARE @zi INT
  SET @zi = CAST(@data AS INT) % 7

  IF @zi = 0 --L
    BEGIN
      INSERT INTO @tabel VALUES( DATEADD(dd,-3,@data) )
      INSERT INTO @tabel VALUES( DATEADD(dd,-4,@data) )
    END
  ELSE
    IF @zi = 1 --Ma
      BEGIN
        INSERT INTO @tabel VALUES( DATEADD(dd,-1,@data) )
        INSERT INTO @tabel VALUES( DATEADD(dd,-4,@data) )
      END
    ELSE
      IF @zi IN (2,3,4)
      BEGIN
        INSERT INTO @tabel VALUES( DATEADD(dd,-1,@data) )
        INSERT INTO @tabel VALUES( DATEADD(dd,-2,@data) )
      END

  RETURN
END
GO

SELECT * FROM dbo.PrajestePestele('2008-09-12')
SELECT * FROM dbo.PrajestePestele('2008-09-13')
SELECT * FROM dbo.PrajestePestele('2008-09-14')
SELECT * FROM dbo.PrajestePestele('2008-09-15')
SELECT * FROM dbo.PrajestePestele('2008-09-16')
GO

SELECT CASE
    WHEN EXISTS(SELECT * FROM dbo.PrajestePestele('2008-09-15')) THEN 'Se poate praji'
    ELSE 'Nu se poate praji'
    END

Re: functie sql server 2000 data anterioara

ignatandrei — Fri, 19 Sep 2008 07:26:43 GMT

Alex:
am asta:

declare @date datetime
set @date = '20080919'

DECLARE @varDayOfWeek INT
SET @varDayOfWeek =((CAST(cast((RTRIM(CONVERT(CHAR(20),@date,101))+' 12:00:00.000') as DATETIME) as INT)%7))

IF (@varDayOfWeek=0 OR @varDayOfWeek=6)
SELECT 'Nu se poate procesa'
ELSE
SELECT dateadd(day,-1,@date) oZiInUrma, dateadd(day,-2,@date) douaZileInUrma

dar cum fac daca oZiInUrma sau douaZileInUrma sa mearga in urma la ziua lucratoare

alex.

Obs 1
Select ((CAST(cast((RTRIM(CONVERT(CHAR(20),getdate(),101))+' 12:00:00.000') as DATETIME) as INT)%7))
e aceeasi ca
select datepart(dw,getdate())-1 ( ma rog, depinde si de setdatefirst)

obs 2.
ca sa fie in zi lucratoare
if e luni si selectezi nu 2 zile in urma, ci 3 si4
if e marti, selectezi 1 zi in urma si 4
else
2 zile in urma

Re: functie sql server 2000 data anterioara

rsocol — Fri, 19 Sep 2008 07:23:23 GMT

CREATE FUNCTION ZilelePrecedente(@Data datetime)
RETURNS TABLE AS RETURN
SELECT @Data-CASE (DATEPART(weekday,@Data)+@@DATEFIRST)%7
       WHEN 0 THEN null
       WHEN 1 THEN null
       WHEN 2 THEN 3
       WHEN 3 THEN 1
       WHEN 4 THEN 1
       WHEN 5 THEN 1
       WHEN 6 THEN 1
   END AS Data
UNION ALL
SELECT @Data-CASE (DATEPART(weekday,@Data)+@@DATEFIRST)%7
       WHEN 0 THEN null
       WHEN 1 THEN null
       WHEN 2 THEN 4
       WHEN 3 THEN 4
       WHEN 4 THEN 2
       WHEN 5 THEN 2
       WHEN 6 THEN 2
   END AS Data
GO
SELECT * FROM ZilelePrecedente('20080918')

Răzvan

Re: functie sql server 2000 data anterioara

Alex — Fri, 19 Sep 2008 07:18:33 GMT

am asta:

declare @date datetime
set @date = '20080919'

DECLARE @varDayOfWeek INT
SET @varDayOfWeek =((CAST(cast((RTRIM(CONVERT(CHAR(20),@date,101))+' 12:00:00.000') as DATETIME) as INT)%7))

IF (@varDayOfWeek=0 OR @varDayOfWeek=6)
SELECT 'Nu se poate procesa'
ELSE
SELECT dateadd(day,-1,@date) oZiInUrma, dateadd(day,-2,@date) douaZileInUrma

dar cum fac daca oZiInUrma sau douaZileInUrma sa mearga in urma la ziua lucratoare

alex.

Re: functie sql server 2000 data anterioara

Alex — Fri, 19 Sep 2008 06:57:52 GMT

nu, nu ma intereseaza sarbatorile legale

multumesc

Re: functie sql server 2000 data anterioara

rsocol — Fri, 19 Sep 2008 06:47:41 GMT

Se poate face, dar eşti sigur că nu trebuie să ţii cont şi de sărbătorile legale ?

Răzvan

Re: functie sql server 2000 data anterioara

Alex — Fri, 19 Sep 2008 06:43:34 GMT

2000

Re: functie sql server 2000 data anterioara

ignatandrei — Fri, 19 Sep 2008 06:35:56 GMT

in ce vers de sql server ? 2000, 2005, 2008 ? ( ideea de return table ...)

functie sql server 2000 data anterioara

Alex — Fri, 19 Sep 2008 06:26:09 GMT

salut, aveti cumva o functie asemanatoare? as vrea o functie care primeste ca parametru o data si:

daca data respectiva este sambata sau duminica imi da mesaj de eroare "Nu se poate genera"

daca data respectiva este o zi lucratoare intoarce asa

primeste luni intoarce joi si vineri din saptamana trecuta

primeste marti intoarce vineri saptamana trecuta si luni dinaintea lui marti primit

primeste miercuri intoarce luni si marti adica 2 zile din urma

si tot asa.....

multumesc mult

alex.