SQL Server 2008 :: Summarizing Transaction Data Removing Nulls?
Oct 27, 2015
I have two tables one list changes of hospital ward and one lists changes of consultant doctor. These can change independently ie a ward change can occur without a consultant change and vice versa. I want to summarise these changes to give the status at each date_serial value.
create table #temp_ward_stay(date_serial bigint,ward_id varchar(10))
;
insert into #temp_ward_stay
values
(201501021200,'W1'),(201501031201,'W2'),(201501091200,'W3'),(201501161200,'W4'),(201501161201,'W5')(201501271200,'W3'),(201502101200,'W5')
;
create table #temp_consultant_episode(
date_serial bigint,
consultant_id varchar(10))
;
insert into #temp_consultant_episode values
(201501021200,'C1'),(201501031200,'C2'),(201501031201,'C3'),(201501091201,'C4'),
(201501121200,'C3'),(201501301200,'C6'),(201502111200,'C6'),(201502111201,'C7')
;
If I use:
select date_serial,consultant_id,null as ward_id from #temp_consultant_episode
union
select date_serial,null as consultant_id,ward_id from #temp_ward_stay
I get:
date_serial ward_id consultant_id
-------------------- ---------- -------------
201501021200 NULL C1
201501021200 W1 NULL
201501031200 NULL C2
201501031201 NULL C3
201501031201 W2 NULL
201501091200 W3 NULL
201501091201 NULL C4
201501121200 NULL C3
201501161200 W4 NULL
[code].....
whereas what I actually want is:
date_serialward_idconsultant_id
201501021200W1C1
201501031200W1C2
201501031201W2C3
201501091200W3C3
201501091201W3C4
201501121200W3C3
201501161200W4C3
201501161201W5C3
[code].....
I can see how I could remove the nulls where the date_serial values are the same:
select ce.date_serial,ward_id,consultant_id from #temp_consultant_episode ce
join #temp_ward_stay ws
on ce.date_serial=ws.date_serial
but I can't see how to move forward from here.
View 9 Replies
ADVERTISEMENT
Aug 4, 2006
Hi,
What sql would I use to remove all the nulls in a table?
thanks!
View 11 Replies
View Related
Jan 4, 2010
I'm a developer working on PHP - MSSQL 2008 combined platform. While running a procedure from a webpage I'm encountering the error as below.
My Query:
DECLARE @ReturnValue INT
EXEC @ReturnValue = S_AccountsBatchActivation @FirstName='abc', @LastName='abc', @Address='abc', @City='abc', @State='abc', @Country='India', @Phone='1', @BatchId='6502'
SELECT ReturnValue=@ReturnValue
Error
Cannot insert the value NULL into column 'TariffId', table 'tempdb.dbo.#tbl
column does not allow nulls. INSERT fails.
Though the same query when run from SSMS runs fine:
Execute S_AccountsBatchActivation
@FirstName='abc',
@LastName='abc',
@Address='abc',
@City='abc',
@State='abc',
@Country='India',
@Phone='11',
@BatchId=6502
[code]...
On my end I've tried out with ANSI_NULL AND ANSI_NULLS settings being both ON and OFF but it didn't worked.
View 9 Replies
View Related
Apr 22, 2014
I have a table named item that looks like this:
PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014
And a second table named orders that looks like this:
OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL
View 4 Replies
View Related
Nov 2, 2015
If a column is set to allow nulls I know that a constraint can be used to supply a default (i.e. GetDate() ) when no value is provided but what about when an explicit NULL is provided in an INSERT or UPDATE statement?Is there any way other then an AFTER trigger to substitute a value for an explicitly provided NULL? In other words, assuming that dtAsof is a NULL enabled column, is there any way to over ride what the following will do to MYTABLE:
INSERT MYTABLE(sCol1, sCol2, sCol3, dtAsOf)
SELECT 'a', 'b', 'c', NULL
If there's no way to do this in SQL Server 2008R2 then what about later versions of SQL Server? Do any more recent versions have a way to deal with this? We have a third party app that uses a SQL Server back end and many of the tables have columns for storing audit like data such as date/time but many are left to NULL values and I'd really like to fix that in as passive a way as possible so as to not break the app that uses the database. I know a constraint with a default can be sued to over ride a null but not when a null is explicitly provided.
View 1 Replies
View Related
May 26, 2008
Hi,
I'm new to SQL and I will appreciate your help, my knowledge only goes so far and I think this task is above my skills. I'm trying to get information form a table in a certain format that allows me to summarize data on an hourly basis but I think I need need to use DATEPART.
The table looks like this:
Group Date_changed Object Proc Idx Value Offset
295 2008-05-19 00:05:00.000OBJ_1 PRC11020008544
295 2008-05-19 00:05:00.000OBJ_1 PRC11130006860
295 2008-05-19 00:05:00.000OBJ_1 PRC11240001372
295 2008-05-19 00:05:00.000OBJ_2 PRC11010002058
295 2008-05-19 00:05:00.000OBJ_2 PRC11120002744
295 2008-05-19 00:05:00.000OBJ_2 PRC11230003430
295 2008-05-19 00:05:00.000OBJ_3 PRC11070004116
295 2008-05-19 00:05:00.000OBJ_3 PRC111 60004102
295 2008-05-19 00:05:00.000OBJ_3 PRC112 50004802
295 2008-05-19 00:10:00.000OBJ_1 PRC11025004602
295 2008-05-19 00:10:00.000OBJ_1 PRC11135004502
295 2008-05-19 00:10:00.000OBJ_1 PRC11245005481
295 2008-05-19 00:10:00.000OBJ_2 PRC110 12005482
295 2008-05-19 00:10:00.000OBJ_2 PRC111 22005483
295 2008-05-19 00:10:00.000OBJ_2 PRC11232005484
295 2008-05-19 00:10:00.000OBJ_3 PRC11074506174
295 2008-05-19 00:10:00.000OBJ_3 PRC111 64506175
295 2008-05-19 00:10:00.000OBJ_3 PRC112 54506176
...
...
...
it contains data on 5 minutes intervals for each object and index.
I'm trying to summarize the data by Idx, I used this query:
select datepart(hour,date_changed) as Hour, Object,avg(Value) as Avg_Idx_10
from table1
where group = 295 and idx = 10 and Object = 'OBJ_1'
and datetime_changed < '2008-05-20'
group by datepart(hour,datetime_changed), Object
Hour Object Avg_Idx_10
0 OBJ_16346
1 OBJ_15039
2 OBJ_14533
3 OBJ_15102
4 OBJ_19019
5 OBJ_123511
6 OBJ_152575
...
...
...
And I get the information I need but each query shows only the data for 1 Idx, what I was wondering is if I can create a query that will allow me to summarize data on average Idx for all the idx in just 1 row yo obtain an output like this:
Hour Object Idx_10_Avg Idx_11_Avg Idx_12_Avg
0 OBJ_1 7500 3200 9500
0 OBJ_2 8400 7600 5800
0 OBJ_3 4700 6500 3400
...
...
...
Again, I'll appreciate any help, thanks in advance.
View 8 Replies
View Related
Nov 2, 2007
Hello,
I'm not a very strong SQL coder. I am having a problem regarding summarizing some data. I am hoping somebody can provide me with some direction.
I have two tables. One table represents a ticket: Ticket (ID, DateSold). Another table represents the status of a ticket: TicketStatus (ID, TicketID, StatusID) I need to create a query that can tell me how many tickets were sold in each month. But then, I need to be able to break that monthly ticket total down by StatusID. I have the following SQL Statement:
SELECT
Count(1) as 'TicketCount',
Month(DateSold) as 'SellMonth'
FROM
Ticket t
GROUP BY
Month(DateSold)
Which works up to the first part of the problem. However, how do I get the number sold per each status? Essentially, I would like my result set to look like the following (pipe-delimited):
TicketCount | SellMonth | Status1Total | Status2Total
5 | 1 | 2 | 3
9 | 2 | 8 | 1
7 | 3 | 3 | 4
Can somebody please help? Thank you!
View 7 Replies
View Related
Aug 25, 2015
I want to remove the nulls from the result set so the result is 1 line. Code and results are below:
SELECT
CustomerNumber,
(case when yearseq = 2012 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)
+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2012',
(case when yearseq = 2013 then
[Code] ....
What can I do to my code to remove the Nulls to the entire result is just 1 line?
View 2 Replies
View Related
Mar 7, 2008
Hi there,
I'm faced with a bit of an issue within SSIS and I was wondering if anyone could help. I have data that looks like the following:
TABLE 1:::
CustomerID StaffID Percentage
A123 1234 1
A124 1245 1
A125 1234 0.7
A125 1443 0.3
B117 6653 1
B154 8899 1
C776 1443 0.2
C776 1337 0.6
C776 1245 0.2
I need to be able to summarize the data above and build up a string that should look like the following:
TABLE 2:::
CustomerID StringID
A123 1234||1
A124 1245||1
A125 1234||0.7||1443||0.3
B117 6653||1
B154 8899||1
C776 1443||0.2||1337||0.6||1245||0.2
As you can see in Table 1, if a CustomerID has only one StaffID associated to it, the "Percentage" Column's value is 1. In the cases where there is more than one StaffID associated to a particular CustomerID, the percentage column get's split up among the StaffIDs, however the sum of this split can never exceed 1 (100%) per CustomerID.
I need to know how to find the cases where there is more than one StaffID associated to a CustomerID, group the CustomerIDs, and build a string made up with the StaffID and Percentages as shown in Table 2.
Any help would be greatly appreciated, as I've struggled with this one for a while and have hit a brick wall.
Ciao,
Colin
View 6 Replies
View Related
Jul 29, 2015
I've look at several different methods for removing leading zero's from a column but I need to remove trailing data from a VARCHAR column. For some reason, the old database saved the time along side the date in my client's app.
For example:
The old database format "2015-07-28 00:00:00"
I need the data in this column in the new database to only be the date "2015-07-28", there are alot of rows with this issue.
Is there a query I can run to remove the 00-00-00 from all of the rows? Some of the fields actually have a time in there like this: 2015-07-28 12:15:35, with this one, I don't think it's going to be easy but if I could at least remove the 00-00-00 from all the rows that have it, that would be a good start.
View 9 Replies
View Related
Nov 20, 2015
I have a table (edw_sbx_bt.jjenkins_OVERAGE2) of 40,000 MA_NUM.
I want to find occurrences of those specific MA_NUM in another table (edw_ar_fl.ARTBASE) with specific attributes (CONTRACT_NUM = '593' and TRANSACT_STATUS_CD = 'D').
When I run the query below it returns 4 million rows. How can I write a query that will return the 40,000 MA_NUM (in edw_sbx_bt.jjenkins_OVERAGE2) with the sum total of MA_PAID_AMT associated with each?
SELECT edw_ar_fl.ARTBASE.MA_NUM,
edw_ar_fl.ARTBASE.MA_PAID_AMT
FROM edw_ar_fl.ARTBASE
JOIN edw_sbx_bt.jjenkins_OVERAGE2
ON edw_ar_fl.ARTBASE.MA_NUM = edw_sbx_bt.jjenkins_OVERAGE2.MA_NUM
WHERE edw_ar_fl.ARTBASE.CONTRACT_NUM = '593'
AND edw_ar_fl.ARTBASE.TRANSACT_STATUS_CD = '
View 4 Replies
View Related
Oct 7, 2015
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
View 0 Replies
View Related
Apr 7, 1999
I am trying to remove a transaction log that has grown too large for its databse and server. I have truncated the log and have run dbcc shrinkfile with the EmptyFile Option, but when I try to use alter database remove file and am I told that the object is not empty and cannot be removed. The server is in dbo use only.
What am I missing?
View 1 Replies
View Related
Feb 5, 2015
I received alerts from a couple of the production servers last night stating that the log file is running out of space. So, I took some log backups and shrunk those files. However, I would like to find out what made the log grow like that. After all, I have t-log backups scheduled every 30 minutes.Is there a way I can find out the reason behind the log growth?
View 9 Replies
View Related
Feb 17, 2015
We had some SAN issues and we dont have Transaction Log files for some databases.. The drive which was holding this Tlog files were missing.. How to bring back databases.
View 1 Replies
View Related
May 4, 2015
I am confused about save transaction in the below scenario :
begin transaction
save transaction t1
delete from #t1
save transaction t2
begin try
delete from #t2
[Code] ....
If there is error after delete #t2 , transaction t1 is rolled back. But i am not able to understand why i am getting error in the statement 'rollback transaction t2' . I am getting error as 'Cannot roll back t2. No transaction or savepoint of that name was found.'. but save point t2 is mentioned in the code.
View 3 Replies
View Related
Jul 1, 2015
Currently, we are on SQL2008R2 EE SP1 CU13 across the board.
We are planning on upgrading the primary SQL server to SP3.
Question: Will transactional replication continue to work properly even if the Publisher is upgraded to SP3 but the subscribers remain at SP1 CU13 ?
View 2 Replies
View Related
Jan 10, 2012
I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:
use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)
I get the following message:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.
I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.
I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.
I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.
View 9 Replies
View Related
Jun 19, 2015
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
View 9 Replies
View Related
Jul 17, 2015
I am altering a table ( changing the data type to varchar (8000) from nvarchar (1500) ) with 352929 rows. I get the transaction log full error.
The database is in FULL RECOVERY model. I changed the recovery model to SIMPLE and performed the alter but I still run into error.
View 3 Replies
View Related
May 8, 2015
i would like to know it's possible to find all transaction(insert, delete,update) on a database for a day. if yes what can i do.
View 2 Replies
View Related
Jun 5, 2015
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
View 2 Replies
View Related
Mar 11, 2015
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml
[code]...
View 2 Replies
View Related
Nov 21, 2013
We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? tell me how exactly it releases the space after truncating a 120GB table?
View 8 Replies
View Related
Apr 17, 2015
LOCALID - POSTCODE - GPCODE
PTO1395164 - DN34 1AB - G9999981
PTO1395164 - DN34 1AB - G9999981
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - TZ14 2AX - G8909058
PTO1395164 - TZ14 2AX - G8909058
The sample data above shows 1 customer with multiple episodes (different attend dates – not important here), during the course of these attendances they moved home and moved GP practice.
Is there a simple way in Access to show a summary of this eh PTO1395164 = 2 postcodes, 2 GP’s
THe ultimate aim would be to identify where a customer has changed postcode or GP within a selected timeframe and disregard the rest.
View 4 Replies
View Related
Sep 4, 2015
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
View 4 Replies
View Related
May 16, 2006
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
View 4 Replies
View Related
Dec 1, 2014
I have a table called "alert" and in this table, for each columns there are data as 1 , or 0 or NULL. how do I write a query to just give me data's with "1" in them not the NULLS or 0?
View 3 Replies
View Related
Mar 15, 2006
Hello..I was wondering if anyone out there could tell me how they deal withNULL values in a data warehouse? I am looking to implement a warehousein SQL 2005 and have some fields which will have NULL values and Iwould like some further ideas on how to deal with them. At my last jobin dealing with Oracle we were just going to leave the fields NULL, butin SQL how would you best recommend cleaning the data? I greatlyappreicate your help and look forward to your reponses.Thank you
View 2 Replies
View Related
Dec 5, 2006
Hi!
I have data in the following manner:
period course1 course2
4 NULL Eng
4 NULL Math
4 Phys NULL
4 Chem NULL
Is there any way I can show this data this way (the order is irrelevant - row1 can be joined with row3 or row4)?
period course1 course2
4 Phys Eng
4 Chem Math
4 Phys Eng
4 Chem Math
Basically, I want to be able to remove nulls.
Please let me know if any of you have come across such an issue.
Thanks so much!
-Parul
View 3 Replies
View Related
Oct 19, 2007
Hi,
With DTS, the default behavior is to import blanks ("") in a data file as nulls in the table.
However, in SSIS, the default behavior is to import blanks as blanks.
I need to ensure that the packages I'm migrating import the data in the exact same way. I am wondering what is the easiest way to convert blanks to nulls?
Right now I'm using a script to convert the blanks to nulls before they are written to the table, but just wondering if there's an easier way.
Thanks
View 30 Replies
View Related
Feb 17, 2008
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies
View Related
Feb 20, 2008
Hello,
I have a datareader and whenever the value that it is suppose to display in the Label is empty because there is no Data Entered Yet I gives me and error and I have the event set ONLOAD so the page with no data to disply does not load.
So the Ranking.text = a select statement i have, but when I dont have any data into the SQL database there is nothing to SELECT so the program gives of an error.
Can someone help me with this. Thank you.
Here is the code: Dim objReader As SqlDataReader
conn.Open()
objReader = comm.ExecuteReader()
objReader.Read()lblRanking.Text = objReader("Rating")
objReader.Close()
View 7 Replies
View Related