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

Query Returns More Records Than Expected

Nov 29, 2012

I am having a query

select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber

tarrifheadno SUM(INVOICEITEMS.ItemQuantity)

84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20

tarrifheadno is unique

Now if i use below query and add invoicetypecode field

select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode

This query return distinct of 11 records

84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2

How to get same 7 records

View 1 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) :: Most Updated Records From Multiple Join Query

Mar 28, 2014

i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).

View 5 Replies View Related

T-SQL (SS2K8) :: Select Query With Records And Sequential Numbers

Apr 5, 2014

I have a problem. In my database I have the following numbers available:

101
104
105
110
111
112
113
114

What I need is to get a select query with records and sequentials numbers after it like:

101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0

How can I do It?

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Avoid Duplicate Records (across Columns)

Jan 3, 2015

rewrite the below two queries (so that i can avoid duplicates) i need to send email to everyone not the dup[right][/right]licated ones)?

Create table #MyPhoneList
(
AccountID int,
EmailWork varchar(50),
EmailHome varchar(50),
EmailOther varchar(50),

[Code] ....

--> In this table AccountID is uniquee

--> email values could be null or repetetive for work / home / Other (same email can be used more than one columns for accountid)

-- a new column will be created with name as Sourceflag( the value could be work, Home, Other depend on email coming from) then removes duplicates

SELECT AccountID , Email, SourceFlag, ROW_NUMBER() OVER(PARTITION BY AccountID, Email ORDER BY Sourceflag desc) AS ROW
INTO #List
from (
SELECTAccountID
, EmailWorkAS EMAIL
, 'Work'AS SourceFlag
FROM#MyPhoneList (NoLock) eml
WHEREIsOffersToWorkEmail= 1

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

T-SQL (SS2K8) :: Script That Returns All SPs To Given Table

Apr 9, 2015

I am looking for a script, that returns all SPs, which related to a given table, incl. the access type.For example: the SPs SP_test1, SP_test2, SP_test3 are dependencies of MyTable

This should be the result:
MyTable
--> SP_test1: select
--> SP test1: update
--> SP_test1: drop
--> SP test2: delete
--> SP_test3: insert

There are a lot of scripts, who returns the related SPs.

View 9 Replies View Related

T-SQL (SS2K8) :: Fetch Next From Cursor Returns Multiple Rows?

Aug 9, 2014

I don't understand using a dynamic cursor.

Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.

Now the Skript to reproduce the whole thing.

create database objects

-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;

[Code]....

Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.

View 8 Replies View Related

T-SQL (SS2K8) :: User Defined Function That Returns Start And End Date

Oct 15, 2014

Any UDF that accepts a Month and Year and returns Start Date and End Date.

For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.

View 8 Replies View Related

T-SQL (SS2K8) :: Obtain A Record Set That Returns Just First Occurrence Of ItemNo Field

Jun 9, 2015

Here is a CTE query

With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
order by ItemNo

The results from Query1 and Query2 overlap sometimes.

The result set looks like:

1 Item1 10000
2 Item1 10000
1 Item2 20000
1 Item3 30000

I only want the first occurrence of each item. The desired result set is:

1 Item1 10000
1 Item2 20000
1 Item3 30000

I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".How would I obtain a record set that returns just the first occurrence of the ItemNo field?

View 7 Replies View Related

Sp Returns Records In Qa, But Not In Adp

Dec 3, 2001

I have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".

To repeat, the sp runs fine in QA, just not in access.

Any thoughts, anybody? Many thanks. David

Alter Procedure spDailyMicroLabel
@Date Datetime = null

AS
IF @Date IS NULL
BEGIN

SET @Date=getdate()

SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled",
fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate",
fldfermidold AS "FermenterCode",
CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay",
"Sample Test" = CASE
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1
THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA'
ELSE 'No Test Micro'
END
INTO #tblTempA
FROM vwUnfilteredFermenters

SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay,
tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results"
FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay

END

View 1 Replies View Related

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

QA Returns Different Records From SQL Server EM

Jul 20, 2005

SQL Server 7.0If I run the following in Query Analyzer I get no records returned:exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'If, however, I run either :exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'orexec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'I get a single record returned in each case (which is what I wouldexpect).The SQL for GetLeadsOutcome_Dealer is:CREATE PROCEDURE GetLeadsOutcome_Dealer@FromDate smalldatetime,@ToDate smalldatetime,@OutcomeTypeID integer,@DealerCode varchar(8000)ASDECLARE @TotalLeads integerBEGINSELECT @TotalLeads=COUNT(fldLeadID) FROM tblLeads WHERE(tblLeads.fldDealerID IS NOT NULL)SELECT DISTINCT (dbo.tblDealers.fldDealerName),COUNT(dbo.tblLeads.fldLeadID) as LeadCount,@TotalLeads AS TotalLeadsFROM tblLeads LEFT OUTER JOINtblOutcome ON tblLeads.fldOutcomeID =tblOutcome.fldOutcomeID RIGHT OUTER JOINtblDealers RIGHT OUTER JOINtblRegion ON tblDealers.fldRegionID =tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerIDWHERE (((dbo.tblLeads.fldEntered) BETWEEN @FromDate And @ToDate)) ANDtblOutcome.fldOutcomeID=@OutcomeTypeID AND(dbo.tblDealers.fldDealerCode in (@DealerCode))GROUP BY dbo.tblDealers.fldDealerNameORDER BY dbo.tblDealers.fldDealerNameENDGOHowever, if I open EM, open a table in query view, and paste this intothe SQL window,SELECT DISTINCT tblDealers.fldDealerName, COUNT(tblLeads.fldLeadID) ASLeadCountFROM tblLeads LEFT OUTER JOINtblOutcome ON tblLeads.fldOutcomeID =tblOutcome.fldOutcomeID RIGHT OUTER JOINtblDealers RIGHT OUTER JOINtblRegion ON tblDealers.fldRegionID =tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerIDWHERE (tblLeads.fldEntered BETWEEN '1/1/2003' AND '12/2/2003') AND(tblOutcome.fldOutcomeID = 10) AND (tblDealers.fldDealerCode IN (176,183))GROUP BY tblDealers.fldDealerNameORDER BY tblDealers.fldDealerNameI get two records returned.What is happening?TIAEdwardTABLE DEFS:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblLeads_tblDealers]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tblDealersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblSystemUsers_tblDealers]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINTFK_tblSystemUsers_tblDealersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblLeadNotes_tblLeads]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINTFK_tblLeadNotes_tblLeadsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblDealers_tblRegion]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_tblRegionGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblDealers]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblLeads]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblOutcome]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblRegion]GOCREATE TABLE [dbo].[tblDealers] ([fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,[fldDealerCode] [varchar] (50) NOT NULL ,[fldDealerName] [varchar] (50) NULL ,[fldDealerTel] [varchar] (20) NULL ,[fldDealerEmail] [varchar] (100) NULL ,[fldDealerContact] [varchar] (50) NULL ,[fldRegionID] [int] NULL ,[fldDealerActive] [smallint] NOT NULL ,[fldHeadOffice] [smallint] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblLeads] ([fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,[fldAccountNo] [varchar] (50) NULL ,[fldDealerID] [int] NULL ,[fldStatusID] [int] NOT NULL ,[fldOutcomeID] [int] NOT NULL ,[fldContacted] [smallint] NOT NULL ,[fldDateContacted] [smalldatetime] NULL ,[fldAppointment] [smallint] NULL ,[fldShowRoom] [smallint] NOT NULL ,[fldTestDrive] [smallint] NOT NULL ,[fldSalesPersonID] [int] NULL ,[fldCustomerName] [varchar] (50) NULL ,[fldCHouseNo] [varchar] (50) NULL ,[fldCStreet] [varchar] (50) NULL ,[fldCDistrict] [varchar] (50) NULL ,[fldCTown] [varchar] (40) NULL ,[fldCCounty] [varchar] (50) NULL ,[fldCPostcode] [varchar] (50) NULL ,[fldPhoneInd] [varchar] (20) NULL ,[fldCTel] [varchar] (50) NULL ,[fldNewBusinessDate] [smalldatetime] NULL ,[fldAgreementType] [varchar] (50) NULL ,[fldCashPrice] [smallmoney] NULL ,[fldBalanceFin] [smallmoney] NULL ,[fldCustRate] [varchar] (10) NULL ,[fldOrigTerm] [int] NULL ,[fldPPPType] [varchar] (10) NULL ,[fldBalloonValue] [smallmoney] NULL ,[fldMonthlyInstal] [smallmoney] NULL ,[fldRegNo] [varchar] (10) NULL ,[fldRegDate] [smalldatetime] NULL ,[fldModel] [varchar] (50) NULL ,[fldDescription] [varchar] (50) NULL ,[fldTheoPIFDate] [smalldatetime] NULL ,[fldMonthsToGo] [int] NULL ,[fldBalanceOS] [smallmoney] NULL ,[fldLeadPrinted] [smallint] NULL ,[fldMergeFile] [varchar] (255) NULL ,[fldTPS] [varchar] (10) NULL ,[fldMPS] [varchar] (10) NULL ,[fldUpdated] [smalldatetime] NULL ,[fldUpdatedBy] [int] NULL ,[fldEntered] [smalldatetime] NULL ,[fldReasonLeadNotProgressed] [varchar] (5000) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblOutcome] ([fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,[fldOutcomeCode] [int] NULL ,[fldOutcome] [varchar] (100) NULL ,[fldConvertedSales] [smallint] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblRegion] ([fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,[fldRegionCode] [varchar] (10) NULL ,[fldRegion] [varchar] (50) NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADDCONSTRAINT [PK_tblDealers] PRIMARY KEY CLUSTERED([fldDealerID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADDCONSTRAINT [PK_tblLeads] PRIMARY KEY CLUSTERED([fldLeadID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADDCONSTRAINT [PK_tblOutcome] PRIMARY KEY CLUSTERED([fldOutcomeID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADDCONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED([fldRegionID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADDCONSTRAINT [DF_tblDealers_fldActive] DEFAULT ((-1)) FOR[fldDealerActive],CONSTRAINT [DF_tblDealers_fldHeadOffice] DEFAULT (0) FOR[fldHeadOffice],CONSTRAINT [IX_tblDealers] UNIQUE NONCLUSTERED([fldDealerCode]) ON [PRIMARY]GOALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADDCONSTRAINT [DF_tblLeads_fldOutcomeID] DEFAULT (10) FOR[fldOutcomeID],CONSTRAINT [DF_tblLeads_fldContacted] DEFAULT (0) FOR [fldContacted],CONSTRAINT [DF_tblLeads_fldAppointment] DEFAULT (0) FOR[fldAppointment],CONSTRAINT [DF_tblLeads_fldShowRoom] DEFAULT (0) FOR [fldShowRoom],CONSTRAINT [DF_tblLeads_fldTestDrive] DEFAULT (0) FOR [fldTestDrive],CONSTRAINT [DF_tblLeads_fldLeadPrinted] DEFAULT (0) FOR[fldLeadPrinted],CONSTRAINT [DF_tblLeads_fldTPSMatch] DEFAULT ('NO') FOR [fldTPS],CONSTRAINT [DF_tblLeads_fldMPSMatch] DEFAULT ('NO') FOR [fldMPS]GOALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADDCONSTRAINT [DF_tblOutcome_fldConvertedSales] DEFAULT (0) FOR[fldConvertedSales]GOALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADDCONSTRAINT [IX_tblRegion] UNIQUE NONCLUSTERED([fldRegionCode]) ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] ADDCONSTRAINT [FK_tblDealers_tblRegion] FOREIGN KEY([fldRegionID]) REFERENCES [dbo].[tblRegion] ([fldRegionID])GOALTER TABLE [dbo].[tblLeads] ADDCONSTRAINT [FK_tblLeads_tblDealers] FOREIGN KEY([fldDealerID]) REFERENCES [dbo].[tblDealers] ([fldDealerID])GOalter table [dbo].[tblLeads] nocheck constraint[FK_tblLeads_tblDealers]GO

View 2 Replies View Related

Dataset Returns 0 Records

Feb 6, 2008

I am new to SSRS 2005 but find it to be a wonderful development tool. I discovered that when you run a report where the dataset returns no records the resulting report is blank as opposed to page header, table header, no data, and page footer. Is there a way to force these items to be displayed even when the dataset is empty? Or better yet is there a way to return a (custom) empty dataset message in place of the report body?

Thanks.

View 1 Replies View Related

SQL Call INNER JOIN Returns Too Many Records

Jan 25, 2006

I have my SQL call:
SELECT     CallLog.CallID, Journal.HEATSeqFROM         CallLog INNER JOIN                      Journal ON CallLog.CallID = Journal.CallID
There are multiple enteries in the Journal table for every entry in the CallLog table, so I receive multiple records:
CallID           HEATSeq00000164     983290904 00000164     983291548 00000164     983295209 00000231     984818271 00000231     985194317 00000231     985280248
I only want to return the LAST record in the Journal table, so the output will be:
CallID           HEATSeq00000164     983295209 00000231     985280248
Can this be done directly in the SQL call?

View 7 Replies View Related

How Many Records A Stored Procedure Returns?

Oct 11, 2007

Hi,
I need to know whether a stored procedure returns only a single record or it can return more than one if the query if for example to return all records in a specific date range.
Thanks.

View 6 Replies View Related

IS NULL Returns Empty Records (using TEXT Type)

Mar 3, 2008

Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?

Code Snippet

SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL
Thanks, Onam.

View 10 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

SQL Server VARCHAR(MAX) Column Returns Error While Inserting Records Into Table(ODBC Driver: SQL Native Client)

Aug 15, 2007

I created very simple table with 3 columns and one is varchar(max) datatype

When i insert records thru VC++ ADO code i am getting this error

Exception Description Multiple-step OLE DB operation generated errors. Check e
ach OLE DB status value, if available. No work was done. and Error Number:: -2147217887

ODBC Driver: SQL Native Client

SQL server 2005

Table

CREATE TABLE [dbo].[RAVI_TEMP](

[ID] [int] NULL,

[Name] [varchar](max) NULL,

[CITY] [varchar](50) NULL

)

VC++ code

#include "stdafx.h"
#include <string>
#include <strstream>
#include <iomanip>

int main(int argc, char* argv[])
{
try
{
HRESULT hr = CoInitialize(NULL);
_RecordsetPtr pExtRst = NULL;
_bstr_t bstrtDSN, bstrtSQL;
bstrtDSN = L"DSN=espinfo;UID=opsuser;PWD=opsuser;";
bstrtSQL = L"SELECT * FROM RAVI_TEMP";

_variant_t vartValueID,vartValueNAME,vartValueCITY;
_bstr_t bstrtValueID,bstrtValueNAME,bstrtValueCITY;

pExtRst.CreateInstance(__uuidof(Recordset));
hr = pExtRst->Open(bstrtSQL, bstrtDSN, adOpenDynamic, adLockOptimistic, adCmdText);

hr = pExtRst->AddNew();

bstrtValueID = L"1";
vartValueID = bstrtValueID.copy();

bstrtValueNAME = L"RAVIBABUBANDARU";
vartValueNAME = bstrtValueNAME.copy();

bstrtValueCITY = L"Santa Clara";
vartValueCITY = bstrtValueCITY.copy();

pExtRst->GetFields()->GetItem(L"ID")->Value = vartValueID;
pExtRst->GetFields()->GetItem(L"NAME")->Value = vartValueNAME;
pExtRst->GetFields()->GetItem(L"CITY")->Value = vartValueCITY;
pExtRst->Update();
pExtRst->Close();

}
catch(_com_error e)
{
printf("Exception Description %s and Error Number:: %d",(LPTSTR)e.Description(),e.Error());
return e.Error();
}
return 0;
CoUninitialize();
}

if i use regular SQL ODBC driver, no error but its truncating the data

Adv Thanks for your help

View 1 Replies View Related

Query Returns No Row

Feb 23, 1999

When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you

View 2 Replies View Related

T-SQL (SS2K8) :: Compare Records In Tables?

Mar 3, 2014

There are four tables

1. Matter

MID, CID, RType

001, a, m
002, a, m
003, b, m
004, c, m

2. Category

CID. RType
a, T
b, T
c, T

3. Security assignmnet

RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04

4. Group

GID
g01
g02
g03
g04

I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"

In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"

How can I create qury to find all the possible record in the table #2?

View 9 Replies View Related

T-SQL (SS2K8) :: Filter Records In Table?

Mar 6, 2014

I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.

MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013

I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.

MachineA 5/7/2011
MachineB 5/5/2010

Select Distinct would almost do it, but I need each unique machine that has the latest date.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Get Distinct Records From Table

Apr 14, 2014

Suppose I have 2 table

1)Main
2)History

Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate

IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id

i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table

table structure
main table
MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory
1100100phone
2150101phone
3200109smartphone
4100202smartphone
History table
History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate
11150100phone4/14/2014
21200101phone4/13/2014
34109202smartphone4/14/2014
44101202smartphone4/13/2014
52200101phone4/13/2014
63100109smartphone4/12/2014

View 3 Replies View Related

T-SQL (SS2K8) :: How To Get One Row On A Table With Multiple Records

Apr 24, 2014

I have a table called TBLCataloghi

I have multiple records with colunms codpro and codcat equal

They differ only by a date called catalog.datfin

I'd like to select all rows but with the same codpro,codcat, obtaining ONLY the row with MIN () field datfin

Field datfin is a date..

Ex. codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 01/01/2010

codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 10/07/2014

I'd like to read both records but in SELECT obtain only the record with datfin MIN (01-10-2010)

I did the query but i was not able to do nothing of good. I obtain all times both records...

SELECT catalog.codpro AS CodProdotto,
catalog.codcat AS CodiceCatalogo,
MIN(catalog.datfin)

FROM pub.catalog

WHERE catalog.codcat = 'MK'

GROUP BY catalog.codpro,catalog.codcat ,catalog.datfin

View 2 Replies View Related

T-SQL (SS2K8) :: Returning Multiple TOP Records?

Jul 10, 2014

Here is my setup: I have the following tables -

tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account

I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.

People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.

So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.

But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?

Straight TOP by itself won't do it.
GROUP BY by itself won't do it.

View 9 Replies View Related

T-SQL (SS2K8) :: Partition And Order Records

Oct 8, 2014

I have the below requirement:

1. Group records according to docno column.
2. Records will sort in desc order. (According to date1 column)
3. In date1 column if more than one date is same than we ll consider the date2 column.
EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.
4. Number column is the expected output column.

docnodate1 date2 Number
d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1
d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2
d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3
d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: Compare With Previous Records

Oct 20, 2014

I am having a table which contains data of students like:

StudentID,StudentName,Term,RESult.

Sample data :

StudentID,StudentName,Term,RESult.
1,ABC,Term1,Pass
1,ABC,Term2,Fail
1,ABC,Term3,Pass
1,ABC,Term4,Pass
1,ABC,Term5,Pass

Now i want to compare Result and dislay prevterm where student fail:

Now my output would be as: Now I want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:

studentID PrevFailTerm, CurrentTerm
1,Term2,Term5

View 1 Replies View Related

T-SQL (SS2K8) :: TOP N Records Comma Separated

Feb 14, 2015

Below is my sample data and query

declare @Sample table (ID int, message varchar(1000))
insert into @Sample(ID,message)
select 1,'Testing 1' union all
select 1,'Testing 2' union all
select 1,'Testing 3' union all

