Help with query plez

Hello all,

I'm trying to write a stored procedure that will prompt the user for a
date range and produce a report based on that date range and 4 incident
types. I only need count how may times these incidents happen within
the data range. The query looks like this

SELECT incident, @.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
@.Enter_Ending_Date) AND (incident = 'Customer Accident')
GROUP BY incident

This works fine, but I need to get incident = customer accident,
Customer Illness, Employee Accident and Employee Illness in my incident
table. When ever I try to add Incident = Customer Illness to this
query, I get no results. Any assistance will be greatly appreciated.First of all, how are you prompting the user for values thru Stored
procedures????

Your question is pretty hard to answer as you havent provided any info
about your table or Data...!

Do you have records that qualifies Incident = 'Customer Illness'...!

It would help people to help you if you post some DDL/DML codes to
understand where the problem is...|||sorry about the vagness,

The table I'm trying to query looks something like this

ID(int) Incident(nvarchar) DateOccured (datetime)
1200 Customer Illness 1/1/2003
1201 Customer Illness 1/2/2003
1202 Customer Accident 1/2/2003
1203 Customer Accident 1/3/2003
1204 Employee Accident 1/5/2003
1205 Employee Illness 1/6/2003

The stored procedure prompts the user for a beginning
(@.Enter_Beginning_Date) and ending date(@.Enter_Ending_Date) which uses
the DateOccured column for the specified date range. I want the query
to count the number of occurences that happen within the specified date
range. I want to create a report that looks loke this

Incident Number of Occurences
Customer Illness 2
Customer Accident 2
Employee Accident 1
Employee Illness 1

When ever I query for one incident (Customer Accident), the query will
work fine, but when I insert another statement [(incident = Customer
Illness) AND (incident = Customer Accident)] I get no results, when I
know there are at least 3 incidents of Customer Illness. Does this help?|||Based on the data you gave..

declare @.Enter_Ending_Date datetime
declare @.Enter_Beginning_Date datetime

set @.Enter_Ending_Date='1/2/2003'
set @.Enter_Beginning_Date='1/2/2003'

SELECT incident,
@.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date],
COUNT(*) AS Occurances
FROM Incident
WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
@.Enter_Ending_Date) AND
(incident = 'Customer Illness') or (incident = 'Customer Accident')
--(incident = 'Customer Accident')
GROUP BY incident

/*******************************
RESULTSET

Customer Accident2003-01-02 00:00:00.0002003-01-02 00:00:00.0002
Customer Illness2003-01-02 00:00:00.0002003-01-02 00:00:00.0001|||On 26 Jan 2005 12:09:50 -0800, ndn_24_7 wrote:

(snip)
>The stored procedure prompts the user for a beginning
>(@.Enter_Beginning_Date) and ending date(@.Enter_Ending_Date) which uses
>the DateOccured column for the specified date range.

Hi ndn_27_7,

I presume you meant to write that the front-end app prompts the user for
these dates and passes them to SQL Server when calling the stored
procedure, as there is no way that a stored procedure will ever prompt for
input.

>When ever I query for one incident (Customer Accident), the query will
>work fine, but when I insert another statement [(incident = Customer
>Illness) AND (incident = Customer Accident)] I get no results, when I
>know there are at least 3 incidents of Customer Illness. Does this help?

The WHERE clause is checked against each row individually. No single row
will ever have both 'Customer Illness' and 'Customer Accident' in the same
column (incident).

SELECT incident, @.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE DateOccured BETWEEN @.Enter_Beginning_Date
AND @.Enter_Ending_Date
AND ( incident = 'Customer Accident'
OR incident = 'Customer Illness')
GROUP BY incident

or

SELECT incident, @.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE DateOccured BETWEEN @.Enter_Beginning_Date
AND @.Enter_Ending_Date
AND incident IN ('Customer Accident', 'Customer Illness')
GROUP BY incident

(Note that IN is just a short form for writing a bunch of OR's)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||It worked very good, THank you for all your assistance

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