Cross tab based on DateDIFF

How do I put in the condition of wether the DateDiff is >= 0. I was trying
someting similiar to this view below but replacing the Count(CASE ACTCAT WHEN
N'X4' Then ACTCAT END with something to make one column count the number of
records where DATEDIFF(DD, dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate)
AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view




SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
FROM dbo.ContActivTbl INNER JOIN
dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
dbo.JobLogTbl.JobNo
WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
(dbo.ContActivTbl.ActCat = 'AG') AND
(dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)

I can do this in MS Access with

TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
[Total Shipments], JobLogTbl.DLocNo
FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo = JobLogTbl.JobNo
WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
((JobLogTbl.ShipStatus)<>"Cancelled"))
GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
ORDER BY Month([JSTime])
PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before Arrival","After
Arrival");

"Russell Fields" wrote:

> Geoff,
>
> Is this what you are after?
>
> SELECT
> SUM (CASE
> WHEN Col1 >= 0 THEN 1
> ELSE 0
> END) AS NonNegative,
> SUM (CASE
> WHEN Col1 >= 0 THEN 0
> ELSE 1
> END) AS Negative
> FROM YourTable
>
> --RLF
>
> "Geoff" wrote in message
>
> > I'm trying trying to build a crosstab query using a Case statement to
> > count
> > the number of records in one column with the DateDiff >=0 and the other
> > column to be a count of the Records with the DateDiff <0. I can do this
> > easlily in MS Access with the IIF Function in a crosstab query but I can't
> > get the Sql Statement to work in an MS Sql 2000 View
>
>
>
 >> Stay informed about: Cross tab based on DateDIFF 

Geoff,

If I am tracking you correctly then you would want something like:

SELECT
DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE())>= 0 THEN 1
ELSE 0
END) AS NonNegative,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE()) >= 0 THEN 0
ELSE 1
END) AS Negative
FROM YourTable
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime),
MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)

I have not put all columns in, but I hope that this helps.

RLF


"Geoff" wrote in message

> How do I put in the condition of wether the DateDiff is >= 0. I was trying
> someting similiar to this view below but replacing the Count(CASE ACTCAT
> WHEN
> N'X4' Then ACTCAT END with something to make one column count the number
> of
> records where DATEDIFF(DD, dbo.JobLogTbl.JSTime,
> dbo.ContActivTbl.AddDate)
> AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
> dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view
>
>
>
>
> SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
> MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
> COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
> COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
> FROM dbo.ContActivTbl INNER JOIN
> dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
> dbo.JobLogTbl.JobNo
> WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
> (dbo.ContActivTbl.ActCat = 'AG') AND
> (dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
> GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
> ORDER BY MONTH(dbo.JobLogTbl.JSTime)
>
> I can do this in MS Access with
>
> TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
> SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
> [Total Shipments], JobLogTbl.DLocNo
> FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo =
> JobLogTbl.JobNo
> WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
> AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
> ((JobLogTbl.ShipStatus)<>"Cancelled"))
> GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
> JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
> ORDER BY Month([JSTime])
> PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before
> Arrival","After
> Arrival");
>
> "Russell Fields" wrote:
>
>> Geoff,
>>
>> Is this what you are after?
>>
>> SELECT
>> SUM (CASE
>> WHEN Col1 >= 0 THEN 1
>> ELSE 0
>> END) AS NonNegative,
>> SUM (CASE
>> WHEN Col1 >= 0 THEN 0
>> ELSE 1
>> END) AS Negative
>> FROM YourTable
>>
>> --RLF
>>
>> "Geoff" wrote in message
>>
>> > I'm trying trying to build a crosstab query using a Case statement to
>> > count
>> > the number of records in one column with the DateDiff >=0 and the other
>> > column to be a count of the Records with the DateDiff <0. I can do this
>> > easlily in MS Access with the IIF Function in a crosstab query but I
>> > can't
>> > get the Sql Statement to work in an MS Sql 2000 View
>>
>>
>>
 >> Stay informed about: Cross tab based on DateDIFF