Case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Case

Author

Topic

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2012-02-06 : 03:42:44

Hi,

How do I do a case based on the format of the record:

Problem:
If the record has a decimal (example 2.5) I want to allocate a specific number or else when it is a full number (example 2) I want to allocate a different numebr. Reason being I am tryning to calculate the ratio of FULL ORDERS vs NOT FULL ORDERS.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-06 : 03:49:42

[code]
case when a_number <> floor(a_number) then <specific number> else <different number> end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2012-02-06 : 03:59:48

Thanks,

But Basically it is not about the size but if it is a FULL NR like 10 cases or 1.5 cases.So was it full order or not.?

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2012-02-06 : 04:07:16

A_Nr Nr Allocated according to you case,
0.5 1
0.5 1
0.5 1
2.5 1
2.5 1
1 2
1 2
12 1
12 1
12 1
12 1
2 2

1 should be decimals
2 should be full numbers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-06 : 04:10:49

quote:
Originally posted by Rheinhardt

A_Nr Nr Allocated according to you case,
0.5 1
0.5 1
0.5 1
2.5 1
2.5 1
1 2
1 2
12 1
12 1
12 1
12 1
2 2

1 should be decimals
2 should be full numbers

Why 12 is not full number ?

KH
[spoiler]Time is always against us[/spoiler]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2012-02-06 : 04:13:35

That is a good question, but unfortunately that is what the case returns?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-06 : 04:19:46

can you post the table schema and the your query ?

KH
[spoiler]Time is always against us[/spoiler]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2012-02-06 : 04:35:46

Table one: [ctbl_MD_Pack_Config]
PackCode varchar(30)
PackDescription varchar(30)
BrandCode varchar(30)
BrandName varchar(30)
ProdWeight float
[Units per pack] float
[packs per sec unit] float
[sec pack weight] float

Table Two: [SecondarySalesCustomGrouping_July2015]
SATELLITE_CODE int
Pricing_Customer_Code_n int
date_sid datetime
VOLUME numeric(38, 4)

select SATELLITE_CODE,pricing_customer_code_n,PackCode,BrandCode,prodweight,[Units per pack],[Type Sec unit],[packs per sec unit],date_sid,sum(volume_2011) VOL,sum(volume_2011)/ProdWeight UNITS,(sum(volume_2011)/ProdWeight)/([packs per sec unit])FULLCASEorNOT,case when (sum(volume_2011)/ProdWeight)/([packs per sec unit]) <> floor((sum(volume_2011)/ProdWeight)/([packs per sec unit])) then 1 else 2 end
from [SecondarySalesCustomGrouping_July2015] s
inner join dbo.ctbl_MD_PRODUCT_MASTER p
on p.PRODUCT_CODE_TEXT=s.product_code_text
inner join [ctbl_MD_Pack_Config] c
on PackCode=PACKSIZE_CODE and BrandCode=BRAND_CODE
group by SATELLITE_CODE,pricing_customer_code_n,PackDescription,BrandName,[Units per pack],[Type Sec unit],[packs per sec unit],date_sid,ProdWeight--PACKSIZE_CODE,PACKSIZE_DESCRIPTION,BRAND_CODE,BRAND_DESCRIPTION
order by 1,2,4

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2012-02-06 : 04:41:41

THANKS it is working now...it was the FLOAT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources