query assistance: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

query help

Hi,
I want to bring up the following data for groups:
Blue, Red, Green and Yellow.
I have the following view in sql 7 which brings up the data i want, How can
i bring up the data for the other groups within the same query.
SELECT DISTINCT
salesorders.srep, SUM(salesitems.sprice) AS Expr1,
delv.dtaxd
FROM dbo.salesorders INNER JOIN
dbo.salesitems ON
dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
dbo.delvitems ON
dbo.salesorders.son = dbo.delvitems.dord AND
dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
dbo.delv ON
dbo.delvitems.delvnoa = dbo.delv.delvno
WHERE (dbo.salesorders.srep = 'blue') AND
(dbo.delv.dedate > CONVERT(DATETIME,
'2008-02-01 00:00:00', 102))
GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
I then need to call this query witin MS Access and use it to output to a
Data sheet.
Thanks
Mohammad
A little more info.
The data i want will look like this as an example:
Team price Date
==== ==== ===
Blue 500 01/01/2008
Green 600 04/02/2008
Yellow 2000 01/02/2008
"mahmad" wrote:

> Hi,
> I want to bring up the following data for groups:
> Blue, Red, Green and Yellow.
> I have the following view in sql 7 which brings up the data i want, How can
> i bring up the data for the other groups within the same query.
> SELECT DISTINCT
> salesorders.srep, SUM(salesitems.sprice) AS Expr1,
> delv.dtaxd
> FROM dbo.salesorders INNER JOIN
> dbo.salesitems ON
> dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
> dbo.delvitems ON
> dbo.salesorders.son = dbo.delvitems.dord AND
> dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
> dbo.delv ON
> dbo.delvitems.delvnoa = dbo.delv.delvno
> WHERE (dbo.salesorders.srep = 'blue') AND
> (dbo.delv.dedate > CONVERT(DATETIME,
> '2008-02-01 00:00:00', 102))
> GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
> I then need to call this query witin MS Access and use it to output to a
> Data sheet.
> Thanks
> Mohammad

Query Help

Hi all,
I have one ‘tall’ table that records the following on a regular basis:
STATE SERIAL# DATE
====== ======== ========
VA Z32WE12 12/31/2003
CA QWEFD1 05/04/2005
VA Z32WE13 01/01/2003
CA QWEFD2 05/05/2005
TX POISD21 05/03/2005
TX POISD21 05/04/2005
TX POISD21 05/05/2005
We are tracking the serial number for each state and would like to report on
the current and previous serial number for each state. Can someone please
help me with building the query in order to get the following:
StateCurrent Serial#SincePrevious Serial#
CAQWEFD205/05/2005QWEFD1
VAZ32WE1301/01/2003Z32WE12
TXPOISD21 05/03/2005 Never Changed
Thanks in advance,
-Appreciator
It seems to be a flaw in the data:

> VA Z32WE12 12/31/2003
> VA Z32WE13 01/01/2003
Did you mean "12/31/2002" for serial# "Z32WE12"?
try:
use northwind
go
create table t1 (
state char(2) not null,
serial# varchar(10) not null,
[date] datetime,
)
go
insert into t1 values('VA','Z32WE12', '12/31/2002')
insert into t1 values('CA','QWEFD1' , '05/04/2005')
insert into t1 values('VA','Z32WE13', '01/01/2003')
insert into t1 values('CA','QWEFD2' , '05/05/2005')
insert into t1 values('TX','POISD21', '05/03/2005')
insert into t1 values('TX','POISD21', '05/04/2005')
insert into t1 values('TX','POISD21', '05/05/2005')
go
create view v1
as
select state, serial#, max([date]) as [date] from t1 group by state, serial#
go
create view v2
as
select
a.state,
a.serial# as current_serial#,
a.[date] as since,
isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
previous_serail#
from
v1 as a
left join
v1 as b
on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
where c.state = a.state and c.[date] > b.[date])
go
select
*
from
v2 as a
where
previous_serail# != 'have_not_changed_since'
or (previous_serail# = 'have_not_changed_since' and not exists(select *
from v2 as b where b.state = a.state and b.previous_serail# !=
'have_not_changed_since'))
order by
case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
a.state
go
drop view v2, v1
go
drop table t1
go
AMB
"URG" wrote:

> Hi all,
> I have one ‘tall’ table that records the following on a regular basis:
> STATE SERIAL# DATE
> ====== ======== ========
> VA Z32WE12 12/31/2003
> CA QWEFD1 05/04/2005
> VA Z32WE13 01/01/2003
> CA QWEFD2 05/05/2005
> TX POISD21 05/03/2005
> TX POISD21 05/04/2005
> TX POISD21 05/05/2005
> We are tracking the serial number for each state and would like to report on
> the current and previous serial number for each state. Can someone please
> help me with building the query in order to get the following:
> StateCurrent Serial#SincePrevious Serial#
> CAQWEFD205/05/2005QWEFD1
> VAZ32WE1301/01/2003Z32WE12
> TXPOISD21 05/03/2005 Never Changed
> Thanks in advance,
> -Appreciator
>
|||Hi Alejandro,
Thanks a bunch! That really works perfect..!!
Sorry about the flaw - I had typed in the sample data.
URG
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> It seems to be a flaw in the data:
>
> Did you mean "12/31/2002" for serial# "Z32WE12"?
> try:
> use northwind
> go
> create table t1 (
> state char(2) not null,
> serial# varchar(10) not null,
> [date] datetime,
> )
> go
> insert into t1 values('VA','Z32WE12', '12/31/2002')
> insert into t1 values('CA','QWEFD1' , '05/04/2005')
> insert into t1 values('VA','Z32WE13', '01/01/2003')
> insert into t1 values('CA','QWEFD2' , '05/05/2005')
> insert into t1 values('TX','POISD21', '05/03/2005')
> insert into t1 values('TX','POISD21', '05/04/2005')
> insert into t1 values('TX','POISD21', '05/05/2005')
> go
> create view v1
> as
> select state, serial#, max([date]) as [date] from t1 group by state, serial#
> go
> create view v2
> as
> select
> a.state,
> a.serial# as current_serial#,
> a.[date] as since,
> isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
> previous_serail#
> from
> v1 as a
> left join
> v1 as b
> on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
> where c.state = a.state and c.[date] > b.[date])
> go
> select
> *
> from
> v2 as a
> where
> previous_serail# != 'have_not_changed_since'
> or (previous_serail# = 'have_not_changed_since' and not exists(select *
> from v2 as b where b.state = a.state and b.previous_serail# !=
> 'have_not_changed_since'))
> order by
> case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
> a.state
> go
> drop view v2, v1
> go
> drop table t1
> go
>
> AMB
> "URG" wrote:

Query Help

I have query with following column names:
cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
officeaddress, officecity, officestate, officezip
I want to be able to pull it this way that whenever there is a certain
unique Firm_no it should populate the homeaddress, homecity, homestate,
homezip for this customer.
What do I have to do to accomplish this?
ThanksU can achieve this by the following way:
Use the following expresson to display Homeaddress:
=IIF(IsNothing(Previous(Fields!Firm_no.Value)),
Fields!homeaddress.Value,
IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
"",
Fields!homeaddress.Value))
In the same way implement the same logic to display homecity, homestate,
homezip.
Regards,
SaraS|||saras,
I have a unique firm_no that I want to use when my report runs it should
look like:
Custo_no Firm_no Name Address City State Zip
123 1111 Jon Doe Main Dallas TX 75000
321 1122 Jon Doe Globe Pano TX 75001
So when anytime the Firm_no is '1122' I want the address to print their
homeaddress.
Thanks for your help.
"saras" wrote:
> U can achieve this by the following way:
> Use the following expresson to display Homeaddress:
> =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> Fields!homeaddress.Value,
> IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> "",
> Fields!homeaddress.Value))
>
> In the same way implement the same logic to display homecity, homestate,
> homezip.
> Regards,
> SaraS
>|||Thanks Saras, It worked I just dint have it in the correct place. Stupid
typo mistake. Thanks a bunch have a great day!
"saras" wrote:
> U can achieve this by the following way:
> Use the following expresson to display Homeaddress:
> =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> Fields!homeaddress.Value,
> IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> "",
> Fields!homeaddress.Value))
>
> In the same way implement the same logic to display homecity, homestate,
> homezip.
> Regards,
> SaraS
>|||Saras,
Sorry to bother again...but now its printing all the homeaddress I still
have this questions:
I have query with following column names:
cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
officeaddress, officecity, officestate, officezip
I want to be able to pull it this way that whenever there is a certain
unique Firm_no it should populate the homeaddress, homecity, homestate,
homezip for this customer.
What do I have to do to accomplish this?
Thanks
"Shan" wrote:
> I have query with following column names:
> cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
> officeaddress, officecity, officestate, officezip
> I want to be able to pull it this way that whenever there is a certain
> unique Firm_no it should populate the homeaddress, homecity, homestate,
> homezip for this customer.
> What do I have to do to accomplish this?
> Thanks|||Ok. I have these fields.
Cust_no, Firm_name, Firm_no, Name, OfficeAddr, OffCity, OffSt, OffZip,
HomeAddr, Homecity, HomeSt, HomeZip
In my report I only want to show based on my query the following format
Custo_no Firm_name Firm_no Name Address City State Zip
123 Keller 1111 Jon Doe Main Dallas TX 75000
321 Ebby 1122 Jon Doe Globe Pano TX 75009
102 Cold 1234 Jon Doe Holly Plano TX 75002
103 Nonmember 1000 Jon Doe Trench Allen TX 75001
109 Nonmember 1000 Jon Doe Trail Prosper TX 75003
Now I want to populate the Address field with customer's homeAddr anytime
the firm_no is 1000 which is a Non member otherwise if the firm_no is
anything else then populate the Address with their OfficeAddr. The Firm_no
1000 doesn't have an OfficeAddr so we want to populate it with customer's
homeaddr.
Thanks for your help.
"Shan" wrote:
> saras,
> I have a unique firm_no that I want to use when my report runs it should
> look like:
> Custo_no Firm_no Name Address City State Zip
> 123 1111 Jon Doe Main Dallas TX 75000
> 321 1122 Jon Doe Globe Pano TX 75001
> So when anytime the Firm_no is '1122' I want the address to print their
> homeaddress.
> Thanks for your help.
> "saras" wrote:
> > U can achieve this by the following way:
> >
> > Use the following expresson to display Homeaddress:
> >
> > =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> > Fields!homeaddress.Value,
> > IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> > "",
> > Fields!homeaddress.Value))
> >
> >
> > In the same way implement the same logic to display homecity, homestate,
> > homezip.
> >
> > Regards,
> > SaraS
> >

Query Help

I have the following query that returns extra header info in the results
when there is more than 1 day with no records in the query. I'm trying to
figure out how to run this and not get the additional header information.
Here is the query:
SET NOCOUNT ON
DECLARE @.datestart int
DECLARE @.datestop int
SET @.datestart = 1
SET @.datestop = 2
WHILE
(SELECT count(1)
FROM [tables]
WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
AND (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
GETDATE())))) = 0
BEGIN
SET @.datestart = @.datestart + 1
SET @.datestop = @.datestop + 1
SELECT [statement]
FROM [tables]
WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))) AND
(pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
GETDATE())))
ORDER BY pp.Last
END
Here's the output:
Date Name Class
--
Date Name Class
-- --
Date Name Class
-- --
May 16 2005 David L Afor 17
May 16 2005 Tina M Coll 17
May 16 2005 Dan O Doer 17chad, You are looping through code and running the select statement for eac
h
value of the @.dateStart variable, while there are records in the table, but
for each iteration, you check the Count() with one value of @.datestart,
@.datestop, and then you increment the values first, before you run the
select.. . After you change the values, there may not be any records that
match the criteria...
Also, in both your selects you refer to columns with a table prefox 'a', and
another 'pp', but these are not defined anywhere in the query... This does
not look like working code...
"chad" wrote:

> I have the following query that returns extra header info in the results
> when there is more than 1 day with no records in the query. I'm trying to
> figure out how to run this and not get the additional header information.
> Here is the query:
> SET NOCOUNT ON
> DECLARE @.datestart int
> DECLARE @.datestop int
> SET @.datestart = 1
> SET @.datestop = 2
> WHILE
> (SELECT count(1)
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
> AND (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))) = 0
> BEGIN
> SET @.datestart = @.datestart + 1
> SET @.datestop = @.datestop + 1
> SELECT [statement]
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))) AN
D
> (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))
> ORDER BY pp.Last
> END
> Here's the output:
> Date Name Class
> --
> Date Name Class
> -- --
> Date Name Class
> -- --
> May 16 2005 David L Afor 17
> May 16 2005 Tina M Coll 17
> May 16 2005 Dan O Doer 17|||chad,
The way you have it the code will run from DateStart = 1 until it finds
any date with no records, even if subsequent dates have records... Is that
what you want? If not, then I suggest you define the endDate and change loop
as so:
Declare @.DateStart int Set @.DateStart = 1
Declare @.DateStop int Set @.DateStop = 350 -- or whatever
While @.DateStart < @.DateStop
Begin
If Exists (Select * from [Tables]
Where pp.Class = 17
And a.Start > DateAdd(d, @.datestart, DateDiff(d, 0,
getDate()))
And a.Start < DateAdd(d, @.datestart, DateDiff(d, 1,
getDate())))
Select <Stuff> from [Tables]
Where pp.Class = 17
And a.Start > DateAdd(d, @.datestart, DateDiff(d, 0,
getDate()))
And a.Start < DateAdd(d, @.datestart, DateDiff(d, 1,
getDate()))
Set @.DateStart = @.DateStart + 1
End
"chad" wrote:

