Link to home
Create AccountLog in
Avatar of rp
rpFlag for Portugal

asked on

sqlserver 2005/2008

I have two tables related to left join. I needed to know whether it was possible to do everything in the same SQL query:

For each product from a date show detail table, before that date show accumulated (Like Sum).

ex:

Date Criteria = 2010/05/01

Product          date           Unit value

1                   2010/02/05     20        
1                   2010/04/08     19                      Sum two lines above -> 39
1                   2010/06/21     19                      Show this line ->             19  
2                   2010/02/04      15
2                   2010/03/09       15                     Sum Two lines above ->   30
2                   2010/06/08       15                     Show this line ->               15
2                   2010/08/03       15                     Show this line ->               15
Avatar of tigin44
tigin44
Flag of Türkiye image

can you clarify your question more
I think this should help you
SELECT Product, NULL AS [date], SUM([Unit Value]) AS [Unit Value]
FROM yourTable
WHERE [date] < @givenDate
GROUP BY Product
UNION 
SELECT Product, [date], [Unit Value]
FROM yourTable
WHERE [date] >= @givenDate
ORDER BY Product, [date]

Open in new window

Avatar of imran_fast
imran_fast

Try this
select * from
(SELECT Product, '2010-05-01' AS [date], SUM([Unit Value]) AS [Unit Value]
FROM TableName
WHERE [date] <='2010-05-01'
GROUP BY Product
UNION
SELECT Product, [date], [Unit Value]
FROM TableName
WHERE [date] > '2010-05-01') as t
ORDER BY Product, [date]
select * from 
(SELECT Product, '2010-05-01' AS [date], SUM([Unit Value]) AS [Unit Value]
FROM TableName
WHERE [date] <='2010-05-01'
GROUP BY Product
UNION 
SELECT Product, [date], [Unit Value]
FROM TableName
WHERE [date] > '2010-05-01') as t
ORDER BY Product, [date]

Open in new window

Avatar of rp

ASKER

After try your code i get the error: "invalid column name:'datainicial'" in the where clause's


select * from
(SELECT entidade, '2010-05-01' as [datainicial] , SUM(credito) AS credito,SUM(debito) AS debito
FROM MovCCclientes
WHERE [datainicial] <='2010-05-01'
GROUP BY entidade
UNION
SELECT numdocumento, datadocumento, credito, debito
FROM MovCCclientes
WHERE [datainicial] > '2010-05-01') as t
ORDER BY entidade, datainicial
Avatar of Anthony Perkins
That would be because the column "datainicial" does not exist in MovCCclientes
Below code will work,
the problem is both the select statement in the union should have same column in the select statement or atleast the datatype of the columns should be same

for example the datatype of column entidade and numdocumento should be same

the second thing is there is no datainicial in the second statement, there you are selecting datadocumento

select * from
(SELECT entidade, '2010-05-01' as [datainicial] , SUM(credito) AS credito,SUM(debito) AS debito
FROM MovCCclientes
WHERE [datainicial] <='2010-05-01'
GROUP BY entidade
UNION
SELECT numdocumento, [datainicial], credito, debito
FROM MovCCclientes
WHERE [datainicial] > '2010-05-01') as t
ORDER BY entidade, datainicial

Open in new window

Avatar of rp

ASKER

With your code i have this errors:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'datainicial'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'datainicial'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'datainicial'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'entidade'.
We can go round and round with this, but you really need to post the structure for your table MovCCclientes (CREATE TABLE).  This way it will become abundantly clear what I meant here http:#a34430304
the column "datainicial" does not exist in MovCCclientes
Avatar of rp

ASKER

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MovCCclientes](
      [Id] [bigint] IDENTITY(1,1) NOT NULL,
      [IdStamp] [varchar](50) NOT NULL,
      [Entidade] [decimal](12, 0) NOT NULL,
      [Entidade1] [decimal](12, 0) NOT NULL,
      [DataDocumento] [datetime] NOT NULL,
      [NumDocumento] [varchar](20) NULL,
      [Descritivo] [varchar](25) NULL,
      [IdCodMov] [bigint] NOT NULL,
      [Debito] [decimal](18, 2) NOT NULL,
      [Credito] [decimal](18, 2) NOT NULL,
      [Debito1] [decimal](18, 2) NOT NULL,
      [Credito1] [decimal](18, 2) NOT NULL,
      [ValorIva] [decimal](18, 2) NOT NULL,
      [ValorIva1] [decimal](18, 2) NOT NULL,
      [Sinal] [varchar](1) NULL,

 CONSTRAINT [PK_MovCli] PRIMARY KEY CLUSTERED
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
you dont have datainicial column in your table  
try below code
select * from
(SELECT entidade, '2010-05-01' as [datainicial] , SUM(credito) AS credito,SUM(debito) AS debito
FROM MovCCclientes
WHERE [DataDocumento] <='2010-05-01'
GROUP BY entidade
UNION
SELECT numdocumento, [DataDocumento] as [datainicial], credito, debito
FROM MovCCclientes
WHERE [DataDocumento] > '2010-05-01') as t
ORDER BY entidade, datainicial

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of imran_fast
imran_fast

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account