VB.Bet SQL
I got this from one of the experts and with a few changes gives me what I wanted BUT
with cte as (
SELECT distinct Id, Code
CASE WHEN start < '2014-01-01' then '2014-01-01' else start end as Start
CASE WHEN finish < '2014-04-01' then finish else
CASE WHEN finish > '2014-04-01' then '2014-04-01' else
CASE WHEN finish IS NULL then '2014-04-01' END END END as finish
FROM CodeFile
WHERE ((start <= 2014-01-01' ) and (Finish >= '2014-04-01')) or
((Start < '2014-04-01') and (finish > 2014-01-01')) or
((Start > '2014-01-01') and (finish < '2014-04-01')) or
((Start < '2014-04-01') and (finish is NULL))
)
,n as (select 0 as d union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9)
,n3 as (select (a.d*100)+(b.d*10)+c.d as d from n as a,n as b,n as c)
Select id
,Dateadd(dd,case when start between '20140101' and '20140401'
then n3.d else 0 end,start) as start
,Dateadd(dd,case when start between '20140101' and '20140401'
and dateadd(dd,n3.d,start)<fin
then n3.d else 0 end,
case when start between '20140101' and '20140401'
and dateadd(dd,n3.d,start)<fin
then start else finish end)as finish
,code
*** add ,cd.Supplier after adding Left Join ***
from cte cross join n3
*** Add Extra Left Join Here ***
where n3.d between 0 and datediff(d,Start,finish)-1
order by id,start,finish
Gives me the correct Records
If I add a left Join at *** Add Extra Left Join Here ***
Left Join CodeFileDates as cd on (cte.id = cd.CodeId) and
(cd.StartDate between '2014-01-01' and '2014-04-01')
or I Tried
Left Join CodeFileDts('2014-01-01','
I Get multiple records how do I stop the date range from the left join adding
extra records meaning if the Left Join is met only return supplier for each date
created before the Left Join
With the origional SQL With out the left join I get 77 records - Correct
adding the Left Join to get the cd.supplier I get 385 records 5 Times 77
I only want the 77
Thanks
with cte as (
SELECT distinct Id, Code
CASE WHEN start < '2014-01-01' then '2014-01-01' else start end as Start
CASE WHEN finish < '2014-04-01' then finish else
CASE WHEN finish > '2014-04-01' then '2014-04-01' else
CASE WHEN finish IS NULL then '2014-04-01' END END END as finish
FROM CodeFile
WHERE ((start <= 2014-01-01' ) and (Finish >= '2014-04-01')) or
((Start < '2014-04-01') and (finish > 2014-01-01')) or
((Start > '2014-01-01') and (finish < '2014-04-01')) or
((Start < '2014-04-01') and (finish is NULL))
)
,n as (select 0 as d union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9)
,n3 as (select (a.d*100)+(b.d*10)+c.d as d from n as a,n as b,n as c)
Select id
,Dateadd(dd,case when start between '20140101' and '20140401'
then n3.d else 0 end,start) as start
,Dateadd(dd,case when start between '20140101' and '20140401'
and dateadd(dd,n3.d,start)<fin
then n3.d else 0 end,
case when start between '20140101' and '20140401'
and dateadd(dd,n3.d,start)<fin
then start else finish end)as finish
,code
*** add ,cd.Supplier after adding Left Join ***
from cte cross join n3
*** Add Extra Left Join Here ***
where n3.d between 0 and datediff(d,Start,finish)-1
order by id,start,finish
Gives me the correct Records
If I add a left Join at *** Add Extra Left Join Here ***
Left Join CodeFileDates as cd on (cte.id = cd.CodeId) and
(cd.StartDate between '2014-01-01' and '2014-04-01')
or I Tried
Left Join CodeFileDts('2014-01-01','
I Get multiple records how do I stop the date range from the left join adding
extra records meaning if the Left Join is met only return supplier for each date
created before the Left Join
With the origional SQL With out the left join I get 77 records - Correct
adding the Left Join to get the cd.supplier I get 385 records 5 Times 77
I only want the 77
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your selection criteria inside the CTE contains a redundant line
WHERE (Start <= '2014-01-01' AND Finish >= '2014-04-01')
OR (Start < '2014-04-01' AND finish > '2014-01-01') --<< redundant see the line above
OR (Start > '2014-01-01' AND finish < '2014-04-01')
OR (Start < '2014-04-01' AND finish IS NULL)
plus it can be simplified if you consider these relationships in the data:
With these points in mind, plus it also makes more sense (to me anyway) to use parameters as well, the existing stuff I believe would be better as:
Oh. and assuming
You may want to visit this http://sqlfiddle.com/#!3/f91ecc/1
details:
WHERE (Start <= '2014-01-01' AND Finish >= '2014-04-01')
OR (Start < '2014-04-01' AND finish > '2014-01-01') --<< redundant see the line above
OR (Start > '2014-01-01' AND finish < '2014-04-01')
OR (Start < '2014-04-01' AND finish IS NULL)
plus it can be simplified if you consider these relationships in the data:
LOW HIGH
-----|-------------------|-----
| |
S-----------E | starts before, ends in
| S---------------E | starts in, ends in
| S----------------------E starts in, ends after
| |
S--------------------------------E spans
| |
S < HIGH
E > LOWWith these points in mind, plus it also makes more sense (to me anyway) to use parameters as well, the existing stuff I believe would be better as:
Oh. and assuming
.[ID] is a unique identity why bother asking for "select distinct"?
[code]declare @low as datetime = '20140101'
declare @high as datetime = '20140401'
;WITH CTE
AS (
SELECT -- ** DISTINCT ** bad bad bad - if ID already UNIQUE???
Id
, Code
, CASE
WHEN start < @low THEN @low
ELSE start
END AS START
, CASE
WHEN [End] < @high THEN [End]
WHEN [End] > @high THEN @high
WHEN [End] IS NULL THEN @high
ELSE [End] --<< this is new too, and needed!
END AS FINISH
FROM CodeFile
WHERE [start] <= @high and ([End] >= @low or [End] is null) --<< much simpler
),
N
AS (
SELECT 0 AS D UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9),
N3
AS (
SELECT
(A.d * 100) + (B.d * 10) + C.d AS D
FROM N AS A
CROSS JOIN N AS B
CROSS JOIN N AS C
)
SELECT
id
, ca.start
, DATEADD(dd,1, ca.start) AS FINISH
, code
--*** Add Extra SQL Here ***
--, cd.Supplier
FROM CTE
CROSS JOIN N3
CROSS APPLY (
SELECT DATEADD(dd,
CASE
WHEN cte.start BETWEEN @low AND @high THEN N3.d
ELSE 0
END, cte.start)
) as ca (start)
--*** Add Extra SQL Here ***
WHERE N3.d BETWEEN 0 AND DATEDIFF(D, cte.start, cte.finish) - 1
ORDER BY id, START, FINISHYou may want to visit this http://sqlfiddle.com/#!3/f91ecc/1
details:
**MS SQL Server 2008 Schema Setup**:
CREATE TABLE CodeFile
([Id] int, [Start] datetime, [End] datetime, [Code] varchar(200))
;
INSERT INTO CodeFile
([Id], [Start], [End], [Code])
VALUES
(1, '2012-03-30 00:00:00', '2014-01-28 00:00:00', 'C1'),
(2, '2014-01-28 00:00:00', '2014-02-10 00:00:00', 'C2'),
(3, '2014-02-10 00:00:00', NULL, 'C1'),
(100, '2014-01-01 00:00:00', '2014-04-01 00:00:00', 'equals'),
(101, '2013-02-10 00:00:00', '2014-03-01 00:00:00', 'starts before, ends in'),
(102, '2014-02-01 00:00:00', '2014-03-01 00:00:00', 'starts in, ends in'),
(103, '2014-02-01 00:00:00', '2015-04-01 00:00:00', 'starts in, ends after'),
(104, '2013-01-01 00:00:00', '2015-04-01 00:00:00', 'spans'),
(104, '2013-01-01 00:00:00', '2013-04-01 00:00:00', 'ignore')
;
**Query 1**:
declare @low as datetime = '2014-01-01'
declare @high as datetime = '2014-01-02'
;WITH CTE
AS (
SELECT -- ** DISTINCT ** bad bad bad isn't ID already UNIQUE???
Id
, Code
, CASE
WHEN start < @low THEN @low
ELSE start
END AS START
, CASE
WHEN [End] < @high THEN [End]
WHEN [End] > @high THEN @high
WHEN [End] IS NULL THEN @high
ELSE [End]
END AS FINISH
FROM CodeFile
WHERE [start] <= @high and ([End] >= @low or [End] is null)
),
N
AS (
SELECT 0 AS D UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9),
N3
AS (
SELECT
(A.d * 100) + (B.d * 10) + C.d AS D
FROM N AS A
CROSS JOIN N AS B
CROSS JOIN N AS C
)
SELECT
id
, ca.start
, DATEADD(dd,1, ca.start) AS FINISH
, code
--*** Add Extra SQL Here ***
--, cd.Supplier
FROM CTE
CROSS JOIN N3
CROSS APPLY (
SELECT DATEADD(dd,
CASE
WHEN cte.start BETWEEN @low AND @high THEN N3.d
ELSE 0
END, cte.start)
) as ca (start)
--*** Add Extra SQL Here ***
WHERE N3.d BETWEEN 0 AND DATEDIFF(D, cte.start, cte.finish) - 1
ORDER BY id, START, FINISH
**[Results][2]**:
| ID | START | FINISH | CODE |
|-----|--------------------------------|--------------------------------|------------------------|
| 1 | January, 01 2014 00:00:00+0000 | January, 02 2014 00:00:00+0000 | C1 |
| 100 | January, 01 2014 00:00:00+0000 | January, 02 2014 00:00:00+0000 | equals |
| 101 | January, 01 2014 00:00:00+0000 | January, 02 2014 00:00:00+0000 | starts before, ends in |
| 104 | January, 01 2014 00:00:00+0000 | January, 02 2014 00:00:00+0000 | spans |
[1]: http://sqlfiddle.com/#!3/f91ecc/2
What I would like to know now is what is it that is in that additional table that you want as part of your result.
It is probably going to be best done in the CTE where you can get those values and, perhaps by using GROUP BY, continue to produce the same number of rows that you want as the final result.
If you do visit sqlfiddle you will see I have used some sample data, and without it I could not have produced a worthwhile query. The same could be said for this additional table most probably. Sample data really does assist in describing your needs and helps us produce a solution.
Oh, regarding N3, that is 1000 rows. so the largest duration you can deal with is 1000 days.
--
so. bottom line, use your left join in the CTE, and most likely, use GROUP BY at the same time, to gather the data without expanding number of rows before cross joining to N3
It is probably going to be best done in the CTE where you can get those values and, perhaps by using GROUP BY, continue to produce the same number of rows that you want as the final result.
If you do visit sqlfiddle you will see I have used some sample data, and without it I could not have produced a worthwhile query. The same could be said for this additional table most probably. Sample data really does assist in describing your needs and helps us produce a solution.
Oh, regarding N3, that is 1000 rows. so the largest duration you can deal with is 1000 days.
--
so. bottom line, use your left join in the CTE, and most likely, use GROUP BY at the same time, to gather the data without expanding number of rows before cross joining to N3
ASKER
do you know how to use a code block? NO
Your Question - What I would like to know now is what is it that is in that additional table that you want as part of your result.
The First table contains the Id start and finish dates plus the supply code
The second table contains the Id start End Dates, supplycode and the Amount paid for the SupplyCode
I need the amount paid
[CodeFile].[ID] is not unique - you can have the same ID but different SupplyCode
This was setup before I started on the system I cannot show you the actual SQL as the company would not like me putting it on the net
Your Question - What I would like to know now is what is it that is in that additional table that you want as part of your result.
The First table contains the Id start and finish dates plus the supply code
The second table contains the Id start End Dates, supplycode and the Amount paid for the SupplyCode
I need the amount paid
[CodeFile].[ID] is not unique - you can have the same ID but different SupplyCode
This was setup before I started on the system I cannot show you the actual SQL as the company would not like me putting it on the net
--------
*sigh* (it's been a tough day)
but you could mock-up data in the same format, this is what we imply when we say "sample data"
(we really are not after company secrets, we just want to help answer a question)
extra_table
id, field1, field2, field3
1, 1.23, 4.56, code-x
-------
Most important:
do you want each line item of cost (as a new row) or that sum of costs?
if you are after the sum of costs then do as I suggested already
use the left join inside the CTE, SUM(the_cost) and GROUP BY the, approriate, fields e.g.
;WITH CTE
AS (
SELECT
Id
, Code
, CASE
WHEN start < @low THEN @low
ELSE start
END AS START
, CASE
WHEN [End] < @high THEN [End]
WHEN [End] > @high THEN @high
WHEN [End] IS NULL THEN @high
ELSE [End] --<< this is new too, and needed!
END AS FINISH
, SUM( [some_cost_FIELD] ) as sum_costs
FROM CodeFile
Left Join CodeFileDates as cd on CodeFile.id = cd.CodeId
and cd.StartDate between @low and @high
WHERE [start] <= @high and ([End] >= @low or [End] is null) --<< much simpler
GROUP BY
Id
, Code
, CASE
WHEN start < @low THEN @low
ELSE start
END
, CASE
WHEN [End] < @high THEN [End]
WHEN [End] > @high THEN @high
WHEN [End] IS NULL THEN @high
ELSE [End]
END
),ASKER
Sorry about delay attached is a layout of the files and what I need If you require more please let me know
Problem.docx
Problem.docx
https://www.experts-exchange.com/questions/28606087/VB-SQL.html