I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.
I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?
like:
create table #temp (row_no int identity (1,1),time datetime, action varchar(5))
insert into #temp select * from cte5
Hi guys,
I know its not a good idea to give you a big query...but/...
I have a query (given below) with Common table expressions. It is giving me error : The multi-part identifier "dbo.tmpValidServices_ALL.Service" could not be bound. for all the fields .. Do you have any idea.. I fed up with analysing query.. made me mad.. please help me...
----------------------------------------------
DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000)
SET @sql = 'WITH Q1 (SiteID,Programme,Complete,PID,COC,NotionalSum,TVMinsSUM,Postcode,Suburb,Qty)
AS
(SELECT
dbo.tmpValidServices_ALL.SiteID,
dbo.tmpValidServices_ALL.Programme,
dbo.tmpValidServices_ALL.Complete AS Complete,
dbo.tmpValidServices_ALL.RID AS PID,
dbo.tmpValidServices_ALL.COC# AS COC,
(dbo.lkpService.Notional$) AS NotionalSum,
(TVMins) AS TVMinsSUM,
dbo.tmpDemographics_ALL.Postcode,
dbo.tmpdemographics_ALL.Suburb,
count(*) as Qty
FROM lkpService
INNER JOIN dbo.tmpValidServices_ALL vs ON dbo.lkpservice.code = dbo.tmpValidServices_ALL.Service
INNER JOIN dbo.tmpDemographics_ALL ON dbo.tmpValidServices_ALL.RID = dbo.tmpDemographics_ALL.RID '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON dbo.tmpValidServices_ALL.COC = c.pvcode '
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '
SELECT @sql = @sql + 'WHERE dbo.lkpService.Schedule = @Schedule '
IF @StartDate IS NOT NULL
SELECT @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete >= @StartDate ) '
IF @EndDate IS NOT NULL
SELECT @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete <= @EndDate ) '
IF @SiteID IS NOT NULL
SELECT @sql = @sql + 'AND (ag.EXACT# = @SiteID) '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '
IF @Provider IS NOT NULL
SELECT @sql = @sql + 'AND (dbo.tmpValidServices_ALL.Provider = @Provider) '
IF @Postcode IS NOT NULL
SELECT @sql = @sql + 'AND (dbo.tmpdemographics_ALL.Postcode = @Postcode) '
SELECT @sql = @sql + ' GROUP BY dbo.tmpValidServices_ALL.SiteID,
dbo.tmpValidServices_ALL.Programme,
dbo.tmpValidServices_ALL.Complete ,
dbo.tmpValidServices_ALL.RID ,
dbo.tmpValidServices_ALL.COC# ,
dbo.tmpDemographics_ALL.postcode,
dbo.tmpdemographics_ALL.Suburb,
(dbo.lkpService.Notional$),
(dbo.lkpService.TVMins) )
SELECT COUNT(DISTINCT PID + CAST(Complete AS varchar(20)) + CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Visits,
COUNT(DISTINCT PID + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Patients,
COUNT(DISTINCT CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20)) + CAST(PID AS varchar(20))) AS COCs,
SUM(NotionalSum) AS NotionalSum,
SUM(TVMinsSUM) AS TVMinsSUM,Postcode,Suburb,sum(Qty) as Qty
FROM Q1
Group by Postcode,Suburb
Order by Postcode,Suburb '
SET @paramlist = '@SiteID as numeric,
@COCGroup as varchar(20),
@Postcode as varchar(20),
@StartDate DateTime,
@EndDate DateTime,
@Schedule varchar(20),
@Provider varchar(20)'
EXEC sp_executesql @sql, @paramlist, @SiteID, @COCGroup, @Postcode, @StartDate, @EndDate, @Schedule, @Provider
I have managed to write my first CTE SQL that handles recursion but I have a problem with distinct - can't get that to work!!
My CTE:WITH StudentsHierarchy(SystemID1, GroupID, AccessType, AccessGroupID, StudentID, HierarchyLevel) AS
(
--Base Case
SELECT
SystemID,
GroupID,
AccessType,
AccessGroupID,
StudentID,
1 as HierarchyLevel
FROM UserGroup a
UNION ALL
--Recursive step
SELECT
u.SystemID,
u.GroupID,
u.AccessType,
u.AccessGroupID,
u.StudentID,
uh.HierarchyLevel + 1 as HierarchyLevel
FROM UserGroup u
INNER JOIN StudentsHierarchy uh ON
u.GroupID = uh.AccessGroupID
)
Select sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
and I would like to have a distinct on the StudentID like:Select DISTINCT sh.StudentID, sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
How should I do?
After reading all the comments about microsoft ole db provider for db2, I've started converting a project from ibm db2 provider to microsoft ole db provider, for the reason that the server would not need a ibm client software installation anylonger.
However in one of the dataflows there is a select statement on the DB2 database (AS400 iseries DB2 V5R4) that uses common table expression. The Oledb data source in the Dataflow returns zero input columns when I use the microsoft provider instead of the ibm provider. When I set it back to use the ibm oledb provider the input columns are available again.
The common table expression is used in the select statement like:
WITH x AS (SELECT ... FROM mytable WHERE ...)
SELECT x.*, (x.a - x.b) As diff
FROM x
We are not allowed to change anything in the DB2 database, so using views or sprocs is not an option.
Did anyone else experience this and can it be avoided without changing the selct logic?
Hi all,
I have the following T-SQL code of Common Table Express (CTE) that works in the SQL Server Management Studio Express (SSMSE):
--CTE.sql--
USE ChemAveRpd
GO
WITH PivotedLabTests AS
(
SELECT LT.AnalyteName, LT.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN LT.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN LT.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN LT.Result END)
FROM LabTests LT
JOIN Samples S ON LT.SampleID = S.SampleID
GROUP BY LT.AnalyteName, LT.Unit
)
SELECT AnalyteName, Unit, avg1 = (abs(Prim + Dupl)) / 2,
avg2 = (abs(Prim + QA)) / 2,
avg3 = (abs(Dupl + QA)) / 2,
RPD1 = (abs(Prim - Dupl) / abs(Prim + Dupl)) * 2,
RPD2 = (abs(Prim - QA) / abs(Prim + QA)) * 2,
RPD3 = (abs(Dupl - QA) / abs(Dupl + QA)) * 2
FROM PivotedLabTests
GO
===========================================
How can I execute this set of the CTE.sql code in the VB 2005 Express via the Stored Procedure programming?
Please help and respond.
Thanks in advance,
Scott Chang
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedHi all, real basic question:
I can't seem to find a basic reference that tells me the syntax and allowable values of parameters in common functions such as Format or CDate when editing a Field Expression in Visual Studio (Report Definition Language?). Where would I start?
For example, I've discovered that the Format function "Returns a string formatted according to instructions contained in a format String expression.", and the string expression can have values like "D" and "d" which produce different results, but where would I find out what the allowable string expressions are and their meaning?
I have also been guessing at the syntax of the CDate command with no luck. I need a reference that tells me what the different function parameters mean.
thanks
Jac
Hi
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan
hi i am using MS SQL Server 2000, i want to delete the common row from a table
table name EMP
rows
Eno Name Sal
1 Jim 1000
2 Mark 1200
1 Jim 1000
2 Mark 1250
1 Jim 1300
no primary key defile in to the table ....
i want to delete the row of
1 Jim 1000
please anybody can can help me ...
samarjit
What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston
View 2 Replies View RelatedHi there!
I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.
When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.
First, what do you think of that implementation?
We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...
What is the common solution for this need?
Thanks
Frantz
I would like to know if SQL Server creates automatic index on the used tables' common fields.
For example:
Code:
CREATE VIEW [dbo].[ORMAN]
AS
SELECT dbo.PARSEL.OBJECTID, dbo.PARSEL.ADAPARSEL, dbo.PARSEL.ORMANADI, dbo.PARSEL.MULKIYET, dbo.PARSEL.ALAN, dbo.PARSEL.YERKOD,
dbo.PARSEL.UYG_KAN, dbo.PARSEL.KomNo, dbo.PARSEL.KadKan, dbo.PARSEL.Mescere, dbo.PARSEL.ACIKLAMA, dbo.PARSEL.URETIM_YONTEMI,
[Code] .....
On the above view example, if table PARSEL has 1 million records do I have to create an index for PARSELDURUM or when I create a view does SQL server create automatic virtual index?
When I connect two table should they always have a column in common with each other?For example the 'patients' and 'doctors' tables in that ER are connected to say each doctor can have many patients and each patient can have many doctors, but they have no columns in common. Is it right?
View 8 Replies View RelatedBasically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...
something like
SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta
FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
With my new data, need result for below scenario ( use CTE )
´`````````````
CREATE TABLE
´´´´´´´´´´´´´´´´
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Code] ....
===============================
Insert DATA TABLE 1
===============================
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FL' ,'FLIGHT' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FP' ,'FLIGHT-Pas' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')
===============================
Insert DATA TABLE 2
===============================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'FL' ,'1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'FP' ,'2','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'SE' ,'-1','0' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'SP' ,'4','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'RD' ,'-1','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'BU' ,'6','3' )
========
RULE
========
1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.From the available result,
2.HasNodes = Yes, Display Parent and 1st Node
3.HasNodes = No, Display Parent Only.
================
Expected Result
================
Mode_Info|Mode_Detail|QA
AIR||4
AIR|FLIGHT|2
ROAD||6
===============
My Query
=============
learning CTE To apply for HasNodes Rules.
;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
from [Travel_Quantity] T1
left join [Travel_Master] T2 on T1.Mode_Id = T2.Mode_Id
) ,
MyCTE2 as (
[code]....
I am working on building a template/design pattern for a DTS to SSIS upgrade project.
During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.
I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.
Has anyone done something like this, or have suggestions on how we might accomplish?
Hi,
I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.
What is wrong with my syntax?
;With OrderedTable
AS
(
select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee
)
delete from OrderedTable where RowId != 1
SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc
I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:
WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */
END
ELSE
BEGIN
/* Do something else here */
END
When I try this, I get a syntax error near the keyword 'IF'
Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!
I tried to use WITH to factor out the shared star join portion from a sql statement as it's usually the most expensive part.
However, examing the execution plan shows that the WITH clause is merely a syntactic suger that will internally be plugged back as derived tables where the same star join is executed repeatedly.
Is the intermediate rowset produced by a WITH caluse ever shared during the query execution?
Hi guys,
I'm trying to have two common table expression in my stored procedure, but I'm receiving errors when executing it, I found that they can't exist side by side,once I removed 1 of them, the stored procedure executed successfully.
The following are the errors
Code:
Msg 156, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 33
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I'm using SQL Server Express
Hi,
We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.
Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.
In ASP Application, We call this query €œ081Stats€? from Record set.
This €˜081Stats€™ query call below sub query itself
Master Query: 081Stats
SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;
Sub Query 1: 08Accepted
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 2: 08Applicants
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 3: 08Dinged
SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 4: 08Interviewed
SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 5: 08Matriculating
SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.
How can I call that CTE from ASP.NET 2.0 using C#?
CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.
How can I implement the CTE, where can I write the CTE and where can I store the CTE.
And how can I call the CTE from ASP.NET 2.0 using C#.
It€™s Very Urgent Requirements.
We need your timely help.
With Thanks & Regards,
Sundar
Hi,
I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table.
So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this.
This is what I want to change:
Declare @Col Varchar(10)
Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10))
Insert into @TempTable
Exec Procedure @Paramter
select @Col = col2 from @TempTable
INTO
With CTE(Col2, Col3, Col4) AS
(
Exec Procedure @Paramter)
select @Col = col2 from @TempTable
Thanks
Punu
Little complex but I'll try to explain the best I canI have the following table…
UserID___Year___Month_____A1____A2____B1____B2
__1_______4______5______45_____5____43____0
__1_______4______6______10_____12___1_____3
__1_______5______5______12_____22___8_____7
__1_______5______6______11_____10___5_____0
__2_______4______5______9______65___33____66
__2_______4______6______3______3____10____11
__2_______5______5______44_____2___77_____1
I want to pull out the data to show the sum of the totals for each year the user is in. The idea is to then be able to see a comparison for year on year on a month by month basis
Eg.
UserID____TotalAYear4____TotalBYear4___TotalAYear5____TotalBYear5___Month
__1___________50_____________43___________34____________15__________5
__1___________22_____________4____________21____________5___________6
__2___________74_____________99___________21____________5___________5
There would be no data returned for UserID2 for month 6 as there is no data for them for year 5 month 6 therefore no comparison can be made.
I’ve having real difficulty getting the correct SQL syntax to do this. Any ideas or pointers would be great thanks
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;
Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
[code]...
I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.
I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.
********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';
WITH CTE AS (
SELECT bkg_nbr
[Code] ....
The raw data from the CTE is:
Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1
The result of the query above was:
Category RecCount
Non-Recidivists3
Recidivists 1
The outcome I was looking for was:
Category RecCount
Non-Recidivists2
Recidivists 1
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId
[code]....
What is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset? The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and then limiting the persisted results based on the CTE function.
It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
hi friends i am reading a csv file and storing the data in table i have created the tables like this:
coloumn names datatype constraints
start_date_time varchar(50) allownull = true
ani varchar(50) allownull=true
dialed_digit varchar(50) allownull=true
actual_dur varchar(50) allownull=true
rounded_dur varchar(50) allownull=true
cost varchar(50) allownull=true
and the second table as
coloumn names datatype constraints
country varchar(50) allownull = true
code varchar(50) allownull=true
rate/min varchar(50) allownull=true
now i want to relate and join the table so that i can create a view with the following coloumns such as
start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...
I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.
I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.
CREATE TABLE dbo.CodesTable (
PartyGrpId INT  NOT NULL
 , CdTyp  VARCHAR ( 8 ) NOT NULL
 , CompId INT  NOT NULL
 , CdValue VARCHAR ( 8 ) NOT NULL
 , CdValueDesc VARCHAR ( 255 ) NULL
 , AltValueDesc VARCHAR ( 100 ) NULL
[Code] ....
I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.
ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'
Hi,
First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?
Thanks,
Al
i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.
i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.
the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?