Re: 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" <Geoff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DC89754E-0A4C-4D78-B5C2-75F547C62D67@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:253BF539-8139-4E63-B824-73440A6302E9@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: Cross tab based on DateDIFF
    ... someting similiar to this view below but replacing the Count(CASE ACTCAT WHEN ... AS DATEDIFF>=0 and another column show the count if DATEDIFF(DD, ... SUM (CASE ... get the Sql Statement to work in an MS Sql 2000 View ...
    (microsoft.public.sqlserver.mseq)
  • Re: How do I calculate times over multiple records?
    ... I got the code to run but not with the SUM in place. ... doing a sum on the DateDiff Calculation. ... query) and therefore runs once for each row returned by the outer query. ... Excluding Weekends and Holidays is more complex. ...
    (microsoft.public.access.queries)
  • Re: How do I calculate times over multiple records?
    ... The sum statement was in the wrong place. ... doing a sum on the DateDiff Calculation. ... query) and therefore runs once for each row returned by the outer query. ... Excluding Weekends and Holidays is more complex. ...
    (microsoft.public.access.queries)
  • Re: Time over 24 hours
    ... you are missing a parenthesis somewhere. ... Overtime: ...
    (comp.databases.ms-access)