Showing posts with label amount. Show all posts
Showing posts with label amount. Show all posts

Sunday, March 25, 2012

a count query


Hi all,
I need to built an efficient query that would be able to tell me
the amount of times that the field 'mark' has an 'x'. It should count
only one time for a given id. In the following example for id 23 the
count should be only 1 even though it occurs more than once.
At the end, the total count for the example should be 4
instead of 6.
Table A
id mark
== =====
23 x
23 x
25 x
27 x
27
27 x
30
31 x
31
35
Thanks in advance,
CarlosCarlos wrote:
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>
SELECT DISTINCT
id,
CASE WHEN mark = 'x' THEN 1 ELSE 0 END AS markcount
FROM table|||Try,
select count(distinct [id]) from tableA where mark = 'x'
AMB
"Carlos" wrote:

>
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>|||CREATE TABLE #tbl
(
id INT,
Mark char(1)
);
SET NOCOUNT ON;
INSERT #tbl
SELECT 23,'x'
UNION ALL SELECT 23,'x'
UNION ALL SELECT 25,'x'
UNION ALL SELECT 27,'x'
UNION ALL SELECT 27,''
UNION ALL SELECT 27,'x'
UNION ALL SELECT 30,''
UNION ALL SELECT 31,'x'
UNION ALL SELECT 31,''
UNION ALL SELECT 35,'';
SELECT MarkCount = SUM(c)
FROM
(
SELECT
id,
c = MAX(CASE mark WHEN 'x' THEN 1 ELSE 0 END)
FROM #tbl
GROUP BY id
) x
DROP TABLE #tbl;
"Carlos" <ch_sanin@.yahoo.com> wrote in message
news:%23ODaJQ%23jGHA.1324@.TK2MSFTNGP04.phx.gbl...
>
> Hi all,
> I need to built an efficient query that would be able to tell me
> the amount of times that the field 'mark' has an 'x'. It should count
> only one time for a given id. In the following example for id 23 the
> count should be only 1 even though it occurs more than once.
> At the end, the total count for the example should be 4
> instead of 6.
>
> Table A
> id mark
> == =====
> 23 x
> 23 x
> 25 x
> 27 x
> 27
> 27 x
> 30
> 31 x
> 31
> 35
> Thanks in advance,
> Carlos
>
>|||Tracy McKibben wrote:
> Carlos wrote:
> SELECT DISTINCT
> id,
> CASE WHEN mark = 'x' THEN 1 ELSE 0 END AS markcount
> FROM table
Sorry, copy/pasted the wrong block from QA... What I meant to post was:
SELECT DISTINCT
id,
1 AS markcount
FROM table
WHERE mark = 'x'sql

Tuesday, March 20, 2012

A Bug With Running Total Query?

Using SQL Server 2005
Query Result From 1 Table with 3 Cols:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
7/1/2005 415.0000 830.0000
8/1/2005 415.0000 1245.0000
9/1/2005 415.0000 1660.0000
10/1/2005 415.0000 2075.0000
11/1/2005 415.0000 2490.0000
12/1/2005 415.0000 2905.0000
1/1/2006 415.0000 3320.0000
2/1/2006 415.0000 3735.0000
3/1/2006 415.0000 4150.0000
4/1/2006 415.0000 4565.0000
5/1/2006 415.0000 4980.0000
Query:
SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
RunningTotal
FROM AmountTable AS Table1 INNER JOIN
AmountTable AS Table2 ON Table1.AmountDate >=
Table2.AmountDate
GROUP BY Table1.AmountDate, Table1.Amount
ORDER BY Table1.AmountDate
The problem is with the 7/1 date sum. It only added 100 and not 415
Does anybody see a problem I did or is this something I should post to MSDN
Product FeedBack?
The query was a bit more complex when I unioned 2 tables and the amount was
not coming out right and I broke it down very simply and found this to be a
n
issue.
On a side note relating to the union relation and doing a running total, I
only had 1 Amount in the other table for now and for some reason, that wasn'
t
summing with the rest of the set.
SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount
INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
CROSS JOIN
(SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
ORDER BY AmountTable1.AmountDate
Produces:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
6/3/2005 10.0000 20.0000*****From Table1
7/1/2005 415.0000 1245.0000
8/1/2005 415.0000 1660.0000
9/1/2005 415.0000 2075.0000
10/1/2005 415.0000 2490.0000
11/1/2005 415.0000 2905.0000
12/1/2005 415.0000 3320.0000
1/1/2006 415.0000 3735.0000
2/1/2006 415.0000 4150.0000
3/1/2006 415.0000 4565.0000
4/1/2006 415.0000 4980.0000
5/1/2006 415.0000 5395.0000
6/1/2006 415.0000 5810.0000
So that 6/3 Amount from Table1 is excluded from summing with the rest of the
set.
I thought it would have been included with the Union.
Any input is welcome,
NathanHi
This should be posted to the beta newsgroups along with version number, DDL
and sample data so that the problem can be recreated.
http://communities.microsoft.com/ne...onalserver.tsql
John
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:C771A09A-69DA-49D0-B71C-5C541E15EC51@.microsoft.com...
> Using SQL Server 2005
> Query Result From 1 Table with 3 Cols:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 7/1/2005 415.0000 830.0000
> 8/1/2005 415.0000 1245.0000
> 9/1/2005 415.0000 1660.0000
> 10/1/2005 415.0000 2075.0000
> 11/1/2005 415.0000 2490.0000
> 12/1/2005 415.0000 2905.0000
> 1/1/2006 415.0000 3320.0000
> 2/1/2006 415.0000 3735.0000
> 3/1/2006 415.0000 4150.0000
> 4/1/2006 415.0000 4565.0000
> 5/1/2006 415.0000 4980.0000
> Query:
> SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
> RunningTotal
> FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
> Table2.AmountDate
> GROUP BY Table1.AmountDate, Table1.Amount
> ORDER BY Table1.AmountDate
> The problem is with the 7/1 date sum. It only added 100 and not 415
> Does anybody see a problem I did or is this something I should post to
> MSDN
> Product FeedBack?
> The query was a bit more complex when I unioned 2 tables and the amount
> was
> not coming out right and I broke it down very simply and found this to be
> an
> issue.
> On a side note relating to the union relation and doing a running total, I
> only had 1 Amount in the other table for now and for some reason, that
> wasn't
> summing with the rest of the set.
> SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
> FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate,
> Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
> WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
> GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
> ORDER BY AmountTable1.AmountDate
> Produces:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 6/3/2005 10.0000 20.0000*****From Table1
> 7/1/2005 415.0000 1245.0000
> 8/1/2005 415.0000 1660.0000
> 9/1/2005 415.0000 2075.0000
> 10/1/2005 415.0000 2490.0000
> 11/1/2005 415.0000 2905.0000
> 12/1/2005 415.0000 3320.0000
> 1/1/2006 415.0000 3735.0000
> 2/1/2006 415.0000 4150.0000
> 3/1/2006 415.0000 4565.0000
> 4/1/2006 415.0000 4980.0000
> 5/1/2006 415.0000 5395.0000
> 6/1/2006 415.0000 5810.0000
> So that 6/3 Amount from Table1 is excluded from summing with the rest of
> the
> set.
> I thought it would have been included with the Union.
> Any input is welcome,
> Nathan|||
Nathan wrote:

>Using SQL Server 2005
>Query Result From 1 Table with 3 Cols:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>7/1/2005 415.0000 830.0000
>8/1/2005 415.0000 1245.0000
>9/1/2005 415.0000 1660.0000
>10/1/2005 415.0000 2075.0000
>11/1/2005 415.0000 2490.0000
>12/1/2005 415.0000 2905.0000
>1/1/2006 415.0000 3320.0000
>2/1/2006 415.0000 3735.0000
>3/1/2006 415.0000 4150.0000
>4/1/2006 415.0000 4565.0000
>5/1/2006 415.0000 4980.0000
>Query:
>SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
>RunningTotal
>FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
>Table2.AmountDate
>GROUP BY Table1.AmountDate, Table1.Amount
>ORDER BY Table1.AmountDate
>
Nathan,
The results are correct for this query. You are grouping by and summing
the same column: Table1.Amount. Table2 only determines how many times
each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
Table1.Amount:
Table1.Amount*(number of rows in the table with earlier or the same date).
Note that your "running sums" are not that at all. They are multiples:
730*1 = 730
415*2 = 830
415*3 = 1245
415*4 = 1660
...
415*12 = 4980
If you want running sums, you will need SUM(Table2.Amount).
Steve Kass
Drew University

>The problem is with the 7/1 date sum. It only added 100 and not 415
>Does anybody see a problem I did or is this something I should post to MSDN
>Product FeedBack?
>The query was a bit more complex when I unioned 2 tables and the amount was
>not coming out right and I broke it down very simply and found this to be
an
>issue.
>On a side note relating to the union relation and doing a running total, I
>only had 1 Amount in the other table for now and for some reason, that wasn
't
>summing with the rest of the set.
>SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
>FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmou
nt
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
>WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
>GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
>ORDER BY AmountTable1.AmountDate
>Produces:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>6/3/2005 10.0000 20.0000*****From Table1
>7/1/2005 415.0000 1245.0000
>8/1/2005 415.0000 1660.0000
>9/1/2005 415.0000 2075.0000
>10/1/2005 415.0000 2490.0000
>11/1/2005 415.0000 2905.0000
>12/1/2005 415.0000 3320.0000
>1/1/2006 415.0000 3735.0000
>2/1/2006 415.0000 4150.0000
>3/1/2006 415.0000 4565.0000
>4/1/2006 415.0000 4980.0000
>5/1/2006 415.0000 5395.0000
>6/1/2006 415.0000 5810.0000
>So that 6/3 Amount from Table1 is excluded from summing with the rest of th
e
>set.
>I thought it would have been included with the Union.
>Any input is welcome,
>Nathan
>|||AAAAHHHHHH!!!!
I can't believe I didn't catch that one. I was working on the original issue
for so long that ended up going blind.
Kudos for the good catch Steve
Nathan
"Steve Kass" wrote:

>
> Nathan wrote:
>
> Nathan,
> The results are correct for this query. You are grouping by and summing
> the same column: Table1.Amount. Table2 only determines how many times
> each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
> Table1.Amount:
> Table1.Amount*(number of rows in the table with earlier or the same date).
> Note that your "running sums" are not that at all. They are multiples:
> 730*1 = 730
> 415*2 = 830
> 415*3 = 1245
> 415*4 = 1660
> ...
> 415*12 = 4980
>
> If you want running sums, you will need SUM(Table2.Amount).
> Steve Kass
> Drew University
>
>

Thursday, March 8, 2012

70-431 (MCTS - SQL 2005)

Hi,

Somebody can speak (tips) about exam 70-431?

Tips?

Amount Questions?

Time ?

....

If you're looking for brain dumps this is not the site to get those answers. The 70-431 exam is a 200-level "how-to" exam on all the nuts and bolts of SQL Server 2005. There are free eLearning classes available on the Microsoft web site which will help you with the content information for the test, as well as a number of excellent books hitting the market about now.

I believe you'll get about 40 questions on the test, though it may be higher than that, and you'll get about 2 1/2 hours to answer them.

Go to www.microsoft.com/sql and click on Learning and you'll find out all you need to know to prepare for the test.

Good luck.

|||

What is the passing mark/percentage for MCTS?

|||

Hi Gunny,

is 700 scored.

|||
E X A M S P E C S
Exam Number: 70-431
Active / Retired: Active
Prerequisites:
Exam Format: Multiple-Choice; Multiple Answer
Num Questions: 55
Time Limit: 90
Cost (USD) $: $125
Passing Score: 700

taken from cramsession's site: http://www.cramsession.com/certifications/exams/sql-server-2005.asp?exam_id=770

also use this code for 20% of the exam price

Call now to schedule your Microsoft exam call 1-800-247-8731 and use code MSUU5C7E0100 for your 20% discount.

|||

Many thanks WPH and rkohler.

That's 700 out of how many? What is the max possible score?

|||1000 max scored.|||

Thanks WPH.

|||

any one can respon

i have two server one is dpmain controller and second is exchange 2003 server i need to setup sql 2005

which one i choice ?

|||

a.m.m.e.

Your question really belongs in a separate/new thread. This thread is about MCTS.

But to answer your question, I would advise against putting it on the domain controller. The domain controller contains the Active Directory and security is extremely important. Adding any other role such as SQL Server would complicate security configuration for it.

Actually, if possible, I would set up a new dedicated SQL Server - not add it to the Exchange Server 2003. Mixing multiple roles on one server always complicates security. But if you cannot set up a new dedicated server I would add it to the Exchange Server 2003.

Other more experienced people may have different ideas.

|||

Hello everyone,

I would like to let you all know and be aware of the fact that microsoft has changed its exam pattern for

070-431 be very carefull as microsoft has changed the exam pattern and due to which i failed the exam today.

Its now based on simulations questions which comes after the multiple choice questions i got 15 simulations

separately apart from 35 multi choice questions along with exibits. I wasnt aware of this fact and it wasnt

mentioned ánywhere about simulations questions I think no one knows about it I am the first to face this type of

exam.. The exams comes up with 2 sections the first sections was questions that was 35 in 1hr and the next

was simulations that was 15 2hr 15mins so in total it was 195min exam and I got all correct in first section

and the second section was gred out 60% I got 560 out of 700 may not be difficult but very new.

If in case you have come across with any good site with simulations of this exam please forward me to

a_afroze@.yahoo.com

All the best to all of you

Abdul Afroze

|||

Hello everyone,

I would like to let you all know and be aware of the fact that microsoft has changed its exam pattern for

070-431 be very carefull as microsoft has changed the exam pattern and due to which i failed the exam today.

Its now based on simulations questions which comes after the multiple choice questions i got 15 simulations

separately apart from 35 multi choice questions along with exibits. I wasnt aware of this fact and it wasnt

mentioned ánywhere about simulations questions I think no one knows about it I am the first to face this type of

exam.. The exams comes up with 2 sections the first sections was questions that was 35 in 1hr and the next

was simulations that was 15 2hr 15mins so in total it was 195min exam and I got all correct in first section

and the second section was gred out 60% I got 560 out of 700 may not be difficult but very new.

If in case you have come across with any good site with simulations of this exam please forward me to

a_afroze@.yahoo.com

All the best to all of you

Abdul Afroze

|||

Thanks for the information. I'm going to be taking the test soon and was afraid they would do something like this. They did this to me on the 70-228... completely changed the content and format from what my rather expensive test prep materials were based on.

Could you give us a clue as to what the simulations focused on? (ie. TSQL, SSMS, etc.) I can do just about everything you need to do in SSMS and other GUI tools, but I don't have all the full syntaxes for the TSQL methods memorized (ie. create HTTP endpoint for SOAP vs. TCP endpoint for mirroring).

|||

Well, this really isn't a database documentation request, and using a questions-only site really isn't the best way to go. There are reputable firms, such as Transcender, that will help you with testing and so on. The point is that you should try and learn the trade, rather than just memorizing questions. I've posted an article that deals with certifications:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=131

And another here dealing with being a DBA. I think you may find those more useful than looking up test questions on a site.

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=247

These are just my thoughts - I do wish you luck in your studies!

|||

Many thanks, Abdul, for that "heads up".

If you or anyone else here come to know of any test exams based on this new pattern I am sure we all would be eager to know about them as soon as possible.

Regards