June 8, 2011 at 8:08 am
Hi
I'm trying to create a query to populate a new created column with values based on another column.
For example, I have 3 tables which have the following information
Table 1 Lookup
BU A B C D E
24 4 6 0 8 10
Table 2 Lim_ABC_Table
Item Current_Code BU
45464 A 24
46463 B 23
36363 C 22
Table 3 Lim_Inventory
ITEM INVENTORY
45464 110
46463 140
So what i've done so far is joined the 3 tables together
SELECT L.ITEM AS Item
,L.CURRENTABC AS Current_ABC
FROM dbo.Lim_ABC_Table L
INNER JOIN
dbo.Lim_Inventory I
ON L.ITEM = I.ITEM
INNER JOIN
dbo.LookupT LU
ON LU.BU = L.BU
These are the results i'm getting which i'm happy with
Item current_ABC
45464 A
46463 B
Now what i want is to include in the statement above is some type of IF statement or anything which is simple to look at the values in current_ABC such as 'A' then create a new column and associate 4 or it could be 'B' and in the new column put next to it 6
Can anyone help me do this???
Thanks in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 8, 2011 at 10:02 am
I think what you want to do is put your query into a CTE and then when selecting from the CTE add a column to the SELECT list that makes use of a CASE expression.
You did a nice job of posting your query and expected results. If you would like some assistance with the query could you kindly post DDL for your tables and DML to add your test data? If you're not sure what I mean please refer to this article:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 11:52 am
opc.three many thanks for your reply and comments much apprenciated
I took your advice and used the Case expression as i found it more easier to code
SELECT L.ITEM AS ITEM,
L.BU AS BU,
SUBFAMILYDESC AS Sub_Family_Desc,
SUBFAMILYCODE AS Sub_Family_Code,
CURRENTABC1 AS Current_ABC,
CASE WHEN L.CURRENTABC IN('A') THEN '4'
WHEN L.CURRENTABC IN('B') THEN '6'
WHEN L.CURRENTABC IN('C') THEN '0'
WHEN L.CURRENTABC IN('D') THEN '8'
WHEN L.CURRENTABC IN('E') THEN '10'
END AS CURRENT_ABC_WEEKS,
LASTMONTHSIX AS Last_months_six_months_forecasts,
STANDARDCOST AS Standard_Cost,
--(LASTMONTHSIX/24)* CURRENT_ABC_WEEKS AS Current_Stock,
STANDARDCOST
FROM dbo.Lim_ABC_Table L
INNER JOIN
dbo.Lim_Inventory I
ON L.ITEM = I.ITEM
INNER JOIN
dbo.LookupT LU
ON LU.BU = L.BU
Results
Item = 45464
BU = A
Sub_Family = Primary
Current_ABC = A5
Current_ABC_weeks = 6
Last_months_six_months_forecasts = 1000
Standard_Cost = 260.10
I'm happy with the current results, however now i want perform a calculation using the case expression column called CURRENT_ABC_WEEKS. By create a new column '(LASTMONTHSIX/24)* CURRENT_ABC_WEEKS AS Current_Stock.
Now i'm getting the following error message
Msg 207, Level 16, State 1, Line 14
Invalid column name 'CURRENT_ABC_WEEKS'.
Can you shad any light on what i need to do to rectify this problem.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 8, 2011 at 11:58 am
Jnrstevej (6/8/2011)
I'm happy with the current results, however now i want perform a calculation using the case expression column called CURRENT_ABC_WEEKS. By create a new column '(LASTMONTHSIX/24)* CURRENT_ABC_WEEKS AS Current_Stock.Now i'm getting the following error message
Msg 207, Level 16, State 1, Line 14
Invalid column name 'CURRENT_ABC_WEEKS'.
Can you shad any light on what i need to do to rectify this problem.
You cannot reference a derived column in another derived column in the the same SELECT list. That's where a CTE will come in handy.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 12:40 pm
Can you show me how you would code this as i'm not to sure how
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 8, 2011 at 12:48 pm
Sure, could you kindly post DDL for your tables and DML to add your test data? If you're not sure what I mean please refer to this article:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 1:46 pm
Hi opc.three
Sorry for the delay
Table 1
-- Inserting data in Lim_ABC_Table
CREATE TABLE [dbo].[Lim_ABC_Table](
[ITEM] [varchar](50) NULL,
[BU] [int] NULL,
[SUBFAMILYDESC] [varchar](50) NULL,
[SUBFAMILYCODE] [varchar](50) NULL,
[CURRENTABC1] [varchar](50) NULL,
[LASTMONTHSIX] [int] NULL,
[STANDARDCOST] [decimal](18, 2) NULL,
[CURRENTABC] [varchar](10) NULL,
[CURRENTSIXMONTHSFORECAST] [int] NULL,
[DSSEGMENTATION] [varchar](1) NULL,
[DSSUGGESTABC] [varchar](1) NULL,
[CURRENTDSSUGGEST] [int] NULL
) ON [PRIMARY]
-- Inserting data into Limerick_country
INSERT INTO dbo.Lim_ABC_Table
VALUES('RAW',024,'CR PRIMARY FEMUR',19,'A',6000,203.35,'A',6263,'R','A','1044')
INSERT INTO dbo.Lim_ABC_Table
VALUES ('5515F402',024,'PS PRIMARY FEMUR','C7','B',600,260.10,'B',600,'R','B',150)
INSERT INTO dbo.Lim_ABC_Table
VALUES ('5514F402',024,'PS BEADED FEMUR','C5','D',100,260.10,'D',100,'R','B',20)
INSERT INTO dbo.Lim_ABC_Table
VALUES ('5514F403',023,'TRIATHLON BEADED P','H5','A',3700,170.00,'A',3784,'S','A',120)
GO
--Creating Lim_Inventory Table
CREATE TABLE [dbo].[Lim_Inventory](
[ITEM] [varchar](50) NULL,
[InventoryForFG123] [int] NULL
) ON [PRIMARY]
GO
-- Inserting data into Lim_Inventory
INSERT INTO dbo.Lim_Inventory
VALUES ('5515F401',110)
INSERT INTO dbo.Lim_Inventory
VALUES ('5514F402',140)
INSERT INTO dbo.Lim_Inventory
VALUES ('5514F403',141)
-- Creating table lookup
CREATE TABLE [dbo].[LookupT](
[BU] [int] NULL,
[A] [decimal](18, 2) NULL,
[decimal](18, 2) NULL,
[C] [decimal](18, 2) NULL,
[D] [decimal](18, 2) NULL,
[E] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--Inserting data into Lookup
INSERT INTO LookupT
VALUES(24,4.00,6.00,0.00,8.00,10.00)
INSERT INTO LookupT
VALUES(23,4.00,6.00,0.00,8.00,10.00)
Main script
WITH StockSafety_CTE(CURRENT_ABC_WEEKS)
AS
(
SELECT L.ITEM AS ITEM,
L.BU AS BU,
SUBFAMILYDESC AS Sub_Family_Desc,
SUBFAMILYCODE AS Sub_Family_Code,
CURRENTABC1 AS Current_ABC,
CASE WHEN L.CURRENTABC IN('A') THEN '4'
WHEN L.CURRENTABC IN('B') THEN '6'
WHEN L.CURRENTABC IN('C') THEN '0'
WHEN L.CURRENTABC IN('D') THEN '8'
WHEN L.CURRENTABC IN('E') THEN '10'
END AS CURRENT_ABC_WEEKS,
LASTMONTHSIX AS Last_months_six_months_forecasts,
STANDARDCOST AS Standard_Cost,
LU.A AS A,
LU.B AS B,
LU.C AS C,
LU.D AS D,
LU.E AS E,
CURRENTABC AS Current_ABC1,
CURRENTSIXMONTHSFORECAST AS Current_six_months_Forecast,
DSSEGMENTATION AS DS_Segmentation_RS,
DSSUGGESTABC AS DS_Suggest_ABC,
CURRENTDSSUGGEST AS Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST
FROM dbo.Lim_ABC_Table L
INNER JOIN
dbo.Lim_Inventory I
ON L.ITEM = I.ITEM
INNER JOIN
dbo.LookupT LU
ON LU.BU = L.BU
)
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 8, 2011 at 1:52 pm
Awesome, thanks. This code demonstrates how you can create the Current_Stock column.
WITH StockSafety_CTE(ITEM, BU, Sub_Family_Desc, Sub_Family_Code, Current_ABC, CURRENT_ABC_WEEKS, Last_months_six_months_forecasts, Standard_Cost, A, B, C, D, E, Current_ABC1, Current_six_months_Forecast, DS_Segmentation_RS, DS_Suggest_ABC, Current_DS_Suggestion_SS_Unit, LASTMONTHSIX, STANDARDCOST)
AS (
SELECT L.ITEM AS ITEM,
L.BU AS BU,
SUBFAMILYDESC AS Sub_Family_Desc,
SUBFAMILYCODE AS Sub_Family_Code,
CURRENTABC1 AS Current_ABC,
CASE WHEN L.CURRENTABC IN ('A') THEN '4'
WHEN L.CURRENTABC IN ('B') THEN '6'
WHEN L.CURRENTABC IN ('C') THEN '0'
WHEN L.CURRENTABC IN ('D') THEN '8'
WHEN L.CURRENTABC IN ('E') THEN '10'
END AS CURRENT_ABC_WEEKS,
LASTMONTHSIX AS Last_months_six_months_forecasts,
STANDARDCOST AS Standard_Cost,
LU.A AS A,
LU.B AS B,
LU.C AS C,
LU.D AS D,
LU.E AS E,
CURRENTABC AS Current_ABC1,
CURRENTSIXMONTHSFORECAST AS Current_six_months_Forecast,
DSSEGMENTATION AS DS_Segmentation_RS,
DSSUGGESTABC AS DS_Suggest_ABC,
CURRENTDSSUGGEST AS Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST
FROM dbo.Lim_ABC_Table L
INNER JOIN dbo.Lim_Inventory I ON L.ITEM = I.ITEM
INNER JOIN dbo.LookupT LU ON LU.BU = L.BU
)
SELECT ITEM,
BU,
Sub_Family_Desc,
Sub_Family_Code,
Current_ABC,
CURRENT_ABC_WEEKS,
Last_months_six_months_forecasts,
Standard_Cost,
A,
B,
C,
D,
E,
Current_ABC1,
Current_six_months_Forecast,
DS_Segmentation_RS,
DS_Suggest_ABC,
Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST,
(LASTMONTHSIX / 24) * CURRENT_ABC_WEEKS AS Current_Stock
FROM StockSafety_CTE ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 2:11 pm
Oh wow that was quick thanks alot for writing that for me and the Forum Etiquette
I want to use CURRENT_ABC_WEEKS to perform similar calculation roughly 4 more.
Would i need to create another CTE 4 more times in order to achieve this?
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 8, 2011 at 2:24 pm
Right i understand it perfectly now I just need to incorporate the caluculation in the StockSafety_CTE section of the code not add another CTE brilliant. Thanks alot for the help I learnt alot today
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 8, 2011 at 2:29 pm
Jnrstevej (6/8/2011)
Right i understand it perfectly now I just need to incorporate the caluculation in the StockSafety_CTE section of the code not add another CTE brilliant. Thanks alot for the help I learnt alot today
You're very welcome, I am happy to assist 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 3:42 pm
Just a quick one I want to Current_Stock by Standard_Cost for example, Standard_Cost * current_stock i was sure this could of been done within StockSafety_CTE. I'm assuming now i need to create another CTE as i'm getting the error message
Msg 207, Level 16, State 1, Line 41
Invalid column name 'Current_Stock'.
WITH StockSafety_CTE(ITEM, BU, Sub_Family_Desc, Sub_Family_Code, Current_ABC, CURRENT_ABC_WEEKS, Last_months_six_months_forecasts, Standard_Cost, A, B, C, D, E, Current_ABC1, Current_six_months_Forecast, DS_Segmentation_RS, DS_Suggest_ABC, Current_DS_Suggestion_SS_Unit, LASTMONTHSIX, STANDARDCOST)
AS (
SELECT L.ITEM AS ITEM,
L.BU AS BU,
SUBFAMILYDESC AS Sub_Family_Desc,
SUBFAMILYCODE AS Sub_Family_Code,
CURRENTABC1 AS Current_ABC,
CASE WHEN L.CURRENTABC IN ('A') THEN '4'
WHEN L.CURRENTABC IN ('B') THEN '6'
WHEN L.CURRENTABC IN ('C') THEN '0'
WHEN L.CURRENTABC IN ('D') THEN '8'
WHEN L.CURRENTABC IN ('E') THEN '10'
END AS CURRENT_ABC_WEEKS,
LASTMONTHSIX AS Last_months_six_months_forecasts,
STANDARDCOST AS Standard_Cost,
LU.A AS A,
LU.B AS B,
LU.C AS C,
LU.D AS D,
LU.E AS E,
CURRENTABC AS Current_ABC1,
CURRENTSIXMONTHSFORECAST AS Current_six_months_Forecast,
DSSEGMENTATION AS DS_Segmentation_RS,
DSSUGGESTABC AS DS_Suggest_ABC,
CURRENTDSSUGGEST AS Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST
FROM dbo.Lim_ABC_Table L
INNER JOIN dbo.Lim_Inventory I ON L.ITEM = I.ITEM
INNER JOIN dbo.LookupT LU ON LU.BU = L.BU
)
SELECT ITEM,
BU,
Sub_Family_Desc,
Sub_Family_Code,
Current_ABC,
CURRENT_ABC_WEEKS,
Last_months_six_months_forecasts,
Standard_Cost,
(LASTMONTHSIX / 24) * CURRENT_ABC_WEEKS AS Current_Stock,
A,
B,
C,
D,
E,
Current_ABC1,
Current_six_months_Forecast,
DS_Segmentation_RS,
DS_Suggest_ABC,
Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST,
STANDARDCOST * CURRENT_ABC_WEEKS
FROM StockSafety_CTE ;
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 8, 2011 at 4:01 pm
You are on the right track. You can cascade CTEs as well. See of something like this works for you:
WITH StockSafety_CTE(ITEM, BU, Sub_Family_Desc, Sub_Family_Code, Current_ABC, CURRENT_ABC_WEEKS, Last_months_six_months_forecasts, Standard_Cost, A, B, C, D, E, Current_ABC1, Current_six_months_Forecast, DS_Segmentation_RS, DS_Suggest_ABC, Current_DS_Suggestion_SS_Unit, LASTMONTHSIX, STANDARDCOST)
AS (
SELECT L.ITEM AS ITEM,
L.BU AS BU,
SUBFAMILYDESC AS Sub_Family_Desc,
SUBFAMILYCODE AS Sub_Family_Code,
CURRENTABC1 AS Current_ABC,
CASE WHEN L.CURRENTABC IN ('A') THEN '4'
WHEN L.CURRENTABC IN ('B') THEN '6'
WHEN L.CURRENTABC IN ('C') THEN '0'
WHEN L.CURRENTABC IN ('D') THEN '8'
WHEN L.CURRENTABC IN ('E') THEN '10'
END AS CURRENT_ABC_WEEKS,
LASTMONTHSIX AS Last_months_six_months_forecasts,
STANDARDCOST AS Standard_Cost,
LU.A AS A,
LU.B AS B,
LU.C AS C,
LU.D AS D,
LU.E AS E,
CURRENTABC AS Current_ABC1,
CURRENTSIXMONTHSFORECAST AS Current_six_months_Forecast,
DSSEGMENTATION AS DS_Segmentation_RS,
DSSUGGESTABC AS DS_Suggest_ABC,
CURRENTDSSUGGEST AS Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST
FROM dbo.Lim_ABC_Table L
INNER JOIN dbo.Lim_Inventory I ON L.ITEM = I.ITEM
INNER JOIN dbo.LookupT LU ON LU.BU = L.BU
),
StockSafety_CTE2(ITEM, BU, Sub_Family_Desc, Sub_Family_Code, Current_ABC, CURRENT_ABC_WEEKS, Last_months_six_months_forecasts, Standard_Cost, A, B, C, D, E, Current_ABC1, Current_six_months_Forecast, DS_Segmentation_RS, DS_Suggest_ABC, Current_DS_Suggestion_SS_Unit, LASTMONTHSIX, STANDARDCOST, Current_Stock)
AS (
SELECT ITEM,
BU,
Sub_Family_Desc,
Sub_Family_Code,
Current_ABC,
CURRENT_ABC_WEEKS,
Last_months_six_months_forecasts,
Standard_Cost,
A,
B,
C,
D,
E,
Current_ABC1,
Current_six_months_Forecast,
DS_Segmentation_RS,
DS_Suggest_ABC,
Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST,
(LASTMONTHSIX / 24) * CURRENT_ABC_WEEKS
FROM StockSafety_CTE
)
SELECT ITEM,
BU,
Sub_Family_Desc,
Sub_Family_Code,
Current_ABC,
CURRENT_ABC_WEEKS,
Last_months_six_months_forecasts,
Standard_Cost,
Current_Stock,
A,
B,
C,
D,
E,
Current_ABC1,
Current_six_months_Forecast,
DS_Segmentation_RS,
DS_Suggest_ABC,
Current_DS_Suggestion_SS_Unit,
LASTMONTHSIX,
STANDARDCOST,
STANDARDCOST * CURRENT_ABC_WEEKS,
Standard_Cost * current_stock AS [Standard_Cost * current_stock]
FROM StockSafety_CTE2 ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 8:54 am
Thanks for the post it worked perfectly
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 10, 2011 at 9:25 am
Jnrstevej (6/10/2011)
Thanks for the post it worked perfectly
Double happiness 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply