Solved

VB.Bet SQL

Posted on 2015-02-12
7
44 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
Comment Utility
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
Comment Utility
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now