with or without :)

sample1

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

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


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.

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

 

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

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

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

--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);

By: |22/02/2019|categories: