Deals, revenu and margin by month, help

1

select misc7,
monthno,
sum(deal) deal,
sum(CASE WHEN ISNUMERIC([misc9])=1
THEN CONVERT(MONEY
,[misc9]) ELSE 0 END)/count(customer_no)
AS [Margin]
,
sum(CASE WHEN ISNUMERIC([misc8])=1
THEN CONVERT(MONEY
,[misc8]) ELSE 0 END)/count(customer_no)
AS [Revenu]

from (

select c.customer_no as thecount, Margin, Revenu, mic7, misc6, 'Revenu' as catagory
(case when misc7 = 'January' then 1
when misc7 = 'February' then 2
when misc7 = 'March' then 3
when misc7 = 'April' then 4
when misc7 = 'May' then 5
when misc7 = 'June' then 6
when misc7 = 'July' then 7
when misc7 = 'August' then 8
when misc7 = 'September' then 9
when misc7 = 'October' then 10
when misc7 = 'November' then 11
when misc7 = 'December' then 12
else 0 end) as monthno
from tblcustomers as c
inner join tblselections s
on c.customer_no = s.customer_no
inner join tblworkinprogress w
on s.customer_no = w.customer_no
where
s.Misc7<>'' AND
s.Misc6<>'' AND
w.date21 <> ''
and s.Misc5 = 'Deal'

union

SELECT
datename("mm",Approved_Date) as misc7, '' as misc6,
case datepart ("yyyy",Approved_date)
when ${curryear} then 'CurrentYear'

END as Category,
datepart("mm",Approved_Date) as MonthNo

FROM tblcustomers
WHERE Approved = 1 and Purchased = 1 and cancelled = 0 and Inactive = 0

) as prequery
piviot
(count(thecount)
for catagory in ([Deal],[Revenu],[Margin],[CurrentYear])
) as PostQuery
group by misc7, monthno

2

would you like to utter a few words to explain what you would like help with

3

sorry, I'm new to all of this SQL stuff. I'm trying to make it report the number of deals, the Revenu, and the margin for each month.

all I get is an exception error and "("

4

put a comma after category

 'Revenu' as catagory,
5

I'm hacking it together from one another guy made and he didn't have that comma.
when i add it it says incorrect syntax near the word as

6

show us what you added and where you added it

8

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

9

:point_up: :point_up:

10

i get that
image
when i put it in idashboards but i get "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" in ZQL

11

:point_up_2:

12

select misc7,
monthno,
sum(deal) deal,
sum(CASE WHEN ISNUMERIC([misc9])=1
THEN CONVERT(MONEY
,[misc9]) ELSE 0 END)/count(customer_no)
AS [Margin]
,
sum(CASE WHEN ISNUMERIC([misc8])=1
THEN CONVERT(MONEY
,[misc8]) ELSE 0 END)/count(customer_no)
AS [Revenu]

from (

select c.customer_no as thecount, Margin, Revenu, mic7, misc6, 'Revenu', as catagory
(case when misc7 = 'January' then 1
when misc7 = 'February' then 2
when misc7 = 'March' then 3
when misc7 = 'April' then 4
when misc7 = 'May' then 5
when misc7 = 'June' then 6
when misc7 = 'July' then 7
when misc7 = 'August' then 8
when misc7 = 'September' then 9
when misc7 = 'October' then 10
when misc7 = 'November' then 11
when misc7 = 'December' then 12
else 0 end) as monthno
from tblcustomers as c
inner join tblselections s
on c.customer_no = s.customer_no
inner join tblworkinprogress w
on s.customer_no = w.customer_no
where
s.Misc7<>'' AND
s.Misc6<>'' AND
w.date21 <> ''
and s.Misc5 = 'Deal'

union

SELECT
datename("mm",Approved_Date) as misc7, '' as misc6,
case datepart ("yyyy",Approved_date)
when ${curryear} then 'CurrentYear'

END as Category,
datepart("mm",Approved_Date) as MonthNo

FROM tblcustomers
WHERE Approved = 1 and Purchased = 1 and cancelled = 0 and Inactive = 0

) as prequery
piviot
(count(thecount)
for catagory in ([Deal],[Revenu],[Margin],[CurrentYear])
) as PostQuery
group by misc7, monthno

13

:point_up:
not

'Revenu', as catagory
14

yah thanks, I got alot furher now, still not all the way but im almost there

15

That is not valid T-SQL (Microsoft SQL Server) syntax. I have no idea what 'idashboards' is - so no idea if the above is some macro or something being passed as-is to SQL Server.

Another problem is that you are returning different number of columns in the UNION - that cannot be done, you need the same number of columns in both queries in a UNION query. It looks like you are missing the customer_no, Margin and Revenu in the second query - and 'CurrentYear' as the category if the date is in the current year but NULL if it is not.

There are no values in the category column with the value of [Deal] or [Margin] so that cannot be pivoted.

I am not even sure why you have 2 separate queries that are being unioned - as both query the table tblcustomers.

If you want additional help, it would help us if you provided sample data and expected results. Sample data in the form of create statements for the tables and insert statements to add that data to the sample tables.

16

I have changed alot of it and got it alot closer to working.
select misc7,
sum(deal) deal,

sum(CASE WHEN ISNUMERIC([misc9])=1
THEN CONVERT(MONEY
,[misc9]) ELSE 0 END)/count(customer_no)
AS Margin,
 
sum(CASE WHEN ISNUMERIC([misc8])=1
THEN CONVERT(MONEY
,[misc8]) ELSE 0 END)/count(customer_no)
AS Revenu, mic7, misc6, 'Deal' as catagory

from (

select c.customer_no as thecount, 
sum(CASE WHEN ISNUMERIC([misc9])=1
THEN CONVERT(MONEY
,[misc9]) ELSE 0 END)/count(customer_no)
AS Margin, sum(CASE WHEN ISNUMERIC([misc8])=1
THEN CONVERT(MONEY
,[misc8]) ELSE 0 END)/count(customer_no)
AS Revenu, mic7, misc6, 'Deal' as catagory,
(case when misc7 = 'January' then 1 
when misc7 = 'February' then 2
when misc7 = 'March' then 3
when misc7 = 'April' then 4
when misc7 = 'May' then 5
when misc7 = 'June' then 6
when misc7 = 'July' then 7
when misc7 = 'August' then 8
when misc7 = 'September' then 9
when misc7 = 'October' then 10
when misc7 = 'November' then 11
when misc7 = 'December' then 12
else 0 end) as monthno
from tblcustomers as c
inner join tblselections s
on c.customer_no = s.customer_no
where
s.Misc7<>'' AND
s.Misc6<>'' AND
s.Misc5 = 'Deal'

and Approved = 1 and Purchased = 1 and cancelled = 0 and Inactive = 0 


) as prequery

piviot

(count(thecount)
for catagory in ([Deal])
) as PostQuery
group by misc7, monthno

Now it gives me an error

Incorrect syntax near 'piviot'.

If i run just the insdie select stament then i get

Column 'tblcustomers.Customer_No' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

17

fixed the typo in pivot now i just get the

Column 'tblcustomers.Customer_No' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.