I am trying to write a query in Transact-SQL to create a user view in my SQL
database. I am trying to populate columns for each "aging" category (30, 60,
90, etc), so the correct age receives the amount due, but other columns are
zero. However, I can't find the correct CASE or IF...THEN syntax. It needs t
o
do something equal to the following:
If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
CurrentDue = 0.
If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else 30DayDue
= 0...
etc
I would be grateful for help...Thank you.Try using CASE expressions like the snippet below. See the Books Online for
more info.
SELECT
CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END AS
"CurrentDue",
CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END AS
"30DayDue"
etc...
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>I am trying to write a query in Transact-SQL to create a user view in my
>SQL
> database. I am trying to populate columns for each "aging" category (30,
> 60,
> 90, etc), so the correct age receives the amount due, but other columns
> are
> zero. However, I can't find the correct CASE or IF...THEN syntax. It needs
> to
> do something equal to the following:
> If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
> CurrentDue = 0.
> If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else
> 30DayDue
> = 0...
> etc
> I would be grateful for help...Thank you.
>|||Thank you. That should do it. I did not see an example in the on-line books
of using CASE to populate a quantity in a column (thought it may have been
there).
"Dan Guzman" wrote:
> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29 I
want to update CurrentPatientDue to PatientDue but at the same time also
update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
writing another full CASE statement?
"Dan Guzman" wrote:
> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||The important concept here is that SQL CASE is an expression and not a
statement. It simply returns a single value conditionally so you need to
replicate the CASE expression. If you are using SQL 2000, you can
encapsulate CASE in a user-defined scalar function like the example below.
CREATE FUNCTION dbo.AgeAmount
(
LowAge int,
HighAge int,
Age int,
Value int
)
RETURNS int
AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
GO
SELECT
dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
"CurrentInsuranceDue"
etc..
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
> Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29
> I
> want to update CurrentPatientDue to PatientDue but at the same time also
> update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
> writing another full CASE statement?
> "Dan Guzman" wrote:
>|||This opens up a whole new area for me to learn and use. However, my first
attempt is producing an error I don't understand. Instead of declaring the
function every time, I thought I would attempt to create a "User Defined
Function". In the properties dialog I believe I am using your text as follow
s:
CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value int)
RETURNS int AS
BEGIN
(RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END)
END
GO
When I try to save this or check the syntax, the error is: "Must declare the
variable @.age". Can you help me with this as well, please?
"Dan Guzman" wrote:
> The important concept here is that SQL CASE is an expression and not a
> statement. It simply returns a single value conditionally so you need to
> replicate the CASE expression. If you are using SQL 2000, you can
> encapsulate CASE in a user-defined scalar function like the example below.
>
> CREATE FUNCTION dbo.AgeAmount
> (
> LowAge int,
> HighAge int,
> Age int,
> Value int
> )
> RETURNS int
> AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> GO
> SELECT
> dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
> dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
> dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
> "CurrentInsuranceDue"
> etc..
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
>
>|||Try running the following using Query Analyzer:
CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
int)
RETURNS int AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
> This opens up a whole new area for me to learn and use. However, my first
> attempt is producing an error I don't understand. Instead of declaring the
> function every time, I thought I would attempt to create a "User Defined
> Function". In the properties dialog I believe I am using your text as
> follows:
> CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value
> int)
> RETURNS int AS
> BEGIN
> (RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END)
> END
> GO
> When I try to save this or check the syntax, the error is: "Must declare
> the
> variable @.age". Can you help me with this as well, please?
> "Dan Guzman" wrote:
>|||Hello Dan,
OK I'm in business. However, I ran the script in Query Analyzer. It ran
successfully, but I could not find my function, even though I'm sure I was
accessing the correct database. So, I opened a new user defined function,
deleted the standard text and inserted your text. This saved with no errors
and I am using the function now. Thank you for introducing me to this
interesting area of SQL 2000.
"Dan Guzman" wrote:
> Try running the following using Query Analyzer:
> CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
> int)
> RETURNS int AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
>
>
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Create a script rename value of a column
Hi
Is there a way, to make a script that changes the values of a colum
I have a tabel called "fasesrt
That tabel has 3 columns
Fase & Fasesrtom & fasegrpn
In the column fasesrtom I have 6 values that I want to change
I know the old value and the new valu
Can Anyone help me to make a script, If I have a start, I think I can do it
I just need a start
Thank
JoUPDATE Fasesrt
SET fasesrtom = CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
--
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
Is there a way, to make a script that changes the values of a colum
I have a tabel called "fasesrt
That tabel has 3 columns
Fase & Fasesrtom & fasegrpn
In the column fasesrtom I have 6 values that I want to change
I know the old value and the new valu
Can Anyone help me to make a script, If I have a start, I think I can do it
I just need a start
Thank
JoUPDATE Fasesrt
SET fasesrtom = CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
--
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
create a new column
HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.
Thursday, March 22, 2012
Create a Database
Hello,
I would like just to create a database using SQL. I am currently using Microsoft Visual Studio 6.0. I am currently able to add columns and tables. Change the data in the DB, but again I am not able to create the database. It does no seem that it should be that hard. Thanks for the help.The Database needs to be a Microsoft Access Database|||In your project, reference the Access object library.
In your code, use
Dim accessApp As New Access.Application
accessApp.NewCurrentDatabase ("c:\new.mdb")
to create a new mdb.sql
I would like just to create a database using SQL. I am currently using Microsoft Visual Studio 6.0. I am currently able to add columns and tables. Change the data in the DB, but again I am not able to create the database. It does no seem that it should be that hard. Thanks for the help.The Database needs to be a Microsoft Access Database|||In your project, reference the Access object library.
In your code, use
Dim accessApp As New Access.Application
accessApp.NewCurrentDatabase ("c:\new.mdb")
to create a new mdb.sql
Tuesday, March 20, 2012
Create a cube in analysis services out of more than one fact table.
Is it possible to create a cube in analysis services that has columns from
more than one fact table as long as the fact tables share the same data
grain and dimensions?
Thanks for any advice.
Regards,
Anthony
Hi Anthony,
In AS 2005, this is support via the UDM.
In AS 2000, create two Cubes (one from each fact table) then create a
virtual cube to link the two.
If the facts are at the same grain and have the same dimensions, consider
moving the measures into one fact table.
regards
Jamie
"anthony garcia" <acgarcia21@.verizon.net> wrote in message
news:ZISch.7793$Ga7.2309@.trnddc01...
> Is it possible to create a cube in analysis services that has columns from
> more than one fact table as long as the fact tables share the same data
> grain and dimensions?
> Thanks for any advice.
> Regards,
> Anthony
>
more than one fact table as long as the fact tables share the same data
grain and dimensions?
Thanks for any advice.
Regards,
Anthony
Hi Anthony,
In AS 2005, this is support via the UDM.
In AS 2000, create two Cubes (one from each fact table) then create a
virtual cube to link the two.
If the facts are at the same grain and have the same dimensions, consider
moving the measures into one fact table.
regards
Jamie
"anthony garcia" <acgarcia21@.verizon.net> wrote in message
news:ZISch.7793$Ga7.2309@.trnddc01...
> Is it possible to create a cube in analysis services that has columns from
> more than one fact table as long as the fact tables share the same data
> grain and dimensions?
> Thanks for any advice.
> Regards,
> Anthony
>
Create a cube in analysis services out of more than one fact table.
Is it possible to create a cube in analysis services that has columns from
more than one fact table as long as the fact tables share the same data
grain and dimensions?
Thanks for any advice.
Regards,
AnthonyHi Anthony,
In AS 2005, this is support via the UDM.
In AS 2000, create two Cubes (one from each fact table) then create a
virtual cube to link the two.
If the facts are at the same grain and have the same dimensions, consider
moving the measures into one fact table.
regards
Jamie
"anthony garcia" <acgarcia21@.verizon.net> wrote in message
news:ZISch.7793$Ga7.2309@.trnddc01...
> Is it possible to create a cube in analysis services that has columns from
> more than one fact table as long as the fact tables share the same data
> grain and dimensions?
> Thanks for any advice.
> Regards,
> Anthony
>
more than one fact table as long as the fact tables share the same data
grain and dimensions?
Thanks for any advice.
Regards,
AnthonyHi Anthony,
In AS 2005, this is support via the UDM.
In AS 2000, create two Cubes (one from each fact table) then create a
virtual cube to link the two.
If the facts are at the same grain and have the same dimensions, consider
moving the measures into one fact table.
regards
Jamie
"anthony garcia" <acgarcia21@.verizon.net> wrote in message
news:ZISch.7793$Ga7.2309@.trnddc01...
> Is it possible to create a cube in analysis services that has columns from
> more than one fact table as long as the fact tables share the same data
> grain and dimensions?
> Thanks for any advice.
> Regards,
> Anthony
>
Create 2 publications for same table
Hi
I created a DB named 'TestDB' and created a table called Users. This user table having 3 columns (Uname and pwd,version).
I need to create two publication for this user table.
1) Create a publication using all columns.
2) create a publication using Uname and pwd (not version column).
I am using Merge publication.
When I create first publication (any one - all 3 columns or any 2 coulmns) it create successfully.
When I create second publication it throws error. The details are below.
TITLE: New Publication Wizard
SQL Server Management Studio could not create article 'Users'.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
An article with a different subscriber_upload_options value already exists for object '[dbo].[Users]'.
Changed database context to 'TestDB'. (Microsoft SQL Server, Error: 20053)
How can i create the second publication? Is it possible? If yes, please give me the steps.
Thanks
Hi
Got the solution. Solved the problem.
The problem was when i create the second publication I changed the article synchronization direction. thats why it throws the error. After givig the same article synchronization direction it is working.
Monday, March 19, 2012
Crazy SQL statement help needed... converting the rows to columns (sort of)...
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).
Employees table:
empName
Jane Doe
Alton Brown
John Doe
TrainingCourse table:
courseName
Welding
Brain Surgery
Scuba Diving
Results table:
empName: courseName: completeDate:
Jane Doe Welding 2/2/2002
Jane Doe Brain Surgery 3/7/2005
Alton Brown Scuba Diving 9/23/2004
Alton Brown Welding 11/4/2004
John Doe Brain Surgery 6/14/2003
End result of select statement:
Welding Brain Surgery Scuba Diving
Jane Doe 2/2/2002 3/7/2005
Alton Brown 11/4/2004 9/23/2004
John Doe 6/14/2003
Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
--For SQL Server 2005, there is a pivot function to do this
select pt.empName, pt.[Welding]as [Welding], pt.[Brain Surgery]as [Brain Surgery],pt.[Scuba Diving]as [Scuba Diving]
from dbo.coursePivot$as t
pivot(min(t.completeDate)for t.courseNamein([Welding], [Brain Surgery],[Scuba Diving]))as pt
--For SQL Server 2000 use case
SELECT pvt.empName
,MAX(CASE pvt.courseNameWHEN'Welding'THEN completeDateEND)AS [Welding]
,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN completeDateEND)AS [Brain Surgery]
,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN completeDateEND)AS [Scuba Diving]
FROM dbo.coursePivot$AS pvt
GROUPBY pvt.empName
--Either way, you need hardcode your course names to get the pivot result table.
|||Thank you very much for your reply. I will give this a shot.|||Okay, I just got a chance to try this, but how do I get this to work with multiple tables (I'm using 2000)? All examples I find don't really make that distinction. For example how does it determine where to get the pvt.empName from? Should it be:
SELECT dbo.Employees.empName
,MAX(CASE pvt.courseNameWHEN'Welding'THEN dbo.Results.completeDateEND)AS [Welding]
,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN dbo.Results.completeDateEND)AS [Brain Surgery]
,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN dbo.Results.completeDateEND)AS [Scuba Diving]
FROM dbo.coursePivot$AS pvt
GROUPBY pvt.empName
Thanks Again.
|||"how do I get this to work with multiple tables (I'm using 2000)? "
Could you post the tables with a set of data along with your expect result? That would be easy to solve and explain. Thanks.
|||One other thing: I get this error "The Query Designer does not support the CASE SQL construct."
|||Yes. But it will run.|||Thanks limno for all your help. Because of the amount of work that has to be done in order to get this to work on the real tables, doing this using a pivot would still require us to run about 5000 queries, and it would remove the dynamic needs of the system, such as having to add new columns manually. My team and I have thought about it and are trying to come up with a way to query the database and create an multidimensional array of the course names and IDs, query the employee database and get all of the employee IDs and names, then finally, query the results table and get the employee ID, the course ID, and date (again, stored in a multidimensional array. I can then come up with a way to compare the values within a function that draws the table (I'll have to figure that out as well). The idea is that memory is cheaper than server processing from an efficiency standpoint.
So I now have two new questions:
1. I know how to store a dataset as an array, but how do I store a dataset as a multidimensional array?
2. (non-Sql): How can I compare, say the second values of multidimensional arrays, meaning if I have two multidimensional arrays, like [1,3] and [7,3], how can I check to see if the two 3's are equal?
Thanks again for all the help so far and to anyone willing to share more insight.
|||Take a look at this article on my site:http://www.theabstractionpoint.com/dynamiccolumns.asp
This is one approach I have used. It is built for SQL Server 2005 (using dynamic SQL and the PIVOT statement available in 2005), but you could adjust it to dynamically generate the CASE statements you've already been shown. It might give you some ideas, at least.
Sunday, February 19, 2012
counting rows by date (was "Help on Query")
I hate to ask such silly helps..but I'm missing something here..need help.
I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help
SELECT
CAST(createddate AS DATETIME) AS createdDate,
Created,
Deleted,
Remaining
FROM(
SELECT
CONVERT(VARCHAR,createdon,102) AS CreatedDate,
COUNT(1) created,
(SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)) Deleted,
((SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
(SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC)
AS tmpFirst, your outer select is completely unnecessary. All it does is display the data exactly as it is returned from the inner query. Drop it.
Second, please use count(*) instead of count(1). Why? Just to make the rest of us happy. That's why.
Third, use the DATEDIFF function instead of subtracting an integer from GETDATE().
Fourth, lets hope to God your table has a primary key, which in keeping with your naming convention we will name "primarykey".
Then see if this doesn't run faster:
select Convert(char(10), ta1.createdon, 120) CreateDate,
count(distinct ta1.primarykey) Created,
count(distinct ta2.primarykey) Deleted,
count(distinct ta3.primarykey) - count(distinct ta4.primarykey) Remaining
from table ta1
left outer join table ta2 on datediff(day, ta2.deletedon, ta1.createdon) = 1
left outer join table ta3 on datediff(day, ta3.createdon, ta1.createdon) >= 0
left outer join table ta4 on datediff(day, ta4.deletedon, ta1.createdon) >= 0
where datediff(day, ta1.createdon, getdate()) <= 90
group by Convert(char(10), ta1.createdon, 120)
order by Convert(char(10), ta1.createdon, 120)|||My primary key is a uniqueid, so this query gives an error: count unique aggregate operation cannot take uniqueidentifier datatype as argument.|||Try count(Distinct cast([YourPrimaryKey] as char(36))|||Well. I tried that. It worked, but it takes a lot more time than before, could be because of outer joins. I am trying to replace the date comparision with datediff, but getting 'not contained in aggregate function or group by' error. still working out|||This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.|||Originally posted by blindman
This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.
Really? You give us too much credit...
I'd like to see the DDL AND an explanation of what you're trying to do...
IN Business terms...nothing technical
Sample data would be helpful as well
Oh and what the final result should be...
Do that and THEN I'll agree with the Blind dude...
(amazing how he can find the keys...no?)
SELECT
CAST(createddate AS DATETIME) AS createdDate
, Created
, Deleted
, Remaining
FROM ( SELECT
, CONVERT(VARCHAR,createdon,102) AS CreatedDate
, COUNT(1) created
, ( SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)
) Deleted
, (( SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
( SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC
) AS tmp|||Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.|||Do you know how to script the DDL for the table?
Post it here...|||CREATE TABLE [dbo].[Order] (
[OrderId] [uniqueidentifier] NOT NULL ,
[CustomerNo] [varchar] (100) NULL ,
[Address] [varchar] (255) NULL ,
[DocNo] [varchar] (100) NULL ,
[DocDate] [datetime] NULL ,
[OrderType] [uniqueidentifier] NULL ,
[SalesRep] [uniqueidentifier] NULL ,
[Value] [money] NULL ,
[Comments] [varchar] (2048) NULL ,
[CreatedBy] [uniqueidentifier] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [uniqueidentifier] NULL ,
[UpdatedOn] [datetime] NULL ,
[Deleted] [bit] NOT NULL ,
[DeletedBy] [uniqueidentifier] NULL ,
[DeletedOn] [datetime] NULL ,
[Status] [char] (1) NULL,
CONSTRAINT [I_ORDER] PRIMARY KEY CLUSTERED
(
[OrderId]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]|||Originally posted by nanadmin
Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.
Well...is that the difference of the 2?
Also, you mention you have a column that stores 0 or 1 to show that it's logically deleted...
But doesn't the existence of a date in the DeletedOn column infer that?
Same thing with the CreatedOn Column for New Orders?
Yes?|||It is not the difference of 2. Because, there may be 10 orders created yesterday and 5 today; and if 6 were deleted yesterday and 4 today, then my end of day remaining count will be 4 for yesterday and 5 for today. The result would be 10,6,4 and 5,4,5
'Deleted' column has the value 0 or 1. Deleted date is enough, but I needed a flag for other querying purposes, since it is programatically good to check (deleted =1) instead of (deleteddate <> null)|||1. From DDL statement there is only index on the table - PK by order ID. So your query are processed like 4 full table scans.
2. Do not compare DateTime fields this way: CONVERT(VARCHAR,ta2.deletedon,102) = CONVERT(VARCHAR,ta.createdon,102). This way query engine cannot use indexes and it lowering comparision performance.
3. If create indexes on createdon and deletedon fields and remove CONVERT on comparision operations your select would be much faster.|||Palex, welcome to the forum. But if you are going to start responding to year-old posts then you are going to have a lot of catching up to do.
Hope your calendar is clear for the next month or two...
I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help
SELECT
CAST(createddate AS DATETIME) AS createdDate,
Created,
Deleted,
Remaining
FROM(
SELECT
CONVERT(VARCHAR,createdon,102) AS CreatedDate,
COUNT(1) created,
(SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)) Deleted,
((SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
(SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC)
AS tmpFirst, your outer select is completely unnecessary. All it does is display the data exactly as it is returned from the inner query. Drop it.
Second, please use count(*) instead of count(1). Why? Just to make the rest of us happy. That's why.
Third, use the DATEDIFF function instead of subtracting an integer from GETDATE().
Fourth, lets hope to God your table has a primary key, which in keeping with your naming convention we will name "primarykey".
Then see if this doesn't run faster:
select Convert(char(10), ta1.createdon, 120) CreateDate,
count(distinct ta1.primarykey) Created,
count(distinct ta2.primarykey) Deleted,
count(distinct ta3.primarykey) - count(distinct ta4.primarykey) Remaining
from table ta1
left outer join table ta2 on datediff(day, ta2.deletedon, ta1.createdon) = 1
left outer join table ta3 on datediff(day, ta3.createdon, ta1.createdon) >= 0
left outer join table ta4 on datediff(day, ta4.deletedon, ta1.createdon) >= 0
where datediff(day, ta1.createdon, getdate()) <= 90
group by Convert(char(10), ta1.createdon, 120)
order by Convert(char(10), ta1.createdon, 120)|||My primary key is a uniqueid, so this query gives an error: count unique aggregate operation cannot take uniqueidentifier datatype as argument.|||Try count(Distinct cast([YourPrimaryKey] as char(36))|||Well. I tried that. It worked, but it takes a lot more time than before, could be because of outer joins. I am trying to replace the date comparision with datediff, but getting 'not contained in aggregate function or group by' error. still working out|||This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.|||Originally posted by blindman
This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.
Really? You give us too much credit...
I'd like to see the DDL AND an explanation of what you're trying to do...
IN Business terms...nothing technical
Sample data would be helpful as well
Oh and what the final result should be...
Do that and THEN I'll agree with the Blind dude...
(amazing how he can find the keys...no?)
SELECT
CAST(createddate AS DATETIME) AS createdDate
, Created
, Deleted
, Remaining
FROM ( SELECT
, CONVERT(VARCHAR,createdon,102) AS CreatedDate
, COUNT(1) created
, ( SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)
) Deleted
, (( SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
( SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC
) AS tmp|||Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.|||Do you know how to script the DDL for the table?
Post it here...|||CREATE TABLE [dbo].[Order] (
[OrderId] [uniqueidentifier] NOT NULL ,
[CustomerNo] [varchar] (100) NULL ,
[Address] [varchar] (255) NULL ,
[DocNo] [varchar] (100) NULL ,
[DocDate] [datetime] NULL ,
[OrderType] [uniqueidentifier] NULL ,
[SalesRep] [uniqueidentifier] NULL ,
[Value] [money] NULL ,
[Comments] [varchar] (2048) NULL ,
[CreatedBy] [uniqueidentifier] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [uniqueidentifier] NULL ,
[UpdatedOn] [datetime] NULL ,
[Deleted] [bit] NOT NULL ,
[DeletedBy] [uniqueidentifier] NULL ,
[DeletedOn] [datetime] NULL ,
[Status] [char] (1) NULL,
CONSTRAINT [I_ORDER] PRIMARY KEY CLUSTERED
(
[OrderId]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]|||Originally posted by nanadmin
Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.
Well...is that the difference of the 2?
Also, you mention you have a column that stores 0 or 1 to show that it's logically deleted...
But doesn't the existence of a date in the DeletedOn column infer that?
Same thing with the CreatedOn Column for New Orders?
Yes?|||It is not the difference of 2. Because, there may be 10 orders created yesterday and 5 today; and if 6 were deleted yesterday and 4 today, then my end of day remaining count will be 4 for yesterday and 5 for today. The result would be 10,6,4 and 5,4,5
'Deleted' column has the value 0 or 1. Deleted date is enough, but I needed a flag for other querying purposes, since it is programatically good to check (deleted =1) instead of (deleteddate <> null)|||1. From DDL statement there is only index on the table - PK by order ID. So your query are processed like 4 full table scans.
2. Do not compare DateTime fields this way: CONVERT(VARCHAR,ta2.deletedon,102) = CONVERT(VARCHAR,ta.createdon,102). This way query engine cannot use indexes and it lowering comparision performance.
3. If create indexes on createdon and deletedon fields and remove CONVERT on comparision operations your select would be much faster.|||Palex, welcome to the forum. But if you are going to start responding to year-old posts then you are going to have a lot of catching up to do.
Hope your calendar is clear for the next month or two...
Friday, February 17, 2012
Counting Rows
I have a simple report, listing patient names, and then columns depending on
certain condition. If they have the condition the column is True, and if
they don't the column is false.
The problem I have is trying to count how many have each condition. I
inserted the following expression in the footer:
=Count(cint(field!.condtion1.value)=-1)
If the column is true it does resolve to a -1, a 0 if it is false.
The expression just counts every row. It doesn't seem to evaluate the
expression.
Any help would be appreciated.
John HartJohn,
Try having the expression evaluate like this:
Count(iif(cint(field!.condtion1.value)=-1, 1, nothing))|||or try
SUM(iif(field!.condtion1.value = true, 1, nothing))
certain condition. If they have the condition the column is True, and if
they don't the column is false.
The problem I have is trying to count how many have each condition. I
inserted the following expression in the footer:
=Count(cint(field!.condtion1.value)=-1)
If the column is true it does resolve to a -1, a 0 if it is false.
The expression just counts every row. It doesn't seem to evaluate the
expression.
Any help would be appreciated.
John HartJohn,
Try having the expression evaluate like this:
Count(iif(cint(field!.condtion1.value)=-1, 1, nothing))|||or try
SUM(iif(field!.condtion1.value = true, 1, nothing))
Counting NULL columns?
If I had a basic table with 3 VARCHAR fields; let's say A, B, C
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right
the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls
3
2
0
1
0
0
0
2
0
3
0
2
1
at least my query actually produces something useful
:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D
And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.
... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable
Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.
Thanks Rudy and blindman!
FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern
suppose i had a Three Stooges table with this row:
'curly','larry',null
you're saying it's okay to overwrite this with
'tom','dick','harry'
but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:
Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey
This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!
-PatP|||Only when you are in the dark...|||nice
might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right
the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls
3
2
0
1
0
0
0
2
0
3
0
2
1
at least my query actually produces something useful
:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D
And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.
... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable
Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.
Thanks Rudy and blindman!
FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern
suppose i had a Three Stooges table with this row:
'curly','larry',null
you're saying it's okay to overwrite this with
'tom','dick','harry'
but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:
Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey
This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!
-PatP|||Only when you are in the dark...|||nice
might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?
Tuesday, February 14, 2012
Counting Date Query
I have a table called sv_call_log
The table contains columns row_id, card_num, start_date, end_date.
This table logs calls.
The start_date is when the call begins and end_date is when the call ends.
They are both fromatted as follows EX. 2004-09-20 15:55:29.247
We currently have 4 lines avliable for people to call in out.
Does anyone know how to write a query to show when more than 3 lines are
used at the same time? The query would have to see if 3 or more start_dates
occured between a start_date and end_date. I'm
on how I would
write this query or if there is even a way. Any help would be greatly
appreciated. Thank you.Having the entire table DDL would be very useful for us to provide
meaningful help. Otherwise we are just guessing...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:2EBC3CD5-95D8-49CF-B59B-49EBB78E508C@.microsoft.com...
>I have a table called sv_call_log
> The table contains columns row_id, card_num, start_date, end_date.
> This table logs calls.
> The start_date is when the call begins and end_date is when the call ends.
> They are both fromatted as follows EX. 2004-09-20 15:55:29.247
> We currently have 4 lines avliable for people to call in out.
> Does anyone know how to write a query to show when more than 3 lines are
> used at the same time? The query would have to see if 3 or more
> start_dates
> occured between a start_date and end_date. I'm on how I
> would
> write this query or if there is even a way. Any help would be greatly
> appreciated. Thank you.|||Hmm. Interesting.
Hopefully start_date and end_date are actually of datetime datatype,
and the formatting you describe is simply how they are displayed.
I think this will show you any call that starts while at least 2 other
calls are already in progress. If a fourth call starts there will be
rows for both the third and fourth.
--Each call matches itself, possibly others
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
start_date as start_overlap,
min(end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:
>I have a table called sv_call_log
>The table contains columns row_id, card_num, start_date, end_date.
>This table logs calls.
>The start_date is when the call begins and end_date is when the call ends.
>They are both fromatted as follows EX. 2004-09-20 15:55:29.247
>We currently have 4 lines avliable for people to call in out.
>Does anyone know how to write a query to show when more than 3 lines are
>used at the same time? The query would have to see if 3 or more start_date
s
>occured between a start_date and end_date. I'm on how I would
>write this query or if there is even a way. Any help would be greatly
>appreciated. Thank you.|||Ron,
When I run the query it throws back
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Start_Date'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'End_Date'.
"Roy Harvey" wrote:
> Hmm. Interesting.
> Hopefully start_date and end_date are actually of datetime datatype,
> and the formatting you describe is simply how they are displayed.
> I think this will show you any call that starts while at least 2 other
> calls are already in progress. If a fourth call starts there will be
> rows for both the third and fourth.
> --Each call matches itself, possibly others
> SELECT X.row_id,
> count(Y.row_id) as OverlapLevel,
> start_date as start_overlap,
> min(end_date) as end_overlap
> FROM CallLog as X
> JOIN CallLog as Y
> ON X.start_date BETWEEN Y.start_date AND Y.end_date
> GROUP BY X.row_id
> HAVING count(Y.row_id) > 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
> <tarheels4025@.discussions.microsoft.com> wrote:
>
>|||Sorry about that.
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
X.start_date as start_overlap,
min(Y.end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy
On Thu, 22 Jun 2006 12:55:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:
>Ron,
>When I run the query it throws back
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'Start_Date'.
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'End_Date'.
The table contains columns row_id, card_num, start_date, end_date.
This table logs calls.
The start_date is when the call begins and end_date is when the call ends.
They are both fromatted as follows EX. 2004-09-20 15:55:29.247
We currently have 4 lines avliable for people to call in out.
Does anyone know how to write a query to show when more than 3 lines are
used at the same time? The query would have to see if 3 or more start_dates
occured between a start_date and end_date. I'm
on how I wouldwrite this query or if there is even a way. Any help would be greatly
appreciated. Thank you.Having the entire table DDL would be very useful for us to provide
meaningful help. Otherwise we are just guessing...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:2EBC3CD5-95D8-49CF-B59B-49EBB78E508C@.microsoft.com...
>I have a table called sv_call_log
> The table contains columns row_id, card_num, start_date, end_date.
> This table logs calls.
> The start_date is when the call begins and end_date is when the call ends.
> They are both fromatted as follows EX. 2004-09-20 15:55:29.247
> We currently have 4 lines avliable for people to call in out.
> Does anyone know how to write a query to show when more than 3 lines are
> used at the same time? The query would have to see if 3 or more
> start_dates
> occured between a start_date and end_date. I'm
on how I> would
> write this query or if there is even a way. Any help would be greatly
> appreciated. Thank you.|||Hmm. Interesting.
Hopefully start_date and end_date are actually of datetime datatype,
and the formatting you describe is simply how they are displayed.
I think this will show you any call that starts while at least 2 other
calls are already in progress. If a fourth call starts there will be
rows for both the third and fourth.
--Each call matches itself, possibly others
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
start_date as start_overlap,
min(end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:
>I have a table called sv_call_log
>The table contains columns row_id, card_num, start_date, end_date.
>This table logs calls.
>The start_date is when the call begins and end_date is when the call ends.
>They are both fromatted as follows EX. 2004-09-20 15:55:29.247
>We currently have 4 lines avliable for people to call in out.
>Does anyone know how to write a query to show when more than 3 lines are
>used at the same time? The query would have to see if 3 or more start_date
s
>occured between a start_date and end_date. I'm
on how I would>write this query or if there is even a way. Any help would be greatly
>appreciated. Thank you.|||Ron,
When I run the query it throws back
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Start_Date'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'End_Date'.
"Roy Harvey" wrote:
> Hmm. Interesting.
> Hopefully start_date and end_date are actually of datetime datatype,
> and the formatting you describe is simply how they are displayed.
> I think this will show you any call that starts while at least 2 other
> calls are already in progress. If a fourth call starts there will be
> rows for both the third and fourth.
> --Each call matches itself, possibly others
> SELECT X.row_id,
> count(Y.row_id) as OverlapLevel,
> start_date as start_overlap,
> min(end_date) as end_overlap
> FROM CallLog as X
> JOIN CallLog as Y
> ON X.start_date BETWEEN Y.start_date AND Y.end_date
> GROUP BY X.row_id
> HAVING count(Y.row_id) > 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
> <tarheels4025@.discussions.microsoft.com> wrote:
>
>|||Sorry about that.
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
X.start_date as start_overlap,
min(Y.end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy
On Thu, 22 Jun 2006 12:55:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:
>Ron,
>When I run the query it throws back
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'Start_Date'.
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'End_Date'.
Subscribe to:
Posts (Atom)