gracie1972
asked on
Convert formula to max at a specific date by month
In my database I have a [Sell out week ending date] and [week]. Both dates are Saturday dates. I need to modify my query to only pull the most recent date.
For example, my data has dates:
Date Inventory ON Hand
2017-01-07 00:00:00.000 101
2017-01-14 00:00:00.000 99
2017-01-21 00:00:00.000 60
2017-01-28 00:00:00.000 200
2017-02-04 00:00:00.000 125
I am building a table that will pull in the max date by month for the [Sell out week ending date] and the total inventory on hand at that time.
If I were to run this next Monday I would only see:
2017-01-28 00:00:00.000 200
2017-02-04 00:00:00.000 125
I also want to convert to the date show:
Date Qty
01-2017 200
02-2017 125
Nothing I am doing is working, please help.
For example, my data has dates:
Date Inventory ON Hand
2017-01-07 00:00:00.000 101
2017-01-14 00:00:00.000 99
2017-01-21 00:00:00.000 60
2017-01-28 00:00:00.000 200
2017-02-04 00:00:00.000 125
I am building a table that will pull in the max date by month for the [Sell out week ending date] and the total inventory on hand at that time.
If I were to run this next Monday I would only see:
2017-01-28 00:00:00.000 200
2017-02-04 00:00:00.000 125
I also want to convert to the date show:
Date Qty
01-2017 200
02-2017 125
Nothing I am doing is working, please help.
ASKER
These are not necessarily max values, there are about 282,000 values in the data. I was the max date, total amount by that data so I can build current point in time totals at the end of each month by customer, does this make sense?
Hi,
can you please explain with an example?
can you please explain with an example?
ASKER
Here is an example output from my data:
Sell Out Week Ending ARTICLE Color Customer_Name Sell_Out_Week_Ending_Onhand
2017-01-21 00:00:00.000 01511 Black X 18172
2017-01-28 00:00:00.000 01511 Black X 18246
2017-01-14 00:00:00.000 01511 Black X 18335
2017-02-04 00:00:00.000 01511 Black X 18346
I only want to see:
Sell Out Week Ending ARTICLE Color Customer_Name Sell_Out_Week_Ending_Onhan
2017-01-28 00:00:00.000 01123 Black ABC 18246
2017-02-04 00:00:00.000 01123 Black ABC 18346
(Converted to mm-yyyy if possible)
Sell Out Week Ending ARTICLE Color Customer_Name Sell_Out_Week_Ending_Onhan
01-2017 01123 Black ABC 18246
02-2017 01123 Black ABC 18346
Does this help?
Hi,
Please try this -
Current data
Output
Hope it helps!
Please try this -
CREATE TABLE MaxMonth
(
[Sell Out Week Ending] DATETIME
,[Inventory ON Hand] INT
,ARTICLE VARCHAR(10)
,color VARCHAR(10)
,Customer_Name VARCHAR(1000)
,Sell_Out_Week_Ending_Onhand BIGINT
)
GO
INSERT INTO MaxMonth VALUES
('2017-01-07 00:00:00.000', 101 ,'01123', 'Black', 'ABC', 18246 ),
('2017-01-14 00:00:00.000', 99 ,'01123','Black', 'ABC', 18246 ),
('2017-01-21 00:00:00.000', 60 ,'01123','Black', 'ABC', 18246 ),
('2017-01-28 00:00:00.000', 200 ,'01123','Black', 'ABC', 18246 ),
('2017-02-04 00:00:00.000', 125 ,'01123','Black', 'ABC', 18246 )
GO
SELECT CONCAT(RIGHT(CONCAT('0',MONTH([Sell Out Week Ending])),2),'-',YEAR([Sell Out Week Ending])) [Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM MaxMonth
)k WHERE rnk = 1
Current data
/*------------------------
select * from MaxMonth
------------------------*/
Sell Out Week Ending Inventory ON Hand ARTICLE color Customer_Name Sell_Out_Week_Ending_Onhand
----------------------- ----------------- ---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
2017-01-07 00:00:00.000 101 01123 Black ABC 18246
2017-01-14 00:00:00.000 99 01123 Black ABC 18246
2017-01-21 00:00:00.000 60 01123 Black ABC 18246
2017-01-28 00:00:00.000 200 01123 Black ABC 18246
2017-02-04 00:00:00.000 125 01123 Black ABC 18246
(5 row(s) affected)
Output
/*------------------------
SELECT CONCAT(RIGHT(CONCAT('0',MONTH([Sell Out Week Ending])),2),'-',YEAR([Sell Out Week Ending])) [Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM MaxMonth
)k WHERE rnk = 1
------------------------*/
Sell Out Week Ending Inventory ON Hand ARTICLE color Customer_Name Sell_Out_Week_Ending_Onhand
-------------------- ----------------- ---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
01-2017 200 01123 Black ABC 18246
02-2017 125 01123 Black ABC 18246
(2 row(s) affected)
Hope it helps!
ASKER
I get this in SQL 2014
Msg 195, Level 15, State 10, Line 1
'CONCAT' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'k'.
Msg 195, Level 15, State 10, Line 1
'CONCAT' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'k'.
ASKER
Do I have to turn anything on for the CONCAT to work?
Hi,
It should work if you are using SQL 2012+, no problem , please use below -
Output
Hope it helps!
It should work if you are using SQL 2012+, no problem , please use below -
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4))
[Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM MaxMonth
)k WHERE rnk = 1
Output
/*------------------------
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4))
[Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM MaxMonth
)k WHERE rnk = 1
------------------------*/
Sell Out Week Ending Inventory ON Hand ARTICLE color Customer_Name Sell_Out_Week_Ending_Onhand
-------------------- ----------------- ---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
01-2017 200 01123 Black ABC 18246
02-2017 125 01123 Black ABC 18246
(2 row(s) affected)
Hope it helps!
ASKER
I do not have create permissions in SQL, how can I get around that?
You just have to run the below query , no creation required.
Replace yourtablename with my tablename and columns from yourtable.
Replace yourtablename with my tablename and columns from yourtable.
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4))
[Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM MaxMonth
)k WHERE rnk = 1
ASKER
Okay, the month and year worked but nothing else.
Here is my query as a start over from scratch.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
,MAX([WEEK])
,ARTICLE
,[SAH_Customer_Name]
,Sell_Out_Week_Ending_Onha nd
FROM [dbo].[VW_Tableau_Sell_Out ]
WHERE
[Sell Out Week Ending] > '12/31/2016'
AND [SAH_Customer_Name] = 'ABC' >>>FOR TESTING ONLY TO GET A SMALL POPULATION
AND [ARTICLE] = '0151123' >>>FOR TESTING ONLY TO GET A SMALL POPULATION
GROUP BY
[Sell Out Week Ending]
,[ARTICLE]
,[NRF_Color]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onh and]
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
RESULT:
Sell Out Week Ending (No column name) ARTICLE SAH_Customer_Name Sell_Out_Week_Ending_Onhan d
01-2017 2017-01-21 00:00:00.000 0151123 ABC 18,172
01-2017 2017-01-28 00:00:00.000 0151123 ABC 18,246
01-2017 2017-01-14 00:00:00.000 0151123 ABC 18,335
01-2017 2017-01-07 00:00:00.000 0151123 ABC 18,346
OPTIMAL RESULT:
Sell Out Week Ending [WEEK] ARTICLE SAH_Customer_Name Sell_Out_Week_Ending_Onhan d
01-2017 2017-01-28 00:00:00.000 015110 ABC 18,246
Here is my query as a start over from scratch.
--------------------------
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
,MAX([WEEK])
,ARTICLE
,[SAH_Customer_Name]
,Sell_Out_Week_Ending_Onha
FROM [dbo].[VW_Tableau_Sell_Out
WHERE
[Sell Out Week Ending] > '12/31/2016'
AND [SAH_Customer_Name] = 'ABC' >>>FOR TESTING ONLY TO GET A SMALL POPULATION
AND [ARTICLE] = '0151123' >>>FOR TESTING ONLY TO GET A SMALL POPULATION
GROUP BY
[Sell Out Week Ending]
,[ARTICLE]
,[NRF_Color]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onh
--------------------------
RESULT:
Sell Out Week Ending (No column name) ARTICLE SAH_Customer_Name Sell_Out_Week_Ending_Onhan
01-2017 2017-01-21 00:00:00.000 0151123 ABC 18,172
01-2017 2017-01-28 00:00:00.000 0151123 ABC 18,246
01-2017 2017-01-14 00:00:00.000 0151123 ABC 18,335
01-2017 2017-01-07 00:00:00.000 0151123 ABC 18,346
OPTIMAL RESULT:
Sell Out Week Ending [WEEK] ARTICLE SAH_Customer_Name Sell_Out_Week_Ending_Onhan
01-2017 2017-01-28 00:00:00.000 015110 ABC 18,246
Please try this-
SELECT
[Sell Out Week Ending]
,[WEEK]
,[ARTICLE]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onhand]
FROM
(
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
,([Sell Out Week Ending]) [WEEK]
,ARTICLE
,[SAH_Customer_Name]
,Sell_Out_Week_Ending_Onhand
,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM [dbo].[VW_Tableau_Sell_Out]
WHERE
[Sell Out Week Ending] > '12/31/2016'
AND [SAH_Customer_Name] = 'ABC'
AND [ARTICLE] = '0151123'
)m WHERE rnk = 1
ASKER
That did not work.
If I use the WHERE rnk = 1, then I only get 1 result.
There are over 476K records so far.
I only need to return the records for the MAX ([Sell Out Week Ending]) by Month.
Every Month will have a max data returned. Nothing is working.....
If I use the WHERE rnk = 1, then I only get 1 result.
There are over 476K records so far.
I only need to return the records for the MAX ([Sell Out Week Ending]) by Month.
Every Month will have a max data returned. Nothing is working.....
I have also used Rnk = 1 in all my samples and we are getting multiple rows. Each row per month. How you are saying you will get only 1 row.?
Please execute and the query and provide me the results?
Please execute and the query and provide me the results?
I got it - Your where clause is the problem, you are taking data > 12/31/2016 so effectively you will have only 1 month that is Jan 2017.
[Sell Out Week Ending] > '12/31/2016'
AND [SAH_Customer_Name] = 'ABC'
AND [ARTICLE] = '0151123'
Removed above and please try again.
[Sell Out Week Ending] > '12/31/2016'
AND [SAH_Customer_Name] = 'ABC'
AND [ARTICLE] = '0151123'
Removed above and please try again.
SELECT
[Sell Out Week Ending]
,[WEEK]
,[ARTICLE]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onhand]
FROM
(
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
,([Sell Out Week Ending]) [WEEK]
,ARTICLE
,[SAH_Customer_Name]
,Sell_Out_Week_Ending_Onhand
,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM [dbo].[VW_Tableau_Sell_Out]
)m WHERE rnk = 1
Hi,
Just created full proof of concept for you- My Solution is working perfectly fine.
My Solution
Output
NOTE - I am getting data all 5 months i have in my table
01-2017
02-2017
03-2017
04-2017
05-2017
Hope it helps!
Just created full proof of concept for you- My Solution is working perfectly fine.
--
CREATE TABLE [dbo].[VW_Tableau_Sell_Out]
(
[Sell Out Week Ending] DATETIME
,ARTICLE VARCHAR(10)
,[SAH_Customer_Name] VARCHAR(100)
,Sell_Out_Week_Ending_Onhand BIGINT
)
GO
INSERT INTO [dbo].[VW_Tableau_Sell_Out] VALUES
('2017-01-07 00:00:00.000', '01123', 'ABC', 18246 ),
('2017-01-14 00:00:00.000', '01123', 'ABC', 18246 ),
('2017-01-21 00:00:00.000', '01123', 'ABC', 18246 ),
('2017-01-28 00:00:00.000', '01123', 'ABC', 18246 ),
('2017-02-04 00:00:00.000', '01123', 'ABC', 18246 ),
('2017-03-03 00:00:00.000', '0151123', 'ABC', 18246 ),
('2017-03-03 00:00:00.000', '0151123', 'ABC', 18246 ),
('2017-03-03 00:00:00.000', '0151123', 'ABC', 18246 ),
('2017-03-03 00:00:00.000', '0151123', 'ABC', 18246 ),
('2017-03-03 00:00:00.000', '0151123', 'ABC', 18246 ),
('2017-04-04 00:00:00.000', '01511', 'ABC', 18246 ),
('2017-05-04 00:00:00.000', '01511', 'ABC', 18246 )
GO
--
My Solution
--
SELECT
[Sell Out Week Ending]
,[WEEK]
,[ARTICLE]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onhand]
FROM
(
SELECT
RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
+ '-'
+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
,([Sell Out Week Ending]) [WEEK]
,ARTICLE
,[SAH_Customer_Name]
,Sell_Out_Week_Ending_Onhand
,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
FROM [dbo].[VW_Tableau_Sell_Out]
)m WHERE rnk = 1
--
Output
/*------------------------
OUTPUT
------------------------*/
Sell Out Week Ending WEEK ARTICLE SAH_Customer_Name Sell_Out_Week_Ending_Onhand
-------------------- ----------------------- ---------- ---------------------------------------------------------------------------------------------------- ---------------------------
01-2017 2017-01-28 00:00:00.000 01123 ABC 18246
02-2017 2017-02-04 00:00:00.000 01123 ABC 18246
03-2017 2017-03-03 00:00:00.000 0151123 ABC 18246
04-2017 2017-04-04 00:00:00.000 01511 ABC 18246
05-2017 2017-05-04 00:00:00.000 01511 ABC 18246
(5 row(s) affected)
NOTE - I am getting data all 5 months i have in my table
01-2017
02-2017
03-2017
04-2017
05-2017
Hope it helps!
ASKER
The concept seems to work but see below, however, I am excluding hundreds of thousands of data for each customer.
Sell Out Week Ending WEEK ARTICLE SAH_Customer_Name Sell_Out_Week_Ending_Onhand
Nov-16 11/26/2016 B12345 ABC 0
Oct-16 10/29/2016 B12346 CDF 92
Jul-16 7/30/2016 B12347 EFG 2
Feb-16 2/27/2016 B12348 HIJ 1
Jun-16 6/25/2016 B12349 LOO 5
Mar-16 3/26/2016 B12350 MKJ 1
Apr-16 4/30/2016 B12351 AMA 1
Dec-16 12/31/2016 B12352 BST -3
May-16 5/28/2016 B12353 FOO 1
Sep-16 9/24/2016 B12354 AMK 12
Jan-17 1/28/2017 B12355 TAF 1
Aug-16 8/27/2016 B12356 AMP 8
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just created full and tested solution for you.
Open in new window
OUTPUT - 1
Open in new window
OUTPUT - 2
Open in new window
Hope it helps!