Adding texty and join fileds to make one long result

[Cpreston]

Hi

Sorry been on a few times today hopefully this is my last request for a while.

I have this query

select sum(TotalSellPrice) AS Sales,
Count(TotalSellPrice) AS Amount
FROM OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()),8))
AND OrderType = 1
AND Deleted = 0

Which gives me the sales total and count. What I would like to achieve if possible is for it to read and look like

Total Daily Sales (count) Salestotal

I have been trying things from google like 'This Weeks Sales' + CONVERT(Varchar(12) etc... but cannot get it working.

Any ideas please

 

[jbenson001]

You can try:

 'This Weeks Sales' + CAST(Count(TotalSellPrice), AS VARCHAR(10)) AS [Total Sales]

 

[Cpreston]

Sorry how and where would this git into my code?

Thanks

 

[Cpreston]

Hi

Ok got it just about except for spacing

I now have

This weeks Sales154 321431.17

I would like it to look like

This Weeks Sales (154) 1431.17

Any ideas how I get the brackets and also the spacing

Thanks

 

[Cpreston]

In fact I need both fields joining together as one if this is possible

 

[jbenson001]

'This Weeks Sales (' + CAST(Count(TotalSellPrice), AS VARCHAR(10)) +' ) + CAST(SUM(TotalSellPrice), AS VARCHAR(10))

 

[SQLBill]

One thing to remember when using +. It means two different things which depend on how it is used.

Add: when used with numbers.
SELECT 1 + 1
That will return: 2

Concatenate: when used with strings.
SELECT 'Hello' + ' World'
That will return: Hello World
(notice the space between the ' and World in the select).

However, when you try to concatenate and a number is used, SQL Server will think 'add'. So;
SELECT 'Number: ' + 1
That will cause SQL Server to try and add Number: plus 1. An error will be returned. To concatenate a number, you need to make it a string. Two ways to do this:
SELECT 'Number: ' + '1'
SELECT 'Number: ' + CAST(1 AS CHAR(1))

Either CAST and CONVERT can be used...the syntax is just different.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Cpreston]

Hi

Yes I have tried CAST and CONVERT but cannot get my syntax correct to get the result of having it all as one field. It is more down to my lack of experience of programming I am sure but I will keep trying.

If someone could point me in the right direction regarding how this is done with my code that would be great

Thanks

 

[Cpreston]

Sorry just saw Jbenson001 reply, I will try that with my code and get back with the result

As always thanks for the replys

 

[Cpreston]

Hi

I have my code like this now

from OrderHeader where DateTimeCreated >= DATEADD(day, datediff(day,0,getdate()),0) AND DateTimeCreated <= DATEADD(day, datediff(day,0,getdate()),0) + 1
AND OrderType = 1
AND Deleted = 0

but I am getting the following error

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ') + CAST(SUM(TotalSellPrice), AS VARCHAR(10)))
from OrderHeader where DateTimeCreated >= DATEADD(day, datediff(day,0,getdate()),0) AND DateTimeCreated <= DATEADD(day, datediff(day,0,getdate()),0) + 1
AND OrderType = 1
AND Deleted = 0


I have tried to change things on line 2 but I am making things worse I think, any ideas please

 

[jbenson001]

post the full SQL you have

 

[Cpreston]

As below

select 'This Weeks Sales (' + CAST(Count(TotalSellPrice), AS VARCHAR(10)) +' ) + CAST(SUM(TotalSellPrice), AS VARCHAR(10))
FROM OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()),8))
AND OrderType = 1
AND Deleted = 0




Thanks

 

[djj55]

No comma in the CAST

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Cpreston]

Hi

Not sure where you mean but I have taken out the commas in the Select and I now have this

select 'This Weeks Sales (' + CAST(Count(TotalSellPrice) AS VARCHAR(10)) +' ) + CAST(SUM(TotalSellPrice) AS VARCHAR(10))
FROM OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()),8))
AND OrderType = 1
AND Deleted = 0


but I am getting this
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' ) + CAST(SUM(TotalSellPrice) AS VARCHAR(10))
FROM OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()),8))
AND OrderType = 1
AND Deleted = 0
'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' ) + CAST(SUM(TotalSellPrice) AS VARCHAR(10))
FROM OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDAT'.

 

[Cpreston]

Hi

A little closer, I now have this

select 'This Weeks Sales ( '+ CAST(Count(TotalSellPrice) AS VARCHAR(10)) +' ) ('+ CAST(SUM(TotalSellPrice) AS VARCHAR(10))
FROM OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()),8))
AND OrderType = 1
AND Deleted = 0

Which gives me the result of

This Weeks Sales ( 794 ) (1696084.71

I cannot find out where to get rid of the ( by the (1696084.71

If I can get rid of that it will be perfect

 

[Cpreston]

Sorry about this just sorted it, final code below just in case someone else as issues

select 'This Weeks Sales ( '+ CAST(Count(TotalSellPrice) AS VARCHAR(10)) +' ) '+ CAST(SUM(TotalSellPrice) AS VARCHAR(10))
FROM OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()),8))
AND OrderType = 1
AND Deleted = 0


Thanks for the help

 

[jbenson001]

You need a quote after the ') .. so change this

select 'This Weeks Sales (' + CAST(Count(TotalSellPrice) AS VARCHAR(10)) [red][b]+' )[/b][/red] + CAST(SUM(TotalSellPrice) AS VARCHAR(10))

to this

select 'This Weeks Sales (' + CAST(Count(TotalSellPrice) AS VARCHAR(10)) +[red][b]' )'[/b][/red] + CAST(SUM(TotalSellPrice) AS VARCHAR(10))