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.

 All Forums
 Other Forums
 MS Access
 equivalent code

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:

select
sum(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
Go to Top of Page

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
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-01 : 11:17:46
sql server doesn't have Transfrom and pivot options/functions.
use this:
http://www.sqlteam.com/item.asp?ItemID=2955

Go with the flow & have fun! Else fight the flow
Go to Top of Page
   

- Advertisement -