Showing posts with label number. Show all posts
Showing posts with label number. Show all posts

Friday, March 30, 2012

Need help bracketing counts

I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?

I have a tblClients with a clientid field

I have a tblPayments with the clientid, pmtdate, and pmtamount

For example:

1 Payment -- 23 Clients -- $16000

2 Payments -- 12 Clients -- $32000

3 Payments -- 4 Clients -- $13000

etc...

If you the the "how many clients" value you must also have such a field in the tblPayments table, or is it one purchased client per payment? If so, then

SELECT c.clientid, count(p.clientid) as clientCount,sum(p.pmtamount) as pmtSum
FROM tblClients c
LEFT JOIN tblPayments p ON c.clientid=p.clientid
WHERE p.pmtdate BETWEEN @.startdate AND @.enddate
GROUP BY c.clientid

|||

SELECTCOUNT(t.clientid)as clients,t.tblPaymentsCount,SUM(t.pmtamount)as pmtamountFROM(SELECT clientidas clientid,COUNT(tblPaymentsID)as tblPaymentsCount,SUM(pmtamount)as pmtamountFROM tblPaymentsGROUP BY clientid) tGROUP BY tblPaymentsCountORDER BY tblPaymentsCount
|||

Now that I saw BradFMJ's suggestion I realize that I quite possibly misunderstood the task

Maybe this is what you're after:

SELECTcount(*)as paymentCount,count(DISTINCT clientid)as clientCount,SUM(pmtamount)as pmtSumWHERE pmtdateBETWEEN @.startDateAND @.endDate

Wednesday, March 21, 2012

Need Cursor Help

Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is thatallthe rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number.

For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 ....

I could really use some help with this cursor:

Declare @.CustomerName as char (60),
@.seqno as int,
@.BaseSeqno as int
set @.Baseseqno = 278300

declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.CustomerName
while @.@.fetch_status=0
begin
set @.seqno = @.BaseSeqno + 1

update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.CustomerName

fetch next from c into @.CustomerName
end
close c
deallocate c

Make sure you are dealing with the right data. Is custnmbr same as customername?
Then, you would modify your sequence generation to

set @.seqno = @.seqno + 1

and set the @.seqno to 278300. You wouldnt need the @.Baseseqno at all. Based on your current logic, you will always gett he same number since you are adding 1 to @.BaseSeqno which is 278300.

Finally, I would recommend looking at alternatives to cursors. Read up books on line regarding cursors. There are pros/cons.|||

Thanks Dinakar for helping. I change my code to what is below and thought that I had things correctly from your explaination but the update still updates all the rows with 278301. Yes, customername is the same as custnmbr so I changed the code to reflect this.

Declare @.custnmbr as char (60),
@.seqno as int
set @.seqno = 278300

declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.custnmbr
while @.@.fetch_status=0
begin
set @.seqno = @.seqno + 1

update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.custnmbr

fetch next from c into @.custnmbr
end
close c
deallocate c
go

I'm a little confused with sequence generation. Isn't set @.seqno = @.segno + 1 in the correct location.

Thanks again for responding.

|||you might want to change the @.custnmbr from char to varchar. char is a fixed length data type. so it pads spaces if the string is less than the specified length. You might also want to add the condition adrscode='BILL TO' in the update statement, so if you have 2 customers with same name you have a higher chance the correct record can get updated.

Need basic technical MSDE information.

Hello, I'm looking for a technical faq for MSDE. I need to know the
essentials for, example, the number of simultaneous connections that MSDE
permits, etc.
Thanks
hi,
"Narwe" <Narwe@.discussions.microsoft.com> ha scritto nel messaggio
news:22828EB8-E242-4E8F-B115-96E7B768D366@.microsoft.com
> Hello, I'm looking for a technical faq for MSDE. I need to know the
> essentials for, example, the number of simultaneous connections that
> MSDE permits, etc.
> Thanks
the pricipal feature can be seen at
http://www.microsoft.com/sql/msde/pr...fo/default.asp
and some comparison beteen edition at
http://msdn.microsoft.com/library/de...ar_ts_1cdv.asp
the specific question, regarding connections, is answered at
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp ,
but you have to keep in mind the Query Workload Governor kicking in when 8
concurrent workloads are executed, like explained in
http://msdn.microsoft.com/library/?u...asp?frame=true
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||www.qmsdnug.org/DAT306_LOW.ppt might also help.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2rllhiF1c75oqU1@.uni-berlin.de...
> hi,
> "Narwe" <Narwe@.discussions.microsoft.com> ha scritto nel messaggio
> news:22828EB8-E242-4E8F-B115-96E7B768D366@.microsoft.com
> the pricipal feature can be seen at
> http://www.microsoft.com/sql/msde/pr...fo/default.asp
> and some comparison beteen edition at
>
http://msdn.microsoft.com/library/de...ar_ts_1cdv.asp
> the specific question, regarding connections, is answered at
>
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp ,
> but you have to keep in mind the Query Workload Governor kicking in when 8
> concurrent workloads are executed, like explained in
>
http://msdn.microsoft.com/library/?u...asp?frame=true
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Need assistance with Duplicate Select Statment

Any help would be appreciated here. My question is two part:

1.) Select duplicates that match any of a number of columns for example...

The email is the same OR

The homephone is the same OR

The mobilephone is the same OR

The address1 is the same

The uniqueID is ConsIntID

My Select Statement: (Which does not work)

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS

WHERE CONSINTID IN (SELECT CONSINTID

FROM CONSULTANTS

HAVING COUNT(HOMEPHONE) > 1 OR COUNT(MOBILEPHONE) >1 OR etc.... )

-

Once the Select Duplicates Statement is corrected I will insert those into a ConsultantsDupe Table

The Second Question is: If I have more then one duplicate my stored_procedure fails

Here is my cursor and logic. The problem is getting multiple results in the subquery and I don't know the best way to correct this.

DECLARE DUPES CURSOR FOR

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS_DUPS

ORDER BY CREATEDATE DESC

OPEN DUPES

FETCH NEXT FROM DUPES

INTO @.CONSINTID, @.FIRSTNAME, @.LASTNAME, @.EMAIL1, @.ADDRESS1, @.HOMEPHONE, @.MOBILEPHONE, @.CREATEdATE, @.USERIDS

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.FOUNDCONSINTID = (SELECT DISTINCT CONSINTID FROM CONSULTANTS_DUPS

WHERE CONSINTID <> @.CONSINTID AND HOMEPHONE = @.HOMEPHONE "NEED TO ADD COMPARISON FOR MOBILEPHONE, EMAIL ADDRESS HERE") <== This can provide more then one result which messes me up.

I'll take a couple shots at this. Hopefully somebody can post something even better. You'll have to forgive me. Without any sample tables I had to "shoot from the hip" and write it without any syntax checking.

SELECT * FROM Consultants C

WHERE EXISTS (SELECT * FROM Consultants CPhone

WHERE (C.HomePhone = CPhone.HomePhone

OR C.HomePhone = CPhone.CellPhone

OR C.CellPhone = CPhone.HomePhone

OR C.CellPhone = CPhone.CellPhone)

AND C.CONSINTID <> CPhone.CONSINTID

)

OR EXISTS (SELECT * FROM Consultants CEmail

WHERE C.EMAIL1 = CEmail.EMAIL1

AND C.CONSINTID <> EMAIL1.CONSINTID)

OR EXISTS ...

Here's another shot:

SELECT * FROM Consultants

WHERE HomePhone IN (SELECT HomePhone

FROM Consultants

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT CellPhone

FROM Consultants

WHERE CellPhone IS NOT NULL

GROUP BY CellPhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

This method suffers from the fact that you can't check HomePhone and CellPhone at the same time (with UNION ALL) because of a contact has the same phone number listed as home and cell then it could show up as a duplicate.

Here's where you might take the previous query as a natural transition to cross-check home phone against cell phone:

SELECT * FROM Consultants C

WHERE HomePhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

You may also be better off with multiple SELECT statements as it's hard to tell the cause of your duplicate in a query like this. Keep in mind that all of these queries are very read-intensive as you're dealing with multiple scans across your table. You may benefit, instead, by placing your duplicates in a temporary table (or table variable) and then querying against the temporary table.

|||something like this:

select *
from consultants
where consintid in
(select consintid
from consultants
group by email
having count(*)>1
union all
select consintid

from consultants

group by homephone

having count(*)>1

union all
select consintid

from consultants

group by cellphone

having count(*)>1

union all
select consintid

from consultants

group by address1

having count(*)>1
)|||

Jared,

Yours worked! This gives me what I need for the duplicate temp table and the assistance is greatly appreciated.

Any feedback on the second portion?

Monday, March 19, 2012

Need all rows within each group to be shown on 1 page

Hi,

I have data that is grouped by a code number. One of the code numbers have over 600 rows, while other code numbers have around 10 to 20 rows within it.

When I run the report the code number that has over 600 rows gets split over 2 pages while the other code numbers each get their own page.

How do I make it so that when I run the report the code number that has over 600 rows gets all displayed on the 1 page instead of being split over 2?

Thanks

Ben.

can anyone help please?|||

Hi ben22,

I'm afraid there is not really a functionality in Reporting Services that does this. I know this is a lack in Reporting Services.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi Ben

Do you just want it to display on 1 page in the browser or
do you require this for printing aswell?

If it is for viewing purposes only, you can try to set the
'Interactive Page size' in the report properties to have a higher width.

G

|||need it both for display in the browser and for printing, as when it gets exported to excell it needs to be in the same sheet instead of being expanded over 2. This is because a certain value in the row gets summed at the end and I need that sum to include all the rows within the group, as when it splits over 2 pages I get two different sums where they should be altogether.|||

Hi ben,

As mentioned before, it is not possible to make the page size dynamic for printing and viewing at the same time.

You mentioned that you receive 2 sum results. It is possible to only have only a total line at the end in case of on each page.

For example:
When you use a Table you can set the total in the Table footer. Even if the table overlaps multiple pages, it will only be displayed at the last page.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Sorry, I should of explain things a bit better before. By groups I meant I have 1 group in the table. This group breaks the data up by the unique field assigned to each data item and uses a group footer to do the sum. For example:

I have 800 cars, 600 are red 100 are blue and 100 are green. So when I run the report is looks like this

Page 1

RED

Name | Model | Price

Car1 | c1 | 20000

Car2 | c2 | 30000

…

Car400 | c400 | 15000

-

Total Price: 3000000

Page 2

RED

Name | Model | Price

Car401 | c401 | 20000

Car402 | c402 | 30000

…

Car600 | c600 | 15000

-

Total Price: 1500000

Page 3

BLUE

Name | Model | Price

Car1 | c1 | 10000

Car2 | c2 | 30000

…

Car100 | c100 | 40000

-

Total Price: 1000000

Page 4

GREEN

Name | Model | Price

Car1 | c1 | 25000

Car2 | c2 | 17000

…

Car100 | c100 | 40000

-

Total Price: 1200000

As you can see there are 4 pages. It splits the RED cars up over 2 pages giving the 2 different sums ie: cars 1 – 400 with the sum 3000000 and then cars 401 – 600 with the sum 1500000. I want my report to work so that the RED cars are all together and not on different pages, so altogether there are only 3 pages. So to look like this:

Page 1

RED

Name | Model | Price

Car1 | c1 | 20000

Car2 | c2 | 30000

…

Car600 | c600 | 15000

-

Total Price: 4500000

Page 2

BLUE

Name | Model | Price

Car1 | c1 | 10000

Car2 | c2 | 30000

…

Car100 | c100 | 40000

-

Total Price: 1000000

Page 3

GREEN

Name | Model | Price

Car1 | c1 | 25000

Car2 | c2 | 17000

…

Car100 | c100 | 40000

-

Total Price: 1200000

|||

Hi,

It is not possible to force a group on 1 page but it is possible to have only 1 total per group. You need to set the Color field in the expresion of the group. When you then look at the group footer, you will see that the total is done on group base.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Geert Verhoeven wrote:

It is not possible to force a group on 1 page but it is possible to have only 1 total per group. You need to set the Color field in the expresion of the group. When you then look at the group footer, you will see that the total is done on group base.

Sorry but I dont really understand what you have said or how to implement it

|||

Hi,

What I mean is that you can not force the contents of a group to be on 1 page. Although the problem you have that the total is displayed on each page can be avoided by using the footer of the group. When you set the total in the group of the footer, only at the end of the group a total will be displayed and not at the end of each page.

Hope this is more clear.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||im using a group a footer and it still displays the total at the end of each page, not at the end of the group

Monday, February 20, 2012

Nb Months between 2 dates (one in DB2 format)

How can I get the number of months (or years or days...)
between the current_timestamp
an a date in the DB2 format ?
DateDB2 Char(26) = 2000-12-30-12.55.30.123456
DateSQL = GetDate() or Current_Timestampdatediff (mm,convert (datetime , substring(datedb2,1,10)),DateSQL )|||nope,

I've tried that sort of conversion but it seems
that the CONVERT(DateTime,...) function inverts
the month and the day

Convert(DateTime,'2000-12-30') >>> doesn't work !!!
Convert(DateTime,'2000-30-12') >>> works !!!

Is '2000-30-12' a standard English-Format-Date ?|||Convert(DateTime,'2000-12-30') >>> doesn't work !!!
Then try this >>> select Convert(DateTime,'2000-12-30',120)|||Thank you very much Enigma !