Solved

case statement inside a select statement

Posted on 2014-04-17
17
331 Views
Last Modified: 2014-06-06
The attached script is executed nightly to gather data used for a crystal report.

The issue I'm having is the case statement at the column name pyulast.

These scripts work fine in Tsql with values rather than variable name.  ie:"12345' rather than clientid.

there are two sets of statement to execute depending on the value in company_record column.

the when = 0 statement fails while entering the code.  I want the value of pyulast to be the result of either set depending on value of company_record.

Another set of eyes would be helpful
expexchselect.txt
0
Comment
Question by:Jeff_Kingston
  • 8
  • 8
17 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40006830
btw it would be better to enclose your T-SQL in a Code block, instead of an attachment.

> pyulast = case n.company_record when 1  (select max(dateval) from  ...
For starters, you're missing the THEN after WHEN 1

>                              ) as maxdate
The alias 'as max date' belongs after the END, not after each subquery in the CASE block.

>                              ) as maxdate  end...
Forgot the comma after the END

If it helps, I have an article out there called SQL Server CASE Solutions that is a good read.

>      (select 0) as renewal_discount,
Should just be -->  0 as renewal_discount,
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40006880
Use CTE's to make it more readable. You can also use a LEFT JOIN instead of the nested query on bm_onlinepass_payment. E.g.

WITH    Union1
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_online_library_use.ID
               WHERE    wti.top_id = clientid
                        AND web_online_library_use.olu_date >= startdate
                        AND web_online_library_use.olu_date <= DATEADD(YYYY, -1, startdate) - 1
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_onlinepass_use.ID
               WHERE    wti.top_id = clientid
                        AND web_onlinepass_use.use_date >= startdate
                        AND web_onlinepass_use.use_date <= DATEADD(YYYY, -1, startdate) - 1
             ),
        Union2
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
               WHERE    web_online_library_use.ID = '84572'
                        AND web_online_library_use.olu_date >= '12/1/2012'
                        AND web_online_library_use.olu_date <= '11/30/2013'
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
               WHERE    web_onlinepass_use.ID = '84572'
                        AND web_onlinepass_use.use_date >= '12/1/2012'
                        AND web_onlinepass_use.use_date <= '11/30/2013'
             )
    SELECT  CASE n.company_record
              WHEN 1 THEN ( SELECT  MAX(dateval)
                            FROM    Union1
                          )
              WHEN 0 THEN ( SELECT  MAX(dateval)
                            FROM    Union2
                          )
            END AS pyulast ,
            bop.cctype AS paymenttype ,
            clientID ,
            startDate ,
            endDate ,
            period ,
            pricePaid ,
            isNewLawyer ,
            isSponsor ,
            isBaseSubscription ,
            optedOut ,
            sizeOfFirmSource ,
            uid.yab ,
            baseAmount ,
            transactionalDiscountAmount ,
            priceToPayYear ,
            initialpurchdate ,
            monthlyamount ,
            lastpayment ,
            lastpaydate ,
            isPromotion ,
            isAutoEnrolled ,
            otherDiscountAmount ,
            isSoldThroughAdmin ,
            periodNextYear ,
            subscriptionStatus ,
            renewalFlag ,
            n.company ,
            n.full_name ,
            n.City ,
            n.company_record ,
            n.work_phone ,
            0 AS renewal_discount ,
            0 AS renewal_amount ,
            '' AS renewal_renewalflag ,
            '' AS renewal_payment_type ,
            '' AS renewal_failure ,
            '' AS pyufirst ,
            0 AS pyuart2 ,
            0 AS pyuebook ,
            0 AS pyuweb ,
            0 AS pyuondemand ,
            0 AS opbook ,
            0 AS opinpers ,
            0 AS opaudio ,
            0 AS opspent
    FROM    BM_OnlinePass_Subscription_archive bos
            INNER JOIN Name n ON n.id = bos.clientid
            LEFT JOIN UD_Indiv_Data uid ON uid.ID = n.id
            LEFT JOIN bm_onlinepass_payment bop ON bop.clientID = bos.clientid
                                                   AND CONVERT(VARCHAR(10), dateCreated, 101) = CONVERT(VARCHAR(10), bos.startdate, 101)
    WHERE   bos.isBaseSubscription = 1
            AND ( startDate >= @lystart
                  AND startDate <= @lyend
                );

Open in new window

0
 

Author Comment

by:Jeff_Kingston
ID: 40007623
I have tried everything I know how to do, but when I add the cte to the select statement,
the entire statement goes into heart failure.

Old school I know but i complete the original select and then I created a cursor to process the created records.  I checked the two datasets created in the cte and they contain the correct data.  What is wrong with this code starting @

select case @cr?

All I get inserted is null.

--
-- Set up variables
--
      Declare @client as varchar  (10)
      Declare @StartD as datetime
      declare @paytype as varchar(15)
      declare @endd as datetime
      declare @cr as integer
      declare @max as datetime
--
-- Define cursor
--
      DECLARE program CURSOR FOR
      select clientid, startdate,dateadd(YYYY,-1,startdate)-1 as endd,company_record
      from BM_OnlinePass_monthlytrackingJack where isbasesubscription = 1
