A tricky one. SQL Statement - SQL Server Forums

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

A tricky one. SQL Statement

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 10:15:42

I have several records where I want to list them showing the Name, count IssuedDate > 0 and count IssuedDate = 0.

Any help with this?

I know I can get the count where IssuedDate > 0 or IssuedDate = 0 but can I get both?

To get one I would use:
SELECT Component, Count (Component) AS [Issued]
FROM tblStructuralSchedule WHERE Issued > 0
GROUP BY Component

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 10:44:49

A solution, but maybe not the best.

I added grouping by Issued, I iterate over the recordset and if the issued date is 0 I count it as issued and > 0 I count it as not issued.

m_cComponents.DeleteAllItems();
m_cIssued.DeleteAllItems();

if(m_pDistinct->IsOpen())
m_pDistinct->Close();

CString csDistinct;
csDistinct.Format("SELECT Component AS [Mark], COUNT (*) AS [Count], Issued AS [Date] FROM tblStructuralSchedule WHERE Project = '%s' AND Component LIKE '%s%%' GROUP BY Component, Issued", m_cProject.GetCurSelString(), csPrefix);

m_pDistinct->SetQuery(csDistinct);
if(m_pDistinct->Open(m_pDocument->m_pInform->GetActiveConnection()))
{
CString csPrevious = _T("");
int nIndex = 0;
while(!m_pDistinct->IsEOF())
{
CString csComponent;
int nCount;
COleDateTime dt;

m_pDistinct->GetFieldValue(_T("Mark"), csComponent);
m_pDistinct->GetFieldValue(_T("Count"), nCount);
m_pDistinct->GetFieldValue(_T("Date"), dt);
m_pDistinct->MoveNext();

if(csPrevious != csComponent)
{
nIndex = m_cComponents.InsertItem(csComponent);
m_cComponents.SetSubItem(nIndex, 1, 0);
m_cComponents.SetSubItem(nIndex, 2, 0);

m_cIssued.InsertItem(csComponent);
m_cIssued.SetSubItem(nIndex, 1, 0);
csPrevious = csComponent;
}

if(dt == 0) // Available
{
int nSched, nAvail;
m_cComponents.GetItemValue(nIndex, 1, nSched);
m_cComponents.GetItemValue(nIndex, 2, nAvail);

nSched += nCount;
nAvail += nCount;

m_cComponents.SetSubItem(nIndex, 1, nSched);
m_cComponents.SetSubItem(nIndex, 2, nAvail);
}
else // Issued
{
int nSched, nIssued;
m_cComponents.GetItemValue(nIndex, 1, nSched);
m_cIssued.GetItemValue(nIndex, 1, nIssued);

nSched += nCount;
nIssued += nCount;

m_cComponents.SetSubItem(nIndex, 1, nSched);
m_cIssued.SetSubItem(nIndex, 1, nIssued);
}
}
m_pDistinct->MoveFirst();
}
}

Mike B

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-22 : 10:52:05

Yikes! no need for a cursor !

SELECT Component,
SUM(CASE WHEN Issued > 0 THEN 1 ELSE 0 END) as GreaterThanZero,
SUM(CASE WHEN Issued <= 0 THEN 1 ELSE 0 END) as LessThanEqualZero
FROM tblStructuralSchedule
GROUP BY Component

- Jeff

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 14:09:53

Thanks for the prompt reply. I have entered the following into the query designer and got syntax errors. I am using C++, ADO, and Access97.

SELECT [Component], SUM(CASE WHEN Issued > 0 THEN 1 ELSE 0 END) as [Greater], SUM(CASE WHEN Issued <= 0 THEN 1 ELSE 0 END)
as [Less] FROM tblStructuralSchedule
WHERE Project = '10002'
AND Component LIKE 'BC*'
AND PourNumber = 0
GROUP BY [Component]

Syntax errors:
Syntax Error (missing operator) in query expression 'SUM (CASE WHEN Issued > 0 THEN 1 ELSE 0 END)'.

Any ideas?
Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 14:12:43

Ignore, jumped the gun..... The following SQL does not work correctly. It seems to list all the records.

Mike B

This seems to work however.

SELECT [Component],
COUNT (Issued > 0) as [Greater],
COUNT(Issued <= 0) as [Less]
FROM tblStructuralSchedule
WHERE Project = '10002'
AND Component LIKE 'BC*'
AND PourNumber = 0
GROUP BY [Component]

Any problems with this?

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 14:45:44

Another attempt?

This seems to work!

SELECT Component,
(SELECT Count(Component)
FROM tblStructuralSchedule
WHERE Issued > 0
AND Project = '10002'
AND Component LIKE 'BC*') AS GreatherThen,
(SELECT Count(Component)
FROM tblStructuralSchedule
WHERE Issued <= 0
AND Project = '10002'
AND Component LIKE 'BC*') AS LessThen
FROM tblStructuralSchedule
WHERE Project = '10002'
AND Component LIKE 'BC*'
GROUP BY Component

Messy and probably time costly ?

There must be problems with this right?

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 15:23:00

I have it, what about the "iif" statement?

SELECT Component,SUM (iif(Issued > 0, 1, 0)) As GreaterThen,SUM (iif(Issued <= 0, 1, 0)) AS LessThenFROM tblStructuralScheduleWHERE Project = '%s'AND Component LIKE '%s%%'GROUP BY Component"

Mike B

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-22 : 15:51:31

yes, iif() is what you want ... sorry I thought you were using SQL Server.

- Jeff

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 15:58:21

thanks, can iif be used via C++ ADO? I have the query working in Access but in my application, GreaterThen and LessThen end up being 0 for the exact same query.

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 16:37:39

Alright, I found the problem. It works great! Thanks Jeff (jsmith8858)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources