I have a table (wfm_astat) that looks like this:
auftrag | af
---+--
1311703 | 30
1311703 | 40
1400065 | 20
1400065 | 30
1400065 | 40
I have another table (wfm_auftrag) that looks like this:
auftrag |
---+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |
I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:
auftrag | af
---+--
1311703 | 30
1400065 | 20
Can anybody give me some ideas on how to select the record with the smallest 'af' value??
Any help would be appreciated.
Thanks, stephen.In case anybody else is looking for an answer to this problem, here it is:
SELECT wfm_astat.auftrag, wfm_astat.af
FROM wfm_astat
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
AND wfm_astat.af = (
SELECT min(af)
FROM wfm_astat
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag)|||Self-answering : good job !
This would also work :
SELECT wfm_astat.auftrag, min(wfm_astat.af)
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag;
Regards,
RBARAER|||I have a table (wfm_astat) that looks like this:
auftrag | af | abt |
---+--+--+
1311703 | 30 | D00 |
1311703 | 40 | F00 |
1400065 | 20 | C12 |
1400065 | 30 | C23 |
1400065 | 40 | F00 |
I have another table (wfm_auftrag) that looks like this:
auftrag |
---+
1311703 |
1311704 |
1400065 |
1400066 |
1400067 |
I am trying to create a query that returns the record with the smallest 'af' number from the wfm_astat table, for all records that are also in the wfm_auftrag table. So my result would look something like this:
auftrag | af | abt
---+--+--
1311703 | 30 | D00
1400065 | 20 | C12
The query from RBARAER worked fine when I only had the two columns in the wfm_astat table, but now that there is the extra 'abt' field, I no longer get only the minimum 'af' value per 'auftrag'. Here is my query that returns the wrong results:
SELECT wfm_astat.auftrag, min(wfm_astat.af), wfm_astat.abt
FROM wfm_astat, wfm_auftrag
WHERE wfm_astat.auftrag = wfm_auftrag.auftrag
GROUP BY wfm_astat.auftrag, wfm_astat.abt;
Can anybody tell me what I am doing wrong here?|||Try removing wfm_astat.abt from the group by clause.|||urquel, that would require removing it from the SELECT list as well (so as not to cause a syntax error), but then the query would no longer return the right number of columns
steve, try this:select wfm_astat.auftrag
, wfm_astat.af
, wfm_astat.abt
from wfm_astat as x
inner
join wfm_auftrag
on wfm_astat.auftrag
= wfm_auftrag.auftrag
where wfm_astat.af
= ( select min(af)
from wfm_astat
where auftrag = x.auftrag )
Showing posts with label selecting. Show all posts
Showing posts with label selecting. Show all posts
Monday, March 19, 2012
Monday, March 12, 2012
Help With Query
Hello,
I am selecting DISTINCT records from a table:
"SELECT DISTINCT Model, Description FROM Warranty"
This works great, but I would like to concatinate some other data in the
returned records (possibly with a JOIN)
From the same table I would like the returned records to return "Family" and
"Price".
So the returned records would be:
Model Description Family Price (with the Model and Description being
Distrinct).
NOTE: There is a Primary Key Called "ID".
Maybe something like:
SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
Any help is greatly appreciated,
Thanks,
Chuck
Hi Charles
How about
SELECT Model, Description, MAX(Family) AS [Family], Max(Price) AS [Price]
FROM Warranty
GROUP BY Model, Description
John
>
"Charles A. Lackman" wrote:
> Hello,
> I am selecting DISTINCT records from a table:
> "SELECT DISTINCT Model, Description FROM Warranty"
> This works great, but I would like to concatinate some other data in the
> returned records (possibly with a JOIN)
> From the same table I would like the returned records to return "Family" and
> "Price".
> So the returned records would be:
> Model Description Family Price (with the Model and Description being
> Distrinct).
> NOTE: There is a Primary Key Called "ID".
> Maybe something like:
> SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
> C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
> Any help is greatly appreciated,
> Thanks,
> Chuck
>
>
I am selecting DISTINCT records from a table:
"SELECT DISTINCT Model, Description FROM Warranty"
This works great, but I would like to concatinate some other data in the
returned records (possibly with a JOIN)
From the same table I would like the returned records to return "Family" and
"Price".
So the returned records would be:
Model Description Family Price (with the Model and Description being
Distrinct).
NOTE: There is a Primary Key Called "ID".
Maybe something like:
SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
Any help is greatly appreciated,
Thanks,
Chuck
Hi Charles
How about
SELECT Model, Description, MAX(Family) AS [Family], Max(Price) AS [Price]
FROM Warranty
GROUP BY Model, Description
John
>
"Charles A. Lackman" wrote:
> Hello,
> I am selecting DISTINCT records from a table:
> "SELECT DISTINCT Model, Description FROM Warranty"
> This works great, but I would like to concatinate some other data in the
> returned records (possibly with a JOIN)
> From the same table I would like the returned records to return "Family" and
> "Price".
> So the returned records would be:
> Model Description Family Price (with the Model and Description being
> Distrinct).
> NOTE: There is a Primary Key Called "ID".
> Maybe something like:
> SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
> C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
> Any help is greatly appreciated,
> Thanks,
> Chuck
>
>
Friday, March 9, 2012
Help With Query
Hello,
I am selecting DISTINCT records from a table:
"SELECT DISTINCT Model, Description FROM Warranty"
This works great, but I would like to concatinate some other data in the
returned records (possibly with a JOIN)
From the same table I would like the returned records to return "Family" and
"Price".
So the returned records would be:
Model Description Family Price (with the Model and Description being
Distrinct).
NOTE: There is a Primary Key Called "ID".
Maybe something like:
SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
Any help is greatly appreciated,
Thanks,
ChuckHi Charles
How about
SELECT Model, Description, MAX(Family) AS [Family], Max(Price) AS [P
rice]
FROM Warranty
GROUP BY Model, Description
John
>
"Charles A. Lackman" wrote:
> Hello,
> I am selecting DISTINCT records from a table:
> "SELECT DISTINCT Model, Description FROM Warranty"
> This works great, but I would like to concatinate some other data in the
> returned records (possibly with a JOIN)
> From the same table I would like the returned records to return "Family" a
nd
> "Price".
> So the returned records would be:
> Model Description Family Price (with the Model and Description being
> Distrinct).
> NOTE: There is a Primary Key Called "ID".
> Maybe something like:
> SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
> C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
> Any help is greatly appreciated,
> Thanks,
> Chuck
>
>
I am selecting DISTINCT records from a table:
"SELECT DISTINCT Model, Description FROM Warranty"
This works great, but I would like to concatinate some other data in the
returned records (possibly with a JOIN)
From the same table I would like the returned records to return "Family" and
"Price".
So the returned records would be:
Model Description Family Price (with the Model and Description being
Distrinct).
NOTE: There is a Primary Key Called "ID".
Maybe something like:
SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
Any help is greatly appreciated,
Thanks,
ChuckHi Charles
How about
SELECT Model, Description, MAX(Family) AS [Family], Max(Price) AS [P
rice]
FROM Warranty
GROUP BY Model, Description
John
>
"Charles A. Lackman" wrote:
> Hello,
> I am selecting DISTINCT records from a table:
> "SELECT DISTINCT Model, Description FROM Warranty"
> This works great, but I would like to concatinate some other data in the
> returned records (possibly with a JOIN)
> From the same table I would like the returned records to return "Family" a
nd
> "Price".
> So the returned records would be:
> Model Description Family Price (with the Model and Description being
> Distrinct).
> NOTE: There is a Primary Key Called "ID".
> Maybe something like:
> SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
> C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
> Any help is greatly appreciated,
> Thanks,
> Chuck
>
>
Friday, February 24, 2012
Help with MDX selecting multiple attributes in Where clause
Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~Steven
I guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&[2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&[2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:
> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~Steven
I guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&[2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&[2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:
> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>
Help with MDX selecting multiple attributes in Where clause
Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/20
05]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]
),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~StevenI guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&
1;2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&
1;2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:
> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/
2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[
2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[
2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/20
05]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]
),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~StevenI guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&
1;2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&
1;2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:
> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/
2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[
2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[
2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>
Sunday, February 19, 2012
Help with joining/selecting values to show
I am editing a pre-existing view.
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
I did not cover the percentage thing, since that was just too unclear
to me how it works.
>
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
>
From what you have said, it could be something like this:
>
LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate
>
Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.
>
>
I did not cover the percentage thing, since that was just too unclear
to me how it works.
>
If you want more accurate assistance, I would suggest that you post:
>
o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Well, this is apparently solved.
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
I did not cover the percentage thing, since that was just too unclear
to me how it works.
Thanks for the table and sample data. Below is my query (together with
a table I composed for the clients.) For quote 10, I got different
results that you had as the desired. Looking at the sample data, my
result seemed OK, but I may have misunderstood something about the
business rules. Whether this query performs better than yours, I don't
know. Only testing can tell.
This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.
I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.
So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.
Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.
Example:
for group X of clients...
Deposit 1 due on oct 1: $20
Deposit 2 due on oct 15: $30
Deposit 3 due on nov 15: $40
Deposit 4 due on nov 30: $50
for group Y of clients...
Deposit 1 due on Oct 30: $200
Deposit 2 due on Nov 30: $300
Deposit 3 due on Dec 30: $400
So when if I execute the view today (Nov 7th) each client from group X
should have:
Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)
Group Y should have:
Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)
And so on.J (julian.solis@.gmail.com) writes:
Quote:
Originally Posted by
This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.
>
I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.
>
So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
From what you have said, it could be something like this:
LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate
Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.
Quote:
Originally Posted by
Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.
I did not cover the percentage thing, since that was just too unclear
to me how it works.
If you want more accurate assistance, I would suggest that you post:
o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hey there, sorry about the lack of details.
<< o CREATE TABLE statement for your deposits table>>
CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the
field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT NULL DEFAULT 1 --(typeid 0 or 1 means amount is a
dollar amount. typeid = 2 means amount is a percentage amount)
PRIMARY KEY(QuoteDueDateID)
)
<<, and a table that represents the view, including the essential
columns only.>>
The result from the view is pretty big but the important columns are
QuoteID (int) - the quote they belong to -, BillingPrice (money) -the
total they have to pay - and payments (money) - how much they've paid
so far -
<< INSERT statements with sample data.>>
INSERT INTO datQuotationDueDates VALUES (4, 1, '10/1/2006', 15, 2)
this is a deposit due on October 1st for 15% of their total billing
price for all clients in quote 4
INSERT INTO datQuotationDueDates VALUES (4, 1, '11/1/2006', 100, 1)
this is a deposit due on November 1st for $100 for all clients in quote
4
INSERT INTO datQuotationDueDates VALUES (4, 1, '12/1/2006', 100, 1)
this is a deposit due on December 1st for $100 for all clients in quote
4
INSERT INTO datQuotationDueDates VALUES (4, 1, '1/1/2007', 100, 1)
this is a deposit due on January 1st for $100 for all clients in quote
4
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/1/2006', 20, 2)
this is a deposit due on November 1st for 20% of their total for all
clients in quote 10
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/15/2006', 10, 2)
this is a deposit due on November 15th for 10% of their total for all
clients in quote 10
INSERT INTO datQuotationDueDates VALUES (10, 1, '12/1/2006', 300, 1)
this is a deposit due on December 1st for $300 for all clients in quote
10
<<The desired result given the sample.>>
let's say, for argument's sake that the view only brings back the
columns I mentioned above: Quote, billingprice and payments along with
client name
4 | 1000.00 | 250.00 | john smith
4 | 1000.00 | 150.00 | jane doe
10 | 2000.00 | 400.00 | jack jones
10 | 2000.00 | 0.00 | james james
now, what I'm looking for is this:
- Quote 4 has 2 deposits that should have been paid already (Oct 1st -
15% of their total - and Nov 1st - $100 -) and the next one is due on
Dec 1st for another $100
- Quote 10 has 1 deposit that should have been paid already (Nov 1st -
20% of their total -) and the next one is due on Dec 1st for another
$300
john smith has paid his deposits in full
jane doe has only paid $150 of $250 that she should have paid
jack jones paid his first deposit in full
james james has paid nothing
The result should be (QuoteID, billingPrice, Payments, Name,
NextDueDate, TotalDue: total due is (the total of deposits due by
NextDueDate) - (payments))
4 | 1000.00 | 250.00 | john smith | 12/1/2006 | 100.00
4 | 1000.00 | 150.00 | jane doe | 12/1/2006 | 200.00
10 | 2000.00 | 400.00 | jack jones | 12/1/2006 | 300.00
10 | 2000.00 | 0.00 | james james | 12/1/2006 | 700.00
<< Which version of SQL Server you are using.>>
SQL Server 2000
Erland Sommarskog wrote:
Quote:
Originally Posted by
J (julian.solis@.gmail.com) writes:
Quote:
Originally Posted by
This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.
I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.
So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.
>
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
>
From what you have said, it could be something like this:
>
LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate
>
Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.
>
Quote:
Originally Posted by
Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.
>
I did not cover the percentage thing, since that was just too unclear
to me how it works.
>
If you want more accurate assistance, I would suggest that you post:
>
o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Well, this is apparently solved.
There probably was simpler or more optimized way of doing it but I'm
just starting to use SQL server for tasks like this one. My solution
was this:
LEFT JOIN
(SELECT DISTINCT QuoteID,
(SELECT TOP 1 DueDate FROM datQuotationDueDates WHERE DateTypeID = 1
and DueDate GETDATE() and QuoteID = dQDD.QuoteID ORDER BY DueDate) as
NextDueDate
,(SELECT SUM(CASE WHEN (TypeID = 1 OR TypeID = 0) THEN Amount END )
FROM datQuotationDueDates WHERE DateTypeID = 1 AND QuoteID =
dQDD.QuoteID AND DueDate <= (SELECT TOP 1 DueDate FROM
datQuotationDueDates WHERE DateTypeID = 1 and DueDate GETDATE() AND
QuoteID = dQDD.QuoteID ORDER BY DueDate) ) AS Dollars
,(SELECT SUM(CASE WHEN TypeID = 2 THEN Amount END ) FROM
datQuotationDueDates WHERE DateTypeID = 1 AND QuoteID = dQDD.QuoteID
AND DueDate <= (SELECT TOP 1 DueDate FROM datQuotationDueDates WHERE
DateTypeID = 1 AND DueDate GETDATE() AND QuoteID = dQDD.QuoteID ORDER
BY DueDate) ) AS Percentages
FROM datQuotationDueDates dQDD) AS NextDD on NextDD.QuoteID =
vwClients.QuoteID
this way I bring the next due date, a column with to total of dollar
amounts and the total of percentages. Then I print out the date and use
amount and percentages along with billingprice and payments to
calculate the amount due for the next due date
I'd still be very interested in hearing how any of you would have done
it
J wrote:
Quote:
Originally Posted by
Hey there, sorry about the lack of details.
>
<< o CREATE TABLE statement for your deposits table>>
CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the
field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT NULL DEFAULT 1 --(typeid 0 or 1 means amount is a
dollar amount. typeid = 2 means amount is a percentage amount)
PRIMARY KEY(QuoteDueDateID)
)
>
<<, and a table that represents the view, including the essential
columns only.>>
The result from the view is pretty big but the important columns are
QuoteID (int) - the quote they belong to -, BillingPrice (money) -the
total they have to pay - and payments (money) - how much they've paid
so far -
>
>
<< INSERT statements with sample data.>>
INSERT INTO datQuotationDueDates VALUES (4, 1, '10/1/2006', 15, 2)
this is a deposit due on October 1st for 15% of their total billing
price for all clients in quote 4
>
INSERT INTO datQuotationDueDates VALUES (4, 1, '11/1/2006', 100, 1)
this is a deposit due on November 1st for $100 for all clients in quote
4
>
INSERT INTO datQuotationDueDates VALUES (4, 1, '12/1/2006', 100, 1)
this is a deposit due on December 1st for $100 for all clients in quote
4
>
INSERT INTO datQuotationDueDates VALUES (4, 1, '1/1/2007', 100, 1)
this is a deposit due on January 1st for $100 for all clients in quote
4
>
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/1/2006', 20, 2)
this is a deposit due on November 1st for 20% of their total for all
clients in quote 10
>
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/15/2006', 10, 2)
this is a deposit due on November 15th for 10% of their total for all
clients in quote 10
>
INSERT INTO datQuotationDueDates VALUES (10, 1, '12/1/2006', 300, 1)
this is a deposit due on December 1st for $300 for all clients in quote
10
>
<<The desired result given the sample.>>
let's say, for argument's sake that the view only brings back the
columns I mentioned above: Quote, billingprice and payments along with
client name
>
4 | 1000.00 | 250.00 | john smith
4 | 1000.00 | 150.00 | jane doe
10 | 2000.00 | 400.00 | jack jones
10 | 2000.00 | 0.00 | james james
>
now, what I'm looking for is this:
- Quote 4 has 2 deposits that should have been paid already (Oct 1st -
15% of their total - and Nov 1st - $100 -) and the next one is due on
Dec 1st for another $100
>
- Quote 10 has 1 deposit that should have been paid already (Nov 1st -
20% of their total -) and the next one is due on Dec 1st for another
$300
>
john smith has paid his deposits in full
jane doe has only paid $150 of $250 that she should have paid
jack jones paid his first deposit in full
james james has paid nothing
>
The result should be (QuoteID, billingPrice, Payments, Name,
NextDueDate, TotalDue: total due is (the total of deposits due by
NextDueDate) - (payments))
>
4 | 1000.00 | 250.00 | john smith | 12/1/2006 | 100.00
4 | 1000.00 | 150.00 | jane doe | 12/1/2006 | 200.00
10 | 2000.00 | 400.00 | jack jones | 12/1/2006 | 300.00
10 | 2000.00 | 0.00 | james james | 12/1/2006 | 700.00
>
<< Which version of SQL Server you are using.>>
SQL Server 2000
>
>
Erland Sommarskog wrote:
Quote:
Originally Posted by
J (julian.solis@.gmail.com) writes:
Quote:
Originally Posted by
This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.
>
I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.
>
So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
From what you have said, it could be something like this:
LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate
Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.
Quote:
Originally Posted by
Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.
I did not cover the percentage thing, since that was just too unclear
to me how it works.
If you want more accurate assistance, I would suggest that you post:
o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Quote:
Originally Posted by
Hey there, sorry about the lack of details.
>...
><<, and a table that represents the view, including the essential
columns only.>>
The result from the view is pretty big but the important columns are
QuoteID (int) - the quote they belong to -, BillingPrice (money) -the
total they have to pay - and payments (money) - how much they've paid
so far -
Thanks for the table and sample data. Below is my query (together with
a table I composed for the clients.) For quote 10, I got different
results that you had as the desired. Looking at the sample data, my
result seemed OK, but I may have misunderstood something about the
business rules. Whether this query performs better than yours, I don't
know. Only testing can tell.
One note about the table datQuotationDueDates: I would guess that (QuoteID, DueDate) is unique. But in such case that should be the primary key, and
the column QuoteDueDateID does not really serve any purpose.
Here is the script:
CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT NULL DEFAULT 1 --(typeid 0 or 1 means amount is a dollar amount. typeid = 2 means amount is a percentage amount)
PRIMARY KEY(QuoteDueDateID)
)
INSERT INTO datQuotationDueDates VALUES (4, 1, '10/1/2006', 15, 2)
--this is a deposit due on October 1st for 15% of their total billing price for all clients in quote 4
INSERT INTO datQuotationDueDates VALUES (4, 1, '11/1/2006', 100, 1)
-- this is a deposit due on November 1st for $100 for all clients in quote 4
INSERT INTO datQuotationDueDates VALUES (4, 1, '12/1/2006', 100, 1)
-- this is a deposit due on December 1st for $100 for all clients in quote 4
INSERT INTO datQuotationDueDates VALUES (4, 1, '1/1/2007', 100, 1)
-- this is a deposit due on January 1st for $100 for all clients in quote 4
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/1/2006', 20, 2)
-- this is a deposit due on November 1st for 20% of their total for all clients in quote 10
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/15/2006', 10, 2)
-- this is a deposit due on November 15th for 10% of their total for all clients in quote 10
INSERT INTO datQuotationDueDates VALUES (10, 1, '12/1/2006', 300, 1)
-- this is a deposit due on December 1st for $300 for all clients in quote 10
go
CREATE TABLE clients (quoteid smallint not null,
billprice float not null,
payments float not null,
name varchar(20) not null)
go
insert clients values(4, 1000.00, 250.00, 'john smith')
insert clients values(4, 1000.00 , 150.00, 'jane doe')
insert clients values(10, 2000.00, 400.00, 'jack jones')
insert clients values(10 , 2000.00, 0.00, 'james james')
go
SELECT c.quoteid, c.billprice, c.payments, c.name,
due.DueDate,
due.Amount + due.Perc * c.billprice / 100 - c.payments
FROM clients c
JOIN (SELECT a.QuoteID, DueDate = MAX(a.DueDate),
Amount = SUM(CASE TypeID WHEN 1 THEN Amount ELSE 0 END),
Perc = SUM(CASE TypeID WHEN 2 THEN Amount ELSE 0 END)
FROM datQuotationDueDates a
JOIN (SELECT QuoteID, DueDate = MIN(DueDate)
FROM datQuotationDueDates
WHERE DueDate getdate()
GROUP BY QuoteID) AS b
ON a.QuoteID = b.QuoteID
AND a.DueDate <= b.DueDate
GROUP BY a.QuoteID) AS due
ON due.QuoteID = c.quoteid
go
DROP TABLE datQuotationDueDates
drop table clients
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
help with Interactive Sort on a column...
I have been selecting the column name textbox at the top of a column
with an int value in sql server.
When I go to Interactive Sort/Sort Expression, I just put the same
expression as the one below the column name in the details "=Fields!
qtySold.Value".
The results is not what I was hoping for:
981
90
9
876
800
8
777
76
7
Instead of:
981
876
800
777
90
76
9
8
7
Any help is appreciated.
Thanks,
Trinttrint wrote:
> I have been selecting the column name textbox at the top of a column
> with an int value in sql server.
> When I go to Interactive Sort/Sort Expression, I just put the same
> expression as the one below the column name in the details "=Fields!
> qtySold.Value".
> The results is not what I was hoping for:
> 981
> 90
> 9
> 876
> 800
> 8
> 777
> 76
> 7
> Instead of:
> 981
> 876
> 800
> 777
> 90
> 76
> 9
> 8
> 7
> Any help is appreciated.
> Thanks,
> Trint
Just a guess, but it looks like it's sorting it as if it's text and not
a number. Hope I'm not pointing out the obvious. Have you tried
converting the value that you're sorting on to an integer explicitly?
James
--
with an int value in sql server.
When I go to Interactive Sort/Sort Expression, I just put the same
expression as the one below the column name in the details "=Fields!
qtySold.Value".
The results is not what I was hoping for:
981
90
9
876
800
8
777
76
7
Instead of:
981
876
800
777
90
76
9
8
7
Any help is appreciated.
Thanks,
Trinttrint wrote:
> I have been selecting the column name textbox at the top of a column
> with an int value in sql server.
> When I go to Interactive Sort/Sort Expression, I just put the same
> expression as the one below the column name in the details "=Fields!
> qtySold.Value".
> The results is not what I was hoping for:
> 981
> 90
> 9
> 876
> 800
> 8
> 777
> 76
> 7
> Instead of:
> 981
> 876
> 800
> 777
> 90
> 76
> 9
> 8
> 7
> Any help is appreciated.
> Thanks,
> Trint
Just a guess, but it looks like it's sorting it as if it's text and not
a number. Hope I'm not pointing out the obvious. Have you tried
converting the value that you're sorting on to an integer explicitly?
James
--
Help with Insert statemnt selecting from Access database
Hi, I need to select and later Update a database with information I select
from a Access 2000 database. In the following T-SQL, I want to select only
one record for each VName not already exists in my database. Since each
VName have many records in the table, I'm only looking with the record that
has the most recenst date-time, d_DateTime. I keep getting error with this
though. Appreciate it if someone can help me out here.
Thanks, AlphaOops, forgot to past the script.
INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
ORDER BY [d_RemoteName]
"Alpha" wrote:
> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select onl
y
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record tha
t
> has the most recenst date-time, d_DateTime. I keep getting error with thi
s
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Alpha,
Try:
SELECT VName, MAX(d_DateTime)
FROM yourtable
GROUP BY VName
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select
> only
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record
> that
> has the most recenst date-time, d_DateTime. I keep getting error with
> this
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Why not just use DISTINCT and remove the datetime criteria after NOT IN?
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:4ACAE646-A41C-432D-9C65-3B28C3446C6C@.microsoft.com...
> Oops, forgot to past the script.
> INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
> SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
> from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
> ORDER BY [d_RemoteName]
> "Alpha" wrote:
>|||I got error message that it doesn't like the database in "From". Do you kno
w
how to specify a tabe from Access database?
SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
GROUP BY [Data].[d_RemoteName]
Thank you,
Alpha
"Jerry Spivey" wrote:
> Alpha,
> Try:
> SELECT VName, MAX(d_DateTime)
> FROM yourtable
> GROUP BY VName
> HTH
> Jerry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
>
>|||I usually add a linked server definition for the Access database. Take a
look at sp_addlinkedserver in the SQL Server Books Online - there is an
example of how to create one there.
HTH
JErry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>I got error message that it doesn't like the database in "From". Do you
>know
> how to specify a tabe from Access database?
> SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> GROUP BY [Data].[d_RemoteName]
> Thank you,
> Alpha
> "Jerry Spivey" wrote:
>|||Is link server the only option to get the Access data? My application looks
in a directory where each day a new Access file is created with file name
inlcuding the date. I use the most recent file each time the user start my
application and needs to update my database. So you see, the link server
won't work for me because it needs to specify the file location. Unless, I
would delete the link server and create a new one each time my application
starts. But that seems odd and is there even a way to delete the
linkedserver?
Thanks, Alpha.
"Jerry Spivey" wrote:
> I usually add a linked server definition for the Access database. Take a
> look at sp_addlinkedserver in the SQL Server Books Online - there is an
> example of how to create one there.
> HTH
> JErry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>
>|||Great, that works...... Except that I don't think it's getting me the
records that I want. I need to select for oen distinct VNAME record that ha
s
the most recent d_DateTime. I think the script I have below just select a
distinct VName and then plug in the MAx DATE and then the Max Odometer which
each can come from different records with the same VNAME. Anyway to do what
I want.
Thanks a lot, Alpha
"Jerry Spivey" wrote:
> Alpha,
> Try OPENROWSET. From SQL Server BOL:
> C. Use the Microsoft OLE DB Provider for Jet
> This example accesses the orders table in the Microsoft Access Northwind
> database through the Microsoft OLE DB Provider for Jet.
>
> Note This example assumes that Access is installed.
>
> USE pubs
> GO
> SELECT a.*
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
> AS a
> GO
> HTHJerry"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:1876CCCD-5CC0-4185-85FA-A755936B7C62@.microsoft.com...
>
>
from a Access 2000 database. In the following T-SQL, I want to select only
one record for each VName not already exists in my database. Since each
VName have many records in the table, I'm only looking with the record that
has the most recenst date-time, d_DateTime. I keep getting error with this
though. Appreciate it if someone can help me out here.
Thanks, AlphaOops, forgot to past the script.
INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
ORDER BY [d_RemoteName]
"Alpha" wrote:
> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select onl
y
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record tha
t
> has the most recenst date-time, d_DateTime. I keep getting error with thi
s
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Alpha,
Try:
SELECT VName, MAX(d_DateTime)
FROM yourtable
GROUP BY VName
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select
> only
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record
> that
> has the most recenst date-time, d_DateTime. I keep getting error with
> this
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Why not just use DISTINCT and remove the datetime criteria after NOT IN?
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:4ACAE646-A41C-432D-9C65-3B28C3446C6C@.microsoft.com...
> Oops, forgot to past the script.
> INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
> SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
> from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
> ORDER BY [d_RemoteName]
> "Alpha" wrote:
>|||I got error message that it doesn't like the database in "From". Do you kno
w
how to specify a tabe from Access database?
SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
GROUP BY [Data].[d_RemoteName]
Thank you,
Alpha
"Jerry Spivey" wrote:
> Alpha,
> Try:
> SELECT VName, MAX(d_DateTime)
> FROM yourtable
> GROUP BY VName
> HTH
> Jerry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
>
>|||I usually add a linked server definition for the Access database. Take a
look at sp_addlinkedserver in the SQL Server Books Online - there is an
example of how to create one there.
HTH
JErry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>I got error message that it doesn't like the database in "From". Do you
>know
> how to specify a tabe from Access database?
> SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> GROUP BY [Data].[d_RemoteName]
> Thank you,
> Alpha
> "Jerry Spivey" wrote:
>|||Is link server the only option to get the Access data? My application looks
in a directory where each day a new Access file is created with file name
inlcuding the date. I use the most recent file each time the user start my
application and needs to update my database. So you see, the link server
won't work for me because it needs to specify the file location. Unless, I
would delete the link server and create a new one each time my application
starts. But that seems odd and is there even a way to delete the
linkedserver?
Thanks, Alpha.
"Jerry Spivey" wrote:
> I usually add a linked server definition for the Access database. Take a
> look at sp_addlinkedserver in the SQL Server Books Online - there is an
> example of how to create one there.
> HTH
> JErry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>
>|||Great, that works...... Except that I don't think it's getting me the
records that I want. I need to select for oen distinct VNAME record that ha
s
the most recent d_DateTime. I think the script I have below just select a
distinct VName and then plug in the MAx DATE and then the Max Odometer which
each can come from different records with the same VNAME. Anyway to do what
I want.
Thanks a lot, Alpha
"Jerry Spivey" wrote:
> Alpha,
> Try OPENROWSET. From SQL Server BOL:
> C. Use the Microsoft OLE DB Provider for Jet
> This example accesses the orders table in the Microsoft Access Northwind
> database through the Microsoft OLE DB Provider for Jet.
>
> Note This example assumes that Access is installed.
>
> USE pubs
> GO
> SELECT a.*
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
> AS a
> GO
> HTHJerry"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:1876CCCD-5CC0-4185-85FA-A755936B7C62@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)