Sub Queries wont return Value

Question

0.00/5 (No votes)

See more:

Hello all

I'm having a hard time trying to get a query to bring the data i want. The rest of the fields in the select show just fine except for those that should come from the Sub queries.

Many thanks in advance!

Here is the code:

SQL

with cte as  
 (select 
  rt.TelID as 'Telefono',  
  td.Descr as 'Tipo', cd.Descr As 'Categoria', 
  e.codigo+' - '+e.Nombre as 'Empleado', 
  t.RentBas as 'Renta', 
 (select distinct  sum(MPagar) from TelefonoFactu rt1   where ANO = 2012   and MES = 6 - 3   
  AND rt1.telid IN (Select TelID from Telefonos where rt1.TelID = TelID) GROUP BY rt1.TelId, rt1.MPagar) as 'M3', 	
 (select distinct sum(MPagar) from TelefonoFactu rt2   where ANO =  2012   and MES = 6 - 2  AND rt2.TelID = rt.TelID
  AND rt2.telid IN (Select TelID from Telefonos where rt2.TelID = TelID) GROUP BY TelId) as 'M2',  
   (select distinct sum(MPagar) from TelefonoFactu rt3 where ANO =  2012   and MES = 6 - 1  AND rt3.TelID = rt.TelID
  AND rt3.telid IN (Select TelID from Telefonos where rt3.TelID = TelID) GROUP BY TelId) as 'M1'
 ,sum(MPagar) as 'MActual'
  FROM TelefonoFactu rt
  INNER JOIN Telefonos t on (t.TelID = rt.telID and t.[status] = 1)
  INNER JOIN TipoDevice td on (td.TipoID = t.TipoID)
  INNER JOIN CategoriaDevice cd on (cd.CategID = t.CategID) 
  LEFT OUTER JOIN empleados e on (e.codigo = t.CodAsig)
where ANO =  YEAR(GETDATE()) and Mes = 6 

AND (t.TipoID like  'TD001')
AND (t.CategID LIKE 'CC001')

  Group by  rt.TelID,  
  td.Descr , cd.Descr , 
  e.codigo+' - '+e.Nombre, 
  t.RentBas)
  
  select Telefono,  
  Tipo, Categoria, 
  Empleado, 
  Renta, 
   CASE M3 WHEN null  THEN 0 END AS M3,
  --M3,
  CASE M2 WHEN null  THEN 0 END AS M2, 	
  --M2, 
  CASE M1 WHEN null  THEN 0 END AS M1, 
 -- M1,
  MActual,
 (MActual - M1) / MActual As 'V1M',
 (M1 + M2 + M3) As 'T3M',
 (M1 + M2 + M3) / 3 As 'P3M',
 (MActual - ((M1 + M2 + M3) / 3)) / ((M1 + M2 + M3) / 3) AS 'V3M'
FROM cte

-------------> This is the Table structure

SQL

USE [Portal]
GO

/****** Object:  Table [dbo].[TelefonoFactu]    Script Date: 07/16/2013 16:50:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TelefonoFactu](
	[TelID] [nvarchar](10) NOT NULL,
	[Ano] [int] NOT NULL,
	[Mes] [int] NOT NULL,
	[Fecha] [smalldatetime] NOT NULL,
	[MItbis] [decimal](19, 2) NULL,
	[MFactura] [decimal](19, 2) NULL,
	[MPagar] [decimal](19, 2) NULL,
	[MDbCr] [decimal](19, 2) NULL,
	[Nota] [nvarchar](200) NULL,
	[FechaCrea] [smalldatetime] NULL,
	[FechaMod] [smalldatetime] NULL,
	[UserCrea] [nvarchar](20) NULL,
	[UserMod] [nvarchar](20) NULL,
	[NCF] [varchar](19) NULL,
	[MISC] [decimal](19, 2) NULL,
	[MCargoAtraso] [decimal](19, 2) NULL,
	[Modificado] [varchar](1) NOT NULL,
 CONSTRAINT [PK_TelefonoFactu] PRIMARY KEY CLUSTERED 
(
	[TelID] ASC,
	[Ano] ASC,
	[Mes] ASC,
	[Fecha] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MItbis'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MFactura'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MPagar'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MDbCr'
GO

ALTER TABLE [dbo].[TelefonoFactu] ADD  CONSTRAINT [DF_TelefonoFactu_FechaCrea]  DEFAULT (getdate()) FOR [FechaCrea]
GO

ALTER TABLE [dbo].[TelefonoFactu] ADD  DEFAULT ('M') FOR [Modificado]
GO

Posted 16-Jul-13 5:47am

Dionis Matos

Updated 16-Jul-13 10:51am

v2

Add a Solution

Comments

ZurdoDev 16-Jul-13 11:54am

And what is wrong? What IS happening?

Dionis Matos 16-Jul-13 11:57am

Well all the sub queries return null, while the other fields return the data they are supposed to return. i dont know how to make it work right now.

ZurdoDev 16-Jul-13 12:01pm

If you run the subqueries separately do they return data? Also, do you really mean 6 - 2 in your where clause?

Dionis Matos 16-Jul-13 13:28pm

6 - 2 is the month minus 2. This Query should return the phone bill for each employee for the current month, but also the same phone bill for the last 3 months.

ZurdoDev 16-Jul-13 13:40pm

So what happens if you run just the subquery part by itself?

Dionis Matos 16-Jul-13 14:16pm

it returns all the values for the phone bill of each phone in the relationship, mysteriously it wont doit when i run the whole thing..

ZurdoDev 16-Jul-13 14:18pm

It would appear that the join is not getting what you think it should then.

RedDk 16-Jul-13 12:57pm

DM,

This looks like a well thought-out procedure.

[edit 11.2]
.
.
.
d
a
t
a
?

[end edit 11.2]

Dionis Matos 16-Jul-13 13:16pm

Thanks RedDK

This Query should return the phone bill for each employee for the current month, but also the same phone bill for the last 3 months. Thats only a part of a much larger query.

I've made some modifications and now i get this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Dionis Matos 16-Jul-13 16:23pm

Hello, What do you mean by data?

RedDk 16-Jul-13 16:36pm

"TelefonoFactu" is a table. Show some lines of data found in that table, include all columns; better yet right-context click that table in the Object Explorer and select "Script Table AS -> CREATE To clipboard" and paste THAT in your original post (use "Improve Question" widget).

RedDk 16-Jul-13 17:00pm

Now try:

SELECT * FROM [dbo].[TelefonoFactu]

Is there any return?

Maciej Los 16-Jul-13 14:11pm

I think that the reason of your problem is the difference in conditions:
where ANO = YEAR(GETDATE()) and Mes = 6 - inside cte
where ANO = 2012 and Mes = 6 - outside cte
;)

[no name] 16-Jul-13 17:07pm

are you sure about select distinct sum(MPagar) did you mean select sum(distinct MPagar) ?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-17T06:37:00

I would suggest you to:
1) reduce number of subqueries,
2) optimize query.

All above is to improve performance ;)

For example below query should return SUM(MPagar) for 3 months starting from actual month:

SQL

SELECT MES, SUM(MPagar) AS SumOfPagar
FROM TelefonoFactu 
GROUP BY MES
WHERE ANO = YEAR(GETDATE()) and MES BETWEEN MONTH(GETDATE())-3 AND MONTH(GETDATE())

Example result:

MES  SumOfPagar
5    100
6    150
7    125

As i see, you were stuck with query which should return sums of MPagar for desired set of records and months. In your case, i would suggest you to use PIVOT[^] query.

SQL

DECLARE @cols VARCHAR(30)
DECLARE @counter INT

SET @counter = MONTH(GETDATE())-3

WHILE @counter < MONTH(GETDATE()) BEGIN
    SET @cols = @cols + '[' + CONVERT(VARCHAR(2), @counter) + '],'
    SET @counter = @counter + 1
END

SET @cols = LEFT(@cols, LEN(@cols)-1)
--[5],[6],[7]

SELECT Telefono, Tipo, Categoria, Empleado, Renta, @cols 
FROM (
    SELECT rt.TelID as 'Telefono',  td.Descr as 'Tipo', cd.Descr As 'Categoria', e.codigo+' - '+e.Nombre as 'Empleado', t.RentBas as 'Renta', MPagar as 'MActual'
    FROM TelefonoFactu rt INNER JOIN Telefonos t on (t.TelID = rt.telID and t.[status] = 1)
        INNER JOIN TipoDevice td on (td.TipoID = t.TipoID) INNER JOIN CategoriaDevice cd on (cd.CategID = t.CategID) 
        LEFT OUTER JOIN empleados e on (e.codigo = t.CodAsig)
    WHERE ANO = YEAR(GETDATE()) and Mes = BETWEEN MONTH(GETDATE())-3 AND MONTH(GETDATE()) AND (t.TipoID like  'TD001') AND (t.CategID LIKE 'CC001')
    ) AS DT
PIVOT(SUM(MActual) FOR Telefono IN(@cols))

Sub Queries wont return Value

1 solution

Solution 2

Add your solution here

Preview 0

Sub Queries wont return Value

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us