Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

NEED MSSQL VIEW TWEAKED TO SUMMARIZE DATA

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
datatechcorp
Asked:
datatechcorp
  • 19
  • 12
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
>> 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
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
datatechcorpAuthor Commented:
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
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
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
 
datatechcorpAuthor Commented:
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
 
PadawanDBACommented:
I probably won't have a reply until monday, but I'll take a look at it this weekend.
0
 
datatechcorpAuthor Commented:
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
 
datatechcorpAuthor Commented:
Hi PadawanDBA...

Anything on this yet?  Anything I may test with?  Please let me know...and, again, many thanks!...Mark
0
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
OK...fantastic!  No problem amigo :-)
0
 
datatechcorpAuthor Commented:
Hi PadawanDBA...you haven't forgotten about me, have you? :-)
0
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
Oh...MAZEL TOV!!!  That's a *wonderful* thing!  Congratulations Papa!!!  I hope that mama & baby Padawan are doing just fine. :-)
0
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
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
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
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
 
PadawanDBACommented:
So you don't want anything like the returns/sales_excl_returns data split out like that too?
0
 
datatechcorpAuthor Commented:
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
 
datatechcorpAuthor Commented:
Hi Padawan...anything?  Sorry...not trying to be pushy...but my boss is now getting on my case...oy yay yoy...
0
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
Hi Padawan...

I ran this against our database, and got no results.  Any thoughts?
0
 
PadawanDBACommented:
Against the sample database you sent me or live?  You should get 19 rows against the sample db.
0
 
datatechcorpAuthor Commented:
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
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
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
 
datatechcorpAuthor Commented:
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
 
PadawanDBACommented:
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
 
datatechcorpAuthor Commented:
*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
 
datatechcorpAuthor Commented:
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
 
PadawanDBACommented:
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 19
  • 12
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now