[Code] ....

I need to get top three values has to be comma separated. for example id 1 has 5 rows message which comma separated. Instead i need to consider top three message group by Id

expected result :

IDmessage
1Testing 1, Testing 2, Testing 3
2Testing 6, Testing 7, Testing 8
3Testing 11, Testing 12, Testing 13

View 3 Replies View Related

Query Returns Different Values?

Dec 10, 2012

I have written sql query

select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +

[Code].....

I am getting different amount 984000.0000 and quantity 9.

View 1 Replies View Related

T-SQL (SS2K8) :: Get Delta Records Between 2 Tables With Same Structure

Dec 30, 2011

I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.

Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A

id name address meter_flag end_date

23 john 1201 salt lake dr no 2011-12-28

24 tom 1222 gibson ln yes 2011-12-16

25 alex 1334 qayak dr no 2011-12-17

In Table B

23 john 1344 mc kinney st yes 2011-12-18

24 tom 1222 gibson ln yes 2011-12-16

56 gary 1335 pruitt rd no 2011-12-18

25 alex 1334 qayak dr no 2011-12-17

So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table

View 9 Replies View Related

T-SQL (SS2K8) :: Compare Tables With More Than 4.9 Million Records?

Mar 18, 2014

I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.

SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)

My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2

View 7 Replies View Related