--
-- Open Cursor
--
      OPEN program
--
-- Fetch loop
--
      FETCH NEXT FROM program into @client, @startd, @endd, @cr
      WHILE @@FETCH_STATUS = 0
      BEGIN
--            select @client, @startd, @endd, @cr
      ;WITH    Firms
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_online_library_use.ID
               WHERE    wti.top_id = @client
                        AND web_online_library_use.olu_date >= @startd
                        AND web_online_library_use.olu_date <= DATEADD(YYYY, -1, @startd) - 1
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_onlinepass_use.ID
               WHERE    wti.top_id = @client
                        AND web_onlinepass_use.use_date >= @startd
                        AND web_onlinepass_use.use_date <= DATEADD(YYYY, -1, @startd) - 1
             ),
        Solos
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
               WHERE    web_online_library_use.ID = @client
                        AND web_online_library_use.olu_date >= @startd
                        AND web_online_library_use.olu_date <= DATEADD(YYYY, -1, @startd) - 1
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
               WHERE    web_onlinepass_use.ID = @client
                        AND web_onlinepass_use.use_date >= @startd
                        AND web_onlinepass_use.use_date <= DATEADD(YYYY, -1, @startd) - 1
             )
           
 
    (SELECT  CASE @cr
              WHEN 1 THEN ( SELECT  MAX(dateval)
                            FROM    Firms)
                         
              WHEN 0 THEN ( SELECT  MAX(dateval)
                            FROM    Solos)
                         
            END AS maxdate)

          update BM_OnlinePass_monthlytrackingJack set pyulast = maxdate
             where clientID = @client and isbasesubscription = 1
           
 
   FETCH NEXT FROM Program into @client, @startd, @endd, @cr
      END
      CLOSE program
      DEALLOCATE program
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40008486
There is simply a comma and a declaration missing. E.g.

WITH    Firms AS ( .. ),
        Solos AS ( .. ),           
	MaxDate AS 	
	(
		SELECT	CASE @cr
			    WHEN 1 THEN ( SELECT MAX(dateval) FROM Firms )                         
			    WHEN 0 THEN ( SELECT MAX(dateval) FROM Solos )                         
            	END AS maxdate
	)
	UPDATE	J
	SET	pyulast = MD.maxdate
	FROM	BM_OnlinePass_monthlytrackingJack J
		CROSS JOIN MaxDate MD
	WHERE	clientID = @client 
		AND isbasesubscription = 1;

Open in new window

0
 

Author Comment

by:Jeff_Kingston
ID: 40008584
Still returns nulls?

Here is the code with the changes you suggested.

      Declare @client as varchar  (10)
      Declare @StartD as datetime
      declare @paytype as varchar(15)
      declare @endd as datetime
      declare @cr as integer
      declare @max as datetime
--
-- Define cursor
--
      DECLARE program CURSOR FOR
      select clientid, startdate,dateadd(YYYY,-1,startdate)-1 as endd,company_record
      from BM_OnlinePass_monthlytrackingJack where isbasesubscription = 1
--
-- Open Cursor
--
      OPEN program
--
-- Fetch loop
--
      FETCH NEXT FROM program into @client, @startd, @endd, @cr
      WHILE @@FETCH_STATUS = 0
      BEGIN
--            select @client, @startd, @endd, @cr
      ;WITH    Firms
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_online_library_use.ID
               WHERE    wti.top_id = @client
                        AND web_online_library_use.olu_date >= @startd
                        AND web_online_library_use.olu_date <= DATEADD(YYYY, -1, @startd) - 1
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_onlinepass_use.ID
               WHERE    wti.top_id = @client
                        AND web_onlinepass_use.use_date >= @startd
                        AND web_onlinepass_use.use_date <= DATEADD(YYYY, -1, @startd) - 1
             ),
        Solos
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
               WHERE    web_online_library_use.ID = @client
                        AND web_online_library_use.olu_date >= @startd
                        AND web_online_library_use.olu_date <= DATEADD(YYYY, -1, @startd) - 1
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
               WHERE    web_onlinepass_use.ID = @client
                        AND web_onlinepass_use.use_date >= @startd
                        AND web_onlinepass_use.use_date <= DATEADD(YYYY, -1, @startd) - 1
             ),
               MaxDate AS       
                  (
                        SELECT      CASE @cr
                              WHEN 1 THEN ( SELECT MAX(dateval) FROM Firms )                        
                              WHEN 0 THEN ( SELECT MAX(dateval) FROM Solos )                        
                  END AS maxdate
                  )
                  UPDATE      J
                  SET      pyulast = MD.maxdate
                  FROM      BM_OnlinePass_monthlytrackingJack J
                  CROSS JOIN MaxDate MD
                  WHERE      clientID = @client
                  AND isbasesubscription = 1;
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40008740
Do you work with the correct data?

WITH WOLU AS (
		SELECT	CONVERT(DATE, olu_date) AS dateval
		FROM	web_online_library_use				
	),
	WOU AS (
		SELECT	CONVERT(DATE, use_date) AS dateval
		FROM	web_onlinepass_use 
	),	
	Firms AS (
		SELECT	WOLU.dateval,
			wti.top_id AS Client
		FROM	WOLU
			INNER JOIN web_top_id wti ON wti.ind_ID = WOLU.ID
		UNION ALL 
		SELECT	WOU.dateval,
			wti.top_id AS Client
		FROM	WOU
			INNER JOIN web_top_id wti ON wti.ind_ID = wou.ID			
	),
	Solos AS (
		SELECT	dateval,
			ID AS Client					
		FROM	WOLU			
		UNION ALL 
		SELECT	dateval,
			ID AS Client
		FROM	WOU			
	),
	Jack AS (
		SELECT	ClientID,
			StartDate,
			DATEADD(YYYY, -1, StartDate)-1 AS EndDate,
			Company_Record,
		FROM	BM_OnlinePass_monthlytrackingJack
		WHERE	IsBaseSubscription = 1
	)
	SELECT	J.*,
		CASE Company_Record
		WHEN 1 THEN (
			SELECT	MAX(F.dateval) 
			FROM	Firms F
			WHERE	F.Client = J.ClientID
				AND F.dateval >= J.StartDate 
				AND F.dateval <= J.EndDate
			)
		WHEN 0 THEN (
			SELECT	MAX(S.dateval) 
			FROM	Solos S
			WHERE	S.Client = J.ClientID
				AND S.dateval >= J.StartDate 
				AND S.dateval <= J.EndDate
			)
		END AS maxdate 			
	FROM	Jack J;

Open in new window

0
 

Author Comment

by:Jeff_Kingston
ID: 40009184
This code does not address the issue of updating BM_OnlinePass_monthlytrackingJack table with the value of max date for either firm or solo.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40010320
Not it does not. But does it returns the correct data?  Because this seems to be your problem. Otherwise you wouldn't get NULLs.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Jeff_Kingston
ID: 40014703
there are three bugs in the code, 2 are references to WOLU.id, WOU.id which don't exist in your code and an extra comma after company_record in JACK.

After correcting these, this is the result:

BTW I didn't answer yesterday, Patriots Day in Boston is a holiday.

215627      2012-12-13 00:00:00.000      2011-12-12 00:00:00.000      0      NULL
217659      2012-12-04 16:56:35.400      2011-12-03 16:56:35.400      0      NULL
103325      2012-12-09 08:52:39.953      2011-12-08 08:52:39.953      0      NULL
77832      2012-12-10 00:00:00.000      2011-12-09 00:00:00.000      0      NULL
214180      2012-12-10 12:52:50.883      2011-12-09 12:52:50.883      0      NULL
215169      2012-12-11 13:00:45.910      2011-12-10 13:00:45.910      0      NULL
228492      2012-12-11 13:10:48.413      2011-12-10 13:10:48.413      0      NULL
66871      2012-12-12 15:57:41.673      2011-12-11 15:57:41.673      0      NULL
62      2012-12-13 00:00:00.000      2011-12-12 00:00:00.000      1      NULL
1288      2012-12-18 00:00:00.000      2011-12-17 00:00:00.000      1      NULL
209755      2012-12-19 00:00:00.000      2011-12-18 00:00:00.000      0      NULL
170681      2012-12-28 00:00:00.000      2011-12-27 00:00:00.000      0      NULL
228196      2012-12-28 00:00:00.000      2011-12-27 00:00:00.000      0      NULL
36291      2012-12-28 12:10:54.530      2011-12-27 12:10:54.530      0      NULL
228274      2012-12-03 10:20:56.040      2011-12-02 10:20:56.040      0      NULL
51762      2012-12-05 10:34:44.123      2011-12-04 10:34:44.123      0      NULL
156237      2012-12-17 11:24:05.763      2011-12-16 11:24:05.763      0      NULL
187742      2012-12-20 11:46:02.190      2011-12-19 11:46:02.190      0      NULL
175851      2012-12-07 10:08:20.463      2011-12-06 10:08:20.463      0      NULL
117174      2012-12-21 10:38:17.810      2011-12-20 10:38:17.810      0      NULL
98052      2012-12-10 00:00:00.000      2011-12-09 00:00:00.000      0      NULL
216648      2012-12-17 15:24:12.177      2011-12-16 15:24:12.177      0      NULL
1629      2012-12-26 14:09:58.403      2011-12-25 14:09:58.403      1      NULL
222147      2012-12-03 00:00:00.000      2011-12-02 00:00:00.000      0      NULL
135846      2012-12-07 00:00:00.000      2011-12-06 00:00:00.000      0      NULL
135375      2012-12-10 00:00:00.000      2011-12-09 00:00:00.000      0      NULL
85843      2012-12-12 13:50:14.663      2011-12-11 13:50:14.663      0      NULL
43522      2012-12-13 00:00:00.000      2011-12-12 00:00:00.000      0      NULL
151957      2012-12-13 00:00:00.000      2011-12-12 00:00:00.000      0      NULL
3560      2012-12-14 00:00:00.000      2011-12-13 00:00:00.000      1      NULL
64009      2012-12-19 00:00:00.000      2011-12-18 00:00:00.000      0      NULL
138718      2012-12-20 13:34:27.403      2011-12-19 13:34:27.403      0      NULL
66053      2012-12-25 13:33:49.790      2011-12-24 13:33:49.790      0      NULL
331      2012-12-31 00:00:00.000      2011-12-30 00:00:00.000      1      NULL
22038      2012-12-05 00:00:00.000      2011-12-04 00:00:00.000      1      NULL
110123      2012-12-04 14:27:41.233      2011-12-03 14:27:41.233      0      NULL
200540      2012-12-19 00:00:00.000      2011-12-18 00:00:00.000      0      NULL
35951      2012-12-03 16:09:55.667      2011-12-02 16:09:55.667      0      NULL
151719      2012-12-05 00:00:00.000      2011-12-04 00:00:00.000      1      NULL
4477      2012-12-05 13:13:53.137      2011-12-04 13:13:53.137      1      NULL
60425      2012-12-05 14:55:52.853      2011-12-04 14:55:52.853      0      NULL
48296      2012-12-06 10:39:11.590      2011-12-05 10:39:11.590      0      NULL
42171      2012-12-07 16:45:15.840      2011-12-06 16:45:15.840      0      NULL
84747      2012-12-11 00:00:00.000      2011-12-10 00:00:00.000      0      NULL
45461      2012-12-06 00:00:00.000      2011-12-05 00:00:00.000      0      NULL
227335      2012-12-12 11:57:28.857      2011-12-11 11:57:28.857      1      NULL
170743      2012-12-12 22:13:54.010      2011-12-11 22:13:54.010      0      NULL
156062      2012-12-10 13:44:09.333      2011-12-09 13:44:09.333      0      NULL
170007      2012-12-06 11:44:29.960      2011-12-05 11:44:29.960      0      NULL
75074      2012-12-04 14:23:14.730      2011-12-03 14:23:14.730      0      NULL
171914      2012-12-31 00:00:00.000      2011-12-30 00:00:00.000      1      NULL
7708      2012-12-14 00:00:00.000      2011-12-13 00:00:00.000      1      NULL
120278      2012-12-27 00:00:00.000      2011-12-26 00:00:00.000      0      NULL
11324      2012-12-31 00:00:00.000      2011-12-30 00:00:00.000      1      NULL
213961      2012-12-11 00:00:00.000      2011-12-10 00:00:00.000      0      NULL
155341      2012-12-31 00:00:00.000      2011-12-30 00:00:00.000      0      NULL
34716      2012-12-29 10:39:29.853      2011-12-28 10:39:29.853      0      NULL
84572      2012-12-01 00:00:00.000      2011-11-30 00:00:00.000      0      NULL
59933      2012-12-03 10:36:23.800      2011-12-02 10:36:23.800      0      NULL
130361      2012-12-05 13:52:06.783      2011-12-04 13:52:06.783      0      NULL
21084      2012-12-06 00:00:00.000      2011-12-05 00:00:00.000      1      NULL
16040      2012-12-07 00:00:00.000      2011-12-06 00:00:00.000      1      NULL
222557      2012-12-10 12:28:38.040      2011-12-09 12:28:38.040      0      NULL
104034      2012-12-11 14:41:31.247      2011-12-10 14:41:31.247      0      NULL
35490      2012-12-12 17:36:46.797      2011-12-11 17:36:46.797      0      NULL
168617      2012-12-08 16:03:16.280      2011-12-07 16:03:16.280      0      NULL
227019      2012-12-10 00:00:00.000      2011-12-09 00:00:00.000      1      NULL
151230      2012-12-14 00:00:00.000      2011-12-13 00:00:00.000      0      NULL
228624      2012-12-13 10:47:10.137      2011-12-12 10:47:10.137      1      NULL
235907      2012-12-14 16:28:38.150      2011-12-13 16:28:38.150      1      NULL
119971      2012-12-14 16:57:32.770      2011-12-13 16:57:32.770      0      NULL
152436      2012-12-17 21:21:31.003      2011-12-16 21:21:31.003      0      NULL
35008      2012-12-26 15:29:29.947      2011-12-25 15:29:29.947      0      NULL
217715      2012-12-20 00:00:00.000      2011-12-19 00:00:00.000      0      NULL
135474      2012-12-28 11:40:44.620      2011-12-27 11:40:44.620      0      NULL
113237      2012-12-29 23:38:44.403      2011-12-28 23:38:44.403      0      NULL
195563      2012-12-18 00:00:00.000      2011-12-17 00:00:00.000      0      NULL
222276      2012-12-31 00:00:00.000      2011-12-30 00:00:00.000      1      NULL
208151      2012-12-26 17:09:10.443      2011-12-25 17:09:10.443      0      NULL
204360      2012-12-28 13:07:58.783      2011-12-27 13:07:58.783      0      NULL
177796      2012-12-06 00:00:00.000      2011-12-05 00:00:00.000      1      NULL
156078      2012-12-18 00:00:00.000      2011-12-17 00:00:00.000      0      NULL
36819      2012-12-27 12:02:45.030      2011-12-26 12:02:45.030      0      NULL
205895      2012-12-19 00:00:00.000      2011-12-18 00:00:00.000      0      NULL
116415      2012-12-19 00:00:00.000      2011-12-18 00:00:00.000      0      NULL
139885      2012-12-31 00:00:00.000      2011-12-30 00:00:00.000      0      NULL
206748      2012-12-19 00:00:00.000      2011-12-18 00:00:00.000      0      NULL
77185      2012-12-10 16:26:32.570      2011-12-09 16:26:32.570      0      NULL
218551      2012-12-28 12:43:51.630      2011-12-27 12:43:51.630      0      NULL
183427      2012-12-27 00:00:00.000      2011-12-26 00:00:00.000      1      NULL
222045      2012-12-13 00:00:00.000      2011-12-12 00:00:00.000      1      NULL
983      2012-12-27 00:00:00.000      2011-12-26 00:00:00.000      1      NULL
10065      2012-12-27 00:00:00.000      2011-12-26 00:00:00.000      1      NULL
170698      2012-12-28 14:49:40.000      2011-12-27 14:49:40.000      0      NULL
228892      2012-12-28 00:00:00.000      2011-12-27 00:00:00.000      1      NULL
40942      2012-12-05 16:55:21.193      2011-12-04 16:55:21.193      0      NULL
205216      2012-12-19 00:00:00.000      2011-12-18 00:00:00.000      0      NULL
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40015613
Well, when it's the same column order as in my sample, then it's obvious: Your start date is always greater than your calculated end date. Thus the date predicate in your CASE statement cannot return any rows, thus it's NULL.
0
 

Author Comment

by:Jeff_Kingston
ID: 40015897
I fat fingered the dateadd when I retyped... should have been + not -.

I have duplicated the last section to get the minimum date as well.

Never having written a CTE,   where in the process or is it done after does the update statement get placed?  using the dataset as we have built it.  If I can get this resolved, I will forever be in your debt.

215627      2012-12-13 00:00:00.000      2013-12-12 00:00:00.000      0      2013-08-16      2013-01-09
217659      2012-12-04 16:56:35.400      2013-12-03 16:56:35.400      0      NULL      NULL
103325      2012-12-09 08:52:39.953      2013-12-08 08:52:39.953      0      2013-10-26      2012-12-11
77832      2012-12-10 00:00:00.000      2013-12-09 00:00:00.000      0      NULL      NULL
214180      2012-12-10 12:52:50.883      2013-12-09 12:52:50.883      0      2013-10-22      2013-03-11
215169      2012-12-11 13:00:45.910      2013-12-10 13:00:45.910      0      2012-12-21      2012-12-17
228492      2012-12-11 13:10:48.413      2013-12-10 13:10:48.413      0      2013-11-28      2012-12-26
66871      2012-12-12 15:57:41.673      2013-12-11 15:57:41.673      0      NULL      NULL
62      2012-12-13 00:00:00.000      2013-12-12 00:00:00.000      1      2013-09-05      2012-12-14
1288      2012-12-18 00:00:00.000      2013-12-17 00:00:00.000      1      2013-05-29      2013-01-20
209755      2012-12-19 00:00:00.000      2013-12-18 00:00:00.000      0      2013-12-05      2013-03-07
170681      2012-12-28 00:00:00.000      2013-12-27 00:00:00.000      0      2013-12-06      2012-12-30
228196      2012-12-28 00:00:00.000      2013-12-27 00:00:00.000      0      2013-01-15      2012-12-31
36291      2012-12-28 12:10:54.530      2013-12-27 12:10:54.530      0      2013-08-02      2012-12-29
228274      2012-12-03 10:20:56.040      2013-12-02 10:20:56.040      0      2013-06-26      2013-01-08
51762      2012-12-05 10:34:44.123      2013-12-04 10:34:44.123      0      2012-12-06      2012-12-06
156237      2012-12-17 11:24:05.763      2013-12-16 11:24:05.763      0      2013-03-05      2013-01-07
187742      2012-12-20 11:46:02.190      2013-12-19 11:46:02.190      0      2013-01-10      2013-01-06
175851      2012-12-07 10:08:20.463      2013-12-06 10:08:20.463      0      2013-10-18      2012-12-10
117174      2012-12-21 10:38:17.810      2013-12-20 10:38:17.810      0      NULL      NULL
98052      2012-12-10 00:00:00.000      2013-12-09 00:00:00.000      0      2013-07-29      2012-12-23
216648      2012-12-17 15:24:12.177      2013-12-16 15:24:12.177      0      2013-02-02      2013-01-16
1629      2012-12-26 14:09:58.403      2013-12-25 14:09:58.403      1      2013-05-18      2013-01-02
222147      2012-12-03 00:00:00.000      2013-12-02 00:00:00.000      0      2013-12-01      2012-12-04
135846      2012-12-07 00:00:00.000      2013-12-06 00:00:00.000      0      NULL      NULL
135375      2012-12-10 00:00:00.000      2013-12-09 00:00:00.000      0      NULL      NULL
85843      2012-12-12 13:50:14.663      2013-12-11 13:50:14.663      0      2013-07-31      2013-01-31
43522      2012-12-13 00:00:00.000      2013-12-12 00:00:00.000      0      2013-11-09      2012-12-15
151957      2012-12-13 00:00:00.000      2013-12-12 00:00:00.000      0      NULL      NULL
3560      2012-12-14 00:00:00.000      2013-12-13 00:00:00.000      1      2013-11-07      2012-12-17
64009      2012-12-19 00:00:00.000      2013-12-18 00:00:00.000      0      NULL      NULL
138718      2012-12-20 13:34:27.403      2013-12-19 13:34:27.403      0      2013-12-17      2012-12-21
66053      2012-12-25 13:33:49.790      2013-12-24 13:33:49.790      0      2013-12-21      2013-01-02
331      2012-12-31 00:00:00.000      2013-12-30 00:00:00.000      1      2013-12-30      2013-01-03
22038      2012-12-05 00:00:00.000      2013-12-04 00:00:00.000      1      NULL      NULL
110123      2012-12-04 14:27:41.233      2013-12-03 14:27:41.233      0      2013-08-29      2013-01-03
200540      2012-12-19 00:00:00.000      2013-12-18 00:00:00.000      0      2013-12-13      2013-01-02
35951      2012-12-03 16:09:55.667      2013-12-02 16:09:55.667      0      2013-08-05      2013-01-27
151719      2012-12-05 00:00:00.000      2013-12-04 00:00:00.000      1      2013-09-24      2012-12-05
4477      2012-12-05 13:13:53.137      2013-12-04 13:13:53.137      1      2013-10-03      2012-12-17
60425      2012-12-05 14:55:52.853      2013-12-04 14:55:52.853      0      2013-12-04      2012-12-06
48296      2012-12-06 10:39:11.590      2013-12-05 10:39:11.590      0      2013-10-07      2013-01-25
42171      2012-12-07 16:45:15.840      2013-12-06 16:45:15.840      0      2013-12-04      2012-12-10
84747      2012-12-11 00:00:00.000      2013-12-10 00:00:00.000      0      2013-12-10      2013-04-19
45461      2012-12-06 00:00:00.000      2013-12-05 00:00:00.000      0      2013-11-01      2012-12-12
227335      2012-12-12 11:57:28.857      2013-12-11 11:57:28.857      1      2013-12-09      2013-01-17
170743      2012-12-12 22:13:54.010      2013-12-11 22:13:54.010      0      2013-12-06      2013-03-06
156062      2012-12-10 13:44:09.333      2013-12-09 13:44:09.333      0      2013-07-02      2013-02-10
40942      2012-12-05 16:55:21.193      2013-12-04 16:55:21.193      0      2013-10-04      2013-10-04
205216      2012-12-19 00:00:00.000      2013-12-18 00:00:00.000      0      2013-12-02      2012-12-20
170007      2012-12-06 11:44:29.960      2013-12-05 11:44:29.960      0      2013-01-23      2013-01-23
75074      2012-12-04 14:23:14.730      2013-12-03 14:23:14.730      0      2013-11-26      2012-12-06
171914      2012-12-31 00:00:00.000      2013-12-30 00:00:00.000      1      2013-12-18      2013-01-30
7708      2012-12-14 00:00:00.000      2013-12-13 00:00:00.000      1      2013-09-20      2012-12-27
120278      2012-12-27 00:00:00.000      2013-12-26 00:00:00.000      0      2013-12-04      2012-12-27
11324      2012-12-31 00:00:00.000      2013-12-30 00:00:00.000      1      2013-12-19      2013-03-06
213961      2012-12-11 00:00:00.000      2013-12-10 00:00:00.000      0      2013-10-22      2012-12-11
155341      2012-12-31 00:00:00.000      2013-12-30 00:00:00.000      0      2013-09-09      2013-03-14
34716      2012-12-29 10:39:29.853      2013-12-28 10:39:29.853      0      2013-12-10      2013-01-03
84572      2012-12-01 00:00:00.000      2013-11-30 00:00:00.000      0      2013-10-22      2012-12-11
59933      2012-12-03 10:36:23.800      2013-12-02 10:36:23.800      0      2013-09-09      2013-01-07
130361      2012-12-05 13:52:06.783      2013-12-04 13:52:06.783      0      2013-10-31      2012-12-10
21084      2012-12-06 00:00:00.000      2013-12-05 00:00:00.000      1      2013-12-04      2012-12-07
16040      2012-12-07 00:00:00.000      2013-12-06 00:00:00.000      1      2013-12-05      2012-12-07
222557      2012-12-10 12:28:38.040      2013-12-09 12:28:38.040      0      2013-12-01      2012-12-12
104034      2012-12-11 14:41:31.247      2013-12-10 14:41:31.247      0      2013-12-09      2013-03-19
35490      2012-12-12 17:36:46.797      2013-12-11 17:36:46.797      0      2013-10-30      2013-01-23
168617      2012-12-08 16:03:16.280      2013-12-07 16:03:16.280      0      2013-08-05      2013-01-14
227019      2012-12-10 00:00:00.000      2013-12-09 00:00:00.000      1      2013-12-04      2013-01-08
151230      2012-12-14 00:00:00.000      2013-12-13 00:00:00.000      0      2013-11-06      2013-07-16
228624      2012-12-13 10:47:10.137      2013-12-12 10:47:10.137      1      2013-11-09      2012-12-15
235907      2012-12-14 16:28:38.150      2013-12-13 16:28:38.150      1      2013-12-10      2012-12-18
119971      2012-12-14 16:57:32.770      2013-12-13 16:57:32.770      0      2013-09-19      2013-01-04
152436      2012-12-17 21:21:31.003      2013-12-16 21:21:31.003      0      2013-11-07      2013-02-25
35008      2012-12-26 15:29:29.947      2013-12-25 15:29:29.947      0      2013-10-09      2013-01-05
217715      2012-12-20 00:00:00.000      2013-12-19 00:00:00.000      0      2013-12-08      2012-12-20
135474      2012-12-28 11:40:44.620      2013-12-27 11:40:44.620      0      2013-05-30      2013-01-25
113237      2012-12-29 23:38:44.403      2013-12-28 23:38:44.403      0      2013-12-27      2013-08-08
195563      2012-12-18 00:00:00.000      2013-12-17 00:00:00.000      0      2013-12-16      2012-12-20
222276      2012-12-31 00:00:00.000      2013-12-30 00:00:00.000      1      2013-12-16      2013-01-16
208151      2012-12-26 17:09:10.443      2013-12-25 17:09:10.443      0      2013-12-18      2012-12-29
204360      2012-12-28 13:07:58.783      2013-12-27 13:07:58.783      0      2013-10-30      2013-01-02
177796      2012-12-06 00:00:00.000      2013-12-05 00:00:00.000      1      2013-05-09      2013-01-04
156078      2012-12-18 00:00:00.000      2013-12-17 00:00:00.000      0      2013-12-04      2012-12-18
36819      2012-12-27 12:02:45.030      2013-12-26 12:02:45.030      0      2013-02-20      2013-02-20
205895      2012-12-19 00:00:00.000      2013-12-18 00:00:00.000      0      2013-11-15      2013-07-09
116415      2012-12-19 00:00:00.000      2013-12-18 00:00:00.000      0      2013-12-16      2013-01-24
139885      2012-12-31 00:00:00.000      2013-12-30 00:00:00.000      0      2013-12-04      2013-01-04
206748      2012-12-19 00:00:00.000      2013-12-18 00:00:00.000      0      2013-12-12      2013-01-03
77185      2012-12-10 16:26:32.570      2013-12-09 16:26:32.570      0      2013-05-24      2013-01-24
218551      2012-12-28 12:43:51.630      2013-12-27 12:43:51.630      0      2013-12-27      2012-12-31
183427      2012-12-27 00:00:00.000      2013-12-26 00:00:00.000      1      2013-10-30      2013-03-01
222045      2012-12-13 00:00:00.000      2013-12-12 00:00:00.000      1      2013-11-04      2012-12-17
983      2012-12-27 00:00:00.000      2013-12-26 00:00:00.000      1      2013-10-22      2013-01-14
10065      2012-12-27 00:00:00.000      2013-12-26 00:00:00.000      1      2013-12-24      2013-01-14
170698      2012-12-28 14:49:40.000      2013-12-27 14:49:40.000      0      NULL      NULL
228892      2012-12-28 00:00:00.000      2013-12-27 00:00:00.000      1      2013-11-13      2013-01-10
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40016225
As far as I can tell, you can now simply replace the SELECT with your UPDATE statement. See #40008486. E.g. like

WITH WOLU AS ( .. ),
	WOU AS ( .. ),	
	Firms AS ( .. ),
	Solos AS ( .. ),
	Jack AS (
		SELECT	ClientID,
			StartDate,
			DATEADD(YYYY, -1, StartDate)-1 AS EndDate,
			Company_Record,
		FROM	BM_OnlinePass_monthlytrackingJack
		WHERE	IsBaseSubscription = 1
	)
	UPDATE J
		SET pyulast  =
		CASE Company_Record
		WHEN 1 THEN (
			SELECT	MAX(F.dateval) 
			FROM	Firms F
			WHERE	F.Client = J.ClientID
				AND F.dateval >= J.StartDate 
				AND F.dateval <= J.EndDate
			)
		WHEN 0 THEN (
			SELECT	MAX(S.dateval) 
			FROM	Solos S
			WHERE	S.Client = J.ClientID
				AND S.dateval >= J.StartDate 
				AND S.dateval <= J.EndDate
			)
		END
	FROM	Jack J;

Open in new window

0
 

Author Comment

by:Jeff_Kingston
ID: 40017668
Followed your examples and have one last question.

The second update statement doesn't recognize maxdate??

      ;WITH    
      Firms
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_online_library_use.ID
               WHERE    wti.top_id = @client
                        AND web_online_library_use.olu_date >= @startd
                        AND web_online_library_use.olu_date <= DATEADD(YYYY, -1, @startd) - 1
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
                        INNER JOIN web_top_id wti ON wti.ind_ID = web_onlinepass_use.ID
               WHERE    wti.top_id = @client
                        AND web_onlinepass_use.use_date >= @startd
                        AND web_onlinepass_use.use_date <= DATEADD(YYYY, -1, @startd) - 1
             ),
        Solos
          AS ( SELECT   CONVERT(DATE, web_online_library_use.olu_date) AS dateval
               FROM     web_online_library_use
               WHERE    web_online_library_use.ID = @client
                        AND web_online_library_use.olu_date >= @startd
                        AND web_online_library_use.olu_date <= DATEADD(YYYY, -1, @startd) - 1
               UNION ALL
               SELECT   CONVERT(DATE, web_onlinepass_use.use_date) AS dateval
               FROM     web_onlinepass_use
               WHERE    web_onlinepass_use.ID = @client
                        AND web_onlinepass_use.use_date >= @startd
                        AND web_onlinepass_use.use_date <= DATEADD(YYYY, -1, @startd) - 1
             ),


               MaxDate AS      
                  (
                  SELECT      CASE @cr
                              WHEN 1 THEN ( SELECT MAX(dateval) FROM Firms )                        
                              WHEN 0 THEN ( SELECT MAX(dateval) FROM Solos )                        
                  END AS MaxDate
                  ),


               MinDate AS      
                  (
                  SELECT      CASE @cr
                              WHEN 1 THEN ( SELECT MIN(dateval) FROM Firms )                        
                              WHEN 0 THEN ( SELECT MIN(dateval) FROM Solos )                        
                  END AS MinDate
                  )

                  UPDATE      J
                  SET      pyufirst = MI.mindate
                  FROM      BM_OnlinePass_monthlytrackingJack J
                  CROSS JOIN MinDate MI
                  WHERE      clientID = @client
                  AND isbasesubscription = 1;
 
                  UPDATE      J
                  SET      pyulast = MD.maxdate
                  FROM      BM_OnlinePass_monthlytrackingJack J
                  CROSS JOIN MaxDate MD
                  WHERE      clientID = @client
                  AND isbasesubscription = 1;
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40017705
A CTE is one common table expression. It's only valid for one statement, so you can only use one UPDATE statement. But this is imho quite simple in your case:

WITH WOLU AS ( .. ),
	WOU AS ( .. ),	
	Firms AS ( .. ),
	Solos AS ( .. ),
	Jack AS (
		SELECT	ClientID,
			StartDate,
			DATEADD(YYYY, -1, StartDate)-1 AS EndDate,
			Company_Record,
		FROM	BM_OnlinePass_monthlytrackingJack
		WHERE	IsBaseSubscription = 1
	)
	UPDATE J
		SET pyulast  =
		CASE Company_Record
		WHEN 1 THEN (
			SELECT	MAX(F.dateval) 
			FROM	Firms F
			WHERE	F.Client = J.ClientID
				AND F.dateval >= J.StartDate 
				AND F.dateval <= J.EndDate
			)
		WHEN 0 THEN (
			SELECT	MAX(S.dateval) 
			FROM	Solos S
			WHERE	S.Client = J.ClientID
				AND S.dateval >= J.StartDate 
				AND S.dateval <= J.EndDate
			)
		END,
		pyufirst  =
		CASE Company_Record
		WHEN 1 THEN (
			SELECT	MIN(F.dateval) 
			FROM	Firms F
			WHERE	F.Client = J.ClientID
				AND F.dateval >= J.StartDate 
				AND F.dateval <= J.EndDate
			)
		WHEN 0 THEN (
			SELECT	MIN(S.dateval) 
			FROM	Solos S
			WHERE	S.Client = J.ClientID
				AND S.dateval >= J.StartDate 
				AND S.dateval <= J.EndDate
			)
		END
	FROM	Jack J; 

Open in new window

0
 

Author Comment

by:Jeff_Kingston
ID: 40035080
Still have no answer how to insert the results into a table.
0
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 40036946
INSERT? Use an INSERT statement. E.g.

WITH WOLU AS ( .. ),
	WOU AS ( .. ),	
	Firms AS ( .. ),
	Solos AS ( .. ),
	Jack AS (
		SELECT	ClientID,
			StartDate,
			DATEADD(YYYY, -1, StartDate)-1 AS EndDate,
			Company_Record,
		FROM	BM_OnlinePass_monthlytrackingJack
		WHERE	IsBaseSubscription = 1
	)
	INSERT INTO yourTable
	SELECT	J.columns
	FROM	Jack J;

Open in new window

0
 

Author Comment

by:Jeff_Kingston
ID: 40117314
This was resolved by rewriting the script into several smaller steps and it now works fine.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 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

17 Experts available now in Live!

Get 1:1 Help Now