on 05-14-2015 9:02 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
94 | |
11 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.