|
Home > Archive > MS SQL Server > April 2005 > Help with query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Sarah Kingswell 2005-04-21, 11:23 am |
| 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_C
P1_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
| |
| David Portas 2005-04-21, 1:23 pm |
| 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
--
| |
| David Portas 2005-04-21, 1:23 pm |
| 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
--
| |
| Sarah Kingswell 2005-04-22, 11:23 am |
| 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_REPLYI
NG_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
> --
>
| |
| Sarah Kingswell 2005-04-22, 11:23 am |
| 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_REPLYI
NG_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
> --
>
| |
| Hugo Kornelis 2005-04-22, 8:23 pm |
| 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)
| |
| Sarah Kingswell 2005-04-25, 3:23 am |
| 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:lp1j619m1rf6grk
apt32uj177d1lqeetju@
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)
| |
| David Portas 2005-04-25, 7:23 am |
| 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
--
| |
| Sarah Kingswell 2005-04-25, 7:23 am |
| Thank a million.. I never thought to try creating the view in the analyzer.
It works a treat!
Cheers
"David Portas" < REMOVE_BEFORE_REPLYI
NG_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
> --
>
|
|
|
|
|