Solved

case statement inside a select statement

Posted on 2014-04-17
17
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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
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 33

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 33

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 33

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
 

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 33

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 33

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 33

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 33

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

735 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