Solved

NEED MSSQL VIEW TWEAKED TO SUMMARIZE DATA

Posted on 2014-09-04
33
99 Views
Last Modified: 2014-10-31
HELLO ALL...

In our Inventory system, we have a "canned" View in MSSQL...the script of which follows:

CREATE VIEW [dbo].[AA_ITEM_MONTHLY_HIST]
AS
SELECT     dbo.IM_ITEM.ITEM_NO, MIN(dbo.VI_PS_PRIOR_MONTHS.MonthName) AS Month_and_Year, SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END)
                      AS [Returns], SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END) AS Sales_excl_returns, COALESCE (SUM(dbo.PS_TKT_HIST_LIN.EXT_PRC), 0) AS Sales,
                      COALESCE (SUM(dbo.PS_TKT_HIST_LIN.QTY_SOLD * dbo.PS_TKT_HIST_LIN.QTY_NUMER / dbo.PS_TKT_HIST_LIN.QTY_DENOM), 0) AS Qty_Sold,
                      SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END) AS Tickets, CAST(COALESCE (SUM(dbo.PS_TKT_HIST_LIN.EXT_PRC)
                      / NULLIF (SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END), 0), 0) AS decimal(15, 2)) AS Avg_Ticket,
                      CAST(COALESCE (SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) * - (1 * 100 / NULLIF (SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END),
                      0)), 0) AS decimal(15, 2)) AS Pct_Returns, dbo.VI_PS_PRIOR_MONTHS.PriorMonths, dbo.IM_ITEM.DESCR_UPR, dbo.IM_ITEM.ITEM_VEND_NO,
                      dbo.IM_ITEM.VEND_ITEM_NO, dbo.IM_ITEM.LST_COST, dbo.IM_ITEM.PRC_1
FROM         dbo.IM_ITEM CROSS JOIN
                      dbo.VI_PS_PRIOR_MONTHS LEFT OUTER JOIN
                      dbo.PS_TKT_HIST_LIN ON dbo.PS_TKT_HIST_LIN.BUS_DAT >= dbo.VI_PS_PRIOR_MONTHS.BOM AND
                      dbo.PS_TKT_HIST_LIN.BUS_DAT < dbo.VI_PS_PRIOR_MONTHS.BONM AND dbo.PS_TKT_HIST_LIN.ITEM_NO = dbo.IM_ITEM.ITEM_NO AND
                      dbo.PS_TKT_HIST_LIN.LIN_TYP IN ('R', 'S')
WHERE     (dbo.VI_PS_PRIOR_MONTHS.PriorMonths <= 26)
GROUP BY dbo.IM_ITEM.ITEM_NO, dbo.VI_PS_PRIOR_MONTHS.PriorMonths, dbo.IM_ITEM.DESCR_UPR, dbo.IM_ITEM.ITEM_VEND_NO, dbo.IM_ITEM.VEND_ITEM_NO,
                      dbo.IM_ITEM.LST_COST, dbo.IM_ITEM.PRC_1
GO

When run for (1) specific item in our Inventory, for a specific range of time (from April thru July), this produces the following result:

ITEM_NO      Month_and_Year      Returns      Sales_excl_returns      Sales      Qty_Sold      Tickets      Avg_Ticket      Pct_Returns      PriorMonths      DESCR_UPR      ITEM_VEND_NO      VEND_ITEM_NO      LST_COST      PRC_1
SHO-1545      July      2014      0.00      39.95      39.95      1.000000000000000      1      39.95      0.00      2      WOMENS ROCKET DOG BIGTOP      ROCKETDOG      1545-XX249      16.4200      39.9500
SHO-1545      June      2014      0.00      0.00      0.00      0.000000000000000      0      0.00      0.00      3      WOMENS ROCKET DOG BIGTOP      ROCKETDOG      1545-XX249      16.4200      39.9500
SHO-1545      May       2014      0.00      39.95      39.95      1.000000000000000      1      39.95      0.00      4      WOMENS ROCKET DOG BIGTOP      ROCKETDOG      1545-XX249      16.4200      39.9500
SHO-1545      April     2014      0.00      0.00      0.00      0.000000000000000      0      0.00      0.00      5      WOMENS ROCKET DOG BIGTOP      ROCKETDOG      1545-XX249      16.4200      39.9500

That's fine for what it does.  What we really *NEED*, however, is a ONE-LINER result, which contains *THIS* month's history data...AND...a field containing *THE PRIOR 3-MONTHS* data...again, all summarized, on one single line of output...and we need to *STORE* this as an MSSQL "VIEW"...but we're having issues with the Syntax...uugh.


Please help!  I'd truly appreciate it!...Thanks!...Mark
0
Comment
Question by:datatechcorp
  • 19
  • 12
  • 2
33 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> When run for (1) specific item in our Inventory, for a specific range of time (from April thru July), this produces the following result: <<

Can you provide the SELECT that does that?  Otherwise we're just totally guessing.  Keep in mind, we know NOTHING about your data.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Scott...

OK, sure...it's very straightforward, but here goes:

select * from AA_ITEM_MONTHLY_HIST
where ITEM_NO='SHO-1545'
and PriorMonths in ('2','3','4','5')

Thank You!...Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Hmm, months 2-5 representing July-April doesn't seem "straightforward" to me.  

Thus, I need to drop out and let someone else handle this.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Scott...

I just used that "select" query...as an example.  I need that data, again, summarized, into a "ONE-LINER"...and, thus, need some adjustment to the Syntax in the View.  Thoughts?  Thanks!...Mark
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
If you are looking for a one liner, we're probably looking at some CTEs and/or window functions.  Can you give the table structure and some (5-10 rows) sample data from each.  Along with that, could you include what you're looking for from the output (columns and an explanation of what should be in each)?  Thanks!
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

OK...I'll start gathering the data for you...will take me some time, as I have a project I'm working on for a deadline at the moment.  I'll try to have something presentable for you by the end of the day today...and THANK YOU!!!...Mark
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

OK, so, to make everything simpler, I have zipped and uploaded the database for you here.  It's not very large...31-mb zipped, only 350-mb in total.

You'll see what I'm talking about (I think/hope so anyway).  Please let me know if this makes sense.

Thanks!...Mark
DemoChloe846-SQL-Backup-For-Tests-2014-1
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
I probably won't have a reply until monday, but I'll take a look at it this weekend.
0
 

Author Comment

by:datatechcorp
Comment Utility
OK...no problem at all.  It's been a month since it's been posted...I can be patient :-)

Have a *great* weekend...and THANK YOU!
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...

Anything on this yet?  Anything I may test with?  Please let me know...and, again, many thanks!...Mark
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Mark - sorry, I haven't gotten to look at this yet.  Work has been hectic beyond belief.  I should be able to take a look tomorrow!
0
 

Author Comment

by:datatechcorp
Comment Utility
OK...fantastic!  No problem amigo :-)
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi PadawanDBA...you haven't forgotten about me, have you? :-)
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
I have not!  I have been out for the past week for the birth of my 4th son.  I am actually going to take a look at this on my lunch (which is right now).
0
 

Author Comment

by:datatechcorp
Comment Utility
Oh...MAZEL TOV!!!  That's a *wonderful* thing!  Congratulations Papa!!!  I hope that mama & baby Padawan are doing just fine. :-)
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Mom and baby are doing great!  And thank you for the congratulations.

On to your question:  So you are saying that you guys want a one liner of the data from the current month and then the previous 3 months.  What data is it that you want this information for?  What I am getting at is, are you looking for all columns of the sample output  for the current month and then again for 3month summary in a single line ?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:datatechcorp
Comment Utility
If I understand your question properly...yes.  To make this simpler, I need something like the following:

ITEM_NO  ALL_OTHER_ITEM_DATA  CURRENT_MONTH_$_DATA  CURRENT+LAST_3_MONTHS_$_DATA
ABC123     BLAH BLAH BLAH                 250.00                                      925.46

Does this make sense?  Please let me know...Thanks!
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
what are those amounts that you want?  are you wanting the sales data for the current month and then current + 3month prior ?  I guess my question is, what columns do you want current/current+3previous month data for?
0
 

Author Comment

by:datatechcorp
Comment Utility
Yes...correct...we need the Sales Data...on ONE row...TWO different columns.  The first column would be "Current Month Sales", and the second column would be "Current + 3-Months Prior Sales Data".  I believe we're on the same page.
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
So you don't want anything like the returns/sales_excl_returns data split out like that too?
0
 

Author Comment

by:datatechcorp
Comment Utility
No...not at this time anyway.  If we can get just a good sample output of that "One-Liner" concept nailed down, I believe we can use that as a roadmap for any future 'tweaks' and/or requirements...n'est pas?  :-)
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Padawan...anything?  Sorry...not trying to be pushy...but my boss is now getting on my case...oy yay yoy...
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Alright.  I finally had some time to try and finish this up.  Scott was probably correct in that this is a pretty involved question, but luckily i was feeling starved for playing with TSQL lately.  I would have liked to play around with windowing functions on this, but in the interest of getting you something functional (also, there is little/no attention paid to performance, hence i just ran with subqueries), try the following:

if object_id( 'tempdb..#tempPTHL' ) is not null
	drop table #tempPTHL;

select
	PTHL.BUS_DAT,
	PTHL.EXT_PRC,
	PTHL.QTY_SOLD,
	PTHL.QTY_NUMER,
	PTHL.QTY_DENOM,
	PTHL.TKT_NO,
	PTHL.ITEM_NO,
	VPPM.PriorMonths,
	II.DESCR_UPR,
	II.ITEM_VEND_NO,
	II.VEND_ITEM_NO,
	II.LST_COST,
	II.PRC_1,
	VPPM.MonthName
into
	#tempPTHL
from
	dbo.PS_TKT_HIST_LIN as PTHL
		inner join dbo.VI_PS_PRIOR_MONTHS as VPPM on PTHL.BUS_DAT >= VPPM.BOM and PTHL.BUS_DAT < VPPM.BONM
		inner join dbo.IM_ITEM as II on PTHL.ITEM_NO = II.ITEM_NO
where
	VPPM.PriorMonths <= 3 and
	PTHL.LIN_TYP in( 'R', 'S' );

-- select * from #tempPTHL;


SELECT     
	ITEM_NO, 
	MIN(MonthName) AS Month_and_Year, 
	SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) AS [Returns], 
	SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END) AS Sales_excl_returns, 
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths = 0
	) as Current_Sales,
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P3
		where
			P3.ITEM_NO = P1.ITEM_NO and
			P3.PriorMonths > 0
	) as Previous_Sales,
	COALESCE (SUM(QTY_SOLD * QTY_NUMER / QTY_DENOM), 0) AS Qty_Sold, 
    SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END) AS Tickets, 
	CAST(COALESCE (SUM(EXT_PRC) / NULLIF (SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END), 0), 0) AS decimal(15, 2)) AS Avg_Ticket,
	CAST(COALESCE (SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) * - (1 * 100 / NULLIF (SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END), 0)), 0) AS decimal(15, 2)) AS Pct_Returns, 
	PriorMonths, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
from
	#tempPTHL as P1
GROUP BY 
	ITEM_NO, 
	PriorMonths, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
order by
	ITEM_NO,
	Month_and_Year;

Open in new window


For the purposes of brevity, I filtered out items that have no sales.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Padawan...

I ran this against our database, and got no results.  Any thoughts?
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Against the sample database you sent me or live?  You should get 19 rows against the sample db.
0
 

Author Comment

by:datatechcorp
Comment Utility
Ah...ha!  Don't ask...it was "Pilot Error" on my part...yes, it runs.

But, I don't see, the "one-liner" in regards to output.  Was your prior code/query simply a snippet for testing?  Please let me know...and, as always, THANK YOU!...Mark
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Oops.  Forgot the last part of that:

if object_id( 'tempdb..#tempPTHL' ) is not null
	drop table #tempPTHL;

select
	PTHL.BUS_DAT,
	PTHL.EXT_PRC,
	PTHL.QTY_SOLD,
	PTHL.QTY_NUMER,
	PTHL.QTY_DENOM,
	PTHL.TKT_NO,
	PTHL.ITEM_NO,
	VPPM.PriorMonths,
	II.DESCR_UPR,
	II.ITEM_VEND_NO,
	II.VEND_ITEM_NO,
	II.LST_COST,
	II.PRC_1,
	VPPM.MonthName
into
	#tempPTHL
from
	dbo.PS_TKT_HIST_LIN as PTHL
		inner join dbo.VI_PS_PRIOR_MONTHS as VPPM on PTHL.BUS_DAT >= VPPM.BOM and PTHL.BUS_DAT < VPPM.BONM
		inner join dbo.IM_ITEM as II on PTHL.ITEM_NO = II.ITEM_NO
where
	VPPM.PriorMonths <= 3 and
	PTHL.LIN_TYP in( 'R', 'S' );

-- select * from #tempPTHL;


SELECT     
	ITEM_NO, 
	MIN(MonthName) AS Month_and_Year, 
	SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) AS [Returns], 
	SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END) AS Sales_excl_returns, 
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths = 0
	) as Current_Sales,
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			#tempPTHL as P3
		where
			P3.ITEM_NO = P1.ITEM_NO and
			P3.PriorMonths > 0
	) as Previous_Sales,
	COALESCE (SUM(QTY_SOLD * QTY_NUMER / QTY_DENOM), 0) AS Qty_Sold, 
    SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END) AS Tickets, 
	CAST(COALESCE (SUM(EXT_PRC) / NULLIF (SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END), 0), 0) AS decimal(15, 2)) AS Avg_Ticket,
	CAST(COALESCE (SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) * - (1 * 100 / NULLIF (SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END), 0)), 0) AS decimal(15, 2)) AS Pct_Returns, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