> I have the following query that returns extra header info in the results
> when there is more than 1 day with no records in the query. I'm trying to
> figure out how to run this and not get the additional header information.
> Here is the query:
> SET NOCOUNT ON
> DECLARE @.datestart int
> DECLARE @.datestop int
> SET @.datestart = 1
> SET @.datestop = 2
> WHILE
> (SELECT count(1)
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
> AND (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))) = 0
> BEGIN
> SET @.datestart = @.datestart + 1
> SET @.datestop = @.datestop + 1
> SELECT [statement]
> FROM [tables]
> WHERE (a.Start > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))) AN
D
> (pp.Class = 17) AND (a.Start < DATEADD(d, @.datestop, DATEDIFF(d, 0,
> GETDATE())))
> ORDER BY pp.Last
> END
> Here's the output:
> Date Name Class
> --
> Date Name Class
> -- --
> Date Name Class
> -- --
> May 16 2005 David L Afor 17
> May 16 2005 Tina M Coll 17
> May 16 2005 Dan O Doer 17|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your names in the sample output are a mix of reserved
words and useless violations of basic ISO-11179 conventions. This
totally useless.
The use of a loop tells us that you do not understand a declarative
language. The use of the word "record" tells us do not know SQL, which
is why you are writing loops and other procedural code tha twill runs
ordersof magnitude slower than good code. .
In clear English, so we do not have to guess, what do you want to do.
And post minimal DDL next time. Was CBretana right about about looking
for a run starting at a given date?|||This query returns records correctly when there is only one day of no
records. It is when I hit two days of zero records that I get an additional
header row with no data that messes it up. I set the @.datestart and
@.datestop at 1 and 2 respectfully to look for tomorrows records, if zero the
n
increment by 1 and rerun till it finds a count. Once that is satisfied then
it needs to run the second part of the query to retun data.
-- Here is the entire query with table names changed
--
SET NOCOUNT ON
DECLARE @.datestart int
DECLARE @.datestop int
SET @.datestart = 1
SET @.datestop = 2
WHILE
(SELECT count(*)
FROM dbo.TableA a LEFT OUTER JOIN
dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
OUTER JOIN
dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
JOIN
dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
JOIN
dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0,
GETDATE()))) AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d,
@.datestop, DATEDIFF(d, 0, GETDATE())))) = 0
BEGIN
SET @.datestart = @.datestart + 1
SET @.datestop = @.datestop + 1
SELECT CAST(a.ApptStart AS varchar(11)) AS Date, ISNULL(pp.First + ' ',
'') + ISNULL(pp.Middle + ' ', '') + pp.Last AS Name, dfr.ListName AS
ResourceName,
pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name,
'Unknown') AS ApptType, pp.Phone1 AS Phone1, CAST(pp.Birthdate AS
varchar(11)) AS Birthdate,
pp.FinancialClassMId
FROM dbo.TableA a LEFT OUTER JOIN
dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
OUTER JOIN
dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
JOIN
dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
JOIN
dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE())))
AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d, @.datestop,
DATEDIFF(d, 0,
GETDATE())))
ORDER BY pp.Last
END|||chad,
The way you have your code written, is equivilent to this psueo code
While (There's 1 or more records using DateStart, DateStart +1)
Begin
Increment DateStart
Return Records for NEW CHANGED VALUE of Datestart, datestart +1
End
-- This *sounds* illogical... It means that whenever the code gets to a
place where there ARE records for the current date, (which would have been
returne the last time through the loop), but NONE FOR THE NEXT day, (althoug
h
it doesn't know that yet) it will enter the while loop, return an empty set,
(That's where you're getting your header with no records) and then, when it
retests the while it will stop...
Your boolean test, in the while clause (with the COunt(1) ...) is testing
how many records were selected in the last time through the loop... , becaus
e
you're incrementing the control variables BEFORE you run the select...
"chad" wrote:

> This query returns records correctly when there is only one day of no
> records. It is when I hit two days of zero records that I get an additiona
l
> header row with no data that messes it up. I set the @.datestart and
> @.datestop at 1 and 2 respectfully to look for tomorrows records, if zero t
hen
> increment by 1 and rerun till it finds a count. Once that is satisfied the
n
> it needs to run the second part of the query to retun data.
>
> -- Here is the entire query with table names changed
> --
> SET NOCOUNT ON
> DECLARE @.datestart int
> DECLARE @.datestop int
> SET @.datestart = 1
> SET @.datestop = 2
> WHILE
> (SELECT count(*)
> FROM dbo.TableA a LEFT OUTER JOIN
> dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
> OUTER JOIN
> dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
> JOIN
> dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
> INNER JOIN
> dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER
JOIN
> dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
> LEFT OUTER JOIN
> dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
> dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
> JOIN
> dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
> dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
> WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0,
> GETDATE()))) AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(
d,
> @.datestop, DATEDIFF(d, 0, GETDATE())))) = 0
> BEGIN
> SET @.datestart = @.datestart + 1
> SET @.datestop = @.datestop + 1
> SELECT CAST(a.ApptStart AS varchar(11)) AS Date, ISNULL(pp.First + '
',
> '') + ISNULL(pp.Middle + ' ', '') + pp.Last AS Name, dfr.ListName AS
> ResourceName,
> pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name,
> 'Unknown') AS ApptType, pp.Phone1 AS Phone1, CAST(pp.Birthdate AS
> varchar(11)) AS Birthdate,
> pp.FinancialClassMId
> FROM dbo.TableA a LEFT OUTER JOIN
> dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
> OUTER JOIN
> dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
> JOIN
> dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
> INNER JOIN
> dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER
JOIN
> dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
> LEFT OUTER JOIN
> dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
> dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
> JOIN
> dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
> dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
> WHERE (a.ApptStart > DATEADD(d, @.datestart, DATEDIFF(d, 0, GETDATE()))
)
> AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d, @.datestop,
> DATEDIFF(d, 0,
> GETDATE())))
> ORDER BY pp.Last
> END|||CBretana-
I changed the code to resemble your suggestion in the prior post, and it
complete successfully but returns no data.
Declare @.DateStart int
Declare @.DateStop int
Set @.DateStart = 1
Set @.DateStop = 2
WHILE @.DateStart < @.DateStop
BEGIN
IF EXISTS (SELECT CAST(a.ApptStart AS varchar(11)) AS Date,
ISNULL(pp.First + ' ', '') + ISNULL(pp.Middle + ' ', '') + pp.Last AS Name,
dfr.ListName AS ResourceName,
pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name,
'Unknown') AS ApptType, pp.Phone1 AS Phone1, CAST(pp.Birthdate AS
varchar(11)) AS Birthdate, pp.FinancialClassMId
FROM dbo.Appointments a LEFT OUTER JOIN
dbo.ApptChain ac ON a.ApptChainId = ac.ApptChainId LEFT OUTER JOIN
dbo.ApptSet aset ON a.ApptSetId = aset.ApptSetId INNER JOIN
dbo.DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.PatientProfile pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.ApptType at ON a.ApptTypeId = at.ApptTypeId
WHERE pp.FinancialClassMId = 1739 And a.ApptStart >DATEADD(d,
@.datestart, DATEDIFF(d, 0, GETDATE())) And a.ApptStart < DATEADD(d,
@.datestart, DATEDIFF(d, 1, GETDATE())))
SELECT CAST(a.ApptStart AS varchar(11)) AS Date, ISNULL(pp.First + ' ', '')
+ ISNULL(pp.Middle + ' ', '') + pp.Last AS Name, dfr.ListName AS
ResourceName,
pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name, 'Unknown') AS ApptType,
pp.Phone1 AS Phone1, CAST(pp.Birthdate AS varchar(11)) AS Birthdate,
pp.FinancialClassMId
FROM dbo.Appointments a LEFT OUTER JOIN
dbo.ApptChain ac ON a.ApptChainId = ac.ApptChainId LEFT OUTER JOIN
dbo.ApptSet aset ON a.ApptSetId = aset.ApptSetId INNER JOIN
dbo.DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId INNER
JOIN
dbo.PatientProfile pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId LEFT
OUTER JOIN
dbo.ApptType at ON a.ApptTypeId = at.ApptTypeId
WHERE pp.FinancialClassMId = 1739 And a.ApptStart > DATEADD(d, @.datestart,
DATEDIFF(d, 0, GETDATE())) And a.ApptStart < DATEADD(d, @.datestart,
DATEDIFF(d, 1, GETDATE()))
ORDER BY pp.Last
SET @.DateStart = @.DateStart + 1
END|||>> I changed the code to resemble your suggestion in the prior post,
and it complete successfully but returns no data. <<
And now, how about some DDL? And try to fix up the obviously absurd
things like "type_id" in the data model.
day of no records [sic]. It is when I hit two days of zero records
[sic] that I get an additional header row with no data that messes it
up. <<
You might also want to learn the differences between records and rows.
If you keep talking in fiel system terms, you will keep producing file
system code, like your cursors.
Sample data and expect results would be nice, too. Are you looking for
runs with gaps of two or more days between them, so a gap of one is
fine?|||On Thu, 12 May 2005 16:10:27 -0700, chad wrote:

>I have the following query that returns extra header info in the results
>when there is more than 1 day with no records in the query. I'm trying to
>figure out how to run this and not get the additional header information.
(snip)
Hi Chad,
I'm not sure if you're still reading., since this question is already 5
days old.
Anyway, reading your code I have the feeling that you could do this in
one SELECT statement instead of using a loop:
SELECT statement
FROM tables
WHERE CONVERT(char(10), Start, 114)
= (SELECT CONVERT(char(10), MIN(Start), 114)
FROM tables
WHERE CONVERT(char(10), Start, 114)
> CONVERT(char(10), CURRENT_TIMESTAMP, 114))
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query help

I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanksrick,
try this:
update ts
set tsfbr1=(select t.tsfbr1 from ts t where t.[datetime]=(select
max(t2.[datetime]) from ts t2 where t2.[datetime]<ts.[datetime] and tsfbr1
is not null))
where tsfbr1 is null and tsfbr1on<>0
and please, don't use reserved words or typenames for column names :)
dean
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
>I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try
select t1.[datetime], "tsfbr1" =
CASE
WHEN t1.tsfbr1 IS NULL AND t1.tsfbr1on = 0 THEN NULL
WHEN t1.tsfbr1 IS NULL THEN (SELECT TOP 1 t2.tsfbr1 FROM TS t2
WHERE (t2.[datetime] < t1.[datetime] AND t2.tsfbr1 IS NOT NULL) ORDER
BY t2.[datetime] DESC)
ELSE t1.tsfbr1
END,
t1.tsfbr1on
FROM TS t1
This will produce the output that you want through a SELECT.
I'll try producing an UPDATE statement that accomplishes the same thing and
post back.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try:
update t1
set
TSFBR1 = t2.TSFBR1
from
TS t1
join
TS t2 on t2.[Datetime] =
(
select
max (t3.[Datetime])
from
TS t3
where
t3.Datetime < t1.Datetime
and t3.TSFBR1 is not null
)
where
t1.TSFBR1 is null
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanks|||Thanks Guys. I appreciate it.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>

Wednesday, March 28, 2012

Query Help

Hi all,
I have one ‘tall’ table that records the following on a regular basis:
STATE SERIAL# DATE
====== ======== ========
VA Z32WE12 12/31/2003
CA QWEFD1 05/04/2005
VA Z32WE13 01/01/2003
CA QWEFD2 05/05/2005
TX POISD21 05/03/2005
TX POISD21 05/04/2005
TX POISD21 05/05/2005
We are tracking the serial number for each state and would like to report on
the current and previous serial number for each state. Can someone please
help me with building the query in order to get the following:
State Current Serial# Since Previous Serial#
CA QWEFD2 05/05/2005 QWEFD1
VA Z32WE13 01/01/2003 Z32WE12
TX POISD21 05/03/2005 Never Changed
Thanks in advance,
-AppreciatorIt seems to be a flaw in the data:

> VA Z32WE12 12/31/2003
> VA Z32WE13 01/01/2003
Did you mean "12/31/2002" for serial# "Z32WE12"?
try:
use northwind
go
create table t1 (
state char(2) not null,
serial# varchar(10) not null,
[date] datetime,
)
go
insert into t1 values('VA', 'Z32WE12', '12/31/2002')
insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
insert into t1 values('VA', 'Z32WE13', '01/01/2003')
insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
insert into t1 values('TX', 'POISD21', '05/03/2005')
insert into t1 values('TX', 'POISD21', '05/04/2005')
insert into t1 values('TX', 'POISD21', '05/05/2005')
go
create view v1
as
select state, serial#, max([date]) as [date] from t1 group by state,
serial#
go
create view v2
as
select
a.state,
a.serial# as current_serial#,
a.[date] as since,
isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
previous_serail#
from
v1 as a
left join
v1 as b
on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as
c
where c.state = a.state and c.[date] > b.[date])
go
select
*
from
v2 as a
where
previous_serail# != 'have_not_changed_since'
or (previous_serail# = 'have_not_changed_since' and not exists(select *
from v2 as b where b.state = a.state and b.previous_serail# !=
'have_not_changed_since'))
order by
case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
a.state
go
drop view v2, v1
go
drop table t1
go
AMB
"URG" wrote:

> Hi all,
> I have one ‘tall’ table that records the following on a regular basis:
> STATE SERIAL# DATE
> ====== ======== ========
> VA Z32WE12 12/31/2003
> CA QWEFD1 05/04/2005
> VA Z32WE13 01/01/2003
> CA QWEFD2 05/05/2005
> TX POISD21 05/03/2005
> TX POISD21 05/04/2005
> TX POISD21 05/05/2005
> We are tracking the serial number for each state and would like to report
on
> the current and previous serial number for each state. Can someone please
> help me with building the query in order to get the following:
> State Current Serial# Since Previous Serial#
> CA QWEFD2 05/05/2005 QWEFD1
> VA Z32WE13 01/01/2003 Z32WE12
> TX POISD21 05/03/2005 Never Changed
> Thanks in advance,
> -Appreciator
>|||Hi Alejandro,
Thanks a bunch! That really works perfect..!!
Sorry about the flaw - I had typed in the sample data.
URG
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> It seems to be a flaw in the data:
>
> Did you mean "12/31/2002" for serial# "Z32WE12"?
> try:
> use northwind
> go
> create table t1 (
> state char(2) not null,
> serial# varchar(10) not null,
> [date] datetime,
> )
> go
> insert into t1 values('VA', 'Z32WE12', '12/31/2002')
> insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
> insert into t1 values('VA', 'Z32WE13', '01/01/2003')
> insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
> insert into t1 values('TX', 'POISD21', '05/03/2005')
> insert into t1 values('TX', 'POISD21', '05/04/2005')
> insert into t1 values('TX', 'POISD21', '05/05/2005')
> go
> create view v1
> as
> select state, serial#, max([date]) as [date] from t1 group by stat
e, serial#
> go
> create view v2
> as
> select
> a.state,
> a.serial# as current_serial#,
> a.[date] as since,
> isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
> previous_serail#
> from
> v1 as a
> left join
> v1 as b
> on a.state = b.state and a.[date] = (select min(c.[date]) from v1
as c
> where c.state = a.state and c.[date] > b.[date])
> go
> select
> *
> from
> v2 as a
> where
> previous_serail# != 'have_not_changed_since'
> or (previous_serail# = 'have_not_changed_since' and not exists(select *
> from v2 as b where b.state = a.state and b.previous_serail# !=
> 'have_not_changed_since'))
> order by
> case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
> a.state
> go
> drop view v2, v1
> go
> drop table t1
> go
>
> AMB
> "URG" wrote:
>

query help

