Adding Sum Function To Query To Sum 12 Records

Sep 23, 2014

I'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:

IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C

select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB
INTO #TMP1C
from Vantage.dbo.SBB_BASE (NOLOCK)
where PRIN_SBB in (6000,7500)

[Code] .....

Not entirely sure what I am doing wrong, or why the numbers are coming back so high.

View 1 Replies


ADVERTISEMENT

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 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

Why Would Adding Dbo. Cause My Function To Run Forever?

Jan 13, 2006

Lately I did a mass update on all our scripts and addeddbo. in front of all tables and other objects.There is a function that returns a table and the function isI think 300 lines. There are a lot of UNIONs, EXISTS,NOT EXISTS, etc...Anyhow there's a single place where if i add dbo. in frontof the table this function which is being called from an SP,when i run the SP from the QA or from the ASP applicationit runs forever. As soon as i remove the dbo. again it runsin 6-8 seconds. Here's the thing this same table reference existsa few lines below in the 2nd UNION for example andhaving a dbo. in front doesn't cause any problems.Even more confusing if i add Databasename.dbo.TableNameand run it again, i don't run into any problem.So it's almost like if i specify dbo. in front of the table, somehowSQL Server or our code is getting lost and searching for thistable in other databases?Has anyone run or seen such a problem?I am sure I can make changes to the code and end upwriting a different code but before I make changes I wouldlike to find out more about my mystery problem.I am running SQL 2000 SP4 and the same problem occuredon two different machines. Win 2000 Pro and Win 2000 Server.Any ideas, suggestions?Thank you

View 4 Replies View Related

Adding Records

Jul 8, 2005

HiHow do i simply add records to my Ms SQL database??Does anyone have an example?Best regards/Erik

View 1 Replies View Related

Adding Records

Apr 2, 2007

Hello guys,



I hope someone can help me!



I have a (probably simple) problem with compact edition, I can't seem to add records using VB 6.0. Here's some sample code I've been testing with:



Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset

Set pConn = New ADODB.Connection
Set pRS = New ADODB.Recordset

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C: est.sdf"
'pConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C: est.mdb"
pConn.Open

pConn.Execute "INSERT INTO tblActions ([Branch ID], [Computer ID], [User ID], [Program ID], [Type ID], ID, [Extra 1], [Extra 2]) " & _
"VALUES (1, 1, 1, 1, 1, 1, 'some text', 'more text')"

' Open the recordset
pRS.Open "tblActions", pConn, adOpenForwardOnly, adLockOptimistic

pRS.AddNew
pRS![Branch ID] = 1
pRS![Computer ID] = 1
pRS![User ID] = 1
pRS![Program ID] = 1
pRS![Type ID] = 1
pRS![ID] = 1
pRS![Extra 1] = 1
pRS![Extra 2] = 1
pRS.Update

pRS.Close
pConn.Close

Set pRS = Nothing
Set pConn = Nothing



I can add the records using an SQL statement, but when I try to open the recordset and add a record using the AddNew method it fails with the message:



The command contained one or more errors. [,,,,,]



But, if I connect to an Access database (which uses exactly the same tables, etc.) using the commented out connection string in the above sample, the code works fine. What am I doing wrong?



Many thanks in advance!

View 32 Replies View Related

Adding Records To SQL DB

Nov 24, 2006

Here's my dilema. I'm using VB.Net 2003 and SQL DB

I'm having a problen adding a record to a table in my SQL DB

My previous experience has been working with Access databases.

When I try to create an SQLDataAdapter using the wizard from the toolbox the wizard will only create the Select statement. it won't create the UPDATE, DELETE or INSERT statements. I'm not sure why, I even tried using the form wizard and it still wouldn't work...

My tables all have primary keys defined.

So I decided that I would try to create everything programatically.

What I need to know is how to create the INSERT statement and which statements to use to add the record to the table.

I know that when using the OLEDataAdapter with the Acces DB I would do something like the following code.



MyNewRow = MyDatSet.Tables("MyTable").NewRow
MyDataSet.Tables("MyTable").Rows.Add(MyNewRow)
MyDataAdapter.Update(MyDataSet, "MyTable")



I'm pretty sure that for an SQL DB it is different but I don't know how to do it.

Can anyone please help ????

I should also mention that all my textbox fields on my form are databound..



thanks

tattoo



View 1 Replies View Related

Adding Records To An SQL Table

May 7, 2007



I trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.

I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:

use [c:developesqlsqldatapsw.listener]

append from 'c:developesqldatalistener.txt' type sdf

go



and then I pressed F5. I get an error that says:



Msg 911, Level 16, State 1, Line 1

Could not locate entry in sysdatabases for database 'c:developesqlsqldatapsw.listener'. No entry found with that name. Make sure that the name is entered correctly.