from
	#tempPTHL as P1
GROUP BY 
	ITEM_NO, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
order by
	ITEM_NO,
	Month_and_Year;

Open in new window

0
 

Author Comment

by:datatechcorp
Comment Utility
OK...this seems to be running now.  I have to audit the data, to ensure we're getting (reasonably correct) results.  I have a Demo to attend right now...I'll check back with you in a few hours, OK?  Thanks!...Mark
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Padawan...

OK...your Query runs great, and returns the correct data, when run from Management Studio.  That's great.  So, now...and likely the last part of this exercise...how do we get this to run...as an MSSQL "View"?  I only ask, because when I attempt to save this as a View...I get the following errors (uugh) fed back from MSSQL:

MSSQL-ERROR.JPG
0
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 500 total points
Comment Utility
So if you want it in a view, I believe you are going to be looking at a CTE:

with tempPTHL as
(
	select
		PTHL.BUS_DAT,
		PTHL.EXT_PRC,
		PTHL.QTY_SOLD,
		PTHL.QTY_NUMER,
		PTHL.QTY_DENOM,
		PTHL.TKT_NO,
		PTHL.ITEM_NO,
		VPPM.PriorMonths,
		II.DESCR_UPR,
		II.ITEM_VEND_NO,
		II.VEND_ITEM_NO,
		II.LST_COST,
		II.PRC_1,
		VPPM.MonthName
	from
		dbo.PS_TKT_HIST_LIN as PTHL
			inner join dbo.VI_PS_PRIOR_MONTHS as VPPM on PTHL.BUS_DAT >= VPPM.BOM and PTHL.BUS_DAT < VPPM.BONM
			inner join dbo.IM_ITEM as II on PTHL.ITEM_NO = II.ITEM_NO
	where
		VPPM.PriorMonths <= 3 and
		PTHL.LIN_TYP in( 'R', 'S' )
)


SELECT     
	ITEM_NO, 
	MIN(MonthName) AS Month_and_Year, 
	SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) AS [Returns], 
	SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END) AS Sales_excl_returns, 
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths = 0
	) as Current_Sales,
	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			tempPTHL as P3
		where
			P3.ITEM_NO = P1.ITEM_NO and
			P3.PriorMonths > 0
	) as Previous_Sales,
	COALESCE (SUM(QTY_SOLD * QTY_NUMER / QTY_DENOM), 0) AS Qty_Sold, 
    SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END) AS Tickets, 
	CAST(COALESCE (SUM(EXT_PRC) / NULLIF (SUM(CASE WHEN TKT_NO IS NOT NULL THEN 1 ELSE 0 END), 0), 0) AS decimal(15, 2)) AS Avg_Ticket,
	CAST(COALESCE (SUM(CASE WHEN EXT_PRC < 0 THEN EXT_PRC ELSE 0 END) * - (1 * 100 / NULLIF (SUM(CASE WHEN EXT_PRC > 0 THEN EXT_PRC ELSE 0 END), 0)), 0) AS decimal(15, 2)) AS Pct_Returns, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
from
	tempPTHL as P1
GROUP BY 
	ITEM_NO, 
	DESCR_UPR, 
	ITEM_VEND_NO, 
	VEND_ITEM_NO, 
	LST_COST, 
	PRC_1
order by
	ITEM_NO,
	Month_and_Year;

Open in new window

0
 

Author Comment

by:datatechcorp
Comment Utility
*BRILLIANT*!!!  Thank you...thank you...THANK YOU...SOOOO Much!

So, you have certainly been a Godsend...and I'm granting you all the points...heck, I'd grant you 5-million points if I could.

One last question...if I wanted to use this as a 'roadmap'...and, for example, in the "Current Sales" Column...I wanted that to represent, say, the "Current + Last Month's Combined" sales data...what adjustment should I be making to your Query...to accommodate this (all else remaining the same)?  Please let me know...and, again, I can't thank you enough amigo!...Mark
0
 

Author Closing Comment

by:datatechcorp
Comment Utility
PadawanDBA was *SOOOOOOO* helpful...and so patient with me in solving this issue!  What a Godsend this person is...superb follow-up and superb advice!!!
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
So the part you would modify would be the where clause of the current_sales subquery:

	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths = 0
	) as Current_Sales,

Open in new window


Would become:

	(
		select
			COALESCE (SUM(EXT_PRC), 0) AS Sales
		from
			tempPTHL as P2
		where
			P2.ITEM_NO = P1.ITEM_NO and
			P2.PriorMonths <= 1
	) as Current_Sales,

Open in new window


And happy I could be of help!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now