Hi,
I want to bring up the following data for groups:
Blue, Red, Green and Yellow.
I have the following view in sql 7 which brings up the data i want, How can
i bring up the data for the other groups within the same query.
SELECT DISTINCT
salesorders.srep, SUM(salesitems.sprice) AS Expr1,
delv.dtaxd
FROM dbo.salesorders INNER JOIN
dbo.salesitems ON
dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
dbo.delvitems ON
dbo.salesorders.son = dbo.delvitems.dord AND
dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
dbo.delv ON
dbo.delvitems.delvnoa = dbo.delv.delvno
WHERE (dbo.salesorders.srep = 'blue') AND
(dbo.delv.dedate > CONVERT(DATETIME,
'2008-02-01 00:00:00', 102))
GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
I then need to call this query witin MS Access and use it to output to a
Data sheet.
Thanks
MohammadA little more info.
The data i want will look like this as an example:
Team price Date
==== ==== ===Blue 500 01/01/2008
Green 600 04/02/2008
Yellow 2000 01/02/2008
"mahmad" wrote:
> Hi,
> I want to bring up the following data for groups:
> Blue, Red, Green and Yellow.
> I have the following view in sql 7 which brings up the data i want, How can
> i bring up the data for the other groups within the same query.
> SELECT DISTINCT
> salesorders.srep, SUM(salesitems.sprice) AS Expr1,
> delv.dtaxd
> FROM dbo.salesorders INNER JOIN
> dbo.salesitems ON
> dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
> dbo.delvitems ON
> dbo.salesorders.son = dbo.delvitems.dord AND
> dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
> dbo.delv ON
> dbo.delvitems.delvnoa = dbo.delv.delvno
> WHERE (dbo.salesorders.srep = 'blue') AND
> (dbo.delv.dedate > CONVERT(DATETIME,
> '2008-02-01 00:00:00', 102))
> GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
> I then need to call this query witin MS Access and use it to output to a
> Data sheet.
> Thanks
> Mohammadsql

Query Help

Hi all,
I have one â'tallâ' table that records the following on a regular basis:
STATE SERIAL# DATE
====== ======== ======== VA Z32WE12 12/31/2003
CA QWEFD1 05/04/2005
VA Z32WE13 01/01/2003
CA QWEFD2 05/05/2005
TX POISD21 05/03/2005
TX POISD21 05/04/2005
TX POISD21 05/05/2005
We are tracking the serial number for each state and would like to report on
the current and previous serial number for each state. Can someone please
help me with building the query in order to get the following:
State Current Serial# Since Previous Serial#
CA QWEFD2 05/05/2005 QWEFD1
VA Z32WE13 01/01/2003 Z32WE12
TX POISD21 05/03/2005 Never Changed
Thanks in advance,
-AppreciatorIt seems to be a flaw in the data:
> VA Z32WE12 12/31/2003
> VA Z32WE13 01/01/2003
Did you mean "12/31/2002" for serial# "Z32WE12"?
try:
use northwind
go
create table t1 (
state char(2) not null,
serial# varchar(10) not null,
[date] datetime,
)
go
insert into t1 values('VA', 'Z32WE12', '12/31/2002')
insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
insert into t1 values('VA', 'Z32WE13', '01/01/2003')
insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
insert into t1 values('TX', 'POISD21', '05/03/2005')
insert into t1 values('TX', 'POISD21', '05/04/2005')
insert into t1 values('TX', 'POISD21', '05/05/2005')
go
create view v1
as
select state, serial#, max([date]) as [date] from t1 group by state, serial#
go
create view v2
as
select
a.state,
a.serial# as current_serial#,
a.[date] as since,
isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
previous_serail#
from
v1 as a
left join
v1 as b
on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
where c.state = a.state and c.[date] > b.[date])
go
select
*
from
v2 as a
where
previous_serail# != 'have_not_changed_since'
or (previous_serail# = 'have_not_changed_since' and not exists(select *
from v2 as b where b.state = a.state and b.previous_serail# !='have_not_changed_since'))
order by
case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
a.state
go
drop view v2, v1
go
drop table t1
go
AMB
"URG" wrote:
> Hi all,
> I have one â'tallâ' table that records the following on a regular basis:
> STATE SERIAL# DATE
> ====== ======== ========> VA Z32WE12 12/31/2003
> CA QWEFD1 05/04/2005
> VA Z32WE13 01/01/2003
> CA QWEFD2 05/05/2005
> TX POISD21 05/03/2005
> TX POISD21 05/04/2005
> TX POISD21 05/05/2005
> We are tracking the serial number for each state and would like to report on
> the current and previous serial number for each state. Can someone please
> help me with building the query in order to get the following:
> State Current Serial# Since Previous Serial#
> CA QWEFD2 05/05/2005 QWEFD1
> VA Z32WE13 01/01/2003 Z32WE12
> TX POISD21 05/03/2005 Never Changed
> Thanks in advance,
> -Appreciator
>|||Hi Alejandro,
Thanks a bunch! That really works perfect..!!
Sorry about the flaw - I had typed in the sample data.
URG
"Alejandro Mesa" wrote:
> It seems to be a flaw in the data:
> > VA Z32WE12 12/31/2003
> > VA Z32WE13 01/01/2003
> Did you mean "12/31/2002" for serial# "Z32WE12"?
> try:
> use northwind
> go
> create table t1 (
> state char(2) not null,
> serial# varchar(10) not null,
> [date] datetime,
> )
> go
> insert into t1 values('VA', 'Z32WE12', '12/31/2002')
> insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
> insert into t1 values('VA', 'Z32WE13', '01/01/2003')
> insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
> insert into t1 values('TX', 'POISD21', '05/03/2005')
> insert into t1 values('TX', 'POISD21', '05/04/2005')
> insert into t1 values('TX', 'POISD21', '05/05/2005')
> go
> create view v1
> as
> select state, serial#, max([date]) as [date] from t1 group by state, serial#
> go
> create view v2
> as
> select
> a.state,
> a.serial# as current_serial#,
> a.[date] as since,
> isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
> previous_serail#
> from
> v1 as a
> left join
> v1 as b
> on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
> where c.state = a.state and c.[date] > b.[date])
> go
> select
> *
> from
> v2 as a
> where
> previous_serail# != 'have_not_changed_since'
> or (previous_serail# = 'have_not_changed_since' and not exists(select *
> from v2 as b where b.state = a.state and b.previous_serail# !=> 'have_not_changed_since'))
> order by
> case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
> a.state
> go
> drop view v2, v1
> go
> drop table t1
> go
>
> AMB
> "URG" wrote:
> > Hi all,
> >
> > I have one â'tallâ' table that records the following on a regular basis:
> >
> > STATE SERIAL# DATE
> > ====== ======== ========> > VA Z32WE12 12/31/2003
> > CA QWEFD1 05/04/2005
> > VA Z32WE13 01/01/2003
> > CA QWEFD2 05/05/2005
> > TX POISD21 05/03/2005
> > TX POISD21 05/04/2005
> > TX POISD21 05/05/2005
> >
> > We are tracking the serial number for each state and would like to report on
> > the current and previous serial number for each state. Can someone please
> > help me with building the query in order to get the following:
> >
> > State Current Serial# Since Previous Serial#
> > CA QWEFD2 05/05/2005 QWEFD1
> > VA Z32WE13 01/01/2003 Z32WE12
> > TX POISD21 05/03/2005 Never Changed
> >
> > Thanks in advance,
> >
> > -Appreciator
> >sql

Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
.. and so on).
Just wondering.
Thank you!
Kyra
--
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBAKyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:
>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:
> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
> >Help, please!
> >
> >I have 3 columns with numeric values. i want to write a query that returns
> >the following:
> >If all 3 columns are populated, then the middle value (not the average);
> >If one colunn has a null, then the lesser value,
> >If 2 are null, then the remaining value.
> >
> >For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> >the result to be "10". If ColumnB were null, then the result would be "5".
> >Making sense?
> >
> >Is there a way to do this, without having to create a bazillion Case When
> >statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
> >else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
> >.. and so on).
> >
> >Just wondering.
> >Thank you!
> >Kyra
> >
> >
> >
>

