Query longer in SP: March 2012

Friday, March 30, 2012

query question

I saw a query in which there was a expression
SELECT
case when credit_flag&1 = 1 then 1 else 0 end as credit_default_flag1,
case when credit_flag&2 = 2 then 1 else 0 end as credit_default_flag2,
case when credit_flag&4 = 4 then 1 else 0 end as credit_default_flag3
FROM ...
What happens when we do column & 1, column & 2
THanks
Sanjay& is the bit-wise AND operator in T-SQL. So credit_flag &
1 will return 1 on the first bit only when the first bit
from the right (of credit_flag) is set to 1. And
credit_flag & 4 will return 1 on the third bit (thus
decimal 4) only when the third bit from the right (of
credit_flag) is set to 1.
Linchi
>--Original Message--
>I saw a query in which there was a expression
>SELECT
>case when credit_flag&1 = 1 then 1 else 0 end as
credit_default_flag1,
>case when credit_flag&2 = 2 then 1 else 0 end as
credit_default_flag2,
>case when credit_flag&4 = 4 then 1 else 0 end as
credit_default_flag3
>FROM ...
>
>What happens when we do column & 1, column & 2
>THanks
>Sanjay
>.
>|||HI Linch
Do you have some white paper or document which would explain more about these bit-wise operation
I think you explained what the result would be but i am not clear how bit-wise operations work at first plac
Thanks|||The bit-wise AND operator (also OR and XOR) is supported
in most, if not all, programming languages. You can pick
up any programming tutorial book and find information on
the bit-wise operations. You can also find the info in the
SQL Server Books Online.
Linchi
>--Original Message--
>HI Linchi
>Do you have some white paper or document which would
explain more about these bit-wise operations
>I think you explained what the result would be but i am
not clear how bit-wise operations work at first place
>Thanks
>.
>

quote:

>--Original Message--
>I saw a query in which there was a expression
>SELECT
>case when credit_flag&1 = 1 then 1 else 0 end as

credit_default_flag1,

quote:
ed">
>case when credit_flag&2 = 2 then 1 else 0 end as

credit_default_flag2,

quote:
ed">
>case when credit_flag&4 = 4 then 1 else 0 end as

credit_default_flag3

quote:
d">
>FROM ...
>
>What happens when we do column & 1, column & 2
>THanks
>Sanjay
>.
>

|||HI Linchi
Do you have some white paper or document which would explain more about thes
e bit-wise operations
I think you explained what the result would be but i am not clear how bit-wi
se operations work at first place
Thanks|||The bit-wise AND operator (also OR and XOR) is supported
in most, if not all, programming languages. You can pick
up any programming tutorial book and find information on
the bit-wise operations. You can also find the info in the
SQL Server Books Online.
Linchi

quote:

>--Original Message--
>HI Linchi
>Do you have some white paper or document which would

explain more about these bit-wise operations

quote:

>I think you explained what the result would be but i am

not clear how bit-wise operations work at first place

quote:

>Thanks
>.
>

sql

Query Question

I have a question that I will illustrate with the script below. As you can
see there two tables, airlines and routes. Each airline has a customer_rank
that indicates the preferred airline of the customer ie 1 is most preferred.
Each route has a name, cost and duration.
What I would like to do is write a query that returns one row for each route
with the columns : airline, cost and duration. I would like the airline
column to be based on the preferred carrier, irrespective of the cost and
duration.
Can someone please stop my head spinning?
Thanks, Tad
CREATE TABLE [dbo].[Airlines] (
[Airline] [char] (10) PRIMARY KEY,
[Customer_Rank] [tinyint] NOT NULL
)
CREATE TABLE [dbo].[Routes] (
[ID] [int] IDENTITY (1, 1) PRIMARY KEY,
[Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
[Route] [char] (10) NOT NULL ,
[Cost] [numeric](18, 0) NOT NULL ,
[Duration] [numeric](18, 0) NOT NULL
)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('BOAC',3)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('TWA',2)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('United',1)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'A2B', 150, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('TWA', 'A2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'B2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('United', 'A2B', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('United', 'D2E', 300, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'B2D', 300, 3)first of all you want to know which airline is the preferred carrier
for each route, so you have to inner join the two tables and find the
airline with the lowest (if highest substitute min by max) rank:
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
with this query you get the the ranking of the preferred airline per
route. This output has to be inner joined with the airlines and routes
to get the desired result
select *
from airlines a,
routes r,
(
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
) p
where a.customer_rank = p.rank
and r.route = p.route
and a.airline = r.airline
if you want to avoid an inner table (the stuff between (...)p) you
could create a view, but you will learn that in your next class.|||Hi
Thanks for the DDL and Example data, showing your expected results would
also be nice. You can either use a subquery in the where clause or a derived
table such as
SELECT A.[Customer_Rank],
R.[ID], R.[Airline], R.[Route], R.[Cost], R.[Duration]
FROM [dbo].[Routes] R
JOIN [dbo].[Airlines] A ON A.[Airline] = R.[Airline]
JOIN ( SELECT MIN(E.[Customer_Rank]) AS [Customer_Rank], D.[Rout
e]
FROM [dbo].[Routes] D
JOIN [dbo].[Airlines] E ON D.[Airline] = E.[Airline]
GROUP BY D.[Route] ) F ON F.[Customer_Rank] = A.[Customer_Rank]
AND
F.[Route] = R.[Route]
ORDER BY R.[Route], A.[Customer_Rank]
John
"Tadwick" wrote:

> I have a question that I will illustrate with the script below. As you ca
n
> see there two tables, airlines and routes. Each airline has a customer_ra
nk
> that indicates the preferred airline of the customer ie 1 is most preferre
d.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each rou
te
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2D', 300, 3)|||Nite4Hawks and John,
You are both awesome. I am amazed to get responses with two different
techniques in such a short time. Are there pros and cons of the correlated
subquery vs derived table methods?
Thanks again, Tad
"Tadwick" wrote:

> I have a question that I will illustrate with the script below. As you ca
n
> see there two tables, airlines and routes. Each airline has a customer_ra
nk
> that indicates the preferred airline of the customer ie 1 is most preferre
d.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each rou
te
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2D', 300, 3)

Query question

I'm having a problem with a query where it is not doing
what I would like for it to do. I have a Call table
where helpdesk calls get logged. I then have a Severity
table that stores the severity level of the call. Each
call must have a severity of 1,2 or 3.
I want to query the database and return Call counts for
each of the severity (1,2 and 3) given a date period.
For example, in the month of January, there were a total
of 2 severity 1 calls, 25 severity 2, and 50 severity 3.
My output would look like this.
Severity CallCount
-- --
1 2
2 25
3 50
Now, for the month of February, say there were zero(0)
severity 1 calls, 30 severity 2, and 77 severity 3, I
want my output to look like this
Severity CallCount
-- --
1 0
2 30
3 77
....However, my problem is that I do not get a record
for severity 1 cuz there are no calls for that month.
My query is below. What am I doing wrong? There is no
relationship between the two tables. The Severity table
just stores detail information regarding each of the
severities such as required response time and repair time
to fix a problem based on the severity. Please help.
SELECT
Severity.Severity,
Severity.ResponseTime,
Severity.RepairTime,
Count(Call.CallNo) As CallCount
FROM
Severity
Left Join Call
On Severity.Severity = Call.Severity
WHERE
Call.DateTimeSubmitted Between '01/01/2004'
and '01/31/2004'
AND
Call.Status <> 'CANCELLED'
GROUP BY
Severity.Severity,
Severity.ResponseTime,
Severity.RepairTime,
Order By
Severity.Severity
TIA,
VicPut Your WHERE criteria into the ON clause:
SELECT S.severity, S.responsetime, S.repairtime, COUNT(C.callno) AS
callcount
FROM Severity AS S
LEFT JOIN Call AS C
ON S.Severity = C.Severity
AND C.datetimesubmitted >= '20040101'
AND C.datetimesubmitted < '20050101'
AND C.status <> 'CANCELLED'
GROUP BY S.severity, S.responsetime, S.repairtime
ORDER BY S.Severity
As above, you may want to use >= and < for the date range rather than
BETWEEN. The code you posted will exclude rows where call was submitted on
2004-12-31 but the datetimesubmitted was timed after midnight.
Hope this helps.
David Portas
SQL Server MVP
--|||REPOST: I misread the dates
Put Your WHERE criteria into the ON clause:
SELECT S.severity, S.responsetime, S.repairtime, COUNT(C.callno) AS
callcount
FROM Severity AS S
LEFT JOIN Call AS C
ON S.Severity = C.Severity
AND C.datetimesubmitted >= '20040101'
AND C.datetimesubmitted < '20040201'
AND C.status <> 'CANCELLED'
GROUP BY S.severity, S.responsetime, S.repairtime
ORDER BY S.Severity
As above, you may want to use >= and < for the date range rather than
BETWEEN. The code you posted will exclude rows where call was submitted on
2004-01-31 but the datetimesubmitted was timed after midnight.
Hope this helps.
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:91cb01c4332b$51b42fb0$a301280a@.phx.gbl...
> I'm having a problem with a query where it is not doing
> what I would like for it to do. I have a Call table
> where helpdesk calls get logged. I then have a Severity
> table that stores the severity level of the call. Each
> call must have a severity of 1,2 or 3.
> I want to query the database and return Call counts for
> each of the severity (1,2 and 3) given a date period.
> For example, in the month of January, there were a total
> of 2 severity 1 calls, 25 severity 2, and 50 severity 3.
> My output would look like this.
> Severity CallCount
> -- --
> 1 2
> 2 25
> 3 50
>
> Now, for the month of February, say there were zero(0)
> severity 1 calls, 30 severity 2, and 77 severity 3, I
> want my output to look like this
> Severity CallCount
> -- --
> 1 0
> 2 30
> 3 77
> ....However, my problem is that I do not get a record
> for severity 1 cuz there are no calls for that month.
> My query is below. What am I doing wrong? There is no
> relationship between the two tables. The Severity table
> just stores detail information regarding each of the
> severities such as required response time and repair time
> to fix a problem based on the severity. Please help.
> SELECT
> Severity.Severity,
> Severity.ResponseTime,
> Severity.RepairTime,
> Count(Call.CallNo) As CallCount
> FROM
> Severity
> Left Join Call
> On Severity.Severity = Call.Severity
> WHERE
> Call.DateTimeSubmitted Between '01/01/2004'
> and '01/31/2004'
> AND
> Call.Status <> 'CANCELLED'
> GROUP BY
> Severity.Severity,
> Severity.ResponseTime,
> Severity.RepairTime,
> Order By
> Severity.Severity
> TIA,
> Vic

Friday, March 30, 2012

query question

Query question

Query question

query question

query question

Query question

Query Question

Query Question

Query Question

Query Question

query question

Query Question

query question

query question

Query Question

Query Question

query question

Query Question

Query question

Query longer in SP

Blog Archive

About Me