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 2005 Forums
 Transact-SQL (2005)
 help modifying query (output params vs XML output

Author  Topic 

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-17 : 09:21:40
Hi,

I have the following query below. I'm looking to populate all the OUTPUT parameters somehow and efficiently as possible. I'm bringing this data back via OUTPUT parameters to avoid extra unneeded columns.

Does anyone have any suggestions on the best way to do this ?

Any help much appreciated as always!

thanks again,
mike123

CREATE PROCEDURE [dbo].[select_linkSwap_stats_myAccount_MonthlySummary]
(
@partnerID int,
@monthsAgo int,
--0 denotes current month so far, 1 = previous full month, 2 = 2 months previous full month etc
@inbound_total int OUTPUT,
@inbound_mean int OUTPUT,
@inbound_min int OUTPUT,
@inbound_max int OUTPUT,
@outbound_total int OUTPUT,
@outbound_mean int OUTPUT,
@outbound_min int OUTPUT,
@outbound_max int OUTPUT
)
AS SET NOCOUNT ON


--somehow populate all the OUTPUT params here
--NEED HELP HERE

--select @inbound_total =.....etc


SELECT coalesce(Inbound.clickdate, Outbound.clickdate) as clickdate,
coalesce(Inbound.inbound_clicks_total, 0) as inbound_clicks_total,
coalesce(Outbound.outbound_clicks_total, 0) as outbound_clicks_total

from --Inbound
(SELECT dateadd(day, datediff(day, 0, clickDate), 0) as clickDate,
COUNT(clickID) AS inbound_clicks_total
FROM tblLinkSwap_ClickLog_InBound
WHERE clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0)
AND clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)
GROUP BY dateadd(day, datediff(day, 0, clickDate), 0)) Inbound

full outer join --Outbound

(SELECT dateadd(day, datediff(day, 0, tblLinkSwap_ClickLog_OutBound.clickDate), 0) as clickDate,
COUNT(tblLinkSwap_ClickLog_OutBound.clickID) AS outbound_clicks_total
FROM tblLinkSwap_ClickLog_OutBound
JOIN [tblLinkSwap_OutBoundLinks] on tblLinkSwap_OutBoundLinks.linkID = tblLinkSwap_ClickLog_OutBound.linkID
WHERE tblLinkSwap_OutBoundLinks.partnerID = 1
AND tblLinkSwap_ClickLog_OutBound.clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0)
AND tblLinkSwap_ClickLog_OutBound.clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)
GROUP BY dateadd(day, datediff(day, 0, tblLinkSwap_ClickLog_OutBound.clickDate), 0)) Outbound
on Inbound.clickdate = Outbound.clickdate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-17 : 09:23:43
will you always be returning a single row of data.else it would be better to make this to a table valued function.
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-17 : 09:27:47
Hi Visakh16,

I will be returning 1 row for each day of the month, and most often a full month. So 30 rows on average lets say. I'm open to any different ways on formatting the data returned that you suggest. One reason I like this way is that I am populating a custom class in the front end (asp.net) which I cache, so having the data returned together is slightly beneficial.

I'm not sure what you mean by a table valued function ?

Appreciate your input!

Thanks again,
mik123
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-17 : 09:34:11
http://www.sqlteam.com/article/user-defined-functions
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-17 : 09:35:51
the use of udfs will improve usuability also as it cant be used like a table.
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-17 : 09:58:37
Hi Visakh16,

I just finished reading the article you suggested. Good read and I appreciated the article. I understand UDF are a very valuable tool in the proper situatuion, but I don't fully understand how you are suggesting I use it in this situation ?

Anything further much appreciated..

cheers,
mike123
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-17 : 11:35:32
quote:
Originally posted by mike123

Hi Visakh16,

I just finished reading the article you suggested. Good read and I appreciated the article. I understand UDF are a very valuable tool in the proper situatuion, but I don't fully understand how you are suggesting I use it in this situation ?

Anything further much appreciated..

cheers,
mike123

cant you just use your query to populate the table valued function (third example in article). Then you can use it just like a table in your joins and where condition.
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-17 : 11:42:35
ah ok, I think I see where your going with this.... so my final resultset would have an extra 8 columns ?

The reason I structured like this was to avoid bloating the cache on the webserver, I want as small of a footprint as possible to cache.. If I have a resultset with an extra 8 columns (30 rows) , its not as slick as having just 8 INT values stored in cache ..

make sense ? What do you think about bringing them back as OUTPUT params in this existing query ?

Please let me know if I misunderstood anything

Thanks!
Mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-17 : 12:01:20
I still suggest an XML approach, since the consumer of the data want XML input.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-17 : 14:52:33
hey peso,

would you recommmend *against* this output technique for any reason?

There are some complications with the XML way, and I think its going to be difficult to integrate with an XML query for this particular instance because the XML page I am generating is 95% rows of XML that are just configuration settings.. so this query only represents about 5% of the total data on the XML ... to make it more complicated there is a specific way it has to be formatted, and the parts from this query are in 2 different sections .... =[


thanks again,
mike123
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-12-10 : 11:59:56
Hey Guys,

Still trying to solve this one, wondering if anyone can help me populate the OUTPUT variables in this query...

alternatively, as Peso suggested XML might be a good idea...

I shyed away from the XML approach previously as I thought it would be a big headache because of all my XML bloat....

I've uploaded a sample of what my XML looks like here. I've included both versions for easy of use, not sure which is better for you guys.

I think it should be easy to pick out what areas of the XML need to be dynamic. Look for <string> , <number>

as well as


Outgoing clicks we sent you
Mean
Min
Max

Incoming clicks you sent us
Mean
Min
Max


<text color=""000000"" alpha=""60"" font=""arial"" rotation=""0"" bold=""1"" size=""14"" x=""23"" y=""10"" width=""300"" h_align=""left"">" & graphDateStart & " - " & graphDateEnd & "</text>"

Raw XML:
[url]http://www.mediafire.com/?u2m5ctyzm9e[/url]

Formatted XML:
[url][/url]


Hopefully this all makes sense! Any questions please let me know :)

If my bloated XML is not discouraging I am definatley open to doing it via XML. OUTPUT variables is good too.


Thanks very much!
Mike123
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-12-10 : 12:16:14
also, to help out here are the table structures...


again, very much appreciated!!

thanks, :)
mike123

CREATE TABLE [dbo].[tblLinkSwap_ClickLog_OutBound](
[clickID] [int] IDENTITY(1,1) NOT NULL,
[linkID] [int] NOT NULL,
[referURL] [varchar](500) NOT NULL,
[IP_Address] [varchar](50) NOT NULL,
[IP_CountryShort] [varchar](50) NOT NULL,
[clickDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tblLinkSwap_ClickLog_InBound](
[clickID] [int] IDENTITY(1,1) NOT NULL,
[domainID] [int] NOT NULL,
[referURL] [varchar](500) NULL,
[landingPage] [varchar](500) NOT NULL,
[IP_Address] [varchar](50) NOT NULL,
[clickDate] [datetime] NOT NULL
) ON [PRIMARY]



CREATE TABLE [dbo].[tblLinkSwap_OutBoundLinks](
[linkID] [int] IDENTITY(1,1) NOT NULL,
[partnerID] [int] NOT NULL,
[linkTitle] [varchar](50) NOT NULL,
[linkDestination] [varchar](50) NOT NULL,
[linkWeight] [smallint] NOT NULL,
[displayStatus] [tinyint] NOT NULL,
[approvalStatus] [tinyint] NOT NULL,
[createdDate] [datetime] NOT NULL,
[approvedDate] [datetime] NULL
) ON [PRIMARY]

GO
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-11 : 03:54:25
Where is the formatted XML?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-12-11 : 03:58:32
oops, somehow missed posting it... here is the link

[url]http://www.mediafire.com/?4eetyzkm1td[/url]

much appreciated :)

thanks!
mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-11 : 04:07:24
Is this a correct indentation of the formatted xml data?
<chart>
	<axis_category shadow="low" size="11" color="000000" alpha="50" skip="2"/>
	<axis_ticks value_ticks="false" category_ticks="true" major_thickness="2" minor_thickness="1" minor_count="3" major_color="000000" minor_color="888888" position="outside"/>
	<axis_value shadow="low" size="10" color="000000" alpha="50" steps="4" prefix="" suffix="" decimals="0" separator="" show_min="true"/>
	<chart_border color="000000" top_thickness="1" bottom_thickness="2" left_thickness="0" right_thickness="0"/>
	<chart_data>
		<row>
			<string>03/12/2008 12:00:00 AM</string>
		</row>
		<row>
			<string>Clicks In</string>
			<number>7</number>
		</row>
		<row>
			<string>Clicks Out</string>
			<number>0</number>
		</row>
	</chart_data>
	<chart_grid_h alpha="5" color="0066FF" thickness="28"/>
	<chart_grid_v alpha="10" color="0066FF" thickness="1"/>
	<chart_rect x="63" y="50" width="740" height="220" positive_color="FFFFFF" positive_alpha="40"/>
	<draw>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="23" y="10" width="300" h_align="left">03/12/2008 12:00:00 AM - 03/12/2008 12:00:00 AM</text>
		<rect x="65" y="305" width="12" height="12" fill_color="5a86fb" line_color="000000"/>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="80" y="302" h_align="left">Incoming clicks you sent us (2)</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="320" h_align="left">Mean: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="335" h_align="left">Min: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="350" h_align="left">Max: 2</text>
		<rect x="475" y="305" width="12" height="12" fill_color="f75b36" line_color="000000"/>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="490" y="302" h_align="left">Outgoing clicks we sent you (2)</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="320" h_align="left">Mean: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="335" h_align="left">Min: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="350" h_align="left">Max: 2</text>
	</draw>
	<filter>
		<shadow id="high" distance="3" angle="45" color="0" alpha="50" blurX="10" blurY="10"/>
		<shadow id="low" distance="2" angle="45" alpha="35" blurX="5" blurY="5"/>
		<shadow id="bg" inner="true" quality="1" distance="25" angle="135" color="000000" alpha="15" blurX="200" blurY="150"/>
	</filter>
	<legend_rect x="-100" y="-100" alpha="50" width="250"/>
	<legend_label layout="vertical" size="12"/>
	<legend layout="hide"/>
	<series_color>
		<color>dd6b66</color>
		<color>7e6cee</color>
	</series_color>
	<series set_gap="0" bar_gap="0"/>
</chart>


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-12-11 : 04:21:52
Hi Peso,

Yes perfect! Just one tiny detail, we must include the NULL value as shown below here.

- <row>
<null />
<string>03/12/2008 12:00:00 AM</string>
</row>



Thank you!
Mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-11 : 04:23:17
Copy my post and edit it accordingly and post here again with [ code ] tags. That tag will preserve tabs.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-12-11 : 04:28:37
quote:
Originally posted by Peso

Is this a correct indentation of the formatted xml data?
<chart>
	<axis_category shadow="low" size="11" color="000000" alpha="50" skip="2"/>
	<axis_ticks value_ticks="false" category_ticks="true" major_thickness="2" minor_thickness="1" minor_count="3" major_color="000000" minor_color="888888" position="outside"/>
	<axis_value shadow="low" size="10" color="000000" alpha="50" steps="4" prefix="" suffix="" decimals="0" separator="" show_min="true"/>
	<chart_border color="000000" top_thickness="1" bottom_thickness="2" left_thickness="0" right_thickness="0"/>
	<chart_data>
		<row>
                        <null />
			<string>03/12/2008 12:00:00 AM</string>
		</row>
		<row>
			<string>Clicks In</string>
			<number>7</number>
		</row>
		<row>
			<string>Clicks Out</string>
			<number>0</number>
		</row>
	</chart_data>
	<chart_grid_h alpha="5" color="0066FF" thickness="28"/>
	<chart_grid_v alpha="10" color="0066FF" thickness="1"/>
	<chart_rect x="63" y="50" width="740" height="220" positive_color="FFFFFF" positive_alpha="40"/>
	<draw>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="23" y="10" width="300" h_align="left">03/12/2008 12:00:00 AM - 03/12/2008 12:00:00 AM</text>
		<rect x="65" y="305" width="12" height="12" fill_color="5a86fb" line_color="000000"/>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="80" y="302" h_align="left">Incoming clicks you sent us (2)</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="320" h_align="left">Mean: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="335" h_align="left">Min: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="350" h_align="left">Max: 2</text>
		<rect x="475" y="305" width="12" height="12" fill_color="f75b36" line_color="000000"/>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="490" y="302" h_align="left">Outgoing clicks we sent you (2)</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="320" h_align="left">Mean: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="335" h_align="left">Min: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="350" h_align="left">Max: 2</text>
	</draw>
	<filter>
		<shadow id="high" distance="3" angle="45" color="0" alpha="50" blurX="10" blurY="10"/>
		<shadow id="low" distance="2" angle="45" alpha="35" blurX="5" blurY="5"/>
		<shadow id="bg" inner="true" quality="1" distance="25" angle="135" color="000000" alpha="15" blurX="200" blurY="150"/>
	</filter>
	<legend_rect x="-100" y="-100" alpha="50" width="250"/>
	<legend_label layout="vertical" size="12"/>
	<legend layout="hide"/>
	<series_color>
		<color>dd6b66</color>
		<color>7e6cee</color>
	</series_color>
	<series set_gap="0" bar_gap="0"/>
</chart>


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-12-11 : 04:29:38
Hey Peso,

Looks like my second attempt worked. You can see I added the 1 value in the 8th line of the XML. I didn't bold it or anything so it can easily be copy / pasted.... hope that works for you

thanks again :)
mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-11 : 05:31:52
Which elements/tags are static and which are dynamic/change according to data?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-12-11 : 06:08:43
Hey Peso,

Let me highlight the dynamic areas, everything else is static.

Please note its the "<number>" tag in the chart data that will have a dynamic # of rows. Each row represents a day in the chart, and we obviously want to chart more than one day. Hope this is clear, if not please let me know.

Thanks!
mike123

quote:
Originally posted by mike123

quote:
Originally posted by Peso

Is this a correct indentation of the formatted xml data?
<chart>
	<axis_category shadow="low" size="11" color="000000" alpha="50" skip="2"/>
	<axis_ticks value_ticks="false" category_ticks="true" major_thickness="2" minor_thickness="1" minor_count="3" major_color="000000" minor_color="888888" position="outside"/>
	<axis_value shadow="low" size="10" color="000000" alpha="50" steps="4" prefix="" suffix="" decimals="0" separator="" show_min="true"/>
	<chart_border color="000000" top_thickness="1" bottom_thickness="2" left_thickness="0" right_thickness="0"/>
	<chart_data>
		<row>
                        <null />
			<string>03/12/2008 12:00:00 AM</string>
		</row>
		<row>
			<string>Clicks In</string>
			<number>7</number>
		</row>
		<row>
			<string>Clicks Out</string>
			<number>0</number>
		</row>
	</chart_data>
	<chart_grid_h alpha="5" color="0066FF" thickness="28"/>
	<chart_grid_v alpha="10" color="0066FF" thickness="1"/>
	<chart_rect x="63" y="50" width="740" height="220" positive_color="FFFFFF" positive_alpha="40"/>
	<draw>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="23" y="10" width="300" h_align="left">03/12/2008 12:00:00 AM - 03/12/2008 12:00:00 AM</text>
		<rect x="65" y="305" width="12" height="12" fill_color="5a86fb" line_color="000000"/>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="80" y="302" h_align="left">Incoming clicks you sent us (2)</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="320" h_align="left">Mean: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="335" h_align="left">Min: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="350" h_align="left">Max: 2</text>
		<rect x="475" y="305" width="12" height="12" fill_color="f75b36" line_color="000000"/>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="490" y="302" h_align="left">Outgoing clicks we sent you (2)</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="320" h_align="left">Mean: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="335" h_align="left">Min: 2</text>
		<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="350" h_align="left">Max: 2</text>
	</draw>
	<filter>
		<shadow id="high" distance="3" angle="45" color="0" alpha="50" blurX="10" blurY="10"/>
		<shadow id="low" distance="2" angle="45" alpha="35" blurX="5" blurY="5"/>
		<shadow id="bg" inner="true" quality="1" distance="25" angle="135" color="000000" alpha="15" blurX="200" blurY="150"/>
	</filter>
	<legend_rect x="-100" y="-100" alpha="50" width="250"/>
	<legend_label layout="vertical" size="12"/>
	<legend layout="hide"/>
	<series_color>
		<color>dd6b66</color>
		<color>7e6cee</color>
	</series_color>
	<series set_gap="0" bar_gap="0"/>
</chart>


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site