query help

I have the following table -

Name Enrolment# File#
x 422 011
y 421 022
z 444 023
a 345 024

I have to produce the following table -

S.No Name Enrolment# File#
1 a 345 024
2 y 421 022
3 x 422 021
4 z 444 023

Could someone please help me out with the SQL query to do the
operation above? I know how to sort by enrolment# but how do I produce
the first column of the target table?

Thanks in Advance.

- P.On 27 Oct 2004 10:34:55 -0700, Parth wrote:

>I have the following table -
>Name Enrolment# File#
>x 422 011
>y 421 022
>z 444 023
>a 345 024
>
>I have to produce the following table -
>S.No Name Enrolment# File#
>1 a 345 024
>2 y 421 022
>3 x 422 021
>4 z 444 023
>
>Could someone please help me out with the SQL query to do the
>operation above? I know how to sort by enrolment# but how do I produce
>the first column of the target table?
>Thanks in Advance.
> - P.

Hi Parth,

Try the following queries. Since you didn't provide CREATE TABLE and
INSERT statements to base my tests on, I didn't test them.

SELECT COUNT(*) AS "S.No",
a.Name, a.Enrolment#, a.File#
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.File# >= a.File#
GROUP BY a.Name, a.Enrolment, a.File#
ORDER BY a.File# DESC

