Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anandh_ramesh
Starting Member
3 Posts |
 Posted - 2005-03-01 : 09:25:47
|
| hi all,can anyone suggest me an equivalent sql server query for this query?***********************SELECT [severity] AS Sev,sum(IIf(([status]='open' And [recd_date]<#01/03/2004#) Or ([status]='closed' And [recd_date]<#01/03/2004# And [act_end] Between #01/03/2004# And #01/03/2005#),1,0)) AS FromPreviousMonths, sum(IIf([recd_date] Between #01/03/2004# And #01/03/2005#,1,0)) AS RecdThisMonth, sum(IIf([status]='closed' And [act_end] Between #01/03/2004# And #01/03/2005#,1,0)) AS CompletedThisMonth, sum(IIf([status]='open' And [exp_end] Between #01/03/2004# And #01/03/2005#,1,0)) AS Delayed, sum(IIf([status]='open',1,0)) AS WorkInProgress FROM Maintable WHERE system='POSFE'GROUP BY [severity] ORDER BY [severity];************************any help is appreciated...cheers,Anandh |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-01 : 09:37:12
|
iif construct in sql goes:sum(case when someCondition then 1 else 0 end)so for you it would be:selectsum(case when ([status]='open' And [recd_date]<'01/03/2004') Or ([status]='closed' And [recd_date]<'01/03/2004' And [act_end] Between '01/03/2004' And '01/03/2005') then 1 else 0 end AS FromPreviousMonths, ...Go with the flow & have fun! Else fight the flow  |
 |
|
|
anandh_ramesh
Starting Member
3 Posts |
Posted - 2005-03-01 : 10:48:21
|
| hi,thanks for that...there is a similar conversion problem with this query also... can you try this?****************String qry2="TRANSFORM Sum(TIMESHEET.EFFORT) AS SumOfEFFORT SELECT timesheet.prjsystem, emp_master.EMP_NAME, Sum(TIMESHEET.EFFORT) AS [Total Of EFFORT] FROM TIMESHEET INNER JOIN emp_master ON TIMESHEET.emp_id = emp_master.emp_id WHERE (((TIMESHEET.emp_id)=[emp_master].[emp_id]) AND ((TIMESHEET.WORKDAY) Between #"+start+"# And #"+end+"#)) GROUP BY timesheet.prjsystem, emp_master.EMP_NAME PIVOT Left(TIMESHEET.REQUEST_ID,2);";*************************************cheers,Anandh |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
|
|
equivalent code