Ayuda Query

[Former Member]

Jovenes me pidieron un reporte en donde muestre solo los datos de una semana o los dias que necesiten ver pero no he logrado hacer que solo me muestre los dias que se necesita les dejo el query para que puedan indicar en donde esta mi error

SELECT T2.[DocDate][Fecha], T3.[ItemCode][Codigo Articulo], T3.[ItemName][Nombre Articulo],/*T2.[Quantity][Cantidad],*/ T2.[Weight1][Peso], T4.[SlpName][Vendedor],

       month(t2.docdate)[Mes],

       case when day(t2.docdate)= '01' Then T2.[Quantity] else '0'end [01], case when day(t2.docdate)= '02' Then T2.[Quantity] else '0'end [02],

       case when day(t2.docdate)= '03' Then T2.[Quantity] else '0'end [03], case when day(t2.docdate)= '04' Then T2.[Quantity] else '0'end [04],

       case when day(t2.docdate)= '05' Then T2.[Quantity] else '0'end [05], case when day(t2.docdate)= '06' Then T2.[Quantity] else '0'end [06],

       case when day(t2.docdate)= '07' Then T2.[Quantity] else '0'end [07], case when day(t2.docdate)= '08' Then T2.[Quantity] else '0'end [08],

       case when day(t2.docdate)= '09' Then T2.[Quantity] else '0'end [09], case when day(t2.docdate)= '10' Then T2.[Quantity] else '0'end [10],

       case when day(t2.docdate)= '11' Then T2.[Quantity] else '0'end [11], case when day(t2.docdate)= '12' Then T2.[Quantity] else '0'end [12],

       case when day(t2.docdate)= '13' Then T2.[Quantity] else '0'end [13], case when day(t2.docdate)= '14' Then T2.[Quantity] else '0'end [14],

       case when day(t2.docdate)= '15' Then T2.[Quantity] else '0'end [15], case when day(t2.docdate)= '16' Then T2.[Quantity] else '0'end [16],

       case when day(t2.docdate)= '17' Then T2.[Quantity] else '0'end [17], case when day(t2.docdate)= '18' Then T2.[Quantity] else '0'end [18],

       case when day(t2.docdate)= '19' Then T2.[Quantity] else '0'end [19], case when day(t2.docdate)= '20' Then T2.[Quantity] else '0'end [20],

       case when day(t2.docdate)= '21' Then T2.[Quantity] else '0'end [21], case when day(t2.docdate)= '22' Then T2.[Quantity] else '0'end [22],

       case when day(t2.docdate)= '23' Then T2.[Quantity] else '0'end [23], case when day(t2.docdate)= '24' Then T2.[Quantity] else '0'end [24],

       case when day(t2.docdate)= '25' Then T2.[Quantity] else '0'end [25], case when day(t2.docdate)= '26' Then T2.[Quantity] else '0'end [26],

       case when day(t2.docdate)= '27' Then T2.[Quantity] else '0'end [27], case when day(t2.docdate)= '28' Then T2.[Quantity] else '0'end [28],

       case when day(t2.docdate)= '29' Then T2.[Quantity] else '0'end [29], case when day(t2.docdate)= '30' Then T2.[Quantity] else '0'end [30],

       case when day(t2.docdate)= '31' Then T2.[Quantity] else '0'end [31]

    FROM OCRD T0  INNER JOIN

         ORDR T1 ON T0.CardCode = T1.CardCode INNER JOIN

         RDR1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN

         OITM T3 ON T2.ItemCode = T3.ItemCode INNER JOIN

         OSLP T4 ON T0.SlpCode = T4.SlpCode

WHERE T2.[DocDate] >='20150504' and  T2.[DocDate] <='20150509'

order by t2.docdate

En espero de su valiosa ayuda

Muy agradecido de antemano

Saludos

[Former Member · Accepted solution]

Prueba Así :

