March 12, 2017 at 12:42 pm
Dear All,
Please help me to sort out it
I have reporttable where the fault report date and complete date is inserted and i have a master table for the ID.
1 ReportTable
| ID | ReportedDate | CompletedDate |
| 101 | 10-02-2016 | 12-02-2016 |
| 111 | 18-02-2016 | 19-02-2016 |
| 113 | 18-03-2016 | 22-03-2016 |
| 101 | 25-02-2016 | 27-02-2016 |
| 111 | 10-03-2016 | 12-03-2016 |
CREATE TABLE [dbo].[ReportTable](
[ID] [int] NULL,
[ReportedDate] [datetime] NULL,
[CompletedDate] [datetime] NULL
) ON [PRIMARY]
insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(101,'2016-02-10','2016-02-12')
insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(111,'2016-02-18','2016-02-19')
insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(113,'2016-03-18','2016-03-22')
insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(101,'2016-02-25','2016-02-27')
insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(111,'2016-03-10','2016-03-12')
2 IDTable
| ID |
| 101 |
| 111 |
| 113 |
| 101 |
| 111 |
| 122 |
| 133 |
| 141 |
| 151 |
CREATE TABLE [dbo].[IDTable](
[ID] [int] NULL
) ON [PRIMARY]
insert into [dbo].[IDTable]([ID]) values(101)
insert into [dbo].[IDTable]([ID]) values(113)
insert into [dbo].[IDTable]([ID]) values(111)
insert into [dbo].[IDTable]([ID]) values(122)
insert into [dbo].[IDTable]([ID]) values(133)
insert into [dbo].[IDTable]([ID]) values(141)
insert into [dbo].[IDTable]([ID]) values(151)
Output
I would like to get the result based on the date selection as 113 was down for 4 days, 101 was down for 2 + 2 = 4 days, 111 was down for 1 + 2 = 3 days and whatever ID not present in the reporttable will have date difference as 0. In where conditioin i must get the option to put Date range
| ID | Date Difference |
| 113 | 4 |
| 101 | 4 |
| 111 | 3 |
| 122 | 0 |
| 133 | 0 |
| 141 | 0 |
| 151 | 0 |
March 12, 2017 at 10:17 pm
Can anyone help me to solve this issue ?
March 13, 2017 at 2:16 am
Most probablydeclare @start date = '2016-02-11';
declare @end date = '2016-03-11';
select i.ID, coalesce(sum(datediff(day
,case when r.[ReportedDate] < @start then @start else r.[ReportedDate] end
,case when r.[CompletedDate] > @end then @end else r.[CompletedDate] end)
),0)
from [dbo].[IDTable] i
left join [dbo].[ReportTable] r on r.[ID]= i.[ID]
and r.[CompletedDate] >= @start and r.[ReportedDate] <= @end
group by i.[ID]
order by i.[ID]
March 13, 2017 at 10:25 am
shagil.a.gopinath - Sunday, March 12, 2017 10:17 PM>>I have report table where the fault report date and complete date is inserted and I have a master table for the ID. << There is no such thing as a master table in RDBMS; we have referenced and referencing tables. That term comes from the old days with network databases and file systems. Also, putting the word “_table†in the name of a table is design error called a Tibble. You are mixing data and metadata in violation of all of basic data modeling rules.It usually the symptom of some more serious design problems, as well as a source of amusement for experienced people.
You also got the display format for dates wrong; the only format allowed in ANSI'), ISO standard SQL is based on ISO 8601. The DDL you posted can never have a key, because everything is NULL-able. But by definition, a table must have at least one key.
Finally,there is no such thing as a generic “id†in RDBMS. It must be theidentifier of something in particular. Have you ever had a course in basic logic? This is a result of what is called the law of identity,the foundation of all Western thought. Furthermore, and identifier can never be a numeric value; what math were you going to do on it?
Let's start by fixing what you’ve got.
CREATE TABLE Something_Reports
(report_id CHAR(3) NOT NULL
REFERENCES Report(report_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
report_date DATE NOT NULL,
CHECK(report date <= completion_date),
PRIMARY KEY (report_id, report_date));Please notice all the guessing I had to do. I had to guess at a key. I had to correct the data types. I had to guess the relationship between the report date and the completion date. I hope I got it right since you didn’t help. Then your insertions can be done in one statement and should be. Microsoft has had the ANSI/ISO standard syntax for a few years now.
INSERT INTO Something_Reports
VALUES (‘101’,'2016-02-10', '2016-02-12'),
(‘101’,'2016-02-25', '2016-02-27'),
(‘111’,'2016-02-18', '2016-02-19'),
(‘111’,'2016-03-10', '2016-03-12'),
(‘113’,'2016-03-18', '2016-03-22');Instead of having to do these one row at a time, like you did when you were working with punch cards, this insertion can be done in a single statement.
CREATE TABLE Reports
(report_id CHAR(3) NOT NULL PRIMARY KEY);See how we have a key? See how we don’t have duplicate rows because that's the definition of a table and First Normal Form (1NF) in RDBMS? Now go back up and look at the references clause in the Something_Reports table. Again, I have to make a guess that if are port_id is dropped or changed, this update cascades.
INSERT INTO Reports
VALUES('101'), ('111'), ('113'),
('122'),('133'), ('141'), ('151');>>I would like to get the result based on the date selection as 113 was down for 4 days, 101 was down for 2 + 2 = 4 days, 111 was down for 1+ 2 = 3 days and whatever report_id not present in the Something_Report will have date difference as 0. In WHERE condition I must get the option to put Date range <<
this is a little bit problematic. We don’t have a definition about what it means for an event to be in a range. Does it have to be completely enclosed by the range? Or can it start outside the range? Or can it finish outside the range? Why do you want to set the results to zeros instead of null? Think about it. If an event completes on its report date then it’s a zero. But if it never occurs at all, then it’s null. The basic query. You seem to be after would look like this,and then you can put it in a CTE and filter it, according to whatever your business rules are.
SELECT I.report_id, SUM(DATEDIFF(DAY, R.report_date,R.completion_date))FROM Reports AS I LEFT OUTER JOIN Something_Reports AS R ONR.report_id = I.report_idGROUP BY I.report_id;
[/
Please post DDL and follow ANSI/ISO standards when asking for help.
March 13, 2017 at 4:10 pm
jcelko212 32090 - Monday, March 13, 2017 10:24 AMYou also got the display format for dates wrong; the only format allowed in ANSI'), ISO standard SQL is based on ISO 8601.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply