Max date in case statement pulling 2007 instead of 2011

[SyntaxTerror]

ntaxTerror (TechnicalUser) Jan 6, 2011
I have a CASE statement where I'm using MAX in my WHEN and it's failing the conditions of the WHEN because the date doesn't fall within the specified range, when it should.

Looking at the date output, it pulls a date of 2007-01-09 when it should pull something like 2011-01-03. Why isn't it pulling the max date?:

SELECT CASE WHEN max(table.CreatedDate) between '2010-12-28' AND '2011-01-06' THEN blahblah ELSE 'You fail.'

It keeps outputting 'You Fail' because it's pulling the 2007 date. What do I need to do to get it to recognize the actual max date?

Thanks!

 

[markros]

You need to either get max date in the subquery (derived table / cte) or switch the order, e.g.

MAX(case when Table.CreatedDate between ... then ... else .. end) as ...

PluralSight Learning Library

 

[SyntaxTerror]

Hm... I tried your example, but no luck. Could it have to do with the data type? That field uses a smalldatetime data type...

Basically I'd have multiple case statements. ...one for one week back, where I need to show the max CreatedDate within that range, then another week back, where I need the max CreatedDate of that range, and so on... So, I don't just need to pull one maxdate from a subquery, but multiple maxdates for multiple date ranges.

...I'm wondering if I just need to have tons of subqueries, one for each date range. Can you think of a better way to do it?

 

[markros]

Can you please post DDLs of the table(s) involved, INSERT statements and the desired output? This way I will be able to help you more easily than trying to do it based on the narrative.

PluralSight Learning Library

 

[SyntaxTerror]

I had to google DDL... I don't use CREATE, ALTER, DROP, or INSERT in my statement, but maybe I'm misunderstanding you. Please clarify if so (I'm obviously relatively new to SQL and not a DBA or anything...) I use Microsoft SQL Server 2005.

This is for a report only, so it's just a complex query. I need it to pull the maximum date of a field within a 1-week range for 4 different weeks.

The results would be along the lines of:

WEEK 1 | WEEK 2 | WEEK 3 | WEEK 4 |
12/11/10|12/18/10|12/24/10|01/01/11|

The dates might vary depending on the data for that week, which is fine, I'm just not sure of the best way to write this. Perhaps there's a better way than using CASE.

 

[gmmastros]

Basically, Markros is trying to get your table structure and some sample data. For the table structure... in SQL Server Management Studio, right click on the table -> Script table as -> Create To -> new query window. Post that here. Then, post some sample data that shows the problem. Post the entire query and what output you want.

I know this sounds like a lot of work, but it's a sure fire guarantee that you'll get the best advice possible.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

In SQL Server 2005 and up you may want to take a look at the PIVOT operator.

Quick untested idea:

;with cte as (select CreatedDate, OtherField, 
datepart(week, CreatedDate) as WeekNum from your table)

select * from cte PIVOT (max(CreatedDate) FOR WeekNum in ([1],[2],[3],[4])) pvt

PluralSight Learning Library

 

[SyntaxTerror]

Hm... Well, here's the stuff gmmatros mentioned:

USE [APPRAI]
GO
/****** Object:  Table [dbo].[xInvtCountHdr]    Script Date: 01/06/2011 10:32:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xInvtCountHdr](
	[Approved] [char](1) NOT NULL,
	[CountDate] [smalldatetime] NOT NULL,
	[CountID] [char](16) NOT NULL,
	[CountStatus] [char](1) NOT NULL,
	[Crtd_DateTime] [smalldatetime] NOT NULL CONSTRAINT [DF_xInvtCountHdr_Crtd_DateTime]  DEFAULT (getdate()),
	[Crtd_Prog] [char](10) NOT NULL CONSTRAINT [DF_xInvtCountHdr_Crtd_Prog]  DEFAULT (''),
	[Crtd_User] [char](10) NOT NULL CONSTRAINT [DF_xInvtCountHdr_Crtd_User]  DEFAULT (''),
	[Lupd_DateTime] [smalldatetime] NOT NULL CONSTRAINT [DF_xInvtCountHdr_Lupd_DateTime]  DEFAULT (getdate()),
	[Lupd_Prog] [char](10) NOT NULL CONSTRAINT [DF_xInvtCountHdr_Lupd_Prog]  DEFAULT (''),
	[Lupd_User] [char](10) NOT NULL CONSTRAINT [DF_xInvtCountHdr_Lupd_User]  DEFAULT (''),
	[SiteID] [char](10) NOT NULL,
	[User1] [char](30) NOT NULL CONSTRAINT [DF_xInvtCountHdr_User1]  DEFAULT (''),
	[User2] [char](30) NOT NULL CONSTRAINT [DF_xInvtCountHdr_User2]  DEFAULT (''),
	[User3] [float] NOT NULL CONSTRAINT [DF_xInvtCountHdr_User3]  DEFAULT (0),
	[User4] [float] NOT NULL CONSTRAINT [DF_xInvtCountHdr_User4]  DEFAULT (0),
	[User5] [char](10) NOT NULL CONSTRAINT [DF_xInvtCountHdr_User5]  DEFAULT (''),
	[User6] [char](10) NOT NULL CONSTRAINT [DF_xInvtCountHdr_User6]  DEFAULT (''),
	[User7] [smalldatetime] NOT NULL CONSTRAINT [DF_xInvtCountHdr_User7]  DEFAULT (''),
	[User8] [smalldatetime] NOT NULL CONSTRAINT [DF_xInvtCountHdr_User8]  DEFAULT (''),
	[tstamp] [timestamp] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

and...

 

[SyntaxTerror]

...
It wouldn't allow me to post all of my starter code -_-'

Here is the ouput:

http://img152.imageshack.us/img152/5903/outputz.png

Here's a snippet of my code:

SELECT  
	CASE WHEN max(HDR.Crtd_DateTime) Between DATEADD(D,-7, @CurrDate) and DATEADD(D, 1, @CurrDate) THEN tr.countid ELSE 'You fail at SQL' END AS 'Week4', HDR.siteid, TR.invtid, HDR.crtd_datetime, @currdate as Today
FROM xinvtcounthdr as HDR 
	inner join xinvtcounttran as TR on HDR.countid=TR.countid 

WHERE HDR.siteid=@Clinic AND TR.invtid='10476'
GROUP BY  HDR.siteid, tr.countid, TR.invtid, HDR.crtd_datetime

 

[markros]

Try

SELECT     SUM(CASE WHEN HDR.Crtd_DateTime) Between DATEADD(D,-7, @CurrDate) and DATEADD(D, 1, @CurrDate) THEN tr.countid ELSE 0 END) AS 'Week4', 
HDR.siteid, TR.invtid, max(HDR.crtd_datetime) as Last_Date, @currdate as Today
FROM xinvtcounthdr as HDR     inner join xinvtcounttran as TR on HDR.countid=TR.countid WHERE HDR.siteid=@Clinic AND TR.invtid='10476'GROUP BY  HDR.siteid, tr.countid, TR.invtid, HDR.crtd_datetime

group by HDR.SiteID, TR.InvtID

PluralSight Learning Library

 

[SyntaxTerror]

An expression of non-boolean type specified in a context where a condition is expected, near ')'."

Perhaps an extra paren or a missing paren? I'm not sure what your thoughts are regarding the code so I thought I'd ask you. Also, there are two group bys at the end. Not sure which one you wanted to use.

THANK you for helping

 

[markros]

Remove your original GROUP BY. I'm using IE now and unforunately it messes up code when I copy it. Also, instead of SUM change to Max in your first CASE expression. I thought CountID is some count, but I've checked your table definition and see, it's a character.

PluralSight Learning Library

 

[SyntaxTerror]

Thanks. I think there's one last thing - it's still complaining about he parenthesis I think it's missing an open parenthesis somewhere in the SELECT, but I'm not sure where you intended it to be. The max statement is contained, the dateadds, are contained, but there's no open parenthesis to match up with the one after END.

 

[markros]

There is a typo here

SUM(CASE WHEN HDR.Crtd_DateTime)

should be

MAX(case when ....
)

in other words, the ) here should not be.

PluralSight Learning Library

 

[SyntaxTerror]

Okay, it ran. Tragically, the "Last_Date" is pulling 2007-01-09, and thus, failing the case's conditions.

I just wrote a quick select and the most current Crtd_DateTime is 2011-01-03. Oh MAX, why do you do this?

 

[markros]

This is Max date per each SiteID and InvtID. Are you checking each of them?

PluralSight Learning Library

 

[SyntaxTerror]

Thanks guys. This is embarrassing to admit, but it was that particular InvtID. It had literally not been ordered since 2007 which is HIGHLY irregular, to the point that I didn't even think it could be that... Rotten luck picking one of what has to be only a small handful of inventory IDs that hasn't been ordered in 3-4 years... Murphy's law wins again.