How can I combine 2 SELECT statements into 1 ?

Please consider the following SELECT statements; is it possible to combine
them into a sole SELECT statement returning 4 columns ? Thank you.
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
--
Gilbert.HI
Use UNION or UNION ALL
Cheers
vinu
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Sorry but, as far as I know, with UNION I will get more lines with always 3
columns. This is not why I need. I need the same number of lines
(approximatively) with 4 columns.
--
Gilbert.
"vinu" wrote:

> HI
> Use UNION or UNION ALL
> Cheers
> vinu
>
> "Gilbert" <gilbert@.nospam.nospam> wrote in message
> news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
>
>|||Please post DDL, sample data and expected results.
You might also consider joining the two queries as derived tables on common
columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fool
you into thinking I have any idea what you really want. Stop us guessing and
explain your goals.
ML
http://milambda.blogspot.com/|||I thought that giving the SELECT statements was enough details; sorry. I
intend to do monthly statistics on a table storing user requests. The first
query gives the number of requests per month. The second query gives the
average delay of the answer per month, for all requests that are closed (thi
s
is why there is a WHERE clause). If I execute 2 independent SELECT statement
s
I receive 2 tables with 3 columns, each line displaying the value for a
specific month. What I would like to do is to execute only 1 SELECT, giving
me 1 table of 4 columns (year, month, number of requests, and average delay)
,
each line displaying the values for a specific month. Thank you for your hel
p.
--
Gilbert.
"ML" wrote:

> Please post DDL, sample data and expected results.
> You might also consider joining the two queries as derived tables on commo
n
> columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fo
ol
> you into thinking I have any idea what you really want. Stop us guessing a
nd
> explain your goals.
>
> ML
> --
> http://milambda.blogspot.com/|||Ok, I see. But we still need to understand how the data in the two queries
can be joined to form one valid result-set.
That's why we need to see the DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||OK. Here is the DDL (simplified by hand to remove unuseful columns and
constraints) :
CREATE TABLE [InterventionDI] (
[NumIntervention] [int] IDENTITY (1, 1) NOT NULL ,
[DhEnregistrementIntervention] [datetime] NOT NULL CONSTRAINT
[DF_InterventionDI_DhEnregistrementInter
vention] DEFAULT (getdate()),
[DhClotureIntervention] [datetime] NULL ,
[CodEtatIntervention] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL,
CONSTRAINT [PK_InterventionDI] PRIMARY KEY NONCLUSTERED
(
[NumIntervention]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO
Here is a set on 10 records (INSERT created by hand) :
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (18, '13/09/2002 09:52:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (19, '13/09/2002 09:53:18', '21/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (20, '14/09/2002 09:54:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (21, '14/09/2002 09:55:18', '15/09/2002 16:53:57', 'O')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (22, '14/09/2002 09:56:18', '15/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (23, '14/09/2002 09:57:18', '18/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (24, '13/10/2002 09:58:18', '20/09/2003 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (25, '13/10/2002 09:59:18', '20/09/2004 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (26, '13/01/2003 09:12:18', '20/09/2005 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (27, '13/01/2003 09:22:18', '20/09/2006 16:53:57', 'F')
Thank you.
--
Gilbert.
"ML" wrote:

> Ok, I see. But we still need to understand how the data in the two queries
> can be joined to form one valid result-set.
> That's why we need to see the DDL and sample data:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML
> --
> http://milambda.blogspot.com/|||Based on what the two queries have in common this might give the correct
result:
select Demande.Anee
,Demande.Mois
,Demande.ItvQteDemande
,Duree.ItvDuree
from (
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Demande (Anee, Mois, ItvQteDemande)
inner join (
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Duree (Anee, Mois, ItvDuree)
on (Duree.Anee = Demande.Anee)
and (Duree.Mois = Demande.Mois)
order by Demande.Anee
,Demande.Mois
Please, validate this against a sufficient amount of cases before using it
in production.
ML
http://milambda.blogspot.com/|||UNTESTED
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
, (
case when CodEtatIntervention = 'F' then
ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0)
ELSE NULL END
)AS ItvDuree
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Wonderful ! Thank you for having teached me something new.
--
Gilbert.
"ML" wrote:

> Based on what the two queries have in common this might give the correct
> result:
> select Demande.Anee
> ,Demande.Mois
> ,Demande.ItvQteDemande
> ,Duree.ItvDuree
> from (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Demande (Anee, Mois, ItvQteDemande)
> inner join (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Duree (Anee, Mois, ItvDuree)
> on (Duree.Anee = Demande.Anee)
> and (Duree.Mois = Demande.Mois)
> order by Demande.Anee
> ,Demande.Mois
> Please, validate this against a sufficient amount of cases before using it
> in production.
>
> ML
> --
> http://milambda.blogspot.com/sql

How can I check for Null or Empty in an Insert/Select Statement - example in Access

The following sample of code in access is what i need to be able to do in
MSSQL 2000.
Can i use iif statements like this in the select part of the insert if so i
cannot get this to work in MSSQL
iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
INSERT INTO dbo_ContactAddress (ContactID, AddressTypeCode, AddressLine1,
AddressLine2, AddressLine3, AddressLine4, AddressLine5,AddressLine6,
CountryCode, Town, PostalCode, State, DoNotMarket, DoNotSell )
SELECT NBCDON.ID+100100000, 1,iif(IsNull(NBCDON.Unit), "",NBCDON.Unit+"/") +
NBCDON.Number +iif(IsNull(NBCDON.suf), "",NBCDON.suf)+ " " + NBCDON.Street
," ", " ", " ", " ", " ",1, iif(IsNull(NBCDON.SubTown),
"?",NBCDON.SubTown) ,iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
NBCDON.State, 0, 0
FROM NBCDON
WHERE not IsEmpty(NBCDON.Street)
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003I resolved this by Using the IsNul( field, value if null) Expression.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003sql

How can I catch all errors of the stored at the same time?

I have a stored prcedure . In the stored I wrote 3 SQL statements, one is OK but 2 other statements have error as:

1. Invalid column name 'F2'

2. Invalid object name '##_152008049'.

I put the stored inside try block and catch error in catch block as the following. But I always catch only the first error : invalid column name F2 . How about the second statement?

How can I catch all the errors when I put the stored in try block. Now I don't want to add try..catch inside the store for each statement.

Begin try

exec mystored

End try

begin catch

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage,

end catch

You are only getting the first one, because when you encounter the first error, it will fall through to the catch block. Any statements after the error don't even get executed.|||Not all errors are of the same kind, there is a difference between statement abort and batch abort errors. But as the previous poster already said, there is no way to return to the next statement after the catch block was handled.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de