Apr 7, 2009

Select values with monthly based and order by month with case method

SELECT CAST(DATENAME(MONTH, TimeOut)AS VARCHAR(50))+'-'+CAST(DATENAME(YEAR, TimeOut)AS VARCHAR(50))[Time],
R.[Researcher] ,
'10k' [Project],
CONVERT(VARCHAR(10),B.[Filingdate],101) [FilingDate],
'10k'[Form],
COUNT(*)[NormalFiles],
COUNT(CASE RED_FLAG WHEN 'type1' THEN 1 END)[Type1],
COUNT(CASE RED_FLAG WHEN 'type2' THEN 1 END)[Type2],
COUNT(CASE RED_FLAG WHEN 'type3' THEN 1 END)[Type3],
COUNT(CASE RED_FLAG WHEN 'type4' THEN 1 END)[Type4],
COUNT(CASE RED_FLAG WHEN 'type5' THEN 1 END)[Type5],
COUNT(CASE RED_FLAG WHEN 'type6' THEN 1 END)[Type6],
COUNT(CASE A.Status WHEN 'RecordNotFound' THEN 1 END)[NewCompanies],

COUNT(*)+
COUNT(CASE RED_FLAG WHEN 'type1' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type2' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type3' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type4' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type5' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type6' THEN 1 END)+
COUNT(CASE A.Status WHEN 'RecordNotFound' THEN 1 END)[Total]

FROM [10K2K9] A JOIN T_Allocation B
ON A.Record_ID = B.Record_ID
LEFT JOIN T_Tracking E on E.Enterprise_number = B.Enterprise_Number
join T_Accounts R on R.EmployeeID= A.Researcher
WHERE TimeOut IS NOT NULL
GROUP BY DATENAME(MONTH, TimeOut),R.[Researcher],CONVERT(VARCHAR(10),B.[Filingdate],101) ,DATENAME(YEAR, TimeOut)
ORDER BY CASE DATENAME(MONTH, TimeOut) WHEN 'January' THEN'01'
WHEN 'February'THEN'02'
WHEN 'March'THEN'03'
WHEN 'April'THEN'04'
WHEN 'May'THEN'05'
WHEN 'June'THEN'06'
WHEN 'July'THEN'07'
WHEN 'August'THEN'08'
WHEN 'September'THEN'09'
WHEN 'October'THEN'10'
WHEN 'November'THEN'11'
WHEN 'December'THEN '12'
ELSE '1' END

Finding week of the month in sqlserver 2005

DECLARE @DATE VARCHAR(50)
SET @DATE ='04/03/2009'
select cast(datename(week,@DATE) as int)- cast( datename(week,dateadd(dd,1-day(@DATE),@DATE)) as int)+1
---------------------------


SELECT DATEPART(DAY, GETDATE()-10) / 7 AS WeekofMonth