Entering Dates In Blank Rows
May 9, 2007
I'm hoping that someone can help. This is my first time posting and fortunately I can normally find what I need but this time I am stumped.
I have a query that produces a date range. The problem is I want to insert or at lease display the dates between even if they don't show up in table. Here's the problem.
My intial query is
select date_of_call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
order by date_of_call
Results:
2001-09-03 00:00:00.000
2001-09-07 00:00:00.000
2001-09-10 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-14 00:00:00.000
When I do a group by and count it looks like this:
select date_of_call, count(date_of_call) as Count_Date_of_Call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
group by date_of_call
order by date_of_call
Date_Of_Call, Count_Date_of_Call
2001-09-03 00:00:00.0001
2001-09-07 00:00:00.0001
2001-09-10 00:00:00.0001
2001-09-13 00:00:00.0005
2001-09-14 00:00:00.0001
If you notice out of 15 days it only shows 5 days. I am wondering how I can insert the days missing and insert either null or 0 values in the count column so it would look something like this:
Date_Of_Call, Count_Date_of_Call
2001-09-01 00:00:00.0000
2001-09-02 00:00:00.0000
2001-09-03 00:00:00.0001
2001-09-04 00:00:00.0000
2001-09-05 00:00:00.0000
2001-09-06 00:00:00.0000
2001-09-07 00:00:00.0001
2001-09-08 00:00:00.0000
2001-09-09 00:00:00.0000
2001-09-10 00:00:00.0001
2001-09-11 00:00:00.0000
2001-09-12 00:00:00.0000
2001-09-13 00:00:00.0005
2001-09-14 00:00:00.0001
2001-09-15 00:00:00.0000
Any help would be much appreciates.
View 5 Replies
ADVERTISEMENT
May 1, 2005
How can I enter NULL manually in one of the rows in Sql Server database. Those rows does not contain any data.
View 3 Replies
View Related
Jan 31, 2007
How do I get a Report Table to supress blank rows from displaying ?
In Crystal Reports this option is available under Format Detail Section, supress blank section chekckbox.
View 9 Replies
View Related
Mar 13, 2007
Hi All,
Few days back we faced a problem when we were doing an Export to Excel of a report which was using sub-reports. After going thro. the knowledge base articles we came to know that SSRS currently doesn't suppot this option, an alternate is to use use List instead of tables, so we used List and did all the formatting with the list and the data was displayed correctly and it was Exporting to Excel also properly. But now we found out that after doing an Export to Excel, the Excel File is leaving blank rows between the data (i.e.,) If the report consists of two rows, then it displays the first row in the 10th row of excel and the second row in the 12th row of excel. The 11th row is blank and it appears as a small blank row between 10 and 12. The actual problem because of this is we are not able to do Auto filter in Excel, because Excel by default considers the values only until it encounters a blank row when we are doing auto filter. So is there a way to avoid this blank row while exporting to Excel, we have tried to remove the borders and all other stuffs but nothing seems to work. Have anyone encountered the same problem or is there any work around for this problem. Thanks in advance.
View 5 Replies
View Related
Feb 12, 2014
I got two columns DEATHDATE and STATUS
I have to update STATUS Column as INVALID when data in DEATHDATE column as follows
Example of INAVLID DEATH DATE : 1221, 12xt88,12#10, 1, ABC, #@ , etc
Do nothing when the DEATHDATE is VALID like
Examples of Valid/accepatable DEATHDATE: 09122012, 091212 ,******,********, 000000,00000000
How can i achieve this.
View 1 Replies
View Related
Apr 17, 2012
I have a table for example like following
DECLARE @tmpTable table
(
name varchar(10),
address1 varchar(10),
phnno varchar(10),
mobno varchar(10)
)
INSERT INTO @tmpTable(name,address1,phnno,mobno)
[Code] ....
I want to remove all empty rows like row 1,2 and 3 in the above example.
I can't check all columns null values as there are many columns in my actual table.
View 6 Replies
View Related
Jul 25, 2006
W2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)
I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.
I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...
One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.
The REPLACE works except that it fills in repeated data for all the blank rows.
Example:
Incoming data is:
1 Smith,Jane^Jones,Jane
2 Brown,John
3
4 Adams,James^Adams,Jim
5
6 White,Debra
Data inserted into the table is:
1 Smith,Jane; Jones,Jane
2 Brown,John
3 Brown,John
4 Adams,James; Adams,Jim
5 Adams,James; Adams,Jim
6 White,Debra
I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).
Any suggestions on how to fix this would be most appreciated!
Thank you.
View 5 Replies
View Related
Jul 30, 2015
I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.
View 7 Replies
View Related
Oct 31, 2013
I have two tables a stock table and a price table and I want to select the correct price for the Stock Date.
Problem is sometimes there is a promotion date in the price table between the live dates so the rows double up. Below is my sql but I get over 17,000 rows when it should be about 16,964.
I will post up the table and data.
SELECT a.[Company]
,a.[ProductID]
,a.[ColourSize]
,a.[StockDate]
,a.[Quantity]
,b.[Ticket Price]
FROM[Stock_Ledger] a
LEFT OUTER JOIN [Product_Prices] b
ON a.[Company] = b.[Company]
AND a.[ProductID] = b.[ProductID]
AND a.[ColourSize] = b.[ColourSize]
AND a.[StockDate] BETWEEN b.[StartDate] AND b.[EndDate]
ORDER BY a.[StockDate],a.[ProductID],a.[ColourSize]
View 7 Replies
View Related
May 10, 2008
Hi,
I have a table that holds pay rate changes with a field for the rate start date and a field for the rate end date. When an employee gets given a new pay rate, the existing rate is given an end date and a new row is added with the rate start date being the day following the end date of the old pay rate.
I need to identify the staff who have had a rate change within the past month, therefore an end date on one row that is within one month of the current month, and a start date on another row that is one day after an end date on a separate row and within one month of the current month.
Is someone able to help me out please?
Thanks
View 8 Replies
View Related
Nov 27, 2007
This is tricky so please read it through
For displaying data on the report I am using the following query
SELECT ReferenceNumber, ActivityID, ActivityTimeStamp, ActivityType, ActivityPerformedBy FROM ActivityDetails
ORDER BY ReferenceNumber, ActivityID
The result set is
Issue Reference #
Activity ID
Activity Date/Time
Activity Type
100819
4521404
11/4/07 2:06 PM
INIT
100819
4521405
11/4/07 2:07 PM
LOG
100819
4521406
11/4/07 2:07 PM
LOG
100819
4521473
11/4/07 2:28 PM
TR
100819
4521501
11/4/07 2:33 PM
WIP
100819
4521839
11/4/07 3:25 PM
RE
100819
4521844
11/4/07 3:27 PM
RE_Method
100819
4522575
11/4/07 8:53 PM
CL
100820
4521412
11/4/07 2:10 PM
INIT
100820
4521419
11/4/07 2:13 PM
ATTACHTDOC
100820
4525856
11/5/07 2:49 PM
ATTACHTDOC
100820
4525859
11/5/07 2:49 PM
LOG
100820
4525869
11/5/07 2:49 PM
CL
100821
4521423
11/4/07 2:14 PM
INIT
100821
4521425
11/4/07 2:14 PM
LOG
100821
4521429
11/4/07 2:14 PM
TR
100821
4521432
11/4/07 2:14 PM
ACK
100821
4522219
11/4/07 4:58 PM
RE
100821
4522221
11/4/07 4:58 PM
RE_Method
100821
4522447
11/4/07 6:51 PM
CL
On the report I have used the grouped by clause on 'Issue Reference #'. I want one more column which would calculate the difference between two consecutive Activity Date/Time of the same reference #.
e.g. Time difference between 4521404 and 4521405, 4521405 and 4521406, 4521406 and 4521473 etc. Please note that the difference between 4521412 and 4522575 will NOT be calculated since they are from different Reference Numbers.
Thanks,
View 2 Replies
View Related
Sep 26, 2006
Hi all,
I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)
...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...
At the moment, I have the following query that I think is on the right track:
€¦
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
€¦
However, this gives me too much data:
€¦
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
€¦
So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I€™d like to do this in a single query.
Thanks for any help you have to offer,
Regards,
Stephen.
View 4 Replies
View Related
Feb 19, 2008
I am having a bit of a problem over here. I am trying to consolidate dates from multiple records into a time line that has no date overlap. I'll give you an example to make things clear.Let's say I have 3 data records:RowID BeginDate EndDate Price ($)----------------------------------------------------1 01/01/2008 01/10/2008 1.002 01/05/2008 01/15/2008 2.003 12/20/2007 02/01/2008 1.50 The result I would like to see should look like this: 12/20/2007 - 12/31/2007 the price was 1.5001/01/2008 - 01/10/2008 the price was 2.50 because row 1 and 3 overlap.01/05/2008 - 01/15/2008 the price was 3.50 because row 2 and 3 overlap.01/16/2008 - 02/01/2008 the price was 1.50 because of the row 3. Any idea on how I can automate generation of this data?I have a lot of code written for that but I can't get the result I want.I don't know if someone wants to see my code, I got around 500 lines of it.I would appreciate any help with this.Thanks!
View 3 Replies
View Related
Jan 16, 2007
I have been looking for the answer to this for a while, but probably haven't found the right place or query. I want to achieve the following:Table 1Table 2
DateShiftDateShiftData
---------------------------------------------------------------------------------
1/1/200711/1/20072Some data
1/1/200721/2/20073Some more data
1/1/20073
1/2/20071
1/1/20072
1/1/20073
and generate:
Table 3
DateShiftData
--------------------------------------------------------
1/1/20071NULL
1/1/20072Some data
1/1/20073NULL
1/2/20071NULL
1/1/20072NULL
1/1/20073Some more data
This way, the information can be displayed and show that some of the entries were not entered for the dates with NULL. Thanks for the help, -Syn
View 2 Replies
View Related
Jan 21, 2014
I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:
EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234
Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.
View 7 Replies
View Related
Apr 29, 2008
Hi,
I receive blanks for a column called value and i need to represent it as a blank or NA into a colum whose datatype is float in the datawarehouse.
how is this possible because in current schenario a blank is being converted to 0
which is not the right thing.
i would like to retain the blank in conversion from char to float ,
please explain me if it can be done or how to overcome this issue.
Thanks
View 1 Replies
View Related
Feb 18, 2015
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
View 9 Replies
View Related
Apr 8, 2015
I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.
Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000
View 2 Replies
View Related
Apr 21, 2015
I am trying to find an easy way to create multiple of just two date in a single sql statement.
E.G.
A statement using the parameters
@StartDate = '2015-01-01'
@EndDate = '2015-01-05'
Ends up with rows:
'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'
What would be the best way to do this ?
View 3 Replies
View Related
Dec 2, 2015
This question is extension from the topic Updating table Rows with overlapping dates:Â [URL] .....
I am actually having a table as following:
Table Name: PromotionList
Note that the NULL in endDate means no end date or infinite end date.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
Flag = 1, success merge row. Flag = 2, fail row
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.
Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.
Original table:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL
Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL
Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).
By taking the min date and max date of the same group, the following table should be achieve after the table is updated.
Final outcome:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL
View 2 Replies
View Related
Oct 15, 2007
hi
I am working on webforms i have to insert system date into sql database when a button is clicked.
I have have 3 fields on the webform and a submit button.
I have to insert the date along with the other fileds into the sql database
when I am trying to insert date using getdate() it is getting inserted but not into the same row as that of other fields.
Can you plzzz help me with the code for inserting system data into the database into the same row as that of other fields
myCommand2 = New SqlCommand("Insert into Items(Requestdate) values (getdate()) where Hospital= '" & DropDownList2.SelectedItem.Text & "' ", myConnection)
ra = myCommand2.ExecuteNonQuery()
myConnection.Close()
THE ABOVE CODE GIVES AN SYNTAX ERROR NEAR "WHERE"
please help
you can also post me ur replies on urs_forever_tanya2001@yahoo.co.in
Thanks bye
View 6 Replies
View Related
Dec 13, 2013
I am trying to write a query which user be able to enter data after getting message for example:
"Enter Value for Descriptionparam: "
here is my query:
selet * from tablename
where descriotion = @'%descriptionparam%'
View 2 Replies
View Related
Sep 4, 2007
How does one enter data into the tables of a new database in the first place? I'm using SQL Server Management Studio Express. I can name the columns, but that seems to be as far as I get. Thanks for any Help.
View 3 Replies
View Related
Apr 7, 2006
I've built a web app using VWDEE. I created my express database in the
app_data directory using VWDEE and everything worked really well.
Then I copied the site into my production directory (same computer) and
setup the site under IIS. Now I cannot connect to the database.
How is one supposed to access a express database created then moved in this way?
Thanks,
View 2 Replies
View Related
Apr 30, 2008
I have a sql installation disk that automatically puts in a product key during installation.
We have already used that product key and need to use the disk with a new product key.
When the installation gets to the Registration Information window, the product key is already populated in the field and it does not allow you to change it.
Is there anyway to change it?
Any help will be greatly appreciated.
View 1 Replies
View Related
Feb 28, 2008
Can someone help me to understand something about SQL Server? I will use Access for comparison.
Suppose you have a SQL Server database and you want users to be able to enter data into the fields just like you could with an Access database and forms. How would you set that up with SQL Server? I have noticed that you can convert an Access database into SQL Server, but you still need to use Access forms to enter data into the tables stored in SQL Server.
What if you wanted to create an SQL Server database from the ground up and set it up for user entry?
What am I missing here?
View 3 Replies
View Related
Jan 4, 2007
Hi, there,
We intended to create a report that allows users to enter remarks, comments or additional notes in supporting the report.
Can it be done in SSRS? FYI, we are using SQL Server 2005 Enterprise.
Thank you.
Regards,
Yong Hwee
View 5 Replies
View Related
Aug 28, 2007
Hello experts
I'm trying to enter some data into a table to run some tests. Whenever I open the table, I can enter data into all my fields *except* for the fields that I defined as Binary data types. I've tried to enter 0, 1, true, false... I always get the same error...
"Invalid value for cell...
The changed value in this cell was not recognized as valid.
Error Message: You cannot use the Result pane to set this Field data to values other than NULL"
Any ideas on this? I thought Binary was True or False? Is it the way I'm trying to enter data? I'm not familiar with SQL code and I just need to get a few rows filled up with data so I can test using gridview to pull the data onto a webpage.
Thanks
View 3 Replies
View Related
Feb 1, 2006
Hi,I need to set up a generic table in Oracle that allows users to enterdata that can later be retrieved in reports.I was thinking of a 3 column table in this structure:DATE,ITEM,VALUEfiguring that you can store anything in this way because ITEM can beanything and you can have multiple instances of a particular value byhaving a new date.Does that seem reasonable?In any case, what are my user front end options? I was thinking aMicrosoft Access database but its not ideal because not everyone herehas Access?Can you do it with Excel? Or what other options are there as a simpleuser front end to enter the data?Thanks for any advice.Kim
View 1 Replies
View Related
Mar 2, 2006
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
View 3 Replies
View Related
Jul 18, 2014
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
View 9 Replies
View Related
May 21, 2008
String or binary data would be truncated. I get this error when entering data using sql server management studio express.
I am not running a sql to insert or update the table. This is through the EDI.
The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.
Any ideas?
View 2 Replies
View Related
Feb 28, 2015
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
View 7 Replies
View Related