How To Return Months For Column Labels In Crosstab
Aug 1, 2013
I want to line up across the top of a cross tab Jan-12 Feb-12 ... July-13 Aug-13 up to and including the latest month in the db. What's the best way to achieve this?
I currently create crosstabs with case statements as follows:
select
sum(case month(date) when 1 then value) as Jan,
sum(case month(date) when 2 then value) as Feb
from db
I figured you could create some sort of while loop for the case statements but I can't get the syntax right.
Or another strategy would be to use the Pivot method and build [Jan], [Feb], [Mar] etc through a loop?
I came across an article in SQL Mag about Crosstab Queries. It worksgreat in Query Analyzer, but I'm stuck on how to use it in an AccessADP. I need to use it as a Recordsource in a form and report. Cansomeone tell me how to use it, and please try to be as descriptive aspossible. I'm new to Stored Procedures.Thanks*****************************************CREATE PROC sp_CrossTab@table AS sysname, -- Table to crosstab@onrows AS nvarchar(128), -- Grouping key values (on rows)@onrowsalias AS sysname = NULL, -- Alias for grouping column@oncols AS nvarchar(128), -- Destination columns (on columns)@sumcol AS sysname = NULL -- Data cellsASDECLARE@sql AS varchar(8000),@NEWLINE AS char(1)SET @NEWLINE = CHAR(10)-- step 1: beginning of SQL stringSET @sql ='SELECT' + @NEWLINE +' ' + @onrows +CASEWHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsaliasELSE ''ENDCREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)DECLARE @keyssql AS varchar(1000)SET @keyssql ='INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +'FROM ' + @tableEXEC (@keyssql)DECLARE @key AS nvarchar(100)SELECT @key = MIN(keyvalue) FROM #keysWHILE @key IS NOT NULLBEGINSET @sql = @sql + ',' + @NEWLINE +' SUM(CASE CAST(' + @oncols +' AS nvarchar(100))' + @NEWLINE +' WHEN N''' + @key +''' THEN ' + CASEWHEN @sumcol IS NULL THEN '1'ELSE @sumcolEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS c' + @keySELECT @key = MIN(keyvalue) FROM #keysWHERE keyvalue > @keyENDSET @sql = @sql + @NEWLINE +'FROM ' + @table + @NEWLINE +'GROUP BY ' + @onrows + @NEWLINE +'ORDER BY ' + @onrows-- PRINT @sql + @NEWLINE -- For debugEXEC (@sql)GO
I have the following SSAS MDX query, currently taking a From & To date. How would I alter the MDX below so that just the prior 3 months data was returned for a chart of balances. I do not wish to retain the date parameters
SELECT NON EMPTY { [Measures].[EOD Book Balance] } ON COLUMNS, NON EMPTY { ([Time].[Simple Date].[Simple Date].ALLMEMBERS * [Products].[Product ID].[Product ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) : STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Type].&[Term] } ) ON COLUMNS FROM ( SELECT ( { [Book Balance Type].[Balance Type].&[Credit Balance] } ) ON COLUMNS FROM [DailyBalances]))) WHERE ( [Book Balance Type].[Balance Type].&[Credit Balance], [Products].[Product Type].&[Term] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I currently have a stored procedure that returns a list of dates based on a date range a user enters.
CREATE PROCEDURE sp_GetContactScheduleDates @MonthFrom int, @YearFrom int, @MonthTo int, @YearTo int, @DaysInMonth int AS Select distinct s.ScheduleMonth, s.ScheduleYear From OnCall_Schedules s Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime) >= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime) And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime) <= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime) Order by s.ScheduleYear, s.ScheduleMonth GO
However, this only brings back those dates that are in the table. I need to get ALL dates within the range.
For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?
Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.
Can some sort of function be used? What would the code look like?
I've been trying to create a function that returns the difference, in months, between two dates. The DateDiff function (m) returns an integer, but I really need a decimal. I could return the days instead, but it would be difficult to figure out how the number of months from this, especially when the dates span multiple years and I need to adjust for leap year. Does anyone know of a resource that might have a solution for this?
I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.
Hi,I need what would be similar to a cross tab query in Access.First Column down needs to show all the months, column headings wouldbe the day of the month....1 2 3 4 etc...JanFebMaretchow do i set this up in a stored procedure?any help to get me in the right direction would be greatlyappreciated!!thanks,paul
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[TrialBalance]( [Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
Hi all I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.
Thanks.
TRANSFORM Min(tCompany.cCompanyName) AS CompanyName SELECT tProjContacts.ProjectID FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*")) GROUP BY tProjContacts.ProjectID PIVOT tCompanyType.CoType;
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN (SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR (user2_guid = @userGuid))) AND (UserId IN (SELECT UserId FROM espace_ProfileProperty))
I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:
The following query:
SELECT t_Category.Name as [Category] FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID AND t_Category.Category_ID = t_User_Assets.Category_ID AND t_Priority.Priority_ID = t_User_Assets.Priority_ID AND t_Location.Location_ID = t_User_Assets.Location_ID
Returns this result:
Category BMS BMS Water BMS BMS Air
And the following query:
SELECT COUNT(t_Category.Category_ID) AS AssetQty FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID AND t_Category.Category_ID = t_User_Assets.Category_ID AND t_Priority.Priority_ID = t_User_Assets.Priority_ID AND t_Location.Location_ID = t_User_Assets.Location_ID GROUP BY t_Category.Category_ID
Returns this result:
AssetQty 4 1 1
I need to have both of those results returned, as a single result. Such as:
Category AssetQty
BMS 4 WATER 1 AIR 1
However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:
Column 't_Category.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.
Hi thereI have the following two tablesmainprofile (profile varchar(20), description)accprofile (profile varchar(20), acct_type int)Sample data could bemainprofile----------------prof1 | profile oneprof2 | profile twoprof3 | profile threeaccprofile--------------prof1 | 0prof1 | 1prof1 | 2prof2 | 0Now doing a join between these two tables would return multiple rows,but I would like to know whether it would be possible to returnacct_type horizontally in a column of the result set, e.g.prof1 | profile one | [0,1,2]prof2 | profile two | [0]I could probably manage this with cursors, but it would be veryresource intensive. Is there a better way?Regards,Louis
Hello, I'm using C# to access sql server. When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use? Thanks.
SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount FROM tbl_CYProcessedSales WHERE tc NOT LIKE 'T%' AND tc NOT LIKE 'R%' AND ISNUMERIC(TC) = 0 AND NOT submitrep1 = '' AND Submitrep1 = 'along' GROUP BY submitrep1, tc
Returns a result like this:
ALONG KL 65 ALONG KP 35
How can I return the one record that contains the MAX(TCCount)?
I have this query that returns the largest value in a row, but i need to know the column name that this value is in as well. any help in advance is appreciated
select clientID, (select max(incomeValue) from (select earnings as incomeValue union all select unemployment union all select pensionRetirement union all select alimony union all select childSupport union all select dividendInterest union all select SS union all select SSI union all select SSDI union all select veteranBenefits union all select FIP union all select workStudy union all select other union all select otherHHWS) as income) as MaxIncomeValue from tbl_income
I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.
From the INFORMATION_SCHEMA.TABLES view I want to return the TABLE_NAME of tables that have columns say, named Email and EmailStatusId. Is it possible to do this with a single select statement or would I have to use two selects for this?
I am using BCP and one of the tables in my SQL contains a column of type nvarchar(500) - whose data are actually email messages such as:
Sent: Wednesday, January 09, 2008 11:03 PM To: Kathy Shouner Cc: Brent Ford; William Dew Subject: RE: Revision of presentation schedule
I support the recommended revisions.
When I open the datafile, it spans 6 separate lines - probably ending in carriage returns (not sure how to confirm this). Hence, I am having a problem importing it to my table. When I query the database, it appears as one line under that column - which is the way it should be.
I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?
