Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
Greg
On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg
Showing posts with label contains. Show all posts
Showing posts with label contains. Show all posts
Wednesday, March 21, 2012
Help building query
Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Gregsql
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Gregsql
Help building query
Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> >Hello,
> >
> >I need help in finding the best way to build a query for the following.
> (very big snip)
> >What I would like assistance on is how you would recommend doing this...
> >what do you believe would be the best method. I run into this type of
> >need several times a year and I really would like find a solution I can
> >use in situations as this.
> >
> >Thank you for your time and willingness to help.
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> >Hello,
> >
> >I need help in finding the best way to build a query for the following.
> (very big snip)
> >What I would like assistance on is how you would recommend doing this...
> >what do you believe would be the best method. I run into this type of
> >need several times a year and I really would like find a solution I can
> >use in situations as this.
> >
> >Thank you for your time and willingness to help.
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg
Monday, March 12, 2012
Help (question from 70-228)
Dear All,
I need your help to answer this question below:
======
You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert Lie
I believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:
> Dear All,
> I need your help to answer this question below:
> ======
> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======
> Thanks
> Robert Lie
>
I need your help to answer this question below:
======
You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert Lie
I believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:
> Dear All,
> I need your help to answer this question below:
> ======
> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======
> Thanks
> Robert Lie
>
Help (question from 70-228)
Dear All,
I need your help to answer this question below:
====== You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert LieI believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:
> Dear All,
> I need your help to answer this question below:
> ======> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======> Thanks
> Robert Lie
>
I need your help to answer this question below:
====== You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert LieI believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:
> Dear All,
> I need your help to answer this question below:
> ======> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======> Thanks
> Robert Lie
>
Help (question from 70-228)
Dear All,
I need your help to answer this question below:
======
You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert LieI believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:
> Dear All,
> I need your help to answer this question below:
> ======
> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======
> Thanks
> Robert Lie
>
I need your help to answer this question below:
======
You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert LieI believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:
> Dear All,
> I need your help to answer this question below:
> ======
> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======
> Thanks
> Robert Lie
>
HELP (!): Possible corrupted DB
Hello,
The problem accured with the database that contains critical data, so it is
very important to me to solve it as quickly as possible.
So the problem is that I cannot attach my database to the SQL server.
How all this began? At first I decided to back up my database. But by
mistake in stead of backuping I started restoring my database. After a few
seconds I realized that I was doing wrong operation, so I pressed STOP. The
restoring process seemed to be stopped, but unfortunately I couldn't do
anything with the database. Each time I tried I got an error "...restoring
is in progress..." or smth. Than I noticed that the status of the database
(in the enterprise manager) changed. The color of the database changed from
yellow to gray and the DB name became [database name] (loading). After t
hat
I decided to detach my database and re-attach it. Detaching completed
succesffuly, but now I cann't attach it. Each time when I try to attach I
got an error:
"Error 9004: An error occured while processing the log for database
'[database name]'".
(On the top of the message window: "Microsoft SQL-DMO (ODBC
SQLState:HY000)")
Attempts to attach database file without log ends with the same error.
What is wrong with my database? Is there any possibility to recover my
database? What actions should I take?
I appreciate any information.
By the way I use Enterprise manager for all operations.
Vycka
P.S. MS SQL Server 2000 (SP3), OS Win 2003Answered in .tools ... please stop multiposting.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Vycka" <Vyciunas@.One.Lt> wrote in message
news:ergHIXHNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello,
> The problem accured with the database that contains critical data, so it
is
> very important to me to solve it as quickly as possible.
> So the problem is that I cannot attach my database to the SQL server.
> How all this began? At first I decided to back up my database. But by
> mistake in stead of backuping I started restoring my database. After a few
> seconds I realized that I was doing wrong operation, so I pressed STOP.
The
> restoring process seemed to be stopped, but unfortunately I couldn't do
> anything with the database. Each time I tried I got an error "...restoring
> is in progress..." or smth. Than I noticed that the status of the database
> (in the enterprise manager) changed. The color of the database changed
from
> yellow to gray and the DB name became [database name] (loading). After
that
> I decided to detach my database and re-attach it. Detaching completed
> succesffuly, but now I cann't attach it. Each time when I try to attach I
> got an error:
> "Error 9004: An error occured while processing the log for database
> '[database name]'".
> (On the top of the message window: "Microsoft SQL-DMO (ODBC
> SQLState:HY000)")
> Attempts to attach database file without log ends with the same error.
> What is wrong with my database? Is there any possibility to recover my
> database? What actions should I take?
> I appreciate any information.
> By the way I use Enterprise manager for all operations.
> Vycka
> P.S. MS SQL Server 2000 (SP3), OS Win 2003
>
The problem accured with the database that contains critical data, so it is
very important to me to solve it as quickly as possible.
So the problem is that I cannot attach my database to the SQL server.
How all this began? At first I decided to back up my database. But by
mistake in stead of backuping I started restoring my database. After a few
seconds I realized that I was doing wrong operation, so I pressed STOP. The
restoring process seemed to be stopped, but unfortunately I couldn't do
anything with the database. Each time I tried I got an error "...restoring
is in progress..." or smth. Than I noticed that the status of the database
(in the enterprise manager) changed. The color of the database changed from
yellow to gray and the DB name became [database name] (loading). After t
hat
I decided to detach my database and re-attach it. Detaching completed
succesffuly, but now I cann't attach it. Each time when I try to attach I
got an error:
"Error 9004: An error occured while processing the log for database
'[database name]'".
(On the top of the message window: "Microsoft SQL-DMO (ODBC
SQLState:HY000)")
Attempts to attach database file without log ends with the same error.
What is wrong with my database? Is there any possibility to recover my
database? What actions should I take?
I appreciate any information.
By the way I use Enterprise manager for all operations.
Vycka
P.S. MS SQL Server 2000 (SP3), OS Win 2003Answered in .tools ... please stop multiposting.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Vycka" <Vyciunas@.One.Lt> wrote in message
news:ergHIXHNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello,
> The problem accured with the database that contains critical data, so it
is
> very important to me to solve it as quickly as possible.
> So the problem is that I cannot attach my database to the SQL server.
> How all this began? At first I decided to back up my database. But by
> mistake in stead of backuping I started restoring my database. After a few
> seconds I realized that I was doing wrong operation, so I pressed STOP.
The
> restoring process seemed to be stopped, but unfortunately I couldn't do
> anything with the database. Each time I tried I got an error "...restoring
> is in progress..." or smth. Than I noticed that the status of the database
> (in the enterprise manager) changed. The color of the database changed
from
> yellow to gray and the DB name became [database name] (loading). After
that
> I decided to detach my database and re-attach it. Detaching completed
> succesffuly, but now I cann't attach it. Each time when I try to attach I
> got an error:
> "Error 9004: An error occured while processing the log for database
> '[database name]'".
> (On the top of the message window: "Microsoft SQL-DMO (ODBC
> SQLState:HY000)")
> Attempts to attach database file without log ends with the same error.
> What is wrong with my database? Is there any possibility to recover my
> database? What actions should I take?
> I appreciate any information.
> By the way I use Enterprise manager for all operations.
> Vycka
> P.S. MS SQL Server 2000 (SP3), OS Win 2003
>
HELP (!): Possible corrupted DB
Hello,
The problem accured with the database that contains critical data, so it is
very important to me to solve it as quickly as possible.
So the problem is that I cannot attach my database to the SQL server.
How all this began? At first I decided to back up my database. But by
mistake in stead of backuping I started restoring my database. After a few
seconds I realized that I was doing wrong operation, so I pressed STOP. The
restoring process seemed to be stopped, but unfortunately I couldn't do
anything with the database. Each time I tried I got an error "...restoring
is in progress..." or smth. Than I noticed that the status of the database
(in the enterprise manager) changed. The color of the database changed from
yellow to gray and the DB name became [database name] (loading). After that
I decided to detach my database and re-attach it. Detaching completed
succesffuly, but now I cann't attach it. Each time when I try to attach I
got an error:
"Error 9004: An error occured while processing the log for database
'[database name]'".
(On the top of the message window: "Microsoft SQL-DMO (ODBC
SQLState:HY000)")
Attempts to attach database file without log ends with the same error.
What is wrong with my database? Is there any possibility to recover my
database? What actions should I take?
I appreciate any information.
By the way I use Enterprise manager for all operations.
Vycka
P.S. MS SQL Server 2000 (SP3), OS Win 2003
Answered in .tools ... please stop multiposting.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Vycka" <Vyciunas@.One.Lt> wrote in message
news:ergHIXHNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello,
> The problem accured with the database that contains critical data, so it
is
> very important to me to solve it as quickly as possible.
> So the problem is that I cannot attach my database to the SQL server.
> How all this began? At first I decided to back up my database. But by
> mistake in stead of backuping I started restoring my database. After a few
> seconds I realized that I was doing wrong operation, so I pressed STOP.
The
> restoring process seemed to be stopped, but unfortunately I couldn't do
> anything with the database. Each time I tried I got an error "...restoring
> is in progress..." or smth. Than I noticed that the status of the database
> (in the enterprise manager) changed. The color of the database changed
from
> yellow to gray and the DB name became [database name] (loading). After
that
> I decided to detach my database and re-attach it. Detaching completed
> succesffuly, but now I cann't attach it. Each time when I try to attach I
> got an error:
> "Error 9004: An error occured while processing the log for database
> '[database name]'".
> (On the top of the message window: "Microsoft SQL-DMO (ODBC
> SQLState:HY000)")
> Attempts to attach database file without log ends with the same error.
> What is wrong with my database? Is there any possibility to recover my
> database? What actions should I take?
> I appreciate any information.
> By the way I use Enterprise manager for all operations.
> Vycka
> P.S. MS SQL Server 2000 (SP3), OS Win 2003
>
The problem accured with the database that contains critical data, so it is
very important to me to solve it as quickly as possible.
So the problem is that I cannot attach my database to the SQL server.
How all this began? At first I decided to back up my database. But by
mistake in stead of backuping I started restoring my database. After a few
seconds I realized that I was doing wrong operation, so I pressed STOP. The
restoring process seemed to be stopped, but unfortunately I couldn't do
anything with the database. Each time I tried I got an error "...restoring
is in progress..." or smth. Than I noticed that the status of the database
(in the enterprise manager) changed. The color of the database changed from
yellow to gray and the DB name became [database name] (loading). After that
I decided to detach my database and re-attach it. Detaching completed
succesffuly, but now I cann't attach it. Each time when I try to attach I
got an error:
"Error 9004: An error occured while processing the log for database
'[database name]'".
(On the top of the message window: "Microsoft SQL-DMO (ODBC
SQLState:HY000)")
Attempts to attach database file without log ends with the same error.
What is wrong with my database? Is there any possibility to recover my
database? What actions should I take?
I appreciate any information.
By the way I use Enterprise manager for all operations.
Vycka
P.S. MS SQL Server 2000 (SP3), OS Win 2003
Answered in .tools ... please stop multiposting.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Vycka" <Vyciunas@.One.Lt> wrote in message
news:ergHIXHNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello,
> The problem accured with the database that contains critical data, so it
is
> very important to me to solve it as quickly as possible.
> So the problem is that I cannot attach my database to the SQL server.
> How all this began? At first I decided to back up my database. But by
> mistake in stead of backuping I started restoring my database. After a few
> seconds I realized that I was doing wrong operation, so I pressed STOP.
The
> restoring process seemed to be stopped, but unfortunately I couldn't do
> anything with the database. Each time I tried I got an error "...restoring
> is in progress..." or smth. Than I noticed that the status of the database
> (in the enterprise manager) changed. The color of the database changed
from
> yellow to gray and the DB name became [database name] (loading). After
that
> I decided to detach my database and re-attach it. Detaching completed
> succesffuly, but now I cann't attach it. Each time when I try to attach I
> got an error:
> "Error 9004: An error occured while processing the log for database
> '[database name]'".
> (On the top of the message window: "Microsoft SQL-DMO (ODBC
> SQLState:HY000)")
> Attempts to attach database file without log ends with the same error.
> What is wrong with my database? Is there any possibility to recover my
> database? What actions should I take?
> I appreciate any information.
> By the way I use Enterprise manager for all operations.
> Vycka
> P.S. MS SQL Server 2000 (SP3), OS Win 2003
>
HELP (!): Possible corrupted DB
Hello,
The problem accured with the database that contains critical data, so it is
very important to me to solve it as quickly as possible.
So the problem is that I cannot attach my database to the SQL server.
How all this began? At first I decided to back up my database. But by
mistake in stead of backuping I started restoring my database. After a few
seconds I realized that I was doing wrong operation, so I pressed STOP. The
restoring process seemed to be stopped, but unfortunately I couldn't do
anything with the database. Each time I tried I got an error "...restoring
is in progress..." or smth. Than I noticed that the status of the database
(in the enterprise manager) changed. The color of the database changed from
yellow to gray and the DB name became [database name] (loading). After that
I decided to detach my database and re-attach it. Detaching completed
succesffuly, but now I cann't attach it. Each time when I try to attach I
got an error:
"Error 9004: An error occured while processing the log for database
'[database name]'".
(On the top of the message window: "Microsoft SQL-DMO (ODBC
SQLState:HY000)")
Attempts to attach database file without log ends with the same error.
What is wrong with my database? Is there any possibility to recover my
database? What actions should I take?
I appreciate any information.
By the way I use Enterprise manager for all operations.
Vycka
P.S. MS SQL Server 2000 (SP3), OS Win 2003Answered in .tools ... please stop multiposting.
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Vycka" <Vyciunas@.One.Lt> wrote in message
news:ergHIXHNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello,
> The problem accured with the database that contains critical data, so it
is
> very important to me to solve it as quickly as possible.
> So the problem is that I cannot attach my database to the SQL server.
> How all this began? At first I decided to back up my database. But by
> mistake in stead of backuping I started restoring my database. After a few
> seconds I realized that I was doing wrong operation, so I pressed STOP.
The
> restoring process seemed to be stopped, but unfortunately I couldn't do
> anything with the database. Each time I tried I got an error "...restoring
> is in progress..." or smth. Than I noticed that the status of the database
> (in the enterprise manager) changed. The color of the database changed
from
> yellow to gray and the DB name became [database name] (loading). After
that
> I decided to detach my database and re-attach it. Detaching completed
> succesffuly, but now I cann't attach it. Each time when I try to attach I
> got an error:
> "Error 9004: An error occured while processing the log for database
> '[database name]'".
> (On the top of the message window: "Microsoft SQL-DMO (ODBC
> SQLState:HY000)")
> Attempts to attach database file without log ends with the same error.
> What is wrong with my database? Is there any possibility to recover my
> database? What actions should I take?
> I appreciate any information.
> By the way I use Enterprise manager for all operations.
> Vycka
> P.S. MS SQL Server 2000 (SP3), OS Win 2003
>
The problem accured with the database that contains critical data, so it is
very important to me to solve it as quickly as possible.
So the problem is that I cannot attach my database to the SQL server.
How all this began? At first I decided to back up my database. But by
mistake in stead of backuping I started restoring my database. After a few
seconds I realized that I was doing wrong operation, so I pressed STOP. The
restoring process seemed to be stopped, but unfortunately I couldn't do
anything with the database. Each time I tried I got an error "...restoring
is in progress..." or smth. Than I noticed that the status of the database
(in the enterprise manager) changed. The color of the database changed from
yellow to gray and the DB name became [database name] (loading). After that
I decided to detach my database and re-attach it. Detaching completed
succesffuly, but now I cann't attach it. Each time when I try to attach I
got an error:
"Error 9004: An error occured while processing the log for database
'[database name]'".
(On the top of the message window: "Microsoft SQL-DMO (ODBC
SQLState:HY000)")
Attempts to attach database file without log ends with the same error.
What is wrong with my database? Is there any possibility to recover my
database? What actions should I take?
I appreciate any information.
By the way I use Enterprise manager for all operations.
Vycka
P.S. MS SQL Server 2000 (SP3), OS Win 2003Answered in .tools ... please stop multiposting.
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Vycka" <Vyciunas@.One.Lt> wrote in message
news:ergHIXHNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello,
> The problem accured with the database that contains critical data, so it
is
> very important to me to solve it as quickly as possible.
> So the problem is that I cannot attach my database to the SQL server.
> How all this began? At first I decided to back up my database. But by
> mistake in stead of backuping I started restoring my database. After a few
> seconds I realized that I was doing wrong operation, so I pressed STOP.
The
> restoring process seemed to be stopped, but unfortunately I couldn't do
> anything with the database. Each time I tried I got an error "...restoring
> is in progress..." or smth. Than I noticed that the status of the database
> (in the enterprise manager) changed. The color of the database changed
from
> yellow to gray and the DB name became [database name] (loading). After
that
> I decided to detach my database and re-attach it. Detaching completed
> succesffuly, but now I cann't attach it. Each time when I try to attach I
> got an error:
> "Error 9004: An error occured while processing the log for database
> '[database name]'".
> (On the top of the message window: "Microsoft SQL-DMO (ODBC
> SQLState:HY000)")
> Attempts to attach database file without log ends with the same error.
> What is wrong with my database? Is there any possibility to recover my
> database? What actions should I take?
> I appreciate any information.
> By the way I use Enterprise manager for all operations.
> Vycka
> P.S. MS SQL Server 2000 (SP3), OS Win 2003
>
Monday, February 27, 2012
Help - Contiguous Date Range Query
I have a table that contains (among other things) start and end range datetimes. The ranges can overlap. View these datetimes as segments of coverage on a number line. Rather than returning each line segment individually, I'd like the query to return the start and the end of the contiguous segments. For example, the desired output of the query on the below table would be:
/* Start SQL */
/* Desired output: range_start_datetime range_end_datetime
01/01/2003 01/06/2003
01/08/2003 01/12/2003
*/
create table #ranges (range_start_datetime datetime, range_end_datetime datetime)
/* Range 1: 01/01/2003 to 01/06/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/01/2003', '01/02/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/03/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/06/2003')
/* Gap - no data at 01/07/2003 */
/* Range 2: 01/08/2003 to 01/12/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/08/2003', '01/09/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/09/2003', '01/10/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/10/2003', '01/12/2003')
select * from #ranges
drop table #ranges
/* End SQL */This version will work in Oracle at least:
SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );
RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||This works in Sybase as well. I had to change the first select to distinct when I added a few more test cases, but it works! I'm impressed with your skills.
If you get a moment, could you explain what your query is doing? I didn't even consider a 4-join solution.
Gump
Originally posted by andrewst
This version will work in Oracle at least:
SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );
RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||Probably easiest to start from here:
select range_start_datetime,
from ranges r1
where not exists
( select range_end_datetime from ranges r2
where r2.range_end_datetime >= r1.range_start_datetime
and r2.range_start_datetime < r1.range_start_datetime
);
All I have done is remove the second column from the main select (which was a "scalar subquery").
This query gets the start of each contiguous range, by finding all the start dates for which there does not exist a preceding record that meets or overlaps it, i.e.:
RANGE_STAR
----
01/01/2003
01/08/2003
Now let's look at the scalar subquery:
select min(range_end_datetime)
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)
and r3.range_end_datetime >= r1.range_start_datetime
This sort of does the opposite of the query above: it finds the end of each contiguous range, i.e. all the end dates for which there does not exist a following record that meets or overlaps it, i.e.
select range_end_datetime
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)
RANGE_END_
----
01/06/2003
01/12/2003
It then finds the MIN of those end dates where the end_date is on or after the start date of each record in the main query (i.e. corellated): for start date 01/01/2003 that will be 01/06/2003, and for start date 01/08/2003 that will be 01/12/2003.
Does that make sense?
/* Start SQL */
/* Desired output: range_start_datetime range_end_datetime
01/01/2003 01/06/2003
01/08/2003 01/12/2003
*/
create table #ranges (range_start_datetime datetime, range_end_datetime datetime)
/* Range 1: 01/01/2003 to 01/06/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/01/2003', '01/02/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/03/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/06/2003')
/* Gap - no data at 01/07/2003 */
/* Range 2: 01/08/2003 to 01/12/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/08/2003', '01/09/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/09/2003', '01/10/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/10/2003', '01/12/2003')
select * from #ranges
drop table #ranges
/* End SQL */This version will work in Oracle at least:
SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );
RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||This works in Sybase as well. I had to change the first select to distinct when I added a few more test cases, but it works! I'm impressed with your skills.
If you get a moment, could you explain what your query is doing? I didn't even consider a 4-join solution.
Gump
Originally posted by andrewst
This version will work in Oracle at least:
SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );
RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||Probably easiest to start from here:
select range_start_datetime,
from ranges r1
where not exists
( select range_end_datetime from ranges r2
where r2.range_end_datetime >= r1.range_start_datetime
and r2.range_start_datetime < r1.range_start_datetime
);
All I have done is remove the second column from the main select (which was a "scalar subquery").
This query gets the start of each contiguous range, by finding all the start dates for which there does not exist a preceding record that meets or overlaps it, i.e.:
RANGE_STAR
----
01/01/2003
01/08/2003
Now let's look at the scalar subquery:
select min(range_end_datetime)
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)
and r3.range_end_datetime >= r1.range_start_datetime
This sort of does the opposite of the query above: it finds the end of each contiguous range, i.e. all the end dates for which there does not exist a following record that meets or overlaps it, i.e.
select range_end_datetime
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)
RANGE_END_
----
01/06/2003
01/12/2003
It then finds the MIN of those end dates where the end_date is on or after the start date of each record in the main query (i.e. corellated): for start date 01/01/2003 that will be 01/06/2003, and for start date 01/08/2003 that will be 01/12/2003.
Does that make sense?
Subscribe to:
Posts (Atom)