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