SELECT (SELECT COUNT(*)
FROM YourTable AS b
WHERE b.File# >= a.File#) AS "S.No",
a.Name, a.Enrolment#, a.File#
FROM YourTable AS a
ORDER BY a.File# DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<u540o0p73v88i1c6cb6vss27fggt1o4vl7@.4ax.com>...
> On 27 Oct 2004 10:34:55 -0700, Parth wrote:
> >I have the following table -
> >Name Enrolment# File#
> >x 422 011
> >y 421 022
> >z 444 023
> >a 345 024
> >I have to produce the following table -
> >S.No Name Enrolment# File#
> >1 a 345 024
> >2 y 421 022
> >3 x 422 021
> >4 z 444 023
> >Could someone please help me out with the SQL query to do the
> >operation above? I know how to sort by enrolment# but how do I produce
> >the first column of the target table?
> >Thanks in Advance.
> > - P.
> Hi Parth,
> Try the following queries. Since you didn't provide CREATE TABLE and
> INSERT statements to base my tests on, I didn't test them.
> SELECT COUNT(*) AS "S.No",
> a.Name, a.Enrolment#, a.File#
> FROM YourTable AS a
> INNER JOIN YourTable AS b
> ON b.File# >= a.File#
> GROUP BY a.Name, a.Enrolment, a.File#
> ORDER BY a.File# DESC
> SELECT (SELECT COUNT(*)
> FROM YourTable AS b
> WHERE b.File# >= a.File#) AS "S.No",
> a.Name, a.Enrolment#, a.File#
> FROM YourTable AS a
> ORDER BY a.File# DESC
>
> Best, Hugo

Monday, March 26, 2012

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3
On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value = sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value => sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration='86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query from Access 2000 won't work in MSDE 2000

I have the following SQL query which works great in MS Access 2000
SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
[tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
[tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
[tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
[tbKeymap].[ItemIndex]
This query does not work in MSDE 2000.
I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near '.'.
I have no idea why this is the case since the MDAC is 2.7 (I'm
assuming since this is MSDE 2000).
Any ideas or solutions would be appreciated.
Ali
SQL Server (MSDE) and Access use different dialect of SQL language.
keyword DISTINCTROW does not exist in SQL Server's T-SQL: it is DISTINCT in
SQL Server, while in Access, you have DISTINCTROW and DISTINCT, they do
things slightly different
"Ali Syed" <alijsyed@.hotmail.com> wrote in message
news:26c82868.0408181145.34f0f6e9@.posting.google.c om...
> I have the following SQL query which works great in MS Access 2000
> SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
> [tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
> [tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
> ([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
> [tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
> [tbKeymap].[ItemIndex]
>
> This query does not work in MSDE 2000.
> I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
> syntax near '.'.
>
> I have no idea why this is the case since the MDAC is 2.7 (I'm
> assuming since this is MSDE 2000).
>
> Any ideas or solutions would be appreciated.
>
> Ali
|||Thanks Norman that fixed it.
It seems that I will have to compensate for this issue.
Ali

Friday, March 23, 2012

Query for the first and latest wish

Hi all,

I have the following table

Name Date Wish Valid

Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.

So, a typical data set looks like:

Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1

My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

I can do UNION, but is there another way to do that?
Thank you.On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

Hi all,
>
I have the following table
>
Name Date Wish Valid
>
Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.
>
So, a typical data set looks like:
>
Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1
>
My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.
>
I can do UNION, but is there another way to do that?
Thank you.

-- Put them into a temporary table:

SELECT Name, Min(Date) as FirstWishDate, Max(Date) as LastWishDate
INTO #FirstAndLast
FROM Wishlist
WHERE Date >= @.StartingDate
AND Date <= @.EndingDate

-- Then compare the values

SELECT t.Name, t.FirstWishDate, w1.Wish as FirstWish, t.LastWishDate,
w2.Wish as LastWish
FROM #FirstAndLast t,
WishList w1,
WishList w2
WHERE t.Name = w1.Name
AND t.FirstWishDate = w1.Date
AND t.Name = w2.Name
AND t.FirstWishDate = w2.Date

Of course, this is supposing they've only made one wish per day,
otherwise you'll duplicate some rows. If that is the case, make sure
you are tracking times as well.

Good luck!

-Utah|||Eugene (alsu50@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:
>
Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1
>
My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

SELECT a.Name, a.FirstDate, f.Wish .FirstWish,
a.LastDate, l.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @.start AND @.end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 22, 1:25 pm, Utahd...@.hotmail.com wrote:

Quote:

Originally Posted by

On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hi all,

Quote:

Originally Posted by

I have the following table

Quote:

Originally Posted by

Name Date Wish Valid

Quote:

Originally Posted by

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

I can do UNION, but is there another way to do that?
Thank you.

>
-- Put them into a temporary table:
>
SELECT Name, Min(Date) as FirstWishDate, Max(Date) as LastWishDate
INTO #FirstAndLast
FROM Wishlist
WHERE Date >= @.StartingDate
AND Date <= @.EndingDate
>
-- Then compare the values
>
SELECT t.Name, t.FirstWishDate, w1.Wish as FirstWish, t.LastWishDate,
w2.Wish as LastWish
FROM #FirstAndLast t,
WishList w1,
WishList w2
WHERE t.Name = w1.Name
AND t.FirstWishDate = w1.Date
AND t.Name = w2.Name
AND t.FirstWishDate = w2.Date
>
Of course, this is supposing they've only made one wish per day,
otherwise you'll duplicate some rows. If that is the case, make sure
you are tracking times as well.
>
Good luck!
>
-Utah

Utah,

Thank you for the idea! However, having the extra step of getting the
temp table is not something that I think the DBA here would approve.
The good news is that the date field is the datetime (defaulting to
getdate()) and it puts the date and time up to milliseconds, so the
chances for two people making the wish at the same time are very
minimal.

Thanks again!|||On Feb 22, 1:29 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Eugene (als...@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

>
SELECT a.Name, a.FirstDate, f.Wish .FirstWish,
a.LastDate, l.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @.start AND @.end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Wow. Simple and elegant, what else can I say?! Thank you!|||On Feb 22, 1:29 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Eugene (als...@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

Hm, I thought this was like conversation mode, so the reply would have
posted right underneath the answer. Anyway, Thanks a bunch, Erland!

BTW, for the folks who is looking at this some time later, the working
query looks like the following:

SELECT a.Name, a.FirstDate, b.Wish as FirstWish,
a.LastDate, c.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @.start AND @.end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date|||Eugene wrote:

Quote:

Originally Posted by

I have the following table
>
Name Date Wish Valid
>
Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.

The 'Valid' column is redundant (you can use MAX(Date) instead) and
breakable (what if a row with Valid = 1 is deleted?). I'd ditch it
if I were you.|||On Feb 22, 6:41 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

On Feb 22, 1:25 pm, Utahd...@.hotmail.com wrote:
>
>
>

Quote:

Originally Posted by

On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Hi all,

Quote:

Originally Posted by

Quote:

Originally Posted by

I have the following table

Quote:

Originally Posted by

Quote:

Originally Posted by

Name Date Wish Valid

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

I can do UNION, but is there another way to do that?
Thank you.

Quote:

Originally Posted by

-- Put them into a temporary table:

Quote:

Originally Posted by

SELECT Name, Min(Date) as FirstWishDate, Max(Date) as LastWishDate
INTO #FirstAndLast
FROM Wishlist
WHERE Date >= @.StartingDate
AND Date <= @.EndingDate

Quote:

Originally Posted by

-- Then compare the values

Quote:

Originally Posted by

Quote:

Originally Posted by

Of course, this is supposing they've only made one wish per day,
otherwise you'll duplicate some rows. If that is the case, make sure
you are tracking times as well.

Quote:

Originally Posted by

Good luck!

Quote:

Originally Posted by

-Utah

>
Utah,
>
Thank you for the idea! However, having the extra step of getting the
temp table is not something that I think the DBA here would approve.
The good news is that the date field is the datetime (defaulting to
getdate()) and it puts the date and time up to milliseconds, so the
chances for two people making the wish at the same time are very
minimal.
>
Thanks again!

Oops, yeah, temporary tables have their places and this wouldn't be
one of them. But, I think you've got the idea.

Friday, March 30, 2012

query help

Query Help

Query Help

Query Help

Query help

Wednesday, March 28, 2012

Query Help

query help

Query Help

Query Help

query help

Monday, March 26, 2012

Query Hangs on Values that start with a number

Query Hangs on Values that start with a number

Query Hangs on Values that start with a number

Query from Access 2000 won't work in MSDE 2000

Friday, March 23, 2012

Query for the first and latest wish

query assistance

Blog Archive

About Me