Creating a multiple select statement

[696796]

Hi,

At the moment i have 13 seperate statements selecting a count of records depending on the criteria dates. A few are shown below:-

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  9 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  10 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  11 AND impactId = 1

What i'd like to do is combine these statements into one which can be run, and name each result with an alias, such as period1, period2, period3...

I have tried to code it but am unsure:-

SELECT count(impactID), select count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  9 AND impactId = 1 AND supplierId = 1 as period2 iyear =  2005 AND iperiod =  9 AND impactId = 1 AND supplierId = 1

Many thanks,

Alex

 

[PHV]

Something like this ?
SELECT Sum(IIf(iperiod=9,1,0)) AS period1
, Sum(IIf(iperiod=10,1,0)) AS period2
, Sum(IIf(iperiod=11,1,0)) AS period3
FROM tblBaseData
WHERE iyear = 2005 AND iperiod IN (9,10,11) AND impactId = 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[696796]

Thanks, whats the 9,1,0 in Sum(IIf(iperiod=9,1,0))?

 

[PHV]

In the SQL view pane put the cursor inside the IIf word and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[696796]

Right i see now - the thing is that the iDate sometimes changes from 2005 to 2006, i also tried rning the statement but it doesn't return values for the second and third part of the query, just the first part...

 

[PHV]

Could you please post your actual SQL code, some input samples, given results and expected results ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[696796]

Sorry, it does return values, but the date issue still stands. Heres some sample data;-

BaseDataId	supplierId	originIsCustomer	dateRaised	supplierResponse	dateCARsent	dateResponseReceived	dateCARclosed	daysToClose	statusIsOpen	weigtedIncidentresult	impactId	iyear	iperiod
63	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	4	2	2005	9
64	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	4	2	2005	9
65	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	4	2	2005	9
66	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	4	2	2006	8
67	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	4	1	2005	10
68	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	1	1	2005	10
69	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	1	1	2005	10
70	33	True	08/21/2006	2	21/08/2006	21/08/2006	21/08/2006	12	No	1	2	2006	8

More sql statements may look like this:-

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  9 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  10 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  11 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2006 AND iperiod =  1 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2006 AND iperiod =  2 AND impactId = 1

 

[696796]

There will be records in the database for all periods years, so there will be from year 2004-2006 and each may have data for each period(1-13)

 

[696796]

Could i put a year in the IIf(iperiod=9,1,0) statement somewhere? IIf(iyear = 2005(iperiod=9,1,0))?

 

[PHV]

Have you tried to follow the crosstab query wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[696796]

No i haven't - i'm runing the code from vb.net so writing the statement in there. How would a crosstab help me?

 

[PHV]

Perhaps you only wanted this ?
SELECT 100*iyear+iperiod AS YearPeriod, Count(*) AS Total
FROM tblBaseData
WHERE impactId = 1
GROUP BY iyear, iperiod

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[696796]

Interesting.. looks good, it's just my date problem, let me explain in more detail. The user will pick a year and period. From that i calculate back 13 periods, so for example...

If the user chooses Year 2006, period 8 - ihave the following years and dates:-

actualYear actualPeriod
2005 9
2005 10
2005 11
2005 12
2005 13
2006 1
2006 2
2006 3
2006 4
2006 5
2006 6
2006 7
2006 8

I must count the occurance of a record in the table, much like the query you just gave, so i need to pass these through to the query - can't just count where it occurs -so if year 2006 period 4 has no records, i must return 0

 

[PHV]

OK, say you have created a table with all the possible year,period values named tblPeriods :
SELECT 100*P.actualYear+P.actualPeriod AS YearPeriod, Count(B.impactID) AS Total
FROM tblPeriods AS P LEFT JOIN tblBaseData AS B ON P.actualYear = B.iyear AND P.actualPeriod = B.iperiod
WHERE 100*P.actualYear+P.actualPeriod Between 200509 AND 200608
GROUP BY P.actualYear, P.actualPeriod

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886