T-SQL (SS2K8) :: How To Add Counts To A Query

Jun 11, 2014

How to get task counts in a sales report.

The data is located in three tables: Projects, ProjectTasks, Sales.

I need to group the data by month and project. It needs to include sales per month as well as the number of each project task completed that month.

Projects:
ProjectIDName
1Project1

ProjectTasks:
ProTaskIDProjectIDTaskCodeBeginDateEndDate
11 Task11/1/20141/15/2014
21Task21/15/20141/20/2014
31Task3 1/21/20141/29/2014

Sales:
SalesIDProjectIDClosingAmount
11 1/31/2014$5000

Query Output:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 1

My query so far is:

SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate,
p.name as Project, SUM(s.amount) as TotalSales
FROM Sales s
JOIN Project p ON p.projectID = s.projectID
WHEREs.closing >= DATEADD(mm, -12, GETDATE())
GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name

This will give me the grouping by month/year and project.

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Obtaining Counts From Historical Data After Given Date

May 12, 2015

I'm looking to get counts on historical data where the number of records exists on or after May 1 in any given year. I've got the total number of records for each year worked out, but now looking for the number of records exist after a specific date. Here's what I have so far.

SELECT p.FY10,p.FY11,p.FY12,p.FY13,p.FY14,p.FY15
FROM
(
SELECT COUNT(recordID) AS S,
CASE DateFY

[code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Forcing Existence Of Date Records For A Graph Of Counts Over Time

Oct 14, 2014

I'm trying to get a count of Employed and Available contractors per time period, and I have a table of Contracts... something like:

CREATE TABLE empContract(
empContractID INT IDENTITY(10000,1) PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE,
ContractorAssigned INT,
FOREIGN KEY ContractorID REFERENCES Contractor(ContractorID)
);

I don't think this is possible without the existence of some kind of Calendar table. Given the existence of a calendar table, the query seems really simple - just something like:

SELECT cal.CalendarDate, ec.ContractID
FROM Calendar cal LEFT JOIN empContract ec ON cal.CalendarDate BETWEEN ec.StartDate AND ec.EndDate

The left join forces the existence of all dates in a range (@StartDate and @EndDate), so that when I try to create a graph with counts by day, I don't have any gaps in my time series.

View 2 Replies View Related

Multiple Counts In A Query

May 20, 2008

I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other  - it shows 12 as the count)  Your help is appreciated. 

View 2 Replies View Related

SQL Query For Multiple Counts

Dec 8, 1999

I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:

store isbn OnHand OnOrder
===== ========== ====== =======
104 0394572368 2 0
108 0394572368 0 1
109 0394572368 2 0
104 3321695545 2 1
108 3321695545 1 0
109 3321695545 3 1

And I need a table that will combine the isbns and give me sums
and counts like this:

Isbn OnHandLocations OnHandQty OnOrderLocations OnOrderQty
========== =============== ========= ================ ==========
0394572368 2 4 1 1
3321695545 3 6 2 2

Does anyone know of a query that can accomplish this? Thanks in advance.

View 1 Replies View Related

Get The Row Counts For Each Day Going Back To 6 Months (was Query Help)

Jun 15, 2006

I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.

With this proc i can get one day's row couts.. i need to loop through for all dates.

Please can someone get me the code for this.



create proc p_rowcounts

@Date1 datetime,
@Date2 datetime

SELECT
count (*) as 'Number of Rows', @Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @Date1 and ModifyTime < @Date2

thanks for the help.

View 3 Replies View Related

Multiple Counts In One Nested Query: How?

May 12, 2008

I'd appreciate some help with the issue below - my SQL is a bit rusty and was never that hot to be frank. I'm using SQL Server 2000 (although have a test box with 2005 Express also). I've trawled MSDN and a few forums but can't find the solution (maybe I don't know what I'mm looking for!), so any help would be marvellous...

I have a table with a field called 'IRV' containing a string of comma-separated values. I want to be able to query a point in that string and count the number of times a given value appears. So...as an example, I want to count how many times '1' appears at position 7 in the IRV. I can create SQL to do this as follows:


SELECT COUNT(X) AS is1
FROM myIRVtable
WHERE (SUBSTRING(IRV, 7, 1) = '1')


So far so good. However, it is also possible that the value at position 7 in this string could be '2' (or '3', or '4', etc) - and rather than re-running the query again and again to get these values, I'd like to do it in one hit.

How can I combine all this together - anyone have any brilliant solutions?

View 4 Replies View Related

Need To Query A Database With Distinct Column And Row Counts

May 18, 2007

On my company site, I have created a database that is for the purpose of tracking google adwords, as well as pages that the user visits.  For instance, if you were to search for "guitars" and then click our ad, an entry is created in the database like thisKeyword:         SessionGUID:         PageVisited:                                                                          VisitedDateTime:Guitars            lkjfeilfjskdlfjsije         ~ViewCategory.aspx?Cat=Guitars,KW=Guitars                       12/01/2000 12:00amGuitars            lkjfeilfjskdlfjsije         ~ViewProduct.aspx?ProductID=1253&SubProductID=3            12/01/2000 12:03amGuitars            lkjfeilfjskdlfjsije        ~Search.aspx?Q=BC%20%Rich                                             12/01/2000 12:05am Pretty much, in our administrative area, I want to be able to have a table that would generate these results: Keyword              Total HitsGuitars                3So im guessing that obviously I would need to do a select distinct for the Keyword column, but how do I also have a column showing a count of the records? 

View 1 Replies View Related

SQL Enterprise Manager And Query Analyzer Displaying Different Row Counts

Jul 23, 2005

The row counts I am getting from SQL Enterprise Manager and QueryAnalyzer are different. When I double click a table in EnterpriseManager it gives me a row count of say 1000. However, when I select *from that same table in Query Analyzer it returns 1028 rows. Anyoneknow why or better yet, how to fix it? Thanks!

View 2 Replies View Related

Parameterized Query That Counts The Number Of Null Values In An Int32 Column

Jul 18, 2006

Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));

cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));

cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));


cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));


cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));


cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));




cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.

View 5 Replies View Related

T-SQL (SS2K8) :: Query For Treeview

Sep 11, 2012

I have a query which is working fine. Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.

In other words Child column is directly under Table2's row column name (Father), but occasionally it comes under Table1 with no relation to Table 2.

How can I out put that in a query for a treeview? I am assuming that I will have to program the out come in c# also with 3 for loops and in the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of treeview, but I am having a problem building a query in sql.

The query below shows all Parent, then child then grand child(all well and working), but what is desired is at times child takes place of a father.

declare @x as xml
set @x =
(
SELECT distinct
Table1.AssetSysID, Table1.Asset_ID , Table1.FromLR, Table1.Asset_ID + ', ' + Table1.[Desc2] as GarndFather,
Table2.ACISysID ,Table2.PAssetSysID, Table2.FeatureName + ', ' + Table2.[DESC] AS Father,
Table3.ITMSysID ,Table3.Item_ID + ',' + Table3.[DESC] as Child

[Code] .....

View 6 Replies View Related

T-SQL (SS2K8) :: Duplicates In Sub Query

Sep 13, 2014

I have the piece of sql code here below that keeps giving out duplicates. How to resolve this.

isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1
LEFT OUTER JOIN
#BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and
a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0)
As Actual_CurrentMonth,

View 1 Replies View Related

T-SQL (SS2K8) :: Query To Get Total At The End

Sep 15, 2014

select col1,count(*) from client1..table1 group by col1
union
select col1,count(*) from client2..table1 group by col1
union
select col1,count(*) from client3..table1 group by col1

The results yields

33915
3405
3412

I am trying to get the following result but can't figure out how to get the total in the end.

33915
3405
3412
Total 22

View 2 Replies View Related

T-SQL (SS2K8) :: Pivot And Unpivot In Same Query?

Feb 15, 2014

I have below table and within same query i need pivot and unpivot.

create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp

Now i need output in below format

name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30

View 4 Replies View Related

T-SQL (SS2K8) :: Appropriate Query For Multi Parameters?

Mar 13, 2014

I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.

Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.

For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.

SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName

[Code] ....

View 3 Replies View Related

T-SQL (SS2K8) :: Query To Track Record?

Mar 14, 2014

I am having table which is having 5 columns say A,B,C,D and E. There are chances to change in C,D,E columns. I want to identify if any change happened in the above column and show to user prev value + new value.

i.e. i want to prepare query by using calculated extra 6 column where i want to put change happned in C,D,E columns.

how to do that?

View 5 Replies View Related

T-SQL (SS2K8) :: Query XML Data For Blanks

May 1, 2014

I am try to see if there are any blanks in a node of a table that has xml data in one of the columns. The query I use is returning zero results.

Select COUNT(*)from ENTITY
Where CONVERT(XML, Ent_root_xml, 0 ).value('(//UD_PQ_FLAG/node())[1]', 'VARCHAR(50)')= ''

View 1 Replies View Related

T-SQL (SS2K8) :: No Error When Sub Query Not Valid

May 16, 2014

I have a query that is coming back with all my rows from an "IN" where condition. The problem is that the query in the sub-query is invalid.

I can't recreate it exactly but in the sample here, I don't get any rows back (not sure why they are different) but why am I not getting an error?

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
DROP TABLE #temp1

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
DROP TABLE #temp2

CREATE TABLE #temp1

[Code] ...

The "SELECT LastName FROM #temp2" subquery is invalid and if you run it by itself you do get the error.

So why no error when this runs.

The other query is:

SELECT *
FROM Staging ves
WHERE ves.ssn IN ( SELECT ssn
FROM Employee )

There is no "ssn" in the Employee table (it is in a different table), I get 12,000 records back from Staging.

Why would that be the case?

View 3 Replies View Related

T-SQL (SS2K8) :: Subtracting Figures Within A Query

Aug 27, 2014

I have a table CombinedSales script below (CreateCombSalesTable.sql) which contains the details of a sale.

A row in the table has two flags online/offline
SalesAmount is the financial amount,
online = 1 (online sales)
online = 0 (combined sales off and online).

The table also contains the product and the client etc.,

What I am trying to do is subtract onlinesales from combinedsales within the query or outside I don't mind, to return the result as (below)

HERE is HOW I want the results to appear

skuid(No column name) onlineClientName
200437215183.350Asda
20043716816.651Asda

I query the table using:

select skuid,SUM(SalesAmount),online,ClientName from t_CombinedSalesDetails
where ClientName ='Asda'
and SkuId = 200437
group by Skuid,Online,ClientName
order by skuid

THIS is what I get below the online = 0 is the total sales of this product.

skuid(No column name) onlineClientName
200437232000.000Asda
20043716816.651Asda

How I could achieve the required results

/****** Object: Table [dbo].[t_CombinedSalesDetails] Script Date: 27/08/2014 13:23:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON

[Code] ....

View 1 Replies View Related

T-SQL (SS2K8) :: BCP Query-out Returns No Records?

Sep 3, 2014

I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.

I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:

DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)

[code].....

The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:

output

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL

If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.

I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Show ED / CESS In Query

Nov 5, 2014

Detail:

create table detail
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)

insert into detail values

('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
values ('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
values ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')

i wanna display ED ,CESS ,HCESS,VAT in separate columns in output How to that?

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Group By Only One Column

Jan 21, 2015

How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?

select col1, col2, col3
from testtable
group by col1.

View 4 Replies View Related

T-SQL (SS2K8) :: Using For XML Path To Concatenate In A Sub-Query

Feb 17, 2015

how to properly use the "For XML Path" code to concatenate multiple values into one record. What I have is a procedure I've written for a SSRS report that summarizes drive information: date, account, recruiter, times.But each drive can have multiple shifts, and each shift will have it's own vehicle assigned. So a drive may have 1 vehicle assigned to it or it may have 5. If there are 5 shifts/5 vehicles, I don't want to the report to display 5 rows of data for the one drive. So I'm trying to get all of the vehicles to be placed into one field for the report.My initial sub-query will not work because it is possible that it will contain more than one item (vehicle):

Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
DSD.ShiftID [ShiftID],
Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic],
Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],

[code]...

SQL Server Newbie or here either. I'm a newbie as well.

View 8 Replies View Related

T-SQL (SS2K8) :: SUM OVER Coming Up With Different Values Than CTE Query

Mar 6, 2015

I am currently reading through Itzik Ben-Gan's "Microsoft SQL Server 2012 High-Performance T-SQL using Windows Functions." In attempt to test the SUM OVER() function in SQL 2008 because that's what I've got. I do not currently have sample data (trying to generate it has become a major PITA), but I have some pseudocode.

My current code (actual production code) pulls a bunch of ITD (inception to date) contracts then calculates a certain dollar amount based on monthly changes. Not all contracts have values during a given month, so here's what I cobbled together a few months ago. (Per our finance team, these numbers ARE accurate).

WITH MonthlyVals AS
(SELECT ContractID, SUM(Col1 - (Col2 + Col3 + Col4 + Col5)) AS MyTotal
FROM MyTable
WHERE MyDate >= @ThisMonthStartDate AND MyDate <= @ThisMonthEndDate
AND StatementType IN (8,4,2)

[code]....

To test the totals, I also added a COMPUTE SUM(MyTotal) to the end of each query. (Yes, I know COMPUTE is deprecated. Just wanted a quick check.). The difference between the two bits of code was over 68k, with the SUM OVER() code coming up with a total higher than the CTE code. I know CTE code is correct for a fact. It went through extensive testing before getting put in Production. Is it the way I joined the table for the SUM OVER()? Or is it the use of PARITION BY?

View 5 Replies View Related

T-SQL (SS2K8) :: Job Details In Query Output

May 1, 2015

I have a T-SQL query that outputs exactly as I wish when run via Management Studio. I now want to automate this query to run monthly via a SQL Agent Job.

I have got this to run but the resulting file is left with a header row which shows "Job 'MyJobName' : Step 1, 'My QUERY' : Began Executing 2015-05-01 10:52:01" and an extra blank row before my data (and column headers).

Is there any way of stopping these extra rows from being created using T-SQL in my query?

View 2 Replies View Related

T-SQL (SS2K8) :: Query Not Use Same Execution Plan?

Jun 5, 2015

if t-sql query is perfectly run in development and when I execute in production at that time I want to use execution plan which is in development . so how I can do using cache? I know about hint we can use hint USE_PLANE. but I want to do with cache .

View 1 Replies View Related

T-SQL (SS2K8) :: Best Way To Snapshot Results From A Query?

Oct 28, 2015

best way to snapshot results from a query?

View 3 Replies View Related

T-SQL (SS2K8) :: Query Using Multiple APPLY Joins

Mar 20, 2014

I’ve never written a query with multiple APPLY joins before and I’m running into some troubles with my first one. The below SQL statement runs within 10 seconds if I comment out either one of the APPLY joins and its corresponding field columns. However, when I try to execute with both APPLY joins, the query runs indefinitely. The longest I’ve waited before cancelling it is 90 minutes.

Now, I know there are probably other ways I could write this query to get me the results I’m looking for. I’m posting this on the board because I’m curious about finding out why multiple APPLY joins could cause SQL Server to run away. I’m hoping to gain some insight so that I can better understand how APPLY joins work so that in case I have a big need to do this again in the future (without suitable workarounds) I can code it correctly.

Here are some things I’ve tried so far…

1.Changed the States table into a subquery that only returns a single state
2.Change all the references inside the APPLY subqueries so that they had different aliases (just in case they were conflicting with each other).
3.Changed the CROSS applies to OUTER applies. States has 50 records and only 32 have matching permit data so the 18 extra iterations using OUTER APPLY don’t impact performance any when an APPLY is used by itself.

SELECT s.state_name
, COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits
, SUM(DistinctPermits) AS DistinctPermits
FROM States S
CROSS APPLY (SELECT w.StateID, COUNT(*) as DistinctPermits

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Query Regarding Update Data Using Application

Apr 7, 2014

SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact

Contact table contains 4 columns as explained. in the application there is one tab called Contact where it displays Above information and description is non editable.Now the new requirement has come user can update the description information and save information in a new column say 'Description1'. that means new column needs to be added in the db and also necessary changes needs to be done at the application side

For ex :SELECT ContactID,FirstName,MiddleName,LastName,Description,Description1 FROM Contact

Now when user views the contact table it should display description info by default from 'description' table.If he edits he should see edited data from 'description1' table.the logic should if updated data is there display that data from 'description1' table other wise display from 'description' table

View 9 Replies View Related

T-SQL (SS2K8) :: Arrival And Depart Location Query

Apr 7, 2014

I have a question regarding the selection of arrival and departure city.

CREATE TABLE #XY123
(
tktamt MONEY,
departcty VARCHAR(5),
arrivalcty VARCHAR(5),
tktnum INT

[Code] ....

There are One way, Round trips and journey with connecting flights in the above data, what I am looking for a report is tktamt, deprt city, arrival city and tktnum in a single line for each trip.

SO if it is one way trip or round trip there is no issue, if it is a trip with connecting flights tktnum has more than four records. In the above case for tktnum 34567 there are four records, for which I have to display only one record, where the depart city is 'JFK and Arrival city is 'SFO' as well as amount and tktnum.

View 9 Replies View Related

T-SQL (SS2K8) :: Removing Not Necessary Joins In Child Query?

May 23, 2014

I am building a application, which will generates a MASTER query with 15 fields & 5 tables joins.Now, the user selects only some fields from that master fields and generates the CHILD query. This is creating a performance issue.

To minimize this, i would like to remove the not necessary joins in the child query.

View 8 Replies View Related

T-SQL (SS2K8) :: Query To Combine Records In A Single Row

Jun 5, 2014

I'm working on a report where my table is as follows:

WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'

[Code] .....

Here is my query against the table above:

SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE

FROM SampleData

Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):

IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL

[Code] .....

My Desired results are as follows:

IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement

View 2 Replies View Related

T-SQL (SS2K8) :: Search Query Including Security?

Jul 17, 2014

I'm trying to integrate the security settings for our system into the reports and search and its a nightmare trying to fit in all the rules.

Basically I have a massive custom search query which I now have filter even further. [URL] for previous discussions on this query, which I'm currently happy with).

so we have 6 different types of transactions. each type can have different admins. the search can be done for either 1 type or all types.Transactions by default are available to everyone, But there are a few (probably less than 1% of all) that security is enabled which is simply done by added people to the security table.

each transaction can be see by

* Everyone If no record found in security table for transaction.

* If even just one transaction found, the below have permission to view it.

- Person who raised transaction

- person currently responsible for it.

- person currently working on it.

- everyone in the security table for this transaction.

- Admins for this Transaction Type.

So when someone does a search I need to fit all the above. Because I'm building the search query it does make it a little easier to accommodate the various scenarios.And I think I have them all EXCEPT someone who is trying to search for records under ALL types, but is an admin of just 1 or 2 of the types. Ie they have too be able to see all records for those they are admins for, and then have normal security imposed for the other types of records.

The section near "DECLARE @IsUserAdmin AS BIT" sets admin flags for when they are an admin of a single type if searching for a single type, or sets admin flag if they are an admin for all types.

ALTER PROCEDURE [dbo].[uspJobSearch]
@csType as nvarchar(20) = '-1',
@status as nvarchar(20) = '-1',
@startID as nvarchar(50) = '',
@endID as nvarchar(50) = '',
@complaintType as nvarchar(50) ='',

[code]....

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved