Help with SQL query

Hi everyone.

I'm stuck on an SQL query, and hope one of you can help me. Have been trying to solve it all day long without any success. Doesn't even sound that difficult...

I have a table that has four columns. A combination of the first three columns is a foreign key for 'products'. The last column is a foreign key for 'stores'. This table keeps track of which products are assigned to which stores. Example of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 1 100
0 0 2 11476
0 0 3 12939
0 0 4 960
0 0 4 12941
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 6 1
0 0 6 10
0 0 7 1
0 0 7 15033
0 0 7 92606

In the application, the user selects multiple stores, and the application has to display which all products are common to them. For example, if the user selects store 1 and 10, then application has to pick up products (0,0,5) and (0,0,6). Simple enough right?

Unfortunately, I can't form the query. Can someone please help me with this? If you could just give me a query that works for stores 1 and 10, I'm sure I can modify it myself in the application to make the whole process dynamic.

Thanks in advance folks!Table definitions and some sample data (INSERTs) would be helpful.|||drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)|||Originally posted by sbaru
Table definitions and some sample data (INSERTs) would be helpful.

Actually the table structure etc. are completely irrelevant. Forget the whole part about the foreign keys etc... The only table to be used here is the one I showed above..

Let me see if I can make it any more clear though.. One of the earliest solutions I tried was a query like this -

SELECT DISTINCT Id_Prod_Grupo, Id_Prod_Tipo, Id_Prod_Pres FROM CVR_PRODUCTOS_TIENDA
WHERE Id_Cliente = 1 or Id_Cliente = 10

However, this query simply returns all the products that belong to EITHER one of the stores. I want products which are _common_ to both these stores. Get it?|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Worked beautifully Snail! Thanks a lot!!|||You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?|||Originally posted by Pat Phelan
You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?

Nice try - it works better than mine.|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..|||Originally posted by anujjain
Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..

Use Pat Phelan solution - it works like a charm ...|||Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)|||Originally posted by anujjain
Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)

Try this is combination (I hope Pat Phelan will not be offended ;) )

select t1.* from #test t1
join (SELECT f1,f2,f3
FROM #test
WHERE f4 IN (1,2,3) -- store list goes here
GROUP BY f1, f2, f3
HAVING Count(DISTINCT f4) = 3 -- store count goes here
) as t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3|||Off we go, into... Nevermind!SELECT *
FROM phrog AS a
WHERE 2 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM phrog AS z
WHERE Id_Cliente IN (1, 10) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)

Edited to fix two logic errors (oops)!

-PatP|||Originally posted by snail
Try this is combination (I hope Pat Phelan will not be offended ;) ) Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP|||Originally posted by Pat Phelan
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP Nice to meet you ;)|||Pat Phelan / Snake,

Both your solutions don't work for the following set of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 8 1
0 0 8 10
0 0 8 960
0 0 8 92606

The whole table is returned when running the query for 3 stores (1, 10 and 960), whereas row 4 and 8 shouldn't be in the results...|||Did you change the 2 count to a 3 count in my query?

-PatP|||Yes, here is the exact query I ran...

SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)|||Uff-da! Bone-head alert. I missed an important part. I showed the products that existed in those three stores, without regard to where those products were stored (doh!). For a band-aid fix, you can use:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (1, 10, 960)
AND 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)while I think about a more elegant fix. Sorry!

-PatP|||Pat Phelan,

You da man! :) Works perfectly. Thanks a lot!|||Just because I like simple solutions, could you also try:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (SELECT Id_Cliente
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres
GROUP BY z.Id_Cliente
HAVING Count(DISTINCT z.Id_Cliente) = 3)) -- store count goes hereThis shouldn't change the execution plan, but it does simplify the query because you only need to include the store list once and store count once in this query.

-PatP|||Originally posted by Pat
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it?|||Originally posted by rdjabarov
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it? You be amazed at the stuff we've got posted on the walls around here! ;)

-PatPsql

Help with query

I am looking for some suggestions as I am a little stuck trying a create a
view that will give me the correct results. Basically I need to produce a
view where document amounts do not balance to 0 and to do this I am using
the ApplyTo column. It also needs to calculate the remaining balance of a
document
CREATE TABLE [dbo].[Invoices_Payments] (
[type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocNo] [int] NULL ,
[ApplyTo] [int] NULL ,
[Amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,50)
Using the above example, the query should return the following results
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Any help would be much appreciated.
ThanksIt seems that you want to display only one document for each set that
does not balance to zero. That's not difficult but I'm not clear just
which document of the set you want to display. For example, the
following will return the highest numbered doc in each case:
SELECT type, docno, applyto, amount
FROM dbo.Invoices_Payments AS P
WHERE docno = (SELECT MAX(docno)
FROM dbo.Invoices_Payments
WHERE applyto = P.applyto
HAVING SUM(amount)<>0)
Also, what is the primary key? Apparently you don't have one. Shouldn't
ApplyTo be declared as a foreign key?
--
David Portas
SQL Server MVP
--|||I think this may be closer to what you wanted, although perhaps there
was a typo in your data: Credit: 50 instead of Credit: -50.
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
David Portas
SQL Server MVP
--|||David
Thanks for the suggestion, I am going to run it to see if it works with the
data I have here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks David this has really helped. I just have another question for you..
is it possible to exclude the grouping on the apply to number when it equals
0. For example
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,-50)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1018,0,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Payment',1019,0,-65)
Returns the following records
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Credit, 1018, 0 -60
Payment, 1019,0 -65
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
>Thanks David this has really helped. I just have another question for you..
>is it possible to exclude the grouping on the apply to number when it equals
>0. For example
(snip)
Hi Sarah,
You could adapt David's code, like this:
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
Thanks.. can I get this working in a view? Apparently you can't use UNION
in a view?
Is there anyway around this?
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lp1j619m1rf6grkapt32uj177d1lqeetju@.4ax.com...
> On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
>>Thanks David this has really helped. I just have another question for
>>you..
>>is it possible to exclude the grouping on the apply to number when it
>>equals
>>0. For example
> (snip)
> Hi Sarah,
> You could adapt David's code, like this:
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes, you can use UNION in a view. Maybe it's just that the GUI you are
using prevents you doing this? Try creating the view in Query Analyzer:
CREATE VIEW foo
AS
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
If you mean that you can't create an INDEXED view then you are correct.
I don't think it will be possible to create an indexed view for this
because you can't avoid a self-join or subquery.
--
David Portas
SQL Server MVP
--|||Thank a million.. I never thought to try creating the view in the analyzer.
It works a treat!
Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114420218.812239.309580@.z14g2000cwz.googlegroups.com...
> Yes, you can use UNION in a view. Maybe it's just that the GUI you are
> using prevents you doing this? Try creating the view in Query Analyzer:
> CREATE VIEW foo
> AS
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
> If you mean that you can't create an INDEXED view then you are correct.
> I don't think it will be possible to create an indexed view for this
> because you can't avoid a self-join or subquery.
> --
> David Portas
> SQL Server MVP
> --
>

Help with query

I am looking for some suggestions as I am a little stuck trying a create a
view that will give me the correct results. Basically I need to produce a
view where document amounts do not balance to 0 and to do this I am using
the ApplyTo column. It also needs to calculate the remaining balance of a
document
CREATE TABLE [dbo].[Invoices_Payments] (
[type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocNo] [int] NULL ,
[ApplyTo] [int] NULL ,
[Amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,50)
Using the above example, the query should return the following results
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Any help would be much appreciated.
Thanks
It seems that you want to display only one document for each set that
does not balance to zero. That's not difficult but I'm not clear just
which document of the set you want to display. For example, the
following will return the highest numbered doc in each case:
SELECT type, docno, applyto, amount
FROM dbo.Invoices_Payments AS P
WHERE docno =
(SELECT MAX(docno)
FROM dbo.Invoices_Payments
WHERE applyto = P.applyto
HAVING SUM(amount)<>0)
Also, what is the primary key? Apparently you don't have one. Shouldn't
ApplyTo be declared as a foreign key?
David Portas
SQL Server MVP
|||I think this may be closer to what you wanted, although perhaps there
was a typo in your data: Credit: 50 instead of Credit: -50.
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
David Portas
SQL Server MVP
|||David
Thanks for the suggestion, I am going to run it to see if it works with the
data I have here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegro ups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>
|||Thanks David this has really helped. I just have another question for you..
is it possible to exclude the grouping on the apply to number when it equals
0. For example
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,-50)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1018,0,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Payment',1019,0,-65)
Returns the following records
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Credit, 1018, 0 -60
Payment, 1019,0 -65
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegro ups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>
|||On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:

