Hello, I have a table that lists my bank transactions. I have a uniqueid in
the first field and several other fields...one of which is DATE... now, I
want to run a query where my view is based upon the DATE. however I want to
only show transactions that are the same date as the system date (this is to
see how much work my employees have done). Please see my example below, this
code works...
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE ([date] = '9/9/2007')
However, where it has 9/9/2007, I want it to be TODAYS Date
Thanks for your help in advance!WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
"SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> Hello, I have a table that lists my bank transactions. I have a uniqueid
> in
> the first field and several other fields...one of which is DATE... now,
> I
> want to run a query where my view is based upon the DATE. however I want
> to
> only show transactions that are the same date as the system date (this is
> to
> see how much work my employees have done). Please see my example below,
> this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Try:
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE
[date] >= convert(char(8), getdate(), 112) and
and [date] < dateadd(day, 1, convert(char(8), getdate(), 112))
go
AMB
"SQL Brad" wrote:
> Hello, I have a table that lists my bank transactions. I have a uniqueid in
> the first field and several other fields...one of which is DATE... now, I
> want to run a query where my view is based upon the DATE. however I want to
> only show transactions that are the same date as the system date (this is to
> see how much work my employees have done). Please see my example below, this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Aaron...thanks for your help, it worked perfectly!! I also changed the 0 to
a 1 and it went to yesterday....very much appreciated!
"Aaron Bertrand [SQL Server MVP]" wrote:
> WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
>
> "SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
> news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> > Hello, I have a table that lists my bank transactions. I have a uniqueid
> > in
> > the first field and several other fields...one of which is DATE... now,
> > I
> > want to run a query where my view is based upon the DATE. however I want
> > to
> > only show transactions that are the same date as the system date (this is
> > to
> > see how much work my employees have done). Please see my example below,
> > this
> > code works...
> >
> > SELECT transid, [date], description, amt, taxamt
> > FROM dbo.taxtransactions
> > WHERE ([date] = '9/9/2007')
> >
> > However, where it has 9/9/2007, I want it to be TODAYS Date
> >
> > Thanks for your help in advance!
> >
>
>sql
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Thursday, March 29, 2012
Help with crosstab (was "Query Help Needed!")
Hey,
i have a table which has the foll data:
employeecode Amount AmountDescription
1 100 x
2 200 y
3 150 x
4 300 z
now i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
any suggestions???
platform: SQL Server 2000
thanx!sorry, no suggestions.... unless we know
1) why do u need it (the practical scenario)
2) how do u ensure that the string "x" fits a column name
3) how do u ensure that the number of columns is within the max limit for select/table
4) what would be the value against row 1, col x of the output|||I think this will do what you want.
SELECT empcode
, Max(CASE WHEN AmountDescription = 'x' THEN amount ELSE 0 END) As 'x'
, Max(CASE WHEN AmountDescription = 'y' THEN amount ELSE 0 END) As 'y'
, Max(CASE WHEN AmountDescription = 'z' THEN amount ELSE 0 END) As 'z'
FROM MyTable
GROUP BY empcode|||no, george, that will put 0s where they didn't exist in the data|||just tweak that last response to use null values instead of 0's
select empcode, case when AmountDescription = 'x' then amount else null end as X,
Case when AmountDescription = 'y' then amount else null end as Y,
Case when AmountDescription = 'z' then amount else null end as Z
from MyTable
Group by Empcode|||but don't lose your MAXes ;)|||Max means that it becomes aggregated and doesn't have to be used in the GROUP BY clause ;)|||i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
SELECT empcode
, Amount
, x = NULL
, y = NULL
, z = NULL
FROM MyTable
:)|||<sigh />
pootle, please forgive the lack of proper spacing in the original post
this is what was intended (and you can see this if you open up the original post in Edit) --
empcode x y z
1 100
2 200
3 150
4 300sql
i have a table which has the foll data:
employeecode Amount AmountDescription
1 100 x
2 200 y
3 150 x
4 300 z
now i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
any suggestions???
platform: SQL Server 2000
thanx!sorry, no suggestions.... unless we know
1) why do u need it (the practical scenario)
2) how do u ensure that the string "x" fits a column name
3) how do u ensure that the number of columns is within the max limit for select/table
4) what would be the value against row 1, col x of the output|||I think this will do what you want.
SELECT empcode
, Max(CASE WHEN AmountDescription = 'x' THEN amount ELSE 0 END) As 'x'
, Max(CASE WHEN AmountDescription = 'y' THEN amount ELSE 0 END) As 'y'
, Max(CASE WHEN AmountDescription = 'z' THEN amount ELSE 0 END) As 'z'
FROM MyTable
GROUP BY empcode|||no, george, that will put 0s where they didn't exist in the data|||just tweak that last response to use null values instead of 0's
select empcode, case when AmountDescription = 'x' then amount else null end as X,
Case when AmountDescription = 'y' then amount else null end as Y,
Case when AmountDescription = 'z' then amount else null end as Z
from MyTable
Group by Empcode|||but don't lose your MAXes ;)|||Max means that it becomes aggregated and doesn't have to be used in the GROUP BY clause ;)|||i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
SELECT empcode
, Amount
, x = NULL
, y = NULL
, z = NULL
FROM MyTable
:)|||<sigh />
pootle, please forgive the lack of proper spacing in the original post
this is what was intended (and you can see this if you open up the original post in Edit) --
empcode x y z
1 100
2 200
3 150
4 300sql
Help with counting query
This is a counting issue following an inner join.
I have two tables (simplifying it for clarity)
Offering Table: OfferingID, year
Registration Table: RegistrationID, OfferingID, Registration_Status
-- Start Table Definitions
--
CREATE TABLE [dbo].[Test1] (
[OfferingID] [int] NOT NULL ,
[Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[test2] (
[RegistrationID] [int]NOT NULL ,
[OfferingID] [int] NOT NULL ,
[Registration_Status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Test1 Values (1,2005)
INSERT INTO Test1 Values (2,2005)
INSERT INTO Test1 Values (3,2006)
INSERT INTO Test2 Values (1,1,1)
INSERT INTO Test2 Values (2,1,1)
INSERT INTO Test2 Values (3,1,2)
INSERT INTO Test2 Values (4,1,2)
INSERT INTO Test2 Values (5,1,2)
INSERT INTO Test2 Values (6,2,1)
INSERT INTO Test2 Values (7,2,1)
INSERT INTO Test2 Values (8,3,1)
INSERT INTO Test2 Values (9,3,2)
-- END Table Definitions
--
Now I want to do some counting, basically I want to count the number of
offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
I also want to count the total number of registrations of
Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 and
4
respectivly)
This issue is I want them all in a single record. I came up with this SQL
Query
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Returns:
Attended Cancelled 2005 2006
-- -- -- --
5 4 7 2
It returns the correct Registration Status counts, but not the correct
number of offerings per year because the records are counted over and over
due to the join. What I really want is to count only distinct records in
test1.
Attended Cancelled 2005 2006
-- -- -- --
5 4 2 1
Thanks!You need to use COUNT(distinct).
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
THEN t1.OfferingID ELSE NULL END) AS [2005],
COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
THEN t1.OfferingID ELSE NULL END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Note that the ELSE NULL is optional; if there is no ELSE clause the
CASE defaults to NULL when not matched. But it is a bit clearer with
the explicit assignment. COUNT does not count NULLs.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 11:05:02 -0700, Ramez
<Ramez@.discussions.microsoft.com> wrote:
>Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
>Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
>Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005]
,
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
>FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID|||Actually you don't need a join.
try this
select * from
(Select SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled from test2) as t2,
(select SUM(CASE WHEN Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
from test1) t1
Hope this helps.
--
"Ramez" wrote:
> This is a counting issue following an inner join.
> I have two tables (simplifying it for clarity)
> Offering Table: OfferingID, year
> Registration Table: RegistrationID, OfferingID, Registration_Status
> -- Start Table Definitions
> --
> CREATE TABLE [dbo].[Test1] (
> [OfferingID] [int] NOT NULL ,
> [Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[test2] (
> [RegistrationID] [int]NOT NULL ,
> [OfferingID] [int] NOT NULL ,
> [Registration_Status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Test1 Values (1,2005)
> INSERT INTO Test1 Values (2,2005)
> INSERT INTO Test1 Values (3,2006)
> INSERT INTO Test2 Values (1,1,1)
> INSERT INTO Test2 Values (2,1,1)
> INSERT INTO Test2 Values (3,1,2)
> INSERT INTO Test2 Values (4,1,2)
> INSERT INTO Test2 Values (5,1,2)
> INSERT INTO Test2 Values (6,2,1)
> INSERT INTO Test2 Values (7,2,1)
> INSERT INTO Test2 Values (8,3,1)
> INSERT INTO Test2 Values (9,3,2)
> -- END Table Definitions
> --
> Now I want to do some counting, basically I want to count the number of
> offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
> I also want to count the total number of registrations of
> Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 an
d 4
> respectivly)
> This issue is I want them all in a single record. I came up with this SQL
> Query
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005
],
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006
]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Returns:
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 7 2
> It returns the correct Registration Status counts, but not the correct
> number of offerings per year because the records are counted over and over
> due to the join. What I really want is to count only distinct records in
> test1.
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 2 1
> Thanks!|||Hello, Ramez
To get the desired result, you can simply use something like this:
SELECT
(SELECT COUNT(*) FROM test2 WHERE Registration_status=1) AS Attended,
(SELECT COUNT(*) FROM test2 WHERE Registration_status=2) AS Cancelled,
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2005) AS [2005],
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2006) AS [2006]
If you really want to use a join (but I don't see any good reason for
this), you can use the following query:
SELECT
SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS Cancelled,
COUNT(DISTINCT CASE WHEN Offering_Year = '2005' THEN t1.OfferingID
END) AS [2005],
COUNT(DISTINCT CASE WHEN Offering_Year = '2006' THEN t1.OfferingID
END) AS [2006]
FROM test1 t1 INNER JOIN test2 t2 ON t1.OfferingId = t2.OfferingID
However, this comes at the expense of a warning: "Warning: Null value
is eliminated by an aggregate or other SET operation."; the warning can
be eliminated by using SET ANSI_WARNINGS OFF, but this is not
recommended (one of the reasons is because ANSI_WARNINGS is required to
be ON for using indexes on computed columns and indexed views).
Razvan|||Roy,
Do we need the join and case and distinct ops for this scenario. I
seriously doubt it.
--
"Roy Harvey" wrote:
> You need to use COUNT(distinct).
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
> THEN t1.OfferingID ELSE NULL END) AS [2005],
> COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
> THEN t1.OfferingID ELSE NULL END) AS [2006]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Note that the ELSE NULL is optional; if there is no ELSE clause the
> CASE defaults to NULL when not matched. But it is a bit clearer with
> the explicit assignment. COUNT does not count NULLs.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 11:05:02 -0700, Ramez
> <Ramez@.discussions.microsoft.com> wrote:
>
>
I have two tables (simplifying it for clarity)
Offering Table: OfferingID, year
Registration Table: RegistrationID, OfferingID, Registration_Status
-- Start Table Definitions
--
CREATE TABLE [dbo].[Test1] (
[OfferingID] [int] NOT NULL ,
[Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[test2] (
[RegistrationID] [int]NOT NULL ,
[OfferingID] [int] NOT NULL ,
[Registration_Status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Test1 Values (1,2005)
INSERT INTO Test1 Values (2,2005)
INSERT INTO Test1 Values (3,2006)
INSERT INTO Test2 Values (1,1,1)
INSERT INTO Test2 Values (2,1,1)
INSERT INTO Test2 Values (3,1,2)
INSERT INTO Test2 Values (4,1,2)
INSERT INTO Test2 Values (5,1,2)
INSERT INTO Test2 Values (6,2,1)
INSERT INTO Test2 Values (7,2,1)
INSERT INTO Test2 Values (8,3,1)
INSERT INTO Test2 Values (9,3,2)
-- END Table Definitions
--
Now I want to do some counting, basically I want to count the number of
offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
I also want to count the total number of registrations of
Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 and
4
respectivly)
This issue is I want them all in a single record. I came up with this SQL
Query
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Returns:
Attended Cancelled 2005 2006
-- -- -- --
5 4 7 2
It returns the correct Registration Status counts, but not the correct
number of offerings per year because the records are counted over and over
due to the join. What I really want is to count only distinct records in
test1.
Attended Cancelled 2005 2006
-- -- -- --
5 4 2 1
Thanks!You need to use COUNT(distinct).
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
THEN t1.OfferingID ELSE NULL END) AS [2005],
COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
THEN t1.OfferingID ELSE NULL END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Note that the ELSE NULL is optional; if there is no ELSE clause the
CASE defaults to NULL when not matched. But it is a bit clearer with
the explicit assignment. COUNT does not count NULLs.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 11:05:02 -0700, Ramez
<Ramez@.discussions.microsoft.com> wrote:
>Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
>Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
>Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005]
,
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
>FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID|||Actually you don't need a join.
try this
select * from
(Select SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled from test2) as t2,
(select SUM(CASE WHEN Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
from test1) t1
Hope this helps.
--
"Ramez" wrote:
> This is a counting issue following an inner join.
> I have two tables (simplifying it for clarity)
> Offering Table: OfferingID, year
> Registration Table: RegistrationID, OfferingID, Registration_Status
> -- Start Table Definitions
> --
> CREATE TABLE [dbo].[Test1] (
> [OfferingID] [int] NOT NULL ,
> [Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[test2] (
> [RegistrationID] [int]NOT NULL ,
> [OfferingID] [int] NOT NULL ,
> [Registration_Status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Test1 Values (1,2005)
> INSERT INTO Test1 Values (2,2005)
> INSERT INTO Test1 Values (3,2006)
> INSERT INTO Test2 Values (1,1,1)
> INSERT INTO Test2 Values (2,1,1)
> INSERT INTO Test2 Values (3,1,2)
> INSERT INTO Test2 Values (4,1,2)
> INSERT INTO Test2 Values (5,1,2)
> INSERT INTO Test2 Values (6,2,1)
> INSERT INTO Test2 Values (7,2,1)
> INSERT INTO Test2 Values (8,3,1)
> INSERT INTO Test2 Values (9,3,2)
> -- END Table Definitions
> --
> Now I want to do some counting, basically I want to count the number of
> offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
> I also want to count the total number of registrations of
> Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 an
d 4
> respectivly)
> This issue is I want them all in a single record. I came up with this SQL
> Query
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005
],
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006
]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Returns:
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 7 2
> It returns the correct Registration Status counts, but not the correct
> number of offerings per year because the records are counted over and over
> due to the join. What I really want is to count only distinct records in
> test1.
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 2 1
> Thanks!|||Hello, Ramez
To get the desired result, you can simply use something like this:
SELECT
(SELECT COUNT(*) FROM test2 WHERE Registration_status=1) AS Attended,
(SELECT COUNT(*) FROM test2 WHERE Registration_status=2) AS Cancelled,
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2005) AS [2005],
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2006) AS [2006]
If you really want to use a join (but I don't see any good reason for
this), you can use the following query:
SELECT
SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS Cancelled,
COUNT(DISTINCT CASE WHEN Offering_Year = '2005' THEN t1.OfferingID
END) AS [2005],
COUNT(DISTINCT CASE WHEN Offering_Year = '2006' THEN t1.OfferingID
END) AS [2006]
FROM test1 t1 INNER JOIN test2 t2 ON t1.OfferingId = t2.OfferingID
However, this comes at the expense of a warning: "Warning: Null value
is eliminated by an aggregate or other SET operation."; the warning can
be eliminated by using SET ANSI_WARNINGS OFF, but this is not
recommended (one of the reasons is because ANSI_WARNINGS is required to
be ON for using indexes on computed columns and indexed views).
Razvan|||Roy,
Do we need the join and case and distinct ops for this scenario. I
seriously doubt it.
--
"Roy Harvey" wrote:
> You need to use COUNT(distinct).
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
> THEN t1.OfferingID ELSE NULL END) AS [2005],
> COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
> THEN t1.OfferingID ELSE NULL END) AS [2006]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Note that the ELSE NULL is optional; if there is no ELSE clause the
> CASE defaults to NULL when not matched. But it is a bit clearer with
> the explicit assignment. COUNT does not count NULLs.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 11:05:02 -0700, Ramez
> <Ramez@.discussions.microsoft.com> wrote:
>
>
Help with counting Query
I am trying to find a query to count rows with multiple conditions.
I have files which are suffixed with -R and then a number from 1 to 3 and
then two digits 00-14.
the field is char
i.e.:
-R101
-R102
-R114
-R201
-R302
I need to determine how many 101, how many 102, etc through 114 and then the
same for
the 201-214 series and then
the 301-314 series.
I will be sending the query from Visual Basic using ADODC so I am not sure
how the data will be returned. If it was written to a temp table that would
be great.
Thanks,
Bob Hiller
Lifts for the Disabled LLCselect right(columnName, 3), count(columnName)
from tableName
group by right(columnName, 3)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>I am trying to find a query to count rows with multiple conditions.
> I have files which are suffixed with -R and then a number from 1 to 3 and
> then two digits 00-14.
> the field is char
> i.e.:
> -R101
> -R102
> -R114
> -R201
> -R302
> I need to determine how many 101, how many 102, etc through 114 and then
> the same for
> the 201-214 series and then
> the 301-314 series.
> I will be sending the query from Visual Basic using ADODC so I am not sure
> how the data will be returned. If it was written to a temp table that
> would be great.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
>|||Thank you,
That worked great but I did not ask the full question. Maybe you can help
again.
if these strings are in a column
12345678-R101
12345678-R201
12345678-R301
98564512-R112
18752381-R101
18752381-R201
18752381-R110
18752381-R111
18752381-R211
If there is a -R2 there will always be a -R1. Likewise if there is a -R3
there will always be a -R2.
In the above example I need to return
12345678-R301
98564512-R112
18752381-R201
18752381-R110
18752381-R211
I hope I have explained this well enough.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
> select right(columnName, 3), count(columnName)
> from tableName
> group by right(columnName, 3)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>|||select left(columnName, charindex('-R',columnName)+1),
MAX(substring(columnName,charindex('-R',columnName)+2,10))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Thank you,
> That worked great but I did not ask the full question. Maybe you can help
> again.
> if these strings are in a column
> 12345678-R101
> 12345678-R201
> 12345678-R301
> 98564512-R112
> 18752381-R101
> 18752381-R201
> 18752381-R110
> 18752381-R111
> 18752381-R211
> If there is a -R2 there will always be a -R1. Likewise if there is a -R3
> there will always be a -R2.
> In the above example I need to return
> 12345678-R301
> 98564512-R112
> 18752381-R201
> 18752381-R110
> 18752381-R211
> I hope I have explained this well enough.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
>|||OOPS... You need to convert the count values to int...
select left(columnName, charindex('-R',columnName)+1),
MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(substring(columnName,charindex('-R',columnName)+2,10))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||I will do some more checking but thus far your very appreciated suggestion
is producing some very strange results.
For one thing it is returning 2 expressions. I would expect only one.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I can live with the 2 returned expressions, they will work fine.
Here is what is returned when I run the sample:
12345678-R301
98564512-R112
18752381-R211
I am missing:
18752381-R201
18752381-R110
Think of the first number after the -R as a counter for the last 2 numbers
that represent a group.
When these are in the table
18752381-R101 01 is the group and 1 is the counter
18752381-R201 01 is the group and 2 is the counter
we want to return the largest counter for group 01 for the number to the
left of -R
return (18752381-R201)
18752381-R110 10 is the group and 1 is the counter
return (18752381-R110) it is the only group 10 for the number to the left
of -R
18752381-R111 11 is the group and 1 is the counter
18752381-R211 11 is the group and 2 is the counter
return (18752381-R211)
Thank in advance,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||select max(a)
from(select '12345678-R101'
union all select '12345678-R201'
union all select '12345678-R301'
union all select '98564512-R112'
union all select '18752381-R101'
union all select '18752381-R201'
union all select '18752381-R110'
union all select '18752381-R111'
union all select '18752381-R211')x(a)
group by left(a,8),right(a,2)
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Ok, I can live with the 2 returned expressions, they will work fine.
> Here is what is returned when I run the sample:
> 12345678-R301
> 98564512-R112
> 18752381-R211
> I am missing:
> 18752381-R201
> 18752381-R110
> Think of the first number after the -R as a counter for the last 2 numbers
> that represent a group.
> When these are in the table
> 18752381-R101 01 is the group and 1 is the counter
> 18752381-R201 01 is the group and 2 is the counter
> we want to return the largest counter for group 01 for the number to the
> left of -R
> return (18752381-R201)
> 18752381-R110 10 is the group and 1 is the counter
> return (18752381-R110) it is the only group 10 for the number to the
> left of -R
> 18752381-R111 11 is the group and 1 is the counter
> 18752381-R211 11 is the group and 2 is the counter
> return (18752381-R211)
> Thank in advance,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
>|||oj,
Thanks for the suggestion but the values where just given as samples. There
are thousands of rows that I have to search through. I don't think this
approach will work.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"oj" <nospam_ojngo@.home.com> wrote in message
news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
> select max(a)
> from(select '12345678-R101'
> union all select '12345678-R201'
> union all select '12345678-R301'
> union all select '98564512-R112'
> union all select '18752381-R101'
> union all select '18752381-R201'
> union all select '18752381-R110'
> union all select '18752381-R111'
> union all select '18752381-R211')x(a)
> group by left(a,8),right(a,2)
>
> --
> -oj
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
>|||Bob,
You need to adapt the technique to your data.
e.g.
select max(your_col)
from tb
group by left(your_col,8),right(your_col,2)
If it does not give you the desired result, you'd want to post ddl + sample
data + expected result here so we can help.
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uRMPBI8TGHA.5836@.TK2MSFTNGP10.phx.gbl...
> oj,
> Thanks for the suggestion but the values where just given as samples.
> There are thousands of rows that I have to search through. I don't think
> this approach will work.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
>
I have files which are suffixed with -R and then a number from 1 to 3 and
then two digits 00-14.
the field is char
i.e.:
-R101
-R102
-R114
-R201
-R302
I need to determine how many 101, how many 102, etc through 114 and then the
same for
the 201-214 series and then
the 301-314 series.
I will be sending the query from Visual Basic using ADODC so I am not sure
how the data will be returned. If it was written to a temp table that would
be great.
Thanks,
Bob Hiller
Lifts for the Disabled LLCselect right(columnName, 3), count(columnName)
from tableName
group by right(columnName, 3)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>I am trying to find a query to count rows with multiple conditions.
> I have files which are suffixed with -R and then a number from 1 to 3 and
> then two digits 00-14.
> the field is char
> i.e.:
> -R101
> -R102
> -R114
> -R201
> -R302
> I need to determine how many 101, how many 102, etc through 114 and then
> the same for
> the 201-214 series and then
> the 301-314 series.
> I will be sending the query from Visual Basic using ADODC so I am not sure
> how the data will be returned. If it was written to a temp table that
> would be great.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
>|||Thank you,
That worked great but I did not ask the full question. Maybe you can help
again.
if these strings are in a column
12345678-R101
12345678-R201
12345678-R301
98564512-R112
18752381-R101
18752381-R201
18752381-R110
18752381-R111
18752381-R211
If there is a -R2 there will always be a -R1. Likewise if there is a -R3
there will always be a -R2.
In the above example I need to return
12345678-R301
98564512-R112
18752381-R201
18752381-R110
18752381-R211
I hope I have explained this well enough.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
> select right(columnName, 3), count(columnName)
> from tableName
> group by right(columnName, 3)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>|||select left(columnName, charindex('-R',columnName)+1),
MAX(substring(columnName,charindex('-R',columnName)+2,10))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Thank you,
> That worked great but I did not ask the full question. Maybe you can help
> again.
> if these strings are in a column
> 12345678-R101
> 12345678-R201
> 12345678-R301
> 98564512-R112
> 18752381-R101
> 18752381-R201
> 18752381-R110
> 18752381-R111
> 18752381-R211
> If there is a -R2 there will always be a -R1. Likewise if there is a -R3
> there will always be a -R2.
> In the above example I need to return
> 12345678-R301
> 98564512-R112
> 18752381-R201
> 18752381-R110
> 18752381-R211
> I hope I have explained this well enough.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
>|||OOPS... You need to convert the count values to int...
select left(columnName, charindex('-R',columnName)+1),
MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(substring(columnName,charindex('-R',columnName)+2,10))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||I will do some more checking but thus far your very appreciated suggestion
is producing some very strange results.
For one thing it is returning 2 expressions. I would expect only one.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I can live with the 2 returned expressions, they will work fine.
Here is what is returned when I run the sample:
12345678-R301
98564512-R112
18752381-R211
I am missing:
18752381-R201
18752381-R110
Think of the first number after the -R as a counter for the last 2 numbers
that represent a group.
When these are in the table
18752381-R101 01 is the group and 1 is the counter
18752381-R201 01 is the group and 2 is the counter
we want to return the largest counter for group 01 for the number to the
left of -R
return (18752381-R201)
18752381-R110 10 is the group and 1 is the counter
return (18752381-R110) it is the only group 10 for the number to the left
of -R
18752381-R111 11 is the group and 1 is the counter
18752381-R211 11 is the group and 2 is the counter
return (18752381-R211)
Thank in advance,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||select max(a)
from(select '12345678-R101'
union all select '12345678-R201'
union all select '12345678-R301'
union all select '98564512-R112'
union all select '18752381-R101'
union all select '18752381-R201'
union all select '18752381-R110'
union all select '18752381-R111'
union all select '18752381-R211')x(a)
group by left(a,8),right(a,2)
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Ok, I can live with the 2 returned expressions, they will work fine.
> Here is what is returned when I run the sample:
> 12345678-R301
> 98564512-R112
> 18752381-R211
> I am missing:
> 18752381-R201
> 18752381-R110
> Think of the first number after the -R as a counter for the last 2 numbers
> that represent a group.
> When these are in the table
> 18752381-R101 01 is the group and 1 is the counter
> 18752381-R201 01 is the group and 2 is the counter
> we want to return the largest counter for group 01 for the number to the
> left of -R
> return (18752381-R201)
> 18752381-R110 10 is the group and 1 is the counter
> return (18752381-R110) it is the only group 10 for the number to the
> left of -R
> 18752381-R111 11 is the group and 1 is the counter
> 18752381-R211 11 is the group and 2 is the counter
> return (18752381-R211)
> Thank in advance,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
>|||oj,
Thanks for the suggestion but the values where just given as samples. There
are thousands of rows that I have to search through. I don't think this
approach will work.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"oj" <nospam_ojngo@.home.com> wrote in message
news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
> select max(a)
> from(select '12345678-R101'
> union all select '12345678-R201'
> union all select '12345678-R301'
> union all select '98564512-R112'
> union all select '18752381-R101'
> union all select '18752381-R201'
> union all select '18752381-R110'
> union all select '18752381-R111'
> union all select '18752381-R211')x(a)
> group by left(a,8),right(a,2)
>
> --
> -oj
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
>|||Bob,
You need to adapt the technique to your data.
e.g.
select max(your_col)
from tb
group by left(your_col,8),right(your_col,2)
If it does not give you the desired result, you'd want to post ddl + sample
data + expected result here so we can help.
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uRMPBI8TGHA.5836@.TK2MSFTNGP10.phx.gbl...
> oj,
> Thanks for the suggestion but the values where just given as samples.
> There are thousands of rows that I have to search through. I don't think
> this approach will work.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
>
Help with complicated SQL query
Hi everyone.
This is my scenario:
I have two tables:
persons (id,age,roleid)
roles (roleid,description)
I want to build a sql query to produce the following rows (example):
range(age) role1 role2 role3 ... rolen
0 to 4 11 24 5 7
5 to 9 42 7 1 0
10 to 14 14 21 9 8
15 to 20 7 0 7 19
I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.
Thanks for all your help!
Rolandyou want a cross tab query using the CASE statement. Read about both in books online and come back if you are still having trouble.|||Lookup "Crosstab" in Books Online.|||this isn't quite as straightforward as it first appearsselect range
, sum(case when roleid =1
then rows else 0 end) as role1
, sum(case when roleid =2
then rows else 0 end) as role2
, ...
, sum(case when roleid =n
then rows else 0 end) as rolen
from (
select '0 to 4' as range
, roleid
, count(*) as rows
from persons
where age between 0 and 4
group by roleid
union all
select '5 to 9' as range
, roleid
, count(*) as rows
from persons
where age between 5 and 9
group by roleid
union all
select '10 to 14' as range
, roleid
, count(*) as rows
from persons
where age between 10 and 14
group by roleid
union all
select '15 to 20' as range
, roleid
, count(*) as rows
from persons
where age between 15 and 20
group by roleid
) as dt
group by range|||Great r937!! thanks!! everything worked perfectly!
Roland
This is my scenario:
I have two tables:
persons (id,age,roleid)
roles (roleid,description)
I want to build a sql query to produce the following rows (example):
range(age) role1 role2 role3 ... rolen
0 to 4 11 24 5 7
5 to 9 42 7 1 0
10 to 14 14 21 9 8
15 to 20 7 0 7 19
I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.
Thanks for all your help!
Rolandyou want a cross tab query using the CASE statement. Read about both in books online and come back if you are still having trouble.|||Lookup "Crosstab" in Books Online.|||this isn't quite as straightforward as it first appearsselect range
, sum(case when roleid =1
then rows else 0 end) as role1
, sum(case when roleid =2
then rows else 0 end) as role2
, ...
, sum(case when roleid =n
then rows else 0 end) as rolen
from (
select '0 to 4' as range
, roleid
, count(*) as rows
from persons
where age between 0 and 4
group by roleid
union all
select '5 to 9' as range
, roleid
, count(*) as rows
from persons
where age between 5 and 9
group by roleid
union all
select '10 to 14' as range
, roleid
, count(*) as rows
from persons
where age between 10 and 14
group by roleid
union all
select '15 to 20' as range
, roleid
, count(*) as rows
from persons
where age between 15 and 20
group by roleid
) as dt
group by range|||Great r937!! thanks!! everything worked perfectly!
Roland
Help with complicated query...
Hello All,
I have a simple table that stores messages of different types from
different sources. The definition of the table is shown below. I need
to devise an efficient query to return a "list of the N more recent
messages for a subset of sources within a specified time frame."
create table MessageTbl
(
src nvarchar(50), -- Source of the message
type nvarchar(50), -- Type of the message.
msg nvarchar(1000), -- Text of the message
dt datetime -- When the message was posted
)
We are given the following parameters:
1. declare @.startTime datetime -- Start of the time frame
2. declare @.endTime datetime -- End of the time frame
3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
contains a list of sources/types for which we want to obtain the
messages.
4. N -- How many messages per source/type
If all I wanted was the 20 more recent messages for source1/type1
between @.startTime and @.endTime, I could do something like:
SELECT TOP 20 *
FROM MessageTbl
WHERE (src ='source1') AND ( type='type1')
AND ( dt BETWEEN @.startTime AND @.endTime )
ORDER BY dt DESC
In my case, however, I have a number of pairs (src,type) in the local
table @.myTable. Therefore, what I ultimately want is equivalent to the
UNION of the results of such query for each pair(src,type). Another
thing missing is that I am using a hardcoded value for the TOP clause.
This also varies.
All the solutions that I can think of are very inneficient, cumbersome,
and involve a number of temporary tables. I was wondering if the
experts could lead me to a cleaner query design.
Thank you
- CDOn 25 Oct 2005 14:57:37 -0700, crbd98@.yahoo.com wrote:
>Hello All,
>I have a simple table that stores messages of different types from
>different sources. The definition of the table is shown below. I need
>to devise an efficient query to return a "list of the N more recent
>messages for a subset of sources within a specified time frame."
>create table MessageTbl
>(
> src nvarchar(50), -- Source of the message
> type nvarchar(50), -- Type of the message.
> msg nvarchar(1000), -- Text of the message
> dt datetime -- When the message was posted
> )
>We are given the following parameters:
>1. declare @.startTime datetime -- Start of the time frame
>2. declare @.endTime datetime -- End of the time frame
>3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
>contains a list of sources/types for which we want to obtain the
>messages.
>4. N -- How many messages per source/type
>
>If all I wanted was the 20 more recent messages for source1/type1
>between @.startTime and @.endTime, I could do something like:
>SELECT TOP 20 *
>FROM MessageTbl
>WHERE (src ='source1') AND ( type='type1')
> AND ( dt BETWEEN @.startTime AND @.endTime )
>ORDER BY dt DESC
>In my case, however, I have a number of pairs (src,type) in the local
>table @.myTable. Therefore, what I ultimately want is equivalent to the
>UNION of the results of such query for each pair(src,type). Another
>thing missing is that I am using a hardcoded value for the TOP clause.
>This also varies.
>All the solutions that I can think of are very inneficient, cumbersome,
>and involve a number of temporary tables. I was wondering if the
>experts could lead me to a cleaner query design.
>Thank you
>- CD
Hi CD,
Try if this works:
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your reply. The query that you posted produces the
correct result but it is very expensive. It takes almost 30 seconds
when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
the execution plan and it seems there is a lot of table scanning going
on.
Maybe I have to look into a solution that does not use the join...
Any Ideas?
Thanks
- CD|||Are there any indexes? Your original DDL had no keys and you used a
proprietary table variable.
Also, you might want to fix the data element names. They are horrible.|||Hello Celko,
The table MessageTbl has a compound primary key involving src and type.
There are no other indexes. My local table variable has no keys and no
indexes. Do you think this is a problem? Is there any problem in using
table variables or are you just concerned about the portability of the
code?
Any suggestions?
Thank you
CD
--CELKO-- wrote:
> Are there any indexes? Your original DDL had no keys and you used a
> proprietary table variable.
> Also, you might want to fix the data element names. They are horrible.|||On 25 Oct 2005 16:22:34 -0700, crbd98@.yahoo.com wrote:
>Hello Hugo,
>Thank you for your reply. The query that you posted produces the
>correct result but it is very expensive. It takes almost 30 seconds
>when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
>the execution plan and it seems there is a lot of table scanning going
>on.
>Maybe I have to look into a solution that does not use the join...
>
>Any Ideas?
Hi CD,
Try if adding this index helps:
CREATE INDEX ProperNameHere
ON MessageTbl (src, type, dt DESC)
On 26 Oct 2005 10:50:55 -0700, crbd98@.yahoo.com wrote:
>Hello Celko,
>The table MessageTbl has a compound primary key involving src and type.
Huh? If there's a compount primary key on src and type, then how can you
find the 20 most recent messages between two moments for a given src and
type? As a result of the primary key, there will be only one message for
each src / type combination!!
But since you apparently have a PRIMARKY KEY that was not included in
your first post, please post the complete CREATE TABLE statement, WITH
all constraints, properties and indexes. My suggestion above might well
be invalidated by your current keys and indexes.
>There are no other indexes. My local table variable has no keys and no
>indexes. Do you think this is a problem?
You might have duplicates in the table variable, which will never
improve performance.
You can't define indexes for a table variable, but you can define
PRIMARY KEY or UNIQUE constraints (and they DO automatically add an
index). In your case, try if adding a PRIMARY KEY (src, type) helps the
performance. And if it doesn't, but doesn't hinder performance either,
then do leave it in.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for the suggestion of creating an index with the src, type,
and dt. That reduced the execution time from 30 sec to less than a
second. My last problem was to parameterize the value for TOP clause. I
created a huge dynamic SQL statement that I use with sp_executesql.
VERY UGLY!!! Do you have any alternative suggestion?
Thanks
Cassiano|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:
> Hello Hugo,
> Thank you for the suggestion of creating an index with the src, type,
> and dt. That reduced the execution time from 30 sec to less than a
> second. My last problem was to parameterize the value for TOP clause. I
> created a huge dynamic SQL statement that I use with sp_executesql.
> VERY UGLY!!! Do you have any alternative suggestion?
> Thanks
> Cassiano
Issue a SET ROWCOUNT in the stored procedure:
CREATE PROCEDURE foo (@.nRows int, @.bar varchar(30))
AS
SET ROWCOUNT @.nRows
SELECT * FROM sysobjects where name <> @.bar
SET ROWCOUNT 0
GO
SQL Server specific, but it works, and there's no dynamic SQL or even
recompilation.|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:
>Hello Hugo,
>Thank you for the suggestion of creating an index with the src, type,
>and dt. That reduced the execution time from 30 sec to less than a
>second. My last problem was to parameterize the value for TOP clause. I
>created a huge dynamic SQL statement that I use with sp_executesql.
>VERY UGLY!!! Do you have any alternative suggestion?
>Thanks
>Cassiano
Hi Cassiano,
The suggestion made by Ross (SET ROWCOUNT) is fine if you wwant to limit
the total number of rows returned by the query. But I seem to recall
that your problem was more complex than that.
Going back in the thread, I see this query I posted a few days ago - is
this the one you are using, and where you want to replace TOP 20 with a
variable number?
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
The easiest answer is to wait a few w
s. TOP @.variable will be
suppported in SQL Server 2000, which will hit the streets in the w of
November 7th.
Or use the following (which is ANSI standard to boot):
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND (SELECT COUNT(*)
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
AND c.dt <= a.dt) <= 20
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you very much for your reply. This whole discussion has been very
enlightening.
I compared the performance of two variations of the solution:
#1. The last solution that you suggested.
#2. Based on your original solution (the one with hardcoded TOP
clause). The only modification that I made was to create the query
string dynamically to simmulate the effect of a variable TOP clause.
The table has the following indices (in addition to a PK index on a
MsgId column that I did not include in the posting).
CREATE INDEX IX_MessageTbl1
ON MessageTbl (src, type, dt DESC)
and
CREATE INDEX IX_MessageTbl2
ON MessageTbl (dt DESC)
I noticed that #1 was 6 times slower than #2. Although #2 is faster, I
do not like it, because I create the query dynamically. Do you know if
there is any index that I can create or any hint that I can use to
speed-up the query #1.
Thank you
- CDsql
I have a simple table that stores messages of different types from
different sources. The definition of the table is shown below. I need
to devise an efficient query to return a "list of the N more recent
messages for a subset of sources within a specified time frame."
create table MessageTbl
(
src nvarchar(50), -- Source of the message
type nvarchar(50), -- Type of the message.
msg nvarchar(1000), -- Text of the message
dt datetime -- When the message was posted
)
We are given the following parameters:
1. declare @.startTime datetime -- Start of the time frame
2. declare @.endTime datetime -- End of the time frame
3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
contains a list of sources/types for which we want to obtain the
messages.
4. N -- How many messages per source/type
If all I wanted was the 20 more recent messages for source1/type1
between @.startTime and @.endTime, I could do something like:
SELECT TOP 20 *
FROM MessageTbl
WHERE (src ='source1') AND ( type='type1')
AND ( dt BETWEEN @.startTime AND @.endTime )
ORDER BY dt DESC
In my case, however, I have a number of pairs (src,type) in the local
table @.myTable. Therefore, what I ultimately want is equivalent to the
UNION of the results of such query for each pair(src,type). Another
thing missing is that I am using a hardcoded value for the TOP clause.
This also varies.
All the solutions that I can think of are very inneficient, cumbersome,
and involve a number of temporary tables. I was wondering if the
experts could lead me to a cleaner query design.
Thank you
- CDOn 25 Oct 2005 14:57:37 -0700, crbd98@.yahoo.com wrote:
>Hello All,
>I have a simple table that stores messages of different types from
>different sources. The definition of the table is shown below. I need
>to devise an efficient query to return a "list of the N more recent
>messages for a subset of sources within a specified time frame."
>create table MessageTbl
>(
> src nvarchar(50), -- Source of the message
> type nvarchar(50), -- Type of the message.
> msg nvarchar(1000), -- Text of the message
> dt datetime -- When the message was posted
> )
>We are given the following parameters:
>1. declare @.startTime datetime -- Start of the time frame
>2. declare @.endTime datetime -- End of the time frame
>3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
>contains a list of sources/types for which we want to obtain the
>messages.
>4. N -- How many messages per source/type
>
>If all I wanted was the 20 more recent messages for source1/type1
>between @.startTime and @.endTime, I could do something like:
>SELECT TOP 20 *
>FROM MessageTbl
>WHERE (src ='source1') AND ( type='type1')
> AND ( dt BETWEEN @.startTime AND @.endTime )
>ORDER BY dt DESC
>In my case, however, I have a number of pairs (src,type) in the local
>table @.myTable. Therefore, what I ultimately want is equivalent to the
>UNION of the results of such query for each pair(src,type). Another
>thing missing is that I am using a hardcoded value for the TOP clause.
>This also varies.
>All the solutions that I can think of are very inneficient, cumbersome,
>and involve a number of temporary tables. I was wondering if the
>experts could lead me to a cleaner query design.
>Thank you
>- CD
Hi CD,
Try if this works:
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your reply. The query that you posted produces the
correct result but it is very expensive. It takes almost 30 seconds
when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
the execution plan and it seems there is a lot of table scanning going
on.
Maybe I have to look into a solution that does not use the join...
Any Ideas?
Thanks
- CD|||Are there any indexes? Your original DDL had no keys and you used a
proprietary table variable.
Also, you might want to fix the data element names. They are horrible.|||Hello Celko,
The table MessageTbl has a compound primary key involving src and type.
There are no other indexes. My local table variable has no keys and no
indexes. Do you think this is a problem? Is there any problem in using
table variables or are you just concerned about the portability of the
code?
Any suggestions?
Thank you
CD
--CELKO-- wrote:
> Are there any indexes? Your original DDL had no keys and you used a
> proprietary table variable.
> Also, you might want to fix the data element names. They are horrible.|||On 25 Oct 2005 16:22:34 -0700, crbd98@.yahoo.com wrote:
>Hello Hugo,
>Thank you for your reply. The query that you posted produces the
>correct result but it is very expensive. It takes almost 30 seconds
>when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
>the execution plan and it seems there is a lot of table scanning going
>on.
>Maybe I have to look into a solution that does not use the join...
>
>Any Ideas?
Hi CD,
Try if adding this index helps:
CREATE INDEX ProperNameHere
ON MessageTbl (src, type, dt DESC)
On 26 Oct 2005 10:50:55 -0700, crbd98@.yahoo.com wrote:
>Hello Celko,
>The table MessageTbl has a compound primary key involving src and type.
Huh? If there's a compount primary key on src and type, then how can you
find the 20 most recent messages between two moments for a given src and
type? As a result of the primary key, there will be only one message for
each src / type combination!!
But since you apparently have a PRIMARKY KEY that was not included in
your first post, please post the complete CREATE TABLE statement, WITH
all constraints, properties and indexes. My suggestion above might well
be invalidated by your current keys and indexes.
>There are no other indexes. My local table variable has no keys and no
>indexes. Do you think this is a problem?
You might have duplicates in the table variable, which will never
improve performance.
You can't define indexes for a table variable, but you can define
PRIMARY KEY or UNIQUE constraints (and they DO automatically add an
index). In your case, try if adding a PRIMARY KEY (src, type) helps the
performance. And if it doesn't, but doesn't hinder performance either,
then do leave it in.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for the suggestion of creating an index with the src, type,
and dt. That reduced the execution time from 30 sec to less than a
second. My last problem was to parameterize the value for TOP clause. I
created a huge dynamic SQL statement that I use with sp_executesql.
VERY UGLY!!! Do you have any alternative suggestion?
Thanks
Cassiano|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:
> Hello Hugo,
> Thank you for the suggestion of creating an index with the src, type,
> and dt. That reduced the execution time from 30 sec to less than a
> second. My last problem was to parameterize the value for TOP clause. I
> created a huge dynamic SQL statement that I use with sp_executesql.
> VERY UGLY!!! Do you have any alternative suggestion?
> Thanks
> Cassiano
Issue a SET ROWCOUNT in the stored procedure:
CREATE PROCEDURE foo (@.nRows int, @.bar varchar(30))
AS
SET ROWCOUNT @.nRows
SELECT * FROM sysobjects where name <> @.bar
SET ROWCOUNT 0
GO
SQL Server specific, but it works, and there's no dynamic SQL or even
recompilation.|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:
>Hello Hugo,
>Thank you for the suggestion of creating an index with the src, type,
>and dt. That reduced the execution time from 30 sec to less than a
>second. My last problem was to parameterize the value for TOP clause. I
>created a huge dynamic SQL statement that I use with sp_executesql.
>VERY UGLY!!! Do you have any alternative suggestion?
>Thanks
>Cassiano
Hi Cassiano,
The suggestion made by Ross (SET ROWCOUNT) is fine if you wwant to limit
the total number of rows returned by the query. But I seem to recall
that your problem was more complex than that.
Going back in the thread, I see this query I posted a few days ago - is
this the one you are using, and where you want to replace TOP 20 with a
variable number?
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
The easiest answer is to wait a few w
s. TOP @.variable will besuppported in SQL Server 2000, which will hit the streets in the w
ofNovember 7th.
Or use the following (which is ANSI standard to boot):
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND (SELECT COUNT(*)
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
AND c.dt <= a.dt) <= 20
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you very much for your reply. This whole discussion has been very
enlightening.
I compared the performance of two variations of the solution:
#1. The last solution that you suggested.
#2. Based on your original solution (the one with hardcoded TOP
clause). The only modification that I made was to create the query
string dynamically to simmulate the effect of a variable TOP clause.
The table has the following indices (in addition to a PK index on a
MsgId column that I did not include in the posting).
CREATE INDEX IX_MessageTbl1
ON MessageTbl (src, type, dt DESC)
and
CREATE INDEX IX_MessageTbl2
ON MessageTbl (dt DESC)
I noticed that #1 was 6 times slower than #2. Although #2 is faster, I
do not like it, because I create the query dynamically. Do you know if
there is any index that I can create or any hint that I can use to
speed-up the query #1.
Thank you
- CDsql
help with complex SQL query
I need some help from prog. gurus with sql query.
I have a commodity table and I would like to pull information from it based on the conditions. I can write code to pull straight forward information from the table but I need help in writing query such that the later columns are based on the former columns.
To clear my question, I am writing the sample code below.
The column 4, 5 and 6 are based on column 1, 2, 3.
Column6 is column3/column2 (which is simple)
But column4 & 5 are too complex for me to write the code for. Column4 is the % of Quantity (cty_code)/(all countries) for that particular commodity. cty_code correspends to a country.
Table information:
[dbo].[2005exp](
[dom_or_for] [char](1) NOT NULL,
[commodity] [char](10) NOT NULL,
[cty_code] [char](4) NOT NULL,
[district] [char](2) NOT NULL,
[stat_month] [char](2) NOT NULL,
[cards_mo] [decimal](8, 0) NOT NULL,
[all_qy1_mo] [decimal](12, 0) NOT NULL,
[all_val_mo] [decimal](12, 0) NOT NULL
)
Table data snapshot:
Query1:
select top 10 commodity, sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where cty_code=5310 group by Commodity order by price desc
Output:
Commodity | Quantity | Price | Column4 | Column5 | Price/Quantity |
8517305000 | 0 | 46307629 | |||
8517905000 | 0 | 11990255 | |||
3003100000 | 2268 | 2687905 | 0.35% | 29.92% | 1185.1437 |
8524990000 | 148 | 1815000 | |||
8471300000 | 2591 | 1673570 | |||
9802400000 | 0 | 1560247 | |||
9880004000 | 0 | 1197407 | |||
8802300080 | 1 | 1100000 | |||
3819000000 | 601192 | 899417 | |||
9802200000 | 0 | 811996 |
Query2: (based on the results of query 1 i.e., commodity)
select Sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where commodity=3003100000
Output:
Quantity | Price |
645261 | 8982928 |
2268 / 645261 = 0.35% Column 4
2687905 / 8982928 = 29.92% Column 5
Quantity/Price = Average (2687906/2268 = 1185.1437) i.e. column3/column2=Column6
The final output should like: (There are additional 2 columns in the table below but they are not required)
select a.commodity, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as Price,
CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b
where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b
where a.commodity=b.commodity))*100.00 ELSE NULL END as col4, CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as col5,
CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as col6
from [2005exp] a
WHERE a.cty_code=5310
GROUP BY a.commodity
|||Thank you so much. The code works perfectly. I really appreciate all your help.
I wanted to add another column 7 which is column5/column4. How can I save the column5 and Column4 values in 2 different variables and then perform the division ?
I tried to use just the alias names i.e col5/col4, but its not working.
I tried using the actual code for both columns and its working but its taking 4 minutes to execute the query. Its repeating the same steps twice. How is it done right way ?
Also, I was trying to delcare a variable for the table name but without any success. Who is it done ?
Use test
Go
Declare @.Country Int
Set @.Country=5310
select top 10 a.commodity as Commodity, c.descrip_1 as Description, c.quantity_1 as Unit,
sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],
CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Quantity) %],
CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Value) %],
CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as [Average Price],
CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN
(((sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)))/((sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity)))) ELSE NULL END as [Price Ratio]
from [2005exp] a inner join concord c on a.commodity=c.commodity
WHERE a.cty_code=@.Country
GROUP BY a.commodity, c.descrip_1, c.quantity_1 order by [Value] desc
Wednesday, March 28, 2012
Help with complex query
Hi Everyone,
I need help writing the following query. I have to group my data by Department and have a field that will calculation the number of minutes that employee worked in that department. So basically I take the total number of minutes worked in the department and divide it by the total number of minutes the employee worked for the specified date range.
--
Agent Name: John Doe
Date Range: 1/1/2007 - 6/30/2007
RowID Work Minutes in Dept Total Work Minutes Dept
1 26355 52920 Service
2 9000 52920 Parts
3 17565 52920 Dispatch
Service = 26355 / 52920 = 0.499 = 50%
Parts = 9000 / 52920 = 0.17 = 17%
Dispatch= 17565 / 52920 = 0.33 = 33%
--
How can I accomplish this?
I am using SQL Server 2005 Express
Thank You
Assuming your table is the grouped sum's by department:
Code Snippet
create table #t1 (RowID int, [Work Minutes] int, [Total Work Minutes] int, Dept varchar(20) )
insert into #t1
select 1, 26355, 52920, 'Service'
union all select 2, 9000, 52920, 'Parts'
union all select 3, 17565, 52920, 'Dispatch'
select Dept, ' = ' + convert(varchar(15), [Work Minutes]) + ' / ' + convert(varchar(15), [Total Work Minutes]),
round(([Work Minutes]*100.00)/[Total Work Minutes], 0) as 'Percentage'
from #t1
|||DaleJ,
The table data is not grouped. Thats what makes this query complex.
|||
SamCosta wrote:
DaleJ,
The table data is not grouped. Thats what makes this query complex.
Can you right click the tables that you are using and choose "Script Table As..." and "Create To.." and post those back here. Once we have your structure we can help further.|||
And some additional sample data.
It's not that difficult, but would like to get it right the first time (or two 
Code Snippet
SELECT E.EMPID,
E.DEPTID,
CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),
Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,
SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI
FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date
<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID
WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)
GROUP BY E.ID, E.DEPTID
This query outputs:
EmpID DeptID AvgPLevel DeptWorkMI
28899 Service 2 17244
28899 Parts 3 9000
28899 Dispatch 1 27836
I need to then group the query results by EmpID to get a total Average Productivity Level
Result:
EmpID AvgPLevel
28899 2
How to calculate total productivity level:
(2 * 17244 / 54080) + (3 * 9000 / 54080) + (1 * 27836 / 54080) = 1.65 = Level 2
54080 is the total number of minutes worked in ALL departs. (17244 + 9000 + 27836 = 54080)
Thank You
|||See if this does what you need:
Code Snippet
;WITH base
AS
(
SELECT E.EMPID,
E.DEPTID,
CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),
Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,
SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI
FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date
<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID
WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)
GROUP BY E.ID, E.DEPTID
),
Totals
AS
(
SELECT EmpID, SUM(DeptWorkMi) AS TotalMinutes
FROM base
GROUP BY EmpID
)
SELECT b.EmpID, ROUND(SUM(1.0 * b.AvgPLevel * b.DeptWorkMI / t.TotalMinutes), 0) as AvgPLevel
FROM base b
INNER JOIN Totals AS t
ON b.EmpID = t.EmpID
GROUP BY b.EmpID
|||Thank you DaleJ. I was able to solve this problem using the CTE query example you provided.
Help with complex query
Hi Everyone,
I need some help writing a query that joins a table to a UNION query. I was wondering what is the most efficent way to do this.
Tables
Employees (EMPID, FULLNAME)
DailySchedules (SCHID,EMPID, SCHDATE,DEPTID)
GeneralSegments (GSID,EMPID,STARTTIME,STOPTIME)
DetailSegments (DSID,EMPID,STARTTIME,STOPTIME)
I need to join the records from GeneralSegments and DetailSegments THEN inner join DailySchedules and Employees.
Query must output:
EMPID, FULLNAME, SCHDATE,STARTTIME,STOPDTIME
Thank You
Can you provide some sample data (preferrably in the form of insert statements) along with a data representation of what you want for output?
Thanks.
|||Untested, but, it should give you an idea
Code Snippet
SELECT Segments.EMPID,
Employees.FULLNAME,
DailySchedules.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME
FROM
(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID
Hi David,
That example will work. One question though, I have to display the next 14 days (2 weeks) on the web. Where should I place my WHERE clause? In the outer query or include the date range in both union statements?
ie. WHERE SCHDATE BETWEEN GETDATE() AND GETDATE() + 14
Thank You
|||Is SCHDATE column present in the tables in the DailySchedule table or the segments tables? In any case, it doesn't matter. SQL Server will automatically roll the schdate predicate into the inner queries as well based on their reference. For example, if you want to filter on STARTTIME then you can include just one WHERE clause like below and it will be applied to both GeneralSegments & DetailSegments
Code Snippet
SELECT Segments.EMPID,
Employees.FULLNAME,
d.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME
FROM
(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID
WHERE Segments.STARTTIME >= @.Start
Tuesday, March 27, 2012
Help with building query
Hello,
This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:
Two tables:
Table 1:
Orders
Some of the fields:
ID
PropID
WorkOrderNum
OrderDesc
DateCompleted
Table 2:
OrderDetail
ID
OrderID
TenantName
As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.
Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):
SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'
Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.
thanks.Acciording to your explanation,
1. PropertyLocations has 1:many relation to ORDERS,
2. Orders has 1:many relation to OrderDetail.
My first try would be like below for TenantName:
SELECT Distinct
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID, Orders.PropID, Orders.WorkOrderNum,
Orders.OrderDesc, Orders.DateCompleted
FROM PropertyLocations INNER JOIN Orders
ON PropertyLocations.ID = Orders.PropID
INNER JOIN OrderDetail
ON Orders.ID = OrderDetail.OrderID
WHERE Orders.OrderDesc LIKE '%lds%'
AND OrderDetail.TenantName LIKE 'Steve%'|||That's almost it. It's pulling up all my orders that has the specified 'tenantname' (that is great!)...but it's showing duplicate orders.
For instance:
I have a record in my 'Orders' table of ID=2886. And in my 'OrderDetail' table, there are 3 records that have the characters 'dr.' in it that all have the value of 2886 in the 'OrderID' field which means that they all belong to the record in the 'Orders' table with ID of 2886 that I mentioned at the beginning of this paragraph. So it is showing me my data from the 'Orders' table 3 times (duplicates).
I also modified your statement slightly:
SELECT Distinct PropertyLocations.PropertyLocation, OrderDetail.TenantName, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC
I'm only doing 1 'LIKE' at a time...but that's probably my fault...I wasn't real clear about it.
So how do I make it show me just a single instance of a record from the 'Orders' table even though that there may be several records in the 'OrderDetail' table that matches the search criteria?
P.S. Thank you for reminding me about trying to be SQL99 compliant. ;)
thanks.|||The reason that you do have duplicates is because of Orders.DateCompleted even if you have DISTINCT in the SELECT statement.
Therefore my next question would be
1. Do you need Orders.DateCompleted to display?
2. If YES, then duplicates are unavoidable.
3. If NO, then remove Orders.DateCompleted from the SELECT statement. - Problem solved.
In case of YES,
1. Do the dates of Orders.DateCompleted fall into the same date?
If YES, then use CAST(Orders.DateCompleted as varchar(11)) to pick up only the date portion that ignores time portion of the Orders.DateCompleted. - Problem solved.
If NO, then still duplicates are unavoidable.|||Well, I tried removing the Orders.DateCompleted field even though I really wanted it, but it still gave me the same results.
I included 2 screenshots now, one w/DateCompleted so everyone can see exactly what I'm getting, and my SQL statement, and then 1 after I removed the Orders.DateCompleted field.
Just to be sure that everyone knows what I mean when I say duplicates, I mean that the same exact Work Order (which is a row...or an order...or record) is showing up multiple times even though only 1 actually exists in the database. You can see this in the screenshots - there are 3 rows of the WO#6997 which is far from correct - there is only 1 record in the database with that Work Order Number. But there are 3 unique records in the OrderDetail table that contain the characters 'dr.' in it that belong to that WO#6997 work order. (And just to clarify, the WorkOrderNum is not my 'unique id' field in the 'Orders' table, nor is it the field that is 'linked' with the OrderDetail table)
If the above is what everyone already realized/knew, then cool, but if it wasn't, then maybe it will help clear things up for someone to help a little easier.
thanks.|||Let me clarify
1. You have one record in the Order table with WO#6997.
2. You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
3. WO# in the Order table is not unique.
4. WO# in the OrderDetail is not unique.
OK.
First, my previous email regarding the Orders.DateCompleted was incorrect. Because it belongs to Orders table, it wouldn't generate duplicates. So please ignore my suggestion to remove the column Orders.DateCompleted.
Then please show me the TenantNames. Are they the same?
If they are different, obviously you will have 3 records.
Inside the SELECT statement, you have the following columns to display:
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted
Among them, only TenantName comes from OrderDetail table.
Therefore if TenantName is the same it shouldn't display three records.
I am puzzled and very curious on the result for the TenantName.|||1. Yes
2. Kind of...The OrderDetail table doesn't actually have a WO# field in it. It has an OrderID field that corresponds to the ID field of the Orders table (which contains a WO# field)...but yes, there are 3 records that show up from the OrderDetail field with a TenantName like '%dr.%' that belong to the record that contains WO#6997 in the Orders table
3. Actually, WO# is unique (although there might be some records in the table ('Orders' table as I mentioned), that don't have a value for this field...but the records would be so old, it will never come up...so is it still considered 'unique'?), but isn't my identity field. I have an ID field for that.
4. As I mentioned, the OrderDetail table does not have a WO# field.
Ok, no problem.
Below are the 3 records that came up that matched the criteria:
The following data is from the TenantName field, and yes, as you can see, are unique. They all share the same OrderID of 2886...which is the ID of the Orders table. So I guess this is where the problem is? See, I don't really care that 3 records from the OrderDetail table matches my search criteria...all I need to know is that at least one record matches, and then I want to show the Order/Work Order from the Orders table that it corresponds to...1 time.
At Napa 9, 870 Napa Valley Corp. Dr.:
Work to be done at: [nl]870 Napa Valley Corp.Dr./Napa 9[nl]2511-2515 Napa Valley Corp. Dr./The Vines[nl]~+
At The Vines, 2511-2515 Napa Valley Corp. Dr.:
Yes, those are the columns I would like to display, and yes, TenantName is the only one that comes from the OrderDetail table.
As you see, the 3 records aren't the same that match the search criteria.|||Now I can see whole picture.
You have one record in the Order table with WO#6997.
You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
ID is primary key in Order table.
OrderID in the OrderDetail is foreign key to Order table.
Inside the SELECT statement, you have the following columns to display:
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted
But OrderDetail.TenantName are not the same. Therefore you have to force it to be the same by using the criteria.
So
Select Distinct
PropertyLocations.PropertyLocation,
'Like ''%Dr.%''' as [TenantName Criteria],
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompletedFROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC
I hope this should satisfy your requirements.|||Cool, I'm sorry it took so long to get there.
1. Yes
2. Yes, in a way...see #2 from my previous post
3. Yes
4. Yes
Ok, this is where you're going to want to kill me. I just realized after re-reading your post...I don't actually need to display OrderDetail.TenantName. You can even see that I'm not showing that field/column in my screenshots...silly me.
So I tried one of your previous SELECT statements, and removed it (the OrderDetail.TenantName part) from the beginning so as to not display it, and it worked! I'm so sorry for all of the confusion and the extra steps.
But out of not wanting all your hard work to not be in vain, I tried your last statement, and it worked too! So now I know what to do if I have a similar situation. Although, I do have a question...I've never seen part of your statement before:
'Like ''%Dr.%''' as [TenantName Criteria],
What is that all about? Obviously it works, but it's new to me. I also don't really understand why it would've worked without showing the OrderDetail.TenantName column.
Thanks for sticking with me and all of your help, TerryP.|||I am glad that I could help you.
Your question:
'Like ''%Dr.%''' as [TenantName Criteria],
This is a constant column. Because the TenantName is not actually the name of the tenant but a memo or description, I tried to make a common string for TenantName which comes from the criteria in the WHERE clause. When it displayed, all three records would have 'Like ''%Dr.%'''. So I could eliminate multiple records of the same constant string to a single record by using DISTINCT keyword in SELECT clause.
I also don't really understand why it would've worked without showing the OrderDetail.TenantName column
You do not have to display all or any of the columns of tables used in FROM clause. But it would have returned three rows if DISTINCT had not been used. So the trick was in DISTINCT keyword.
This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:
Two tables:
Table 1:
Orders
Some of the fields:
ID
PropID
WorkOrderNum
OrderDesc
DateCompleted
Table 2:
OrderDetail
ID
OrderID
TenantName
As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.
Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):
SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'
Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.
thanks.Acciording to your explanation,
1. PropertyLocations has 1:many relation to ORDERS,
2. Orders has 1:many relation to OrderDetail.
My first try would be like below for TenantName:
SELECT Distinct
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID, Orders.PropID, Orders.WorkOrderNum,
Orders.OrderDesc, Orders.DateCompleted
FROM PropertyLocations INNER JOIN Orders
ON PropertyLocations.ID = Orders.PropID
INNER JOIN OrderDetail
ON Orders.ID = OrderDetail.OrderID
WHERE Orders.OrderDesc LIKE '%lds%'
AND OrderDetail.TenantName LIKE 'Steve%'|||That's almost it. It's pulling up all my orders that has the specified 'tenantname' (that is great!)...but it's showing duplicate orders.
For instance:
I have a record in my 'Orders' table of ID=2886. And in my 'OrderDetail' table, there are 3 records that have the characters 'dr.' in it that all have the value of 2886 in the 'OrderID' field which means that they all belong to the record in the 'Orders' table with ID of 2886 that I mentioned at the beginning of this paragraph. So it is showing me my data from the 'Orders' table 3 times (duplicates).
I also modified your statement slightly:
SELECT Distinct PropertyLocations.PropertyLocation, OrderDetail.TenantName, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC
I'm only doing 1 'LIKE' at a time...but that's probably my fault...I wasn't real clear about it.
So how do I make it show me just a single instance of a record from the 'Orders' table even though that there may be several records in the 'OrderDetail' table that matches the search criteria?
P.S. Thank you for reminding me about trying to be SQL99 compliant. ;)
thanks.|||The reason that you do have duplicates is because of Orders.DateCompleted even if you have DISTINCT in the SELECT statement.
Therefore my next question would be
1. Do you need Orders.DateCompleted to display?
2. If YES, then duplicates are unavoidable.
3. If NO, then remove Orders.DateCompleted from the SELECT statement. - Problem solved.
In case of YES,
1. Do the dates of Orders.DateCompleted fall into the same date?
If YES, then use CAST(Orders.DateCompleted as varchar(11)) to pick up only the date portion that ignores time portion of the Orders.DateCompleted. - Problem solved.
If NO, then still duplicates are unavoidable.|||Well, I tried removing the Orders.DateCompleted field even though I really wanted it, but it still gave me the same results.
I included 2 screenshots now, one w/DateCompleted so everyone can see exactly what I'm getting, and my SQL statement, and then 1 after I removed the Orders.DateCompleted field.
Just to be sure that everyone knows what I mean when I say duplicates, I mean that the same exact Work Order (which is a row...or an order...or record) is showing up multiple times even though only 1 actually exists in the database. You can see this in the screenshots - there are 3 rows of the WO#6997 which is far from correct - there is only 1 record in the database with that Work Order Number. But there are 3 unique records in the OrderDetail table that contain the characters 'dr.' in it that belong to that WO#6997 work order. (And just to clarify, the WorkOrderNum is not my 'unique id' field in the 'Orders' table, nor is it the field that is 'linked' with the OrderDetail table)
If the above is what everyone already realized/knew, then cool, but if it wasn't, then maybe it will help clear things up for someone to help a little easier.
thanks.|||Let me clarify
1. You have one record in the Order table with WO#6997.
2. You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
3. WO# in the Order table is not unique.
4. WO# in the OrderDetail is not unique.
OK.
First, my previous email regarding the Orders.DateCompleted was incorrect. Because it belongs to Orders table, it wouldn't generate duplicates. So please ignore my suggestion to remove the column Orders.DateCompleted.
Then please show me the TenantNames. Are they the same?
If they are different, obviously you will have 3 records.
Inside the SELECT statement, you have the following columns to display:
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted
Among them, only TenantName comes from OrderDetail table.
Therefore if TenantName is the same it shouldn't display three records.
I am puzzled and very curious on the result for the TenantName.|||1. Yes
2. Kind of...The OrderDetail table doesn't actually have a WO# field in it. It has an OrderID field that corresponds to the ID field of the Orders table (which contains a WO# field)...but yes, there are 3 records that show up from the OrderDetail field with a TenantName like '%dr.%' that belong to the record that contains WO#6997 in the Orders table
3. Actually, WO# is unique (although there might be some records in the table ('Orders' table as I mentioned), that don't have a value for this field...but the records would be so old, it will never come up...so is it still considered 'unique'?), but isn't my identity field. I have an ID field for that.
4. As I mentioned, the OrderDetail table does not have a WO# field.
Ok, no problem.
Below are the 3 records that came up that matched the criteria:
The following data is from the TenantName field, and yes, as you can see, are unique. They all share the same OrderID of 2886...which is the ID of the Orders table. So I guess this is where the problem is? See, I don't really care that 3 records from the OrderDetail table matches my search criteria...all I need to know is that at least one record matches, and then I want to show the Order/Work Order from the Orders table that it corresponds to...1 time.
At Napa 9, 870 Napa Valley Corp. Dr.:
Work to be done at: [nl]870 Napa Valley Corp.Dr./Napa 9[nl]2511-2515 Napa Valley Corp. Dr./The Vines[nl]~+
At The Vines, 2511-2515 Napa Valley Corp. Dr.:
Yes, those are the columns I would like to display, and yes, TenantName is the only one that comes from the OrderDetail table.
As you see, the 3 records aren't the same that match the search criteria.|||Now I can see whole picture.
You have one record in the Order table with WO#6997.
You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
ID is primary key in Order table.
OrderID in the OrderDetail is foreign key to Order table.
Inside the SELECT statement, you have the following columns to display:
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted
But OrderDetail.TenantName are not the same. Therefore you have to force it to be the same by using the criteria.
So
Select Distinct
PropertyLocations.PropertyLocation,
'Like ''%Dr.%''' as [TenantName Criteria],
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompletedFROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC
I hope this should satisfy your requirements.|||Cool, I'm sorry it took so long to get there.
1. Yes
2. Yes, in a way...see #2 from my previous post
3. Yes
4. Yes
Ok, this is where you're going to want to kill me. I just realized after re-reading your post...I don't actually need to display OrderDetail.TenantName. You can even see that I'm not showing that field/column in my screenshots...silly me.
So I tried one of your previous SELECT statements, and removed it (the OrderDetail.TenantName part) from the beginning so as to not display it, and it worked! I'm so sorry for all of the confusion and the extra steps.
But out of not wanting all your hard work to not be in vain, I tried your last statement, and it worked too! So now I know what to do if I have a similar situation. Although, I do have a question...I've never seen part of your statement before:
'Like ''%Dr.%''' as [TenantName Criteria],
What is that all about? Obviously it works, but it's new to me. I also don't really understand why it would've worked without showing the OrderDetail.TenantName column.
Thanks for sticking with me and all of your help, TerryP.|||I am glad that I could help you.
Your question:
'Like ''%Dr.%''' as [TenantName Criteria],
This is a constant column. Because the TenantName is not actually the name of the tenant but a memo or description, I tried to make a common string for TenantName which comes from the criteria in the WHERE clause. When it displayed, all three records would have 'Like ''%Dr.%'''. So I could eliminate multiple records of the same constant string to a single record by using DISTINCT keyword in SELECT clause.
I also don't really understand why it would've worked without showing the OrderDetail.TenantName column
You do not have to display all or any of the columns of tables used in FROM clause. But it would have returned three rows if DISTINCT had not been used. So the trick was in DISTINCT keyword.
Monday, March 26, 2012
Help with assigning variables to from a SQL query
I've reconfigured Microsoft's IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:
Dim strSQL as String = "Select orderID, modelNumber from orderDetails" & _
"where CustomerID = " & User.Identity.Name & _
"And orderid = (SELECT MAX(orderid)FROM orderDetails" & _
"where CustomerID = " & User.Identity.Name & ")"
What I would like to do is assign specific values to variables based off of the above query. For example:
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this?? Any help is much appreciated! Thanks in advance.
RonI'm not fluent in VB, but try to grasp the outline of the code :)
[code]
Dim myConnection As SqlConnection = New SqlConnection("..my connection string..")
Dim myCommand As SqlCommand = New SqlCommand("the query...")
myCommand.Connection = myConnection
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
'String Builder objects speed up performance, as strings are immutable.
Dim modelNumbers As System.Text.StringBuilder = New System.Text.StringBuilder
Dim orderID As System.Text.StringBuilder = New System.Text.StringBuilder
' Go through all the rows of the query
While (myReader.Read())
modelNumbers.Append(myReader.GetString(0)) 'Add the ModelNumber of the row to the string
orderID.Append(myReader.GetString(1)) ' Add the OrderID of the row to the string
End While
myReader.Close()
myConnection.Close()
[/code]
HTH|||Thanks for your help.
The code creates this error: "Specified cast is not valid", from the following line:
modelNumbers.Append(myReader.GetString(0)) 'Add the ModelNumber of the row to the string
Any ideas?
Again, thanks for your help!
Ron|||The field is either null or its a byte field or something.
I would check if its null first before adding it to the string.
HTH
Tony|||It doesn't come back null. To test this I simply bound the query data to a datagrid in order to display the information on the page. There are no null items; only data...as expected|||Try
modelNumbers.Append(myReader(0).ToString())
Does this help|||This worked beautifully.
Thank you!
Help with assigning values to variables
With some help Jacco Schalkwijk (SQL Server MVP), I've been able to query
accurate data from my shopping cart database. This query collects customer
purchase information based on the current session and userID. The query
Dim objCommand as SqlCommand = New SqlCommand("Select orderID, modelNumber from orderDetails where CustomerID = " &
User.Identity.Name & " And orderid = (SELECT MAX(orderid) FROM orderDetails where CustomerID = " &
User.Identity.Name & ")"
Now that I've collected all the orderIDâ's associated with the current customer,
I would like to assign specific values to variables based on this query. I would
like to create something like the following
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this' Any help is much appreciated! Thanks in advance.
Ro=?Utf-8?B?Um9u?= (ron.r.williams@.comcast.net) writes:
> I would like to assign specific values to variables based on this query.
> I would like to create something like the following:
> Dim orderItem as String = (all of the modelNumbers from the query)
> Dim orderIdItem as String = (all of the orderIDs from the query)
> How do I do this' Any help is much appreciated! Thanks in advance.
You would have to iterate over the dataset to build this string. So this
is more of a VB .Net question than an SQL question. I should add that
doing this in SQL is possible, but it is not a good idea.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
accurate data from my shopping cart database. This query collects customer
purchase information based on the current session and userID. The query
Dim objCommand as SqlCommand = New SqlCommand("Select orderID, modelNumber from orderDetails where CustomerID = " &
User.Identity.Name & " And orderid = (SELECT MAX(orderid) FROM orderDetails where CustomerID = " &
User.Identity.Name & ")"
Now that I've collected all the orderIDâ's associated with the current customer,
I would like to assign specific values to variables based on this query. I would
like to create something like the following
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this' Any help is much appreciated! Thanks in advance.
Ro=?Utf-8?B?Um9u?= (ron.r.williams@.comcast.net) writes:
> I would like to assign specific values to variables based on this query.
> I would like to create something like the following:
> Dim orderItem as String = (all of the modelNumbers from the query)
> Dim orderIdItem as String = (all of the orderIDs from the query)
> How do I do this' Any help is much appreciated! Thanks in advance.
You would have to iterate over the dataset to build this string. So this
is more of a VB .Net question than an SQL question. I should add that
doing this in SQL is possible, but it is not a good idea.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Help With Another UPDATE Query Please!
Hi,
I am making some alterations to my Database. I have a table called projects
and a table called Work_Types. Projects currently contains the name of the
work type (Work_Type) but now I want to change this so it contains the
Work_Type_ID, is it possible to update Projects with one query?
Thanks for your helpALTER TABLE Projects ADD work_type_id INTEGER NULL
REFERENCES Work_Types (work_type_id)
UPDATE Projects
SET work_type_id =
(SELECT work_type_id
FROM Work_Types
WHERE work_type = Projects.work_type)
ALTER TABLE Projects DROP COLUMN work_type
ALTER TABLE Projects ALTER COLUMN work_type_id INTEGER NOT NULL
(untested and based on assumed DDL).
--
David Portas
----
Please reply only to the newsgroup
--
Help With Another Query
Hello and Thank You for your previous help,
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
ChuckHi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
ChuckHi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>
Help With Another Query
Hello and Thank You for your previous help,
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
Chuck
Hi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
Chuck
Hi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>
Help With Another Query
Hello and Thank You for your previous help,
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
ChuckHi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
ChuckHi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>
Help with AND and OR query
I am having trouble with the below query. This is attached to a SQLDataAdapter which in turn is connected to a grid view.
@.pram5 is a dropdownlist
all other perameters such as @.nw in the big OR statement are check boxes.
My tables look similar to this:
Company TblComodity TblRegion
---- ----- -----
PK CompanyID PK CommodityID PK RegionID
CompanyName FK CompanyID FK CompanyID
CommodityName North
South, East, etc
What I would am trying to do is have a user slect a commodity which is a distinct value from the comodity table. Then select by tick boxes locations, then in the grid view companies with possible locations and commoditys appear (matching record for commodity name and True values for any one particular location). My problem is even when I select a commodity and leave all tick boxes blank (false) the records still display for the selected commodity- like its only filltering on commodity name. Can anyone help ? I can provide more info if needed
Another little example of the above in case you dont understand.
Say in the dopdown list you choose "Buildings" and out of all the check box values you only choose Scotland, and North the record should still be returned if North is False and Scotland is True.
Here is my query:
SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.NorthWest, TblRegion.NorthEast, TblRegion.SouthEast, TblRegion.SouthWest,
TblRegion.Scotland, TblRegion.Wales, TblRegion.Midlands, TblRegion.UKNational, TblRegion.EuropOotherThanUK, TblComodity.ComName
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID INNER JOIN
TblComodity ON TblCompany.CompanyID = TblComodity.CompanyID AND TblComodity.ComName = @.pram5
WHERE (TblRegion.NorthWest = @.nw) OR
(TblRegion.NorthEast = @.NE) OR
(TblRegion.SouthEast = @.se) OR
(TblRegion.SouthWest = @.sw) OR
(TblRegion.Scotland = @.scot) OR
(TblRegion.Wales = @.wal) OR
(TblRegion.Midlands = @.mid) OR
(TblRegion.EuropOotherThanUK = @.EU) AND (TblRegion.UKNational = @.UKN)
INNER JOIN evaluates if only the condition on both the tables is matched. In your case, you need to use OUTER JOIN (LEFT OR RIGHT) so that the records from left/right table are fetched even if the condition in right/left table fails. In your query change the INNER JOIN to LEFT OUTER JOIN
Thanks
|||Thank you, I am new to this and use the query designer as I am learning. What you have said has taught me somthing new, We it be possible to modify my query to include your suggestion of the Left Outer Join?
Many thanks,
Adam.
|||I re-read your question and looks like you have nothing to do with the OUTER JOINS. Try this
SELECT TblCompany.CompanyName, TblComodity.CommodityName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE TblComodity.CommodityName = @.pram5 AND TblRegion.NorthWest = @.nw
In the above query i used only one region. If you want to add more regions use AND
Thanks
|||
Hi,
Thats almost what I want but I need OR's for example if you have a Commodity Name called buildins and you select TRUE values for North East and South and the particular commodity only has a TRUE value for South I still would like the record returned. It would work similar to say a holiday web site where you would choose a country and say select multiple regions and all records for regions would be returned. Do you understand?
|||Did you try using the query i provided with OR with which you can get the desired results.
Thanks
|||Your code gives me all results for some strange reason if I make pram5 = 'buildings' and @.nw = 'true'
returns
Company Commodity Northwest
SELECT TblCompany.CompanyName, TblComodity.ComName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE (TblComodity.ComName = @.pram5) OR
(TblRegion.NorthWest = @.nw)
Subscribe to:
Posts (Atom)