May 5, 2016 at 7:22 am
I have 2 tables that I need to group by the cust_code and part number but also have it output as the total for each month for the past 6 months. So in other words, I need it to look like this:
cust_codepart_no6mth5mth5mth3mth2mth1mth
ASEFG123456584623
PRFEDW7845268794523
Here is my code so far though:
select b.cust_code, a.part_no,
COUNT(Case when b.date_entered > DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0) and b.date_entered < DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) -5, 0)) End) as 6mth
from orders_all b
join ord_list a
on a.order_no = b.order_no and a.order_ext = b.ext
where b.date_entered > GETDATE() - 181
group by b.cust_code
Any help would be appreciated
May 5, 2016 at 7:39 am
You almost had it, you just needed to complete the CASE statement. Also make the range start inclusive.
SELECT b.cust_code,
a.part_no,
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 5, 0)
THEN 1 END) AS [6mth]
FROM orders_all b
JOIN ord_list a ON a.order_no = b.order_no and a.order_ext = b.ext
WHERE b.date_entered > GETDATE() - 181
GROUP BY b.cust_code;
I'd advice you against using irrelevant letters as table alias. Otherwise, on larger queries, you'll end up going back and forth to review what does a, b or c mean instead of identifying it by significant aliases.
May 5, 2016 at 7:58 am
Thank you very much for your help.
I will have to look into that. I only do it to reference the tables using because many of the tables used have the same names and we have to use the certain one.
May 5, 2016 at 8:35 am
jonathanm 4432 (5/5/2016)
Thank you very much for your help.I will have to look into that. I only do it to reference the tables using because many of the tables used have the same names and we have to use the certain one.
I would also change this:
WHERE b.date_entered > GETDATE() - 181
to this:
WHERE b.date_entered > DATEADD(day, -181, GETDATE())
The former clause won't work with the newer date and date/time data types. Get used to using the date functions.
May 5, 2016 at 11:50 am
Ok, so I tried to then do an average but getting an error. Would you happen to know what is missing
select a.[Sales Rep], a.CustomerName as [Customer Name], a.AcctNumber, a.StartDateI as [Start Date], a.PartI as [Part Number], a.Desci as [Desc], d.in_stock as available, b.Curr_Cost as CustSellPrice,
a.QtyExpI as ExpMonthQty, (b.Curr_Cost * a.QtyExpI) as ExpMonthSell,
c.[6mth] as [6MthQty],
c.[5mth] as [5MthQty],
c.[4mth] as [4MthQty],
c.[3mth] as [3MthQty],
c.[2mth] as [2MthQty],
c.[1mth] as [LastMthQty],
IF((((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell)<0,((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell,0),
b.Supp_Name as Vendor
from ##WesnerReport a
join tho_Vendor_Price_export b
on a.PartI = b.Stock and a.Desci = b.Description
join ##6MonthReport c
on c.part_no = a.PartI and c.cust_code = a.AcctNumber
join inventory_in_stock_vw d
on d.part_no = a.PartI
order by a.PartI, a.StartDateI asc
May 5, 2016 at 12:44 pm
jonathanm 4432 (5/5/2016)
Ok, so I tried to then do an average but getting an error. Would you happen to know what is missingselect a.[Sales Rep], a.CustomerName as [Customer Name], a.AcctNumber, a.StartDateI as [Start Date], a.PartI as [Part Number], a.Desci as [Desc], d.in_stock as available, b.Curr_Cost as CustSellPrice,
a.QtyExpI as ExpMonthQty, (b.Curr_Cost * a.QtyExpI) as ExpMonthSell,
c.[6mth] as [6MthQty],
c.[5mth] as [5MthQty],
c.[4mth] as [4MthQty],
c.[3mth] as [3MthQty],
c.[2mth] as [2MthQty],
c.[1mth] as [LastMthQty],
IF((((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell)<0,((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell,0),
b.Supp_Name as Vendor
from ##WesnerReport a
join tho_Vendor_Price_export b
on a.PartI = b.Stock and a.Desci = b.Description
join ##6MonthReport c
on c.part_no = a.PartI and c.cust_code = a.AcctNumber
join inventory_in_stock_vw d
on d.part_no = a.PartI
order by a.PartI, a.StartDateI asc
And what is the error? We can't see what you see.
Edit: Oh, you can't use IF in your select statement.
May 5, 2016 at 12:48 pm
Not getting an error. I am trying to get the if equation to work correctly so that if the average of the sum of the 3 fields is less then 0, then populate with the result, otherwise populate 0.
May 5, 2016 at 12:49 pm
jonathanm 4432 (5/5/2016)
Not getting an error. I am trying to get the if equation to work correctly so that if the average of the sum of the 3 fields is less then 0, then populate with the result, otherwise populate 0.
According to this:
jonathanm 4432 (5/5/2016)
Ok, so I tried to then do an average but getting an error. Would you happen to know what is missing
you got an error.
May 5, 2016 at 12:57 pm
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'IF'.
Msg 195, Level 15, State 10, Line 9
'AVERAGE' is not a recognized built-in function name.
May 5, 2016 at 12:58 pm
For better assistance, please post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (not production data) as INSERT INTO statements for the table(s) involved, expected results based on the sample data, and what you have done so far to solve the problem.
Please remember we are volunteers on this site offering our help on our time. The more you help us to help you, the better answers you will receive in return.
May 5, 2016 at 12:59 pm
jonathanm 4432 (5/5/2016)
Msg 156, Level 15, State 1, Line 9Incorrect syntax near the keyword 'IF'.
Msg 195, Level 15, State 10, Line 9
'AVERAGE' is not a recognized built-in function name.
Again, you can't use the IF in your select statement. The average function is AVG.
May 5, 2016 at 1:07 pm
Maybe this would be better. Here is the table
select b.cust_code, a.part_no,
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 5, 0)
THEN 1 END) AS [6mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 5, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 4, 0)
THEN 1 END) AS [5mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 4, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 0)
THEN 1 END) AS [4mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0)
THEN 1 END) AS [3mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
THEN 1 END) AS [2mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
THEN 1 END) AS [1mth],
**New column here that is: if((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell)<0,((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell,0)
**
from orders_all b
join ord_list a
on a.order_no = b.order_no and a.order_ext = b.ext
WHERE b.date_entered > DATEADD(day, -185, GETDATE())
group by b.cust_code, a.part_no
May 5, 2016 at 1:08 pm
jonathanm 4432 (5/5/2016)
Maybe this would be better. Here is the tableselect b.cust_code, a.part_no,
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 5, 0)
THEN 1 END) AS [6mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 5, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 4, 0)
THEN 1 END) AS [5mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 4, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 0)
THEN 1 END) AS [4mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0)
THEN 1 END) AS [3mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
THEN 1 END) AS [2mth],
COUNT(CASE WHEN b.date_entered >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
AND b.date_entered < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
THEN 1 END) AS [1mth],
**New column here that is: if((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell)<0,((AVERAGE(c.[3mth],c.[2mth],c.[1mth]))* CustSellPrice)- ExpMonthSell,0)
**
from orders_all b
join ord_list a
on a.order_no = b.order_no and a.order_ext = b.ext
WHERE b.date_entered > DATEADD(day, -185, GETDATE())
group by b.cust_code, a.part_no
Code snippets aren't a table be.
May 5, 2016 at 1:37 pm
Error 1: IF statements can't be part of a SELECT statement, that's what CASE is for. IIF can be used only on 2012+ versions.
Error 2: AVERAGE function (which is AVG) only accepts one parameter and aggregates rows, not columns.
Error 3: You can't reference column alias in the column list. You could use column names from a CTE or you could generate a new formula that includes the 3 months and divide the value by 3.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply