Zbiór zmian 792 dla trunk/SQL

Pokaż
Ignoruj:
Data:
2009-07-30 13:54:27 (17 years ago)
Autor:
marek
Opis:

re #196 - poprawki w procedurce do wyszukiwania reklam

Pliki:
1 zmodyfikowane

Legenda:

Bez zmian
Dodane
Usunięte

  • trunk/SQL/StoredProcedures/sp_GetReklamy.txt

    r732 r792  
    3030DECLARE @query nvarchar(4000) 
    3131 
     32SET @query = '' 
     33 
     34IF (NOT (@rok IS NULL)) AND (NOT (@ms IS NULL)) 
     35BEGIN 
     36 
     37SET @query = @query +  
     38' 
     39DECLARE @startDate DATETIME  
     40DECLARE @endDate DATETIME  
     41 
     42SELECT @startDate=dateadd(yy,(' + CAST(@rok as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@ms as nvarchar) + '-1,0) + 0  
     43SELECT @endDate=dateadd(yy,(' + CAST(@rok as nvarchar) + '-1900),0) + dateadd(mm,' + CAST(@ms as nvarchar) + '-1+1,0) + 0  
     44 
     45' 
     46END 
    3247 
    3348 
    34 set @query = ' 
     49SET @query = @query + ' 
     50 
    3551SELECT TOP 1000  
    36 --R.ReklamaID 
    3752R.*,  
    3853O.wyd_od AS OD,  
    3954O.wyd_do AS DO,  
    40 DATEPART(year, NR.DATA_W),  
    41 DATEPART(month, NR.DATA_W),  
    42 NR.DATA_W AS ''1emisja'',  
     55DATEPART(year, N.DATA_W),  
     56DATEPART(month, N.DATA_W),  
     57N.DATA_W AS ''1emisja'',  
    4358A.Symbol AS agencja,  
    4459(r.[CENA JEDN] - r.[RABAT WARTOŒÆ]) / r.SZER * r.WYS AS [CENA MODU£U],  
    … …  
    5166RIGHT JOIN REKLAMA R ON A2.Symbol = R.[SYMBOL AKWIZYTORA]  
    5267LEFT JOIN dbo.VIEW_REKLAMA_OD_DO_DATY O ON R.ReklamaID = O.ReklamaId  
    53 LEFT JOIN NR ON R.TYTU£ = NR.TYT AND O.wyd_od = NR.NRW -- OR O.wyd_do = NR.NRW)  
     68LEFT JOIN dbo.NR N ON R.TYTU£ = N.TYT AND O.wyd_od = N.NRW  
    5469LEFT OUTER JOIN [NAZWY MODU£ÓW] NM ON r.MOD_TYP = NM.MOD_TYP AND r.[Tytu³] = NM.Tytul 
    5570LEFT OUTER JOIN dbo.KLIENCI AS K ON R.customerId=K.CustomerId  
    … …  
    6782 
    6883if NOT (@rok IS NULL) 
    69 SET @query = @query + ' AND DATEPART(year,NR.DATA_W)=' + CAST(@rok as nvarchar) + ' ' 
     84SET @query = @query + ' AND DATEPART(year, N.DATA_W)=' + CAST(@rok as nvarchar) + ' ' 
    7085 
    71 if NOT (@ms IS NULL) 
     86if (NOT (@rok IS NULL)) AND (NOT (@ms IS NULL)) 
    7287BEGIN 
    73 SET @query = @query + ' AND DATEPART(month, O.data_od)<=' + CAST(@ms as nvarchar) + ' ' 
    74 SET @query = @query + ' AND DATEPART(month, O.data_do)>=' + CAST(@ms as nvarchar) + ' ' 
     88SET @query = @query + ' AND R.ReklamaId IN ' 
     89SET @query = @query + ' (SELECT DISTINCT U.reklamaid ' 
     90SET @query = @query + ' FROM [Uka¿e siê w Nr] U ' 
     91SET @query = @query + ' INNER JOIN Reklama R on R.ReklamaID=U.ReklamaID ' 
     92SET @query = @query + ' INNER JOIN dbo.Nr N on U.[nr wydania]=N.nrw and N.TYT=R.[Tytu³] ' 
     93SET @query = @query + ' WHERE N.Data_W>=@startDate AND N.Data_W<@endDate ' 
     94IF (NOT @fw IS NULL) 
     95SET @query = @query + ' AND U.Zafakturowana=' + CAST(@fw as nvarchar) 
     96 
     97SET @query = @query + ' ) ' 
    7598END 
    7699 
    77 if NOT (@tytul IS NULL) 
     100IF NOT (@tytul IS NULL) 
    78101SET @query = @query + ' AND R.[tytu³]=''' + @tytul + ''' ' 
    79102 
    … …  
    84107SET @query = @query + ' AND R.[Promocja]=' + CAST(@promocja as nvarchar) + ' ' 
    85108 
    86 if (NOT (@fw IS NULL) AND NOT(@ms IS NULL) AND NOT(@rok IS NULL)) 
    87 BEGIN 
    88 SET @query = @query + ' AND R.ReklamaId IN ' 
    89 SET @query = @query + ' (SELECT DISTINCT U.reklamaid ' 
    90 SET @query = @query + ' FROM [Uka¿e siê w Nr] U ' 
    91 SET @query = @query + ' INNER JOIN Reklama R on R.ReklamaID=U.ReklamaID ' 
    92 SET @query = @query + ' INNER JOIN nr N on u.[nr wydania]=N.nrw and N.TYT=R.[Tytu³] ' 
    93 SET @query = @query + ' WHERE DATEPART(MONTH, N.Data_W)=' + CAST(@ms as nvarchar) 
    94 SET @query = @query + ' AND DATEPART(YEAR, N.Data_W)=' + CAST(@rok as nvarchar) 
    95 SET @query = @query + ' AND U.Zafakturowana=' + CAST(@fw as nvarchar) + ') ' 
    96 END 
     109--if (NOT (@fw IS NULL) AND NOT(@ms IS NULL) AND NOT(@rok IS NULL)) 
     110--BEGIN 
     111--SET @query = @query + ' AND R.ReklamaId IN ' 
     112--SET @query = @query + ' (SELECT DISTINCT U.reklamaid ' 
     113--SET @query = @query + ' FROM [Uka¿e siê w Nr] U ' 
     114--SET @query = @query + ' INNER JOIN Reklama R on R.ReklamaID=U.ReklamaID ' 
     115--SET @query = @query + ' INNER JOIN nr N on u.[nr wydania]=N.nrw and N.TYT=R.[Tytu³] ' 
     116--SET @query = @query + ' WHERE N.Data_W>=@startDate AND N.Data_W<@endDate ' 
     117--SET @query = @query + ' AND U.Zafakturowana=' + CAST(@fw as nvarchar) + ') ' 
     118--END 
    97119 
    98120IF NOT (@zd IS NULL)