I have a table with data like below
SampleDateTime Value
2015-08-08 01:00 AM 0.5
2015-08-08 02:00 AM Null
2015-08-08 03:00 AM Null
2015-08-08 04:00 AM Null
[code]....
Using subquery I thought of a couple of ways but that didn't work.
I'm guessing this is a fairly straight forward need, but want to make sure I am using the correct set of tasks:
In the dataflow, some values I need to carry forward from the previous row, such as a balance that I need to carry forward for the current customer record. This is similar to a running total, only I am not summing anything, but just carrying over from the previous records value (assuming dataset is sorted correctly, first by customer #, then by date).
Do I need the Dervied Column transform, and use a variable to store the previous value, or is there another transform that would be better suited?
Thanks
Kory
I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code
date product_code
01/01/14 | BIC01 |
03/01/14 | |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | |
03/01/14 | CASH |
I though of using the LAG function but the table does not have any specific order.So after running the select, the table should look like:
date product_code
01/01/14 | BIC01 |
03/01/14 | BIC01 |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | CASH |
03/01/14 | CASH |
Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:
ONETWO
a1
2
3
b1
2
3
Would want:
ONETWO
a1
a2
a3
b1
b2
b3
Thanks for your time:
Background: After Insert Trigger runs a sproc that inserts values into another table IF items on the form were
populated. THose all work great, but last scenario wont work: Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL:
IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ...
from inserted i
WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...)
BEGIN
Insert into dbo.Items2Fix ...
From inserted i
END
How to get First day of previous month and last day of previous month(From getdate()) using SQL..?
View 13 Replies View RelatedI am having table like below,
Period Pointvaluemonth area
NULLNULLNULLApril tn
NULLNULLNULLMaytn
NULLNULLNULLJunetn
NULLNULLNULLJulytn
NULLNULLNULLAugusttn
201109 100 10Septembertn
[Code] ....
Then i want to copy september value for october and november and Jan value for feb & mar like next null values should get replaced with previous not null value ...
My desired output is ,
Expected Output is…..
Period Pointvaluemonth area
NULLNULLNULLApril tn
NULLNULLNULLMay tn
NULLNULLNULLJune tn
NULLNULLNULLJuly tn
NULLNULLNULLAugust tn
201109 100 10September tn
[Code] ....
I have one table :
file _target
which has below records..
file_target_ID is identity column which will repeat per files_ID
Now, i just shown Target log for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date column
Like this ..
Target Date New Column
2015-09-09 00:00:00.000 2015-09-16 00:00:00.000
2015-09-16 00:00:00.000 2015-09-25 00:00:00.000
2015-09-25 00:00:00.000 New Target date after 25-9-2015
how to do this in SQL Server ??
using Cross Apply ?? Row_Number() ??
How I can setup the first of November of the previous year dynamically? So running now it should be 01-11-2014 but if I run in 2th of January 2016 it should be 01-11-2015.
View 17 Replies View RelatedI need to return the previous row value if it is negative in current row. For example, in the below table for ID=7 i need the value 1305(ID=4) since 6,5 are negative values.
Existing
values
ID
Input
ID
Input
[code]...
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case
when col>'20150901' then 'NULL'
else col
end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
Below is the resultset I got using the following SQL statement
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1
Resultset:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL
[Code] ....
Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18
[Code] ....
I am trying to pick up the customers invoiced twice or more within a month. In the case below Pepsi Cola and Jack Daniel were invoiced twice in October. The query need to pickup the previous month, se being in October I need to pick up the invoice of September.
create table #forum (Customer varchar(20),Invoiced date)
insert into #forum values ('Pepsi Cola','2015-09-01') ,('Pepsi Cola','2015-09-06') ,
('Pepsi Cola','2015-10-01') ,('Pepsi Cola','2015-10-02') ,('Pepsi Cola','2015-11-01') ,
('Ferrarelle','2015-09-01') ,('Ferrarelle','2015-10-01') ,('Ferrarelle','2015-11-16') ,('Ferrarelle','2015-11-01') ,
('Jack Daniel','2015-09-01') ,('Jack Daniel','2015-09-04') ,('Jack Daniel','2015-09-06') ,('Jack Daniel','2015-09-30') ,
('Jack Daniel','2015-10-01') ,('Jack Daniel','2015-10-18') ,('Jack Daniel','2015-11-01') ,
('Bud','2015-09-01') ,('Bud','2015-10-01') ,('Bud','2015-11-01')
select * from #forum
I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1
We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID
My tables look like
Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)
Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)
select * from @tab1
1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only
2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column
3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column
My expected result set should be like ....
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date
Price Status
1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore
3 11/12/2015 100 High Value
create table testmaster
(
ID int,
Threshold int
)
create table testdetail
(
ID int,
Date varchar(20),
Price float
)
[Code] ...
I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?
select id, list_date
from tableA
where list_date > DATEADD(year,-1,GETDATE())
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated.
2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I'm on sql server 2008.
is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.
I have a table of events like:
User, TimeStamp, etc...
I want to identify the row which is the event number 50 in the past 24 hours.
does the window functions can do this? and how?
the ROW PRECEDING etc... appear to not have enough feature to support time related function.
Stream-insight is able to create this type of query, but I have to do it directly in SQL.
Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
Copy out all data from a DB table into/across delimited text file(s) ensuring that each text file size is no more than 3MB.Have created a SSIS solution where it achieves this requirement ..well sort of achieves the requirement ... Here it what the current solution (sparing the minute details) does in a nutshell & Problems with it:
1) Created a function (Script below) which finds the
maximum row size in bytes in a given DB table & uses it to calculate
how many rows can be copied out into a text file without exceeding 3MB size limit.
For instance: A DB table selected had 788 rows in total and this function for this particular table returned a value of 181 rows { select [dbo].[udf_GetRowPartitionNumber](‘<TableName>’)as #ofRowstoPartitionTableby --181} meaning in order to not exceed the requirement of 3MB per text file, we had to copy all the data from DB table across (create) 5 text files {Select
CEILING(788
[code]....
I am using sql server 2012. suppose i have a table called cte which contains id and name columns . in name column there are null value . i want to display top row value instead of null value as like attached image. Here is query :
;with CTE As (
Select 1 as Id , 'Advance' as Name
union all
Select 2 as Id , NULL as Name
union all
Select 3 as Id , NULL as Name
[Code] ...
Expected Result :
I want to write normal select Query. i am not interest to using loop or cursor.
When building stored procs, I typically have several nullable parameters. Then in the where clause I will either do this
WHERE (@MyParam IS NULL OR MyColumn = @MyParam)
or this:
WHERE MyColumn = COALESCE(@MyParam, MyColumn)
Lately, I've noticed that either of these imposes a performance penalty. In other words, if I change the first one to
WHERE MyColumn = @MyParam
without the IS NULL, it is noticeably faster. If I only have one or two nullable parameters, I can separate the logic using IF/ELSE, but it becomes huge if I have a bunch of nullable params.I've looked at the execution plans.
I am using Visual Web Developer Express 2005 and SQL Server Express 2005.
I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine.
I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed.
How can I check for null values and skip a block of code within my Transact Sql trigger.
Thanks.....
DECLARE @I1 VARCHAR(5),
@I2 VARCHAR(5)
;
WITH cte
AS (SELECT 1 AS i,
'val1' AS j UNION ALL
SELECT 2,
'val2')
[code]....
Why @i1 is null but not @i2 ? I'm trying to assign values after grouping by the column.
i want to display the max Date in place of NULL when Indicator is "1"
INPUT
Emp Num
Date
Indicator
1
Null
1
[code]....
I have column X:
X
-------
1
NULL
4
NULL
2
3
NULLI need to sort this column to get this output: X
-----
1
NULL
2
NULL
3
NULL
4
NULL
What i need to do?
I have got this in my query,
WHERE (((tblVehicles.Veh_Parts_Date)>=DateAdd("m",-12,Date()))
How do I say IIF Null return values anyway?
I have written one query like this
select staffid,staffname,deptname
From staff s join dept d on s.deptid=d.deptid
This query we have no results
I need this results
staffid staffname deptname
null null null
I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.
What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.
This is an example of the table:
UpdateColumn
Old Value
New Value
ReceivedDate
7/8/2015 5:00:00 AM
ReceivedDate
7/8/2015 12:00:00 AM
7/9/2015 5:00:00 AM
ReceivedDate
7/9/2015 12:00:00 AM
So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying
This is the query:
CASE UpdateColumn
... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
I tried adding
CASE UpdateColumn
...
WHEN 'ReceivedDate' IS NOT NULL
THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
But it did not like the "IS NOT NULL".
Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.I have tried Where Exists (select FileName From Tbl where
Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where
Prod_Id != @var) In the Select Statement.
I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.
SELECT c.juris_id, b.jrnl_mo_yr
FROM a_trueup a,
#t_mths b, r_rj c
WHERE a.rlzd_mo_yr
=* b.jrnl_mo_yr
AND
a.juris_id
=* c.juris_id
[code]....
I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..
I have a table RequestUtility with 3 columns Vendor, name, system. I want to call this table from BizTalk so that I send multiple vendors and will get corresponding Vendor, name and system for that. As I need to send multiple vendors at the same time I have created a User-Defined Table type VendorNames with a single column Names. and have written a SP :
ALTER PROCEDURE [dbo].[GetName]
-- Add the parameters for the stored procedure here
@vendorN VendorNames ReadOnly
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Select statements for procedure here
SELECT * from dbo.RequestUtility where Vendor IN (SELECT Names from @vendorN)
This is working fine when the value for vendor is present in the table. But if the value is not present it is not returning any thing. But, my requirement is that for example vendor is Dummy which is not present in the table so it should return
Vendor Name System
Dummy NULL NULL
But currently it is not returning anything.