Posts with or without -)
Post
Cancel

with or without -)

sample1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH table0(field1,field2,field3,field4) AS
                    (SELECT row_number() over (partition by table1.field4 order by table2.dateinserted) as field1 , table3.Description, 
                        coalesce(table2.field11,table2.field12), table2.field4
                        from doctoraudits table2
                        inner join table1 on table1.field4 = table2.field4 
                        inner join table4 on  table4.field5 = table2.field5
                        left join tbl table3 on table3.id = table2.statusid
                        where table4.isactive=1 and table1.field4 in (select field4 from table5)
                )
                select count(*) 
                from table1 r
                inner join table6 rd on rd.field4 = r.field4
                inner join table7 d on  d.field6 = rd.field6
                inner join table6 rd2 on  rd2.field6 = d.field6
                left join table0 doc1 on doc1.field9 = r.field9 and doc1.field10 =1
                left join table0 doc2 on doc2.field9 = r.field9 and doc2.field10 =2
                left join table0 doc3 on doc3.field9 = r.field9 and doc3.field10 =3
where rd2.field7 is null and d.field8=1

sample 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
WITH Visits AS(
                SELECT   Albums.AlbumID,COUNT(a.Expr1) AS Visits
                FROM         
                (SELECT SessID,SUBSTRING(username,8,(CHARINDEX(':',username,9)-8) ) AS Expr1 FROM SessionsLog where username like '%EVNUSR%') AS a
                LEFT JOIN Albums ON a.Expr1 = Albums.EventAlbumCode
                GROUP BY Albums.AlbumID
              )
,
PhotoSelected AS(
                SELECT   Albums.AlbumID,COUNT(distinct PhotoSelections.PhotoID) AS PhotosSelected
                FROM         PhotoSelections  
                    INNER JOIN Photos ON dbo.PhotoSelections.PhotoID = dbo.Photos.PhotoID
                    INNER JOIN Albums ON dbo.PhotoSelections.AlbumID = dbo.Albums.AlbumID AND dbo.Photos.AlbumID = dbo.Albums.AlbumID
                    INNER JOIN Carts ON dbo.PhotoSelections.CartID = dbo.Carts.CartID
                    INNER JOIN OrderCarts ON dbo.Carts.CartID = dbo.OrderCarts.CartID
                where  (PhotoSelections.Quantity IS NOT NULL) AND (dbo.PhotoSelections.Quantity > 0) and Carts.CartID is not null
                GROUP BY Albums.AlbumID, Albums.TotalPictures
              ),
CustomOrder as  ( select Albums.AlbumID,Carts.TotalCopies,Orders.OrderID from albums
                    INNER JOIN  Orders ON Albums.AlbumID = Orders.AlbumID
                    INNER JOIN  OrderCarts ON dbo.Orders.OrderID = OrderCarts.OrderID
                    INNER JOIN  Carts ON OrderCarts.CartID = Carts.CartID
                )

SELECT  ISNULL(SUM(distinct CustomOrder.TotalCopies),0) as TotalCopies,
categoryname,albums.AlbumName,  albums.TotalPictures,(Visits) as PageVisits,
count(CustomOrder.AlbumID) as [Bills], ISNULL(SUM(CustomOrder.TotalCopies),0) as [PrintTotal],
ISNULL((PhotoSelected.PhotosSelected),0) AS SelectedPictures
FROM         Albums

LEFT JOIN Visits ON Albums.AlbumID = Visits.AlbumID
LEFT JOIN CustomOrder on Albums.AlbumID =CustomOrder.AlbumID
LEFT JOIN  AlbumCategories on AlbumCategories.AlbumCategoryID = albums.AlbumCategoryID
LEFT JOIN PhotoSelected ON Albums.AlbumID =  PhotoSelected.AlbumID

where albums.isevent = 1
AND (Albums.AlbumCategoryID  =  @AlbumCategoryID OR @AlbumCategoryID IS NULL)
AND (albums.ClientID = @ClientID OR @ClientID IS NULL)
AND albums.IsDeleted = 0
AND (albums.EventDate >= @EventdateFrom OR @EventdateFrom IS NULL)
AND (albums.EventDate <= @eventdateto="" or="" @eventdateto="" is="" null)="" --albums.albumid="769" group="" by="" categoryname,albums.albumname,="" visits.visits,="" albums.totalpictures,photoselected.photosselected="" order="" by="" categoryname="" desc,albums.albumname="" desc="" ```=""></=>[more](https://modern-sql.com/feature/with)

* * *

**UNION** removes duplicate records (where all columns in the results are the same), **UNION ALL** does not.
There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates.

```js
--https://stackoverflow.com/a/49928

