Report Date Help

I created the below report and it has worked fine all year, however it will
not pull data for Dec 2004. Can anyone tell me what may be causing this?
Thanks!
Glen
SELECT
Customers.CustomerName, DNRM_ItemMaster.ItemCategory4,
SalesDetail.ExtendedPrice,
ISNULL((SELECT
SalesDetailWH.ExtendedPrice
FROM
SalesDetail SalesDetailWH
WHERE
SalesDetailWH.UserDefined5 = 'Warehouse' AND
SalesDetailWH.RowID = SalesDetail.RowID),0) AS WHDollars,
ISNULL((SELECT
SalesDetailFD.ExtendedPrice
FROM
SalesDetail SalesDetailFD
WHERE
SalesDetailFD.UserDefined5 <> 'Warehouse' AND
SalesDetailFD.RowID = SalesDetail.RowID),0) AS FDDollars,
SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount AS Price,
SalesDetail.TradeDiscountAmount,
SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
SalesDetail.DocumentType,
SalesDetail.CustomerNumber, DNRM_ItemMaster.ItemClassDescription,
SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
DNRM_Stockholder.StockholderNumber, DNRM_Stockholder.StockholderName,
ISNULL((SELECT
1
FROM
DNRM_ItemMaster DNRM_ItemMasterOB
WHERE
(DNRM_ItemMasterOB.ClassCode='DPOS' OR
DNRM_ItemMasterOB.ClassCode='MTYKFD' OR DNRM_ItemMasterOB.ClassCode='NPOS'
OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
DNRM_ItemMasterOB.ClassCode='POS' OR
(DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYKDIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
DNRM_ItemMasterOB.ItemNumber = DNRM_ItemMaster.ItemNumber),0) AS
OverBill
FROM
(DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
INNER JOIN DNTIRWH.dbo.Customers Customers ON
Customers.CustomerNumber = SalesDetail.CustomerNumber
INNER JOIN DNTIRWH.dbo.DNRM_Stockholder DNRM_Stockholder ON
Left(DNRM_Stockholder.StockholderNumber,
Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDetail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
WHERE
-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC', 'AT', 'BA', 'BT', 'FM',
'LG', 'MT', 'OR', 'SP', 'TU') AND
-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
DNRM_ItemMaster.ItemType='Sales Inventory' AND
-- Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00' AND
Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
ORDER BY
SalesDetail.CustomerNumber,
DNRM_ItemMaster.ItemCategory4Hi Glen,
Are you using a SQL Server database as the Data Source?
To isolate this issue you may want to run the query via
Query Analyzer or OSQL against your database to see if
there is any data returned. If there is any problem at
this point, the SQL Server database may have something
wrong.
If SQL Server returns the data correctly, run the query
in Report Designer->Data tab to verify the result. You
may also want to create a new report and create a new
dataset to test the problem.
Sincerely,
William Wang
Microsoft Online Partner Support
Get Secure! - <www.microsoft.com/security>
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>From: "Glen Tosco" <glen@.delnat.com>
>Subject: Report Date Help
>Date: Thu, 6 Jan 2005 14:49:19 -0600
>Lines: 68
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:38876
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I created the below report and it has worked fine all
year, however it will
>not pull data for Dec 2004. Can anyone tell me what may
be causing this?
>Thanks!
>Glen
>SELECT
> Customers.CustomerName,
DNRM_ItemMaster.ItemCategory4,
>SalesDetail.ExtendedPrice,
> ISNULL((SELECT
> SalesDetailWH.ExtendedPrice
> FROM
> SalesDetail SalesDetailWH
> WHERE
> SalesDetailWH.UserDefined5 = 'Warehouse' AND
> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
WHDollars,
> ISNULL((SELECT
> SalesDetailFD.ExtendedPrice
> FROM
> SalesDetail SalesDetailFD
> WHERE
> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
FDDollars,
>
SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
AS Price,
>SalesDetail.TradeDiscountAmount,
> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>SalesDetail.DocumentType,
> SalesDetail.CustomerNumber,
DNRM_ItemMaster.ItemClassDescription,
> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>DNRM_Stockholder.StockholderNumber,
DNRM_Stockholder.StockholderName,
> ISNULL((SELECT
> 1
> FROM
> DNRM_ItemMaster DNRM_ItemMasterOB
> WHERE
> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
DNRM_ItemMasterOB.ClassCode='NPOS'
> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>DNRM_ItemMasterOB.ClassCode='POS' OR
> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> DNRM_ItemMasterOB.ItemNumber =DNRM_ItemMaster.ItemNumber),0) AS
>OverBill
>FROM
> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> INNER JOIN DNTIRWH.dbo.Customers Customers ON
> Customers.CustomerNumber =SalesDetail.CustomerNumber
> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
DNRM_Stockholder ON
> Left(DNRM_Stockholder.StockholderNumber,
>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>WHERE
>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
'AT', 'BA', 'BT', 'FM',
>'LG', 'MT', 'OR', 'SP', 'TU') AND
>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>-- Month(SalesDetail.GLPostingDate) <
Month(Getdate()) and
> Year(SalesDetail.GLPostingDate) >=(Year(Getdate())-1) and
> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
AND
> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>ORDER BY
> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemCategory4
>
>|||I created these User-Defined Fuctions to generate the previous month date
range.
For example your syntax would be:
SalesDetail.GLPostingDate >= dbo.fn_ISTCurrentMonth1(GETDATE())
AND
SalesDetail.GLPostingDate <= dbo.fn_ISTCurrentMonth2(GETDATE())
---
CREATE FUNCTION fn_ISTCurrentMonth1 (@.DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @.ISTCurrentMonth1 VarChar(20)
SET @.ISTCurrentMonth1 = convert(varchar,
convert(datetime,(convert(varchar, CASE WHEN MONTH(@.DATE)-1 = 0 THEN 12 ELSE
MONTH(@.DATE)-1 END) + '/' + '1' + '/' + convert(varchar, CASE WHEN
MONTH(@.DATE)-1 = 0 THEN YEAR(@.DATE)-1 ELSE YEAR(@.DATE) END)), 101), 101)
RETURN(@.ISTCurrentMonth1)
END
---
CREATE FUNCTION fn_ISTCurrentMonth2 (@.DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @.ISTCurrentMonth2 VarChar(20)
SET @.ISTCurrentMonth2 = convert(varchar, DATEADD(day, -1,
convert(datetime,(convert(varchar, MONTH(@.DATE)) + '/' + '1' + '/' +
convert(varchar, YEAR(@.DATE))), 101)), 101)
RETURN(@.ISTCurrentMonth2)
END
---
I hope these help.
I also have user-defined functions to calculate Current Year, Last Year,
Last Year Month date ranges. The years are based on a 10/1 fiscal year, but
can be modified to work for any. Let me know if these would be of any
benefit to you.
Thanks.
"William Wang[MSFT]" wrote:
> Hi Glen,
> Are you using a SQL Server database as the Data Source?
> To isolate this issue you may want to run the query via
> Query Analyzer or OSQL against your database to see if
> there is any data returned. If there is any problem at
> this point, the SQL Server database may have something
> wrong.
> If SQL Server returns the data correctly, run the query
> in Report Designer->Data tab to verify the result. You
> may also want to create a new report and create a new
> dataset to test the problem.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> Get Secure! - <www.microsoft.com/security>
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> >From: "Glen Tosco" <glen@.delnat.com>
> >Subject: Report Date Help
> >Date: Thu, 6 Jan 2005 14:49:19 -0600
> >Lines: 68
> >X-Priority: 3
> >X-MSMail-Priority: Normal
> >X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> >X-RFC2646: Format=Flowed; Original
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> >Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >NNTP-Posting-Host: mail.delnat.com 65.196.130.98
> >Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
> >Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38876
> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >
> >I created the below report and it has worked fine all
> year, however it will
> >not pull data for Dec 2004. Can anyone tell me what may
> be causing this?
> >
> >Thanks!
> >Glen
> >
> >SELECT
> > Customers.CustomerName,
> DNRM_ItemMaster.ItemCategory4,
> >SalesDetail.ExtendedPrice,
> > ISNULL((SELECT
> > SalesDetailWH.ExtendedPrice
> > FROM
> > SalesDetail SalesDetailWH
> > WHERE
> > SalesDetailWH.UserDefined5 = 'Warehouse' AND
> > SalesDetailWH.RowID = SalesDetail.RowID),0) AS
> WHDollars,
> > ISNULL((SELECT
> > SalesDetailFD.ExtendedPrice
> > FROM
> > SalesDetail SalesDetailFD
> > WHERE
> > SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> > SalesDetailFD.RowID = SalesDetail.RowID),0) AS
> FDDollars,
> >
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
> AS Price,
> >SalesDetail.TradeDiscountAmount,
> > SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
> >SalesDetail.DocumentType,
> > SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemClassDescription,
> > SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
> >DNRM_Stockholder.StockholderNumber,
> DNRM_Stockholder.StockholderName,
> > ISNULL((SELECT
> > 1
> > FROM
> > DNRM_ItemMaster DNRM_ItemMasterOB
> > WHERE
> > (DNRM_ItemMasterOB.ClassCode='DPOS' OR
> >DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='NPOS'
> > OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
> >DNRM_ItemMasterOB.ClassCode='POS' OR
> > (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
> >(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
> >
> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
> > 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> > DNRM_ItemMasterOB.ItemNumber => DNRM_ItemMaster.ItemNumber),0) AS
> >OverBill
> >FROM
> > (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
> >DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> > SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> > INNER JOIN DNTIRWH.dbo.Customers Customers ON
> > Customers.CustomerNumber => SalesDetail.CustomerNumber
> > INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
> DNRM_Stockholder ON
> > Left(DNRM_Stockholder.StockholderNumber,
> >Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
> >WHERE
> >-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
> 'AT', 'BA', 'BT', 'FM',
> >'LG', 'MT', 'OR', 'SP', 'TU') AND
> >-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> > DNRM_ItemMaster.ItemType='Sales Inventory' AND
> >-- Month(SalesDetail.GLPostingDate) <
> Month(Getdate()) and
> > Year(SalesDetail.GLPostingDate) >=> (Year(Getdate())-1) and
> > Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
> AND
> > Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
> >ORDER BY
> > SalesDetail.CustomerNumber,
> > DNRM_ItemMaster.ItemCategory4
> >
> >
> >
>|||The problem is right here in your code
Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
The previous month will not always be < current month...when the current
month is January (1), the previous month December will be (12)..
So you need to change your code to take that into account... Try testing
something like
IF you are looking for the previous month
SalesDetail.GLPostingDate between
dateadd(mm,-1,dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate),
getdate()) ) -- first day of prev month..
and
(dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate)) - 1,
getdate()) --lastday of prev month
This is just some on the fly code, with thought and testing you can make
this better I am sure...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Glen Tosco" <glen@.delnat.com> wrote in message
news:u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl...
> I created the below report and it has worked fine all year, however it
will
> not pull data for Dec 2004. Can anyone tell me what may be causing this?
> Thanks!
> Glen
> SELECT
> Customers.CustomerName, DNRM_ItemMaster.ItemCategory4,
> SalesDetail.ExtendedPrice,
> ISNULL((SELECT
> SalesDetailWH.ExtendedPrice
> FROM
> SalesDetail SalesDetailWH
> WHERE
> SalesDetailWH.UserDefined5 = 'Warehouse' AND
> SalesDetailWH.RowID = SalesDetail.RowID),0) AS WHDollars,
> ISNULL((SELECT
> SalesDetailFD.ExtendedPrice
> FROM
> SalesDetail SalesDetailFD
> WHERE
> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> SalesDetailFD.RowID = SalesDetail.RowID),0) AS FDDollars,
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount AS Price,
> SalesDetail.TradeDiscountAmount,
> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
> SalesDetail.DocumentType,
> SalesDetail.CustomerNumber, DNRM_ItemMaster.ItemClassDescription,
> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
> DNRM_Stockholder.StockholderNumber, DNRM_Stockholder.StockholderName,
> ISNULL((SELECT
> 1
> FROM
> DNRM_ItemMaster DNRM_ItemMasterOB
> WHERE
> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
> DNRM_ItemMasterOB.ClassCode='MTYKFD' OR DNRM_ItemMasterOB.ClassCode='NPOS'
> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='POS' OR
> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
> (DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYKDIMESN','PSYKESTEEM
','PSYKOVATON','PSYKPR3000',
> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> DNRM_ItemMasterOB.ItemNumber = DNRM_ItemMaster.ItemNumber),0) AS
> OverBill
> FROM
> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
> DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> INNER JOIN DNTIRWH.dbo.Customers Customers ON
> Customers.CustomerNumber = SalesDetail.CustomerNumber
> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder DNRM_Stockholder ON
> Left(DNRM_Stockholder.StockholderNumber,
>
Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDetail.CustomerNumber,L
en(SalesDetail.CustomerNumber)-2)
> WHERE
> -- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC', 'AT', 'BA', 'BT', 'FM',
> 'LG', 'MT', 'OR', 'SP', 'TU') AND
> -- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> DNRM_ItemMaster.ItemType='Sales Inventory' AND
> -- Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
> Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00' AND
> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
> ORDER BY
> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemCategory4
>|||If works fine for all months except December...I am sure it is in the coding
but I cannot find a way to get it to work!
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:Bh4U2CG9EHA.764@.cpmsftngxa10.phx.gbl...
> Hi Glen,
> Are you using a SQL Server database as the Data Source?
> To isolate this issue you may want to run the query via
> Query Analyzer or OSQL against your database to see if
> there is any data returned. If there is any problem at
> this point, the SQL Server database may have something
> wrong.
> If SQL Server returns the data correctly, run the query
> in Report Designer->Data tab to verify the result. You
> may also want to create a new report and create a new
> dataset to test the problem.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> Get Secure! - <www.microsoft.com/security>
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
>>From: "Glen Tosco" <glen@.delnat.com>
>>Subject: Report Date Help
>>Date: Thu, 6 Jan 2005 14:49:19 -0600
>>Lines: 68
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>>Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
>>Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38876
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>I created the below report and it has worked fine all
> year, however it will
>>not pull data for Dec 2004. Can anyone tell me what may
> be causing this?
>>Thanks!
>>Glen
>>SELECT
>> Customers.CustomerName,
> DNRM_ItemMaster.ItemCategory4,
>>SalesDetail.ExtendedPrice,
>> ISNULL((SELECT
>> SalesDetailWH.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailWH
>> WHERE
>> SalesDetailWH.UserDefined5 = 'Warehouse' AND
>> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
> WHDollars,
>> ISNULL((SELECT
>> SalesDetailFD.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailFD
>> WHERE
>> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
>> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
> FDDollars,
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
> AS Price,
>>SalesDetail.TradeDiscountAmount,
>> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>>SalesDetail.DocumentType,
>> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemClassDescription,
>> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>>DNRM_Stockholder.StockholderNumber,
> DNRM_Stockholder.StockholderName,
>> ISNULL((SELECT
>> 1
>> FROM
>> DNRM_ItemMaster DNRM_ItemMasterOB
>> WHERE
>> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='NPOS'
>> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>>DNRM_ItemMasterOB.ClassCode='POS' OR
>> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
>> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
>> DNRM_ItemMasterOB.ItemNumber => DNRM_ItemMaster.ItemNumber),0) AS
>>OverBill
>>FROM
>> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
>> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
>> INNER JOIN DNTIRWH.dbo.Customers Customers ON
>> Customers.CustomerNumber => SalesDetail.CustomerNumber
>> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
> DNRM_Stockholder ON
>> Left(DNRM_Stockholder.StockholderNumber,
>>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>>WHERE
>>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
> 'AT', 'BA', 'BT', 'FM',
>>'LG', 'MT', 'OR', 'SP', 'TU') AND
>>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
>> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>>-- Month(SalesDetail.GLPostingDate) <
> Month(Getdate()) and
>> Year(SalesDetail.GLPostingDate) >=> (Year(Getdate())-1) and
>> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
> AND
>> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>>ORDER BY
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemCategory4
>>
>|||Kind of a mini-Y2K every year, eh? Unless you switch around your SQL to use
date arithmetic, you're going to have to add some fancy stuff to solve the
wraparound logic. You're treating months and years like separate integers.
Put them together in a single date and let SQL do the work for you: you've
already been given the answer: DateAdd
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Glen Tosco" <glen@.delnat.com> wrote in message
news:OJJsmxT9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> If works fine for all months except December...I am sure it is in the
> coding but I cannot find a way to get it to work!
> "William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
> news:Bh4U2CG9EHA.764@.cpmsftngxa10.phx.gbl...
>> Hi Glen,
>> Are you using a SQL Server database as the Data Source?
>> To isolate this issue you may want to run the query via
>> Query Analyzer or OSQL against your database to see if
>> there is any data returned. If there is any problem at
>> this point, the SQL Server database may have something
>> wrong.
>> If SQL Server returns the data correctly, run the query
>> in Report Designer->Data tab to verify the result. You
>> may also want to create a new report and create a new
>> dataset to test the problem.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> Get Secure! - <www.microsoft.com/security>
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and
>> confers no rights.
>> --
>>From: "Glen Tosco" <glen@.delnat.com>
>>Subject: Report Date Help
>>Date: Thu, 6 Jan 2005 14:49:19 -0600
>>Lines: 68
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>>Path:
>> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
>> hx.gbl!TK2MSFTNGP12.phx.gbl
>>Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:38876
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>I created the below report and it has worked fine all
>> year, however it will
>>not pull data for Dec 2004. Can anyone tell me what may
>> be causing this?
>>Thanks!
>>Glen
>>SELECT
>> Customers.CustomerName,
>> DNRM_ItemMaster.ItemCategory4,
>>SalesDetail.ExtendedPrice,
>> ISNULL((SELECT
>> SalesDetailWH.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailWH
>> WHERE
>> SalesDetailWH.UserDefined5 = 'Warehouse' AND
>> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
>> WHDollars,
>> ISNULL((SELECT
>> SalesDetailFD.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailFD
>> WHERE
>> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
>> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
>> FDDollars,
>> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
>> AS Price,
>>SalesDetail.TradeDiscountAmount,
>> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>>SalesDetail.DocumentType,
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemClassDescription,
>> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>>DNRM_Stockholder.StockholderNumber,
>> DNRM_Stockholder.StockholderName,
>> ISNULL((SELECT
>> 1
>> FROM
>> DNRM_ItemMaster DNRM_ItemMasterOB
>> WHERE
>> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
>> DNRM_ItemMasterOB.ClassCode='NPOS'
>> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>>DNRM_ItemMasterOB.ClassCode='POS' OR
>> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
>> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
>> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
>> DNRM_ItemMasterOB.ItemNumber =>> DNRM_ItemMaster.ItemNumber),0) AS
>>OverBill
>>FROM
>> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
>> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
>> INNER JOIN DNTIRWH.dbo.Customers Customers ON
>> Customers.CustomerNumber =>> SalesDetail.CustomerNumber
>> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
>> DNRM_Stockholder ON
>> Left(DNRM_Stockholder.StockholderNumber,
>>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
>> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>>WHERE
>>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
>> 'AT', 'BA', 'BT', 'FM',
>>'LG', 'MT', 'OR', 'SP', 'TU') AND
>>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
>> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>>-- Month(SalesDetail.GLPostingDate) <
>> Month(Getdate()) and
>> Year(SalesDetail.GLPostingDate) >=>> (Year(Getdate())-1) and
>> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
>> AND
>> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>>ORDER BY
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemCategory4
>>
>>
>