query assistance

Saturday, February 25, 2012

query based on a field in the same row correction

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
A0_01 1,37
(18-ItemPersonPersonal)+ItemPinax_AMM
A0_02 1,4
(18-ItemPersonPersonal)+ItemPinax_AMM
I need to calculate the value of ItemPinax_FUNCTION taking the value of
ItemPersonPersonal from table ItemPerson who has these fields
ItemPersonAFM ItemPersonCode ItemPersonPersonal
041 A0_01 10
041 A0_02 12
the answer must be for the first row (18-10)+1,37
for the second row (18-12)+1,4
I use this code
DECLARE @.sql VARCHAR(655)
SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
where ItemPersonAFM='041'
EXEC(@.sql)
and I get 1,37 and 1,4. How can I have the right answers?Helen
DDL Means table definition and sample data means insert commadns that you
use. so that we can simulate here
Regards
R.D
"Helen" wrote:

>
>
> I have two tables ItemsPinax , ItemPerson. The first table has these fie
lds
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
> A0_01 1,37
> (18-ItemPersonPersonal)+ItemPinax_AMM
> A0_02 1,4
> (18-ItemPersonPersonal)+ItemPinax_AMM
> I need to calculate the value of ItemPinax_FUNCTION taking the value of
> ItemPersonPersonal from table ItemPerson who has these fields
> ItemPersonAFM ItemPersonCode ItemPersonPersonal
> 041 A0_01 10
> 041 A0_02 12
> the answer must be for the first row (18-10)+1,37
> for the second row (18-12)+1,4
> I use this code
> DECLARE @.sql VARCHAR(655)
> SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
> ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> where ItemPersonAFM='041'
> EXEC(@.sql)
> and I get 1,37 and 1,4. How can I have the right answers?|||Helen
CREATE TABLE TAB1 (COL1 INT) IS DDL
INSERT INTO TAB1 VALUES(1) is sample data
Regards
R.D
"R.D" wrote:
> Helen
> DDL Means table definition and sample data means insert commadns that you
> use. so that we can simulate here
> Regards
> R.D
> "Helen" wrote:
>|||CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
decimal ,ItemPinax_FUNCTION varchar(100)
INSERT INTO ItemsPinax
VALUES( A0_01, 1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
varchar(6), ItemPersonPersonal int)
INSERT INTO ItemPerson VALUES(041, A0_01, 10)|||> > the answer must be for the first row (18-10)+1,37
what is 1,37 and 1,4 ?
I dont really understand what exactly you want calculated feild or
substracted from function definition.
it is already 6 pm here, we will tomorrow
Regards
R.D
"Helen" wrote:
> CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
> decimal ,ItemPinax_FUNCTION varchar(100)
> INSERT INTO ItemsPinax
> VALUES( A0_01, 1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
> CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
> varchar(6), ItemPersonPersonal int)
> INSERT INTO ItemPerson VALUES(041, A0_01, 10)
>
>|||the answer must be for the first row (18-10)+1.37
1.37 AND 1.4 IS THE VALUES FOR ItemPinax_AMM WHICH I USE FOR THE CALCULATION
"R.D" wrote:
> what is 1,37 and 1,4 ?
> I dont really understand what exactly you want calculated feild or
> substracted from function definition.
> it is already 6 pm here, we will tomorrow
> Regards
> R.D
> "Helen" wrote:
>

query based on a field in the same row

Query Average Help

