I have 2 working query that I need to use to generate a report, I have configured 4 parameters that user can choose and I want that if idarea=0 then first query is executed, else second one. I tried the following but give me an error because of the nested with… any help? Thanks
x
declare @DataDa as date='20210501';
declare @DataA as date='20210504';
declare @idUtilizzatore as int=0;
declare @idArea as int=0;
SELECT CASE WHEN ( @idArea=0 )
THEN (select dbaree.dbo.StoricoAree.Descr as Lettore,
TIMBACCESSI.CODICEBADGE,
COGNOME as Cognome,NOME as Nome,
AZIENDE.DESCR as Azienda,FORMAT(DATAORA,'HH:mm') as Ora,
FORMAT(dataora,'dd/MM/yyyy') as Data,
iif(verso=0,'U','E') as Flusso from timbaccessi
join badge on badge.codice=timbaccessi.codicebadge
join anagrafico on anagrafico.ID=badge.IDUTILIZZATORE
join profilo on profilo.IDUTILIZZATORE=badge.IDUTILIZZATORE
join aziende on aziende.id=profilo.IDAZIENDA
join terminali on terminali.id=TIMBACCESSI.IDTERMINALE
join comparee on terminali.id=COMPAREE.IDTERMINALE
join dbAree.dbo.StoricoAree on storicoaree.idArea=COMPAREE.IDAREA
where (DATAORA>=@DataDa and DATAORA<@DataA) and (DATAORA >= dataDa and(DATAORA <= dataA or dataa is null)) and (badge.DATAFINE is null or badge.DATAFINE>=@DataA) AND (badge.idUtilizzatore = @IdUtilizzatore OR 0 = @IdUtilizzatore) AND (PROFILO.IDAZIENDA=@idAzienda OR 0 = @idAzienda) and (dbaree.dbo.StoricoAree.id=@idArea OR 0=@idArea)
group by dataora,cognome,nome,dbaree.dbo.StoricoAree.Descr,timbaccessi.CODICEBADGE,aziende.DESCR,timbaccessi.VERSO
ORDER BY Azienda asc, cognome asc, nome asc, Data asc, ora asc)
ELSE (with area as(
select t.CODICEBADGE as 'Numero tessera',
COGNOME as Cognome,NOME as Nome,
AZIENDE.DESCR as Azienda,FORMAT(DATAORA,'HH:mm') as Ora,
FORMAT(dataora,'dd/MM/yyyy') as Data,
iif(verso=0,'U','E') as Flusso, t.IDTERMINALE,t.DATAORA from timbaccessi t
join badge on badge.codice=t.codicebadge
join anagrafico on anagrafico.ID=badge.IDUTILIZZATORE
join profilo on profilo.IDUTILIZZATORE=badge.IDUTILIZZATORE
join aziende on aziende.id=profilo.IDAZIENDA
join terminali on terminali.id=t.IDTERMINALE
where (DATAORA>=@DataDa and DATAORA<@DataA)
and (badge.DATAFINE is null or badge.DATAFINE>=@DataA) AND (badge.idUtilizzatore = @IdUtilizzatore OR 0 = @IdUtilizzatore)
group by dataora,cognome,nome,t.CODICEBADGE,aziende.DESCR,t.VERSO,t.IDTERMINALE)
select a.[Numero tessera],a.Cognome,a.Nome,a.Azienda,a.Ora,a.Data,a.Flusso,s.Descr from area a join dbAree.dbo.StoricoAree s on s.id=@idArea where a.IDTERMINALE in (select idterminale from dbAree.dbo.termArea t join dbaree.dbo.StoricoAree s on s.id=t.idArea where t.idArea=@idArea and (@DataDa >= s.datada and (s.dataA is null or @DataDa < s.dataA)))
order by a.DATAORA )
END;
Advertisement
Answer
Just use IF
:
IF (@idArea = 0)
BEGIN
<query1>;
END;
ELSE
<query 2>;