1. Get list to inner model
model = visitors.Where(w=>w.Department!=null).GroupBy(x =>
Convert.ToDateTime(x.StartTime).ToShortDateString())
Convert.ToDateTime(x.StartTime).ToShortDateString())
.Select(x => new VisitorStatuModel()
{
Date = Convert.ToDateTime(x.Key).ToString("dd-MMM-yy"),
Department = x.GroupBy(g=>g.Department).Select(s=> new DepCount()
{
Department = s.Key,
TotalIncoming = s.Count(c=>c.Department != null && !c.IsProactive),
TotalProactive = s.Count(c=>c.Department != null && c.IsProactive)
}).ToList(),
}).OrderBy(o => o.DateTime).ToList();
2. Work with cursor
declare @regNo varchar(20)
declare @acSemNo int
declare @scholarships varchar(20)
declare stud cursor for
select regNo,acSemNo,scholarships from [172.16.10.12].[BSC_SIMS_2].[dbo].[StudentRegistration_vw]
where scholarships is not null
open stud
fetch next from stud into @regNo,@acSemNo,@scholarships
while @@FETCH_STATUS=0
begin
declare @r varchar(20)
update tblStudentSemesterRegistration
set ScholershipStatus= @scholarships
from tblStudentSemesterRegistration ssr
join tblSemesterRegistration sr on sr.SemesterID = ssr.SemesterID
join tblStudent s on s.StudentID = ssr.StudentID
where s.StudentRegistrationNo=@regNo and sr.Gayanacsemno=@acSemNo
select @r=s.StudentRegistrationNo from tblStudentSemesterRegistration ssr
join tblStudent s on s.StudentID = ssr.StudentID
join tblSemesterRegistration sr on sr.SemesterID = ssr.SemesterID
where StudentRegistrationNo=@regNo and sr.Gayanacsemno=@acSemNo
print @r
fetch next from stud into @regNo,@acSemNo,@scholarships
end
close stud
deallocate stud
3. Connection string example
<add name="DefaultConnection" connectionString="Data Source=KRISHAN-PC\SQLEXPRESS;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient" /> <add name="testEntities" connectionString="metadata=res://*/Models.test.csdl|res://*/Models.test.ssdl|res://*/Models.test.msl;provider=System.Data.SqlClient;provider connection string="data source=KRISHAN-PC\SQLEXPRESS;initial catalog=test;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" /></connectionStrings>
4.Insert into select in SQL
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...FROM table1WHERE condition;
5.Select multiple columns with the group by
SELECT g2.count1,g1.*,g2.*
FROM Account AS g1
LEFT JOIN (
select a.AccountId,count(o.CustomerId) as 'count1'
from Account a
inner join Opportunity o on o.CustomerId = a.AccountId
group by a.AccountId
) AS g2
on g2.AccountId = g1.AccountId
6. Find keyword in SP
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%
7. Add date to SQL table
WITH TestItOut AS ( SELECT CAST('2019-01-01' as datetime) DateColumn UNION ALL SELECT DateColumn + 1 FROM TestItOut WHERE DateColumn + 1 <= '2019-12-31' ) INSERT INTO DateContours(DateRef,DayIs) select DateColumn,case when datename(dw,DateColumn)='Saturday'then 1 when datename(dw,DateColumn)='Sunday'then 1 else 0 end from TestItOut option (maxrecursion 0)
8.How to get column values in one comma separated value
select distinct t.[user],
STUFF((SELECT distinct ', ' + t1.department
from yourtable t1
where t.[user] = t1.[user]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') department
from yourtable t;
Ex
ID User Department
1 User1 Admin
2 User1 Accounts
3 User2 Finance
4 User3 Sales
5 User3 Finance
I need a select query which results following format
ID User Department
1 User1 Admin,Accounts
2 User2 Finance
3 User3 Sales, Finance
9. Insert multiple record to database using While loop
DECLARE @first AS INT = 1
DECLARE @last AS INT = 1000
SET IDENTITY_INSERT [dbo].[TableName] OFF
WHILE(@first <= @last)
BEGIN
INSERT INTO [dbo].[TableName]
(Cpl1,Col2,Col3,Col4)
VALUE
(1,NULL,'2019-11-22 09:54:27.800','Col4')
print (@first)
SET @first += 1
END
SET IDENTITY_INSERT [dbo].[TableName] ON
3. Connection string example
<add name="DefaultConnection" connectionString="Data Source=KRISHAN-PC\SQLEXPRESS;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient" /> <add name="testEntities" connectionString="metadata=res://*/Models.test.csdl|res://*/Models.test.ssdl|res://*/Models.test.msl;provider=System.Data.SqlClient;provider connection string="data source=KRISHAN-PC\SQLEXPRESS;initial catalog=test;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" /></connectionStrings>
4.Insert into select in SQL
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...FROM table1WHERE condition;
5.Select multiple columns with the group by
SELECT g2.count1,g1.*,g2.*
FROM Account AS g1
LEFT JOIN (
select a.AccountId,count(o.CustomerId) as 'count1'
from Account a
inner join Opportunity o on o.CustomerId = a.AccountId
group by a.AccountId
) AS g2
on g2.AccountId = g1.AccountId
6. Find keyword in SP
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%
7. Add date to SQL table
WITH TestItOut AS ( SELECT CAST('2019-01-01' as datetime) DateColumn UNION ALL SELECT DateColumn + 1 FROM TestItOut WHERE DateColumn + 1 <= '2019-12-31' ) INSERT INTO DateContours(DateRef,DayIs) select DateColumn,case when datename(dw,DateColumn)='Saturday'then 1 when datename(dw,DateColumn)='Sunday'then 1 else 0 end from TestItOut option (maxrecursion 0)
8.How to get column values in one comma separated value
select distinct t.[user],
STUFF((SELECT distinct ', ' + t1.department
from yourtable t1
where t.[user] = t1.[user]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') department
from yourtable t;
Ex
ID User Department
1 User1 Admin
2 User1 Accounts
3 User2 Finance
4 User3 Sales
5 User3 Finance
I need a select query which results following format
ID User Department
1 User1 Admin,Accounts
2 User2 Finance
3 User3 Sales, Finance
9. Insert multiple record to database using While loop
DECLARE @first AS INT = 1
DECLARE @last AS INT = 1000
SET IDENTITY_INSERT [dbo].[TableName] OFF
WHILE(@first <= @last)
BEGIN
INSERT INTO [dbo].[TableName]
(Cpl1,Col2,Col3,Col4)
VALUE
(1,NULL,'2019-11-22 09:54:27.800','Col4')
print (@first)
SET @first += 1
END
SET IDENTITY_INSERT [dbo].[TableName] ON
10. Comma separated parameters
(JobType IN (SELECT value FROM STRING_SPLIT(@JobType, '','')) OR @JobType IN (''*''))
11.Get Index size in SQL server
SELECT
OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
Where i.name in('Indexname1','Indexname2')
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
12.Get SQL Schema changes
SELECT
OBJECT_NAME(sc.[object_id]) as [table]
,sc.[name] as [column]
,so.modify_date
,so.create_date
FROM [sys].[columns] sc
JOIN [sys].[objects] so
ON sc.[object_id] = so.[object_id]
ORDER BY so.modify_date DESC, so.create_date ASC
or
13.Search Tables and Views contains
SELECT COLUMN_NAME AS 'ColumnName'
,TABLE_NAME AS 'TableName'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%MyName%'
ORDER BY TableName
,ColumnName;
14.Remove restricted user on db
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE mydatabase SET MULTI_USER15.Check database historyDECLARE @filename VARCHAR(255) SELECT @FileName = SUBSTRING (path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1; SELECT gt.HostName, gt.ApplicationName, gt.NTUserName, gt.NTDomainName, gt.LoginName, gt.SPID, gt.EventClass, te.Name AS EventName, gt.EventSubClass, gt.TEXTData, gt.StartTime, gt.EndTime, gt.ObjectName, gt.DatabaseName, gt.FileName, gt.IsSystem FROM [fn_trace_gettable](@filename, DEFAULT) gt JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id WHERE EventClass in (164) ORDER BY StartTime DESC
16. Select inner entityvar carsWithParts = _context.Car .Select(car => new { Car = car, Parts = _context.Parts
.Where(p => p.CarId2 == car.AlternativeId).ToList()
})
.ToList()
.Select(result =>
{
var car = result.Car;
car.Parts = result.Parts;
return car;
})
.ToList();
});
No comments:
Post a Comment