I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.
Thanks in advance.
Mark.
Did you try rtrim(ltrim(columnname) in the query?
cheers,
Andrew
|||Thanks Andrew,
rtrim(columnname) did it.
Cheers,
Mark.
Hi all,
I want to generate the ad-hoc report using SQL Reporting Services 2000 or SQL Reporting Services 2005. But the Database Server will be SQL Server 2000. So, Can I send the query at run-time and then attach the data source to the actual report. In this case there will not be any columns etc. at design time with the value field mentioned in it. I know this is not the requirement for which this report tool is developed for. But still if this is possible through some other way round then pls. let me know. Actually we want one generic report which will satisfy all the tables, with diff. conditions mentioned in the select clause and where clause. So, is this possible with this reporting tool? Is there any way out where we want to come up with only one such report by manipulating the datasource of the report at run-time? Pls. let me know.
Thanks & Regards,
Dipali.
There may be a better way to do this in 2005 but the way I acheived dynamic columns in 2000 was to have a maximum number of columns (say 20) that take the value Column01, Column02....Column20 and make sure your data source returns the values under those names. Then you can also pass in the column names as a parameter. That's the only way I could come up with for producing a completely dynamic report.sqlHow one can handle dynamic SQL which has to perform JOIN with other tables.
Example I showed in my first post say has 2 - 3 tables:
PropertyListings and PropertyFeatures, PropertyLotFeatures
PropertyListings table gives me basic information I need to display.
PropertyFeatures and PropertyLotFeatures has information I need for advance search. I don't know which table to use and Join till user selects some features. based on Features selected for advance search I may have to Join:
1) PropertyListings with PropertyFeatures
2) PropertyListings with PropertyLotFeatures
3) PropertyListings with propertyFeatures and PropertyLotFeatures
I know once can right such query in C#, but is that how they do it or there is a better way? Am I missing something?
Let me give an Example :
How about when there is Join Present. How whould you build Dynamic SQL? Do you need Dynamic SQL or there is a Better Way?
For Eg.
Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests
Table4 = Activities
Table5 = Customers_Activities
Table3 holds Customers Interests.
Table5 holds Customers Activities
Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3
Activities Table has Data: A1, A2, A3, A4
Customers_Activities has Data:
C1, A1
C1, A4
C2, A3
C4, A1
Now lets say on my webpage I represent Interests and Activities as CheckBoxes.
Case 1:
I select Interests I1, I3, I4 but No Activities Selected. In this case my query has to join Customers, Interests and Customers_Interests Table
So query will be: Give me list of Customers with Interests I1, I3, I4
Case 2:
If I select Activities A1, A3 and No Interests, query whas to join Customers, Activities and Customers_Activities Table
Case 2:
If I select Interests I1 and Activities A1, in this case Query has to join Customers, Interests, Customers_Interests, Activities and Customers_Activities tables(all tables)
How would you write Query?
Looking for help!!!!!!!!!!!!!!!!!!!!!!!!!!!
I am trying to insert a value numeric + 1 in to db table but i get error when i do this
this is the code
Const SQLAsString ="INSERT INTO [PageHits] ([DefaultPage]) VALUES (@.defaultP)"
Dim myCommandAsNew Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@.DefaultP" +"1", DefaultP.Text.Trim())myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
The Error:
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Must declare the variable '@.defaultP'.
The code you wrote will send a parameter called @.DefaultP1 to the database.
Did you want to increase an existing value in the database? If so, you must use an UPDATE query.sql
Dim connectionString As String = "server='srv_sql'; user id='sa'; password='MyPassword'; Database='MyDB'"
Dim dbConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
However since I have
<identity impersonate=true>
in my web.config file... it tries to login to the MSDE database as the user.. not as the user SA.
I cannot change the web.config file, since I need that for the active directory authentication to work...
in SQL Server you can add users to a database through the enterprise manager.. how can I do a similar thing using MSDE ?
Is it even possible? or do I have to upgrade to a full SQL instance?
any help would be appreciatedTo answer your question, use the osql command line utility. See this KB article for more info:HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility.
what you need to do is use the sp_grantlogin system sp to add the login:
EXEC sp_grantlogin 'Corporate\Test'
Then give it access like this:
EXEC sp_grantdbaccess 'Corporate\BobJ', 'Bob'
You'll also need to give it permissions.
That said, there may need to be adjustments for use with Active Directory.
BUT, you should never, ever, NEVER use the sa login for database access. Not for any app and certainly not for an ASP.NET app. You're opening yourself up to a whole lot of hurt doing it this way. Instead, create a login that has only the specific permissions needed to run the app. No more.
It's more work, but you'll have made the app far more secure.
Don|||...for sure don't use SA on the page itself. What about permissioning the database with Windows authenticated logins since you are using AD...if you haven't already. If you do then you can set windows authentication via Internet Services Manager on the directory hosting the page (be sure to get rid of anonymous users)
I have two fields, Date Purchased (smalldatetime) and warranty in months (integer).
can anyone help me to formulate a query to show the date when the warranty ends, and/or the remaining days/months in the warranty please?
I'm not sure if its doable, but i would really appreciate it if anyone can help me!
Give a look to the DATEADD function in books online; it looks like this:
declare @.example table
( rid integer,
datePurchased smalldatetime,
warranty integer
)insert into @.example
select 1, '4/8/7', 3 union all
select 2, '5/14/7', 12select rid,
convert(varchar(10), datePurchased, 101) as datePurchased,
warranty,
dateadd (mm, warranty, datePurchased) as warrantyEndDate,
datediff (day, getdate(), dateadd (mm, warranty, datePurchased)) as daysRemaining,
datediff (mm, getdate(), dateadd (mm, warranty, datePurchased)) as monthsRemaining
from @.example/*
rid datePurchased warranty warrantyEndDate daysRemaining monthsRemaining
-- - -- - -
1 04/08/2007 3 2007-07-08 00:00:00 45 2
2 05/14/2007 12 2008-05-14 00:00:00 356 12
*/
so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028
could return a result set that looked like:
ID TITLE CLASS_ID URL
1 "Hello" 137 "hardcodedfilename.aspx?id=1"
3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"
19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"
I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?
Thanks in advance for your help.
roger
There are a few approaches, two different ways are shown below.
Chris
DECLARE @.URLTemplate VARCHAR(100)
SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'
SELECT ID,
TITLE,
CLASS_ID,
REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL
FROM ...
--or
DECLARE @.prefix VARCHAR(100)
SET @.prefix = '"hardcodedfilename.aspx?id='
SELECT ID,
TITLE,
CLASS_ID,
@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL
FROM ...
|||Thank you for your quick response! SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML
DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.
Any suggestions? And thanks again for your help.|||
I've made a couple of corrections, see the example below.
I wasn't sure whether you needed the trailing space in the following:
CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
If not, then simply replace the above with the following:
CAST(ID AS VARCHAR(10)) as "@.url",
Chris
CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @.prefix VARCHAR(100)
SET @.prefix = 'rightframe.aspx?s='
RETURN (
SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE
)
END
|||That works beautifully!I can't thank you enough.
Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!
Again, Thank you.|||
HI Chris,
I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:
Thanks
SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.LName)) AS AnalName, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.LName)) AS AgentName, dbo.l_tblAgent.AgentDesc AS AgentDesc,
CRM_SQL_ADMIN.vwOfferAgent_Names.DCAName AS DCAName, CRM_SQL_ADMIN.vwOfferAgent_Names.MCAName AS MCAName,
CRM_SQL_ADMIN.vwOfferAgent_Names.DIRName AS DIRName, CRM_SQL_ADMIN.vwOfferAgent_Names.RMName AS RMName,
CRM_SQL_ADMIN.vwOfferAgent_Names.SReps AS SReps,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'PayTerms' AND [OPTION] = [tblOffer].[PayTerms]) AS PayTerms_Desc, dbo.l_tblCust.CustName AS CustName,
dbo.l_tblCust.StAddress AS StAddress, dbo.l_tblCust.City AS City, dbo.l_tblCust.State AS State, dbo.l_tblCust.ZipCode AS ZipCode,
dbo.l_tblCust.GPO AS GPO, dbo.l_tblCust.IsTargetCust AS IsTargetCust,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgBen' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgBen]) AS ProgBen_Desc,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgCrit' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgCrit]) AS ProgCrit_Desc, dbo.tblOffer.SpecProgType AS SpecProg,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt), 'mm dd, yyyy') AS EndOfQtr,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.WeekDayAdd(- 4, CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt)), 'mm dd, yyyy') AS EndOfQtrLess3d,
OSS.Tot_Qty AS Tot_Qty, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Purch) AS Tot_Purch, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Savings)
AS Tot_Savings
FROM dbo.l_tblCust INNER JOIN
dbo.tblOffer ON dbo.l_tblCust.CustNum = dbo.tblOffer.CustNum LEFT OUTER JOIN
dbo.l_tblBusAnalyst ON dbo.tblOffer.BusAnalystID = dbo.l_tblBusAnalyst.EmpNum LEFT OUTER JOIN
CRM_SQL_ADMIN.vwOfferAgent_Names ON dbo.tblOffer.OfferID = CRM_SQL_ADMIN.vwOfferAgent_Names.OfferID LEFT OUTER JOIN
dbo.l_tblAgent ON dbo.tblOffer.AgentID = dbo.l_tblAgent.AgentID INNER JOIN
CRM_SQL_ADMIN.vwOfferSums_Simple OSS ON dbo.tblOffer.OfferID = OSS.OfferID|||
Change:
dbo.l_tblAgent.AgentDesc AS AgentDesc,
to:
( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,
|||Gr8...That works...Thanks a lot Arnie for your help...so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028
could return a result set that looked like:
ID TITLE CLASS_ID URL
1 "Hello" 137 "hardcodedfilename.aspx?id=1"
3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"
19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"
I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?
Thanks in advance for your help.
roger
There are a few approaches, two different ways are shown below.
Chris
DECLARE @.URLTemplate VARCHAR(100)
SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'
SELECT ID,
TITLE,
CLASS_ID,
REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL
FROM ...
--or
DECLARE @.prefix VARCHAR(100)
SET @.prefix = '"hardcodedfilename.aspx?id='
SELECT ID,
TITLE,
CLASS_ID,
@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL
FROM ...
|||Thank you for your quick response! SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML
DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END
But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.
Any suggestions? And thanks again for your help.
|||
I've made a couple of corrections, see the example below.
I wasn't sure whether you needed the trailing space in the following:
CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
If not, then simply replace the above with the following:
CAST(ID AS VARCHAR(10)) as "@.url",
Chris
CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @.prefix VARCHAR(100)
SET @.prefix = 'rightframe.aspx?s='
RETURN (
SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE
)
END
|||That works beautifully!I can't thank you enough.
Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!
Again, Thank you.
|||
HI Chris,
I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:
Thanks
SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)Change:
dbo.l_tblAgent.AgentDesc AS AgentDesc,
to:
( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,
|||Gr8...That works...Thanks a lot Arnie for your help...sqlI am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI).
Given the sample data:
CREATE TABLE [dbo].[QtrInflation] (
[Qtr] [smalldatetime] NOT NULL ,
[CPI] [decimal](8, 4) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO QtrInflation (Qtr, CPI)
SELECT '1960-03-01', 0.7500 UNION
SELECT '1960-06-01', 1.4800 UNION
SELECT '1960-09-01', 1.4600 UNION
SELECT '1960-12-01', 0.7200 UNION
SELECT '1961-03-01', 0.7100 UNION
SELECT '1961-06-01', 0.7100 UNION
SELECT '1961-09-01',-0.7000 UNION
SELECT '1961-12-01', 0.0000 UNION
SELECT '1962-03-01', 0.0000 UNION
SELECT '1962-06-01', 0.0000 UNION
SELECT '1962-09-01', 0.0000 UNION
SELECT '1962-12-01', 0.0000 UNION
SELECT '1963-03-01', 0.0000 UNION
SELECT '1963-06-01', 0.0000 UNION
SELECT '1963-09-01', 0.7100 UNION
SELECT '1963-12-01', 0.0000 UNION
SELECT '1964-03-01', 0.7000 UNION
SELECT '1964-06-01', 0.7000 UNION
SELECT '1964-09-01', 1.3900 UNION
SELECT '1964-12-01', 0.6800 UNION
SELECT '1965-03-01', 0.6800 UNION
SELECT '1965-06-01', 1.3500 UNION
SELECT '1965-09-01', 0.6700 UNION
SELECT '1965-12-01', 1.3200
I am trying to return the following results:
Qtr CPI YrCPI AvgYrCPI
--- -- -- ---
1-Jun-60 1.48
1-Sep-60 1.46
1-Dec-60 0.72
1-Mar-61 0.71 4.37
1-Jun-61 0.71 3.60
1-Sep-61 -0.70 1.44
1-Dec-61 0.00 0.72 2.53
1-Mar-62 0.00 0.01 1.44
1-Jun-62 0.00 -0.70 0.37
1-Sep-62 0.00 0.00 0.01
1-Dec-62 0.00 0.00 -0.17
1-Mar-63 0.00 0.00 -0.18
1-Jun-63 0.00 0.00 0.00
1-Sep-63 0.71 0.71 0.18
1-Dec-63 0.00 0.71 0.36
1-Mar-64 0.70 1.41 0.71
1-Jun-64 0.70 2.11 1.24
1-Sep-64 1.39 2.79 1.76
1-Dec-64 0.68 3.47 2.45
1-Mar-65 0.68 3.45 2.96
1-Jun-65 1.35 4.10 3.45
1-Sep-65 0.67 3.38 3.60
1-Dec-65 1.32 4.02 3.74
Note, 4 data points are required to calculate a moving sum of CPI
(YrCPI) and 4 calculate YrCPI figures are required calculate the
annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
first 7 results
This sad effort is about as far as I've got:
SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
FROM QtrInflation I
JOIN (
SELECT TOP 4 Qtr, CPI
FROM QtrInflation
) S
ON S.Qtr <= I.Qtr
GROUP BY I.Qtr, I.CPI
ORDER BY I.Qtr ASC
Can anyone suggest how do achieve this result without having to resort
to cursors?
Thanks,
StephenHi
This will do it (I think!) but there may be a neater way!
SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
FROM QtrInflation S LEFT JOIN
( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
LEFT JOIN
( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
< B.Qtr
GROUP BY R.Qtr
HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
ORDER BY S.Qtr
John
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||Stephen,
Here is another approach that I think will work
for you:
-- alternate solution
create table Weights (
offset int,
weight decimal(3,2),
weightA decimal(3,2),
weightB decimal(3,2)
)
go
insert into Weights
select 6, 0, 0, 0.25 union all
select 5, 0, 0, 0.5 union all
select 4, 0, 0, 0.75 union all
select 3, 0, 1, 1.00 union all
select 2, 0, 1, 0.75 union all
select 1, 0, 1, 0.5 union all
select 0, 1, 1, 0.25
go
select
dateadd(month,3*Offset,Q1.Qtr) Qtr,
sum(Weight*Q1.CPI) CPI,
case when sum(WeightA) = 4 then sum(WeightA*Q1.CPI) else NULL end as YrCPI,
case when sum(WeightB) = 4 then sum(WeightB*Q1.CPI) else NULL end as MACPI
from QtrInflation Q1, Weights
group by dateadd(month,3*Offset,Q1.Qtr)
having sum(Weight) = 1
order by dateadd(month,3*Offset,Q1.Qtr)
-- Steve Kass
-- Drew University
-- Ref: 17F9A22A-8DDA-4812-A8CD-B68062BADFA1
Stephen Miller wrote:
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||John & Steve
Thank you for two very interesting (and very different) responses. You
guys are gurus! Both return the results I'm looking for and now I'm
stuck picking which one's best ;)
Thanks again,
Stephen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f6d878f$0$10783$afc38c87@.news.easynet.co.uk>...
> Hi
> This will do it (I think!) but there may be a neater way!
> SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
> FROM QtrInflation S LEFT JOIN
> ( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
> LEFT JOIN
> ( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
> FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
> < B.Qtr
> GROUP BY R.Qtr
> HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
> ORDER BY S.Qtr
> John
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> > Hi,
> > I am trying to add a staggered running total and average to a query
> > returning quarterly CPI data. I need to add 4 quarterly data points
> > together to calculate a moving 12-month sum (YrCPI), and then to
> > complicate things, calculate a moving average of the 12-month figure
> > (AvgYrCPI).
> > Given the sample data:
> > CREATE TABLE [dbo].[QtrInflation] (
> > [Qtr] [smalldatetime] NOT NULL ,
> > [CPI] [decimal](8, 4) NOT NULL
> > ) ON [PRIMARY]
> > GO
> > INSERT INTO QtrInflation (Qtr, CPI)
> > SELECT '1960-03-01', 0.7500 UNION
> > SELECT '1960-06-01', 1.4800 UNION
> > SELECT '1960-09-01', 1.4600 UNION
> > SELECT '1960-12-01', 0.7200 UNION
> > SELECT '1961-03-01', 0.7100 UNION
> > SELECT '1961-06-01', 0.7100 UNION
> > SELECT '1961-09-01',-0.7000 UNION
> > SELECT '1961-12-01', 0.0000 UNION
> > SELECT '1962-03-01', 0.0000 UNION
> > SELECT '1962-06-01', 0.0000 UNION
> > SELECT '1962-09-01', 0.0000 UNION
> > SELECT '1962-12-01', 0.0000 UNION
> > SELECT '1963-03-01', 0.0000 UNION
> > SELECT '1963-06-01', 0.0000 UNION
> > SELECT '1963-09-01', 0.7100 UNION
> > SELECT '1963-12-01', 0.0000 UNION
> > SELECT '1964-03-01', 0.7000 UNION
> > SELECT '1964-06-01', 0.7000 UNION
> > SELECT '1964-09-01', 1.3900 UNION
> > SELECT '1964-12-01', 0.6800 UNION
> > SELECT '1965-03-01', 0.6800 UNION
> > SELECT '1965-06-01', 1.3500 UNION
> > SELECT '1965-09-01', 0.6700 UNION
> > SELECT '1965-12-01', 1.3200
> > I am trying to return the following results:
> > Qtr CPI YrCPI AvgYrCPI
> > --- -- -- ---
> > 1-Jun-60 1.48
> > 1-Sep-60 1.46
> > 1-Dec-60 0.72
> > 1-Mar-61 0.71 4.37
> > 1-Jun-61 0.71 3.60
> > 1-Sep-61 -0.70 1.44
> > 1-Dec-61 0.00 0.72 2.53
> > 1-Mar-62 0.00 0.01 1.44
> > 1-Jun-62 0.00 -0.70 0.37
> > 1-Sep-62 0.00 0.00 0.01
> > 1-Dec-62 0.00 0.00 -0.17
> > 1-Mar-63 0.00 0.00 -0.18
> > 1-Jun-63 0.00 0.00 0.00
> > 1-Sep-63 0.71 0.71 0.18
> > 1-Dec-63 0.00 0.71 0.36
> > 1-Mar-64 0.70 1.41 0.71
> > 1-Jun-64 0.70 2.11 1.24
> > 1-Sep-64 1.39 2.79 1.76
> > 1-Dec-64 0.68 3.47 2.45
> > 1-Mar-65 0.68 3.45 2.96
> > 1-Jun-65 1.35 4.10 3.45
> > 1-Sep-65 0.67 3.38 3.60
> > 1-Dec-65 1.32 4.02 3.74
> > Note, 4 data points are required to calculate a moving sum of CPI
> > (YrCPI) and 4 calculate YrCPI figures are required calculate the
> > annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> > first 7 results
> > This sad effort is about as far as I've got:
> > SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> > FROM QtrInflation I
> > JOIN (
> > SELECT TOP 4 Qtr, CPI
> > FROM QtrInflation
> > ) S
> > ON S.Qtr <= I.Qtr
> > GROUP BY I.Qtr, I.CPI
> > ORDER BY I.Qtr ASC
> > Can anyone suggest how do achieve this result without having to resort
> > to cursors?
> > Thanks,
> > Stephen|||Hi Stephen
I would expect Steve's solution to work alot better than mine under
large loads!
John
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI). <<
I hope you mean to have a key on this table and some contraints
CREATE TABLE QtrInflation
(qtr SMALLDATETIME NOT NULL PRIMARY KEY
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
cpi DECIMAL(8,4) NOT NULL
CHECK(cpi >= 0.0000));
CREATE TABLE QtrReportRanges
(start_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
end_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
CHECK (start_date < end_date),
PRIMARY KEY (start_date < end_date));
INSERT INTO QtrReportRanges VALUES ('1960-03-01', '1960-12-01');
INSERT INTO QtrReportRanges VALUES ('1960-06-01', '1961-03-01');
etc,
now you can get the report easily.
SELECT R.start_date, R.end_date, SUM(cpi) AS yr_cpi, AVG(cpi) AS
avg_yr_cpi
FROM QtrInflation AS I, QtrReportRanges AS R
WHERE I.qtr BETWEEN R.start_date AND R.end_date
GROUP BY R.start_date, R.end_date;
I've a sql query problem I was wondering if you all had a quick and
dirty solution for. I've a query:
Select code, value from table_a where date in
(2004) and a_code in ('1000','2000') and b_code in ('01000','02000')
This returns a table that looks like:
A_CODE B_CODE VALUE
-- -- --
1000 01000 $500
1000 02000 $750
What I'd like to see is:
A_CODE B_CODE VALUE
-- -- --
1000 01000 $500
1000 02000 $750
2000 01000 $0
2000 02000 $0
Any suggestions on how to rewrite my query so the results show A_CODE
2000 with a VALUE of 0 or null?
Thank much in advance!
MarcMarc (brownjenkn@.aol.com) writes:
> I've a sql query problem I was wondering if you all had a quick and
> dirty solution for. I've a query:
> Select code, value from table_a where date in
> (2004) and a_code in ('1000','2000') and b_code in ('01000','02000')
> This returns a table that looks like:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> What I'd like to see is:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> 2000 01000 $0
> 2000 02000 $0
> Any suggestions on how to rewrite my query so the results show A_CODE
> 2000 with a VALUE of 0 or null?
CREATE TABLE a_code (a_code char(4) NOT NULL
CREATE TABLE b_code (b_code char(5) NOT NULL
go
INSERT a_code (a_code) VALUES ('1000')
INSERT a_code (a_code) VALUES ('2000')
INSERT b_code (b_code) VALUES ('01000')
INSERT b_code (b_code) VALUES ('02000')
go
SELECT a.a_code, b.b_code, coalesce(t.value, 0)
FROM (a_code a
CROSS JOIN b_code b)
LEFT JOIN table_a t ON a.a_code = t.a_code
AND b.b_code = t.b_code
ABD t.date = '2004'
Here I am handling a_code and b_code in the same way, so you will
get output for missing b_codes as well.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
