Flag population based on Row_Value
-
Hi,
I want to populate a Sales_Flag :- from the table based on is value is present or not in 5 columns (If all 5 col are populate sales_flag = 5 , 4 col populate flag = 4 and so on )
table:-
CREATE TABLE [dbo].[Sales](
[ID] [float] NULL,
[Sales_Jan] [float] NULL,
[Sales_Feb] [float] NULL,
[Sales_Mar] [float] NULL,
[Sales_Apr] [float] NULL,
[Sales_May] [float] NULL,
[Sales_flag] [float] NULL
) ON [PRIMARY]
ID Sales_Jan Sales_Feb Sales_Mar Sales_Apr Sales_May Sales_flag
1 100 1
2 120 200300 3
3 100300 2
4 100 200100100100 5
5 100 100100100 4
Please suggest best possible way of doing the same.
Regards,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.” -
SQL_By_Chance (1/9/2012)
ID Sales_Jan Sales_Feb Sales_Mar Sales_Apr Sales_May Sales_flag1 100 1
2 120 200300 3
3 100300 2
4 100 200100100100 5
5 100 100100100 4
Can you script this as a series of INSERT statements please? It's unclear what you would use to represent a missing value, even though your columns are nullable. It will also give folks something to test against.
Wouldn't your columns be better typed as INT?
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Hi Smith,
Apologies for the same :-
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(1,100, 0,0,0 ,0 )
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(2,120,0,200,300,0)
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(3,0,100,0,300,0)
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(4,100,200,100,100,100)
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values(5,100,0,100,100,100)
--- Sales ID to populated is based on value <> 0 present in Sales columns. for All 5 months having sales <>0 : flagid = 5 , for 4 months sales <> 0 flagid = 4 , and so on.
Thanks for the same.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.” -
SQL_By_Chance (1/9/2012)
Hi Smith,Apologies for the same :-
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(1,100, 0,0,0 ,0 )
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(2,120,0,200,300,0)
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(3,0,100,0,300,0)
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(4,100,200,100,100,100)
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values(5,100,0,100,100,100)
--- Sales ID to populated is based on value <> 0 present in Sales columns. for All 5 months having sales <>0 : flagid = 5 , for 4 months sales <> 0 flagid = 4 , and so on.
Thanks for the same.
They can be int as well. But this is just a test data that I have created.
Regards
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.” -
These are probably the simplest options:
insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)
values
(1, 100, 0,0,0 ,0 ),
(2, 120,0, 200, 300,0 ),
(3, 0, 100,0 , 300, 0 ),
(4, 100, 200, 100, 100, 100 ),
(5, 100,0, 100, 100, 100 )
SELECT Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May,
Sales_flag1 =
ISNULL(Sales_Jan/NULLIF(Sales_Jan,0),0) +
ISNULL(Sales_Feb/NULLIF(Sales_Feb,0),0) +
ISNULL(Sales_Mar/NULLIF(Sales_Mar,0),0) +
ISNULL(Sales_Apr/NULLIF(Sales_Apr,0),0) +
ISNULL(Sales_May/NULLIF(Sales_May,0),0),
Sales_flag2 =
CASE WHEN Sales_Jan > 0 THEN 1 ELSE 0 END +
CASE WHEN Sales_Feb > 0 THEN 1 ELSE 0 END +
CASE WHEN Sales_Mar > 0 THEN 1 ELSE 0 END +
CASE WHEN Sales_Apr > 0 THEN 1 ELSE 0 END +
CASE WHEN Sales_May > 0 THEN 1 ELSE 0 END
FROM Sales
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
You could (probably should!) consider a more relational design for this table:
Table definition
CREATE TABLE dbo.Sales
(
SalesYear smallint NOT NULL,
SalesMonth tinyint NOT NULL,
SalesAmount money NOT NULL,
CONSTRAINT [CK dbo.Sales Valid Year & Month]
CHECK
(
SalesYear BETWEEN 2000 AND 2099
AND SalesMonth BETWEEN 1 AND 12
),
CONSTRAINT [PK dbo.Sales SalesYear, SalesMonth]
PRIMARY KEY CLUSTERED
(SalesYear, SalesMonth)
);
Sample data:
INSERT dbo.Sales
(SalesYear, SalesMonth, SalesAmount)
VALUES
(2001, 01, $100.00),
(2001, 02, $1.00),
(2002, 01, $120.00),
(2002, 02, $200.00),
(2002, 03, $300.00),
(2002, 04, $3.00),
(2003, 01, $100.00),
(2003, 02, $300.00),
(2003, 03, $2.00),
(2004, 01, $100.00),
(2004, 02, $200.00),
(2004, 03, $100.00),
(2004, 04, $100.00),
(2004, 05, $100.00),
(2004, 06, $5.00),
(2005, 01, $100.00),
(2005, 02, $100.00),
(2005, 03, $100.00),
(2005, 04, $100.00),
(2005, 05, $4.00);
Query:
SELECT
s.SalesYear,
[Jan] = SUM(CASE WHEN s.SalesMonth = 01 THEN s.SalesAmount ELSE $0.00 END),
[Feb] = SUM(CASE WHEN s.SalesMonth = 02 THEN s.SalesAmount ELSE $0.00 END),
[Mar] = SUM(CASE WHEN s.SalesMonth = 03 THEN s.SalesAmount ELSE $0.00 END),
[Apr] = SUM(CASE WHEN s.SalesMonth = 04 THEN s.SalesAmount ELSE $0.00 END),
[May] = SUM(CASE WHEN s.SalesMonth = 05 THEN s.SalesAmount ELSE $0.00 END),
[Jun] = SUM(CASE WHEN s.SalesMonth = 06 THEN s.SalesAmount ELSE $0.00 END),
[Jul] = SUM(CASE WHEN s.SalesMonth = 07 THEN s.SalesAmount ELSE $0.00 END),
[Aug] = SUM(CASE WHEN s.SalesMonth = 08 THEN s.SalesAmount ELSE $0.00 END),
[Sep] = SUM(CASE WHEN s.SalesMonth = 09 THEN s.SalesAmount ELSE $0.00 END),
[Oct] = SUM(CASE WHEN s.SalesMonth = 10 THEN s.SalesAmount ELSE $0.00 END),
[Nov] = SUM(CASE WHEN s.SalesMonth = 11 THEN s.SalesAmount ELSE $0.00 END),
[Dec] = SUM(CASE WHEN s.SalesMonth = 12 THEN s.SalesAmount ELSE $0.00 END),
[Count] = COUNT_BIG(*)
FROM dbo.Sales AS s
GROUP BY
s.SalesYear
ORDER BY
s.SalesYear;Output:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Thanks Paul for the knowledge, I would definitely keep these tips in mind.
Thanks Smith for your solution.
Do you guys think its possible in If...else as well ?
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.” -
SQL_By_Chance (1/9/2012)
Do you guys think its possible in If...else as well ?Sorry, I don't understand the question...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Aaron Aardvark (1/9/2012)
SQL_By_Chance (1/9/2012)
Do you guys think its possible in If...else as well ?Sorry, I don't understand the question...
Who's Smith?
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Sorry Chris the thank was for you 😛
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply