Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Crosstab column date grouping Post Reply Post New Topic
<< Prev Page  of 4
Author Message
Craigbob
Newbie
Newbie


Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
Quote Craigbob Replybullet Posted: 30 Aug 2012 at 10:42am
Originally posted by hilfy



Please post the exact join and where clauses from your query so that we can see what's going on.
 
-Dell


SELECT
dbo.CST_MS_Quote.MSCo,
dbo.CST_MS_Quote.JCCo,
dbo.CST_MS_Quote.Quote,
dbo.CST_MS_Quote.MSHQStatus,
dbo.CST_MS_Quote.QuoteType,
dbo.CST_MS_Quote.Job,
dbo.CST_MS_Quote.Status,
dbo.CST_MS_Quote.Description,
dbo.CST_MS_Quote.QuotedBy,
dbo.RJDateLookup.MonthNumber,
dbo.RJDateLookup.MonthName,
dbo.RJDateLookup.DateFull,
dbo.CST_MS_Quote.QuoteDate,
dbo.CST_MS_Quote.ReqDate,
dbo.CST_MS_Quote.ExpDate,
dbo.CST_MS_Quote.FromLoc,
dbo.CST_MS_Quote.Location2 AS Location,
dbo.CST_MS_Quote.MaterialCode,
dbo.CST_MS_Quote.MaterialDescription,
dbo.CST_MS_Quote.UM,
dbo.CST_MS_Quote.QuoteUnits

FROM dbo.RJDateLookup
Left JOIN dbo.CST_MS_Quote
ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate
and dbo.CST_MS_Quote.QuoteType = 'C'
and dbo.RJDateLookup.MonthNumber <= month(QuoteDate) + 6
WHERE
dbo.RJDateLookup.DateFull >= '08/01/2012'
AND
Quote = 'AC1586'

All I did was add the quote clause so I could see if it works in one instance.

All it did was return the one record and no dates from the datelookup past August.

I was expecting it to return the quote record and the next 6 months from the datelookup table for a total of 7 records.


IP IP Logged
Craigbob
Newbie
Newbie


Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
Quote Craigbob Replybullet Posted: 30 Aug 2012 at 11:40am
Okay some more fuel for the fire. When I try and select dates from the datelookup table alone and filter between 8/1/2012 and 3/1/2013, it returns only the data for August (31 records).

SELECT    
*
FROM       
RJDateLookup
WHERE     
DateFull >= '08/01/2012 00:00:00' AND DateFull < '03/01/2013 00:00:00'

I've also tried DateFull Between '08/01/2012' and '03/01/2013' with the same results.

I did a select * on the table and it came back with all the dates.

What am I missing? Am I insane?
IP IP Logged
Craigbob
Newbie
Newbie


Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
Quote Craigbob Replybullet Posted: 30 Aug 2012 at 12:24pm
Well it looks like the Datelookup table is missing all dates for Sept. Oct. Nov, Dec, Jan and Feb for each year. Makes no sense. Here is the SQL used to populate the table.

DECLARE @Date DATETIME
SET @Date = '1/1/1900'     

WHILE @Date < '1/1/2100'
BEGIN
     INSERT INTO RJDateLookup
     (
        DateKey, DateFull, FullYear,
        QuarterNumber, WeekNumber, WeekDayName,
        MonthDay, MonthName, YearDay,
        DateDefinition,
               CharacterDate,
               WeekDay,
               MonthNumber
     )
     SELECT
        CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
        DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
        DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
               DATENAME(mm, @Date) + ' ' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',   
           ' + CAST(DATEPART(yy, @Date) AS CHAR(4)),
           CONVERT(VARCHAR(10), @Date, 101),
           DATEPART(dw, @Date),
           DATEPART(mm, @Date)
    
     SET @Date = DATEADD(dd, 1, @Date)
END
IP IP Logged
Craigbob
Newbie
Newbie


Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
Quote Craigbob Replybullet Posted: 30 Aug 2012 at 4:23pm
Okay rebuilt and repopulated the date table and all the dates are in there now. But still no luck in getting all dates to show when linked to the quotes table. GRRRRR.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 31 Aug 2012 at 3:15am
Add the quote number to the join instead of the where.
 
-Dell
IP IP Logged
Craigbob
Newbie
Newbie


Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
Quote Craigbob Replybullet Posted: 24 Sep 2012 at 8:58am
Originally posted by hilfy



Add the quote number to the join instead of the where.
 
-Dell


Thanks. just getting back to this now that I've finished the higher priority report that came in.

Here is the code as it it now stands. I've taken out some unnecessary fields and fixed the date filter.

SELECT distinct

dbo.CST_MS_Quote.MSCo,
dbo.CST_MS_Quote.QuotedBy,
dbo.RJDateLookup.MonthNumber,
dbo.RJDateLookup.MonthName,
--dbo.RJDateLookup.DateFull,
Month(dbo.CST_MS_Quote.QuoteDate) QuoteDate,
Month(dbo.CST_MS_Quote.ReqDate) ReqDate,
dbo.CST_MS_Quote.Location2 AS Location,
dbo.CST_MS_Quote.MaterialCode,
dbo.CST_MS_Quote.MaterialDescription,
isnull(sum(dbo.CST_MS_Quote.QuoteUnits),0) QuoteUnits


FROM dbo.RJDateLookup
Left JOIN dbo.CST_MS_Quote
ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate
and dbo.CST_MS_Quote.QuoteType = 'C'
and dbo.RJDateLookup.DateFull <= Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))
--AND
--Quote = 'AC1586'
WHERE
dbo.RJDateLookup.DateFull >= '09/01/2012'

Group by
dbo.CST_MS_Quote.MSCo,
dbo.CST_MS_Quote.QuotedBy,
dbo.RJDateLookup.MonthNumber,
dbo.RJDateLookup.MonthName,
--dbo.RJDateLookup.DateFull,
month(dbo.CST_MS_Quote.QuoteDate),
month(dbo.CST_MS_Quote.ReqDate),
dbo.CST_MS_Quote.Location2,
dbo.CST_MS_Quote.MaterialCode,
dbo.CST_MS_Quote.MaterialDescription

order by QuotedBy,Location,MaterialCode


However I'm not sure how to get the extra months as required dates.

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 01 Oct 2012 at 3:20am
You have two mutually exclusive conditions in your join:
 
dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate 
and

 dbo.RJDateLookup.DateFull <= Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))
both work on the same two fields.  So, you need to decide which one you need.
 
Also, based on whether a join is a left or a right join, they're sensitive to the order of fields in the condition.  Whichever of these two conditions you decide to use, I would change them to this:
 
dbo.CST_MS_Quote.QuoteDate = dbo.RJDateLookup.DateFull

or

Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))>= dbo.RJDateLookup.DateFull
-Dell
IP IP Logged
Craigbob
Newbie
Newbie


Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
Quote Craigbob Replybullet Posted: 03 Oct 2012 at 8:37am
Originally posted by hilfy



You have two mutually exclusive conditions in your join:
 
dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate 
and
 dbo.RJDateLookup.DateFull <= Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))
both work on the same two fields.  So, you need to decide which one you need.
 
Also, based on whether a join is a left or a right join, they're sensitive to the order of fields in the condition.  Whichever of these two conditions you decide to use, I would change them to this:
 
dbo.CST_MS_Quote.QuoteDate = dbo.RJDateLookup.DateFull
or
Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))>= dbo.RJDateLookup.DateFull
-Dell


Actually they are not mutually exclusive. one sets the start date and the other sets the end date.

But a new wrinkle has been thrown into the mix last night. They want to be able to group all records that are earlier than the quote date have a status of Active = 'Y' and expired date < current date in the 1st column.

I think I know how to do that using Nested case statements. When I get back to my desk later I'll post the SQL and see whaty happens.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 03 Oct 2012 at 9:21am
But this
 
dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate
 
means you'll only get records where they're exactly equal - regardless of the end dat you've set.
 
-Dell
IP IP Logged
<< Prev Page  of 4
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.