T-SQL (SS2K8) :: How To Group Total Count Of Similar Items

Mar 30, 2015

We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.

So some sample records of a complete package would look like this:

ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3

ItemId's 2 & 3 are items contained within the ItemId 1 package.

Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:

Name | Available Qty
--------------------------
Package A | 15
Package B | 100

How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?

View 6 Replies


ADVERTISEMENT

SQL 2012 :: Fast Way To Do Group By Count On Items Within Group?

May 1, 2014

select top 15 count(*) as cnt, state from table
group by state
order by cnt desc

[code[...

Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.

View 9 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,

[code]....

I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,

[code]....

View 1 Replies View Related

Reporting Services :: Group And Sum Items / Sub-items Into One Record

Apr 10, 2015

I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products).  In some cases, items can be components of another item (called "Kits").  In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items".  In our Items table, we have a field called "Parent_Item_Id".  Records for Child Items contain the Item Id of the parent.  So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100

Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item.  So an example of the report would be the following:

Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100

How I can setup my report to group properly?

View 6 Replies View Related

DELETE Items Where Count(items) &>1

May 12, 2006

I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)


idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0


I want to DELETE each Row IN



SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1



and WHERE isOK = 0

in my exemple , after deleting, my table must look like



idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0



thank you for helping

View 10 Replies View Related

Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

View 4 Replies View Related

Reporting Services :: SSRS Group Total Expression - Add Total Disabled

Oct 26, 2015

For some reason my Add Total is grey out, when i tried to add grand total using some expression.

I have two row & two column groups?

Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image

I'm using iff condition in my expression.. 

View 15 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

Repeat First Row Group Header For Second Row Group Items

Jun 6, 2007

I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.



Example:

Now:




Code Snippet
Column Column
Section1 -

Mnemonic

Mnemonic

Mnemonic

Mnemonic

Section2 -

Mnemonic

Mnemonic

Mnemonic

Mnemonic





Should be:


Code Snippet

Column Column

Section1 -

Section1 Mnemonic

Section1 Mnemonic

Section1 Mnemonic

Section2 -

Section2 Mnemonic

Section2 Mnemonic

Section2 Mnemonic

View 2 Replies View Related

What's Microsoft Doing About Providing Page Numbering Per Group And Total Pages Per Group?

Nov 27, 2007

Hi!

I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.


https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679

Provide Individual Page Numbering per Group and Total Pages per Group

Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.


I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.


Thanks!
Karen

View 1 Replies View Related

Count Records With Almost Similar Names

Oct 1, 2015

I want to count records which has almost similar name. Suppose record has company name like comcast and other record has company name like Comcast-xfinity, so how would I count this 2 records as Comcast?

View 4 Replies View Related

Reporting Services :: SSRS - Total Sum For Different Report Items

Jul 19, 2015

I have the following record set; for each group where the code of OP=MTL summarize Cost; that is group 10-10= 300, group 20-20=300, group 30-30=600. Then I need to total groups 10-10 and 30-30.

I used ReportItems fields for each break as;

=SUM(IIF(Fields!OprSeq.Value=10 and Fields!RelatedOperation.Value = 10, Fields!Cost.Value,0), "Related Operation") etc,

But obviously when the result is false the ReportItem returns zero, and now I lost the value calculated previously. 

Line OP Mtl COST
01 10 10 100
01 10 10 200
01 20 20 100
01 20 20 200
01 30 30 300
01 30 30 300

View 2 Replies View Related

Analysis :: SSAS MDX Running Total Of Items In Current State?

May 22, 2015

Let's have a table to show what I mean ...

As you can see I have added only one contract (for the sake of simplicity). At any given date I want to calculate the running total for a contract (all contracts), but the aggregation must take the contract state into consideration.

You can see my expected result for queries 1 - 3. That means, with the actual state ('B') I want to aggregate all values regardless of their state in the past.

For query 2 the state is 'A' ... I hope you can follow.

Now my aim is to aggregate all contract states for any given date with the right value. The past states of each contract are not relevant. And of course future ones (with respect to the query date) shall also be irrelevant.

Maybe the solution is a combination of 'LastNonEmpty' and SUM/PERIODSTODATE ...

View 5 Replies View Related

Transact SQL :: Add Group Number To Similar Rows?

Jun 9, 2015

How can I add a group number to the following query?

For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):

RN                   ReportDate Category                       Division                       TotalBalance
-------------------- ---------- ------------------------------ ------------------------------ ---------------------
1                    2015-06-08 Field Sales                    CA BDM                         299743154.3912
2                    2015-06-07 Field Sales                    CA BDM                         299765954.0354
3                    2015-06-01 Field Sales                    CA BDM                         297902654.4172
1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

Becomes

GN   RN                   ReportDate Category                       Division                TotalBalance
-------------------------- ---------- ------------------------------ ------------------------------ ---------------------
1      1                    2015-06-08 Field Sales                    CA BDM                     299743154.3912
1      2                    2015-06-07 Field Sales                    CA BDM                     299765954.0354
1      3                    2015-06-01 Field Sales                    CA BDM                     297902654.4172
2      1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2      2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
2      3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
2      4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

i.e. each combination of Category+Division results in a new GN.

The query is:

selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN'
, ReportDate
, Category
, Division
, sum(BalanceGBP) as 'TotalBalance'
FROM FlowsAndOpenings
group by ReportDate, Category, Division
order by Category, Division, RN

View 2 Replies View Related

How To Group Similar Column Name And Sum The Similar Column Name Together

Apr 10, 2008

Hey Gurus,

I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).


A million thanks in advance.

View 5 Replies View Related

Count Items In Recordset

Feb 18, 2008

Hi All,
I have a question regarding recorcounts. I have a set of records that i got after writing the sql query. The record layout is something like:

ID Amountdue
1 5
2 10
3 40
4 0
5 -6
6 230
7 -5
8 100
9 -1
10 0

I want to get the count of each amount due more than 0, less than 0 and equal to 0 all in the same query. So the result should look something like

Order PassGT0 PassLT0 PassEQ0
1 5 NULL NULL
2 NULL 3 NULL
3 NULL NULL 2

i can do 3 separate select count(*) if these were stored in a table but the result set was created using joins in the Query Analyser. I can also achieve the above result using unions with 3 separate queries with different conditions but this way i have to join the tables 3 times in each query and this is slowing down the query.

Please let me know if there is some other way i can achieve this.

Thanks

View 5 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

Returning A Count Of Items Including Zero

Jul 23, 2005

Hi,I have 2 tables, Mail_subject and Mail_Usage.Mail_Subject contains the subject, body and some other bits of info.CREATE TABLE [Waterford_MailSubject] ([ID] [int] NOT NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailCategory] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailBody] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[MailCreateDate] [smalldatetime] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOMail_Usage records the amount of times a certain mail was sent.CREATE TABLE [Waterford_MailUsage] ([ID] [int] NOT NULL ,[RepScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MemberScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[TimeDate] [smalldatetime] NULL ,) ON [PRIMARY]GOThey are joined by Subject (not my idea, its a DB ive inherited).What i need is to get the Mail Subject and the number of times thatMail was sent. Ive Joined them using an INNER JOIN which gave me acount of the number of times each one occoured except for Mails thathave not been used. I need to get zero as the count of Mails notsent. Ive tried a LEFT OUTER JOIN but it didnt work either.Can someone point out what i need to do ?

View 1 Replies View Related

How To Count No Of Items In Nested Table

Aug 28, 2007

I have the following table
Region Table
ID
ParentID
RegionName

RestaurantTable
ID
RestaurantName
RegionID

What i tried to do is count the number of restaurants by specific regionname. My current query is
Select RegionID, RegionName, count(*) as RestaurantNo
From Region Inner Join Restaurant
On Region.ID = Restaurant.RegionID

However I only get the results below
RegionID RegionName RestaurantNO
1 A1 0
2 A1.1 2
3 A1.2 1
4 A1.3 0

Where A1.1 , A1.2, and A1.3 are children of A1 in Region table
The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3
Could anyone help me to solve this problem.
Thank you




View 6 Replies View Related

No Items In SQL Server Group

Apr 30, 2004

In my enterprise mngr under Microsoft sql Servers
Sql Server Group

I see (No Items)

My network guy doesnt no much about this and i know nothing (all my experience is with db on my local hd.


Please let me know as much as possible.


Thanks

View 1 Replies View Related

SQL Server Group (No Items) ... Help

Apr 17, 2008

I am using SQL Server 2000. I am logging on local machine. When I open "SQL Server Enterprise Manager," I only see following:

Console Root --> Microsoft SQL Servers --> SQL Server Group --> (No items)

The server is running properly and applications are able to access the database. Also, I can run "Query Analyzer" and view all databases.

Please help to resolve the issue to view items in SQL Server Group.
Thanks,
Suraj.

View 5 Replies View Related

No Items Under SQL SERVER GROUP

Mar 26, 2008



Group

I have sql server 2000 that was working fine until today,today I opened EM and under sql server group where used to be all my information now there is NO ITEM, nothing shows(database,Nothing) .no items under SQL SERVER GROUP.
But my application connected with sql server is running without any problem insert record and show all data.
ERROR MESSAGE:
A connection could not be established to sql server-2000-
Reason: Cannot open user default database. Login failed

Please vrify SQL Server is running and check your SQL server registration
properties(by right clicking on the Win-2000-Server node and try again)

PLEASE what that happens.
please iam afraid help me now
iam waitting now
Thanks


hassan

View 4 Replies View Related

SQL Server Group (No Items)

Apr 17, 2008

I am using SQL Server 2000. I am logging on local machine. When I open "SQL Server Enterprise Manager," I only see following:
Console Root --> Microsoft SQL Servers --> SQL Server Group --> (No items)

The server is running properly and applications are able to access the database. Also, I can run "Query Analyzer" and view all databases.

Please help to resolve the issue to view items in SQL Server Group.
Thanks,
Suraj.

View 6 Replies View Related

Aggregate And Group By - Sum Of Items Ordered In One Row

Jan 16, 2014

Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:

Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
from orders o, customers c, Employees e, Shippers s, [Order Details] od
where o.CustomerID = c.CustomerID

[Code] ....

Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?

View 6 Replies View Related

Query To Group Sequential Items

Jul 23, 2005

Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.

View 9 Replies View Related

T-SQL (SS2K8) :: OR Query - Search For Items And Separate Each Word

Oct 17, 2014

I have this query currently:

select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup
from updatedb
left join opencall
on updatedb.callref=opencall.callref

where udindex = '0'
and suppgroup = 'SUPPORT'
and (updatetxt like '%' + @Word + '%')

And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.

They would like it to be more fuzzy with "and" and "or". How can I adapt this?

View 8 Replies View Related

Group By / Hiding Report Items (Newbie)

Apr 3, 2008

Hi. First, I am VERY new to SQL Queries and Reporting. A co-worker is "mentoring" me, but I am trying not to fill his day with questions.

I HAVE read the help files, searched the forums, looked at books, and done general web searches, but any answers I have found have either no addressed my issue, or the answers are way over my head.

Furthermore, the (SQL 2000) DB is built into proprietary software (ISS Proventia Intrusion Prevention System), and the database may NOT be modified outside of the software.

With that said, I am querying multiple tables within the DB. I am using Business Intelligence Dev Studio, and placing my queries on a reporting server maintained by my co-worker. My goal is not only to get a solution, but also to UNDERSTAND it so I can continue to learn. Of course, the solution takes precedence over my understanding!

My Primary key is dbo.SensorData1.SensorDataID. dbo.SensorDataAVP.AttributeText returns a different number of rows, containing different data depending upon the value of dbo.SensorData1.AlertName. I need to return all rows, hence the Left Joins.

Depending upon my query, I might have 1000 events, and due to the many rows of data from dbo.SensorData1.AlertName I might return 20,000 rows (or more.)

I would like to return a report that "groups" events by dbo.SensorData1.SensorDataID., BUT, rather than simply providing these in groups, provides me single rows with a plus sign next to each even, that can be expanded for the additional data.

My co-worker has discussed sub-tables, but since I cannot modify the DB, it will be difficult / complex to do so, AND, for me to understand.

One of my queries follows. I have thirteen queries, total, that use various groupings of attributes. I have chosen one of the more complex combinations so I can generally apply the concept to the queries with fewer parameters more easily.

Note, I'll be asking the same question on www.sqlservercentral.com in the hopes of getting an answer I can understand one of these two places - If you answer here, there's obviously no need answering there answering there.

Thank you in advance.



SELECT
convert(nvarchar(20), AlertDateTime,120)
AlertDateTime,
AlertName,
AlertPriority,
AlertCount,
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))
SrcAddressInt,
SourcePort,
SourcePortName,
convert(varchar,(convert(bigint,DestAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,DestAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) % 256)))
DestAddressInt,
DestPortName,
dbo.SensorData1.ObjectName,
SensorName,
SensorInterfaceName,
AlertTypeID,
convert(varchar,(convert(bigint,SensorAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SensorAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) % 256)))
SensorAddressInt,
ProtocolID,
Cleared,
VulnStatus,
dbo.SensorDataAVP.SensorDataID,
dbo.SensorDataAVP.AttributeName,
dbo.SensorDataAVP.AttributeDataType,
dbo.SensorDataAVP.AttributeText,
dbo.SensorDataAVP.AttributeValue,
dbo.SensorDataAVP.AttributeBlob,
ResponseTypeName,
ResponseName

from
dbo.SensorData

LEFT JOIN

dbo.SensorDataAVP
ON dbo.SensorDataAVP.SensorDataID =
dbo.SensorData1.SensorDataID

LEFT JOIN
dbo.SensorDataResponse
ON dbo.SensorDataResponse.SensorDataID =
dbo.SensorData1.SensorDataID

LEFT JOIN
dbo.ObjectView
ON dbo.ObjectView.ObjectName=
dbo.SensorData1.ObjectName


WHERE
convert(nvarchar(20), AlertDateTime,120) between @StartDate and @EndDate

AND
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))

between @LowerIP and @UpperIP

AND
AlertName = @EventName

View 2 Replies View Related

Transact SQL :: Syntax To Group Items In One Line?

Nov 20, 2015

I have records that I get in this format:

ID                       Customer               Type                 TypeNUm
100                      Tiger                    Item                   T100  
100                      Tiger                     Item                   T200
100                      Tiger                     Item                   T300
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                   WE
200                       Panera                  PO                   POBBB

The reftypes are not always the same, what I need is to get it in this form

ID                       Customer               Type              TypeNUm
100                      Tiger                     Item                   T100,T200, T300 
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                      WE
200                       Panera                  PO                     POBBB

View 6 Replies View Related

T-SQL (SS2K8) :: Add Total Row For Each Name

Aug 29, 2015

I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this

Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here

but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'..This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed

Create Table sales
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)

Insert Into sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done')

View 6 Replies View Related

Total No Of Row Count

Sep 25, 2007

Hi,



I am using a foreach file to loop through mdb files kept in a folder and then transfer them to Sql server.

I want to do the following -



Sum the total no. of rows in mdb while the package executes

Sum the total no of rows transferred to Sql server.

Write them to a database (InitialRowCnt, FinalRowCnt, PackageNm, UserNm)


Note: Total = Total rows in all mdb files. so if there are 10 mdb files with 10 rows each, total = 100.


I saw a RowCount transformation..but just dont know where to place and what to do with it.



thanks

View 2 Replies View Related

Power Pivot :: Count Of Items Between 2 Dates Of The Same Date Column?

Nov 5, 2015

I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015. 

I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.

CountofQ1:=COUNTAX(DATESBETWEEN(DumpLoad1[Start Date],[StrtDate],[EndDate]),DumpLoad1[Start Date])

View 4 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 
 

View 1 Replies View Related

How?: Group By Date And Count Rows In Group

Jan 29, 2007

I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.

this is the best I can come up with:

Code:


SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date



The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

What am I doing wrong? Thanks!

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved