Hello everyone,
I need to convert Access 2000 dbs (just tables) to SQL
2000. I am in the process of learning how to work with
tables (import,delete,append,update..etc) I am new at SQL
and like many of us, with little or none time to spend
trying to figure out the online help. So I decided to
write to you asking for help. A few basic but important
questions:
Is there a feature in SQL that allows me to graphically
manipulate tables? Just like with queries in Access...or
do I need to learn SQL language to do so?
How do I set the relations among my tables?
Where do I look for help to write DPS packages to import
csv files and update my tables?
Once I am done moving everything to SQL..is it worth it
to link this tables to Access and keep working with it as
usual?...or it would be detrimental in terms of speed?
The only reason I upgrade to SQL is because my dbs grew
too much for Access to handle and I need to build a Data
Warehouse.
I thank in advance to anybody who can answer all or any
of my questions...or maybe just guide me a little.
Gustavo
(ex-Access-wizard now SQL-dumb)
For imports/export Access > SQL Server,
look into the DTS (data transfer services) in
the manager under Tools,
For Access like Tables, Right click on your
table and "Design"
Quick relationships can be made Access-like
by putting your tables into a "Diagram" and
you can link tables together.
It is amazing how little SQL you really have
to know to use SQL server. It is almost like
Access but all grown up...
I'm sure you'll get other tips/tricks from this
message.
Bob M.
"Gustavo" <anonymous@.discussions.microsoft.com> wrote in message
news:282ed01c46396$28707780$a301280a@.phx.gbl...
> Hello everyone,
> I need to convert Access 2000 dbs (just tables) to SQL
> 2000. I am in the process of learning how to work with
> tables (import,delete,append,update..etc) I am new at SQL
> and like many of us, with little or none time to spend
> trying to figure out the online help. So I decided to
> write to you asking for help. A few basic but important
> questions:
> Is there a feature in SQL that allows me to graphically
> manipulate tables? Just like with queries in Access...or
> do I need to learn SQL language to do so?
> How do I set the relations among my tables?
> Where do I look for help to write DPS packages to import
> csv files and update my tables?
> Once I am done moving everything to SQL..is it worth it
> to link this tables to Access and keep working with it as
> usual?...or it would be detrimental in terms of speed?
> The only reason I upgrade to SQL is because my dbs grew
> too much for Access to handle and I need to build a Data
> Warehouse.
> I thank in advance to anybody who can answer all or any
> of my questions...or maybe just guide me a little.
> Gustavo
> (ex-Access-wizard now SQL-dumb)
>
|||Thanks a lot for the tips Bob. It certainly is the Access
big brother
>--Original Message--
>For imports/export Access > SQL Server,
>look into the DTS (data transfer services) in
>the manager under Tools,
>For Access like Tables, Right click on your
>table and "Design"
>Quick relationships can be made Access-like
>by putting your tables into a "Diagram" and
>you can link tables together.
>It is amazing how little SQL you really have
>to know to use SQL server. It is almost like
>Access but all grown up...
>I'm sure you'll get other tips/tricks from this
>message.
>Bob M.
>
>"Gustavo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:282ed01c46396$28707780$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
Access...or[vbcol=seagreen]
import[vbcol=seagreen]
as[vbcol=seagreen]
Data
>
>.
>
Showing posts with label design. Show all posts
Showing posts with label design. Show all posts
Friday, March 9, 2012
query design / capability
Hello, I am trying to achieve a query results with not much luck. I am struggling the query design of how to construct to get the right results.
My table has the following data
RoomID Subject StartDate Duration(min) EndDate
201 test 28/09/2007 07:00:00 180 28/09/2007 10:00:00
202 test 28/09/2007 09:00:00 240 28/09/2007 13:00:00
The best way to describe is a scheduling system with the StartDate, Duration, EndDate.
I am trying to query for datetime periods which are allocated, eg the table records show from 7am - 10am and 9am - 1pm are allocated.
When I query on StartDate >= 28/09/2007 07:00:00 AND EndDate <= 28/09/2007 13:00:00, record 201 & 202 shows which is correct because they are within query parameters and are allocated.
When I query on StartDate >= 28/09/2007 07:30:00 AND EndDate <= 28/09/2007 13:00:00, record 202 only shows which is correct because record 201 StartDate is before 07:00:00.
But my problem is I require record 201 to show because the time period between 07:00:00 and 09:00:00 is allocated and the query parameter is 07:30:00 which is still between 07:00:00 and 09:00:00.
I have researched where on similar situations, the use of duration is used, but I am unsure on its application to achieve results.
I have also read of other situations where a lookup table should be used, but again I cannot get my head around the application of alookup table to query the resuults.
Does anyone have any theories, design thoughts or real work solutions they can help me with.
Thanks
Peter Smith
What should be selected if StartDate >= 28/09/2007 06:00:00?
If both, than you can change your AND to OR and it will work...
|||This is a logic issue:
There are several logical possibilities. First, let's define some terms that will be unambiguous in meaning :
SearchStateDate and SearchEndDate = the range of time our search includes.
UsageStartDate and UsageEndDate = the range of time a room is used.
Now, one way to solve this would be to find all the records in which the search and usage dates overlap. That's hard and it hurts my head.
Or, we could try from the opposite direction, by removing all the ones that do not overlap, and keeping the rest.
Sometimes it's easier one way, sometimes it's easier the other. :)
This query returns all the rows thatDO NOT overlap:
select RoomId from RoomSchedule as usage
where usage.EndDate < @.SearchStartDate -- finished using the room before our search starts
or usage.StartDate > @.Search.EndDate -- started using the room after our search ends
Which means that this query returns all the rows thatDO overlap:
select * from RoomSchedule as overlaps
where overlaps.RoomId not in
(
select RoomId from RoomSchedule as usage
where usage.EndDate < @.SearchStartDate
or usage.StartDate > @.Search.EndDate
)
If we were using Oracle, it would be even easier, because Oracle implements a minus operator, which is like a union operator in reverse.
select * from RoomSchedule as overlaps
minus
select RoomId from RoomSchedule as usage
where usage.EndDate < @.SearchStartDate
or usage.StartDate > @.Search.EndDate
The key to using sql effectively is not to think in terms of records, but to think in terms of sets.
If you can define the problem in terms of easily determined sets of data, the sql is often very simple.
|||
DECLARE @.StartDatedatetime DECLARE @.EndDatedatetime SET @.StartDate ='2007-09-28 07:30:00.000'SET @.EndDate ='2007-09-28 13:00:00.000'SELECT RoomID, StartDate, EndDateFROM yourTable
WHERE StartDate>=CONVERT(Datetime,CONVERT(nvarchar(10),@.StartDate,121) +' ' +CASEWHENDatepart(hour,@.StartDate)<=9ANDDatepart(hour,@.StartDate)>=7THEN'07:00:00.000'ELSERIGHT(Convert(nvarchar(22),@.StartDate ,126),8)END, 121)AND EndDate <=Convert(nvarchar(22),@.EndDate ,126)|||
limno:
DECLARE @.StartDatedatetime
DECLARE @.EndDatedatetime
SET @.StartDate ='2007-09-28 07:30:00.000'
SET @.EndDate ='2007-09-28 13:00:00.000'SELECT RoomID, StartDate, EndDate
FROM yourTableWHERE StartDate>=CONVERT(Datetime,CONVERT(nvarchar(10),@.StartDate,121) +' ' +
CASEWHENDatepart(hour,@.StartDate)<=9ANDDatepart(hour,@.StartDate)>=7
THEN'07:00:00.000'
ELSERIGHT(Convert(nvarchar(22),@.StartDate ,126),8)END, 121)
AND EndDate <=Convert(nvarchar(22),@.EndDate ,126)
I do not think this is a workable solution for all data:
Here are the logical possibiliites:
SS = Search Start, SE = Search End.
RS = room start, RE = room end.
Timeline
============================================
SS============SE
RS=1=RE RS=2=RE
RS=3=RE
RS==============4============RE
RS===5===RE RS===6===RE
There are 6 logical possibilities in the timeline above (with >= and <=, more would be needed if > and < were appropriate to use instead ).
My suggested solution removes sets 1 and 2, leaving the other 4 sets (which are overlaps).
Your solution doesn't seem to identify all four of sets 3,4,5 and 6, unless I've misread it.
Plus, the hard-wiring of 7am into it makes me nervous. Seems like a bad approach that would not work with different data with different time ranges..
|||Hi David,
You are right. The code I posted will not work as a whole solution. Instead, it should be treated as the way how to handle the example OP descibed in his question. He can use this logic to handle all cases he wants to.
By no means, this is the good way to do this but just one way I did. A lot of times, I focus on the approach for dealing with questions like this and hope I can share some of my thoughts with the OP.
Thanks for your input.
|||No problem! Today is a "slow brain day" for me, it was very possible that I just didn't get it! So, it was best to ask. :)
|||Thankyou David & Limno for your guidance and subsequent discussion.
I am really struggling to get the logic sorted in my thoughts.
I will try as suggested.
Thanks
Peter
|||
Did the query I suggested return the correct results for you?|||
The solution was to include two EXCEPT clause in my query to exclude the 2 logical problems.
"WHERE (Appointment.Start BETWEEN @.Start AND @.StartEnd) OR ""(Appointment.StartEnd BETWEEN @.Start AND @.StartEnd) "
"WHERE (@.Start BETWEEN Appointment.Start AND Appointment.StartEnd) OR "
"(@.StartEnd BETWEEN Appointment.Start AND Appointment.StartEnd) "
Regards
Peter
Query design
I have the following table in MS SQL
Batch Number Name
A 1 Name 1
A 2 Name 2
A 3 Name 3
A 4 Name 4
B 5 Name 5
B 6 Name 6
B 7 Name 7
B 8 Name 8
B 9 Name 9
C 10 Name 10
C 11 Name 11
C 12 Name 12
C 13 Name 13
C 14 Name 14
I would like to run a query that returns the first name and last name by batch. I would like to use the number field to sort. When I am using the max(name) and min(name) it uses alphabetical order rather so it does not correspond with the order in the number field. The query that I am using is as follows:
select min(name) as 'first name', max(name) as 'last name'
from table
group by batchthis will get what you want --select Batch, Number, Name
from yourtable X
where Number =
( select min(Number)
from yourtable
where Batch = X.Batch )
or Number =
( select max(Number)
from yourtable
where Batch = X.Batch )
if the min and max numbers for a batch are the same, this will return only one row
if you need the names side by side on the same row, that's a different query
rudy|||There is often more than one way to skin a cat.
I use this variation of r937's example, because it makes only one nested subquery call and uses joins rather than a WHERE clause:
select Batch, Number, Name
from yourtable
inner join
(select Batch, min(Number) as MinNumber, max(Number) as MaxNumber from yourtable group by Batch) BatchExtremes
on yourtable.Number = BatchExtremes.MinNumber or yourtable.Number = BatchExtremes.MaxNumber
blindman
Batch Number Name
A 1 Name 1
A 2 Name 2
A 3 Name 3
A 4 Name 4
B 5 Name 5
B 6 Name 6
B 7 Name 7
B 8 Name 8
B 9 Name 9
C 10 Name 10
C 11 Name 11
C 12 Name 12
C 13 Name 13
C 14 Name 14
I would like to run a query that returns the first name and last name by batch. I would like to use the number field to sort. When I am using the max(name) and min(name) it uses alphabetical order rather so it does not correspond with the order in the number field. The query that I am using is as follows:
select min(name) as 'first name', max(name) as 'last name'
from table
group by batchthis will get what you want --select Batch, Number, Name
from yourtable X
where Number =
( select min(Number)
from yourtable
where Batch = X.Batch )
or Number =
( select max(Number)
from yourtable
where Batch = X.Batch )
if the min and max numbers for a batch are the same, this will return only one row
if you need the names side by side on the same row, that's a different query
rudy|||There is often more than one way to skin a cat.
I use this variation of r937's example, because it makes only one nested subquery call and uses joins rather than a WHERE clause:
select Batch, Number, Name
from yourtable
inner join
(select Batch, min(Number) as MinNumber, max(Number) as MaxNumber from yourtable group by Batch) BatchExtremes
on yourtable.Number = BatchExtremes.MinNumber or yourtable.Number = BatchExtremes.MaxNumber
blindman
Query Design
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.examnotes.net ***Please do not multi-post. See my reply in comp.databases.ms-sqlserver.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bill Bob" <nospam@.devdex.com> wrote in message
news:uMGK1tFNGHA.1032@.TK2MSFTNGP11.phx.gbl...
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.examnotes.net ***
Subscribe to:
Posts (Atom)