--UNION Example:
SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

--UNION ALL example:
SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--https://forums.techguy.org/threads/solved-sql-how-to-use-group-by-with-union.539870/#post-4406173

SELECT AVG(date_completed-login_date),
       to_char(to_date(login_date), 'YYYY') AS wYear
FROM
  (SELECT test.date_completed 'date_completed',
                              sample.login_date 'login_date')
FROM sample
JOIN test ON sample.id_numeric = test.sample
UNION ALL
SELECT c_test.date_completed 'date_completed',
                             c_sample.login_date 'login_date'
FROM c_sample
JOIN c_test ON c_sample.id_numeric = c_test.sample ) AS tbl
GROUP BY to_char(to_date(login_date), 'YYYY')

The following query returns always positive number. The condition is true when the summation is 2.

On line 3, the true is variable that setted (when is false = false doesnt continue to execute the subquery) from the code (when the user chosen the specific restriction)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select * from tableA 
where tableA.fieldA = 1 and
( lower('true')='false' or (
	select abs(sum(t.soula))makis
	from
		(
			SELECT CASE 
					 WHEN tableB.statusid = 
						  (SELECT id 
						   FROM   tableBstatuses 
						   WHERE  NAME = 'Approved') 
						THEN 1 

					 WHEN tableB.statusid = 
						  (SELECT id 
						   FROM   tableBstatuses 
						   WHERE  NAME = 'Denied') 
						THEN -1 
					 ELSE 0 
					 END AS soula 
			FROM   tableB 
			WHERE  tableB.recID = tableA.recID 
				   AND tableA.fieldB = 'Pending' 
		) t
	)=2
)

GroupBy complex

1
2
3
4
5
6
select playerid 
from players where badge in (
      select badge from players group by badge having count(*)>1
) and playerid not in (
  select playerid from requests
)

table variable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
--DATEDIFF - https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017
--Table variable - https://searchsqlserver.techtarget.com/tutorial/Table-variables
--test on SQL2008 - 670k records - 29min (6fields)

USE master
GO
DECLARE @start_dt DATETIME;
DECLARE @end_dt DATETIME;

set @start_dt = GETDATE();

print 'STARTED @ ' + convert(nvarchar(25), @start_dt) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);

DECLARE @table_counter bigint;
set @table_counter = (select count(*) from Northwind..categories);
print 'source table total records : ' + convert(nvarchar(25),@table_counter);

set @table_counter = (select count(*) from DestDB..Categories);
print 'destination table total records : ' + convert(nvarchar(25),@table_counter);

--TABLE variable
DECLARE @tmp_ids TABLE (id INT)

--INSERT the temporary record for the while
insert into @tmp_ids  select top 1000 CategoryID from Northwind..Categories

WHILE (select count(*) from @tmp_ids) > 0
BEGIN

--INSERT to DESTINATION TABLE
insert into DestDB..Categories  (CategoryName,Description,Picture) 
(select CategoryName,Description,Picture from Northwind..categories where categoryid in (select id from @tmp_ids))

--delete from SOURCE TABLE incl. temporary
delete from Northwind..categories where categoryid in (select id from @tmp_ids)
delete from @tmp_ids

--INSERT to SOURCE TABLE to VARIABLE
Insert into @tmp_ids  select top 1000 CategoryID from Northwind..Categories

END

set @end_dt = GETDATE();
print  CHAR(13)+CHAR(10) + 'COMPLETED @ ' + convert(nvarchar(25), @end_dt) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
print 'Duration in minutes : ' + convert(nvarchar(8),DATEDIFF(minute,@start_dt, @end_dt)) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
print 'Duration in seconds : ' + convert(nvarchar(8),DATEDIFF(second,@start_dt, @end_dt)) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
print 'Duration in milliseconds : ' + convert(nvarchar(8),DATEDIFF(millisecond,@start_dt, @end_dt)) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);

set @table_counter = (select count(*) from Northwind..categories);
print 'source table total records : ' + convert(nvarchar(25),@table_counter);

set @table_counter = (select count(*) from DestDB..Categories);
print 'destination table total records : ' + convert(nvarchar(25),@table_counter);

origin - https://www.pipiscrew.com/?p=14722 with-or-without

This post is licensed under CC BY 4.0 by the author.
Contents

Trending Tags