November 4, 2008 at 3:53 am
Heres the code I'm after the following
Date |No_Received |Open
--------------------------------------------------
Oct-2008 |0 |1
Sep-2008 |1 |0
Aug-2008 |0 |0
Jul-2008 |1 |0
May-2008 |1 |0
Although I don't mind if the zeros are nulls. The code I have written is as follows;
SELECTCalcMonths.Date,
RecievedRecords.No_Recieved,
OpenRecords.[Open]
FROM CalcMonths
(
with SixValues (
num
) as (
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6
)
select
LEFT(datename(mm,dateadd(mm,datediff(mm, 0, getdate()) - num, 0)),3) + '-' + datename(yy,dateadd(mm,datediff(mm, 0, getdate()) - num, 0)) AS Date
from
SixValues
order by
num asc;
)CalcMonths
LEFT OUTER JOIN
(
SELECTLEFT(DATENAME(MONTH, datWork_dtDate_Received),3) + '-' + DATENAME(YEAR, datWork_dtDate_Received) As Date,
COUNT(vchWork_TaskID) As No_Recieved
FROMRECOVERIES.tblRecoveriesTasks
WHERE((vchWork_Client = 5003) OR (vchWork_Client = 5001)) AND (vchWork_Type = 'R')
AND (datWork_dtDate_Received > DATEADD(m, -6, current_timestamp))
GROUP BYLEFT(DATENAME(MONTH, datWork_dtDate_Received),3) + '-' + DATENAME(YEAR, datWork_dtDate_Received)
)RecievedRecords
ON (CalcMonths.Date = RecievedRecords.Date)
LEFT OUTER JOIN
)
SELECTLEFT(DATENAME(MONTH, datWork_dtDate_Received),3) + '-' + DATENAME(YEAR, datWork_dtDate_Received) As Date,
COUNT(vchWork_TaskID) As [Open]
FROMRECOVERIES.tblRecoveriesTasks
WHERE((vchWork_Client = 5003) OR (vchWork_Client = 5001)) AND (vchWork_Type = 'R')
AND (datWork_dtDate_Received > DATEADD(m, -6, current_timestamp))
AND (vchWork_Status != 'Close') AND (datWork_dtClosed IS NULL)
GROUP BYLEFT(DATENAME(MONTH, datWork_dtDate_Received),3) + '-' + DATENAME(YEAR, datWork_dtDate_Received)
)OpenRecords
ON (CalcMonths.Date = OpenRecords.Date
November 4, 2008 at 7:48 am
The parenthesis right after the last outer join looks reversed, and the final parenthesis is missing.
Maybe you could accomplish this without the sub Qs and joins with
SELECT DISTINCT
LEFT(DATENAME(MONTH, datWork_dtDate_Received),3) + '-' +
DATENAME(YEAR, datWork_dtDate_Received),
COUNT(vchWork_TaskID) AS No_Received,
COUNT(SELECT CASE WHEN (vchWork_Status != 'Close') AND (datWork_dtClosed IS NULL)
THEN 1 ELSE 0 END) AS [Open]
FROM RECOVERIES.tblRecoveriesTasks
WHERE ((vchWork_Client = 5003) OR (vchWork_Client = 5001)) AND (vchWork_Type = 'R')
AND (datWork_dtDate_Received >= DATEADD(m, -6, current_timestamp))
GROUP BY DISTINCT
LEFT(DATENAME(MONTH, datWork_dtDate_Received),3) + '-' +
DATENAME(YEAR, datWork_dtDate_Received)
To get all of the sixth month back, you may want to change out the lone current_timestamp (above) with
SELECT DATEADD(d, 1 - 1* DATEPART(d, current_timestamp), CAST(FLOOR(CAST(current_timestamp AS FLOAT)) AS smallDATETIME))
(Run that alone in a query to see what it does.)
Consider also the ">=" in the date compare, where you had ">".
The existing code also includes the current partial month, BTW.
November 4, 2008 at 11:56 am
Argh! In my code, the
COUNT(SELECT CASE WHEN
should have read
SUM(CASE WHEN
'Sorry -- too hurried.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply