Calling Webservice: orders

Showing posts with label orders. Show all posts

Tuesday, March 27, 2012

Can I have an SQL view like in Access

If you open the Northwind.mdb in access and look at tables Orders &
Employees you'll see that both contain "Employee ID", but when you view the
Orders table you don't see the Employee ID you see the associated Employee's
name. Can this be done in SQL 2005? When I view the Orders table I see a
whole list of Employee ID which makes it hard to know which employee is
associated to the Employee ID. I have a real world application but I'm only
use Northwind as an example as everyone has this database.I don't use SQL 2005 but I am sure you can by creating a View combining
tables Orders and Employees with the EmployeeID as the linking Field.
--
HTH
Van T. Dinh
MVP (Access)
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||Hi.
> Can this be done in SQL 2005?
Not on the table itself. And you don't want to, either. It causes huge
problems. For more information, please see the article, "The Evils of
Lookup Fields in Tables," on the following Web page:
http://www.mvps.org/access/lookupfields.htm
> When I view the Orders table I see a whole list of Employee ID which makes
> it hard to know which employee is associated to the Employee ID.
You shouldn't be viewing the raw data in the tables. That's where the data
is stored, but to glean meaningful information from the data, you need to
use queries, forms or reports. In a query, you can join the two tables and
select the employee's first name and last name to display for each record,
not the EmployeeID. Create a similar view in SQL Server.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||I can see the "evils" of a lookup field in a table. I've never created a
view in SQL 2005 before... do you have a site that I could reference that
would give me a 101 on creating SQL 2005 views?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
> Hi.
>> Can this be done in SQL 2005?
> Not on the table itself. And you don't want to, either. It causes huge
> problems. For more information, please see the article, "The Evils of
> Lookup Fields in Tables," on the following Web page:
> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
> You shouldn't be viewing the raw data in the tables. That's where the
> data is stored, but to glean meaningful information from the data, you
> need to use queries, forms or reports. In a query, you can join the two
> tables and select the employee's first name and last name to display for
> each record, not the EmployeeID. Create a similar view in SQL Server.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know which
>> employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has this
>> database.
>|||A view is very simple to create. The basic syntax is
CREATE VIEW <viewname>
AS <select statement>
See these Books Online topics for the 101 information you need.
http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>I can see the "evils" of a lookup field in a table. I've never created a
>view in SQL 2005 before... do you have a site that I could reference that
>would give me a 101 on creating SQL 2005 views?
> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>|||One may also download the latest edition of SQL Server 2005 Books Online
(BOL), which also replaces previous editions of the SQL Server 2005 Express
BOL. Please see the following Web page for the download:
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>A view is very simple to create. The basic syntax is
> CREATE VIEW <viewname>
> AS <select statement>
> See these Books Online topics for the 101 information you need.
> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>>
>|||Since users shouldn't be viewing tables or even queries, you can simply base
your forms on queries that join the necessary tables. Forms should ALWAYS
be based on queries with selection criteria when the back end is not Jet.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||sure, you can easily write a select statement to do this.
in the select statemetn are the columns you want to "see". in the
where section are the data rules that tell the engine how to get to
that data.
Absolutely, for sure, you do NOT have to have the "where" stuff in the
upper "select" stuff.|||I got the view working nicely, but I can't update any of the data from the
view. Is there a way around this or this is how it should be working?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
> One may also download the latest edition of SQL Server 2005 Books Online
> (BOL), which also replaces previous editions of the SQL Server 2005
> Express BOL. Please see the following Web page for the download:
> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>|||It depends on if it's an updatable view or not. The
conditions on when it's updatable or not are listed in books
online in the Create View topic. There is a section titled
Updatable Views.
-Sue
On Fri, 12 May 2006 13:15:39 -0400, "M.Siler"
<John.Doe@.NoSpam.com> wrote:
>I got the view working nicely, but I can't update any of the data from the
>view. Is there a way around this or this is how it should be working?
>
>"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
>message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>> One may also download the latest edition of SQL Server 2005 Books Online
>> (BOL), which also replaces previous editions of the SQL Server 2005
>> Express BOL. Please see the following Web page for the download:
>> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
>> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>>
>

Wednesday, March 7, 2012

Can CASE match more than once ?

Hi
I'm tring to write a statement to analyse what orders were open on the first
day of each month from a system and return one data set with the months
listed and all the orders open during that month. eg.
Mon Order_No
Jan 001
Jan 002
Jan 003
Feb 002
Feb 003
Feb 004
The orders all have an open and closed date, so I want to check for each
month if the first of the month falls between the open and closed date of
each order.
I've set up a dummy database for testing - what I'd like to know is whether
CASE statements be made to match more than once :
SELECT MyNewField =
CASE
WHEN data1 = 1 THEN 'Is One'
WHEN data1 > 1 then 'Not One'
end,
data2
FROM APW_Test
my table is as follows
data1
1
2
3
4
So I would hope to see one result for the number 1 (Is One) and two results
for the remaining numbers because they match both case statements. However,
CASE seems to match the first statement and then stop for each record.
Is there a way I can achieve the result I want fairly simply ?
Thanks in advance.
AndrewHi ... I made a mistake in my logic. What I meant was

> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 0 then 'Greater Than Zero'
so I expect 1 to appear twice. All else the same.
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||Andrew
you can simply use procedure/function to use if condition.
However post DDL,Sample data to help you better
Regards
R.D
"Andrew Webb" wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
>
> "Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
> news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>|||On Fri, 16 Sep 2005 08:39:06 +0100, "Andrew Webb"
<andrew.webb@.eme-med.co.uk> wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
CASE returns only the first match.|||Andrew,
It sounds to me like you need a JOIN operation, not a CASE expression.
Post the CREATE TABLE and INSERT statements for some specific
data if you want a more careful answer, but this might be close:
select data1, DisplayAnswer
from T join (
select 'equal 1' as TestCondition, 'Is One' as DisplayAnswer
union all
select 'above 1', 'Not One'
) C
on (
TestCondition = 'equal 1' and data1 = 1
) or (
TestCondition = 'above 1' and data1 > 1
)
If you select only from your 4-row table, with no other table
joined in, you cannot obtain a result that contains any row
more than once.
Steve Kass
Drew University
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||On Fri, 16 Sep 2005 08:30:00 +0100, Andrew Webb wrote:

>Hi
>I'm tring to write a statement to analyse what orders were open on the firs
t
>day of each month from a system and return one data set with the months
>listed and all the orders open during that month. eg.
>Mon Order_No
>Jan 001
>Jan 002
>Jan 003
>Feb 002
>Feb 003
>Feb 004
>The orders all have an open and closed date, so I want to check for each
>month if the first of the month falls between the open and closed date of
>each order.
Hi Andrew,
You could use a calendar table or a table of integers to get this. I'll
give an example with a table of integers that's made up "on the fly".
You can expand it as needed, or make a real table of integers (as
explained on http://www.aspfaq.com/show.asp?id=2516).
DECLARE @.StartDate smalldatetime
,@.EndDate smalldatetime
SET @.StartDate = '20050101' -- Should be first of the month
SET @.EndDate = '20051201'
SELECT DATEADD(month, Numbers.n, @.StartDate) AS Mon,
Orders.Order_No
FROM Orders
INNER JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15) AS Numbers(n)
WHERE Orders.OpenDate < DATEADD(month, Numbers.n, @.StartDate)
AND Orders.CloseDate > DATEADD(month, Numbers.n, @.StartDate)
AND DATEADD(month, Numbers.n, @.StartDate) <= @.EndDate
ORDER BY Numbers.n, Orders.Order_No
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Tuesday, March 27, 2012

Can I have an SQL view like in Access

Can I have an SQL view like in Access

Wednesday, March 7, 2012

Can CASE match more than once ?

Calling Webservice

Blog Archive

About Me