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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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))