Showing posts with label char. Show all posts
Showing posts with label char. Show all posts

Thursday, March 29, 2012

Count of same column appearing twice in the query

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS MagazineAdCount
FROM orderformlineitems
where product_variant_id = '4010436709979469536'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek MagazineAdCount
04/16/2007 04/23/2007 8
04/23/2007 04/30/2007 15
04/30/2007 05/07/2007 5


SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS BannerAdCount
FROM orderformlineitems
where product_variant_id = '7453910328410493551'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek BannerAdCount
04/16/2007 04/23/2007 15
04/23/2007 04/30/2007 21
04/30/2007 05/07/2007 22

I WANT THE BELOW OUTPUT THRU A SINGLE QUERY.....

StartWeek EndWeek MagazineAdCount BannerAdCount

04/16/2007 04/23/2007 8 15
04/23/2007 04/30/2007 15 21
04/30/2007 05/07/2007 5 22

Can anyone help please ?

Thanks

You can join these 2 query as single query..

Select Data1.Startweek,

Data1.EndWeek,

Data1.MagazineAdCount,

Data2.BannerAdCount

From

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS MagazineAdCount

FROM orderformlineitems

where product_variant_id = '4010436709979469536'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data1

Join

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS BannerAdCount

FROM orderformlineitems

where product_variant_id = '7453910328410493551'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data2 On Data1.StartWeek = Data2.StartWeek And Data1.EndWeek = Data2.EndWeek

|||Thank you !!|||

Use following query:

Code Snippet

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

sum ( case product_variant_id when '4010436709979469536' then 1 else 0 end) AS MagazineAdCount,

sum ( case product_variant_id when '7453910328410493551' then 1 else 0 end) AS BannerAdCount

FROM orderformlineitems

where product_variant_id in ( '4010436709979469536', '7453910328410493551')

group by datediff(day,'19000101',d_datecreated)/7

order by StartWeek ASC

|||Thanks !!

Tuesday, March 27, 2012

count number of lines in a clumn

The SP returns a column which contains
Error: 1212121
Error: 3434fsfa
Error: fdfdfd
Each above line has Char(10) + Char(13) attached so they will start a new line
Now I want to know the number of errors returns by using pattern "Error". Is
there any function I can use to do this?
thanks.In SQL you could do something like:
(LEN(columname) - LEN(REPLACE(columnname, 'Error' , '') ) / 5
I am not sure what the equivalent VB.Net syntax would look like to do it in
SRS.
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:B2FA56E5-AD24-4100-AD8B-B4CD7E771606@.microsoft.com...
> The SP returns a column which contains
> Error: 1212121
> Error: 3434fsfa
> Error: fdfdfd
> Each above line has Char(10) + Char(13) attached so they will start a new
> line
> Now I want to know the number of errors returns by using pattern "Error".
> Is
> there any function I can use to do this?
> thanks.

Sunday, March 25, 2012

Count CHAR(11) in a string

Hi
I need to cound the number of CHAR(11) charactors in a string.
I am currently attempting to use: len(string) - len(replace(string,
CHAR(11), ''))
But it seems to return far too many
Any help would be much appreciated
Thanks
B> But it seems to return far too many
Can you give an example?
SELECT LEN('foo') - REPLACE(LEN('foo'), CHAR(11), '')
returns 0...|||select dbo.OCCURS2 (string, CHAR(11))
CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
@.cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@.cSearchExpression) > 0
then ( datalength(@.cExpressionSearched)
- datalength(replace(cast(@.cExpressionSear
ched as
nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(@.cSearchExpression
as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
/ datalength(@.cSearchExpression)
else 0
end
end
GO
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wcon...e~2,54,33,27115
Please, download the file
http://www.universalthread.com/wcon...treme~2,2,27115
With the best regards,
Igor.
"Ben" wrote:

> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||hi
just try this:
its same as ur implementation:
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'
select len(@.ch) - len (replace(@.ch,char(11),''))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Ben" wrote:

> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||Chandra,
Please correct me if I am wrong but your solution may only work for one
occurrence of char(11).
I tried the following and still got 1 instead of 2.
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select len(@.ch) - len (replace(@.ch,char(11),''))
http://zulfiqar.typepad.com
BSEE, MCP
"Chandra" wrote:
> hi
> just try this:
> its same as ur implementation:
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'
> select len(@.ch) - len (replace(@.ch,char(11),''))
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Ben" wrote:
>|||Zulfiqar,
You've declared @.ch so that it can only hold 10 characters.
So the value you assigned to @.ch is truncated to
'ABC'+ CHAR(11) + 'DEFjh'
and this does in fact have only one char(11) value.
If you change the varchar(10) declaration to varchar(20) or larger,
you will get the result 2.
Steve Kass
Drew University
ZULFIQAR SYED wrote:

>Chandra,
>Please correct me if I am wrong but your solution may only work for one
>occurrence of char(11).
>I tried the following and still got 1 instead of 2.
>declare
>@.ch varchar(10)
>set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
>select len(@.ch) - len (replace(@.ch,char(11),''))
>
>|||Try:
declare
@.ch varchar(20)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select '*' + @.ch + '*', len(@.ch), len (replace(@.ch,char(11),''))
Since @.ch was varchar(10):
ABCEFjhi
123456790
The char(11) fell off of the end.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ZULFIQAR SYED" <DRSQLnospam2005@.hotmail.com> wrote in message
news:C0F8C537-9633-4880-831F-1497766F6870@.microsoft.com...
> Chandra,
> Please correct me if I am wrong but your solution may only work for one
> occurrence of char(11).
> I tried the following and still got 1 instead of 2.
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
> select len(@.ch) - len (replace(@.ch,char(11),''))
> --
> http://zulfiqar.typepad.com
> BSEE, MCP
>
> "Chandra" wrote:
>|||Sounds like another case for repeating the "string or binary data would be
truncated" error on invalid variable assignments. I wonder how often this
happens in the real world and people have no idea they're losing data.
"Steve Kass" <skass@.drew.edu> wrote in message
news:u$k1WGfqFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Zulfiqar,
> You've declared @.ch so that it can only hold 10 characters.
> So the value you assigned to @.ch is truncated to
> 'ABC'+ CHAR(11) + 'DEFjh'
> and this does in fact have only one char(11) value.
> If you change the varchar(10) declaration to varchar(20) or larger,
> you will get the result 2.
> Steve Kass
> Drew University
> ZULFIQAR SYED wrote:
>|||Thank you everyone
Igor2004's solution worked perfectly.
Thanks again
B
"Igor2004" <Igor2004@.discussions.microsoft.com> wrote in message
news:D82A4BEE-4EC9-44DF-BD41-1A0E3D061EC3@.microsoft.com...
> select dbo.OCCURS2 (string, CHAR(11))
> CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
> @.cExpressionSearched nvarchar(4000))
> returns smallint
> as
> begin
> return
> case
> when datalength(@.cSearchExpression) > 0
> then ( datalength(@.cExpressionSearched)
> - datalength(replace(cast(@.cExpressionSear
ched as
> nvarchar(4000)) COLLATE Latin1_General_BIN,
> cast(@.cSearchExpression
> as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
> / datalength(@.cSearchExpression)
> else 0
> end
> end
> GO
> For more information about string UDFs Transact-SQL please visit the
> http://www.universalthread.com/wcon...e~2,54,33,27115
> Please, download the file
> http://www.universalthread.com/wcon...treme~2,2,27115
> With the best regards,
> Igor.
>
> "Ben" wrote:
>

Count by month in summarized data

create table calls
(
customer char(10),
call_date datetime,
called_number char(10),
call_ammount char(10)
)
insert calls values('Customer1','1/1/2005','555-1234','1.00')
insert calls values('Customer2','1/2/2005','555-1231','0.50')
insert calls values('Customer3','1/1/2005','555-1232','5.00')
insert calls values('Customer4','2/1/2005','555-1233','1.00')
insert calls values('Customer1','2/2/2005','555-1234','1.00')
insert calls values('Customer1','2/3/2005','555-1235','1.00')
insert calls values('Customer2','2/4/2005','555-1236','3.00')
insert calls values('Customer4','2/1/2005','555-1237','2.00')
insert calls values('Customer5','3/1/2005','555-1238','2.00')
I need to get a list of customers calling for > $2.50 a month.
select MONTH(call_date) as Months
, customer
,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
,count( distinct customer ) as 'No. of cust.'
from calls with(nolock)
where call_date >= '1/1/2005'
group by MONTH(call_date), customer
--with rollup
HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
order by Months
, 'SUM Ammount $' desc
(without the counts) will get what I need .
Months Customer SUM $ No. of cust.
1 Customer3 5.00 1
2 Customer2 3.00 1
2 Customer4 3.00 1
3 Customer5 3.00 1
Turns out , I 'd also need to get monthly counts (ideally with $
totals) of these customers .
This is where I get stuck.
When I add a count to the query I have the result as shown above but
what I'd need is
Months SUM $ No. of cust.
1 5.00 1
2 6.00 2
3 3.00 1
or totals like with Rollup but Rollup doesn't work with distinct count.
BTW yes, this is simplified, and I know this is bad (no) design but
there is nothing I can do about that at the moment.
TIA
ZTry using your query as a derived table.
select
months,
sum([SUM $]) as c1,
sum([No. of cust.]) as c2
from
(
select MONTH(call_date) as Months
, customer
,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
,count( distinct customer ) as 'No. of cust.'
from calls with(nolock)
where call_date >= '1/1/2005'
group by MONTH(call_date), customer
HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
)
group by
months
AMB
"zoranlee@.hotmail.com" wrote:

> create table calls
> (
> customer char(10),
> call_date datetime,
> called_number char(10),
> call_ammount char(10)
> )
> insert calls values('Customer1','1/1/2005','555-1234','1.00')
> insert calls values('Customer2','1/2/2005','555-1231','0.50')
> insert calls values('Customer3','1/1/2005','555-1232','5.00')
> insert calls values('Customer4','2/1/2005','555-1233','1.00')
> insert calls values('Customer1','2/2/2005','555-1234','1.00')
> insert calls values('Customer1','2/3/2005','555-1235','1.00')
> insert calls values('Customer2','2/4/2005','555-1236','3.00')
> insert calls values('Customer4','2/1/2005','555-1237','2.00')
> insert calls values('Customer5','3/1/2005','555-1238','2.00')
> I need to get a list of customers calling for > $2.50 a month.
> select MONTH(call_date) as Months
> , customer
> ,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
> ,count( distinct customer ) as 'No. of cust.'
> from calls with(nolock)
> where call_date >= '1/1/2005'
> group by MONTH(call_date), customer
> --with rollup
> HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
> order by Months
> , 'SUM Ammount $' desc
> (without the counts) will get what I need .
> Months Customer SUM $ No. of cust.
> 1 Customer3 5.00 1
> 2 Customer2 3.00 1
> 2 Customer4 3.00 1
> 3 Customer5 3.00 1
> Turns out , I 'd also need to get monthly counts (ideally with $
> totals) of these customers .
> This is where I get stuck.
> When I add a count to the query I have the result as shown above but
> what I'd need is
> Months SUM $ No. of cust.
> 1 5.00 1
> 2 6.00 2
> 3 3.00 1
> or totals like with Rollup but Rollup doesn't work with distinct count.
> BTW yes, this is simplified, and I know this is bad (no) design but
> there is nothing I can do about that at the moment.
> TIA
> Z
>