Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
magmo
Aged Yak Warrior
558 Posts |
 Posted - 2013-04-04 : 03:23:09
|
Hi
I have this table and data...
USE [Test]
GO
/****** Object: Table [dbo].[tbl_test] Script Date: 2013-04-04 09:14:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionID] [nvarchar](255) NULL,
[NumberOfCards] [int] NULL,
[DateAdded] [datetime] NULL,
[IsFetched] [bit] NULL,
[Duplicate] [bit] NULL,
[Format] [nvarchar](5) NULL,
CONSTRAINT [PK_tbl_test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tbl_test] ON
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (1, N'123', 1, CAST(0x0000A17400BC2378 AS DateTime), 1, 0, N'A5')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (2, N'123', 1, CAST(0x0000A17400BC2378 AS DateTime), 0, 1, N'A5')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (3, N'456', 2, CAST(0x0000A17400D83C71 AS DateTime), 1, 0, N'A4')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (4, NULL, 1, CAST(0x0000A17400F3306C AS DateTime), 1, 0, N'A4')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (5, N'0', 1, CAST(0x0000A17400F41889 AS DateTime), 1, 0, N'A4')
GO
SET IDENTITY_INSERT [dbo].[tbl_test] OFF
GO
I would like a query that could return a result that group the different formats, day and also by free and paid, it should be classified as paid if TransactionID is <> '', if its NULL, 0 or '' then it should be classified as free. It should also filter out only the ones that have "IsFetched" = 1 and "Duplicate" = 0, something like this..
QtyFree QtyPaid Day Format
2 1 2013-03-01 A4
0 1 2013-03-01 A5
Can someone please show me how this could be done?
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 03:42:24
|
[code]
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN Format END)AS QtyPaid,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN Format END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
[/code] |
 |
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 04:06:36
|
| Excellent, if I want to have the same conditions but count by "NumberOfCards" instead of TransactionID, how would that be? |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 05:11:24
|
Here we are counting formats, not the Transaction...
The following query is for getting count of NumberOfCards based on TransactionId rules
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END)AS QtyPaid,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
|
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 05:34:00
|
quote:
Originally posted by bandi
Here we are counting formats, not the Transaction...
The following query is for getting count of NumberOfCards based on TransactionId rules
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END)AS QtyPaid,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
Hi
If I change to SUM instead of count it seem to almost work as I meant, but I in the A5 format case I then get NULL instead of 0 for the QtyFree column.
This is what I changed to..
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-04 : 05:58:04
|
[code]
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
[/code]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 06:07:50
|
quote:
Originally posted by visakh16
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
Thanks, that worked, but now I see a new problem. If the TransactionID column contain a value like this "12345_69" then I get a "Conversion failed when converting the nvarchar value '12345_69' to data type int." error.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 06:08:14
|
--Alternate is
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
EDIT: Put 1 instead of NumberOfCards column
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN 1 ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN 1 ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-04 : 06:10:10
|
quote:
Originally posted by magmo
quote:
Originally posted by visakh16
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
Thanks, that worked, but now I see a new problem. If the TransactionID column contain a value like this "12345_69" then I get a "Conversion failed when converting the nvarchar value '12345_69' to data type int." error.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 06:27:32
|
quote:
Originally posted by visakh16
quote:
Originally posted by magmo
quote:
Originally posted by visakh16
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
Thanks, that worked, but now I see a new problem. If the TransactionID column contain a value like this "12345_69" then I get a "Conversion failed when converting the nvarchar value '12345_69' to data type int." error.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
That works fine, but if I run it in SQL Server 2005 I had to change DATE to DATETIME but then I doesn't get it grouped by the date, guess thats beacuse of the use of DATETIME, I would like them to be grouped by the date.
This is how I changed it...
SELECT Format, CAST(DateAdded AS DATETIME) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0 AND (DateAdded BETWEEN CONVERT(DATETIME, '2013-03-01', 102) AND CONVERT(DATETIME, '2013-03-03', 102))
GROUP BY Format, CAST(DateAdded AS DATETIME)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 06:36:18
|
May be this?
SELECT Format, MAX(DateAdded)DateAdded,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE) |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 06:49:06
|
quote:
Originally posted by bandi
May be this?
SELECT Format, MAX(DateAdded)DateAdded,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE)
Sorry but I cannot use DATE I have to use DATETIME, I get a "Type Date is not a defined system type.
" if I use that |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-04 : 06:51:40
|
quote:
Originally posted by magmo
quote:
Originally posted by bandi
May be this?
SELECT Format, MAX(DateAdded)DateAdded,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE)
Sorry but I cannot use DATE I have to use DATETIME, I get a "Type Date is not a defined system type.
" if I use that
use
SELECT Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0),
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0)
see
http://visakhm.blogspot.in/2010/01/some-quick-tips-for-date-formating.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 06:55:23
|
GROUP BY Format, DATEADD(dd, DATEDIFF(dd, 0, DateAdded), 0)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-04 : 07:02:18
|
Please be aware that using COUNT will generate warnings like below which causes issues in application side while executing queries
Null value is eliminated by an aggregate or other SET operation.
Whereas SUM will have very little chance of generating them
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 07:13:37
|
quote:
Originally posted by visakh16
quote:
Originally posted by magmo
quote:
Originally posted by bandi
May be this?
SELECT Format, MAX(DateAdded)DateAdded,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE)
Sorry but I cannot use DATE I have to use DATETIME, I get a "Type Date is not a defined system type.
" if I use that
use
SELECT Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0),
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0)
This is great, but shouldn't the time part be stripped of, it seem that it should if I check your page.
see
http://visakhm.blogspot.in/2010/01/some-quick-tips-for-date-formating.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-04 : 07:24:19
|
the timepart value would be stripped off and only 00:00:00 will remain
datetime always represents timepart also so if you want to strip off 00:00:00 part then you need to do it using formatting function in your front end application or using CONVERT in t-sql (if no front end is present)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 07:25:21
|
While grouping only we have stripped of Time part from DateAdded Column...
Whats the problem now? |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 07:27:08
|
quote:
Originally posted by visakh16
the timepart value would be stripped off and only 00:00:00 will remain
datetime always represents timepart also so if you want to strip off 00:00:00 part then you need to do it using formatting function in your front end application or using CONVERT in t-sql (if no front end is present)
Thank you all for the help, and thanks for the explanation about the time part.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 07:30:10
|
We can get date part only by using CONVERT function.. (Note: this is NOT recommend)
SELECT Format, convert(varchar(10),DateAdded,121) AS DateAdded |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2013-04-04 : 08:13:11
|
Hi again
I thought it would be nice to have an additional approach to this query. If I instead of showing QtyPaid and QtyFree as seperate values and instead have them as one value (QtyPaid + QtyFree as SumTotal) how would that be, I tried just to sum them both but then I got a "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." error... |
|
|
|
Next Page
|
|
|
|
|
Group by formats