>Thanks David this has really helped. I just have another question for you..
>is it possible to exclude the grouping on the apply to number when it equals
>0. For example
(snip)
Hi Sarah,
You could adapt David's code, like this:
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo
Thanks.. can I get this working in a view? Apparently you can't use UNION
in a view?
Is there anyway around this?
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lp1j619m1rf6grkapt32uj177d1lqeetju@.4ax.com...
> On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
> (snip)
> Hi Sarah,
> You could adapt David's code, like this:
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Yes, you can use UNION in a view. Maybe it's just that the GUI you are
using prevents you doing this? Try creating the view in Query Analyzer:
CREATE VIEW foo
AS
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
If you mean that you can't create an INDEXED view then you are correct.
I don't think it will be possible to create an indexed view for this
because you can't avoid a self-join or subquery.
David Portas
SQL Server MVP
|||Thank a million.. I never thought to try creating the view in the analyzer.
It works a treat!
Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114420218.812239.309580@.z14g2000cwz.googlegr oups.com...
> Yes, you can use UNION in a view. Maybe it's just that the GUI you are
> using prevents you doing this? Try creating the view in Query Analyzer:
> CREATE VIEW foo
> AS
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
> If you mean that you can't create an INDEXED view then you are correct.
> I don't think it will be possible to create an indexed view for this
> because you can't avoid a self-join or subquery.
> --
> David Portas
> SQL Server MVP
> --
>

Help with query

I am looking for some suggestions as I am a little stuck trying a create a
view that will give me the correct results. Basically I need to produce a
view where document amounts do not balance to 0 and to do this I am using
the ApplyTo column. It also needs to calculate the remaining balance of a
document
CREATE TABLE [dbo].[Invoices_Payments] (
[type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocNo] [int] NULL ,
[ApplyTo] [int] NULL ,
[Amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-10
0)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60
)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,50)
Using the above example, the query should return the following results
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Any help would be much appreciated.
ThanksIt seems that you want to display only one document for each set that
does not balance to zero. That's not difficult but I'm not clear just
which document of the set you want to display. For example, the
following will return the highest numbered doc in each case:
SELECT type, docno, applyto, amount
FROM dbo.Invoices_Payments AS P
WHERE docno =
(SELECT MAX(docno)
FROM dbo.Invoices_Payments
WHERE applyto = P.applyto
HAVING SUM(amount)<>0)
Also, what is the primary key? Apparently you don't have one. Shouldn't
ApplyTo be declared as a foreign key?
David Portas
SQL Server MVP
--|||I think this may be closer to what you wanted, although perhaps there
was a typo in your data: Credit: 50 instead of Credit: -50.
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
David Portas
SQL Server MVP
--|||David
Thanks for the suggestion, I am going to run it to see if it works with the
data I have here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks David this has really helped. I just have another question for you..
is it possible to exclude the grouping on the apply to number when it equals
0. For example
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-10
0)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60
)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,-50
)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1018,0,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Payment',1019,0,-65)
Returns the following records
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Credit, 1018, 0 -60
Payment, 1019,0 -65
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:

>Thanks David this has really helped. I just have another question for you.
.
>is it possible to exclude the grouping on the apply to number when it equal
s
>0. For example
(snip)
Hi Sarah,
You could adapt David's code, like this:
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
Thanks.. can I get this working in a view? Apparently you can't use UNION
in a view?
Is there anyway around this?
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lp1j619m1rf6grkapt32uj177d1lqeetju@.
4ax.com...
> On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
>
> (snip)
> Hi Sarah,
> You could adapt David's code, like this:
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes, you can use UNION in a view. Maybe it's just that the GUI you are
using prevents you doing this? Try creating the view in Query Analyzer:
CREATE VIEW foo
AS
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
If you mean that you can't create an INDEXED view then you are correct.
I don't think it will be possible to create an indexed view for this
because you can't avoid a self-join or subquery.
David Portas
SQL Server MVP
--|||Thank a million.. I never thought to try creating the view in the analyzer.
It works a treat!
Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114420218.812239.309580@.z14g2000cwz.googlegroups.com...
> Yes, you can use UNION in a view. Maybe it's just that the GUI you are
> using prevents you doing this? Try creating the view in Query Analyzer:
> CREATE VIEW foo
> AS
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
> If you mean that you can't create an INDEXED view then you are correct.
> I don't think it will be possible to create an indexed view for this
> because you can't avoid a self-join or subquery.
> --
> David Portas
> SQL Server MVP
> --
>