Hello I have the following table and data. I need to find the avg game attendance for homegame (where shcool = 'Indiana Univ.' and away games ( where opponent = 'Indiana Univ." This would be 3 columns listing the SCHOOL 'Indiana Univ.", AVG HOMEGAME ATTENDANCE, AVG AWAY ATTENDANCE. I have no clue how to format the query to get the last column.

- Thanks for your help and sugestions.

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 43000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 42000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 41000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 40000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 39000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 38000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 37000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 36000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 51000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 50000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 49000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 48000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 47000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 43000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 42000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, 'Indiana Univ.', 41000, 0, 7, null, null);select 1,avg(goals) from table where it is home
union
select 2,avg(goals) from table where it is not home|||UNION is nice, but it returns 2 rows

Try

SELECT 'Indiana Univ.' as school,
avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
avg(case opponent when 'Indiana Univ.' then attendance else null end) AS avg_awaygame
from homegame;

It returns :

SCHOOL AVG_HOMEGAME AVG_AWAYGAME
Indiana Univ. 41000 46000|||- Thanks for the Reply. Never used case before. I have tried moving the parethesis aronund bet keep getting the belower error?

SQL> SELECT 'Indiana Univ.' as school,
2 avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
3 avg(case opponent when 'Indiana Univ.' then attendance else null end) AS avg_awaygame
4 from homegame;
avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
*
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL>|||I guess you are using an Oracle version that doesn't support CASE (e.g. 8.1.6)

Instead, you can use the good old DECODE.

This does not work :
SQL> SELECT (CASE 1 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END) FROM DUAL;
SELECT (CASE 1 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END) FROM DUAL
*
ERROR at line 1:
ORA-00907: missing right parenthesis

But this might work :
SQL> SELECT DECODE(1,1,'TRUE','FALSE') FROM DUAL;

DECO
--
TRUE

So, in your case :
SELECT 'Indiana Univ.' as school,
avg(decode(school,'Indiana Univ.',attendance,null)) AS avg_homegame,
avg(decode(opponent,'Indiana Univ.',attendance,null)) AS avg_awaygame
FROM homegame;

query at runtime

Hi all,
Is there way in reporting service programming to give the sql select query
for a report during runtime.
Thanks
rsuserYou can have your query be an expression. Be in the generic query designer
(two panes). The button to switch to this is to the right of the ...
Put in an expression.
="select somefield, someotherfield from mytable where startdate>= '" &
parameters!ParamName & "' order by " & parameters!AnotherParam
Note that you have to put your own single quotes where appropriate. What I
do is first have a report with just a textbox with the expression so I can
see it and see if I have created the query appropriately. Then I assign it
to a dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> Hi all,
> Is there way in reporting service programming to give the sql select query
> for a report during runtime.
> Thanks
> rsuser|||Just adding to Bruce's description:
Once you are using an expression-based command-text, you can no longer
execute the query in the report designer data view. Also you can no longer
refresh the fields list. That's why it is important to first finish the
report design based on a constant command text and use Bruce's suggested
approach of a textbox to look at the expression results - before you change
the command text to be expression-based.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> You can have your query be an expression. Be in the generic query designer
> (two panes). The button to switch to this is to the right of the ...
> Put in an expression.
> ="select somefield, someotherfield from mytable where startdate>= '" &
> parameters!ParamName & "' order by " & parameters!AnotherParam
> Note that you have to put your own single quotes where appropriate. What I
> do is first have a report with just a textbox with the expression so I can
> see it and see if I have created the query appropriately. Then I assign it
> to a dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
>> Hi all,
>> Is there way in reporting service programming to give the sql select
>> query
>> for a report during runtime.
>> Thanks
>> rsuser
>|||thanks!! I got it working. I have other issues. Please help me on these
1)Is there a way to supress the image when the report is
displayed in browser and then make them available for printing on paper
alone? is there a way to use any javascript or any other way to do this?
2)Say i have a dot net application which will display reports using
webservices and reports are using custom assembly which requires read
permission. Then while installing the dot net application in the
end users system, how the policy files will be configured. is there a way to
set up client's report server policy files through dot net code?
Thanks,
rsuser
"Robert Bruckner [MSFT]" wrote:
> Just adding to Bruce's description:
> Once you are using an expression-based command-text, you can no longer
> execute the query in the report designer data view. Also you can no longer
> refresh the fields list. That's why it is important to first finish the
> report design based on a constant command text and use Bruce's suggested
> approach of a textbox to look at the expression results - before you change
> the command text to be expression-based.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> > You can have your query be an expression. Be in the generic query designer
> > (two panes). The button to switch to this is to the right of the ...
> >
> > Put in an expression.
> >
> > ="select somefield, someotherfield from mytable where startdate>= '" &
> > parameters!ParamName & "' order by " & parameters!AnotherParam
> >
> > Note that you have to put your own single quotes where appropriate. What I
> > do is first have a report with just a textbox with the expression so I can
> > see it and see if I have created the query appropriately. Then I assign it
> > to a dataset.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> > news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> >> Hi all,
> >>
> >> Is there way in reporting service programming to give the sql select
> >> query
> >> for a report during runtime.
> >>
> >> Thanks
> >> rsuser
> >
> >
>
>

Query Assistance.

I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.

Source table.

Address ID Workstation Test-a 1 WS1 Test-b 2 WS1 Test-a 5 WS2 Test-d 3 WS2 Test-b 7 WS2

I am trying to write a query that will display this result into Excel.

Address Duplicate WS1 WS2 Test-a
Yes
1
5 Test-b
Yes
2
7 Test-d
No

Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.

Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.

I appreciate any help I can get on this.

What are the possible values for the Workstation column? Is it always at most 2 workstations for any address?|||No, there are actually around 8-10 workstations.
|||

This should give you an idea about how to approach the solution.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Address varchar(20),
[ID] int,
Workstation varchar(20)
)

INSERT INTO @.MyTable VALUES ( 'Test-a', 1, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 2, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-a', 5, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-d', 3, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 7, 'WS2' )

SELECT
Duplicate = CASE WHEN dt.ADDRESS IS NULL THEN 'No' ELSE 'Yes' END,
m.Address,
m.[ID],
m.Workstation
FROM @.MyTable m
LEFT JOIN (SELECT Address
FROM @.MyTable
GROUP BY
Address
HAVING count( Address ) >= 2
) dt
ON m.Address = dt.Address
ORDER BY
Address,
Workstation

Duplicate Address ID Workstation
-- -- --
Yes Test-a 1 WS1
Yes Test-a 5 WS2
Yes Test-b 2 WS1
Yes Test-b 7 WS2
No Test-d 3 WS2

|||I appreciate the information. I have been working with this to pull the data I need. I have modified the insert statement to pull the data from another table.

I am getting an error however:
Server: Msg 209, Level 16, State 1, Line 15
Ambiguous column name 'PointAddress'.

As I mentioned I am new to alot of this, how do I identify line 15? I have searched on the error itself and found references that I should be creating an alias for the "pointaddress" column.

I appreciate the help with this. Explanations will be helpful as well.

Code Snippet

SET NOCOUNT ON

DECLARE @.dup_trnd table
( RowID int IDENTITY,
PointAddress varchar(30),
TrendID int,
Workstation varchar(30)
)

INSERT dup_trnd
SELECT PointAddress,TrendID,Workstation from DuplicateTrends

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
m.PointAddress,
m.TrendId,
m.Workstation
FROM @.dup_trnd m
LEFT JOIN (SELECT PointAddress
FROM @.dup_trnd
GROUP BY
PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON m.PointAddress = dt.PointAddress
ORDER BY
PointAddress,
Workstation

|||

Try this:

Code Snippet

SET NOCOUNT ON

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
d.PointAddress,
d.TrendId,
d.Workstation
FROM Dup_Trnd d
LEFT JOIN (SELECT PointAddress
FROM Dup_Trnd
GROUP BY PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON d.PointAddress = dt.PointAddress
ORDER BY
d.PointAddress,
d.Workstation

|||Thank you for the assistance. This is mostly what I need and can work on it from here.

Monday, February 20, 2012

query assistance -return most recent date

I have a table that has two fields, pkg_num, which is a number, and
del_date_time, which is a date-time. The table can contain duplicate pkg_num
values, as long as the del_date_time values are different for any given
number. I need a query that will return the most recent del_date_time for
each pkg_num. Any ideas?
On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:

>I have a table that has two fields, pkg_num, which is a number, and
>del_date_time, which is a date-time. The table can contain duplicate pkg_num
>values, as long as the del_date_time values are different for any given
>number. I need a query that will return the most recent del_date_time for
>each pkg_num. Any ideas?
Hi Rich_A2B,
Probably
SELECT pkg_num, MAX(del_date_time)
FROM MyTable
GROUP BY pkg_num
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That works, thanks! Now to complicate things, I have a third field,
DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
fields in the query result, but only show records with the most recent
DEL_DATE_TIME?
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:
>
> Hi Rich_A2B,
> Probably
> SELECT pkg_num, MAX(del_date_time)
> FROM MyTable
> GROUP BY pkg_num
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||

Quote:

Originally posted by Hugo Kornelis
On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

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)

Saturday, February 25, 2012

query based on a field in the same row correction

query based on a field in the same row

Query Average Help

query at runtime

Query Assistance.

Monday, February 20, 2012

query assistance -return most recent date

Query Assistance Requested.. Summing Data

query assistance

Blog Archive

About Me