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 need to include in my query a filter for dates. i need to just return
records that have happened in the last six months. I do i do this. help
please.
i have a startdate and a enddate field.try date between startdate and dateadd(m,-6,startdate)
"Nat Johnson" wrote:
> i need to include in my query a filter for dates. i need to just return
> records that have happened in the last six months. I do i do this. help
> please.
> i have a startdate and a enddate field.

query help

I have a table with multiple records for an identical person. It is a rolling history of the applications a user has submitted. How can I grab the most current application by the date most closest to todays date. Obviously one of my fields is an applied date.
I need help in creating a query.select * from table t where datefield = (select max(datefield) from table where userid = t.userid)

Nick

query help

i have a table that stores employee records. I then have a history employee table that stores changes to each particular employee (one to many ). Everytime a changes is made to an employee I record their old record in a history table. I want to be able to query against the history table and pull up the "last entry". How would I write a query for this, I am recording a timestamp.You haven't shared any DDL, so I'm guessing on keys and column names. But you should get the idea from this...
SELECT *
FROM EmployeeHistory eh
WHERE eh.theDate =
(SELECT MAX(eh2.theDate)
FROM EmployeeHistory eh2
WHERE eh2.EmployeeId = eh.EmployeeId)

|||

You might want to match on a identity field instead. It will be more reliable then a date field. The date field could always have multiple records with the same date.
Nick

|||Depends on the business rules. If there are multiple rows thatwere updated at exactly the same time, which one is most recent? Or should both be displayed?

Query help

Hi,
I have a single row in a table:

Title Desc Quantity
----------
aaaa bbbbb 4

and I need the query to insert "Qty" number of records into a second table, e.g

Title1 Desc1
------
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb

I reckon its some sort of self join but any help would be appreciated.

gregFor the example below I use a function, but you can also use any table that contains sequencial numbers with no gaps. A table with IDENTITY field that did not have any deletes would do.

set nocount on
create table t1 (
title char(4) not null,
[desc] varchar(50) not null,
quantity int not null)
go
create table t2 (
title1 char(4) not null,
desc1 varchar(50) not null)
go
insert t1 values ('aaaa', 'bbbbb', 4)
insert t1 values ('bbbb', 'ccccc', 1)
insert t1 values ('cccc', 'ddddd', 3)
insert t1 values ('dddd', 'eeeee', 2)
insert t1 values ('eeee', 'fffff', 5)
go
insert t2
select title, [desc] from dbo.fn_CartesianProduct() f
inner join t1 on f.[id] < t1.quantity order by 1
go
drop table t1, t2
go|||Excellent - I have an Integers table that substitutes nicely.
Thanks.

Query Help

I have two tables: Trans & History. For each record in Trans, there can be
many in History. I want to return records in the Trans table that have a
certain status in the History table but not other statuses. Here's an
example:
select transactions.transaction_id,
name
from transactions
inner join statusHistory SH1 on
transactions.transaction_id = SH1.transaction_id
where eMonth = '10' and eYear = '2005' and
SH1.status in ('Rec', 'R1Rec') and
SH1.status not in ('Coll', 'RTR')
The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
want to filter out those that contain either 'Coll' or 'RTR'On Wed, 23 Nov 2005 10:36:02 -0800, Eric wrote:

>I have two tables: Trans & History. For each record in Trans, there can b
e
>many in History. I want to return records in the Trans table that have a
>certain status in the History table but not other statuses. Here's an
>example:
>select transactions.transaction_id,
> name
>from transactions
>inner join statusHistory SH1 on
> transactions.transaction_id = SH1.transaction_id
>where eMonth = '10' and eYear = '2005' and
> SH1.status in ('Rec', 'R1Rec') and
> SH1.status not in ('Coll', 'RTR')
>
>The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
>want to filter out those that contain either 'Coll' or 'RTR'
Hi Eric,
Since you didn't post your table structure (CREATE TABLE statements),
sample data (INSERT statements) and required output, I'll have to do
some wild guess about which of your unprefixed columns belog to which
table. You'll probably have to make some changes. But here's a general
outline:
SELECT T.transaction_id, T.name
FROM transactions AS T
WHERE T.eMonth = '10'
AND T.eYear = '2005'
AND EXISTS
(SELECT *
FROM statusHistory AS SH1
WHERE SH1.transaction_id = T.transaction_id
AND SH1.status IN ('Rec', 'R1Rec'))
AND NOT EXISTS
(SELECT *
FROM statusHistory AS SH2
WHERE SH2.transaction_id = T.transaction_id
AND SH2.status IN ('Col1', 'RTR'))
BTW, why store a date in seperate columns eMonth and eYear? Isn't that
what the datetime datatype is for?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi There,
Use LEFT JOIN instead of Join
With Warm regards
Jatinder Singh

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

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 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 hangs during a seperate write

When writing 100's of records to 1 or more tables within a transaction, the
server seems to hang any query trying to read from those tables until either
a Timeout or the write process completes. I'm not doing a lock.
The writes and reads are separate applications.
What would cause this?How are you doing the write?
AMB
"Joe" wrote:

> When writing 100's of records to 1 or more tables within a transaction, th
e
> server seems to hang any query trying to read from those tables until eith
er
> a Timeout or the write process completes. I'm not doing a lock.
> The writes and reads are separate applications.
> What would cause this?
>
>|||using ADO.NET. The DataAdaptor is doing Inserts into the tables.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> How are you doing the write?
>
> AMB
>
> "Joe" wrote:
>
the
either|||Do you know the isolation level being used in the transaction?
You can use Profiler to trace the locks, Lock:Acquired / Lock:Escalation and
Lock:Released.
AMB
"Joe" wrote:

> using ADO.NET. The DataAdaptor is doing Inserts into the tables.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> the
> either
>
>|||Use with (nolock) on your select statements, for ex.
SELECT au_lname FROM authors WITH (NOLOCK)
This will ensure that select stmt. does the dirty rather than acquiring
an shared lock while the exclusive lock is on.|||You may have a deadlock, and insert is relying on an ID inserted in another
table which itself is waiting for the hanging insert to finish.
If it happens all the time - simply check your logic does the flow work? -
you not done something silly in a transaction.
Otherwise if its intermitant find what process blocks it and syncronise the
order which both processes use DB tables. Try and minimise the time spent in
transactions.
Use profiler to / or current actvity pane in Enterprise man to explore the
processes
Just an approach I've found useful.

query group by

have a table with records ranging from 0 to 100. some are repeated multiple
times, such as:
0
0
1
1
1
1
2
2
trying to figure out how to get a result that list the numbers and how many
times they each occur. such as.
num times
0 2
1 4
2 2
Any help would be appreciated. thanks,.
SELECT num, COUNT(*)
FROM table
GROUP BY num
ORDER BY num
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> have a table with records ranging from 0 to 100. some are repeated
multiple
> times, such as:
> 0
> 0
> 1
> 1
> 1
> 1
> 2
> 2
> trying to figure out how to get a result that list the numbers and how
many
> times they each occur. such as.
> num times
> 0 2
> 1 4
> 2 2
> Any help would be appreciated. thanks,.
|||I miswrote what I was looking for. Actually looking for the numbers of a
range. such as:
0 thru 9 22
10 thru 19 89
20 thru 29 34
or should this be done in a presentation layer.
thanks,
"Aaron [SQL Server MVP]" wrote:

> SELECT num, COUNT(*)
> FROM table
> GROUP BY num
> ORDER BY num
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> multiple
> many
>
>
|||If you have a finite set of numbers, you could write some really elaborate
CASE logic. But I think this kind of thing is better done at the
presentation layer.
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:A7B99A2F-BF8A-46D0-B85F-78215711294A@.microsoft.com...
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
|||That can easily be done by applying integer division. In the query
below, NumRange represents the lower bound of the range.
create table #t (num int,val int)
insert into #t values (0,1)
insert into #t values (1,1)
insert into #t values (9,2)
insert into #t values (10,3)
insert into #t values (15,8)
SELECT (num/10)*10 NumRange,SUM(val)
FROM #t
GROUP BY (num/10)*10
ORDER BY NumRange
drop table #t
Gert-Jan
Chad wrote:[vbcol=seagreen]
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
> thanks,
> "Aaron [SQL Server MVP]" wrote:

query group by

have a table with records ranging from 0 to 100. some are repeated multiple
times, such as:
0
0
1
1
1
1
2
2
trying to figure out how to get a result that list the numbers and how many
times they each occur. such as.
num times
0 2
1 4
2 2
Any help would be appreciated. thanks,.SELECT num, COUNT(*)
FROM table
GROUP BY num
ORDER BY num
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> have a table with records ranging from 0 to 100. some are repeated
multiple
> times, such as:
> 0
> 0
> 1
> 1
> 1
> 1
> 2
> 2
> trying to figure out how to get a result that list the numbers and how
many
> times they each occur. such as.
> num times
> 0 2
> 1 4
> 2 2
> Any help would be appreciated. thanks,.|||I miswrote what I was looking for. Actually looking for the numbers of a
range. such as:
0 thru 9 22
10 thru 19 89
20 thru 29 34
or should this be done in a presentation layer.
thanks,
"Aaron [SQL Server MVP]" wrote:

> SELECT num, COUNT(*)
> FROM table
> GROUP BY num
> ORDER BY num
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> multiple
> many
>
>|||If you have a finite set of numbers, you could write some really elaborate
CASE logic. But I think this kind of thing is better done at the
presentation layer.
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:A7B99A2F-BF8A-46D0-B85F-78215711294A@.microsoft.com...
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.|||That can easily be done by applying integer division. In the query
below, NumRange represents the lower bound of the range.
create table #t (num int,val int)
insert into #t values (0,1)
insert into #t values (1,1)
insert into #t values (9,2)
insert into #t values (10,3)
insert into #t values (15,8)
SELECT (num/10)*10 NumRange,SUM(val)
FROM #t
GROUP BY (num/10)*10
ORDER BY NumRange
drop table #t
Gert-Jan
Chad wrote:[vbcol=seagreen]
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
> thanks,
> "Aaron [SQL Server MVP]" wrote:
>sql

query group by

have a table with records ranging from 0 to 100. some are repeated multiple
times, such as:
0
0
1
1
1
1
2
2
trying to figure out how to get a result that list the numbers and how many
times they each occur. such as.
num times
0 2
1 4
2 2
Any help would be appreciated. thanks,.SELECT num, COUNT(*)
FROM table
GROUP BY num
ORDER BY num
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> have a table with records ranging from 0 to 100. some are repeated
multiple
> times, such as:
> 0
> 0
> 1
> 1
> 1
> 1
> 2
> 2
> trying to figure out how to get a result that list the numbers and how
many
> times they each occur. such as.
> num times
> 0 2
> 1 4
> 2 2
> Any help would be appreciated. thanks,.|||I miswrote what I was looking for. Actually looking for the numbers of a
range. such as:
0 thru 9 22
10 thru 19 89
20 thru 29 34
or should this be done in a presentation layer.
thanks,
"Aaron [SQL Server MVP]" wrote:
> SELECT num, COUNT(*)
> FROM table
> GROUP BY num
> ORDER BY num
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> > have a table with records ranging from 0 to 100. some are repeated
> multiple
> > times, such as:
> > 0
> > 0
> > 1
> > 1
> > 1
> > 1
> > 2
> > 2
> > trying to figure out how to get a result that list the numbers and how
> many
> > times they each occur. such as.
> > num times
> > 0 2
> > 1 4
> > 2 2
> >
> > Any help would be appreciated. thanks,.
>
>|||If you have a finite set of numbers, you could write some really elaborate
CASE logic. But I think this kind of thing is better done at the
presentation layer.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:A7B99A2F-BF8A-46D0-B85F-78215711294A@.microsoft.com...
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.|||That can easily be done by applying integer division. In the query
below, NumRange represents the lower bound of the range.
create table #t (num int,val int)
insert into #t values (0,1)
insert into #t values (1,1)
insert into #t values (9,2)
insert into #t values (10,3)
insert into #t values (15,8)
SELECT (num/10)*10 NumRange,SUM(val)
FROM #t
GROUP BY (num/10)*10
ORDER BY NumRange
drop table #t
Gert-Jan
Chad wrote:
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
> thanks,
> "Aaron [SQL Server MVP]" wrote:
> > SELECT num, COUNT(*)
> > FROM table
> > GROUP BY num
> > ORDER BY num
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Chad" <Chad@.discussions.microsoft.com> wrote in message
> > news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> > > have a table with records ranging from 0 to 100. some are repeated
> > multiple
> > > times, such as:
> > > 0
> > > 0
> > > 1
> > > 1
> > > 1
> > > 1
> > > 2
> > > 2
> > > trying to figure out how to get a result that list the numbers and how
> > many
> > > times they each occur. such as.
> > > num times
> > > 0 2
> > > 1 4
> > > 2 2
> > >
> > > Any help would be appreciated. thanks,.
> >
> >
> >

Query goes from 1 second to 40 minutes with only 1 more record?

I have a very strange problem where I have a simple select count(*) with a handle of of joined tables that works fine and returns a count of records in under 1 second when the criteria in the where clause limits the count to 36984. But if I change the criteria to where the number of records would be 1 higher, 36985, the query takes ~40 minutes!

Here's the query

SELECT count(*)
FROM AT JOIN Al ON Al.AlID= AT.ALID
JOIN DProfile ON Al.DProfileID= DProfile.DProfileID
JOIN Label ON DProfile.LabelID = Label.LabelID
JOIN PricingTier ON PricingTier.LabelID = DProfile.LabelID
JOIN PricingTarget on PricingTarget.PricingTargetID = PricingTier.PricingTargetID
JOIN ATP ON ATP.PricingTierID = PricingTier.PricingTierID
WHERE PricingTarget.Target = 'AT'
AND PricingTier.MaxAgeInDays = 0
AND ATP.LengthMultiple = 0
AND AT.ATID > 408095
AND AT.ATID < 451199

Notice the part of the where clause in red. This is how I'm changing the number of rows that select count(*) should find.

Here's the output from showplan

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[AL].[ALID])=([MGN2].[dbo].[AT].[ALID]))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[PricingTier].[PricingTargetID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[ATP].[PricingTierID]))
| | | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATP].[PK_ATPID]), WHERE:([MGN2].[dbo].[ATP].[LengthMultiple]=(0)))
| | | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), SEEK:([MGN2].[dbo].[PricingTier].[PricingTierID]=[MGN2].[dbo].[ATP].[PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), SEEK:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408095) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

Now, if I change the part of the where clause from 'AND AT.ATID > 408095' to 'AND AT.ATID > 408094', increase the number returned by one, the query goes from taking 1 second to 40 minutes. Here's the showplan text that is different, when the only difference in the query is changing the number in the where clause.

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Merge Join(Inner Join, MERGE:([MGN2].[dbo].[PricingTarget].[PricingTargetID])=([MGN2].[dbo].[PricingTier].[PricingTargetID]), RESIDUAL:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]))
|--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[AL].[ALID]=[MGN2].[dbo].[AT].[ALID]))
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[ATPrice].[PricingTierID]=[MGN2].[dbo].[PricingTier].[PricingTierID]))
| |--Sort(ORDER BY:([MGN2].[dbo].[PricingTier].[PricingTargetID] ASC))
| | |--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| | |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATPrice].[PK_ATPriceID]), WHERE:([MGN2].[dbo].[ATPrice].[LengthMultiple]=(0)))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408094) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

I tried increasing the size of the TempDB from the default of 8 MB to 1000 MB for both the files, but it behaves the exact same way. I'm at a loss as to what is causing this dramatic difference for such a simple query. Anyone have an idea?

Thanks!

By the way, this is on SQL Server 2005, if that helps any.|||

Jim:

Good post. I appreciate your inclusion of the plan and the research you put into your question. It appears to me that you are at a plan "crossover" point. What I mean is that you are at the point where the mere addition of one more record to the output range causes the query optimizer to choose a different query plan. In your plan I see many "clustered index scans". A cover index might be appropriate to improve the performance of your query. Can you post the indexes, keys and unique constraints of all of your tables?

What follows below is academic. I am not sure whether I need to include this or not because it looks like you have already done this with your testing, so it looks like you already have a good handle on this. If this is of no interest, just skip the rest.

Dave

I created a mock-up table with 32767 rows of data. One of the columns is a "testDate" column with an associated index. After creating the table I performed a number of select queries with SHOWPLAN_TEXT turned on so that I could discover the filter criteria at which the query plan switched from "INDEX SEEK" to "CLUSTERED INDEX SCAN". I found that when I switch the filter date from "1/31/7" to "1/30/7" that the plan switched. Therefore I ran these two queries with SHOWPLAN _TEXT turned on to illustrate the query plan "crossover" point. I think that your query has a similar issue. My main question to you is was it your intent to find the crossover or is this something that jumped up and bit you? OUCH!

if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.crossoverTest')
)
drop table dbo.crossoverTest
go

create table dbo.crossoverTest
( rid integer
constraint pk_crossoverTest primary key,
filler char (200),
testDate datetime
)
go

create index testDate_ndx
on dbo.crossoverTest (testDate)
go

insert into dbo.crossoverTest
select iter,
'Filler',
dateadd (mi, -17*iter, convert(datetime, '2/3/7'))
from (
select 256*b.number + a.number as iter
from master.dbo.spt_values a (nolock)
inner join master.dbo.spt_values b (nolock)
on a.[name] is null
and b.[name] is null
and b.number <= 127
and a.number <= 255
and 256*b.number + a.number > 0
) as small_iterator

go

update statistics dbo.crossoverTest
go

set showplan_text on
go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/31/7'

go

-- StmtText
-- --
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[crossoverTest]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[crossoverTest].[testDate_ndx]), SEEK:([crossoverTest].[testDate] >= 'Jan 31 2007 12:00AM') ORDERED FORWARD)

-- Table 'crossoverTest'. Scan count 1, logical reads 813, physical reads 0, read-ahead reads 0.

go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/30/7'

go

-- StmtText
--
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[crossoverTest].[pk_crossoverTest]), WHERE:([crossoverTest].[testDate]>='Jan 30 2007 12:00AM'))

-- Table 'crossoverTest'. Scan count 1, logical reads 913, physical reads 0, read-ahead reads 0.

go

set showplan_text off
go

|||

Thanks Dave,

Ultimately, a simple index was the issue. What perplexed me was just that the difference between the queries when it returned one more row was soooo dramatic. And to answer your question, I had just come upon the crossover issue through trial and error. I hadnt noticed the query plans were different until I got to the point that 1 row was the difference between success and failure.

query function in stored proc... how to do it?

how can i view all the records in my stored procedure?

how is the query function done?

for example i had my datagrid view... and of course a view button that will trigger a view action in able to view the entire records that i input....

i am not familiar with such things..

pls help me to figure this out..

thanks..

im just a begginer when it comes to this..

pls help me..

thanks..

Are you asking about how to do this from your application code or from toosl like SSMS ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Query for most recent of duplicate records

I need some ideas on this query.
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:

> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:

> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>

Query Date Type - Somebody help me!p

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.where u have

Select * from Apro where data between Inicio and Final

should data be date ?? --> Date - Datetime
or u did that on purpose

Can u output the record source that is being executed ?

Query Date Type

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates
with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Leonardo,

There are two problems in your code:

1. Parameters should start with a colon. (e.g. :Inicio and :Final)
2. You have used Parameters[0] and Parameters[2], what about Parameters[1]!?

So this is the correct code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'Select * from Apro where data between Inicio and Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Good luck,
Shervin

"Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
news:3f8d396f$0$199$75868355@.news.frii.net...
>
> Hi,
> I have a table with the follow fields :
> ID - Int
> Date - Datetime
> I need to make a simple query to result the records between to dates
> with a single ID.
> Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> Im using the follow :
> ADOQuery1.Close;
> ADOQuery1.SQL.Clear;
> ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> Final');
> ADOQuery1.Parameters[0].Value:=Inicio;
> ADOQuery1.Parameters[2].Value:=Final;
> ADOQuery1.Open;
> When I open the query it doesnt work cos its result a null set
> How can I solve this?
> Im using SQL Server 2000 and Delphi 6
> Thanks for atention.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Oops! Sorry I forgot to add colons before parameters :-) This is the correct
code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'select * from Apro where data between :Inicio and :Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Shervin

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:voquva8in6re84@.corp.supernews.com...
> Leonardo,
> There are two problems in your code:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;
> Good luck,
> Shervin
>
> "Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
> news:3f8d396f$0$199$75868355@.news.frii.net...
> > Hi,
> > I have a table with the follow fields :
> > ID - Int
> > Date - Datetime
> > I need to make a simple query to result the records between to dates
> > with a single ID.
> > Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> > Im using the follow :
> > ADOQuery1.Close;
> > ADOQuery1.SQL.Clear;
> > ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> > Final');
> > ADOQuery1.Parameters[0].Value:=Inicio;
> > ADOQuery1.Parameters[2].Value:=Final;
> > ADOQuery1.Open;
> > When I open the query it doesnt work cos its result a null set
> > How can I solve this?
> > Im using SQL Server 2000 and Delphi 6
> > Thanks for atention.
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
> Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;

Shervin, did you not for get the colons?

Select * from Apro where data between :Inicio and :Final

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You are right, I forgot them. I fixed it.
Thanks Erland.

Shervin

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9415F34D56661Yazorman@.127.0.0.1...
> Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> > 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> > 2. You have used Parameters[0] and Parameters[2], what about
> > Parameters[1]!?
> > So this is the correct code:
> > with ADOQuery1 do
> > begin
> > Close;
> > SQL.Text := 'Select * from Apro where data between Inicio and Final';
> > Parameters[0].Value := Inicio;
> > Parameters[1].Value := Final;
> > Open;
> > end;
> Shervin, did you not for get the colons?
> Select * from Apro where data between :Inicio and :Final
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

query by time

I am trying to query a db by date and time ie I want records from 9/2/2007 to 9/22/2007 (selectable @.startdate and @.enddate) also i want to be able to sort by time ie shift (1st 7:00am to 3:00pm 2nd 3:00 pm to 11:00 pm 3rd 11:00pm to 7:00am) i can query for the date but not sure how to by time. my db data is coming in 9/13/2007 10:35:18 AM

Thanks

Ed

Quote:

Originally Posted by esadler

I am trying to query a db by date and time ie I want records from 9/2/2007 to 9/22/2007 (selectable @.startdate and @.enddate) also i want to be able to sort by time ie shift (1st 7:00am to 3:00pm 2nd 3:00 pm to 11:00 pm 3rd 11:00pm to 7:00am) i can query for the date but not sure how to by time. my db data is coming in 9/13/2007 10:35:18 AM

Thanks

Ed

consider the BETWEEN operator (is it an operator or clause or pharse :D )...

search the online help

Query Assistance Requested.. Summing Data

Howdy gang,
Quick query question for the query gurus
I want to crete a query that SUMs a field for the last 7 records in its
own field.
Here is my @.example table
Date Money
1/1/01 $100
1/2/01 $200
1/3/01 $300
1/4/01 $100
1/5/01 $200
1/6/01 $200
1/7/01 $500
1/8/01 $200
1/9/01 $400
1/10/01 $100
1/11/01 $200
1/12/01 $100
1/13/01 $100
1/14/01 $200
1/15/01 $700
1/16/01 $100
1/17/01 $400
**This is what I am running**
Select Date, Money,
Case When (Select DATENAME(dw,Date)) = 'Monday'
Then (Select SUM(Money) from @.Example
Where Date between DateAdd(Day,-7,Date) and Date)
Else NULL End as WeekSalesTotal
>From @.Example
What I am trying to accomplish:
When it is Monday, I want to add the previous 7 days Money together
into a new field we will call WeekSalesTotal. All records for days
other than Monday the WeekSalesTotal field would be NULL. My table
should look like following if done correctly.
Date Money WeekSalesTotal
1/1/01 $100 $100
1/2/01 $200 NULL
1/3/01 $300 NULL
1/4/01 $100 NULL
1/5/01 $200 NULL
1/6/01 $200 NULL
1/7/01 $500 NULL
1/8/01 $200 $1500
1/9/01 $400 NULL
1/10/01 $100 NULL
1/11/01 $200 NULL
1/12/01 $100 NULL
1/13/01 $100 NULL
1/14/01 $200 NULL
1/15/01 $700 $1800
1/16/01 $100 NULL
1/17/01 $400 NULL
I have been staring at this issue for a little bit and was hoping fresh
eyes on it would help.
Thank You
On 2 Nov 2005 09:37:44 -0800, EvilReportingGenius wrote:
(snip)[vbcol=seagreen]
>**This is what I am running**
>Select Date, Money,
> Case When (Select DATENAME(dw,Date)) = 'Monday'
> Then (Select SUM(Money) from @.Example
> Where Date between DateAdd(Day,-7,Date) and Date)
> Else NULL End as WeekSalesTotal
Hi EvilReportingGenius,
Try what happens if you change this to:
Select Date, Money,
Case When (Select DATENAME(dw,Date)) = 'Monday'
Then (Select SUM(Money) from @.Example AS b
Where b.Date between DateAdd(Day,-7,a.Date) and a.Date)
Else NULL End as WeekSalesTotal
From @.Example AS a
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)