Solved

VB.Bet SQL

Posted on 2015-02-12
7
53 Views
Last Modified: 2015-08-25
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)<finish
                       then n3.d else 0 end,
                  case when start between '20140101' and '20140401'
                        and dateadd(dd,n3.d,start)<finish
                       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','2014-04-01') as cd on (cte.id = cd.CodeId)

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
0
Comment
Question by:Kevinfeening
  • 5
  • 2
7 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40607051
CROSS JOIN?

What was the previous question's URL please

+edit
sorry, and can you provide the full sql (that produces too many rows)

do you know how to use a code block?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40607057
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40607125
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:
    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 > LOW

Open in new window


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
.[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, FINISH

Open in new window


You 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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40607130
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
0
 

Author Comment

by:Kevinfeening
ID: 40607239
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40607300
use-code-tags-please.png
--------

*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

),

Open in new window

0
 

Author Comment

by:Kevinfeening
ID: 40613441
Sorry about delay attached is a layout of the files and what I need If you require more please let me know
Problem.docx
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question