Help with calculating duration time

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?Blind code, but give it a try: create table #Blocks
(Machine_No int,
Start_Time datetime,
End_Time datetime)

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets) FirstTickets
on Tickets.Machine_No = FirstTickets.Machine_No
and Tickets.Start_Time = FirstTickets.StartTime

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update #Blocks
set End_Time = max(End_Time)
from #Blocks
inner join Tickets
on #Blocks.Machine_No = Tickets.Machine_No
and Tickets.Start_Time between #Blocks.Start_Time and #Blocks.End_Time
and Tickets.End_Time > #Blocks.End_Time

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets
where Start_Time >
(select max(End_Time)
from #Blocks
where #Blocks.Machine_No = Tickets.Machine_No) NextTickets
on Tickets.Machine_No = NextTickets.Machine_No
and Tickets.Start_Time = NextTickets.StartTime
end

select Machine_No,
sum(datediff(s, Start_Time, End_Time)/3600.00 as Hours
from #Blocks
group by Machine_No
If you can deal with discrete time segments (say, 1 minute increments) and place an upper limit on the date range, you may be able to do this with a simpler query using a table of integers.|||I really think I can use this (with minor modifications). How can I make this work so that it only compares the records within the same day and not all the records? Thank you.|||This formula will concatenate datetime to whole dates:

select dateadd(d, datediff(d, 0, [Yourdate]), 0) as WholeDate|||How can I modify the following solution so that it only captures the duration time for range of 7 am - 11 pm spanning multiple days? Right now, it works only for 24 hr period. Here is my code:

SELECT dbo.tbl_installed_ATMs.Term_ID, FirstTickets.Object_Key, FirstTickets.Start_Time, MAX(dbo.tbl_install_tickets.END_TIME) AS End_Time,
CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110) AS Start_Date, CONVERT(varchar(12), dbo.tbl_install_tickets.END_TIME, 110)
AS End_Date, dbo.tbl_status_code_key.DESCRIPTION
INTO dbo.Blocks
FROM dbo.tbl_install_tickets INNER JOIN
(SELECT Object_Key, MIN(Start_Time) Start_Time
FROM tbl_install_tickets
GROUP BY Object_Key) FirstTickets ON dbo.tbl_install_tickets.OBJECT_KEY = FirstTickets.Object_Key AND
dbo.tbl_install_tickets.START_TIME = FirstTickets.Start_Time INNER JOIN
dbo.tbl_status_code_key ON dbo.tbl_install_tickets.STATUS_CODE_KEY = dbo.tbl_status_code_key.LINK INNER JOIN
dbo.tbl_installed_ATMs ON FirstTickets.Object_Key = dbo.tbl_installed_ATMs.Object_Key
GROUP BY FirstTickets.Object_Key, FirstTickets.Start_Time, CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110), CONVERT(varchar(12),
dbo.tbl_install_tickets.END_TIME, 110), dbo.tbl_status_code_key.DESCRIPTION, dbo.tbl_installed_ATMs.Term_ID

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update Blocks
set End_Time = (Select max(Blocks.End_Time)
from Blocks
inner join tbl_install_tickets
On Blocks.Object_Key = tbl_install_Tickets.Object_Key
and tbl_install_Tickets.Start_Time between Blocks.Start_Time and Blocks.End_Time
and tbl_install_Tickets.End_Time > Blocks.End_Time
)
from Blocks
end

SELECT Term_ID, CONVERT(varchar(12), Start_Time, 110) AS Date, DESCRIPTION AS Ticket_type, SUM(DATEDIFF(s, Start_Time, End_Time) / 3600.00)
AS Duration
FROM dbo.Blocks
GROUP BY Term_ID, CONVERT(varchar(12), Start_Time, 110), DESCRIPTION
GO

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 an WHERE NOT EXISTS subquery please.

Hi, I have 2 tables:

tblStations StationID
Station

tblStationUser RecordID
UserName
Station

I'm trying to come up with a dataset that contains the
tblStations.Station
EXCEPT for where that Station exists in tblStationUser where the
UserName = @.varUserName.

I've tried this but get 0 rows (I should get about 40):

SELECT tblStations.Station
FROM tblStations
WHERE NOT EXISTS
(SELECT tblStationUser.Station FROM tblStationUser WHERE
tblStationUser.UserName=@.varUserName)
ORDER BY Station

I tried the subquery separately which returns the correct number of
rows.

Any clues as to where I'm going wrong?

Thanks!
KathyKathy,

You forgot to join tables. Try one of these queries. I haven't tested them.

SELECT Station
FROM tblStations
WHERE NOT EXISTS (SELECT *
FROM tblStationUser
WHERE tblStationUser.Station = tblStations.Station
AND tblStationUser.UserName = @.varUserName)
ORDER BY Station

OR

SELECT Station
FROM tblStations
WHERE Station NOT IN (SELECT Station
FROM tblStationUser
WHERE UserName = @.varUserName)
ORDER BY Station

Good luck,
Shervin

"KathyB" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310160945.2b2df7e4@.posting.google.c om...
> Hi, I have 2 tables:
> tblStations StationID
> Station
> tblStationUser RecordID
> UserName
> Station
> I'm trying to come up with a dataset that contains the
> tblStations.Station
> EXCEPT for where that Station exists in tblStationUser where the
> UserName = @.varUserName.
> I've tried this but get 0 rows (I should get about 40):
> SELECT tblStations.Station
> FROM tblStations
> WHERE NOT EXISTS
> (SELECT tblStationUser.Station FROM tblStationUser WHERE
> tblStationUser.UserName=@.varUserName)
> ORDER BY Station
> I tried the subquery separately which returns the correct number of
> rows.
> Any clues as to where I'm going wrong?
> Thanks!
> Kathy|||Thanks again Shervin. I wish I knew I fraction of what you do!

Thanks, Kat

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You are welcome Kathy, I'm hanging around here to learn more about SQL
Server. :-)

Shervin

"Kathy Burke" <kathyburke40@.comcast.net> wrote in message
news:3f8fd569$0$193$75868355@.news.frii.net...
> Thanks again Shervin. I wish I knew I fraction of what you do!
>
> Thanks, Kat
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Help with an expression

The error is:
The value expression for the textbox â'BillAddressâ' contains an error:
[BC30201] Expression expected.
=If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value) = 0 AND
Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value) = 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value)= 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.value)= 0 AND
Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
Fields!AcctName.Value + Chr(10) +
"No Address Available" + Chr(10) +
"ID: " + AccountNumber
Many ThanksJeff,
I thought VB was the scripting language to be used in expressions. Why
are you concatinating with '+' operators, when you should be using '&'
operators?
thx
-jsh
"Jeff Metcalf" wrote:
> The error is:
> The value expression for the textbox â'BillAddressâ' contains an error:
> [BC30201] Expression expected.
>
> =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr (10) +
> Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
>
> Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.value)= 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> Fields!AcctName.Value + Chr(10) +
> "No Address Available" + Chr(10) +
> "ID: " + AccountNumber
> Many Thanks|||Beats me...I'm just trying to get it to work. The '+' operator works for
for concatinating in other SRS reports and in SQL. I'm not a VB or .Net
programmer, so I have no idea if it's right or not, lol.
"jsh02_nova@.hotmail.com" wrote:
> Jeff,
> I thought VB was the scripting language to be used in expressions. Why
> are you concatinating with '+' operators, when you should be using '&'
> operators?
> thx
> -jsh
> "Jeff Metcalf" wrote:
> > The error is:
> >
> > The value expression for the textbox â'BillAddressâ' contains an error:
> > [BC30201] Expression expected.
> >
> >
> > =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> > And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr (10) +
> > Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> >
> > Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.value)= 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > "No Address Available" + Chr(10) +
> > "ID: " + AccountNumber
> >
> > Many Thanks|||I'm not sure if this is what's causing the problem but SRS uses an
"inline if" -IIF- instead of IF THEN ELSE.
The syntax is =IIF(test expression,true action, false action).
Using your expression from above it would be something like this:
=IIf( Len(Fields!BillTo_Line1.Value)= 0 And
Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 ,
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " +
Fields!BillTo_StateOrProvince.Value + " " +
Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value ,
IIF( Len(Fields!BillTo_Line2.Value)= 0 And
Len(Fields!BillTo_Line1.Value) > 0 And Len(Fields!BillTo_Line3.Value) >
0 , and so on
Nested IIFs can get really ugly. If I were you and if it's possible
I'd do as much as you can with case statements in SQL.
Good luck

HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

help with a User-Defined function to return a string from multiple records

I need some help with writing a User-Defined function in SQL Server 2000.
I would like to return a space-delimited string, which contains the column
data of several records from a table.
Here's an example:
table_fruit
id textid
-- --
1 APPLE
2 BANANA
4 ORANGE
8 PEAR
16 PLUM
My SQL query string uses a bitwise-AND (&) to determine which records to
return.
SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
So, for example, if I pass in the parameter @.param_fruits = 13, then I get
the following records back:
APPLE
ORANGE
PEAR
What I'd like to have is a User-Defined function that returns the data in a
concatenated space-delimited string like this:
APPLE ORANGE PEAR
I need help with writing this function. Thanks very much.To get a space delimited string, you should modify the statement as follows:
=====
-- Your function declarations etc
DECLARE @.returnString VARCHAR(8000)
SET @.returnString = ''
SELECT @.returnString = @.returnString + ' ' + textid
FROM table_fruit WHERE ([id] & @.param_fruits) > 0
RETURN (@.returnString)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I need some help with writing a User-Defined function in SQL Server 2000.
> I would like to return a space-delimited string, which contains the column
> data of several records from a table.
> Here's an example:
> table_fruit
> id textid
> -- --
> 1 APPLE
> 2 BANANA
> 4 ORANGE
> 8 PEAR
> 16 PLUM
>
> My SQL query string uses a bitwise-AND (&) to determine which records to
> return.
> SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> So, for example, if I pass in the parameter @.param_fruits = 13, then I get
> the following records back:
> APPLE
> ORANGE
> PEAR
> What I'd like to have is a User-Defined function that returns the data in
> a
> concatenated space-delimited string like this:
> APPLE ORANGE PEAR
> I need help with writing this function. Thanks very much.
>|||In addition, this method is unreliable and should be done on the client
side
As an alternative take a look at Erland's (if I remember well) example
CREATE PROCEDURE get_company_names_inline @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@.customers) s ON C.CustomerID = s.Value
go
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'
CREATE FUNCTION inline_split_me (@.param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @.param + ',', Number + 1,
charindex(',', ',' + @.param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @.param + ',') - 1
AND substring(',' + @.param + ',', Number, 1) = ',')
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
drop table numbers
drop function inline_split_me
drop proc get_company_names_inline
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
> follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Thanks very much! Worked great.
I had done the same query but had not declared the VARCHAR large enough, and
didn't use the SET statement to initialize the variable.
Thanks
Scott
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
column
get
in
>sql

help with a SELECT

Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JCTry:
select
o.*
from
Orders o
join
(
select
idCLient
, max (orderDate) orderDate
from
Orders
group by
idCLient
) x on x.idClient = o.idClient
and x.orderDate = o.orderDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JC|||Thanks Tom, worked fine... sorry for multi posting
JC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uweVaEsHFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Try:
> select
> o.*
> from
> Orders o
> join
> (
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
> news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a table that contains orders from cliente. Suppose that each
> client has more than one order, I would like to write a SELECT statement
> that returns the order with the oldest date from each client.
> for example:
> Table:
> idClient idOrder orderDate
> 35 100 03/10/2004
> 35 120 03/22/2004
> 35 150 04/10/2004
> 72 45 01/10/2004
> 72 56 01/29/2004
> and the SELECT should return the next records:
> idClient idOrder orderDate
> 35 150 04/10/2004
> 72 56 01/29/2004
> Hope someone can help me with this :)
> Thanks in advance
> JC
>
>|||Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:

>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Good catch!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:

>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Thanks Steve, you're right...MIN is the choice for the oldest date. It
worked fine too.
JC
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Tiny detail - the specs were to retrieve the oldest order
> for each client, not the newest, so you'll want MIN, not
> MAX.
> Another solution is
> select o.*
> from Orders as O1
> where orderDate = (
> select min(orderDate)
> from Orders as O2
> where O2.idClient = O1.idClient
> )
> One or the other formulation may be faster, but what
> I like about this one is that it follows the English description
> closely: select all orders where the order date is
> the earliest for that client.
> Note that there may not be just one earliest order
> for a given client, and each of these queries will
> return all orders with minimum orderDate for the client.
> Steve Kass
> Drew University
> Tom Moreau wrote:
>

Help with a query

Hello,

I need some assistance with a query that i am trying to build.

A table contains records which consitute an employee's shift.

There are 7 'Default' records for each employee, along with any number of additional records which will override the default record if the date in this record equals the date the form is displaying.

For instance: today is Friday 7/28 and the employee name is Joe; if there are no additional records out in the table for Joe for 7/28, then we will grab his default record. If there is a record for Joe for 7/28, then we will use this record to get his shift start and end times.

Here are the fields in the two records and what they may contain:

Name: Joe, XDate:"7/28/2006", XDay:Fri, StartTime:xxxx, EndTime:xxxx

Name: Joe, XDate:"Default", XDay:Fri, StartTime:xxxxx, EndTime:xxxxx

and this situation can occur for serveral employees.

So again, I need to grab the record with a XDate that matches todays date, if that does not exist then I need to grab the record with the XDate that has the word "Default" in it.

By the way, XDate is a text field and not a Date datatype field.

*****oh and one other thing i forgot to mention. If the Date of 7/28 is not found, then i would use the day value of that date "Fri" to grab the default start and end times.

Thanks for your help!

StrangeMike:

oh and one other thing i forgot to mention. If the Date of 7/28 is not found, then i would use the day value of that date "Fri" to grab the default start and end times.

Sorry I'm not very clear with this point: how will you use the XDay date? Previously you said 'Default will be used if no match XDate is found, now how will you use the XDay value? Anyways despite the use of XDay data, you may try the code below:

declare @.d smalldatetime
set @.d='2006-07-29'

select * from test
where name='Joe'
and XDate= CASE WHEN (SELECT count(*) FROM test
WHERE XDate=CONVERT(varchar(12),DATEPART(mm,@.d))+'/'+
CONVERT(varchar(12),DATEPART(dd,@.d))+'/'+
CONVERT(varchar(12),DATEPART(yy,@.d)))>0
THEN CONVERT(varchar(12),DATEPART(mm,@.d))+'/'+
CONVERT(varchar(12),DATEPART(dd,@.d))+'/'+
CONVERT(varchar(12),DATEPART(yy,@.d))
ELSE 'Default'
END

|||

Hi Lori_Jay,

The xDay is the clue to tell me which 'Default' record to take Mon-Sun.

xDate can contain the word "Default" or an actual Date "7/28/2006".

There are 7 'Default' records. So if there is no record with an actual date..like "7/28/2006",

then I need to go after the default record for the day I am currently displaying, and that is how xDay come into play. If today is a Friday, then I grab the default record where xDay = "Fri".

I know it's a bit confusing.

Thank you for your reply.

|||

You'll need to change "MyTable" to your table name, and set @.date to the textbox's .Text property.

SELECT t1.Name,ISNULL(t1.StartTime,(SELECT StartTime FROM MyTable t2 WHERE t2.Name=e.Name and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM MyTable t2 WHERE t2.Name=e.Name and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime

(SELECT DISTINCT Name,@.date AS XDate,CASE DATEPART(dw,CAST(@.date as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM MyTable) e

LEFT JOIN MyTable t1 ON (t1.Name=e.Name and t1.XDate=e.XDate)

|||

Wow nice query. I'm a little confused by the "set @.date to the textbox's .Text property." though.

I am passing two parameters to this stored query that populates a datagrid, those being..Date and Day. Here is the current query which returns the record for the date, but it also includes the default record for the same day, I removed some unrealted fields for simplicity: Using your query how can I modify this one? Thanks

SELECT tblPhotographerShifts.RecId, tblPhotographerShifts.Photographer, tblPhotographerShifts.StartTime, tblPhotographerShift.EndTime, tblPhotographerShifts.xDate, tblPhotographerShifts.Day
FROM tblPhotographerShifts
WHERE (((tblPhotographerShifts.Photographer)<>"TBA") AND ((tblPhotographerShifts.xDate)=[@.ByDate])) OR (((tblPhotographerShifts.xDate)="Default") AND ((tblPhotographerShifts.Day)=[@.Day]))
ORDER BY tblPhotographerShifts.Photographer, tblPhotographerShifts.xDate;

|||

SELECT t1.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime

(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e

LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)

WHERE t1.Photographer<>'TBA'

ORDER BY t1.Photographer

I don't use @.Day, since I calculate it in the query from @.ByDate already.

|||

Motley thanks for your help with this, I've gotta say I don't know how you thought of something like this. I basically just tried cutting and pasting your query into the Sql View of the query. When I tried to go to design I got this error:

"The Select includes a reserve word or argument name that is misspelled or missing, or the puncuation is incorrect"

I gave it a shot looking at it, but to be honest there are parts of this query I have never even seen before. Do you know what may be incorrect?

Thank you.

|||

Heh, I forgot the word FROM, try this:

SELECT

t1.Photographer,ISNULL(t1.StartTime,(SELECT StartTimeFROM tblPhotographerShifts t2WHERE t2.Photographer=e.Photographerand t2.XDate='Default'AND t2.XDay=e.Dow))AS StartTime,ISNULL(t1.EndTime,(SELECT EndTimeFROM tblPhotographerShifts t2WHERE t2.Photographer=e.Photographerand t2.XDate='Default'AND t2.XDay=e.Dow))AS EndTime

FROM

(

SELECTDISTINCT Photographer,@.ByDateAS XDate,CASEDATEPART(dw,CAST(@.ByDateasdatetime))WHEN 1THEN'Sun'WHEN 2THEN'Mon'WHEN 3THEN'Tue'WHEN 4THEN'Wed'WHEN 5THEN'Thu'WHEN 6THEN'Fri'WHEN 7THEN'Sat'ENDAS DowFROM tblPhotographerShifts) e

LEFT

JOIN tblPhotographerShifts t1ON(t1.Photographer=e.Photographerand t1.XDate=e.XDate)

WHERE

t1.Photographer<>'TBA'

ORDER

BY t1.Photographer|||

Getting closer... Here is a new error:

"Wrong number of arguments used with function in query expression ISNULL(t1.StartTime, (Select StartTime FROM tblPhotogpraherShifts t2 where t2.Photographer=e.Phtographer and t2.xDate='Default' and t2.Xday=e.dow))"

|||

You have a typo somewhere, although I did fix one other bug, here is my test script:

USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--DROP TABLE tblPhotographerShifts
GO
CREATE TABLE [dbo].[tblPhotographerShifts](
[Photographer] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XDate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XDay] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartTime] varchar(10) NULL,
[EndTime] varchar(10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('Me','8/1/2006',NULL,'9:30','10:30')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('Me','Default','Wed','9:00','10:00')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','Default','Tue','10:00','11:00')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','Default','Wed','10:30','11:30')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','8/1/2006',NULL,'11:30','12:30')

DECLARE @.ByDate varchar(20)

SET @.ByDate='8/1/2006'
SELECT e.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime ,e.dow
FROM
(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e
LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)
WHERE e.Photographer<>'TBA'
ORDER BY t1.Photographer

SET @.ByDate='8/2/2006'
SELECT e.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime ,e.dow
FROM
(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e
LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)
WHERE e.Photographer<>'TBA'
ORDER BY t1.Photographer

Results:

Me 9:30 10:30 Tue
You 11:30 12:30 Tue

Me 9:00 10:00 Wed
You 10:30 11:30 Wed

|||

It could be an MS Access restriction. I am just cutting and pasting into an SQL View and it is giving me the error. I'll keep trying some different things to see if I can get Access to accept your query.

Thanks

|||Motley, thanks for your assistance on this, between you and another site I have a query that is working for me.|||Heh, it would have helped if you mentioned that you were using Access. Or posted it in the AccessDataSource forums.

Help With a query

If I have table1 and table2, which both have the same fields Product
and Qty. Can you help me with this query...

table1 has two rows
Row 1 Contains ProductA with a Qty of two
Row 2 Contains ProductB with a Qty of four

table2 has has two rows
Row 1 Contains ProductB with a Qty of two
Row 2 Contains ProductC with a Qty of three

What I want to return from a SQL query is a table with three fields...
Product, Table1QTY and Table2QTY
With the example above I would like the result to have three rows, one
for each product.

Any help?

CiarnOn 9 Nov 2004 03:48:54 -0800, Ciar?n wrote:

>If I have table1 and table2, which both have the same fields Product
>and Qty. Can you help me with this query...
>
>table1 has two rows
>Row 1 Contains ProductA with a Qty of two
>Row 2 Contains ProductB with a Qty of four
>table2 has has two rows
>Row 1 Contains ProductB with a Qty of two
>Row 2 Contains ProductC with a Qty of three
>
>What I want to return from a SQL query is a table with three fields...
>Product, Table1QTY and Table2QTY
>With the example above I would like the result to have three rows, one
>for each product.
>Any help?
>Ciarn

Hi Ciarn

Try if this works for you:

SELECT COALESCE(a.Product, b.Product) AS Product,
COALESCE(a.Qty,0) + COAELSCE(b.Qty,0) AS Qty
FROM table1 AS a
FULL OUTER JOIN table2 AS b
ON a.Product = b.Product

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>
> Hi Ciarn
> Try if this works for you:
> SELECT COALESCE(a.Product, b.Product) AS Product,
> COALESCE(a.Qty,0) + COAELSCE(b.Qty,0) AS Qty
> FROM table1 AS a
> FULL OUTER JOIN table2 AS b
> ON a.Product = b.Product
> Best, Hugo

Hugo,

Thanks a million.
Had to tweak the script ever so slightly to get the Qtys in seperate
columns like below, but wouldn't have figured it out without your
help.

SELECT COALESCE (A.Product, B.Product) AS PRODUCT, COALESCE
(A.Qty, 0) AS QTY_A, COALESCE (B.Qty, 0) AS QTY_B
FROM Sheet1 A FULL OUTER JOIN
Sheet2 B ON A.Product = B.Product
WHERE A.Qty>B.Qty

Cheers again.

Ciarn|||On 9 Nov 2004 07:19:49 -0800, Ciar?n wrote:

>Had to tweak the script ever so slightly to get the Qtys in seperate
>columns like below, but wouldn't have figured it out without your
>help.

Hi Ciarn,

I see I misread your query - somehow, I thought you wanted the total.
Apologies for that.

>SELECT COALESCE (A.Product, B.Product) AS PRODUCT, COALESCE
>(A.Qty, 0) AS QTY_A, COALESCE (B.Qty, 0) AS QTY_B
>FROM Sheet1 A FULL OUTER JOIN
> Sheet2 B ON A.Product = B.Product
>WHERE A.Qty>B.Qty

Are you sure about the WHERE clause? With default ANSI settings, this
would remove the rows for all products that are pressent in only Sheet1 or
in only Sheet2, since a comparison involving NULL will never evaluate to
TRUE.

Since this requirement is far from obvious from your first post, I wonder
why you included this WHERE clause and what the acutal business problem
you have to solve is.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help translating Access SQL to T-SQL

Can someone tell me what this Where clause (from MS Access) should be T-SQL?

Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1.

Where IIf([reason] LIKE '%DIST%',Val([movement]),1)>=1

Thanks!

something like this. "iif" is not a valid sql fucntion

where reason like '%dist%' and movement=> 1

val is also not valid so you may use cast or convert

therefore

where reason like '%dist%' and cast (movement, money) => 1

|||

You can do below:

where case when reason like '%DIST%' then sign(Movement) end = 1

-- or

where reason like '%DIST%'

and sign(Movement) = 1

-- or below which will use index on Movement if available

where reason like '%DIST%'

and Movement >= 1

|||

Replace the IIF() with Case:

Case when [reason] like '%DIST%' then...

Am I correct in intrepreting Val() as converting to a number (saves me a google)? Then you want to use Convert().

Final syntax:

Where Case When [reason] Like '%Dist%' then Convert(int, [movement] ) Else 1 End >= 1

You could also use NULL with the Else case.

....Guess I'm a slow typist...

|||

Thanks anomolous! That was the right answer. I just had to change int to real because [movement] holds a decimal value.

Yes, Val() in Access converts a string to a number And if it's NULL, Val() will return 0. Will Convert() return a 0 for a NULL value? Or do I need to use a different function so that I get a 0 if [movement] holds a NULL value?

|||

You have to use coalesce or isnull to check for NULL values. All built-ins return NULL for NULL input. Do something like:

where reason like '%DIST%'

and cast(coalesce(Movement, '') as int) >= 1

Note that CAST or CONVERT in TSQL will return error if the value cannot be converted. I don't know the behavior of VAL in Access. So in that case, you will have to do additional checks like:

where reason like '%DIST%'

and cast isnumeric(Movement) when 1 then cast(coalesce(Movement, '') as int) end >= 1

Even use of ISNUMERIC will not work for all cases since it checks for integer, numeric and money data type conversion semantics. So it is possible that you could have values that can convert to money but not int. So you will be better off actually modifying the schema such that Movement column is integer and it stores only integer values. Mixing different data types in a string column and manipulating it using TSQL is problematic in lot of ways. And it is often done incorrectly leading to bad performance due to conversions, wrong results, run-time errors and so on.

help selecting all rows that contains no null value

I have data on MS SQL Server that have over 60 columns, I would like to
select 30 of these columns that may or may not contain NULL and I don't
want to write out all 30 columns and check for IN NOT NULL. Does anyone
know how to do that?Not trying to be harsh, but... start typing. :)
Is this a recurring problem, or are you shooting for a one time
solution? If one-time, the easiest way to do it is to just write the
SQL query that you're attempting to avoid. If you're trying to develop
some sort of administrative tool to help you validate data, you could
script something that uses dynamic SQL (using the syscolumns table).
That's not something that I would advocate giving to the average user.
Stu
timhz...@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||You must perform each IS NOT NULL test in the query. However, you can
save some typing. This will generate a query to get you started, just
copy the results.
declare @.tbl varchar(50)
set @.tbl = 'Categories'
SELECT CASE WHEN C.ORDINAL_POSITION =
(select min(ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS C2
where C2.TABLE_NAME = @.tbl
and C2.IS_NULLABLE = 'YES')
THEN 'SELECT * FROM ' + C.TABLE_NAME +
char(13) + CHAR(10) +
' WHERE '
ELSE ' AND '
END +
C.COLUMN_NAME + 'IS NOT NULL' + char(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @.tbl
AND C.IS_NULLABLE = 'YES'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
On 29 Jun 2006 08:48:16 -0700, timhzhou@.gmail.com wrote:

>I have data on MS SQL Server that have over 60 columns, I would like to
>select 30 of these columns that may or may not contain NULL and I don't
>want to write out all 30 columns and check for IN NOT NULL. Does anyone
>know how to do that?|||No, there are no lazy shortcuts in T-SQL to select "a set of columns".
But, there are lazy shortcuts to generating the list so that you can create
a valid and reasonable T-SQL statement more quickly. What are you using,
6.5, 7.0, 2000, 2005? In Query Analyzer or Management Studio, when you
expand a table and you see a folder called columns, drag it to the query
window. Voila, like magic, huh?
Laziness is not enough of a reason to use SELECT * (or s a similar
alternative).
A
<timhzhou@.gmail.com> wrote in message
news:1151596096.534039.98000@.p79g2000cwp.googlegroups.com...
>I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?
>|||before you begin anything .. you might want to consider clean up the
data to update those columnes with a default value.
ie.
update table
set column = ''
where column is null
possibly put them in a temporary table?
timhzhou@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||Contrary to Developer opinion, sometimes NULL is an appropriate value.
(Albieit, not as often as it is used just because folks don't take time to
understand the implications.)
A question I posit is: How many responses are there to a Yes/No question?
The correct response is Four. Yes, No, Not Answered, Not Applicable.
The use of the data determines whether or not there is a distinction between
the last two. For example, if I'm analyzing survey results, I wouldn't want
to confound the analysis by combining Not Answered and Not Applicable.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1151597871.024544.185460@.b68g2000cwa.googlegroups.com...
> before you begin anything .. you might want to consider clean up the
> data to update those columnes with a default value.
> ie.
> update table
> set column = ''
> where column is null
> possibly put them in a temporary table?
> timhzhou@.gmail.com wrote:
>

Help regarding Parameters selections in reporting services 2005.

Hi Experts,
I am working on Reporting services 2005 and i am new for this
software.
I have some reports, and some reports contains more than one
parameters.
Now my problem starts...
My client want reports with more and more parameters, and
at the same time he does not want to select all of them
He is saying that he may or may not select all the parameters.
and if not select all the parameters then still reports should be
generated.
for his selected parameters only.
Suppose i have one report with 4 parameters and first parameter is
Country
second one is states, third one is city, fourth one coustmers name
etc.
How i can implement this.
Now he is saying that if he select only one parameters in country and
leave others,
then report should be generated for all the coustemers for that
country.
Please help me...
Regards
DineshCheck this thread:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/43cddb294583ab2c/dc7146eb95e741bb?lnk=gst&q=all+union+parameter&rnum=3#dc7146eb95e741bb
Hope it helps.
On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi Experts,
> I am working on Reporting services 2005 and i am new for this
> software.
> I have some reports, and some reports contains more than one
> parameters.
> Now my problem starts...
> My client want reports with more and more parameters, and
> at the same time he does not want to select all of them
> He is saying that he may or may not select all the parameters.
> and if not select all the parameters then still reports should be
> generated.
> for his selected parameters only.
> Suppose i have one report with 4 parameters and first parameter is
> Country
> second one is states, third one is city, fourth one coustmers name
> etc.
> How i can implement this.
> Now he is saying that if he select only one parameters in country and
> leave others,
> then report should be generated for all the coustemers for that
> country.
> Please help me...
> Regards
> Dinesh|||On Mar 30, 5:12 am, "Alphonse" <amphysv...@.gmail.com> wrote:
> Check this thread:http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Hope it helps.
> On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> > Hi Experts,
> > I am working on Reporting services 2005 and i am new for this
> > software.
> > I have some reports, and some reports contains more than one
> > parameters.
> > Now my problem starts...
> > My client want reports with more and more parameters, and
> > at the same time he does not want to select all of them
> > He is saying that he may or may not select all the parameters.
> > and if not select all the parameters then still reports should be
> > generated.
> > for his selected parameters only.
> > Suppose i have one report with 4 parameters and first parameter is
> > Country
> > second one is states, third one is city, fourth one coustmers name
> > etc.
> > How i can implement this.
> > Now he is saying that if he select only one parameters in country and
> > leave others,
> > then report should be generated for all the coustemers for that
> > country.
> > Please help me...
> > Regards
> > Dinesh
Also, I would suggest setting up the report parameters w/a default
value of 'none selected' as part of the dataset that populates the
parameters (most likely w/a union statement in the dataset query).
That way the user can select only what is wanted and the other
parameters automatically use 'none selected.' Then design the stored
procedure/query that populates the report to handle the 'none
selected' accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant