Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Share tech news, updates, or what's on your mind.

Sign up to Post

We have around 6 billion rows in a single table
We are running some scripts to clear out old data
However the size of the database does not seem to be reducing by much (i know the MDF file will stay same size and i've looked at space available inside this file)
Do we have to somehow defrag the table?
I've looked into index rebuilds are they are highly fragmented, and wondering if this is what we have to do to release the space

Any feedback appreciated
(Sql server 2017 enterprise)
0
I have a run longing script (30 days) and I want to see the progress/size of one of the temp tables

however this doesn't work
select count(*) from [tempdb].[dbo].[#DataToDeleteTable]

Open in new window


its says
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 8
Invalid object name '#DataToDeleteTable'.

Open in new window


I've also tried
SELECT COUNT(*) 
FROM #DataToDeleteTable

Open in new window


but get
Msg 208, Level 16, State 0, Line 5
Invalid object name '#DataToDeleteTable'.

Open in new window


if i run this
select * from tempdb.sys.objects

Open in new window


I see the name
#DataToDeleteTable__________________________________________________________________________________________________000000005144

Open in new window


How might I get the count of this temp table?
0
Been Googling for this and haven't found a solution (I'll research more and post back if I find my answer)

1. If I have a column like this data:

STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:4;GMT: 55;HH:22;MM:5566

2. I want to update this column but only replace LOC:4; with a new value. I want to replace the value after LOC: up to ; with a new value. Leave the rest as is.

3. For example, the new value is 4,10004,10005 I want the new string be:

STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:4,10004,10005GMT: 55;HH:22;MM:5566
0
I've tried the 3 statements below but still can't get it working. . This is what I want

Original string: STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:23,19,25,10003,20,21,22,10000,10002,10005,10004,10001,24,4;GMT:444

Anything after LOC: and before ;

We will always have LOC: and the semicolon.

This is what I have. Close but still not getting what I need:

DECLARE @string VARCHAR(MAX) ='STP:1/3/2020 12:00:00 PM;ENP:1/3/2020 12:00:00 PM;LOC:23,19,25,10003,20,21,22,10000,10002,10005,10004,10001,24,4;GMT:444'

Select Substring(@String,0,CharIndex('LOC',@String))

SELECT RIGHT(@string,LEN(@string)-CHARINDEX('LOC:',@string))

SELECT
    SUBSTRING(@string,CHARINDEX('LOC:',@string)+1,LEN(@string))

Open in new window

0
Scott Pletcher gave me this solution back in the day (I think it was 2004). I've used it several times and had a back up but I can't find it now. I'm in a rush to get something done for a client and need to use SQL loop using while (I don't want to use a cursor)

For the life of me, I can't remember how this was done. This is what I have and I know it's wrong because I can't get into the WHILE loop. If I figure it out, I'll post back.

 DECLARE @count INT = 1
 DECLARE @rowId INT
 Declare @EmailUserId int
 SELECT @count = COUNT(*) FROM #EmailUserAndEmployeeID --these IDs get emails
 SELECT @count -- get 6 rows

 WHILE @count > 0 --*** I think this is correct
BEGIN
    
	--**** I want to loop thru the table
	--SELECT TOP 1 @EmailUserId = EmailUserID,@rowid=rowid1 from #EmailUserAndEmployeeID  ORDER BY rowid1
	      --do other stuff here
       -- DELETE FROM #EmailUserAndEmployeeID WHERE rowid1 = @rowId --*** I think I have to do this so I get get the next rowId1 when WHILE loops again
	SET @count = @count - 1

	--SELECT @count
END

Open in new window

0
hello expert
I have a person table.
I don't want duplicate inserts,
ı want to use SQL Server Trigger to prevent duplicate inserts.
for example
CREATE TABLE person(
    tc_umarasi varchar(11),
     adi VARCHAR (50) ,
    soyadi VARCHAR (50) 
);

Open in new window

I tried to write a trigger
trigger is as follows
create TRIGGER [dbo].[dene_ali]
   ON [dbo].[person] FOR INSERT
AS
IF EXISTS(SELECT I.adi
		From Inserted I, person F
		Where  I.tc_umarasi=F.tc_umarasi AND I.adi = F.adi  
		Group By I.tc_umarasi,I.adi
		Having COUNT(*) >1 )
BEGIN
   
  RAISERROR('>>>DUPLICATES PREVENTED<<< ',10,1)
    ROLLBACK TRAN
	
END 

Open in new window

trying to add a few data
collectively when I insert
insert into person(tc_umarasi,adi,soyadi) values('11','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('88','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('33','ali sahin','bayoglu');

Open in new window

then I run the following query
SELECT * FROM person

Open in new window


tc_umarasi          adi                soyadi        id
11                         ali sahin      bayoglu      NULL
125                         ali sahin      bayoglu      NULL
88                         ali sahin      bayoglu      NULL

insert into person(tc_umarasi,adi,soyadi) values('33','ali sahin','bayoglu');-want this value to be printed in the database.
I want this value to be printed in the database.
but the database stoped the insert operation before it reaches this value.
When trigger encounters duplicate, I don't want to write value to the table, but I want it to continue with other insert operations.
how can I resolve this?
thanks a lot
1
hi, currently we are backup via ms sql 2005 by databasename. i have alot of database name and it is time consuming to backup one by one. is there a way to backup everything at once
0
I wonder if this is doable...I tried a pivot example and couldn't get it working. If not doable in SQL, I'll look into doing it in C#.

1. This is sample data. I have "12345" in the usage column as example.

CREATE TABLE #temptable ( [activationdate] nvarchar(20), [reportdate] nvarchar(20), [usage] money )
INSERT INTO #temptable
VALUES
( N'2018.1', N'2019.5', 12345.0000 ), 
( N'2018.2', N'2019.5', 12345.0000 ), 
( N'2018.3', N'2019.5', 12345.0000 ), 
( N'2018.4', N'2019.5', 12345.0000 ), 
( N'2018.5', N'2019.5', 12345.0000 ), 
( N'2018.6', N'2019.5', 12345.0000 ), 
( N'2018.7', N'2019.5', 12345.0000 ), 
( N'2018.8', N'2019.5', 12345.0000 ), 
( N'2018.9', N'2019.5', 12345.0000 ), 
( N'2018.10', N'2019.5', 12345.0000 ), 
( N'2018.11', N'2019.5', 12345.0000 ), 
( N'2018.12', N'2019.5', 12345.0000 ), 
( N'2019.1', N'2019.5', 12345.0000 ), 
( N'2019.2', N'2019.5', 12345.0000 ), 
( N'2019.3', N'2019.5', 12345.0000 ), 
( N'2019.4', N'2019.5', 12345.0000 ), 
( N'2019.5', N'2019.5', 12345.0000 ), 
( N'2019.6', N'2019.5', 12345.0000 ), 
( N'2019.7', N'2019.5', 12345.0000 ), 
( N'2019.8', N'2019.5', 12345.0000 ), 
( N'2019.9', N'2019.5', 12345.0000 ), 
( N'2019.10', N'2019.5', 12345.0000 ), 
( N'2018.1', N'2019.6', 12345.0000 ), 
( N'2018.2', N'2019.6', 12345.0000 ), 
( N'2018.3', N'2019.6', 12345.0000 ), 
( N'2018.4', N'2019.6', 12345.0000 ), 
( N'2018.5', N'2019.6', 12345.0000 ), 
( N'2018.6', N'2019.6', 12345.0000 ), 
( N'2018.7', N'2019.6', 12345.0000 ), 
( N'2018.8', 

Open in new window

0
I want to sum the 2 select statements in the inner cursor loop.   I get this error: Invalid column name 'TotalDebits'.

I looked at example online and this looks correct. I also tried using the + operator. I removed the second "select" as a test and ran it but still the same error.

I've marked it where I get the error:

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
              -- INSERT INTO #usage (reportdate,activationdate,usage)
			       SELECT CAST(@rYear AS VARCHAR(4)) + '.' + CAST(@rMonth AS VARCHAR(2)), 
				          CAST(@aYear AS VARCHAR(4)) + '.' + CAST(@aMonth AS VARCHAR(2)),
				          SUM(TotalDebits)  ---- ******* I get the error here **************
				        (
					    	
							SELECT 
							   
								   SUM(dcp.[Cash Debit] * -1) TotalDebits 

							......
							 
                            
						UNION ***** I tried the + operator here 
						SELECT SUM([CashBalance]) TotalDebits
						...
                       )   -- union statement
							
					Fetch Next from 

Open in new window

0
We need to get a script done for our client (their developers, DBAs in the dept quit within a few weeks and no one bothered to ask them to turn over their code and scripts!)

My manager came up with this cursor loop but I get an error (I know cursors are not recommended but we have to get this done for now). Not sure where he got the script but I think the placement of closing the cursors is off somewhere..

error: A cursor with the name 'activationCursor' already exists.

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
                                      INSERT INTO #usage (reportdate,activationdate,usage)
				         SELECT
				                .....
					Fetch Next from activationCursor into @aYear, @aMonth
				End
			Close activationCursor
		 --Insert into Usage table
		 
		 Fetch Next from reportCursor into @rYear, @rMonth	
	End
	Close reportCursor

Open in new window

0
I've been looking at examples on how to changes rows into columns and still can't get this:

1. This is an example

CREATE table #final2
(
 whatmonth VARCHAR(10),
 
  col decimal
)

INSERT INTO #final2
(
    whatmonth,
    col
)
SELECT 'July',194


INSERT INTO #final2
(
    whatmonth,
    col
)
SELECT 'June',123

INSERT INTO #final2
(
    whatmonth,
    col
)
SELECT 'total',317

SELECT * FROM #final2

Open in new window


I want it  to be:

total    June   July
317     123   194


I was playing around with this example. I actually want it to be dynamic because I don't know how many rows there will be:

      
select *
from 
(
  select whatmonth, col
  from #final2
) src
pivot
(
  sum(col)
  for whatmonth in ([1],[2],[3])
) piv;

Open in new window

0
I have a high throughput c# console app which is saving around 250-350 records a sec to a log table (sql server 2017 enterprise)

We are looking at ways to increase performance and sometimes a report is run on the table and things grind to a halt
We are also struggling to keep up at busy times

We currently do not use partitions, and are investigating this
We also insert one record at a time, and attach the insertedID to the c# object of log data for further operations

I'm considering partitions, however i'm not sure what to partition on hour, day, week month
We generally store 6 months of historical data (could also be 1 month or 24 months for smaller number of IOT devices.)
data is usually queried by day, last 7 days, week, month, and sometimes custom date ranges.
I would welcome ideas and comments about what is the best option here, we write more than we query, but queries will span multiple days/weeks

I'm also considering a different approach on inserting, instead of one at a time, do a batch insert.
However i'm unsure how I can then associate the database insertedID with C# object.
Currently its one at a time, so i just add the insertedID to the object, and send that off to messages queues for further processing,

I was looking at the SQL OUTPUT and inserted.ID but not sure when batching 500 records how to marry the inserted ID with the c# object

So in summary I would welcome ideas on
1) partition setup
2) batching inserts and marrying the …
0
I have a large table with ID identity seed int (primary key)

Its currently at 1.8 billion rows and growing rapidly
When this hits 2.1 billion rows it will reach the max int size.
Therefore I need to change it to bigint with minimal down time

I've read a LOT of articles online about doing this
I have 3 indexes on this table which references the ID, so I know these will need to be dropped and recreated

My current theory is this
1. Create new column NewId Bigint
2. copy the primary key/int values to this new column so it matches
3. drop all indexes, and unset primary key
4. Drop old ID column
5. Rename "NewId" to "ID"
6. make the new ID column primary key, and identity seed (even though they already contain Id's ?)
7. re-create indexes

Is this solution plausible? what are the issues with the above?
Sql server 2017 enterprise.
0
Hello,

I have these two queries

This one returns 353 rows

select c.store,a.vitem as OrderCode,a.auditid,b.invoicenum, sum(a.quan)as QTY, a.descript as Description, a.Upc, b.quanship as SVInvoice
from nh_svauditdetail a, nh_svauditinvoice b, nh_svaudithdr c
where a.auditid =5896 And b.invoicenum in(499211) And a.auditid=c.id And b.store=10 And a.vitem=b.itemcode group by c.store,a.auditid,b.invoicenum,a.vitem, a.upc, a.descript,b.quanship order by a.vitem

This one when i add the d.pack returns  384 rows.

select c.store,a.vitem as OrderCode,a.auditid,b.invoicenum, sum(a.quan)as QTY, a.descript as Description, a.Upc, b.quanship as SVInvoice, d.pack
from nh_svauditdetail a, nh_svauditinvoice b, nh_svaudithdr c , sh_item d, sh_upc e
where a.auditid =5896 And b.invoicenum in(499211) And a.auditid=c.id And b.store=10 And a.vitem=b.itemcode and a.upc=e.upc and e.id=d.upc group by c.store,a.auditid,b.invoicenum,a.vitem, a.upc, a.descript,b.quanship,d.pack order by a.vitem

I belive i need to move to a "Join" statement instead so the d.pack doesn't get included in the group by and cause the inflation of results.  Just not sure how to structure a multiple table join.  Thanks for any help provided and Happy 4th.

Microsoft SQL server 2005

Nick
0
This is SQL 2012. I know the data is not correct but my manager wants somehow to get the display of these rows correct. I think we (or ask the client) to run their test again but my manager says no.

I want to see if there's a way to fix this before I tell him there's no way to do this.

I created a sample  data:
create table #test
(
  tag varchar(20),
  dateandtime datetime,
  recieptNumber int

)

insert into #test
 select 'FSS', '2019-06-30 17:47:11.153', 0
 
 insert into #test
 select 'ABC', '2019-06-30 17:47:10.153', 123


insert into #test
 select 'XYZ', '2019-06-30 17:47:12.153', 123

select * from #test
order by dateandtime desc

Open in new window


1. You see DateAndTime column. We want to order by that column desc.
2. You see the "tag" column. I have "FSS" and 2 other ones
3. recieptNumber for XYZ and ABC will always be the same. FSS will always have zero as the recieptNumber.
4. We order by DateAndTime desc and it looks like this:
sql-problem.png
5. I want FSS row to show up first. I tried "group by" but it's not "group by'. Ordering by DateAndTime will always  display the rows like that. Anyway to have it display like this?

FSS	2019-06-30 17:47:11.153	0     -- then this one but datetime is between ABC and XYZ but I still want it to show up first.
XYZ	2019-06-30 17:47:12.153	123  -- this this one because it came after teh ABC row below
ABC	2019-06-30 17:47:10.153	123   -- this row last because of the datetime stamp

Open in new window

sql-problem.png
0
I have set up a SQL job to send the Deadlock alert in SQL server 2012. Whenever Deadlock occurs in the database it will send an alert email. This has been configured based on the SQL error 1205. I have been receiving 5 to 6 deadlocks every day but the application or in reports, we did not see any issues. Is it something I need to investigate or it's common in getting SQL deadlock in SQL Server? SQL Server is designed to detect and resolve deadlocks automatically right?
0
Hello Guys

I have a table with 1.000 rows, this table doesn't have an identity column and I'd like to alter my table and add an identity column for this table.

How can I do this ?

regards
0
How can I get better performance with my sql query in a SP? if you look at below my execution pan you will see that :

IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)

Open in new window


has alot of memory usage. How  can I reduce that?

ALTER PROCEDURE [Common].[SaveCustomerEntityIds] 
(
	@EntityType	NVARCHAR(128),
	@CustomerId	INT,
	@EntityId	INT OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)
	BEGIN
		INSERT INTO Common.[CustomerEntityIds]
					([EntityId]
					,[CustomerId]
					,[EntityType])
				VALUES
					(0
					,@CustomerId
					,@EntityType)
	END

	UPDATE	Common.[CustomerEntityIds]
	SET		[EntityId] = ([EntityId]) + 1
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId

	SELECT	@EntityId = EntityId
	FROM	Common.[CustomerEntityIds]
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId
END

Open in new window





picture of execution planpicture of execution planExecutionPlan2.sqlplan
0
Hi,
I have an error when trying to create a view in SQL
SELECT [Date],Age,Alarms,CardNumber
FROM dbo.Everything

and the error message

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Everything'.

I tried typing Everything
and dbo.Everything
and Everything.dbo
But none of them worked.

Yet Everything is a valid table name

Please tell me what I'm doing wrong
Thanks
Ian
0
Need some help to create a view.

SELECT     A.Datum, A.ABNr, C.Name1, C.Ort, C.DebitorenNr, B.Einheitbez14wert AS AuftragsGewicht, B.Einheitbez6wert AS AuftragsStck, A.WaagenNr, 
                      A.WiegekartenNr, A.Netto AS NettoFertigm, A.Brutto, A.Stück, A.Abweichung, CAST(A.StückGewicht AS VARCHAR(10)) AS StückGewicht, A.Mitarbeiter, 
                      A.theoretischeGewicht, A.theoretischeStückzahl, C.Fertigmeldungdatum, C.Fertigmeldunggewicht, C.Fertigmeldungstückzahl, B.Einheitbez1wert AS Typ, 
                      B.Einheitbez3wert AS Breite, B.Einheitbez4wert AS Breite2, B.Einheitbez5wert AS Länge, B.Einheitbez2wert AS Stärke, 
                      B.Artikelkurzbezeichnung AS Güte, B.Kommission, B.Bestellinfo AS BestNr, B.Preiseinheittext AS Preiseinheit, 
                      B.VKPreisBruttoproVertriebseinheitgesamt AS VKPreis, B.Einheitbez9wert AS Paketgewicht, B.Sonderbemerkung, C.Lieferzeit, A.IDNr
FROM         dbo.Waagendaten A INNER JOIN
                      dbo.Auftragsbestätigungspositionen B ON A.ABNr = B.ABNr INNER JOIN
                      dbo.Auftragsbestätigungen C ON A.ABNr = C.ABNr
WHERE     (B.KalkulationMetallwarenIDNr <> '') AND (B.Einheitbez1wert <> 50)
GROUP BY A.ABNr, C.DebitorenNr, C.Name1, C.Ort, C.Lieferzeit, B.Einheitbez1wert, B.Einheitbez3wert, B.Einheitbez4wert, B.Einheitbez5wert, B.Einheitbez2wert, 
                      B.Einheitbez14wert, B.Einheitbez6wert, A.WaagenNr, A.WiegekartenNr, B.Artikelkurzbezeichnung, 

Open in new window

0
Hi

I still have one more issue on the opening stock, below is my query in ms access

SELECT Qry28000StockReportOpening.GrnDate, Qry28000StockReportOpening.ProductID, Qry28000StockReportOpening.ProductCode, Qry28000StockReportOpening.WHID, Qry28000StockReportOpening.ProductName, Qry28000StockReportOpening.OpStock, Qry28000StockReportOpening.Production, Qry28000StockReportOpening.Grn, Qry28000StockReportOpening.Sales, Qry28000StockReportOpening.Issue, Qry28000StockReportOpening.Diff, ((IIf(IsNull([OpStock]),0,[OpStock])+IIf(IsNull([Production]),0,[Production])+IIf(IsNull([Grn]),0,[Grn]))-(IIf(IsNull([Sales]),0,[Sales])+IIf(IsNull([Issue]),0,[Issue])+IIf(IsNull([Diff]),0,[Diff]))) AS Net
FROM Qry28000StockReportOpening

I want the result of the above query to come into the report by referencing the report stock code called ProductID
=IIf(IsNull(DSum("Net","QrystockSummary","[ProductID] =" & [ProductID] & "AND [GrnDate]<#" & Format(([Forms]![frmStockReportPrinting]![txtStartDateStocksg]),"yyyy\/mm\/dd") & "#")),0,DSum("Net","QrystockSummary","[ProductID] =" & [ProductID] & "AND [GrnDate]<#" & Format(([Forms]![frmStockReportPrinting]![txtStartDateStocksg]),"yyyy\/mm\/dd") & "#"))

Now for whatever reasons it’s  pulling  only one product instead of all how do I fix it
0
This is SQL 2012. I'll go step by step and show what I need done. I'm thinking I need a loop but also somehow to see the next row

1. Sample data
  Create Table #test
(
 Device VARCHAR(250),
 CashCredited Money,
 PlayType VarChar(50),
 DateAndTime DateTime,
 [Transaction ID] BIGINT,
 accunulated money)

--inserts

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX',-2.50,'Debit','2019-02-05 11:12:21.920',1211793,0 --last column needs to be -2.50


INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', 0,'Credit','2019-02-05 11:12:45.000',1211794,0

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', -1,'Debit','2019-02-05 11:30:26.383',1211795,0 -- last column needs to be -3.50

  INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'FSS',0 ,'Posting','2019-02-05 11:30:27.383',8888,0 --second column needs to be 3.50

   INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'MX',0,'credit','2019-02-05 11:31:01.000',1211796,0

    INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 

Open in new window

0
Hello,  I have a query that is aggregating incorrectly. I'm no SQL expert and have been unable to get it to work correct.  
I have the following data sets.
Table 1
select * from nh_svauditdetail where auditid=949 and vitem=7211197

id        auditID                  upc            vitem                      descript                      cupc                quan  
257031      949      0004130301175      7211197           EE WAFFLE HMSTYL           1004130301175      1      
257110      949      0004130301175      7211197           EE WAFFLE HMSTYL           1004130301175      1      
257113      949      0004130301175      7211197           EE WAFFLE HMSTYL           1004130301175      1      

Table 2
select * from nh_svauditinvoice where invoicenum=383560 and itemcode=7211197
id      store      custnum      invoicenum            itemcode      dept      upc                     quanorder         quanship                 oos                        brand                      description      
126168      26      453426          383560                     7211197         FR      0004130301175             2                               2                   0                            ee                    WAFFLE HMSTYL 10CT      
I'm using the following query to aggregate the data

select a.vitem as OrderCode,a.auditid,b.invoicenum, sum(a.quan)as QTY, a.descript as Description, sum(b.quanship)as SVInvoice
from nh_svauditdetail a, nh_svauditinvoice b
where a.auditid =949 and b.invoicenum in(383560) and  
b.store=26 and a.vitem=b.itemcode and a.vitem=7211197 group by a.vitem,a.auditid,b.invoicenum, a.descript order by a.vitem
 
and get this result
OrderCode      auditid          invoicenum          QTY                       …
0
Hi I have 2 customers in a table.

custid  type
1       deposit
1       payment
2       deposit
2       credit
3       deposit
3       payment

now I only want the customers who has BOTH type as Deposit and Payment. So i want to select custid 1
and 3 in this case .

can someone give me that tsql please ?

Many Thanks
0
SELECT        COUNT(Receipt_Detail.Receipt_Number) AS totalcountreceipt, SUM(Receipt_Detail.Amount_Applied) AS receiptamount, Invoice_Header.Invoice_Number,?
FROM            Invoice_Detail INNER JOIN
                         Invoice_Header ON Invoice_Detail.Invoice_Number = Invoice_Header.Invoice_Number INNER JOIN
                         Member_Association ON Invoice_Header.Member_Number = Member_Association.Member_Number INNER JOIN
                         Member ON Invoice_Header.Member_Number = Member.Member_Number INNER JOIN
                         Receipt_Detail ON Invoice_Detail.Invoice_Number = Receipt_Detail.Invoice_Number INNER JOIN
                         Receipt_Header ON Member_Association.Member_Number = Receipt_Header.Member_Number AND Receipt_Detail.Receipt_Number = Receipt_Header.Receipt_Number
WHERE        (Invoice_Detail.Charge_Code = 'D' OR
                         Invoice_Detail.Charge_Code = 'T' OR
                         Invoice_Detail.Charge_Code = 'N')  (Invoice_Header.Invoice_Date BETWEEN '20181201' AND '20190131') AND (Receipt_Header.Deposit_Date BETWEEN 
                         '20181201' AND '20190131')  and (Invoice_Header.Invoice_Amount > 0)
GROUP BY Invoice_Header.Invoice_Number
ORDER BY totalcountreceipt DESC

Open in new window

Inside of "?" if I want to add like below, is it possible? Please show me how.

select top 1 (Receipt_Detail.Amount_Applied) where Receipt_Header.Deposit_Date BETWEEN '20181201' AND '20190131' order by Receipt_Header.Deposit_Date asc
0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.