I checked in the directory c:developesqlsqldata and the file psw.mdf is there and when I look in the psw database, the table listener is also there.



Could someone tell me what is going wrong? Also, how do I append the whole dbf table onto an SQL table. there's about 6000 records.



Thanks.

View 1 Replies View Related

Group By And Adding Records To Another Table

Mar 4, 2006

My Table

ID,Customer,Type,Date

records
1,XXX,AAA,Date
2,ZZZ,BBB,Date
3,QQQ,BBB,Date

I group them with the following query

Select Source,Count(*) from table where date=month(getdate()) group by Type order by 2 desc

the result looks like that

AAA,1
BBB,2

------------

Also there are another table for this results (Totals)
fields

Type,Quantity
--------------
AAA,45
CCC,76

(attention, there are no BBB record currently in this table)

I want that
the results of the first query goes to Totals table.

what I need like this

Type,Quantity
--------------
AAA,45 + AAA,1
CCC,76
BBB,2

I don't know how to do

if there is a LOOP solution in sql server , I would like to know

thanks in advance

View 6 Replies View Related

Adding Details To Multiple Records

Nov 29, 2007

Hello

I am trying to add a website address to all the records in a database that match a certain criteria. The statement that I am using is shown below, but surprise surprise, it's not working! I'm new to SQL so any help would me much appreciated! Thanks.

declare @ComNum int
set @ComNum = (select max(communication_number)+1 from communications)

insert into "communications"
(contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
values (NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);
select name
from organisations
where name ='Abc Limited'

So, I have not included a VALUE for CONTACT_NUMBER as I wish to update all records with the website details as per the INSERT statement where the NAME column in the ORGANISATIONS table is 'Abc Limited'. I know something is wrong but I can't quite work out what!

View 11 Replies View Related

Adding/Appending Records Frequently In SQL

Jan 24, 2008

Hey guys. I need a little help once again, here's the problem:

We are using an attendance system software that adds records in an Access 97 table whenever someone logs in or out of the room (Yes, it's old and I hate it, but it came with the hardware).

So we plan to create a program that lets admins check those login/logout data in an SQL Server 2005 database using a Visual Basic as the frontend.

Our only problem is, how do we transfer data from the Access 97 tables to our SQL tables *everytime* it gets updated? We don't need every detail the Access97 table has (it has IDNo, CardNo, Time, Location, plus other useless stuff). We just need to extract the IDNo(int) and Location(varchar) field(we'll just use getdate() for the Time since the update is done in realtime anyway).

So in a way, we are trying to find out a way to technically sync both tables, just like Database Mirroring style.

Any suggestions?

View 3 Replies View Related

Adding A Time Stamp To The Records

Jul 20, 2005

I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Adding Multi Records To An SQL Table

May 7, 2007

I'm trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.

I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:

use [c:developesqlsqldatapsw.listener]
append from 'c:developesqldatalistener.txt' type sdf
goand then I pressed F5. I get an error that says:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'c:developesqlsqldatapsw.listener'.No entry found with that name. Make sure that the name isentered correctly.
I checked in the directory c:developesqlsqldata and the filepsw.mdf is there and when I look in the psw database, the tablelistener is also there.
Could someone tell me what is going wrong? Also, how do Iappend the whole dbf table onto an SQL table. there's about6000 records.

Thanks.

View 2 Replies View Related

Adding Records In SQL Server Express 2005

Dec 6, 2005

When adding records containing a date field in SQL Server 2005 Express I get an error Input string was not in a correct format. Do datefields have to be converted when adding or editing into strings?

View 2 Replies View Related

Adding Records To SQL CE With Standard VS2008 Without Getting Identity Value

Apr 28, 2008

I'm using VS2008 (as shipped with SQL CE 3.5 as shipped).
I want to write a database app and started with a quite simple example:
One table "Master" has 2 columns: int MasterID, int Value (where the MasterID is the primary key and is an identity column.

For the GUI I used the typed dataset components (based on the namespace: System.Data.SqlServerCe) which were automatically generated by VS2008. I just dropped the table from the datasources view to a form and got a DataGridview, a Navigator and some DB components, quite fine so far.

When i run the app i can add new rows and the identity column is filled with -1, -2 ... as expected. When I hit the save button, the rows are saved to DB, but the values of the identity columns are not updated (the still have -1, -2..)

When I use the exact same example with SQL Express evrything works fine: the identity Column's values are updated automatically and have +1, +2, ...
Sure there is a statement "SELECT @@IDENTITY"... but I'm just using automated code so far, and i expect that code to work (automatically). Next problem: if i want to insert that "Select @@identity" stuff (I don't want to do that, but perhaps I have to?) where can I do this, I did not found a OnInsertedRow-Event?
This problem is part of a bigger one: I want to use Master/Detail, but inserting the Details fails because the inserting of the master rows didn't return the correct Indentity values, so the detail rows cannot be inserted (the foreign keys -1, -2... will raise an error).

thanks in advance, Bernd.

View 6 Replies View Related

Inflated Number Of Records Returned When Adding Bit Field Criteria

Jul 20, 2005

When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.

View 1 Replies View Related

Need Efficient Query To Partition Records By Type And Pull Top N Records From DB

Jan 18, 2008

I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.

1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.


;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC

Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?

Thanks in advance.

View 4 Replies View Related

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Function To UPDATE Multiple Records

Jan 7, 2015

I have a question, what kind of Query or function do I have to use to UPDATE multiple record that I just want to add a letter at the end of the existing OrderNum.

Before
ID NAME OrderNum
1 Pete WEB123
2 Paul WEB124
3 Sam WEB125
4 Tim WEB126

After
ID NAME OrderNum
1 Pete WEB123A
2 Paul WEB124A
3 Sam WEB125A
4 Tim WEB126A

View 3 Replies View Related

UPDATE RECORDS WITH AGGREGATE FUNCTION

Mar 2, 2007

Problem is very simple

I want to update sum of a field from another table to first table

TABLE ONE:
==========
ItemID
QtyInStock


Table TWO:
==========
BatchID
ItemID
Qty

I want to Update the QtyInStock of First Table with Sum(Batch.Qty)

here is the query i am writing but giving error

UPDATE ITEMS
SET
INSTOCKQTY=CASE WHEN QtyInBatch>1 THEN QTYINBATCH ELSE 0 END
FROM ITEMS, (
SELECT ITEMS.ITEMID, SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID
)

appericiating anyones help in advance




FAZEEL AMJAD
Systems Engineer
Crystal Technologies

View 4 Replies View Related

Simple Count Function - Show All Records For SSN In A Table

Jul 9, 2013

I want my query to list all SSNS that have more than one record in the table. I have this query:

Code:

SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1

It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.

For example

SSN NAME4 COUNT
123445555 WALTER - 4

I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.

View 6 Replies View Related

Dynamic Function To Return Number Of Records In Table

Aug 5, 2014

I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.

CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT

[Code] .....

View 1 Replies View Related

Dynamic SQL And NewID Function - Pulling Random Records

Jun 11, 2007

I'm trying to use the NEWID function in dynamic SQL and get an errormessage Incorrect syntax near the keyword 'ORDER'. Looks like I can'tdo an insert with an Order by clause.Here's the code:SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'execute sp_executesql @SQLStringMy goal is to get a random percentage of records.The full SP follows. In a nutshell - I pull a set of records fromFD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.I need to retain the set of all records that COULD be eligible forselection. Based on the count of those records, I calculate how manyneed to be pulled - and then need to mark those records as "chosen".I'd just as soon not use the TMP_UR_Randoms table - I went that routebecause I ran into trouble with a #Tmp table in the above SQL.Can anyone help with this? Thanks in advance.Full SQL:CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)--Review type will fill using Crystal Parameter (setting defaults)AS/* 6.06.2007UR Requirements:(1) Initial 4-6 month review: 15% of eligible admissions(eligible via days in program and not yet discharged) must be reviewed4-6 months after admission. This review will be done monthly -meaning we'll have a moving target of names (with overlaps) whichcould be pulled from each month. (Minimum 5 records)(2) Subsequent 6-12 month review: Out of those already reviewed(in #1), we must review 25% of them (minimum of 5 records)(3) Initial 6-12 month review: Exclude any included in 1 or 2 -review 25% of admissions in program from 6-12 months (minimum 5)*/DECLARE @CodeRevType intDECLARE @PriorRec int -- number of records already markedeligible (in case user hits button more than once on same day for sametype of review)DECLARE @CurrRec int --number of eligible admitsDECLARE @RequFiles intDECLARE @SQLString nvarchar(1000)DECLARE @RequFilesSt varchar(100)DECLARE @CodeRevTypeSt char(1)DECLARE @TodayNotime datetimeDECLARE @TodaySt varchar(10)--strip the time off todaySELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)--convert the review type to a codeSelect @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'then 3 END--FD__UR_Randoms always gets filled when this is run (unless it waspreviously run)--Check to see if the review was already pulled for this recordSELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNotime)If @PriorRec 0 GOTO ENDThis--************************************STEP A: Populate FD__UR_Randomstable with records that are candidates for review************************If @CodeRevType = 1BEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 119)AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randomswhere RecordChosen = 'T'))ENDIf @CodeRevType = 2--only want those that were selected in a batch 1 - in program 6-12months; selected for first reviewBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randomswhere SelectType = 1 AND RecordChosen= 'T'))ENDIf @CodeRevType = 3--only want those that were not in batch 1 or 2 - in program 6 to 12monthsBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randomswhere SelectType < 3 AND RecordChosen= 'T'))ENDSELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNoTime)--*************************************STEP B Pick the necessarypercentage **************************************--if code type = 1, 15% otherwise 25%If @CodeRevType = 1BEGINSELECT @RequFiles = (@CurrRec * .15)ENDELSEBEGINSELECT @RequFiles = (@CurrRec * .25)END--make sure we have at least 5If @RequFiles < 5BEGINSELECT @RequFiles = 5End--*************************************STEP C Randomly select thatmany files**************************************--convert all variables to stringsSELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'print @SQLStringexecute sp_executesql @SQLStringSELECT * FROM TMP_UR_Randoms/*--This select statement gives me what i want but I need to somehowmark these records and/or move this subset into the temp tableSelect Top @RequFilesFROM FD__UR_RandomsWHERE SelectType = @CodeRevType and SelectDate =Convert(varchar(10),GetDate(),101))ORDER BY NewID()*/ENDTHIS:GO

View 3 Replies View Related

Does SQL Have A Function That Return Null For Records Which Don't Exist In A FK Realation Ship?

Apr 4, 2007

Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
 
 
Thank you very much.
 

View 2 Replies View Related

Need To Create Custom Function For Enterting Automated Database Records

May 12, 2007

Hi All,I need help in creating a function in VB for my ASP.NET application where I want to add records to database on the first day of every month.I have got no idea about what I have to do for achieving this goal.Its basically for a customer based application where Interest will be paid into customers' account and I need to implement this for every customer on 1st day of every monthThe thing I am not sure about is how can I get the application to add a record for each customer on the first day of each month, i.e. how can I get the application to check that its 1st day of month and then the application adds records automatically for each customer based on my specified rules.If any of you could help me with this, I'll really appreciate it.Thank you.

View 5 Replies View Related

Error Editing Data Records In Gridview When Using Convert Function.

Jan 24, 2008

Hello, i currently have a gridview that is populated with data from a SQLServer datasource. I have put an output mask in the select statement, so the date and time attributes are displayed in the format i prefer them to be in. SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment];
 However when i click the 'edit' link for a record in the gridview, i am unable to edit the date/time attributes and when i click update to confirm any changes to the other attributes, the values in the date/time attributes are emptied. How can i solve this update problem. I'm guessing i need to configure my SQL UPDATE statement, but bit stuck how i do this. Please help!
Thanks,
James

View 9 Replies View Related

SQL Server 2012 :: Return Random Records In A Table-valued Function?

Dec 19, 2013

My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.

I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).

CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE

[Code] ....

But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:

Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.

I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...

The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)

but that's clearly not working. Is there a way to do this without resorting to a cursor?

View 9 Replies View Related

Automatically Adding Records To Child Table When Record Added To Parent Table

Aug 19, 2006

In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks

View 1 Replies View Related

Adding Value In A Query

Aug 25, 2007

I am trying to insert a value numeric + 1 in to db table but i get error when i do this
this is the code
Const SQL As String = "INSERT INTO [PageHits] ([DefaultPage]) VALUES (@defaultP)"Dim myCommand As New Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@DefaultP" + "1", DefaultP.Text.Trim())
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
The Error:  
Must declare the variable '@defaultP'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@defaultP'. 
 

View 1 Replies View Related

Adding In A Query

Sep 14, 2007

i want to add vales in a query
this is my scenario (how can i add the price column values and display as one record) ex. 222  17
table = table1
tep1  price (column)
222     5
222     2
222     10
 

View 2 Replies View Related

Adding Numbers While Doing Query

Mar 28, 1999

I used this with an access DB and would like to re-write it to work with
sql.

sql = "SELECT funds.itemdisc, Sum(IIf(funds.status='Credit'"
sql = sql & ",CDbl(funds.fundamount),0,)) AS income, Sum(IIf funds.status='Debt'"

sql = sql & ",-1*CDbl(funds.fundamount),0)) AS outgo"
sql = sql & " FROM funds "
sql = sql & "GROUP BY funds.itemdisc"

What I am doing here is collecting the columns and using status as
credit or debt, if its debt I am subtracting from outgo if it is credit
I am adding to credit. This also will only select records so that
there are not any double listings. Here is an example.

DB
desc status fundamount
bill debt 10.00
bill debt 15.00
in credit 10.00
bills debt 5.00
in credit 5.00
paper debt 5.00


When I do the query I should have this

desc fundamount
bill -25.00
bills - 5.00
paper - 5.00
in 15.00

Something like that...

What do you think?
Thanks in advance.

View 1 Replies View Related







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