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.

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

---

Ignat Andrei
http://serviciipeweb.ro/iafblog

2000

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

Răzvan

nu, nu ma intereseaza sarbatorile legale

multumesc

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.

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

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

---

Ignat Andrei
http://serviciipeweb.ro/iafblog

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

multumesc mult, e perfect.

alex.