SELECT T2.[DocDate][Fecha], T3.[ItemCode][Codigo Articulo], T3.[ItemName][Nombre Articulo],/*T2.[Quantity][Cantidad],*/ T2.[Weight1][Peso], T4.[SlpName][Vendedor],

       month(t2.docdate)[Mes],

       case when day(t2.docdate)= '01' Then T2.[Quantity] else '0'end [01], case when day(t2.docdate)= '02' Then T2.[Quantity] else '0'end [02],

       case when day(t2.docdate)= '03' Then T2.[Quantity] else '0'end [03], case when day(t2.docdate)= '04' Then T2.[Quantity] else '0'end [04],

       case when day(t2.docdate)= '05' Then T2.[Quantity] else '0'end [05], case when day(t2.docdate)= '06' Then T2.[Quantity] else '0'end [06],

       case when day(t2.docdate)= '07' Then T2.[Quantity] else '0'end [07], case when day(t2.docdate)= '08' Then T2.[Quantity] else '0'end [08],

       case when day(t2.docdate)= '09' Then T2.[Quantity] else '0'end [09], case when day(t2.docdate)= '10' Then T2.[Quantity] else '0'end [10],

       case when day(t2.docdate)= '11' Then T2.[Quantity] else '0'end [11], case when day(t2.docdate)= '12' Then T2.[Quantity] else '0'end [12],

       case when day(t2.docdate)= '13' Then T2.[Quantity] else '0'end [13], case when day(t2.docdate)= '14' Then T2.[Quantity] else '0'end [14],

       case when day(t2.docdate)= '15' Then T2.[Quantity] else '0'end [15], case when day(t2.docdate)= '16' Then T2.[Quantity] else '0'end [16],

       case when day(t2.docdate)= '17' Then T2.[Quantity] else '0'end [17], case when day(t2.docdate)= '18' Then T2.[Quantity] else '0'end [18],

       case when day(t2.docdate)= '19' Then T2.[Quantity] else '0'end [19], case when day(t2.docdate)= '20' Then T2.[Quantity] else '0'end [20],

       case when day(t2.docdate)= '21' Then T2.[Quantity] else '0'end [21], case when day(t2.docdate)= '22' Then T2.[Quantity] else '0'end [22],

       case when day(t2.docdate)= '23' Then T2.[Quantity] else '0'end [23], case when day(t2.docdate)= '24' Then T2.[Quantity] else '0'end [24],

       case when day(t2.docdate)= '25' Then T2.[Quantity] else '0'end [25], case when day(t2.docdate)= '26' Then T2.[Quantity] else '0'end [26],

       case when day(t2.docdate)= '27' Then T2.[Quantity] else '0'end [27], case when day(t2.docdate)= '28' Then T2.[Quantity] else '0'end [28],

       case when day(t2.docdate)= '29' Then T2.[Quantity] else '0'end [29], case when day(t2.docdate)= '30' Then T2.[Quantity] else '0'end [30],

       case when day(t2.docdate)= '31' Then T2.[Quantity] else '0'end [31]

    FROM OCRD T0  INNER JOIN

         ORDR T1 ON T0.CardCode = T1.CardCode INNER JOIN

         RDR1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN

         OITM T3 ON T2.ItemCode = T3.ItemCode INNER JOIN

         OSLP T4 ON T0.SlpCode = T4.SlpCode

WHERE T2.[DocDate] >='[%1]' and  T2.[DocDate] <='[%2]'

order by t2.docdate

[Former Member · Accepted solution]

Gracias Alberto pero si selecciona 04 de mayo al 10 de mayo me sigue mostrando todos los dias y necesitaria que me mostrara solo los dias del 04 al 10.

En espera que me puedan ayuda

Saludos Cordiales

[felipe_loyolarodriguez · Accepted solution]

Buenas tardes

Favor probar el siguiente query

declare @f1 datetime,@f11 datetime
declare @f2 datetime,@f22 datetime

declare @t1 table
    (
        [fecha] datetime
    )
set @f1=(/*select top 1 A.TransId from JDT1 A where A.RefDate>=*/'[%0]')
set @f11=@f1
set @f2=(/*select top 1 A.TransId from JDT1 A where A.RefDate<=*/'[%1]')
set @f22=@f2

while @f1<=@f2
begin
    insert into @t1
    values (@f1)
    set @f1 = dateadd(dd,1,@f1)
end

declare @pvt_table nvarchar(max)
select @pvt_table = coalesce(@pvt_table + ',[' + convert(nvarchar(2),day(fecha)) + ']', '[' + convert(nvarchar(2),day(fecha)) + ']')
from @t1

declare @Pvt nvarchar(max)
SET @Pvt =
N'
    SELECT *
    FROM (
        SELECT 
            T3.[ItemCode][Codigo Articulo], 
            T3.[ItemName][Nombre Articulo],
            T2.[Quantity][Cantidad],
            T4.[SlpName][Vendedor],
            month(t2.docdate)[Mes],
            convert(nvarchar(2),day(T2.DocDate))[Dia]

        FROM OCRD T0  
        INNER JOIN ORDR T1 ON T0.CardCode = T1.CardCode 
        INNER JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry 
        INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode 
        INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode
        WHERE T2.[DocDate] >='''+convert(nvarchar(8),@f11,112)+''' and  T2.[DocDate] <='''+convert(nvarchar(8),@f22,112)+'''
    ) AS A

    PIVOT (
        SUM(Cantidad)
        FOR Dia IN ('+ @pvt_table +')
    ) AS Pvt
'

--EXEC sp_executesql @pvt, N'@pvt_table nvarchar(max), @f11 datetime, @f22 datetime',@pvt_table,@f1,@f2
exec(@pvt)
--print(@pvt)

Saludos

FLR

[Former Member · Accepted solution]

Buenas tardes Felipe gracias por tu valiosa ayuda esta perfecto solo le voy adicionar otros datos pero asi coo esta esta perfecto muy agradecido.

Agradeciendo tu ayuda.

Saludos Cordiales