Cross tab based on DateDIFF

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
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" <Geoff@.discussions.microsoft.com> wrote in message
news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
> 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
|||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" <Geoff@.discussions.microsoft.com> wrote in message
> news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
>
>
|||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" <Geoff@.discussions.microsoft.com> wrote in message
news:DC89754E-0A4C-4D78-B5C2-75F547C62D67@.microsoft.com...[vbcol=seagreen]
> 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: