I need som help - 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

General SQL Server Forums

New to SQL Server Programming

I need som help

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-12 : 12:54:10

Hi guys,

I am working on the query to retrive the sales data for last couple months and I think the best way to do it is using CASE WHEN statement however it did not give me the correct data but 0 numbers.

This is what I'm doing right now.

DECLARE @current_month INT;
DECLARE @previous_month INT;
DECLARE @previous_2_months INT;

SET @current_month = CAST(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112)AS INT);
SET @previous_month = CAST(CONVERT(CHAR(8), DATEADD(MM,-1, GETDATE()),112)AS INT);
SET @previous_2_months = CAST(CONVERT(CHAR(8), DATEADD(MM,-2, GETDATE()),112)AS INT);

SELECT
*

,CASE
WHEN time_period BETWEEN @current_month AND @previous_month
THEN sales_data
ELSE 0
END AS sales_current_month

FROM
(
SELECT
[branch_id]+' - '+[branch_description] as branch
,COALESCE (p21_view_oe_hdr.class_2id,'STORE') as 'invoice_status'
,CAST(p21_sales_history_report_view.year_and_period AS INT) AS time_period
,CAST(ROUND(SUM([detail_price]),2)as decimal (38,2)) as sales_data
,CAST(ROUND(SUM([detail_cogs]),2)as decimal (38,2)) as cogs
,CAST(ROUND((SUM([detail_price])-SUM([detail_cogs])),2) as decimal (38,2)) as 'gm$'
,CAST(ROUND((((SUM([detail_price])-SUM([detail_cogs]))/(SUM([detail_price])))*100),2)as decimal (38,2)) as 'gm%'

FROM [P21].[dbo].[p21_sales_history_report_view]
LEFT OUTER JOIN hsc_p21_view_inv_mast ON [p21_sales_history_report_view].inv_mast_uid = hsc_p21_view_inv_mast.inv_mast_uid
LEFT OUTER JOIN p21_view_oe_hdr ON [p21_sales_history_report_view].order_no = p21_view_oe_hdr.order_no
WHERE
p21_sales_history_report_view.period = 6
AND p21_sales_history_report_view.year_for_period = 2012
AND [p21_sales_history_report_view].parent_oe_line_uid = 0
AND (invoice_adjustment_type='C' AND [p21_sales_history_report_view].source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)
--AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )
GROUP BY
[P21].[dbo].[p21_sales_history_report_view].branch_id
,[P21].[dbo].[p21_sales_history_report_view].branch_description
,p21_view_oe_hdr.class_2id
,p21_sales_history_report_view.year_and_period

As the result, the sales_current_month only shows 0 number instead of matching exactly with sales_data.

Do you guys know why the time and date I set up as the beginning not working with this data?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 12:59:18

The Date comparison using the BETWEEN operator requires the smaller value come first.

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-12 : 13:03:48

Yes and I did switch the smaller value comes first but the result still the same. THank you for the response. The logic seems fine to me but I don't know why it always give the 0 value as the result of ELSE in the CASE WHEN statement.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 13:22:41

What are the values of the time_period column? Are they all the first of the month or are they down to the day level? If they are at the month level (assuming something like 20120701) then you'll need to adjust your dates to the first of the month before you convert them to INTs.

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-12 : 13:40:07

IT is at the month level, do you know how to adjust to the first of the month before converting it.

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-12 : 13:42:51

The value of the time_period column is for example 201207. I don't think it has date on it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 14:02:04

Just change the convert from CHAR(8) to CHAR(6):

SELECT CAST(CONVERT(CHAR(6), DATEADD(MM, 0, getdate()),112)AS INT);

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-12 : 14:08:55

This is what I changed

DECLARE @current_month INT;
DECLARE @previous_month INT;
DECLARE @previous_2_months INT;
DECLARE @previous_3_months INT;

SET @current_month = CAST(CONVERT(CHAR(6), DATEADD(MM, 0, getdate()),112)AS INT);
SET @previous_month = CAST(CONVERT(CHAR(6), DATEADD(MM,-1, GETDATE()),112)AS INT);
SET @previous_2_months = CAST(CONVERT(CHAR(6), DATEADD(MM,-2, GETDATE()),112)AS INT);
SET @previous_3_months = CAST(CONVERT(CHAR(6), DATEADD(MM,-3, GETDATE()),112)AS INT);

SELECT
*
,CASE
WHEN time_period BETWEEN @previous_month AND @current_month
THEN sales
ELSE 0
END AS sales_current_month
,CASE
WHEN time_period BETWEEN @previous_month AND @current_month
THEN cogs
ELSE 0
END AS cogs_current_month
,CASE
WHEN time_period BETWEEN @previous_month AND @current_month
THEN gm$
ELSE 0
END AS gn$_current_month
,CASE
WHEN time_period BETWEEN @previous_month AND @current_month
THEN [gm%]
ELSE 0
END AS 'gm%_current_month'

, CASE
WHEN time_period BETWEEN @previous_2_months AND @previous_month
THEN sales
ELSE 0
END AS sales_last_month

,CASE
WHEN time_period BETWEEN @previous_2_months AND @previous_month
THEN cogs
ELSE 0
END AS cogs_last_month
,CASE
WHEN time_period BETWEEN @previous_2_months AND @previous_month
THEN gm$
ELSE 0
END AS gm$_last_month
,CASE
WHEN time_period BETWEEN @previous_2_months AND @previous_month
THEN [gm%]
ELSE 0
END AS 'gm%_last_month'
, CASE
WHEN time_period BETWEEN @previous_3_months AND @previous_2_months
THEN sales
ELSE 0
END AS sales_last_2_month
, CASE
WHEN time_period BETWEEN @previous_3_months AND @previous_2_months
THEN cogs
ELSE 0
END AS cogs_last_2_month
, CASE
WHEN time_period BETWEEN @previous_3_months AND @previous_2_months
THEN gm$
ELSE 0
END AS gm$_last_2_month
, CASE
WHEN time_period BETWEEN @previous_3_months AND @previous_2_months
THEN [gm%]
ELSE 0
END AS 'gm%_last_2_month'

FROM
(
SELECT
[branch_id]+' - '+[branch_description] as branch
,COALESCE (p21_view_oe_hdr.class_2id,'STORE') as 'invoice_status'
,CAST(CONVERT(CHAR (6),p21_sales_history_report_view.year_and_period) AS INT) AS time_period
,CAST(ROUND(SUM([detail_price]),2)as decimal (38,2)) as sales
,CAST(ROUND(SUM([detail_cogs]),2)as decimal (38,2)) as cogs
,CAST(ROUND((SUM([detail_price])-SUM([detail_cogs])),2) as decimal (38,2)) as 'gm$'
,CAST(ROUND((((SUM([detail_price])-SUM([detail_cogs]))/(SUM([detail_price])))*100),2)as decimal (38,2)) as 'gm%'

FROM [P21].[dbo].[p21_sales_history_report_view]
LEFT OUTER JOIN hsc_p21_view_inv_mast ON [p21_sales_history_report_view].inv_mast_uid = hsc_p21_view_inv_mast.inv_mast_uid
LEFT OUTER JOIN p21_view_oe_hdr ON [p21_sales_history_report_view].order_no = p21_view_oe_hdr.order_no
WHERE

p21_sales_history_report_view.year_and_period=201206
AND [p21_sales_history_report_view].parent_oe_line_uid = 0
AND (invoice_adjustment_type='C' AND [p21_sales_history_report_view].source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)
--AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )
GROUP BY
[P21].[dbo].[p21_sales_history_report_view].branch_id
,[P21].[dbo].[p21_sales_history_report_view].branch_description
,p21_view_oe_hdr.class_2id
,p21_sales_history_report_view.year_and_period

The current_month sales is correct however, the last_month sales is exactly like the current_month_sales and the sale column, the last_2_month sales is 0.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 14:43:40

Not sure, since we have no sampel data to work with. Could this be an issue?


p21_sales_history_report_view.year_and_period=201206

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources