• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

case statement inside a select statement

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
Jeff_Kingston
Asked:
Jeff_Kingston
  • 8
  • 8
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
Still have no answer how to insert the results into a table.
0
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
This was resolved by rewriting the script into several smaller steps and it now works fine.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now